Основы научных и инженерных исследований

Синтаксис: =ДИСП (число 1; число 2; ...)

Пример 3. Для таблицы экспериментальных данных (см. табл. 5.5), занимающей ячейки в электронной таблице

вдиапазоне А2:F9, вычислить дисперсии для каждого из столбцов.

Произведем операции, аналогичные операциям примера 1, с тем отличием, что в ячейку А12 формул запишем: =ДИСП(А2:А9). Результаты расчета дисперсий приведены

в12-й строке ниже.

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

∑ii==1n (Yi −Y )2.

Синтаксис: =КВАДРОТКЛ (число 1; число 2; ...) Пример 4. Для выборки чисел {4;5;8;7;11;4;3} вычис-

лить сумму квадратов отклонений от среднего.

В пустой ячейке электронной таблицы напишем: =КВАДРОТКЛ (4;5;8;7;11;4;3). После введения Enter получим в этой ячейке результат: 48.

171

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

Синтаксис: =МЕДИАНА (число 1; число 2; …) Аргументами этой функции должны быть числа или

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

Пример 5. Для вариационного ряда, содержащего нечетное количество чисел (1;2;3;4;5), вычислить медианное значение. В пустой ячейке электронной таблицы напишем: =МЕДИАНА (1;2;3;4;5). После введения Enter получим в этой ячейке результат: 3.

Пример 6. Для вариационного ряда, содержащего нечетное количество чисел (1;2;3;4;5;6), вычислить медианное значение. =МЕДИАНА (1;2;3;4;5;6).

Результат: 3.5 (среднее между 3 и 4).

Мода – вычисляет наиболее часто встречающееся значение в массиве или интервале данных.

Синтаксис: =МОДА (число 1; число 2; …).

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

Пример 7. Для выборки чисел {5,6;4;4;3;2;4} вычислить моду. В пустой ячейке электронной таблицы напишем: =МОДА (5,6;4;4;3;2;4). После введения Enter получим в этой ячейке результат: 4.

Пример 8. Вычислить величину моды для столбца анализируемых данных (5,6;4;4;3;2;4), размещенных в столбце таблицы Microsoft Excel, в ячейках А1-А6. Для этого в пустой ячейке А7 напишем: =МОДА (А1-А6). После введения Enter получим в этой ячейке результат: 4.

Асимметрия кривой распределения.

Расчетная формула:

172

Синтаксис: =СКОС (число 1; число 2; ...). В качестве аргументов функции могут быть указаны числа, массивы или ссылки на массивы.

Пример 9. Для таблицы экспериментальных данных (см. табл. 5.5), занимающей ячейки в электронной таблице в диапазоне А2:F9, вычислить асимметрию кривой распределения для каждого из столбцов. Для этого произведем операции, аналогичные операциям примера 1, с тем отличием, что в ячейку А13 формул запишем: =СКОС(А2:А9). Результаты расчета асимметрии кривой распределения запишем в таблице в строке 13:

Синтаксис: =ЭКСЦЕСС(число 1; число 2; …).

В качестве аргументов функции могут быть также массивы или ссылки на массивы.

Пример 10. Для таблицы экспериментальных данных (см. табл. 5.5), занимающей ячейки в электронной таблице в диапазоне А2:F9, вычислить эксцесс кривой распределения для каждого из столбцов. Для этого произведем опера-

173

ции, аналогичные операциям примера 1, с тем отличием, что в ячейку А14 запишем: =ЭКСЦЕСС(А2:А9). Результаты расчета эксцесса кривой распределения запишем в таблице в строке 14:

Доверительный интервал (уровень надежности). До-

верительный интервал в программе Microsoft Excel называют уровнем надежности.

Расчетная формула:

εñëó÷ = ±t S y = ±t Syn .

Как следует из приведенной формулы, доверительный интервал – это интервал отклонений с обеих сторон от среднего выборки, имеет знак ±.

Синтаксис: =ДОВЕРИТ(альфа; станд_откл; размер).

Вскобках функции указаны аргументы функции:

•Альфа – это уровень значимости, используемый для вычисления доверительного интервала (уровня надежности). Для альфа, равного 0,05, и размера выборки более 30 доверительный интервал в программе Microsoft Excel определяется по формуле

ε = ±1,96×Sy/ n.

•Станд_откл – это стандартное (среднеквадратичное) отклонение генеральной совокупности для интервала данных, предполагается известным.

•Размер – это размер выборки.

Замечания:

1) если какой-либо из аргументов не является числом, то функция ДОВЕРИТ вычисляет значение ошибки #ЗНАЧ!;

174

2)если альфа = 0 или альфа = 1, то функция ДОВЕРИТ вычисляет значение ошибки #ЧИСЛО!;

