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

Тема : Функции 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 ;

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

Цель работы

· научиться работать с относительными и абсолютными ссылками

· научиться передавать данные из MS Excel в MS Word

· уметь составлять формулы и работать с различными функциями MS Excel

· овладеть различными приемами форматирования текста и данными в таблицах MS Excel

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

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

Задание 1. Создание относительной и абсолютной ссылки

1. Создайте документ MS Excel и сохраните его как Лабораторная_работа_2.xcls. Назовите первый лист "Ссылки". Введите данные, как показано на рис. 1.

Примечания: Чтобы назвать лист, необходимо мышкой выделить его текущее наименование, нажать правой кнопкой мыши и выбрать Переименовать. В графе В2, чтобы получилось 100р., необходимо набрать чисто 100 и выбрать денежный формат (правая кнопка мыши - формат ячеек...).

2. Посчитайте зарплату Иванова при помощи создания формулы, содержащей относительную ссылку. Для этого выделите ячейку С4 и перейдите в строку формул. Введите формулу =В2*В4 (рис. 2) и нажмите Enter .

Рис. 2 Введенное выражение в Строку формул

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

3. Скопируйте формулу в ячейки С5 и С6, потянув за маркер заполнения. При этом тиражирование формулы данного примера с относительными ссылками в ячейке С5 появится сообщение об ошибке (№ЗНАЧ!), так как изменился относительный адрес ячейки В2, и в ячейку С5 скопировалась формула = В3*В5.

