1. Откройте табличный процессор Microsoft Excel 2007 и создайте рабочую книгу с именем Лабораторная работа №1 .

2. Необходимо создать таблицу расчета заработной платы сотрудников предприятия.

3. Для упрощения ввода данных в таблицу создайте раскрывающийся список (рис. 22), содержащий ФИО сотрудников предприятия.

Рис. 22. Раскрывающийся список

4. Вставьте еще один лист в рабочую книгу Excel, используя ярлычок в строке Ярлычок листа .

5. На новом листе создайте список сотрудников (рис. 23).

Рис. 23. Список сотрудников предприятия

6. Для сортировки ФИО по алфавиту выполните команду: вкладка ленты Данные группа Сортировка и фильтр кнопка .

7. Выделите диапазон ячеек А1:А10 и щелкните поле Имя у левого края строки формул. Введите имя для ячеек, например Сотрудники . Нажмите клавишу Enter .

8. Чтобы запретить другим пользователям просмотр и изменение полученного списка, защитите и скройте лист, на котором он находится.

9. Правой кнопкой мыши щелкните по ярлычку листа. В контекстном меню выберите команду .

10. В диалоговом окне Защита листа (рис. 24) введите пароль для отключения защиты листа. В разделе Разрешить всем пользователям этого листа снимите флажки со всех элементов. Нажмите кнопку ОК .

Рис. 24. Диалоговое окно Защита листа

11. В диалоговом окне Подтверждение пароля введите пароль еще раз.

12. Правой кнопкой мыши щелкните по ярлычку листа и в контекстном меню выберите команду Скрыть .

13. Перейдите на Лист 1 и создайте таблицу Расчет заработной платы
(рис. 25). Столбец ФИО заполните, используя раскрывающийся список.

Рис. 25. Структура таблицы

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

15. На вкладке Данные в группе Работа с данными выберите команду Проверка данных .

16. В диалоговом окне Проверка данных укажите тип и источник данных (рис. 26).

17. Откройте вкладку Сообщение для ввода (рис. 27). Заполните пустые поля.

Рис. 26. Диалоговое окно Проверка данных

Рис. 27. Сообщение при вводе данных

18. Перейдите на вкладку Сообщение об ошибке (рис. 28). Заполните поля Вид , Заголовок и Сообщение .

Рис. 28. Сообщение при ошибке ввода данных

19. Для заголовков таблицы установите перенос текста (кнопка , расположенная на панели инструментов Выравнивание вкладки ленты Главная ).

20. Закрепите два первых столбца и строку заголовков таблицы. Для этого выделите диапазон ячеек C5:I20 и выполните команду: вкладка ленты Вид группа Окно кнопка .

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

вкладка ленты Главная панель инструментов Число в раскрывающемся списке форматов выберите Денежный формат .

22. Составим формулу для вычисления премии, которая составляет 20% от оклада. Любая формула начинается со знака = , поэтому переходим в ячейку F5 ивводим формулу =E5*20% (или =Е5*0,2 ).

23. С помощью маркера автозаполнения (черный крестик возле правого нижнего угла выделенной ячейки) скопируйте формулу в область
F6: F11 .

24. Между столбцами Премия и Подоходный налог вставьте столбец Итогоначислено , в котором посчитайте сумму Оклад+ Премия .

25. Заполните остальные столбцы таблицы, учитывая, что подоходный налог составляет 13% от начисленной суммы.

26. Посчитайте сумму к выдаче в долларах, для этого задайте текущий курс доллара, например 32, и в ячейку J5 введите формулу: =I5/$C$14 . Знак $ используется в формуле для того, чтобы при копировании с помощью маркера автозаполнения, адресация ячейки не изменялась.

27. Для ячеек, в которых содержатся денежные данные, установите соответствующий формат.

28. Используя функцию СУММ , посчитайте общую сумму подоходного налога. Для этого:

· установите курсор в ячейку Н12 ;

· поставьте знак =;

· в строке формул нажмите кнопку ;

· в появившемся диалоговом окне мастера функций (рис. 29) выберите категорию Математические , функцию СУММ ;

· в качестве аргумента функции СУММ выделите диапазон суммирования Н5:Н11 ;

· нажмите кнопку ОК.

