Вычисление статистических характеристик выборки и построение гистограммы частот
Лабораторная работа №5
СТАТИСТИЧЕСКИЙ АНАЛИЗ ДАННЫХ В MS EXCEL
Цель работы: использование статистических функций для анализа данных в MS Excel; построение графиков и гистограмм.
ТЕОРЕТИЧЕСКИЕ ПОЛОЖЕНИЯ
Статистические функции
MS Excel предоставляет широкие возможности для анализа статистических данных. Для решения простых задач можно использовать встроенные функции. В табл. 1.1 приведены встроенные функции MS Excel для анализа статистических данных.
Таблица 1.1 – Статистические функции
| Функция | Назначение |
| СРОТКЛ | Возвращает среднее абсолютных значений отклонений точек данных от среднего. |
| СРЗНАЧ | Возвращает среднее арифметическое аргументов. |
| СРЗНАЧА | Возвращает среднее арифметическое аргументов, включая числа, текст и логические значения. |
| БЕТАРАСП | Возвращает интегральную функцию плотности бета-вероятности. |
| БЕТАОБР | Возвращает обратную функцию к интегральной функции плотности бета-вероятности. |
| БИНОМРАСП | Возвращает отдельное значение биномиального распределения. |
| ХИ2РАСП | Возвращает одностороннюю вероятность распределения хи-квадрат. |
| ХИ2ОБР | Возвращает обратное значение односторонней вероятности распределения хи-квадрат. |
| ХИ2ТЕСТ | Возвращает тест на независимость. |
| ДОВЕРИТ | Возвращает доверительный интервал для среднего значения по генеральной совокупности. |
| КОРРЕЛ | Возвращает коэффициент корреляции между двумя множествами данных. |
| СЧЁТ | Подсчитывает количество чисел в списке аргументов. |
| СЧЁТЗ | Подсчитывает количество значений в списке аргументов. |
| СЧИТАТЬПУСТОТЫ | Подсчитывает количество пустых ячеек в заданном диапазоне. |
| СЧЁТЕСЛИ | Подсчитывает количество непустых ячеек, удовлетворяющих заданному условию внутри диапазона. |
| КОВАР | Возвращает ковариацию, то есть среднее произведений отклонений для каждой пары точек. |
| КРИТБИНОМ | Возвращает наименьшее значение, для которого биномиальная функция распределения меньше или равна заданному значению. |
| КВАДРОТКЛ | Возвращает сумму квадратов отклонений. |
| ЭКСПРАСП | Возвращает экспоненциальное распределение. |
| FРАСП | Возвращает F-распределение вероятности. |
| FРАСПОБР | Возвращает обратное значение для F-распределения вероятности. |
| ФИШЕР | Возвращает преобразование Фишера. |
| ФИШЕРОБР | Возвращает обратное преобразование Фишера. |
| ПРЕДСКАЗ | Возвращает значение линейного тренда. |
Продолжение табл. 1.1.
| Функция | Назначение |
| ЧАСТОТА | Возвращает распределение частот в виде вертикального массива. |
| ФТЕСТ | Возвращает результат F-теста. |
| ГАММАРАСП | Возвращает гамма-распределение. |
| ГАММАОБР | Возвращает обратное гамма-распределение. |
| ГАММАНЛОГ | Возвращает натуральный логарифм гамма функции, ?(x). |
| СРГЕОМ | Возвращает среднее геометрическое. |
| РОСТ | Возвращает значения в соответствии с экспоненциальным трендом. |
| СРГАРМ | Возвращает среднее гармоническое. |
| ГИПЕРГЕОМЕТ | Возвращает гипергеометрическое распределение. |
| ОТРЕЗОК | Возвращает отрезок, отсекаемый на оси линией линейной регрессии. |
| ЭКСЦЕСС | Возвращает эксцесс множества данных. |
| НАИБОЛЬШИЙ | Возвращает k-ое наибольшее значение из множества данных. |
| ЛИНЕЙН | Возвращает параметры линейного тренда. |
| ЛГРФПРИБЛ | Возвращает параметры экспоненциального тренда. |
| ЛОГНОРМОБР | Возвращает обратное логарифмическое нормальное распределение. |
| ЛОГНОРМРАСП | Возвращает интегральное логарифмическое нормальное распределение. |
| МАКС | Возвращает максимальное значение из списка аргументов. |
| МАКСА | Возвращает максимальное значение из списка аргументов, включая числа, текст и логические значения. |
| МЕДИАНА | Возвращает медиану заданных чисел. |
| МИН | Возвращает минимальное значение из списка аргументов. |
| МИНА | Возвращает минимальное значение из списка аргументов, включая числа, текст и логические значения. |
| МОДА | Возвращает значение моды множества данных. |
| ОТРБИНОМРАСП | Возвращает отрицательное биномиальное распределение. |
| НОРМРАСП | Возвращает нормальную функцию распределения. |
| НОРМОБР | Возвращает обратное нормальное распределение. |
| НОРМСТРАСП | Возвращает стандартное нормальное интегральное распределение. |
| НОРМСТОБР | Возвращает обратное значение стандартного нормального распределения. |
| ПИРСОН | Возвращает коэффициент корреляции Пирсона. |
| ПЕРСЕНТИЛЬ | Возвращает k-ую персентиль для значений из интервала. |
| ПРОЦЕНТРАНГ | Возвращает процентную норму значения в множестве данных. |
| ПЕРЕСТ | Возвращает количество перестановок для заданного числа объектов. |
| ПУАССОН | Возвращает распределение Пуассона. |
| ВЕРОЯТНОСТЬ | Возвращает вероятность того, что значение из диапазона находится внутри заданных пределов. |
| КВАРТИЛЬ | Возвращает квартиль множества данных. |
| РАНГ | Возвращает ранг числа в списке чисел. |
| КВПИРСОН | Возвращает квадрат коэффициента корреляции Пирсона. |
| СКОС | Возвращает асимметрию распределения. |
| НАКЛОН | Возвращает наклон линии линейной регрессии. |
| НАИМЕНЬШИЙ | Возвращает k-ое наименьшее значение в множестве данных. |
| НОРМАЛИЗАЦИЯ | Возвращает нормализованное значение. |
| СТАНДОТКЛОН | Оценивает стандартное отклонение по выборке. |
| СТАНДОТКЛОНА | Оценивает стандартное отклонение по выборке, включая числа, текст и логические значения. |
| СТАНДОТКЛОНП | Вычисляет стандартное отклонение по генеральной совокупности. |
Продолжение табл. 1.1.
| Функция | Назначение |
| СТАНДОТКЛОНПА | Вычисляет стандартное отклонение по генеральной совокупности, включая числа, текст и логические значения. |
| СТОШYX | Возвращает стандартную ошибку предсказанных значений y для каждого значения x в регрессии. |
| СТЬЮДРАСП | Возвращает t-распределение Стьюдента. |
| СТЬЮДРАСПОБР | Возвращает обратное t-распределение Стьюдента. |
| ТЕНДЕНЦИЯ | Возвращает значения в соответствии с линейным трендом. |
| УРЕЗСРЕДНЕЕ | Возвращает среднее внутренности множества данных. |
| ТТЕСТ | Возвращает вероятность, соответствующую критерию Стьюдента. |
| ДИСП | Оценивает дисперсию по выборке. |
| ДИСПА | Оценивает дисперсию по выборке, включая числа, текст и логические значения. |
| ДИСПР | Вычисляет дисперсию для генеральной совокупности. |
| ДИСПРА | Вычисляет дисперсию для генеральной совокупности, включая числа, текст и логические значения. |
| ВЕЙБУЛЛ | Возвращает распределение Вейбулла. |
| ZТЕСТ | Возвращает двустороннее P-значение z-теста. |
Рассмотрим более подробно применение некоторых стандартных статистических функций MS Excel.
Примечание. Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите сочетание клавиш CTRL+` (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите режим Режим проверки формул.
1.1.1 Функция СРЗНАЧ. Возвращает среднее (арифметическое) своих аргументов.
Синтаксис:
СРЗНАЧ(число1; число2; ...)
число1, число2, ... – это от 1 до 30 аргументов, для которых вычисляется среднее.
Заметки:
- Аргументы должны быть либо числами, либо именами, массивами или ссылками, содержащими числа.
- Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.
Вычисляя средние значения ячеек, следует учитывать различие между пустыми ячейками и ячейками, содержащими нулевые значения, особенно если не установлен флажок Нулевые значения на вкладке Вид (команда меню Сервис Þ Параметры). Пустые ячейки не учитываются, но нулевые ячейки учитываются.
Пример использования функции СРЗНАЧ приведен в табл. 1.2.
Таблица 1.2 – Данные и вычисления с использованием функции СРЗНАЧ
| A | ||
| Данные | ||
| Формула | Описание (результат) | |
| =СРЗНАЧ(A2:A6) | Среднее арифметическое приведенных выше чисел (11,25) | |
| =СРЗНАЧ(A2:A6; 5) | Среднее арифметическое приведенных выше чисел и числа 5 (10) |
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите сочетание клавиш CTRL+` (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите режим Режим проверки формул.
1.1.2 Функция ДОВЕРИТ. Возвращает значение, с помощью которого можно определить доверительный интервал для математического ожидания генеральной совокупности. Доверительный интервал представляет собой диапазон значений. Выборочное среднее 
является серединой этого диапазона, следовательно, доверительный интервал определяется как
( ± ДОВЕРИТ). Для любого значения математического ожидания генеральной совокупности μ0, принадлежащего этому интервалу, вероятность того, что выборочное среднее отличается от μ0 более чем на , превышает значение уровня значимости «альфа». Для любого математического ожидания μ0, не принадлежащего этому интервалу, вероятность того, что выборочное среднее отличается от μ0 более чем на , не превышает значения уровня значимости «альфа».
Синтаксис:
ДОВЕРИТ(альфа ;станд_откл;размер)
Альфа – это уровень значимости используемый для вычисления уровня надежности. Уровень надежности равняется 100*(1 - альфа) процентам, или, другими словами, альфа равное 0,05 означает 95-процентный уровень надежности.
Станд_откл – это стандартное отклонение s генеральной совокупности для интервала данных, предполагается известным.
Размер – это размер выборки n.
Заметки:
- Если какой-либо из аргументов не является числом, то функция ДОВЕРИТ возвращает значение ошибки #ЗНАЧ!.
- Если альфа ≤ 0 или альфа ≥ 1, то функция ДОВЕРИТ возвращает значение ошибки #ЧИСЛО!.
- Если станд_откл ≤ 0, то функция ДОВЕРИТ возвращает значение ошибки #ЧИСЛО!.
- Если размер не целое, то оно усекается.
- Если размер < 1, то функция ДОВЕРИТ возвращает значение ошибки #ЧИСЛО!.
Если предположить, что альфа равняется 0,05, то нужно определить ту часть стандартной нормальной кривой, которая равняется (1 – альфа), или 95 процентам. Это значение равно ± 1,96. Доверительный интервал, следовательно, определяется следующим образом:
.
Пример. Пусть имеется выборка из 50 пассажиров, для которых среднее время поездки на работу составляет 30 минут со стандартным отклонением для генеральной совокупности равным 2,5. Если альфа = 0,05, то функция ДОВЕРИТ(0,05; 2,5; 50) возвращает значение 0,69291. Соответствующий доверительный интервал равняется 30 ± 0,69291, то есть приблизительно
[29,3; 30,7] (табл. 1.3). Для любого математического ожидания генеральной совокупности μ0, принадлежащего этому интервалу, вероятность того, что выборочное среднее отличается от μ0 более чем на 30, превышает 0,05. Аналогично, для любого математического ожидания генеральной совокупности μ0, не принадлежащего этому интервалу, вероятность того, что выборочное среднее отличается от μ0 более чем на 30, меньше 0,05.
Таблица 1.3 – Данные и вычисления с использованием функции ДОВЕРИТ
| A | B | |
| Данные | Описание | |
| 0,05 | Уровень значимости | |
| 2,5 | Стандартное отклонение для генеральной совокупности | |
| Размер выборки | ||
| Формула | Описание (результат) | |
| =ДОВЕРИТ(A2;A3;A4) | Доверительный интервал для математического ожидания генеральной совокупности. Другими словами, доверительный интервал средней продолжительности поездки на работу для генеральной совокупности составляет 30 ± 0,692951 минут или от 29,3 до 30,7 минут. (0,692951) |
1.1.3 Функция КВАДРОТКЛ. Возвращает сумму квадратов отклонений точек данных от их среднего.
Синтаксис:
КВАДРОТКЛ(число1;число2;...)
число1, число2, ... – это от 1 до 30 аргументов, квадраты отклонений которых суммируются. Можно использовать отдельный массив или ссылку на массив вместо аргументов, разделяемых точкой с запятой.
Заметки:
- Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.
- Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.
Пример использования функции КВАДРОТКЛ приведен в табл. 1.4.
Таблица 1.4 – Данные и вычисления с использованием функции КВАДРОТКЛ
| A | ||
| Данные | ||
| Формула | Описание (результат) | |
| =КВАДРОТКЛ(A2:A8) | Сумма квадратов отклонений приведенных выше данных от их среднего значения (48) |
1.1.4 Функция ЧАСТОТА. Вычисляет частоту появления значений в интервале значений и возвращает массив цифр. Поскольку данная функция возвращает массив, она должна задаваться в качестве формулы массива.
Синтаксис:
ЧАСТОТА(массив_данных;массив_интервалов)
Массив_данных – массив или ссылка на множество данных, для которых вычисляются частоты. Если массив_данных не содержит значений, то функция ЧАСТОТА возвращает массив нулей.
Массив_интервалов – массив или ссылка на множество интервалов, в которые группируются значения аргумента массив_данных. Если массив_интервалов не содержит значений, то функция ЧАСТОТА возвращает количество элементов в аргументе массив_данных.
Заметки:
- ЧАСТОТА вводится как формула массива после выделения интервала смежных ячеек, в которые нужно вернуть полученный массив распределения.
- Количество элементов в возвращаемом массиве на единицу больше числа элементов в массиве массив_интервалов. Дополнительный элемент в возвращаемом массиве содержит количество значений, больших чем максимальное значение в интервалах. Например, при подсчете трех диапазонов значений (интервалов), введенных в три ячейки, убедитесь в том, что функция ЧАСТОТА возвращает значения в четырех ячейках. Дополнительная ячейка возвращает число значений в массив_данных, больших чем значение границы третьего интервала.
- ЧАСТОТА игнорирует пустые ячейки и тексты.
- Формулы, которые возвращают массивы, должны быть введены как формулы массива.
Пример использования функции ЧАСТОТА приведен в табл. 1.5.
Таблица 1.5 – Данные и вычисления с использованием
функции ЧАСТОТА
| A | B | |
| Баллы | Интервалы | |
| Формула | Описание (результат) | |
| =ЧАСТОТА(A2:A10;B2:B4) | Число баллов в интервалах с номером 70 и меньше (1) | |
| Число баллов в интервале 71-79 (2) | ||
| Число баллов в интервале 80-89 (4) | ||
| Число баллов в интервалах с номером 90 и больше (2) |
Примечание. Формулу в этом примере необходимо ввести как формулу массива. Для вычисления нескольких значений с помощью формулы массива необходимо ввести массив в диапазон ячеек, состоящий из того же числа строк или столбцов, что и аргументы массива.
1) Выделите диапазон ячеек, в который требуется ввести формулу массива.
2) Введите формулу.
3) Нажмите сочетание клавиш Ctrl+Shift+Enter.
1.1.5 Функция СРГЕОМ. Возвращает среднее геометрическое значений массива или интервала положительных чисел.
Синтаксис:
СРГЕОМ(число1;число2; ...)
число1, число2, ... – это от 1 до 30 аргументов, для которых вычисляется среднее. Можно использовать один массив или одну ссылку на массив вместо аргументов, разделяемых точкой с запятой.
Заметки:
- Аргументы должны быть либо числами, либо именами, массивами или ссылками, содержащими числа.
- Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако ячейки, которые содержат нулевые значения, учитываются.
- Если какой-либо из аргументов ≤ 0, то функция СРГЕОМ возвращает значение ошибки #ЧИСЛО!.
Пример использования функции СРГЕОМ приведен в табл. 1.6.
Таблица 1.6 – Данные и вычисления с использованием функции СРГЕОМ
| A | ||
| Данные | ||
| Формула | Описание (результат) | |
| =СРГЕОМ(A2:A8) | Среднее геометрическое приведенных выше данных (5,476987) |
1.1.6 Функция ЭКСЦЕСС. Возвращает эксцесс множества данных. Эксцесс характеризует относительную остроконечность или сглаженность распределения по сравнению с нормальным распределением. Положительный эксцесс обозначает относительно остроконечное распределение. Отрицательный эксцесс обозначает относительно сглаженное распределение.
Синтаксис:
ЭКСЦЕСС(число1;число2; ...)
число1, число2, ... – от 1 до 30 аргументов, для которых вычисляется мода. Можно использовать один массив или одну ссылку на массив вместо аргументов, разделяемых точкой с запятой.
Заметки:
- Аргументы должны быть либо числами, либо именами, массивами или ссылками, содержащими числа.
- Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, эти значения игнорируются; ячейки, содержащие нулевые значения, учитываются.
- Если задано менее четырех точек данных или если стандартное отклонение выборки равняется нулю, то функция ЭКСЦЕСС возвращает значение ошибки #ДЕЛ/0!.
Пример использования функции ЭКСЦЕСС приведен в табл. 1.7.
Таблица 1.7 – Данные и вычисления с использованием функции ЭКСЦЕСС
| A | ||
| Данные | ||
| Формула | Описание (результат) | |
| =ЭКСЦЕСС(A2:A10) | Эксцесс приведенного выше множества данных (-0,2857) |
1.1.7 Функция МАКС. Возвращает наибольшее значение из набора значений.
Синтаксис:
МАКС(число1;число2; ...)
число1, число2, ... – от 1 до 30 чисел, среди которых требуется найти наибольшее.
Заметки:
- Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел. Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают значения ошибок.
- Если аргумент является массивом или ссылкой, то в нем учитываются только числа. Пустые ячейки, логические значения или текст в массиве или ссылке игнорируются. Если логические значения или текст не должны игнорироваться, следует использовать функцию МАКСА.
- Если аргументы не содержат чисел, то функция МАКС возвращает 0 (ноль).
Пример использования функции МАКСА приведен в табл. 1.8.
Таблица 1.8 – Данные и вычисления с использованием функции МАКСА
| A | ||
| Данные | ||
| Формула | Описание (результат) | |
| =МАКС(A2:A6) | Наибольшее из приведенных выше чисел (27) | |
| =МАКС(A2:A6; 30) | Наибольшее из приведенных выше чисел и числа 30 (30) |
1.1.8 Функция МЕДИАНА. Возвращает медиану заданных чисел. Медиана – это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана.
Синтаксис:
МЕДИАНА(число1;число2;...)
число1, число2, ... – от 1 до 30 чисел, для которых определяется медиана.
Заметки:
- Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа. Microsoft Excel проверяет все числа, содержащиеся в аргументах, которые являются массивами или ссылками.
- Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, эти значения игнорируются; ячейки, содержащие нулевые значения, учитываются.
- Если в множестве четное количеств чисел, то функция МЕДИАНА вычисляет среднее двух чисел, находящихся в середине множества. См. вторую формулу в примере.
Пример использования функции МЕДИАНА приведен в табл. 1.9.
Таблица 1.9 – Данные и вычисления с использованием функции МЕДИАНА
| A | ||
| Данные | ||
| Формула | Описание (результат) | |
| =МЕДИАНА(A2:A6) | Медиана первых пяти чисел в приведенном выше списке (3) | |
| =МЕДИАНА(A2:A7) | Медиана всех приведенных выше чисел или среднее чисел 3 и 4 (3,5) |
1.1.9 Функция МИН. Возвращает наименьшее значение в списке аргументов.
Синтаксис:
МИН(число1;число2; ...)
число1, число2, ... – от 1 до 30 чисел, среди которых требуется найти наименьшее.
Заметки:
- Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел. Аргументы, которые являются значениями ошибки или текстом, не преобразуемым в числа, вызывают значения ошибок.
- Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения или текст в массиве или ссылке игнорируются. Если логические значения или текст игнорироваться не должны, следует пользоваться функцией МИНА.
- Если аргументы не содержат чисел, то функция МИН возвращает 0.
Пример использования функции МИН приведен в табл. 1.10.
Таблица 1.10 – Данные и вычисления с использованием функции МИН
| A | ||
| Данные | ||
| Формула | Описание (результат) | |
| =МИН(A2:A6) | Наименьшее среди вышеприведенных чисел (2) | |
| =МИН(A2:A6;0) | Наименьшее среди вышеприведенных чисел и числа 0 (0) |
1.1.10 Функция МОДА. Возвращает наиболее часто встречающееся или повторяющееся значение в массиве или интервале данных. Как и функция МЕДИАНА, функция МОДА является мерой взаимного расположения значений.
Синтаксис:
МОДА(число1;число2; ...)
число1, число2, ... – от 1 до 30 аргументов, для которых вычисляется мода. Можно использовать один массив или одну ссылку на массив вместо аргументов, разделяемых точкой с запятой.
Заметки:
- Аргументы должны быть числами, именами, массивами или ссылками, которые содержат числа.
- Если аргумент, который является массивом или ссылкой, содержит текст, логические значения или пустые ячейки, эти значения игнорируются; ячейки, содержащие нулевые значения, учитываются.
- Если множество данных не содержит одинаковых данных, то функция МОДА возвращает значение ошибки #Н/Д.
В наборе значений мода – это наиболее часто встречающееся значение; медиана – это значение в середине массива; среднее – это среднее арифметическое значение. Ни одно из этих чисел не характеризует в полной мере то, в какой степени центрированы данные. Пусть данные сгруппированы в трех областях, одна половина данных близка к некоторому малому значению, а другая половина данных близка к двум другим большим значениям. Обе функции СРЗНАЧ и МЕДИАНА могут вернуть значение из относительно пустой середины, а функция МОДА скорее всего вернет доминирующее малое значение.
Пример использования функции МОДА приведен в табл. 1.11.
Таблица 1.11 – Данные и вычисления с использованием функции МОДА
| A | ||
| Данные | ||
| 5,6 | ||
| Формула | Описание (результат) | |
| =МОДА(A2:A7) | Мода или наиболее часто встречающееся число (4) |
1.1.11 Функция ПИРСОН. Возвращает коэффициент корреляции Пирсона r, безразмерный индекс в интервале от -1,0 до 1,0 включительно, который отражает степень линейной зависимости между двумя множествами данных.
Синтаксис:
ПИРСОН(массив1;массив2)
Массив1 – множество независимых значений.
Массив2 – множество зависимых значений.
Заметки:
- Аргументы должны быть числами или именами, массивами или ссылками, содержащими числа.
- Если аргумент, который является массивом или ссылкой, содержит тексты, логические значения или пустые ячейки, то такие значения игнорируются; однако, ячейки, которые содержат нулевые значения, учитываются.
- Если массив1 или массив2 пуст, или они содержат различное число точек данных, то функция ПИРСОН возвращает значение ошибки #Н/Д.
Пример использования функции ПИРСОН приведен в табл. 1.12.
Таблица 1.12 – Данные и вычисления с использованием функции ПИРСОН
| A | B | |
| Независимые значения | Зависимые значения | |
| Формула | Описание (результат) | |
| =ПИРСОН(A2:A6;B2:B6) | Коэффициент корреляции Пирсона для приведенных выше данных (0,699379) |
1.1.12 Функция СТАНДОТКЛОН. Оценивает стандартное отклонение по выборке. Стандартное отклонение – это мера того, насколько широко разбросаны точки данных относительно их среднего.
Синтаксис:
СТАНДОТКЛОН(число1; число2; ...)
число1, число2, ... – от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности. Вместо аргументов, разделенных точкой с запятой, можно также использовать массив или ссылку на массив.
Заметки:
- СТАНДОТКЛОН предполагает, что аргументы являются только выборкой из генеральной совокупности. Если данные представляют всю генеральную совокупность, то стандартное отклонение следует вычислять с помощью функции СТАНДОТКЛОНП.
- Стандартное отклонение вычисляется с использованием «несмещенного» или «n-1» метода.
- Логические значения, такие как ИСТИНА или ЛОЖЬ, а также текст игнорируются. Если текст и логические значения игнорироваться не должны, следует использовать функцию рабочего листа СТАНДОТКЛОНА.
Пример. Предположим, что из инструментов, отштампованных одной и той же машиной, выбраны наугад 10 штук и испытаны на излом. Пример использования функции СТАНДОТКЛОН приведен в табл. 1.13.
Таблица 1.13 – Данные и вычисления с использованием функции СТАНДОТКЛОН
| A | ||
| Сила | ||
| Формула | Описание (результат) | |
| =СТАНДОТКЛОН(A2:A11) | Стандартное отклонение предела прочности (27,46391572) |
1.1.13 Функция ДИСП. Оценивает дисперсию по выборке.
Синтаксис:
ДИСП(число1;число2; ...)
число1, число2, ... – от 1 до 30 числовых аргументов, соответствующих выборке из генеральной совокупности.
Заметки:
- ДИСП предполагает, что аргументы являются только выборкой из генеральной совокупности. Если данные представляют всю генеральную совокупность, вычисляйте дисперсию, используя функцию ДИСПР.
- Логические значения, такие как ИСТИНА или ЛОЖЬ, а также текст игнорируются. Если они не должны игнорироваться, пользуйтесь функцией рабочего листа ДИСПА.
Пример. Предположим, что из инструментов, отштампованных одной и той же машиной, выбраны наугад 10 штук и испытаны на излом. Пример использования функции ДИСП приведен в табл. 1.14.
Таблица 1.14 – Данные и вычисления с использованием функции ДИСП
| А | ||
| Сила | ||
| Формула | Описание (результат) | |
| =ДИСП(A2:A11) | Дисперсия предела прочности для всех инструментов (754,2666667) |
Вычисление статистических характеристик выборки и построение гистограммы частот
1.2.1 Задание исходных данных. Для того чтобы вычислить статистические характеристики выборки необходимо сгенерировать исходные данные. Для этого можно воспользоваться стандартной функцией СЛУЧ(), которая возвращает равномерно распределенное случайное число, большее либо равное 0 и меньшее 1. Новое случайное число возвращается при каждом вычислении рабочего листа.
Для задания случайных чисел в произвольном диапазоне можно воспользоваться следующим выражением =h-(h-l)*СЛЧИС(), где h, l – верхняя и нижняя границы диапазона соответственно. Чтобы скопировать формулу на необходимое количество ячеек можно воспользоваться автозаполнением (см. лабораторную работу №2).
После генерации выборки необходимо заменить формулы результатами вычисления, чтобы полученные значения не изменялись после каждого вычисления рабочего листа. Для этого:
1) выделите ячейку (или группу ячеек), содержащую формулу;
2) нажмите кнопку Копировать на панели инструментов Стандартная;
3) нажмите на стрелку рядом с кнопкой Вставить на панели инструментов Стандартная, а затем выберите команду Значения.
1.2.2 Вычисление статистических характеристик выборки. По полученным данным можно вычислить следующие статистические характеристики:
- минимальное значение выборки (МИН);
- максимальное значение выборки (МАКС);
- выборочное среднее (СРЗНАЧ);
- дисперсия (ДИСП);
- стандартное отклонение (СТАНДОТКЛОН);
- медиана (МЕДИАНА);
- коэффициент эксцесса (ЭКСЦЕСС).
1.2.3 Построение гистограммы частот. Для построения гистограммы необходимо рассчитать ее параметры. Рассмотрим построение гистограммы на примере. Пусть значения выборки расположены в ячейка A1:A100.
1 шаг: нужно рассчитать диапазоны случайных величин (ось X).
1) В ячейке B1 вычислите минимальное значение выборки =МИН(A1:A100).
2) В ячейке B2 вычислите максимальное значение выборки =МАКС(A1:A100).
3) В ячейке B3 вычислите ширину диапазона =(B2-B1)/5 (5 – количество столбцов гистограммы).
4) В ячейку C1 скопируйте минимальное значение выборки (только значение, а не формулу). Полученное значение – левая граница 1-го столбика гистограммы.
5) В ячейке C2 вычислите левую границу 2-го столбика гистограммы =C1+$B$3 (в формуле используется абсолютная ссылка на ячейку B3 для того, чтобы при копировании формулы эта ссылка не изменялась).
6) Скопируйте формулу из ячейки C2 на ячейки C3:C6 (в последней ячейке должна быть величина, равная максимальному значению выборки).
7) В ячейке D1 вычислите середину 1-го столбика гистограммы =C1+$B$3/2 (в формуле используется абсолютная ссылка на ячейку B3 для того, чтобы при копировании формулы эта ссылка не изменялась).
8) Скопируйте формулу из ячейки D1 на ячейки D2:D5 (количество значений в столбце равно количеству столбиков гистограммы).
2 шаг: нужно рассчитать частоты попадания значений выборки в полученные диапазоны с помощью стандартной функции ЧАСТОТА (ось Y).
1) Выделите ячейки E1:E5.
2) Введите формулу =ЧАСТОТА(A1:A100;C2:C5).
3) Нажмите сочетание клавиш Ctrl+Shift+Enter.
3 шаг: построение гистограммы с помощью Мастера диаграмм.
1) Выделите ячейки E1:E5.
2) Запустите Мастер диаграмм (см. лабораторную работу №4).
3) В 1-м окне диалога выберите тип Гистограмма.
4) Во 2-м окне диалога на вкладке Ряд в категории Подписи оси X задайте содержимое столбца D.
5) Задайте остальные параметры в диалоге Мастера диаграмм.
В результате на рабочем листе появится гистограмма частот.
ХОД ВЫПОЛНЕНИЯ РАБОТЫ
2.1 Создать новую книгу и сохранить ее под именем «ваша фамилия»_excel5.
2.2 Рассчитать 100 значений функции (см. индивидуальное задание).
2.3 Построить график функции (использовать тип Гладкие графики на вкладке Нестандартные диалога Мастер диаграмм).
2.4 Сгенерировать выборку из 100 чисел, которая получена путем умножения функции на случайное число (параметры выборки см. по индивидуальному заданию).
2.5 Вычислить с помощью стандартных функций Excel статистические характеристики выборки.
2.6 Вычислить необходимые параметры для построения гистограммы частот (количество столбцов гистограммы см. по индивидуальному заданию).
2.7 Построить гистограмму частот.
2.8 Сохранить все изменения в книге.
ИНДИВИДУАЛЬНЫЕ ЗАДАНИЯ
Номер варианта соответствует 2-й цифре по журналу академической группы (цифра 0 – вариант№10).
| № варианта | Параметры диапазона случайных чисел | Параметры функции | Кол-во столбцов гистограммы | ||||
| нижняя граница l | верхняя граница h | функция | начальное значение | конечное значение |  | ||
| 1. |  | -1 | 18,8 | 0,2 | |||
| 2. |  | 0,4 | 2,38 | 0,02 | |||
| 3. |  | -5 | 69,25 | 0,75 | |||
| 4. | -50 |  | 0,2 | 1,19 | 0,01 | ||
| 5. | -0,5 | 0,5 |  | 0,05 | 5,95 | ||
| 6. | -5 |  | 0,1 | 0,1 | |||
| 7. |  | -40 | -0,4 | 0,4 | |||
| 8. | 0,1 | 0,5 |  | 0,3 | 0,03 | 3,27 | |
| 9. | -30 |  | 10,9 | 0,1 | |||
| 10. |  | -100 | -1 |
КОНТРОЛЬНЫЕ ВОПРОСЫ
1) Какие встроенные функции MS Excel для анализа статистических данных вы знаете?
2) Какие статистические характеристики выборки можно рассчитать с помощью стандартных функций MS Excel?
3) Какие особенности использования функции ЧАСТОТА?
4) Как задать случайное число?
5) Как заменить формулу возвращаемым значением?
6) Что такое гистограмма?
7) Какие параметры необходимо рассчитать для построения гистограммы?
8) Как построить гистограмму?