11А Лабораторная работа #1-6( Microsoft Excel)

Лабораторная работа № 5

В этом задании вы научитесь вводить формулы, а также применять денежный формат к содержимому ячейки.

1. Создайте новый лист. Дайте ему название Работа 1.

1. Создайте таблицу и отформатируйте ее по образцу:

1. Выделите ячейки А2:А3 и, используя функцию АВТОЗАПОЛНЕНИЕ, введите последовательность чисел.

2. Введите формулу подсчета суммы оплаты. Для этого выделите ячейку H2. Наберите на кла- виатуре знак равенства «=», откройте круглую скобку, затем щелкните мышью ячейку G2 с датой убытия, наберите на клавиатуре знак минус «–», после этого щелкните по ячейке F2 с датой прибытия, закройте круглую скобку, поставьте знак умножения «*» и щелкните по ячей- ке Е2.

3. В строке формул появится формула =(G2–F2)*E2. Нажмите ENTER, чтобы закончить ввод формулы.

4. С помощью АВТОЗАПОЛНЕНИЯ распространите эту формулу на диапазон ячеек H3:H8.

5. Изменим формат ячеек Е2:Е8 и H2:H8 с числового на де- нежный. Выберите команду ЯЧЕЙКИ в меню ФОРМАТ, на вкладке ЧИСЛО выберите денежный формат и установите ОБОЗНАЧЕНИЕ – р., ЧИСЛО ДЕСЯТИЧНЫХ ЗНАКОВ – 2.

6. Используя кнопку АВТОСУММА , заполните ячейку H9.

7. Ячейку G9 залейте серым цветом.

В этом задании вы научитесь использовать встроенные функции, удалять и добавлять строки и столбцы, а также применять денежный формат к содержимому ячейки.

1. Задание выполнить на следующем листе, который назвать Налоги. На нем оформите следующую таблицу (фамилии и зарплаты пишите произвольно).

2. Добавьте границы и оформите (A1:D1):

полужирный, более крупный размер шрифта.

1. Ячейки А8:А11 оформите: полужирный, по правому краю.

2. Добавим формулу для расчета подоходного налога. Так как налог равен 13 % от зарплаты, то в ячейку D2 нужно вписать формулу =C2*0,13. Полученную формулу скопируйте автоза- полнением для всех сотрудников.

3. Для того чтобы высчитать сумму к выдаче, нужно из зарплаты вычесть налог, поэтому в ячейку E2 вводим формулу =C2–D2. Полученную формулу скопируйте автозаполнением для всех сотрудников.

4. Вычисление суммы: рассчитаем суммарную зарплату всех сотрудников, уплаченный налог и

общую сумму к выдаче. Воспользуемся кнопкой на панели инструментов. Сначала поставьте курсор в ячейку C8, затем щелкните по кнопке (Автосумма).

Теперь нужно выделить диапазон ячеек, от которых нужно считать сумму, но Excel обычно сам выделяет предполагаемый диапазон мерцающей пунктирной границей. Если вы соглас-

ны с выделенным диапазоном, то нажмите Enter, если нет, то самостоятельно выделите дру- гой диапазон. В данном случае диапазон должен быть C2:C7.

1. В ячейках D8 и E8 рассчитайте суммарный налог и сумму к выдаче. Можете снова использо- вать Автосумму или Автозаполнение ячейки

C8 направо.

1. Вычисление среднего значения: поставьте курсор в ячейку C9, нажмите кнопку (Вставка функции), которая находится перед строкой формул или выполните команду Вставка → Функция. Появится окно Мастера функций. В нем выберите категорию Статистические, найдите функцию – СРЗНАЧ, нажмите ОК.

При этом появится окно уточнения аргументов

функции. Excel обычно сам пишет предполагаемый диапазон, но давайте убедимся, правиль- но ли он написан. При помощи мыши отодвиньте окно так, чтобы было видно всю нашу таб- лицу. Нам нужно считать среднее значение только от зарплат сотрудников, т. е. от диапазона В2:В7. Excel предположил этот диапазон неправильно, но вы можете исправить это, выделив нужные ячейки. Как только нужный диапазон выбран, нажмите ОК.

