Файл: Лабораторная работа 3 электронные таблицы математические и статистические функции.pdf

1
Лабораторная работа № 3
ЭЛЕКТРОННЫЕ ТАБЛИЦЫ: МАТЕМАТИЧЕСКИЕ И
СТАТИСТИЧЕСКИЕ ФУНКЦИИ
Время выполнения – 4 часа (аудиторная работа – 2 часа, самостоятельная работа – 2 часа).
Цель работы: освоение навыков вычислений с помощью математических и статистических функций, построения диаграммы для анализа данных.
Задачи работы
1. Познакомиться с математическими и статистическими функциями.
2. Научиться строить диаграмму (график).
3. Научиться применять функции для расчетов в электронных таблицах.
Перечень обеспечивающих средств
Задания лабораторной работы выполняются в электронной таблице MS Ex-
cel 2013 и выше.
Общие теоретические сведения
Функции – это стандартные формулы, которые обеспечивают выполнение определенных действий над значениями, выступающими в качестве аргументов.
Функции можно использовать для непосредственных вычислений и для преоб- разования чисел, например, для округления, для поиска значений, сравнения и т. д.
Для работы с функциями существует специальное средство – Мастер функ-
ций. Диалоговое окно Мастер функций можно запустить на вкладке Формулы или кнопкой f
x
в строке формул. В открывшемся диалоговом окне выбирается нужная категория и требуемая функция. Далее в окне Аргументы функции зада- ются данные для расчета (список аргументов).

2
Каждая функция, как и формула, начинается со знака равенства (=), далее указывается имя функции (например, СУММ). После имени функции пишутся две скобки, которые содержат аргументы функции. Аргументом функции может быть число, текст, логическое значение, массив, значение ошибки, ссылка на ячейку. В качестве аргументов используются также константы, формулы, или функции. Также есть функции, не содержащие аргументы, например, ПИ().
Использование математических функций
Среди математических функций наиболее часто используется функции суммы (СУММ), произведения (ПРОИЗВЕД), округления чисел. Также важную роль играют тригонометрические функции SIN(число), СОS(число)
И
ТАN(число), в которыхаргумент число – это угол в радианах, для которого определяется зна- чение функции.
Если угол в тригонометрических функциях задан в градусах, его следует предварительно преобразовать в радианы путем умножения его на ПИ()/180 или использованием функции РАДИАНЫ.
На листе 1 рассмотрим пример использования функции sin(x) и функций
ОКРУГЛ(), ОКРУГЛВВЕРХ(), ОКРУГЛВНИЗ().
Составить таблицу значений функции у = sin(x) для х, принадлежащего от- резку [0
o
, 60
o
], значение x изменяется с шагом h = 3
o
. В столбцах D, E, F предста- вить результат вычислений с использованием функций округления чисел. Округ- ление проводить с точностью до 3 десятичных знаков (табл. 1).
Построить график функции у = sin(x).
Таблица 1
Расчет значений функции y = sin(x) градусы радианы y округл округлвверх округлвниз
0
=радианы(A2) =sin(B2) =округл(C2;3) =округлвверх(C2;3) =округлвниз(C2;3)
3
…
60

3
Решение.
Столбец А необходимо заполнить значениями от 0 до 60, используя возмож- ности автозаполнения или инструмента Прогрессия.
В ячейке В2 вызвать мастер функций. Выбрать категорию Математиче-
ские, в списке найти функцию РАДИАНЫ для перевода угла из градусов в ради- аны. В следующем диалоговом окне указать адрес ячейки (A2), для которой вы- полняется операция (с помощью выделения мышью или набрав на клавиатуре).
Выполнить автозаполнение ячеек от B2 до B22.
Аналогичные действия выполнить для столбца С, вычислив значения функции по формуле y=sin(x) (рис. 1).
Рис. 1. Составленная таблица значений функции y = sin(x) без округления и с использованием функций округления чисел