29. Аналогичным образом посчитайте общую сумму к выдаче в долларах и общую сумму к выдаче в рублях.

Рис. 29. Мастер функций

30. Найдите среднюю (СРЗНАЧ ), минимальную (MИН ) и максимальную (MAКС ) заработные платы.

31. Используя условное форматирование, обозначьте красным цветом Суммы к выдаче , менее 5 500 руб. Выполните команду: вкладка ленты Главная группа Стили раскрывающийся список Условное форматирование Правила выделения ячеек .

32. Постройте диаграмму Заработная плата сотрудников предприятия
(рис. 30). Выделите одновременно столбцы Ф.И.О. и Сумма к выдаче (удерживая клавишу Сtrl), и на вкладке ленты Вставка на панели инструментов Диаграммы выберите вид Гистограмма .

33. Используя вкладку ленты Макет, вставьте подписи осей и название диаграммы.

Рис. 30. Пример оформления диаграммы

34. Постройте круговую диаграмму, показывающую соотношение между общей суммой к выдаче и суммарным подоходным налогом (рис. 31).

Рис. 31. Пример оформления круговой диаграммы

Контрольное задание 1. Использование формул и функций в расчетах.

Дано: а, в, с,h, l, m, x - любые числа.

Вычислить:

Результат выполнения:

V=1/3*ПИ()*B1*(B2*B2+B2*B3+B3*B3)

Контрольное задание 2. Использование относительных и абсолютных ссылок в формулах. Создание таблицы «Покупка товаров с предпраздничной скидкой».

Ответы на контрольные вопросы

1. Что такое формула в Excel? Какова её структура? Какие элементы может включать формула? Каковы правила ввода и редактирования формул в Excel?

Формулой в Excel называется последовательность символов, начинающаяся со знака равенства “=“. В эту последовательность символов могут входить постоянные значения, ссылки на ячейки, имена, функции или операторы.

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

3. Как можно копировать и перемещать формулы?

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

4. Как выполняется автозаполнение ячеек формулами?

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

5. Каким образом осуществляется редактирование формул?

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

1. Редактирование формулы в строке формул:

Выделите ячейку с формулой, подлежащей редактированию,

Щелкните мышкой, расположив курсор в строке формул.

2. Редактирование формулы непосредственно в ячейке:

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

Включить «режим редактирования»:

Выделить ячейку и нажать клавишу .

После завершения редактирования формулы «режим редактирования» в ячейке необходимо отключить – нажать клавишу или .

6. Что такое функция в Excel? Какова её структура?

Функции в Excel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами. Помимо встроенных функций вы можете использовать в вычислениях пользовательские функции, которые создаются при помощи средств Excel. Аргументы функции записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой “;”. Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Внутри скобок должны располагаться аргументы. Помните о том, что при записи функции должны присутствовать открывающая и закрывающая скобки, при этом не следует вставлять пробелы между названием функции и скобками.

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

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

Для удобства работы функции в Excel разбиты по категориям: функции управления базами данных и списками, функции даты и времени, DDE/Внешние функции, инженерные функции, финансовые, информационные, логические, функции просмотра и ссылок. Кроме того, присутствуют следующие категории функций: статистические, текстовые и математические.

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

С помощью функций даты и времени можно решить практически любые задачи, связанные с учетом даты или времени (например, определить возраст, вычислить стаж работы, определить число рабочих дней на любом промежутке времени).

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

8. Чем отличается формулы от функций? Как в диалоге сформировать текст функции?

Функции могут входить в состав формул.

В диалоге два окна-списка и несколько кнопок. Для удобства встроенные функции разбиты по категориям. В окне под названием "Категория" находится список категорий функций. А в окне с названием "Функция" представлен в алфавитном порядке список функций, выделенной категории.

Ниже списка функций даётся очень сжатая справка о выделенной функции. Но прочитать эту справку можно только при помощи JAWS-курсора. Если же нажать на кнопку помощи, то откроется новое диалоговое окно с подробной справкой о выделенной функции Excel.