1. В ячейках D9 и E9 рассчитайте среднее значение уплаченного налога и суммы к выдаче. Можете снова использовать вставку функции СРЗНАЧ или Автозаполнение ячейки C9 на- право.

2. В следующей строчке получим максимальное значение всех величин. В ячейку В10 вставь-

те статистическую функцию МАКС от диапазона C2:C7. Протяните эту формулу автозапол- нением на ячейки D10:E11.

1. В 11-й строчке получим минимальное значение всех величин. В ячейку C11 вставьте стати- стическую функцию МИН от диапазона C2:C7. Протяните эту формулу автозаполнением на ячейки D11 и E11.

2. Предположим, что один из сотрудников (например Орлов) уволился, значит нужно удалить из таблицы строчку со сведениями о нем. Щелкните правой кнопкой мыши по номеру 4-й строки и в появившемся меню выберите пункт Удалить. Строчка со всеми данными об этом сотруднике удалится, а все формулы автоматически изменят диапазон вычислений, для всех формул он станет C2:C6.

3. Предположим, что на работу приняли двух новых сотрудников, т. е. нужно добавить две но- вые строки. Щелкните правой кнопкой мыши по номеру любой строки, содержащей сведе- ния о сотрудниках (например, 3) и в появившемся меню выберите пункт Добавить ячейки.

Новая строчка появится выше выделенной. Теперь вам нужно вписать только фамилию и зарплату, все остальное рассчитается автоматически. Добавьте еще одного сотрудника.

1. Применим для всех ячеек, содержащих количество денег, специальный формат. Выделите блок ячеек B2:D11, выполните команду Формат → Ячейки, в появившемся окне перейдите на закладку Число, установите числовой формат Денежный и выберите обозначение «р.». Нажмите ОК.

2. Сохраните все изменения.

В этом задании вы научитесь использовать логическую функцию ЕСЛИ.

Следующий лист назовите Статус семьи.

На нем оформите следующую таблицу:

Для того чтобы длинные строки заголов- ков отображались в несколько строчек в центре ячейки, выделите ячейки A3:D3 и выполните команду Формат → Ячейки. В появившемся диалоговом окне выберите за- кладку Выравнивание и установите сле- дующие параметры: выравнивание по гори-

зонтали и по вертикали – по центру, установите галочку Переносить по словам. Нажмите ОК Статус семьи определяется по количеству детей. Если в семье 3 и более детей, то семья счита- ется многодетной. В ячейку D4 напишем соответствующую формулу. Выделите ячейку D4 и вставьте в нее функцию ЕСЛИ (эта функция относится к категории логических). Установите следующие аргументы: Значение_если_ложь впишите пробел (кавычки добавятся автоматиче- ски). Автозаполнением скопируйте эту формулу для всех сотрудников. В ячейке С15 при по- мощи Автосуммы посчитайте общее количество детей. Закройте книгу, сохранив все измене- ния.

При копировании формулы, содержащей относительные ссылки, и вставке ее в другое место ссылки будут меняться, настраиваясь на новое местоположение. Если необходимо, чтобы ссылки не изменялись при копировании формулы в другую ячейку, используются абсолютные ссылки.

Для создания абсолютной ссылки в адрес ячейки ставится знак доллара $ перед той частью ссылки, которая не должна изменяться. Если формула уже введена, то для установки знака дол- лара можно использовать клавишу F4, которую нужно нажимать до тех пор, пока на экране не появится нужный вид ссылки.

Если знак доллара стоит в таких вариантах А$4 или $А4, то адресация называется смешан- ной. То измерение, у которого стоит знак доллара, при копировании данного адреса не изменя- ется, а то измерение, где нет доллара – настраивается на новое местоположение.

Эта операция выполняется так же, как автозаполнение числами. Ее особенность заключается в необходимости копирования ссылок на другие ячейки. В ходе автозаполнения во внимание принимается характер ссылок в формуле.

Относительные ссылки изменяются в соответствии с относительным расположением копии Vi оригинала. При копировании формулы вниз номера строк увеличиваются на единицу, а но- мера столбцов остаются постоянными. При копировании формулы вправо увеличиваются но- мера столбцов.

Для упрощения просмотра и присвоить имя.