4
В столбцах D, E, F необходимо представить вычисленные в столбце C значе- ния функции с помощью функций ОКРУГЛ(), ОКРУГЛВВЕРХ(), ОКРУГЛВНИЗ() с округлением до 3 десятичных разрядов.
1. Функция ОКРУГЛ() округляет число до указанного количество десятич- ных разрядов. Другими словами, отбрасывает цифры меньшие 5, а цифры, начи- ная с 5 и больше, округляет до следующего разряда.
2. Функция ОКРУГЛВВЕРХ() округляет число до ближайшего большего по модулю.
3. Функция ОКРУГЛВНИЗ() округляет число до ближайшего меньшего по модулю.
Для анализа табличных данных можно использовать их графическое пред- ставление. В табличном процессоре MS Excel можно использовать множество различных типов диаграмм, причем каждый тип содержит несколько форматов.
Каждый тип диаграмм служит для определенных целей.
Типы диаграмм: гистограммы, графики, круговые, линейчатые, точечные и т. д.
Построение диаграмм можно выполнить с помощью мастера диаграмм (Ре- комендуемые диаграммы) или выбрав нужную пиктограмму на вкладке Вставка
(рис. 2).
Рис. 2. Область Диаграммы на Ленте
Основной объект диаграммы – ряд данных. Ряд данных – это совокупность данных, содержащая количественные характеристики объекта. Эти данные со- держатся в одном из векторов (в столбце или в строке), составляющих таблицу.
В качестве имен рядов данных Excel использует заголовки столбцов или строк данных. Имена рядов отображаются в легенде диаграммы. С понятием «ряды данных» тесно связано понятие «категории данных», отражающее качество

5
(свойство) элементов в ряду. Как имена оси категорий Excel использует заго- ловки тех столбцов или строк таблицы, которые не используются в качестве ря- дов данных.
Другими объектами диаграмм являются:
1) легенда – текст, идентифицирующий отдельные элементы диаграммы;
2) ось – одна из сторон диаграммы. По горизонтальной оси обычно отобра- жаются категории и/или названия рядов. По вертикальной оси – данные;
3) сетка – множество линий, являющихся продолжением деления осей, ко- торые способствуют лучшему восприятию данных на диаграмме и облегчают их анализ. Кроме того, сетка помогает определить точное значение данных.
Этапы создания диаграммы.
1. Для построения диаграммы необходимо выделить исходные данные
(например, значения функции).
2. На вкладке Вставка в группе Диаграммы выбрать тип диаграммы. На те- кущем листе будет построена диаграмма.
3. Настроить значения оси x. Для этого нажать «Выбрать данные» на вкладке Конструктор или через контекстное меню. В появившемся окне изме- нить подписи горизонтальной оси, выделив их на листе (рис. 3).
Рис. 3. Изменение значений оси x
4. Вкладка Конструктор позволяет указать название диаграммы, включить легенду, сетку и другие элементы диаграммы (рис. 4); также можно использовать контекстное меню.
5. Вкладка Формат позволяет изменить формат области диаграммы
(настройки оформления) (также можно использовать контекстное меню).

6
Рис. 4. Кнопка «Добавить элемент диаграммы» на вкладке Конструктор
Для построения графика функции y=sin(x) необходимо выполнить 3 шага:
1) выделить исходные значения функции в ячейках C2:C22;
2) перейти на вкладку Вставка;
3) нажать на кнопку Вставить график в группе Диаграммы и выбрать тип диаграммы Линия (рис. 5).
На текущем листе будет размещена диаграмма (рис. 6).
Созданную диаграмму (график) требуется настроить.
Зададим название диаграммы «График функции y=sin(x)» (указывать без ка- вычек).
Далее настроим подписи оси x. Для этого нужно Выбрать данные в кон- текстном меню, нажав правой кнопкой мыши в любом месте диаграммы. Откро- ется диалоговое окно Выбор источника данных. В нем в правой части находятся
Подписи горизонтальной оси. Нажать кнопку Изменить и выбрать диапазон ячеек A2:A22, где находятся значения аргумента функции, представленные в гра- дусах. Подтвердить установленные параметры (рис. 7).