3)если станд_откл = 0, то функция ДОВЕРИТ вычисляет значение ошибки #ЧИСЛО!;

4)если размер не целое число, то оно усекается до целого;

5)если размер < 1, то функция ДОВЕРИТ вычисляет значение ошибки #ЧИСЛО!

Пример 11. Вычислить доверительный интервал для

α= 0,05, величины стандартного отклонения 0,4857 и размера выборки 32. В соответствии с формулой функции в пустой ячейке запишем: =ДОВЕРИТ(0,05;0,4857;32). Результат расчета: 0,16828.

Вероятность. Функция вычисляет вероятность того, что значение из интервала находится внутри заданных пределов. Если «верхний_предел» не задан, то вычисляется вероятность того, что значения в аргументе «x_интервал» равняются значению аргумента «нижний_предел».

Синтаксис:=ВЕРОЯТНОСТЬ(x_интервал; интервал_ вероятностей; нижний_ предел; верхний_предел).

Аргументы функции:

•х_интервал – это интервал числовых значений x (аргумент в функции вероятности P = f(x)).

•Интервал_вероятностей – это множество вероятностей P = f(x)), соответствующих значениям аргумента x.

•Нижний_предел – это нижняя граница значения, для которого вычисляется вероятность P = f(x)).

•Верхний_предел – это необязательная верхняя граница значения, для которого требуется вычислить вероятность P = f(x)).

Замечания:

1)если любое значение в аргументе интервал_вероятностей < 0, или если какое-либо значение в аргументе интервал_вероятностей > 1, то функция ВЕРОЯТНОСТЬ вычисляет значение ошибки #ЧИСЛО!;

2)если сумма значений в аргументе интервал_вероят-

ностей ≠1, то функция ВЕРОЯТНОСТЬ вычисляет значение ошибки #ЧИСЛО!;

175

3)если верхний_предел опущен, то функция ВЕРОЯТНОСТЬ вычисляет вероятность при значении нижнего предела;

4)если x_интервал и интервал_вероятностей содержат различное количество точек данных, то функция ВЕРОЯТНОСТЬ вычисляет значение ошибки #Н/Д.

Примеры:

ВЕРОЯТНОСТЬ({0;1;2;3};{0,2;0,3;0,1;0,4};2) равняется 0,1.

ВЕРОЯТНОСТЬ({0;1;2;3};{0,2;0,3;0,1;0,4};1;3) равня-

ется 0,8.

t-Распределение Стьюдента. Вычисляет величину

уровня значимости (в долях) α = 1 – Р для известного значения t-распределения Стьюдента.

Синтаксис: =СТЬЮДРАСП(x; степени_свободы; хвосты).

Аргументы функции:

•x – численное значение величины t-распределения Стьюдента.

•Степени_свободы – (f = n – 1) целое, указывающее число степеней свободы.

•Хвосты – число вычисляемых хвостов распределения. Если хвосты = 1, то функция СТЬЮДРАСП вычисляет одностороннее распределение. Если хвосты = 2, то функция СТЬЮДРАСП вычисляет двухстороннее распределение.

Замечания:

1)если какой-либо из аргументов не является числом, то функция СТЬЮДРАСП вычисляет значение ошибки

#ЗНАЧ!;

2) если степени_свободы < 1, то функция СТЬЮДРАСП вычисляет значение ошибки #ЧИСЛО!;

3)аргументы степени_свободы и хвосты усекаются до

целых;

4)если хвосты — любое значение, отличное от 1 и 2, то функция СТЬЮДРАСП вычисляет значение ошибки

#ЧИСЛО!;

5)если x < 0, то функция СТЬЮДРАСП вычисляет значение ошибки #ЧИСЛО!

176

Пример 1. Найти величину уровня значимости α и доверительной вероятности для критерия Стьюдента t = 2,306 (двухстороннего распределения) и числа измерений 9 (число степеней свободы 8). В пустую ячейку электронной таблицы внесем запись =СТЬЮДРАСП(2,306;8;2), получим результат 0,0500. Из анализа величины α = 0,05 следует, что доверительная вероятность измерений составляет 0,95.

Пример 2. Найти величину уровня значимости α и доверительной вероятности для критерия Стьюдента t = 1,397 (двухстороннего распределения) и числа измерений 9 (число степеней свободы 8). В ячейку электронной таблицы запишем =СТЬЮДРАСП(1,397;8;2), получим результат 0,200. Из анализа величины α = 0,20 следует, что доверительная вероятность измерений составляет 0,80.

Обратное распределение Стьюдента. Вычисляет зна-

чение t-распределения Стьюдента, приводимого в статистических таблицах для заданного уровня значимости альфа (α = 1 – P) и числа степеней свободы f = n – 1.

Синтаксис: =СТЬЮДРАСПОБР(альфа; степени_свободы)