Рис. 3. Сообщение об ошибке (#ЗНАЧ!) в ячейке С5.

4. Задайте абсолютную ссылку на ячейку В2. Для это выделите ячейку С4. Поставьте курсор в строке формул на В2 и нажмите клавишу F4, которая осуществляет преобразование относительной ссылки в абсолютную и наоборот (Рис. 4). Знак ($ ) появится как перед ссылкой на столбец, так и перед ссылкой на строку. Формула в ячейке С4 будет иметь вид = $B$2*B4.

5. Последовательно нажмите F4, которая будет добавлять или убирать знак $ перед номером столбца или строки. (B$2 или $B2 - так называемые смешанные ссылки).

7. Скопируйте формулу в ячейки С5 и С6, потянув за маркер заполнения. В итоге должна получиться таблица с корректно отраженными данными (рис. 5).

Выслуга лет" href="/text/category/visluga_let/" rel="bookmark">выслугу лет , используя данные, сформированные в Excel, используя для связи данных Специальную вставку.

1. Перейдите на новый лист и назовите его "Специальная вставка" и введите данные, представленные на рисунке 6. Выделите заполненные ячейки и скопируйте в буфер обмена.

Примечание: для того, чтобы в ячейке слова не вылезали за границы и выстраивались одно под другим, нужно выделить ячейку, нажать правой кнопкой мыши, выбрать Формат яеек..., перейти на вкладку Выравнивание и поставить галочку напротив "переносить по словам".

Рис. 6. Данные на листе "Специальная вставка".

2. Создайте текстовый файл в MS Word, сохраните его как Приказ. docs . Оформите его произвольно так, как, по вашему мнению может выглядеть приказ о назначении заработной платы сотрудникам, оставив пустое место там, где по логике можно вставить табличку с посчитанной зарплатой.

3. Поставьте курсор в место вставки таблицы. Выполните команду, представленную на рисунке ниже:

Рис. 7. Команда Специальная вставка

Появится диалоговое окно Специальная вставка (рис.8)

Microsoft" href="/text/category/microsoft/" rel="bookmark">Microsoft Excel (объект).

5. Отметьте переключатель Связать и нажмите кнопку ОК.

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

7. Вернитесь в документ Excel и измените ячейки столбца "премия" на формат "Денежный" (выделите диапазон ячеек Е2:Е10, правой кнопкой мыши выберите Формат ячеек...) (рис. 9). На вкладке Число выберите Денежный. Нажмите ОК.

https://pandia.ru/text/78/392/images/image010_15.jpg" width="497" height="358 src=">

Рис. 10. Данные столбца Премия отображены в денежном формате.

8. Перейдите в документ Word. Выделите объект таблицы. Вызовите правой кнопкой мыши конкретное меню и выберите из перечисленного строку Обновить связь (рис. 11).

https://pandia.ru/text/78/392/images/image012_13.jpg" width="627" height="396 src=">

Рис. 12. Установка переключателя в диалоговом окне Специальная вставка

Задание 3. Используйте функцию ВПР для автоматической подстановки данных из одной таблицы в другую

1. Перейдите в новый лист и переименуйте его в ВПР. Создайте две таблицы, как это показано на рис. 13.

Рис. 13 Данные листа ВПР

2. Перенесите суммы из таблицы Данные возврата из столбца Возвращено (в руб.) в таблицу Возврат долга автоматически, ориентируясь на ФИО с тем, чтобы можно было потом посчитать Остаток задолженности. Для этого дайте диапазону ячеек Данные возврата собственное имя, выделив все, кроме "шапки" (G2:H22) и нажав затем правой кнопкой мыши и из появившегося списка выбрав Имя диапазона.

3. В открывшемся диалоговом окне Создание имени введите имя (без пробелов) остаток . В дальнейшем используйте это имя для ссылки на таблицу Данные возврата.

Рис. 14. Диалоговое окно Создание имени

4. Выделите ячейку D3, куда будет введена формула и откройте Мастер функции, нажав на fx возле строки формул (рис. 15).

Рис. 15 Вызов Мастера функции

Рис. 16 Диалоговое окно Мастер функций

5. В появившемся диалоговом окне ввода аргументов для функции (рис. 17):

Рис. 17. Диалоговое окно Аргументы функции

Заполните их по очереди:

· Искомое_значение - ячейки В3

· Номер­_столбца - порядковый номер (не буква!) столбца, из которого нужно брать значение суммы - 2

· Интервальный_просмотр - введите значение ЛОЖЬ, это означает, что поиск только точного соответствия.

6. Нажмите ОК и скопируйте введенную функцию на весь столбец.

7. Введите в ячейку Е3 формулу для подсчета Остатка задолженности (=С3- D 3). Скопируйте введенную формулу на весь столбец, чтобы автоматически подсчитать Остаток задолженности. (рис. 18).

https://pandia.ru/text/78/392/images/image019_7.jpg" width="633" height="491">

Рис. 19 Диалоговое окно Мастер текстов

4. На первом шаге Мастера выберите Формат исходных данных, т. е. символ, который отделяет друг от друга содержимое будущих отдельных столбцов (с разделителями). Нажмите Далее.

5. На втором шаге Мастера необходимо указать, какой именно символ является разделителем. Отметьте пробел (рис. 20). Нажмите Далее.

Рис. 20 Диалоговое окно Мастер текстов. Установка разделителей

6. На третьем шаге для каждого из получившихся столбцов, выделяя их предварительно в окне Мастера, выберите формат Текстовый (рис. 21). Нажмите Готово, утвердительно ответив на вопрос о замене конечных ячеек, который выдаст Excel.

В результате текст будет разделен на 3 столбца, что и требовалось в задании (рис. 22).

Рис. 21. Диалоговое окно Мастер текстов. Установка формата данных столбца

­

Рис. 22. Результат разделения по столбцам.

Задание 5 Автоматически склейте текст из нескольких ячеек, используя формулу и знак &.

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

2. Введите в ячейки А1, В1, С1 - , соответственно.

3. Выделите ячейку D1. В строку формул введите следующую формулу: = A 1&" "& B 1&" "& C 1 , после чего нажмите Enter.

В результате, в ячейке D1 объединятся фамилия, имя и отчество с необходимыми пробелами (рис. 23).

Рис. 23. Результат объединения ФИО в одну ячейку.

Задание 6. Автоматически склейте текст из нескольких ячеек с помощью функции Извлечение из текста первых букв ЛЕВСИМВ.

1. Создайте новый лист. Введите в ячейки А1, В1, С1 - , соответственно.

2. Выделите ячейку D1. В строку формул введите следующую формулу: = A 1&" "&ЛЕВСИМВ(В1;1)&"."&ЛЕВСИМВ(С1;1)&"."

3 Нажмите Enter (рис. 24).

Рис. 24 Результат склеивания текста, находящегося в разных ячейках.

Задание 7. Транспонируйте Данные таблицы при помощи формулы массива и функции ТРАНСП

1. Создайте новый лист и назовите его ТРАНСП. Введите данные, как показано на рис. 25.

Рис. 25 Данные листа ТРАНСП

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

3. Введите в строку формул функцию транспонирования = ТРАНСП

4. В качестве аргумента функции выделите ваш массив ячеек А1:В10 и закройте скобку.

Обратите внимание, что Вы имеете дело с массивом, и поэтому для ввода формулы, нажать нужно не просто Enter !!!

5. Нажмите Ctrl + Shift + Enter . В строке формул Excel автоматически заключил созданную Вами формулу в фигурные скобки. Получился "перевернутый массив" в качестве результата (рис. 26).

Рис. 26. Результат транспонирования данных

Задание 8. Выделите в таблице данные, повторяющиеся более 1 раза, используя Условное форматирование

1. Создайте новый лист и назовите его Условное форматирование.

2. Скопируйте в него ячейки В3:В22 листа ВПР.

3. Выделите весь список. Выберите в меню Главное - Условное форматирование - Создать правило.

4. Выберите Тип правила - Использовать формулу для определения форматируемых ячеек . В соответствующей строке введите формулу:

СЧЁТЕСЛИ($A:$A;A2)>1

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

5. Для выбора цвета выделения в окне Условное форматирование нажмите кнопку Формат... и перейдите на вкладу Вид . Выберите желтый цвет и нажмите ОК.

Рис. 27. Диалоговое окно Условное форматирование

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

Задание 9. Создайте отчет, используя Сводную таблицу

1. Создайте новый лист и назовите его Сводная таблица . Заполните ее так, как показано на рис. 28.

Рис. 28. Данные листа Сводная таблица

2. Выделите активную ячейку в таблице с данными (любое поле списка) и нажмите в меню Вставка - Сводная таблица - Сводная таблица

3. В появившемся окне заполните все так, как показано на рис. 29.

Рис. 29 Мастер сводных таблиц

4. Нажмите кнопку ОК. Появится следующее окно:

Гистограмма" href="/text/category/gistogramma/" rel="bookmark">гистограммную диаграммы.

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. Формулы и функции MS Excel

Подготовить соответствующие ведомости, воспользовавшись приведенными рекомендациями.

Задание 1 . Сформировать объявления о продаже квартир согласно образцу (рис. 1).

Рис. 1. Объявления о продаже квартир

Сгруппировать имеющиеся данные по квартирам в виде списка (рис. 2).

Рис. 2. Данные о квартирах, выставленных на продажу

В ячейку G2 ввести формулу:

А2&" кв., по "&В2&", площадь: "SD2&", "&Е2&"этаж, "&ТЕКСТ(С2;"# ##0р.")&", "&ECJIИ(F2="+"; "телефон"; "телефона нет")

Объясните данную формулу, воспользовавшись справкой.

Для диапазона G3:G5 воспользоваться маркером автозаполнения либо скопировать данную формулу.

При необходимости отформатировать полученные объявления, используя команду Формат | Автоформат.

Задание 2. Сформировать и заполнить ведомость переоценки основных средств производства по форме, приведенной на рис. 3.

В ячейку А1 ввести название ведомости.

В ячейки A4:F4 ввести названия полей ведомости: Наименование объекта, Балансовая стоимость (БС), Износ объекта (ИО), Остаточная стоимость (ОС), Восстановительная полная стоимость (ВПС), Восстановительная остаточная стоимость (вое). Поле Наименование объекта включает следующие строки: Отдел менеджмента и маркетинга, Отдел транспортировок, Сборочный цех, Отделочный цех, Склад № 1, Склад № 2, Склад № 3, Итого.

Формулы для расчетов:

ОС = БС - ИО

ВПС = БС * К

ВОС = ОС * К

где к - коэффициент, равный:

3,3 - если БС меньше либо равен 650 млн руб.;

4,2 - если БС больше 650 млн руб., но меньше 1000 млн руб.;

5,1 - если БС равен 1000 млн руб. или более.

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

для ячейки D5 : =В5-С5

для ячейки Е5 : =В5*ЕСЛИ(В5<=650;3,3;ЕСЛИ(И(В5>б50;В5<1000);4,2;5,1))

для ячейки F5 : =D5*ЕСЛИ(В5<=650;3.3;ЕСЛИ(И(В5>650;В5<1000);4,2;5.1))

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

СУММ(В5:В11), либо следует выделить диапазон ячеек B12:F12 и воспользоваться возможностью автосуммирования (нажать кнопку Автосумма на панели инструментов).

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

Рис. 3. Ведомость переоценки основных средств производства

Задание 3 . Сформировать и заполнить отчетную ведомость работы сети компьютерных клубов по форме, приведеной на рис. 4.

Рис. 4. Ведомость работы сети компьютерных клубов

В ячейку А1 ввести название ведомости.

В ячейки АЗ:НЗ ввести названия полей ведомости: клуб, Январь, Февраль, Март, Суммарная выручка, Место, Средняя выручка, процент. Поле Клуб включает следующие строки: Альтаир, Грувит, Полигон, Гелакс, Звезда, Хексен, Антей, Арсенал, Арена, Блиндаж, Итого.

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

Формулы для расчета

Отформатировать полученную ведомость.

Задание 4. Сформировать на рабочем листе ведомость "Расчет заработной платы работников научно-проектного отдела "Альфа"(рис. 5).

Рис. 5 Ведомость по расчету заработной платы

В ячейку А2 поместить название ведомости - Расчет заработной платы работников научно-проектного отдела "Альфа", отцентрировать по левому краю (например, командой Формат | Ячейки | вкладка Выравнивание либо соответствующей кнопкой По правому краю (на панели инструментов).

В ячейки АЗ:КЗ ввести названия полей ведомости: № пп, Фамилия И.О., Должность, Тарифная ставка, Стаж, к, Надбавка за стаж, Итого, Процент налога, Удержать, Выплата.

шапке ведомости - к каждому столбцу - создать скрытые примечания. Примечания создаются командой Вставка | Примечание (также можно использовать панель инструментов Рецензирование, которая добавляет командой Вид | Панели инструментов | Рецензирование):

№ пп - номер работника отдела;

Фамилия и.о. - заносятся все фамилии работающих в научно-проектном отделе;

Должность - занимаемая должность на момент заполнения ведомости;

тарифная ставка- денежный эквивалент занимаемой должности;

Стаж- вносится целое число отработанных лет на момент заполнения ведомости;

к- коэффициент за стаж работы;

надбавка за стаж - денежный эквивалент за стаж работы;

итого- начисление заработанной платы с учетом тарифной ставки и стажа работы;

процент налога - определяет процент отчислений в бюджет;

Удержать - денежный эквивалент отчислений в бюджет;

Выплата - сумма, предназначенная к выдаче.

При расчетах в ведомости учитывать следующее:

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

Коэффициент к присваивается из следующего расчета:

0,1 - отработано до 5 лет включительно, 0,2- от 5 до 10 лет включительно, 0,25 - от 10 до 15 лет включительно, 0,3 - свыше 15 лет. Формула для ячейкиF4 :

ЕСЛИ(Е4<=5;0,1;ЕСЛИ(И(Е4>5;Е4<=10);0,2;ЕСЛИ(И(Е4>10; Е4<=15);0,25;0,3)))

надбавка за стаж - денежный эквивалент за стаж работы. Формула для ячейки G4 :

G4 :

(вводится командой Формат | Ячейки | вкладка Число, из списка Числовые форматы выбрать Все форматы и в поле Тип ввести указанный формат).

итого - тарифная ставка с учетом стажа. Формула для ячейки Н4 :

Пользовательский формат числа для ячейки Н4 :

Процент налога - учитывает, что: 2% - начисление (по итого) составляет до 7000 р. включительно, 10%- более 7000 р. до 10 000 р. включительно, 20%- более 10 000 р. до 25 000 р. включительно, 35%- превышающие 25 000 р. Формула для ячейки I4 :

ЕСЛИ(Н4<=7000;0,02;ЕСЛИ(И(Н4>7000;Н4<=10000);0,1;ЕСЛИ(И(Н4>10000;Н4<=25000);0,2;0,35)))

Формат числа для ячейки I4 - Процентный.

Удержать - денежный эквивалент налогов. Формула для ячейки J4 :

Пользовательский формат числа для ячейки J4 :

Выплата - сумма К выдаче: Итого без Удержать.

Требования к столбцу стаж:

Создать пользовательский формат данных, учитывающий стаж работы: до 5 лет - данные представлены желтым цветом, от 5 до 10 - синим, от 10 до 15 - зеленым, свыше 15 - красным.

Воспользоваться командой Формат | Ячейки и ввести пользовательский формат для ячейкиЕ4 :

[Красный]# ##0;

а также использовать команду Формат | Условное форматирование.

В случае ввода отрицательного числа лет должно появляться соответствующее окно. Для проверки

ввода чисел использовать команду Данные | Проверка| вкладка Сообщение об ошибке.

Для поля Тарифная ставка - вывести постоянное сообщение: Тарифная ставка. Будьте внимательны при вводе тарифной ставки для получения которого использовать командуДанные | Проверка | вкладка Сообщение для ввода.

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

# ##0,00р.;[Красный]"Тарифная ставка не может быть

отрицательной!"

Решение

Для основных платежей по займу, который погашается равными платежами в конце или начале каждого расчетного периода, в MS Excel XP используется функция:

ОСПЛТ (Ставка, Период, Кпер, Пс, Бс)

(в более ранних версиях MS Excel эта функция называлась ОСНПЛАТ) В нашем случае функция СППЛТ имеет вид: ОСПЛТ(12%, 4, 5, 1000000000)

Ввод данных и расчеты производятся в соответствии с рис. 4.4.

Рис. 11. Расчет основных платежей по займу

В ячейкуВ8 вводится формула:

ОСПЛТ (В5;В6;В4;ВЗ)

Решение

Для вычисления величины постоянной периодической выплаты ренты (например, регулярных платежей по займу) при постоянной процентной ставке используется функция ПЛТ (в ранних версиях - функция ППЛАТ):

ПЛТ(Ставка; Кпер; Бс; Пс; Тип)

В нашем случае функция ПЛТ имеет вид:

ПЛТ(10%/12; 20*12; -(350000* (1-25%)))- ежемесячные выплаты;

ПЛТ(10%; 20; -(350000* (1-25%)))-ежегодные выплаты.

Решение задачи приведено на рис. 12 и 13.

Рис. 12. Расчет ипотечной ссуды

Рис. 13. Формулы для расчета ипотечной суды

Задание 3. Определить, какая сумма окажется на счете, если 52 000 руб. положены на 20 лет под 11% годовых. Проценты начисляются ежемесячно.

Решение

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

БС(Ставка; Кпер; Плт; Пс; Тип)

Для нашей задачи функция БС примет вид:

БС(11%/12;20*12; ,-52000)

Решение задачи приведено на рис. 14, а формула для ячейки В26 :

БС(B22/B23;B24*B23;;-B21)

Рис. 14. Расчет будущей стоимости вклада

Задание 4 . Облигация номиналом 200 000 руб. выпущена на 7 лет. Предусматривается следующий порядок начисления процентов: в первый год- 11%, последующие три года- по 16%, в оставшиеся

Решение

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

БЗРАСПИС(Первичное; План)

Для нашей задачи функция принимает вид:

БЗРАСПИС(200000; {11%; 16%; 16%; 16%; 20%; 20%; 20%))

Решение приведено на рис. 15, а формула для расчета в ячейке В42 :

БЗРАСПИС(В30;В34:В40)

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

Рис. 15 Расчет наращенной стоимости облигации по сложной процентной ставке

Решение

Расчет внутренней скорости оборота инвестиций производится с помощью функции ВСД (в ранних версиях - ВНДОХ): ВСД (Значения; Предположения)

Рис. 16 Рабочий лист для определения первоначальных затрат по проекту

Ввод исходных данных производится в соответствии с рис. 16. Первоначально для расчета величина затрат на проект выбирается произвольно (ячейку для этой суммы можно оставить даже пустой) и производятся вычисления.

В ячейку В12 вводится формула =ВСД(В4:В9).

примера представлен на рис. 18.

Рис. 17 Окно Подбор параметра

Рис. 18 Рассчитанная величина первоначальных затрат по проекту

Пример расчета эффективности неравномерных капиталовложений

Задание 2. Вас просят дать в долг 15 000 руб. и обещают вернуть через год 3000 руб., через два - 5000 руб., через три - 9000 руб. При какой процентной ставке эта сделка выгодна?

Решение

При решении этой задачи следует использовать функцию ЧПС и средство Подбор параметра:

ЧПС(Ставка; значение 1; значение 2; . . .)

Ввод исходных данных производится в соответствии с рис. 19. Первоначально для расчета выбирается произвольный процент годовой учетной ставки (ячейку с этой величиной можно оставить даже пустой) и производятся вычисления. В ячейку В9 вводится формула: =ЧПС(В6;В2:В4)

Рис. 19. Рабочий лист для решения задачи с неравномерными капиталовложениями

В ячейку С7 можно ввести следующую формулу:

ЕСЛИ(B7=1;"год";ЕСЛИ(И(B7>=2;B7<=4);"года";"лет"))

В поле Установить в ячейке введите В9 , т. е. адрес ячейки, в которой необходимо получить искомое значение суммы сделки (15000);

В поле Значение введите 15000, т. е. само искомое значение суммы сделки;

В поле Изменяя значение ячейки введите адрес ячейки - В8, в которой с помощью средства Подбор параметра будет получена необходимая процентная ставка для рассматриваемой задачи (в случае, если такая существует).

Окончательное решение задачи приведено на рис. 20.

Рис. 19. Окно Подбор параметра для задачи о неравномерных капиталовложениях

Рис. 20 Оптимальная процентная ставка

Лабораторная работа №6. Таблица подстановки

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

Таблицу подстановки можно использовать для:

Изменения одного исходного значения, просматривая при этом результаты одной или нескольких формул;

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

Использование таблицы подстановки с одной изменяющейся переменной и несколькими формулами

Рассмотрим эту методику на примере расчета ежемесячных выплат по займу (расчет происходит с помощью функции ПЛТ) и платежей по процентам (функция ПРОЦПЛАТ):

Решение такой задачи предполагает следующие шаги:

1. Создать или перейти на рабочий лист, где будет решаться анализируемая задача.

2. Организовать интерфейс таким образом, чтобы все вводимые данные были понятны пользователю:

В соответствующие ячейки рабочего листа вводятся необходимые подписи и данные (рис. 21).

В ячейку В5 - формула: =ПЛТ($В$4/12;$В$3*12;$В$2)

В ячейку D6 - формула: =ПРОЦПЛАТ($D$4;$D$5;$D$3;$D$2)

Рис. 21 Подготовка исходных данных

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

3. После подготовки исходных данных перейти к тому месту рабочего листа, где будут располагаться рассчитываемые значения в зависимости от изменения одной переменной и от различных рассчитываемых формул (рис. 22).

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

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

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

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

5. Воспользоваться командой Данные | Таблица подстановки и в диалоговом окне Таблица подстановки (рис. 23) указать, куда и какие значения необходимо подставлять.

В нашем примере - подстановка значений процентной ставки (столбец исходных значений А10:А19) происходит в ячейку В4 , т. к. в этой ячейке изначально указывается величина процентной ставки, входящей в рассчитываемые формулы. Результаты вычислений будут помещены в незаполненные ячейки выделенного диапазона (рис. 24).

Рис. 23 Использование таблицы подстановки

Рис. 24 Рассчитанные значения для одномерной таблицы подстановки

Использование таблицы подстановки с двумя изменяющимися переменными и одной формулой

Рассмотрим эту методику на примере расчета ежемесячных выплат по займу в зависимости от различных сроков погашения и различных процентных ставок. Решение задачи предполагает

следующие шаги:

1. Организовать на рабочем листе соответствующий интерфейс пользователя для некоторого набора входных данных (рис. 25):

Конкретная процентная ставка - 3% (ячейка В4 );

Конкретный срок погашения - 3 года (ячейка ВЗ );

Формула для ячейки В5 :

ПЛТ($В$4/12;$В$3*12;$В$2)

Рис. 25. Подготовка данных задачи

2. Подготовить следующую таблицу (рис. 26):

Изменяемые данные поместить в левый столбец и верхнюю строку- в нашем случае значения процентной ставки (ячейка В4) располагаются в диапазоне В10:В14, а значения срока погашения (ячейка ВЗ ) - в диапазоне C9:F9 ;

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

Рис. 26. Подготовка диапазона для использования двумерной таблицы подстановки

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

4. Выполнить команду Данные | Таблица подстановки и в появившемся окне (рис. 27) указать, куда и какие значения необходимо подставлять.

Рис. 27 Использование таблицы подстановки при расчетах по двум параметрам

В рассматриваемом примере подстановка значений процентной ставки (столбец исходных значений В10:В14 ) происходит в ячейку В4 , т. к. в этой ячейке изначально указывается величина процентной ставки, входящей в рассчитываемую формулу, а подстановка значений сроков погашения (строка значений C9:F9 ) - в ячейку ВЗ . Результаты вычислений будут помещены в незаполненные ячейки выделенного диапазона (28).

Рис. 28.Рассчитанные данные с использованием двумерной таблицы подстановки

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

Исходные данные: затраты по проекту составят 700 млн руб. Ожидаемые доходы в течение последующих 5 лет составят, соответственно, 70, 90,300,250, 300 млн руб. Оценить экономическую

целесообразность проекта по скорости оборота инвестиции, если рыночная норма дохода 12%. Рассмотреть также следующие варианты (затраты на проект представлены числом со знаком минус):

600; 50;100; 200; 200; 300;

650; 90;120;200;250; 250;

500, 100,100, 200, 250, 250.

Рис. 29 Окно Диспетчер сценариев

Решение

Для вычисления внутренней скорости оборота инвестиции (внутренней нормы доходности) используется функция ВСД.:

ВСД (Значения; Предположения)

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

1. Решение приведено на рис. 30. Формулы для расчета:

в ячейкеВ11: =ВСД(В75:В80)

в ячейкеС11: =ЕСЛИ(В84>В82;"Проект экономически целесообразен"; "Проект необходимо отвергнуть")

Рис. 30. Расчет внутренней скорости оборота инвестиций

2. Рассмотрим этот пример для всех комбинаций исходных данных. Для создания сценария следует использовать команду Сервис | Сценарии | кнопка Добавить (рис. 31).

Рис. 31. Добавление сценария для первой комбинации исходных данных

После нажатия на кнопку ОК появляется возможность внесения новых значений для изменяемых ячеек (рис. 32).

Рис. 32. Окно для изменения значений ячеек сценария

Для сохранения результатов по первому сценарию нет необходимости редактировать значения ячеек- достаточно нажать кнопку ОК для подтверждения значений, появившихся по умолчанию, и выхода в окно Диспетчер сценариев (рис. 33).

Рис. 33 Окно Диспетчер сценариев с первым сохраненным сценарием

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

На рис. 34 сценарий Скорость_оборота_1 соответствует данным (-700; 70; 90; 300; 250; 300), сценарий Скорость_оборота_2 - данным (-600; 50; 100; 200; 200; 300), сценарий Скорость_оборота_З- данным (-650; 90; 120; 200; 250; 250), сценарий скорость_оборота_4- данным (-500, 100, 100, 200,

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

Рис. 34. Окно Диспетчер сценариев с добавленными сценариями

по расчету скорости оборота инвестиций

Рис. 4.31. Добавление ячеек результата в окно Отчет по сценарию

4. Для полученияитогового отчета по всем добавленным сценариям следует нажать кнопу Отчет в окне диспетчера сценариев. В появившемся окне Отчет по сценарию (рис. 35) выбрать необходимый тип отчета и дать ссылки на ячейки, в которых вычисляются результирующие функции. При нажатии на кнопку ОК на соответствующий лист рабочей книги выводится отчет по сценариям (рис. 36 и рис. 37)

Рис. 36 Отчет типаСтруктура

Рис. 37 Отчет типаСводная таблица по сценариям расчета скорости оборота инвестиций

Решение

Для выполнения задания:

1. Введите данные на рабочий лист в соответствии с рис. 38.

Рис.38 Подготовка данных для построения диаграммы

2. Выделите мышью диапазон А5:В12 и выполните команду Вставка | Диаграмма либо нажмите кнопку мастера диаграмм напанели инструментов Стандартная.

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

Какой ряд выбран в качестве значения данных (заполните поля Ряд и Значения). В нашем случае в поле Ряд вносим объем и в поле Значения - диапазон $В$5: $В$12;

D какой ряд будет служить подписями по оси X - в поле Подписи по оси X вносим год и указываем диапазон $А$5:$А$12.

Рис. 39. Определение рядов данных

4. Отформатируйте полученную диаграмму, используя контекстное меню каждого ее элемента (рис. 40).

Рис. 40. Построенная диаграмма Объем продаж

Задание 2. Построить график функции: у = cos 3 (πx).

Решение

Результат для этого примера представлен на рис. 41.

Для выполнения задания:

1. Задайте область определения X вводом начальных данных: 0 и 0,1, а затем маркером автозаполнения подготовьте весь диапазон А7:А27.

2. В ячейку В7 введите формулу:

=(СОЗ(ПИ()*А7))^3 и скопируйте ее на диапазон В7:В27.

3. Постройте график функции с помощью мастера диаграмм.

4. Отформатируйте полученный график.

Рис. 41 Пример построения графика функции

Решение

А1:J35, A1:J1 )

2. Сформируйте диапазон критериев для расширенного фильтра в соответствии с рис. 43.

Рис. 43. Диапазон критериев для расширенного фильтра к задаче про белые и черные машины

3. Выполните команду

4. Отфильтрованные данные приведены на рис. 44.

Рис. 44. Данные к задаче про белые и черные машины, отобранные расширенным фильтром

Задание 2. Определить, имеются ли в списке (см. рис. 42) машины, год выпуска которых больше 2000 и пробег которых более 100 00 км, но менее 100 000 км, или черные Мерседесы, цена которых более 20 000 у. е., но менее 30 000 у. е.

Решение

1. Откройте список, подлежащий фильтрации (список располагается в диапазоне А1:JЗ5, строка заголовка- в диапазоне A1:J1 )

МЗ:М4 . В ячейку МЗ введите Условие. В ячейку М4 введите формулу:

ИЛИ(И(G2>10000;G2<100000;D2>1990);И(C2="Мерседес";F2="Черный";H2>20000;H2<30000))

3. Выполните команду Данные | Фильтр | Расширенный фильтр.

4. Отфильтрованные данные представлены на рис. 45.

Рис. 45. Данные к задаче о пробеге, отобранные расширенным фильтром

Задание 3. Определить автомобили белого или красного цвета, цена которых меньше средней цены для всех автомобилей и пробег которых больше либо равен среднему пробегу для всех автомобилей (см. рис. 42).

Решение

1. Откройте список, подлежащий фильтрации (список располагается в диапазоне А1:JЗ5 , строка заголовка- в диапазонеA1:J1 )

2. Сформируйте вычисляемый критерий для расширенного фильтра в диапазоне L1:L2 . В ячейку L1 введите Условие. В ячейку L2 введите формулу:

И(ИЛИ(Г2="белый";Г2="красный");Н2<СРЗНАЧ($Н$2:$Н$133); G2 >=СРЗНАЧ($G$ 2:$G$13 3))

3. Выполните команду Данные | Фильтр | Расширенный фильтр.

Решение

1. Выделите список (или - установите в список указатель ячейки) и проведите сортировку (команда Данные | Сортировка) сначала - по полю Продавец, затем - по полю Дата продажи (рис. 47).

Рис. 46. Список продаж

2. Примените команду Данные | Итоги. В окне Промежуточные итоги установите параметры в соответствии с рис. 48: для получения верхнего (первого) уровня итогов - общее количество товаров, проданных конкретным продавцом.

Рис. 47.Сортировка списка

Рис. 48. Окно Промежуточные итоги для получения итогов по полю Продавец

3. Для получения второго уровня итогов поместите указатель ячейки в список с полученными итогами, затем выполните команду Данные | Итоги, установив в окне Промежуточные итоги параметры в соответствии с рис. 49.

Рис.49. Окно Промежуточные итоги для получения итогов по полю Дата продажи

4. Полученные промежуточные итоги представлены на рис. 50.

Рис. 50.Вложенные промежуточные итоги

Консолидация данных

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

1. Указать местоположение будущих консолидированных данных.

2. Выбрать команду Данные | Консолидация.

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

4. Указать способ консолидации:

Согласно расположению в диапазоне - сняты все флажки области Использовать в качестве имен;

Согласно заголовкам строк и столбцов- установлены

флажки подписи верхней строки и значения левого столбца.

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

6. При необходимости указать добавление структуры - установить флажок Создавать связи с исходными данными.

Задание 1. Объединить данные о количестве и стоимости проданных товаров в сети магазинов, которые представлены в виде списка со следующими полями (рис. 51): Товар, Стоимость, Количество, расположены на листе 2, листе 4 и листе 5.

Рис. 51.Данные о реализованных товарах

Консолидация в соответствии с данными рис. 52. Объединенные данные представлены на рис. 53.

Сводные таблицы

Сводные таблицы представляют собой средство для группировки, обобщения и анализа данных, находящихся в списках MS Excel или в таблицах, созданных в других приложениях. Внешне сводные

Рис. 52. Ввод данных в окно Консолидация

Рис. 53. Представление консолидированных данных

Сводные таблицы

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

Для обобщения большого количества однотипных данных;

Для реорганизации данных (с помощью перетаскивания);

Для отбора и группировки данных;

Для построения диаграмм.

Сводные таблицы создаются с помощью мастера сводных таблиц (команда Данные | Сводная таблица) по следующей методике:

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

2. Выполнить команду Данные | Сводная таблица.

3. Задать исходный диапазон данных, выполнив шаги 1 и 2 мастера (рис. 54 и 55). После нажатия кнопки Далее в окне мастера, приведенном на рис. 55, откроется окно 3-го шага мастера (рис. 56).

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

Рис. 54Определение местоположения данных для сводной таблицы

Рис. 55.Диапазон данных для сводной таблицы

Рис. 56.Указание местоположения будущей сводной таблицы

5. Для определения необходимой операции для полей, помещенных в область Данные, либо задания вычисляемого поля дважды щелкнуть левой кнопкой мыши на поле, помещенном в область Данные (рис. 57), и выбрать необходимые действия в окне Вычисление поля сводной таблицы (рис. 58).

6. Нажать кнопку Параметры (рис. 56) и в открывшемся окне (рис. 59) установить необходимые параметры сводной таблицы.

Рис. 57.Формирование макета сводной таблицы

Рис. 58. Окно Вычисление поля сводной таблицы

Рис. 59. Установка параметров сводной таблицы

7. После проведения всех подготовительных операций нажать кнопку Готово (рис. 56).

При создании, редактировании и работе со сводными таблицами необходимо учитывать следующее:

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

Местонахождение исходных данных – список MS Excel, внешний источник, диапазоны консолидации, находящиеся в другой сводной таблице;

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

1) поля, находящиеся в строках и столбцах таблицы;

2) поля, по которым подводятся итоги (с выбором необходимой операции);

3) поля для страниц, что позволяет представить информацию в трехмерном виде.

Сводная таблица – это средство только для отображения данных. Поэтому в самой таблице данные редактировать нельзя. Для изменения данных в сводной таблице необходимо внести изменения в источник данных, а затем обновить сводную (кнопкой Обновить данные на панели инструментовСводные таблицы (рис. 60);

Рис. 60. Панель инструментов Сводные таблицы

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

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 , после чего можете приступить к сдаче тестов по выполненной работе.