7
Полученную диаграмму можно разместить на отдельном листе рабочей книги, предназначенном только для диаграммы. Для этого на вкладке Конструк-
тор нужно выбрать Переместить диаграмму, далее Разместить диаграмму на
отдельном листе Диаграмма 1 (рис. 8, 9).
Рис. 5. Создание диаграммы (графика)

8
Рис. 6. Созданная диаграмма
Рис. 7. Настройка подписей оси x

9
Рис. 8. Перемещение диаграммы
Рис. 9. Размещение диаграммы на отдельном листе
Построенный график функции y = sin(x) представлен на рис. 10.

10
Рис. 10. Построенный график функции y = sin(x) при x

[0; 60]
Все настройки диаграммы расположены на Ленте в двух контекстных вкладках Конструктор и Формат. Самостоятельно изучите возможности ко- манд в этих вкладках.
Использование статистических функций
Статистические функции используют для анализа данных в области мате- матической статистики и теории вероятностей.
На листе 2 рассмотрим пример использования функций СРЗНАЧ(), МИН(),
МАКС(), НАИБОЛЬШИЙ(), НАИМЕНЬШИЙ(), СЧЁТ(), СЧЁТЗ(), СЧИ-
ТАТЬПУСТОТЫ().
Исходными значениями для статистических функций будут значения в ячейках C2:C22 на листе 1.
Для нахождения максимального и минимального значения из ряда чисел применяют функции МАКС() и МИН().

11
В ячейке A2 найдем максимальное значение с помощью формулы
=МАКС(Лист1!C2:C22) или с помощью мастера формул, выбрав функцию
МАКС() в группе Статистические. Аргументы функции МАКС (диапазон ячеек) можно выбрать с помощью мыши.
В ячейке B2 найдем максимальное значение с помощью формулы
=МИН(Лист1!C2:C22) или с помощью мастера формул, выбрав функцию МИН() в группе Статистические.
В случаях, когда требуется найти не самое большое (маленькое) значение, а значение, которое занимает определенное положение в ряду данных (например, третье по величине), можно использовать функции НАИБОЛЬШИЙ() и
НАИМЕНЬШИЙ().
В ячейках C2, C3, C4 найдем второе, третье и четвертое наибольшее по ве- личине значения с помощью формулы =НАИБОЛЬШИЙ(Лист1!C2:C22;K), где
K – позиция (начиная с наибольшей) в массиве или диапазоне. В качестве аль- тернативы можно использовать мастер формул, в котором выбрать функцию
НАИБОЛЬШИЙ() в группе Статистические.
В ячейке D2, D3, D4 найдем второе, третье и четвертое наименьшее по ве- личине значения с помощью формулы =НАИМЕНЬШИЙ(Лист1!C2:C22;K), где
K – позиция (начиная с наименьшей) в массиве. В качестве альтернативы можно использовать мастер формул, в котором выбрать функцию НАИМЕНЬШИЙ() в группе Статистические.
Для вычисления среднего арифметического значения используется функция
СРЗНАЧ(). Рассчитаем среднее арифметическое значение по исходным данным в диапазоне C2:C22 на листе 1. Результат поместим в ячейку E2. Для этого вос- пользуемся мастером функций или напишем формулу =СРЗНАЧ(Лист1!C2:C22).
Для определения количества ячеек, содержащих числовые значения, приме- няется функция СЧЁТ(). Посчитаем, сколько ячеек из диапазона A1:E5 на листе 2 содержат числовые значения с помощью формулы =СЧЁТ(A1:E5). Результат по- местим в ячейку F2.