А нажатие кнопки "ОК" активизирует второй шаг мастера функций - ввод входных параметров или аргументов функции. Обычно здесь нужно перечислить адреса ячеек и диапазоны, которые участвуют в вычислениях данной функции. После ввода очередного аргумента нужно нажимать табуляцию. В конце нужно нажать "Enter", чтобы активизировать кнопку "Ок". В диалоге ввода параметров также присутствует справочная информация, доступная только для JAWS-курсора.

После задания входных параметров встроенной функции Excel сформирует текст формулы и поместит его в ту ячейку, где находился курсор при вызове мастера функций.

9. Как пользоваться Мастером функций?

Чтобы найти нужную нам встроенную функцию Excel, необходимо войти в меню "Вставка" и активировать пункт "Функция". Раскроется диалог под названием "Мастер функций".

Выводы о проделанной лабораторной работе

В процессе выполнения лабораторной работы освоена методика работы с формулами и функциями в табличном процессоре Microsoft Office Excel.

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

Тема : Функции Excel

Цель :

Познакомиться с различными классами функций;

Научиться использовать Мастер функций;

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

Функции Excel

Функция – это зависимая переменная величина, значение которой вычисляется по определенным правилам на основании значений других величин – аргументов функции . Excel предлагает большой (несколько сотен) набор стандартных (встроенных) функций, которые можно использовать в формулах, например:

Функция - от латинского Functio – исполнение.

За именем функции в круглых скобках следует через точку с запятой список аргументов. Список аргументов может состоять из чисел, текста, логических величин (ИСТИНА или ЛОЖЬ), ссылок, формул, вложенных функций. Если формула начинается с функции, перед именем функции вводится знак «= ».

По характеру аргументов встроенные функции можно разделить на три типа:

С перечислением аргументов (максимум – 30 аргументов): СРЗНАЧ (А2:С23;Е6;200;3) – возвращает среднее значение аргументов

С фиксированными аргументами: СТЕПЕНЬ (6,23;4): возводит первый аргумент (6,24) в степень второго аргумента (4)

Без аргументов : СЕГОДНЯ (): возвращает текущую дату.

Ввод формул

Последовательность ввода функции в формулу:

Имя функции;

Открывающаяся круглая скобка;

Перечень аргументов через точку с запятой;

Закрывающаяся круглая скобка.

Ввод функции можно осуществить несколькими способами:

Функции и панель формул

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

Обязательный аргумент выделен полужирным шрифтом – без него функция не может выполнить обработку;

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

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

Панель формул можно перемещать по экрану, перетаскивая её мышью.

Вложенные функции

Результат вычисления функции может быть использован в качестве аргумента другой функции. Функция, используемая в качестве одного из аргументов другой функции, называется вложенной . Excel поддерживает до 7 уровней вложенности функций.

Например:

ЕСЛИ (А4>0;МАКС (А9:В19) ;0)

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

Специальная вставка

Содержимое ячейки можно представлять как совокупность четырёх слоёв информации: формула, значение, формат и примечание. Excel позволяет выполнять раздельное копирование каждого слоя. Информация помещается в буфер как обычно (команда Копировать ), а вставляется с помощью команды Правка \ Специальная вставка…

Для копирования форматов, также как и других приложениях Office , используется инструмент стандартной панели – Формат по образцу . (Практическая работа « Прогноз погоды » ).

задание:

При помощи функции заполнить блок А1:А5 случайными числами в диапазоне [-10,10];

В клетку В1 ввести формулу для вычисления целой части значений колонки А;

Скопируйте полученную формулу в блок В2:В5;

Эту же последовательность операций применить к функциям и блокам соответственно:

ABS (A) - С1: С5;

EXP (A) - D1:D5;

SQRT (A ) - E 1:E 5;

Вычисление остатка при делении на 2 – F 1:F 5;

Округление с -1 – H 1:H 5;

Округление с +1 – G 1:G 5

В клетку А7 написать формулу суммы элементов первой колонки (А1:А5)

В клетке В7 – среднее арифметическое по (В1:В5)

С7 – максимальный элемент из (С1:С6)

D 7 – минимальный элемент (D 1:D 6)

E 7 – количество элементов (Е1:Е6)

F 7 – дисперсию значений (F 1:F 6)

Диапазон I 1:I 6 заполнить значениями тригонометрических функций:

I1 - PI

I2 – Sin (A1)

I3 – Cos (A2)