запоминания формул одной или нескольким ячейкам можно

Чтобы присвоить имя ячейке, выделите ячейку и выполните команду Вставка → Имя → Присвоить.... В нужное поле впишите имя и нажмите ОК. В поле имени будет отражаться но- вое имя ячейки. Теперь к ней можно обращаться по имени, которое не будет изменяться при копировании и автозаполнении, т. е. будет являться абсолютным адресом ячейки.

Формулы могут ссылаться на ячейки других рабочих листов. Ссылка на ячейку другого лис- та выглядит так: !. Например так: Лист1!А2

В этом задании вы научитесь использовать абсолютную адресацию с использованием сим- вола $.

1. Откройте новую книгу. Сохраните ее в свою папку под именем Абсолютная адресация.

Первый лист этой книги назовите

Прайс-лист ($). На нем оформите

таблицу, как показано на рисунке.

1. В ячейку С2 вставьте функцию СЕ- ГОДНЯ, которая показывает теку- щую дату. Эта функция относится к категории Дата и время.

2. Для расчета цены в рублях

попробуйте сначала использовать

обычную формулу. Т. е. в ячейку С7

введите =В7*С4 и попробуйте автозаполнением распространить ее на нижние ячейки. По- смотрите, что получилось.

1. Объяснение: при копировании вниз автоматически увеличивается номер строки, т. е. в ячей- ке С8 будет формула =В8*С5. Но так как ячейка С5 пуста, Excel не смог правильно вычис- лить формулу. Удалите скопированные формулы в ячейках С8:С14.

2. В начальной формуле =В7*С4 используем абсолютную адресацию для ячейки С4, поскольку при копировании этот адрес должен оставаться неизменным. Исправьте формулу в ячейке С7 на следующую =В7*С$4. Знак доллара можете набрать на клавиатуре, а можете исполь- зовать клавишу F4. Для этого щелкните мышью в ячейке С7 в формуле так, чтобы курсор стоял в ссылке С4 и нажимайте клавишу F4 пока не получите нужную адресацию.

3. Скопируйте эту формулу вниз автозаполнением. Убедитесь, что расчет произведен правильно.

В этом задании вы научитесь использо- вать абсолютную адресацию с использо- ванием имен ячеек.

1. В книге Абсолютная адресация скопи- руйте лист Прайс-лист ($). Его копию на- зовите Присвоение имени. На нем удали- те содержимое ячеек С7:С14.

2. Присвоим ячейке С4 имя. Выделите

ячейку С4, выполните команду Вставка → Имя → Присвоить..., впишите имя ячейки kurs.

Нажмите ОК.

1. Теперь посмотрите, что в поле имени этой ячейки указывается не С4, a kurs. Теперь к этой ячейке можно обращаться по новому имени, которое не будет изменяться при копировании и автозаполнении, т. е. будет являться абсолютным именем ячейки.

2. В ячейку С7 введите формулу =B7*kurs.

3. Полученную формулу скопируйте вниз для всех товаров. Проверьте правильность вычислений.

Создайте таблицу умножения, используя относительную и абсолютную адресацию:

Начальные данные введите с помощью автозаполнения.

В ячейку B2 введите формулу по смыслу (надо заполнить таблицу умножения). Автозапол- нением протяните формулу вниз и, не снимая выделения, вправо. Если вы ввели формулу вер- но, то должна получиться таблица умножения.

Сохраните документ под именем Таблица умножения.

Для того чтобы построить диаграмму: выделите ячейки, содержащие данные, которые должны быть отражены на диаграмме; если необходимо, чтобы в диаграмме были отражены и названия строк или столбцов, выделите также содержащие их ячейки; нажмите кнопку

Мастер диаграмм или выполните команду Вставка → Диаграмма....

Для создания диаграмм из несмежных диапазонов нужно выделить первую груп- пу ячеек, содержащих необходимые

данные, далее, удерживая клавишу CTRL, выделить необходимые дополнительные группы ячеек и нажать кнопку Мастер диаграмм. Перед вами появится окно Мастера диаграмм.