где альфа α = (1 – Р); Р – вероятность. Замечания:

1)если любой из аргументов не является числом, то функция СТЬЮДРАСПОБР вычисляет значение ошибки

#ЗНАЧ!;

2)если вероятность < 0 или вероятность > 1, то функция СТЬЮДРАСПОБР вычисляет значение ошибки

#ЧИСЛО!;

3)если степень_свободы не целое число, то оно усе-

кается;

4) если степени_свободы < 1, то функция СТЬЮДРАСПОБР вычисляет значение ошибки #ЧИСЛО!;

5)СТЬЮДРАСПОБР вычисляет значение t, для которого P(|X| > t) = вероятность, где X – это случайная величина, соответствующая t-распределению и P(|X| > t) = P(X < –t или X > t);

6)одностороннее t-значение может быть получено при замене аргумента альфа на 2 альфа.

177

Пример. Для уровня значимости α = 0,05 и числа степеней свободы, равного 10, двухстороннее значение t вычисляется по следующему выражению:

=СТЬЮДРАСПОБР(0,05;10) равно 2,28139.

Одностороннее значение для той же доверительной вероятности α = 0,05 и числа степеней свободы 10 t может быть вычислено по выражению

=СТЬЮДРАСПОБР(2*0,05;10) равно 1,812462.

5.5.Использование пакета «Описательная статистика» программы Microsoft Excel для расчета статистических

показателей

В состав Microsoft Excel входит набор средств анализа данных (называемый «Описательная статистика»), предназначенный для одновременного вычисления и записи в электронных таблицах следующих величин: Среднее, Стандартная ошибка (среднего), Медиана, Мода, Стандартное отклонение, Дисперсия выборки, Эксцесс, Асимметричность, Интервал, Минимум, Максимум, Сумма, Счет, Уровень надежности. Использование такого пакета существенно ускоряет проведение статистических расчетов и анализа.

Обращение к пакету анализа «Описательная статистика» проводят следующим образом. В меню Microsoft Excel с помощью мыши выбирают «Сервис», в котором обращаются к команде «Анализ данных». В появившемся окне «Инструменты анализа» выбирают «Описательная статистика» и щелкают мышью ОК.

Если в Microsoft Excel нет строки «Анализ данных» в меню «Сервис», то для установки пакета анализа необходимо в меню «Сервис» загрузить «Надстройки» и напротив «Пакет анализа» поставить флажок.

После вхождения в пакет «Описательная статистика» появится диалоговое окно, содержащее следующие параметры:

178

Входные данные: Входной интервал. Группирование:

–по столбцам;

–по строкам.

Метки в первой строке. Параметры вывода:

Выходной интервал:

–новый рабочий лист;

–новая рабочая книга. Итоговая статистика. Уровень надежности: K-й наибольший; K-й наименьший.

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

Группирование. Установите переключатель в положение «По столбцам» или «По строкам» в зависимости от расположения данных во входном диапазоне.

Метки в первой строке. Установите переключатель в положение «Метки в первой строке», если первая строка во входном диапазоне содержит названия столбцов. Установите переключатель в положение «Метки в первом столбце», если названия строк находятся в первом столбце входного диапазона. Если входной диапазон не содержит меток, то необходимые заголовки в выходном диапазоне будут созданы автоматически.

Уровень надежности. Установите флажок, если в выходную таблицу необходимо включить строку для уровня надежности. В поле введите требуемое значение. Например, значение 95 % вычисляет уровень надежности среднего со значимостью 0,05.

K-й наибольший (используется только тогда, когда не выводится «Итоговая статистика»). Установите флажок,

179

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

K-й наименьший (используется только тогда, когда не выводится «Итоговая статистика»). Установите флажок, если в выходную таблицу необходимо включить строку для K-го наименьшего значения для каждого диапазона данных. В соответствующем окне введите число K. Если K равно 1, эта строка будет содержать минимум из набора данных.

Выходной интервал. Введите ссылку на левую верхнюю ячейку выходного диапазона. Этот инструмент анализа выводит два столбца сведений для каждого набора данных. Левый столбец содержит метки статистических данных; правый столбец содержит статистические данные. Состоящий из двух столбцов диапазон статистических данных будет выведен для каждого столбца или для каждой строки входного диапазона в зависимости от положения переключателя.

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

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

Итоговая статистика. Установите флажок, если в выходном диапазоне необходимо получить по одному полю для каждого из следующих видов статистических данных: Среднее, Стандартная ошибка (среднего), Медиана, Мода, Стандартное отклонение, Дисперсия выборки, Эксцесс, Асимметричность, Интервал, Минимум, Максимум, Сумма, Счет, Наибольшее (#), Наименьшее (#), Уровень надежности.

180