Лабораторная работа №3. Расчеты в MS Excel

Цель работы: познакомиться с вычислительными возможностями табличного процессора MS Excel, освоить работу с Мастером функций, научиться выполнять сложные расчеты.

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

Любая функция имеет следующую структуру:

<Имя функции> ( список аргументов )

Здесь Имя функции - это фиксированный набор символов, выбираемый из списка функций; список аргументов - это данные, над которыми будут выполняться операции. В качестве имен функций могут использоваться привычные обозначения, например SIN, LN, либо англоязычные и русскоязычные сокращения выполняемых операций, например СУММ, НЕЧЁТ, ABS и др. Имена функций записываются заглавными или малыми буквами без пробелов. Список аргументов может состоять из чисел, текста, логических величин (например ИСТИНА или ЛОЖЬ), массивов, значений ошибок (например #Н/Д) или ссылок. Кроме того, аргументы могут быть как константами, так и формулами. Эти формулы, в свою очередь, могут содержать другие функции. Некоторые функции имеет только один аргумент, другие могут иметь несколько аргументов, которые отделяются друг от друга точками с запятыми.

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

Рис. 2. Окно Мастера функций

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

ЗАДАНИЕ 1. Математические вычисления в MS Excel

1. Откройте чистый рабочий лист. Переименуйте лист в Функции.

2. Создайте следующую таблицу (Важно! Ячейки С4:С7 не заполняйте - в них будем вводить расчетные формулы) (рис. 3).

Рис. 3

3. В ячейку С4 введите формулу расчета квадратного корня из произведения содержимого ячейки А4 на абсолютное значение числа из ячейки В4.

4. В ячейку С5 введите формулу для возведения содержимого ячейки А5 в степень числа, содержащегося в ячейке В5.

5. В ячейку С6 введите формулу расчета целой части модуля разности содержимого ячеек А6 и В6.

6. В ячейку С7 запишите формулу расчета остатка от деления содержимого ячейки А7 на содержимое ячейки В7.

7. Сверьте свои результаты с данными, представленными в графе “Результаты”.

8. На этом же листе создайте еще одну таблицу (графу “Результаты” не заполняйте!) (рис.4).

Рис. 4

9. В ячейку G4 введите формулу расчета суммы значений из диапазона Е4:Е7.

10. В ячейку G5 введите формулу расчета среднего арифметического диапазона ячеек c E4 по F7.

11. В ячейку G6 введите формулу нахождения максимального значения для данных диапазона F4:F7.

12. В ячейку G7 введите формулу расчета коэффициента корреляции значений, содержащихся в колонках Е4:Е7 и F4:F7.

13. Сверьте свои результаты с данными графы “Результат”.

14. Сохраните работу.

ЗАДАНИЕ 2. Сложные расчеты

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

Например, формула =СТЕПЕНЬ(SIN(А1);2)позволяет вычислить значение квадрата синуса числа, записанного в ячейке А1. Рассмотрим процедуру ввода такой формулы в ячейку В2.

Сначала вызываем Мастер функций и выбираем функцию СТЕПЕНЬ. Функция имеет два аргумента, первым из которых будет синус числа, записанного в ячейке А1. Для ввода вложенной функции SIN можно воспользоваться полем имен ячеек, которое превращается в Палитру формул (см. рис. 5). Щелкнув по кнопке выпадающего списка Палитры формул, можно найти функцию SIN. Если же функции нет в списке, то следует выбрать команду Другие функции и выбрать функцию в открывшемся окне Мастера функций.

Рис. 5

В результате этих действий в строке формул появится функция =СТЕПЕНЬ(SIN()), после чего можно указать аргумент вложенной функции SIN, щелкнув на ячейке А1.

Для возврата к функции первого уровня достаточно щелкнуть по слову СТЕПЕНЬ в строке формул, при этом мы возвращаемся к выбору аргументов функции и можем указать показатель степени – число 2 (рис. 6).

Рис. 6

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

1. Откройте чистый рабочий лист. Переименуйте лист в Сложные функции.

2. Заполните таблицу по образцу на рис. 7.

Рис. 7

3. В ячейку В3 введите формулу для расчета сложной функции .

Ответ: 1,437955.

ЗАДАНИЕ 3. Построение графиков функций

1. Откройте чистый рабочий лист. Переименуйте лист в Синусоида. Наберите таблицу по образцу, показанному на рис. 8.

Рис. 8

2. Выделите ячейки А2:A12. Зайдите в меню Правка/Заполнить/Прогрессия и поставьте шаг 0,5. Ячейки А2:A12 должны заполниться числами 0, 0.5, 1, 1.5, ...5.

3. Установите курсор в ячейку B2. Выполните команду Вставка/Функцияили нажмите кнопку кнопочной панели Стандартная. Выберите категорию Математические, в правом окне найдите SIN. В строке формул появится =SIN() и откроется окно для выбора аргумента функции. В поле Число вставьте адрес ячейки A2 и нажмите клавишу Enter. Полученную формулу скопируйте в ячейки В3:В12. Аналогичные операции выполните для столбца COS.

4. Установите курсор в ячейку D2. Выполните команду Вставка/Функция.Выберитекатегорию Статистические и функцию СРЗНАЧ. В окне выбора аргументов функции напишите B2;C2 или укажите диапазон таблицы при помощи мыши, и у вас в ячейке D2 должно получиться среднее значение ячеек В2 и C2, равное 0,5.

5. Постройте графики функций SIN(X) и COS(X) на одной диаграмме с помощью Мастера диаграмм. Предварительно выделите исходный диапазон ячеек А2:С12. Чтобы графики отображались корректно, выберите тип диаграммы-Точечная.

ТЕСТОВОЕ ЗАДАНИЕ (задает преподаватель)

Указания:

Для построения графика функции y=f(x) на отрезке [a; b] с шагом h откройте чистый лист рабочей книги или создайте его. Переименуйте его, введя название Тест. Создайте таблицу табулирования функции, для этого в ячейку А1 введите Х, в ячейку В1 - У. В ячейки А2, А3 и т.д. введите значения аргумента Х на заданном отрезке с заданным шагом. В ячейку В2 введите формулу для расчета значения функции. Затем скопируйте её в ячейки В3, В4 и т.д.

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

1. на отрезке [-2, 2] с шагом 0,2.

2. на отрезке [0, 4] с шагом 0,2.

3. на отрезке [0, 2] с шагом 0,1.

4. на отрезке [-3, 3] с шагом 0,3.

5. на отрезке [2, 4] с шагом 0,1.

6. на отрезке [-1, 1] с шагом 0,1.

7. на отрезке [0, 5] с шагом 0,5.

8. на отрезке [0, 4] с шагом 0,2.

9. на отрезке [1, 4] с шагом 0,2.

10. на отрезке [1, 10] с шагом 0,5.

11. на отрезке [0, 2] с шагом 0,1.

12. на отрезке [0, 3] с шагом 0,15.

13. на отрезке [-2, 4] с шагом 0,4.

14. на отрезке [0, 10] с шагом 0,4.