Нужно выбрать тип и вид диаграммы. Чаще других используются гистограммы, графики, линейчатые и круговые диаграммы. Когда вы щелкаете на Тип диаграммы, в области Вид по- являются виды диаграммы, соответствующие данному типу. Можно щелкнуть Просмотр ре- зультата, чтобы увидеть, как выделенные данные будут выглядеть в конкретном виде диа- граммы.

На этой странице Мастера следует убедиться, что окно Диапазон содержит ссылку на нуж- ные ячейки таблицы. На закладке Ряд можно выбрать ряды данных, которые будут использо- ваться для подписей содержимого диаграммы.

В зависимости от типа диаграммы это окно может иметь разное количество закладок, здесь можно задавать заголовки, добавлять или убирать линии сетки, подписи данных и т. д.

На этом шаге можно определить, создавать ли диаграмму на новой странице или на сущест- вующем листе. После щелчка по кнопке Готово, диаграмма появится на листе.

Ось значения

Заголовок диаграммы

Данные

Легенда

Элемент легенды

Ключ легенды

Ось категорий

Можно изменять размеры диаграммы, перемещая один из маркеров размера, расположенных на краях и углах диаграммы.

Можно изменить способ заливки или фон, открыв диалоговое окно Формат области диа- граммы (щелкнуть правой кнопкой мыши на область диаграммы).

Изменять любые элементы диаграммы можно при помощи щелчка правой кнопкой мыши по этому элементу, при этом появляется меню, в котором нужно выбрать пункт Формат.

Задание 6

В этом задании вы научитесь строить круговую диаграмму на основе данных из несмежных ячеек таблицы. Также научитесь оформлять заголовки таблиц, расположенные в нескольких строчках.

1. Создайте новую книгу, сохраните ее под именем Диаграммы. Ее первый лист назовите На- числение.

2. Создадим таблицу с начислением заработной платы и построим по этим данным диаграмму:

1. Сначала подготовим строку заголовка. Заголовок размещен в двух строках таблицы, поэтому сначала нужно объединить некоторые ячейки, затем занести туда данные. Выделите те ячейки, которые следует объединить (в нашем случае это ячейки D1 и Е1), а затем щелкните по кнопке

на панели инструментов. Выделенные ячейки должны объединиться, а текст расположить- ся по центру. Объедините также ячейки А1 и А2, В1 и В2, С1 и С2, F1 и F2, G1 и G2.

1. Заполните ячейки заголовка.

2. Выделите весь заголовок таблицы (блок ячеек A1:G2) и выполните команду Формат → Ячейки…. Появится диалоговое окно, перейдите на закладку Выравнивание. Выберите из списка выравнивание по вертикали и горизонтали – по центру. Чтобы длинные надписи раз-

мещались в несколько строк равномерно по ячейке, стоит поставить галочку в поле переносить по словам.

1. Установите нужную ширину столбцов. Примените для заголовка полужирный стиль начер- тания (при помощи кнопок на панели инструментов).

2. Заполните данными ячейки с номером (автозаполнением), фамилией сотрудников, назначьте им оклад (фамилии и оклад можно писать произвольные).

3. Известно, что северная надбавка 50 %, а районная 20 %. Поэтому в ячейку D3 нужно ввести формулу =С3*0,5. В ячейку Е3 введите формулу =С3*0,2 и скопируйте эти формулы вниз для остальных автозаполнением.

4. Для вычисления подоходного налога в ячейку F3 введите формулу =(C3+D3+E3)*0,13. Ско- пируйте ее автозаполнением для всех остальных.

5. Сумма к выдаче складывается из суммы оклада и надбавок с вычетом подоходного налога, поэтому в ячейку G3 вводим соответствующую формулу.

6. Для ячеек с ценой (сумма на руки) установите денежный формат. Для этого выделите эти ячейки и выполните команду Формат → Ячейки..., на закладке Число выберите формат Де- нежный, Обозначение – р. (рубли).

7. Теперь построим диаграмму. Выделите столбец с фамилиями (выделять нужно только фа- милии, т. е. В3:В7) и при нажатой клавише Ctrl столбец с итоговыми суммами (только суммы, т. е. G3:G7). Запустите Мастер диаграмм (в меню Вставка → Диаграмма). Выберите круго- вую диаграмму. Нажмите [Далее], потом еще раз [Далее] Внимательно изучите диалоговое ок- но (шаг 3 из 4).