I4 – Tan (A3)

I5 – Atan (A4)

I6 – Asin (A5)

В строке 10 вести заголовки полей:

Фамилия\Имя Дата рождения Количество дней

Подкорректируйте ширину колонок и произведите отцентровку заголовков;

В блоке А12:А17 ввести фамилии или имена ваших друзей, знакомых. В блоке В12:В17 – их даты рождения. Дату вводить в европейском формате;

В клетке С9 ввести текущую дату;

В клетку С12 формулу для расчёта количества дней, прожитых человеком для текущей даты;

Между колонками Дата рождения и Количество дней вставить колонку День недели;

В первую клетку колонки вписать функцию вычисления дня недели по дате рождения. Скопировать полученную формулу во все клетки колонки;

В колонке F напротив каждой фамилии написать «Молодой» или «Старый», используя логическую функцию ЕСЛИ. Функцию введите, используя, Мастер функций (ЕСЛИ Количество дней<15000, то «Молодой», иначе «Старый»);

Сохраните полученную таблицу на диске в личной папке (Наименование группы).

Контрольные вопросы:

Способы ввода формул в ячейки;

Панель формул;

Обязательный и необязательный аргументы в формулах;

Процедура выполнения вложенных функций в Microsoft Excel ;

Алгоритм специальной вставки в ячейки.

18.1 Теоретические сведения

Одним из основных назначений Microsoft Excel является выполнение различных вычислений с помощью формул и встроенных функций. Формула начинается со знака равенства и представляет собой выражение, которое может состоять из чисел, ссылок (адресов ячеек) или функций, объединенных знаками арифметических действий. В формулах Excel применяются следующие арифметические действия: возведение в степень (^); умножение (*); деление (/); сложение (+); вычитание (-).

Функция - это готовая формула, которая состоит из имени функции и аргумента или нескольких аргументов, например СУММ(A3;C8). Имя функции определяет действия, а аргументы задают значения или ячейки и указываются в круглых скобках. Причем между именем функции и круглыми скобками пробелы отсутствуют. Для вычислений с помощью функций используется Мастер функций – шаг 1 из 2, который вызывается щелчком на пиктограмме fx в строке ввода формул или выполнением команды Вставка/Формула. Перед вызовом функции необходимо установить курсор в ту ячейку, в которую необходимо ввести функцию.

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

С помощью Excel можно создавать сложные диаграммы для данных электронных таблиц. Диаграммы строятся с помощью мастера диаграмм, который можно вызвать, щелкнув на кнопке Мастер диаграмм на стандартной панели инструментов, или выполнив команду Вставка/Диаграмма. Диаграмму можно создать и за один шаг, щелкнув на клавише F11. Перед вызовом мастера диаграмм необходимо выделить электронную таблицу или часть таблицы, для которой требуется создать диаграмму.

Например, для построения графика функции Y = 2sin 3 (5пx) + 7cos(3 пx) 2