12
Функция СЧЁТЗ() позволяет посчитать количество ячеек, содержащих лю- бые значения. В ячейку G2 напишем формулу =СЧЁТЗ(A1:E5).
Если необходимо посчитать количество пустых ячеек, то необходимо вос- пользоваться функцией СЧИТАТЬПУСТОТЫ(). В ячейке H2 напишем формулу
=СЧИТАТЬПУСТОТЫ(A1:E5).
Результат вычислений представлен на рис. 11.
Рис. 11. Результат использования статистических функций
Задания
Задание 1. На листе 4 составить таблицу значений функции по заданному варианту. Представить вычисленные значения функции без округления и с округ- лением, используя функции ОКРУГЛ(), ОКРУГЛВВЕРХ(), ОКРУГЛВНИЗ() с точ- ностью до 4 десятичных знаков. Пример построения таблицы находится на рис. 1.
Построить по данным таблицы график зависимости у от х. На диаграмме
(графике) задать значения оси x согласно заданию, указать название диаграммы.
Диаграмму разместить на отдельном листе диаграммы, который не содержит ячейки, с названием «График».
Варианты заданий:
Номер варианта
Функция
Отрезок
Шаг
1
Y = 20,01 + x
2
– sin(x)
[10 0
;210 0
]
20 0
2
Y = sin(x) – cos(x) + 20,02
[20 0
;290 0
]
30 0
3
Y = 20,03 + tg
3
(x)
[30 0
;190 0
]
20 0
4
Y = sin(x) * 20,04 + cos(x)
[-40 0
;170 0
]
30 0
5
Y = 20,05x – sin(x)
[50 0
;320 0
]
30 0
6
Y = 20,06 + x – cos(x)
[-60 0
;290 0
]
25 0
7
Y = 20,07 * sin
2
(x)
[70 0
;190 0
]
10 0
8
Y = 20,08 + cos
2
(x) + x
[80 0
;130 0
]
5 0
9
Y = 20,09 * tg(x) – cos(x)
[90 0
;240 0
]
30 0
10
Y = 20,1 – tg
2
(x)
[100 0
;340 0
]
30 0
11
Y = sin(x) – 20,11 + cos(x)
[10 0
;210 0
]
20 0

13 12
Y = 20,12x + sin(x)
[20 0
;290 0
]
30 0
13
Y = 20,13 * x – cos(x)
[30 0
;190 0
]
20 0
14
Y = 20,14 – x
2
* sin(x)
[-40 0
;170 0
]
30 0
15
Y = sin(x) * cos(x) + 20,15
[50 0
;320 0
]
30 0
16
Y = 20,16 – tg
3
(x)
[-60 0
;290 0
]
25 0
17
Y = 20,17 + tg(x) – cos(x)
[70 0
;190 0
]
10 0
18
Y = 20,18 + tg
2
(x)
[80 0
;130 0
]
5 0
19
Y = 20,19 – sin
2
(x)
[90 0
;240 0
]
30 0
20
Y = 20,2 + cos
2
(x) * x
[100 0
;340 0
]
30 0
Задание 2. На листе 5 определить следующие значения:
‒ максимальное, минимальное значения (с помощью функций МАКС,
МИН);
‒ второе и третье наибольшее по величине значения (с помощью функции
НАИБОЛЬШИЙ);
‒ второе и третье наименьшее по величине значения (с помощью функции
НАИМЕНЬШИЙ);
‒ среднее арифметическое значение (с помощью функции СРЗНАЧ);
‒ среднее медианное значение (с помощью функции МЕДИАНА).
Исходные данные для выполнения задания – вычисленные значения функ- ции в задании 1, которые находятся на листе 4.
Задание 3. На листе 6 в столбец B ввести исходные данные: числа от X до
22, где X – номер варианта. В столбцах C–G с помощью функций вычислить де- сятичный логарифм, натуральный логарифм, квадратный корень, факториал и двойной факториал чисел (рис. 7).

14
Рис. 7. Пример выполнения 3 задания
Контрольные вопросы
1. Что такое функция в Excel?
2. Описать структуру функции в Excel.
3. Описать процесс создания диаграммы (графика).
4. Перечислить названия всех функций, представленных в работе, и их краткое описание.
Содержание отчета
1. Титульный лист
2. Цель работы.
3. Номер варианта.
4. Формулировка задания.
5. Описание хода выполнения заданий, включая скриншоты рабочих листов электронной таблицы с выполненными заданиями.
6. Ответы на контрольные вопросы.
7. Общий вывод о проделанной работе.