8. На закладке Заголовок определите название диаграммы – Сумма к выдаче.

9. На закладке Легенда выберите размещение легенды снизу.

10. На закладке Подписи данных выберите Значение и установите переключатель Ключ леген- ды. Попробуйте объяснить, для чего все это делается. У вас должна получиться точно такая же диаграмма.

11. Построенную диаграмму можно многократно модифицировать. Если щелкнуть в любом месте диаграммы правой кнопкой, то можно вызвать меню, содержащее настройки отдельных элементов диаграммы.

12. Диаграмма непосредственно свя- зана с данными из таблицы. Убеди- тесь в этом: попробуйте одному че-

ловеку в таблице изменить оклад. Сделайте его оклад равным 10000. Посмотрите, как изменит- ся при этом диаграмма.

1. Как осуществляется запуск Excel и выход из него (указать все известные способы)?

2. Как называется документ, создаваемый программой MS Excel?

3. Сколько листов по умолчанию содержит созданная рабочая книга?

4. Каково максимально допустимое количество рабочих листов в рабочей книге?

5. Как по умолчанию располагаются в ячейке вводимые текстовые данные?

6. Что отобразится на экране при вводе слова «компьютеризация» в ячейку шириной

9 символов, при условии, что соседняя клетка справа занята?

1. Как по умолчанию располагаются в ячейке вводимые числовые данные?

2. Как по умолчанию располагаются в ячейке вводимые текстовые данные?

3. Какой символ является разделитель целой и дробной части в числе в Excel?

4. Какой символ является разделителем в группе ячеек?

5. В Excel иногда при вводе числа ячейка будет выглядеть так: #####. Как исправить поло- жение?

6. Как провести графический анализ данных?

7. Какие виды диаграмм можно использовать в Excel?

8. Иногда после создания диаграммы числовые значения требуется изменить. Как обновить такую диаграмму?

9. Что такое список в Excel?

10. Что такое сортировка списков.

11. Как сформировать критерии поиска данных.

12. Назовите последовательность данных при использовании усиленного фильтра.

13. Как построить форму?

14. Что такое критерии поиска?

15. Для каких целей применяются электронные таблицы?

16. Возможности Excel.

17. Какие электронные таблицы вы знаете?

18. Основные термины прикладной программы Excel.

19. Как загрузить таблицу с диска или записать таблицу на диск?

20. Как напечатать электронную таблицу на принтере? В каком виде может быть распечата- на таблица?

21. Что такое содержимое ячейки, значение содержимого ячейки, формат ячейки и ее адрес?

22. Какие операции и функции используют при написании формул в ячейках электронных таблиц?

23. Данные каких типов могут быть записаны в ячейку?

24. Какие значения может принимать содержимое ячейки?

25. Как изменить размер ячейки?

26. Как записываются абсолютные и относительные адреса ячеек?

27. Когда необходимо использовать абсолютные адреса ячеек?

28. Основные команды системы.

29. Форматы файлов. Вывод на печать.

30. Для чего служат Поиск решения и Подбор параметра в Excel?

Библиографический список

1. Безручко, В. Т. Практикум по курсу «Информатика». Работа в Windows, Word, Excel [Текст] : учеб. пособие / В. Т. Безручко. – М. : Финансы и статистика, 2004. – 272 с.

2. Симонович, С. В. Информатика: базовый курс [Текст] : учеб. для вузов / С. В. Симонович. –

Изд. 2-е. – СПб. : Питер, 2005. – 640 с.

1. Острейковский, В. А. Информатика [Текст] : учеб. для студ. техн. спец. вузов / В. А. Ост- рейковский. – 3-е изд., стер. – М. : Высш. шк., 2005. – 511 с.

2. Соболь, Б. В. Информатика [Текст] : учебник / Б. В. Соболь, А. Б. Галин, Ю. В. Панов и др. –

Ростов н/Д : Феникс, 2005. – 448 с.

1. Степанов, А. Н. Информатика [Текст] : учеб. пособие для студ. вузов, обучающихся по соц.- экон. спец. / А. Н. Степанов. – 4-е изд. – СПб. : Питер, 2006. – 684 с.