в Microsoft Excel, ее необходимо представить (с помощью арифметических операций, используемых в Excel) в виде удобном для выполнения вычислений. После преобразования функции, она будет иметь вид: Y = 2*(sin(5*ПИ()*x)^3 + 7*cos(3*ПИ()*x)^2.

Затем в ячейки, которые определены для аргумента "x", надо ввести числа с определенным шагом (например, от -2 до +2 с шагом 0.1), а в ячейки, предназначенные для размещения функции Y, необходимо ввести формулу = 2*(sin(5*ПИ()*x)^3 + 7*cos(3*ПИ()*x)^2. При этом в формулу вместо аргумента "x" надо ввести ссылки на ячейки, в которых размещены их значения. После выполнения вычислений необходимо выделить результаты вычислений и вызвать мастер построения диаграмм одним из способов, а затем построить график функции за четыре шага, используя мастер диаграмм.

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

18.2 Цель работы

Приобретение практических навыков работы с формулами, функциями и диаграммами в Microsoft Excel.

18.3 Постановка задачи

Вычислите функции:

• Y=2sin(пx)cos 5 (2пx) + sin(5 пx) 2 , где п - число ПИ = 3, 14;
• Z=cos(2пx) sin 3 (пx) - 3cos(4пx);
• диапазон изменения аргумента X нач =-3, X кон =3, шаг- 0,1.

По результатам выполненных вычислений (функций Y и Z) постройте в единой системе координат графики функций. Графики функций расположите на отдельном листе и введите Подписи оси Х.

18.4 Пошаговое выполнение работы

18.4.1 Включите ПК

Нажмите кнопку Power на системном блоке ПК.

18.4.2 Запустите Microsoft Excel

18.4.2.1 Запустите Microsoft Excel, используя команду Главного меню.

После полной загрузки ОС запустите Microsoft Excel, щелкнув на кнопке Пуск и выбрав в главном меню команду Программы/Microsoft Office, Microsoft Office Excel 2003 . В результате откроется окно приложения Microsoft Excel , в котором отображается пустая рабочая книга "Книга 1" с тремя рабочими листами.

18.4.2.2 Сохраните рабочую книгу Excel.

Для сохранения рабочей книги в Excel выполните команду Файл/Сохранить, в окне диалога Сохранение документа введите имя файла: Графики функций. Щелкните на кнопке ОК, сохранив рабочую книгу Excel в папку Мои документы.

18.4.3 Вычисление функций

18.4.3.1 Назначьте столбцам A, B, C имена (Аргумент X, Функция Y, Функция Z).

Выполните следующее:

• в ячейку A1 введите имя Аргумент X;
• в ячейку B1 введите имя Функция Y;
• в ячейку C1 введите имя Функция Z.

18.4.3.2 Заполнение столбца A значениями аргумента X.

Выполните следующее:

• в ячейку A2 введите начальное значение (равное -3);
• выделите ячейку A2 и выполните команду Правка/Заполнить/Прогрессия... ;
• заполните поля: Расположение - по столбцам; Шаг - 0,1; Тип - арифметическая Предельное значение - конечное значение 3;

18.4.3.3 Введение в столбец B формулы для расчета функции Y.

Введите в столбец B формулу для расчета функции Y, используя Мастер функций или вводя ее с клавиатуры:

18.4.3.4 Введение в столбец C формулы для расчета функции Z.

Введите в столбец C формулу для расчета функции Z, используя Мастер функций или вводя ее с клавиатуры. Ввод формулы для расчета функции Z аналогичен вводу формулы для расчета функции Y, описанному в п. 18.4.3.3. Аргументом x для формулы, помещенной в ячейку C2, является адрес ячейки A2.

18.4.3.5 Заполнение формулами остальных ячеек столбцов B и C.

Для заполнения формулами ячеек столбцов B и C целесообразно использовать способ автозаполнения:

• выделите одновременно ячейки B2 и C2;
• установите указатель мыши на маркер заполнения и, удерживая левую кнопку мыши, протяните выделение вниз на остальные ячейки. Ячейки будут заполнены формулами.

18.4.4 Построение графиков

18.4.4.1 По результатам вычислений постройте график функции Y.

Для построения графика функции Y на отдельном листе выполните следующее:

• выделите результаты вычислений вместе с заголовком Функция Y;
• выполните команду Вставка/Диаграмма;
• выбирая на каждом из четырех шагов требуемые установки, постройте график на отдельном листе.

18.4.4.2 По результатам вычислений постройте график функции Z на той же диаграмме что и график функции Y.

Для построения графика функции Z на той же диаграмме что и график функции Y выполните:

• выделите результаты вычислений вместе с заголовком Функция Z;
• выполните команду Правка/Копировать;
• откройте лист с графиком функции Y;
• выделите диаграмму, щелкнув на ней левой клавишей мыши;
• вставьте данные из буфера обмена, выполнив команду Правка/Вставить.

18.4.5 Введите Подписи оси X на диаграмме

Для ввода подписи оси X на диаграмме выполните следующее:

• выделите диаграмму;
• выполните команду меню Диаграмма/Исходные данные...;
• откройте вкладку Ряд;
• щелкните на кнопке свернуть, расположенную справа текстового окна Подписи оси X;
• перейдите на лист с функциями, выделите значения аргумента X, кроме заголовка и нажмите клавишу Enter;
• чтобы закрыть окно Исходные данные щелкните на кнопке ОК.

Сохраните изменения в файле.

18.4.6 Завершение работы

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