• Прогрессии.
• Построение графиков функций.
• Решение уравнений.
• Встроенные функции анализа.
• Поиск решения.
• Пакет анализа.

Анализу данных в Excel недаром отведена целая глава. Средства анализа, от простых встроенных функций до выполненных в виде надстроек мощных пакетов статистического и финансового анализа, могут быть использованы для решения самых разных задач и делают Excel пакетом, сравнимым по функциональным возможностям с такими профессиональными пакетами для проведения математических расчетов, как Mathemathiсa и Statistica.

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

Прогрессии

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

Прогрессии

Простейшим способом построения прогрессий в Excel является применение команды ГлавнаяРедактированиеЗаполнитьПрогрессия. Эта команда позволяет заполнять ячейки не только одинаковыми значениями по образцу, но и арифметическими или геометрическими прогрессиями, основанными на заданной величине шага или на автоматической величине шага, если заполнение происходит на основе выделенного диапазона ячеек.

Упражнение

Создадим простейшую арифметическую прогрессию на основе заданных параметров.

1. Создайте новую книгу Excel.
2. Введите в ячейку K10 значение 1.
3. Выделите мышью ячейки K10…K20.
4. Выберите команду ГлавнаяРедактированиеЗаполнитьПрогрессия. На экран будет выведено окно настройки параметров прогрессии (рис. 9.1).

Рис. 9.1. Окно настройки параметров прогрессии

1. Как видите, в этом окне можно задавать разные типы прогрессий, не только арифметическую и геометрическую, но и линию времени. Догадливая электронная таблица, базируясь на выделенном нами фрагменте столбца, сама выбрала расположение прогрессии, установив переключатель По столбцам. Шаг, который предлагается нам, по умолчанию выбран равным 1, а тип прогрессии — Арифметическая. Оставьте все эти параметры без изменений и щелкните на кнопке OK. Выделенный нами фрагмент столбца заполнится значениями от 1 до 11, образовав арифметическую прогрессию с шагом 1 (каждое последующее значение больше предыдущего на единицу).
2. Снова выберите команду ГлавнаяРедактированиеЗаполнитьПрогрессия (ячейки K10…K20 должны быть выделены) и введите величину шага 2, оставив остальные параметры без изменения. Щелкните на кнопке OK и посмотрите на результат. Да, шаг прогрессии изменился, и ячейки заполнились цифрами от 1 до 21.
3. Выделите ячейки K0…K10. Вновь вернитесь к команде Прогрессия и, не меняя ничего, щелкните на кнопке OK. Ничего и не происходит. И снова выберите команду Прогрессия, только на этот раз установив флажок Автоматическое определение шага. После щелчка на кнопке OK ячейки с K9 по K0 будут заполнены убывающими вверх значениями от 0 до –8. То есть электронная таблица «понимает», что направление вправо и вниз от ячейки — это направление положительных приращений, а влево и вверх — отрицательных.
4. Но это далеко не все, что может сама «сообразить» электронная таблица. Продолжим наши исследования. В ячейке К10 у вас сейчас уже есть значение 1. Введите в ячейку L10 значение 2. Выделите ячейки K10…T10, затем выберите команду Прогрессия и, ничего не меняя в настройке, щелкните на кнопке OK. Выделенный диапазон заполнится значениями арифметической прогрессии от 1 до 10.
5. В ячейке K11 у вас в данный момент находится значение 3. Введите в ячейку L11 значение 6, выделите диапазон K11…T11, выберите команду Прогрессия, установите флажок Автоматическое определение шага и щелкните на кнопке OK. Результат покажет вам, что Excel автоматически различает шаг, если он задан в двух последовательных ячейках, и выделенный диапазон заполнится арифметической прогрессией с шагом 2.
6. Удалите в диапазоне K11…T11 все значения, кроме двух первых (3 и 6). Введите в ячейку M11 значение 7. Выделите диапазон

Построение графиков функций

K11…T11, выберите команду Прогрессия, установите флажок Автоматическое определение шага и щелкните на кнопке OK. Вы увидите, что диапазон заполнен арифметической прогрессией с шагом 2,33333…. Таким образом, Excel автоматически определяет шаг не только для явных случаев, но и для случаев, когда в начале диапазона находится несколько ячеек с разным шагом. Тогда шаг прогрессии из этих значений находится способом наименьших квадратов с последующей линейной аппроксимацией.

1. И еще несколько штрихов к вопросу о применении прогрессий. Введите в ячейку M13 значение 1. Удерживая нажатую клавишу Ctrl, щелкните последовательно на ячейках M15, M17, M19 и M21. Эти ячейки будут выделены. Затем выберите уже ставшую нам привычной команду Прогрессия, установите переключатель По столбцам и щелкните на кнопке OK. Результат покажет вам, что для ввода прогрессии диапазон ячеек не обязательно должен быть непрерывным.
2. Выделите диапазон ячеек M13…T21, выберите команду Прогрессия, установите переключатель По строкам и щелкните на кнопке OK. Так вы получите пример того, что прогрессию можно вводить одновременно в нескольких диапазонах.

Построение графиков функций

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

Упражнение

Операция построения графиков функций в Excel может быть практически полностью автоматизирована.

1. Создайте новую книгу Excel. Начнем с построения простого графика линейной зависимости типа y = kx (а точнее y = 2x).
2. Создайте список значений аргумента в диапазоне [-10; 10]. Для этого введите в ячейку A1 значение –10, выберите команду Глав ­ наяРедактированиеЗаполнитьПрогрессия, установите переключатель По столбцам, введите в поле Предельное значение число 10

и затем щелкните на кнопке OK. Столбец будет заполнен значениями аргумента.

1. Для создания значений функции заполните следующий столбец. Щелкните в ячейке B1. Введите в строку формул символы =2*, затем щелкните в ячейке A1 (в строке формул отобразится формула =2*A1) и нажмите клавишу Enter.
2. Выделите диапазон B1…B21 (этот диапазон включает в себя столько же строк, сколько и диапазон аргументов в столбце А). Выберите команду ГлавнаяРедактированиеЗаполнитьВниз, и столбец B будет заполнен значениями функции.
3. Выделите мышью все значения в столбце B. Выполните команду ВставкаДиаграммыГрафикВсе типы диаграммТочечнаяТочечная с гладкими кривыми. В лист будет вставлена диаграмма с изображением нашего линейного графика.
4. Щелкните правой кнопкой мыши на диаграмме и выполните команду Выбрать данные. В окне, выведенном на экран, выберите слева раздел Подписи горизонтальной оси и щелкните в нем на кнопке Изменить. Когда на экран будет выведено окно выбора диапазона, выделите мышью значения столбца А.
5. Выделив данные, щелкните на кнопке OK в одном окне, во втором, и все… График готов. Результат показан на рис. 9.2.

Рис. 9.2. График линейной функции

1. Следующим будет график тригонометрической функции y = cos(x). Щелкните в ячейке A1. Оставьте введенное в ней значение –10 без изменений, выберите команду ГлавнаяРедактирова ниеЗаполнитьПрогрессия, установите переключатель По столбцам, за-

Построение графиков функций

дайте значение шага 0,5, предельное значение — 10 и щелкните на кнопке OK.

1. Щелкните в ячейке B1. В строке формул введите формулу =COS(A1) и нажмите клавишу Enter. Выделите диапазон в столбце B, совпадающий по количеству строк с диапазоном в столбце А. Выберите команду ГлавнаяРедактированиеЗаполнитьВниз.
2. Повторите шаги 5, 6 и 7. В результате вы получите график тригонометрической функции (рис. 9.3). Этот график можно несколько

усложнить, сделав из него график функции

3

)

cos(

x

x

y

+

=

.

Рис. 9.3. График тригонометрической функции

1. Щелкните в ячейке B1 и в строке формул введите следующую формулу:

….=(COS(A1)+(A1)^(1/3))

1. Выделите диапазон значений в столбце B и выберите команду ПравкаЗаполнитьВниз.
2. Повторите шаги 5, 6 и 7. В результате вы получите график, показанный на рис. 9.4. Еще более усложним функцию y= cos(3x)x+3 x .
3. Для построения графика этой функции введите в строку формул следующее выражение:

….=(COS(A1)+(A1)^(1/3))/(A1)^(1/3)

Рис. 9.4. График сложной функции

1. Повторите шаги 12 и 13. В результате должен получиться график, показанный на рис. 9.5.

Рис. 9.5. График еще более сложной функции

Таким образом, в Excel можно строить графики практически любых функций. Уменьшив шаг разбивки значений, можно добиться более гладкого рисунка кривой.

Решение уравнений

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

Решение уравнений

Упражнение

Для демонстрации методов решения уравнений в Excel возьмем в качестве примера логарифмическую функцию y = ln(x), преобразовав ее в уравнение ln(x) = 10.

1. Щелкните в ячейке B1, в строке формул введите выражение =Ln(A1) и нажмите клавишу Enter.
2. Выберите команду ДанныеРабота с даннымиАнализ «Что­если»Подбор параметра. На экран будет выведено диалоговое окно, показанное на рис. 9.6.

Рис. 9.6. Подбор параметра

1. В поле Установить в ячейке введите ссылку на ячейку, в которой содержится формула уравнения. В поле Значение введите значение, которое находится по другую сторону знака равенства (в нашем случае 10). В поле Изменяя значение ячейки введите ссылку на ячейку, в которой содержится значение переменной уравнения (А1).
2. Щелкните на кнопке OK. Вы получите результат в диалоговом окне (рис. 9.7).

Рис. 9.7. Результат подбора параметра

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

Встроенные функции анализа

Говоря об инструментах анализа, нельзя не сказать, что многие из встроенных функций Excel уже являются готовыми инструментами анализа. Я имею в виду, в частности, статистические функции. Например, функция КОРРЕЛ возвращает коэффициент корреляции Пирсона между двумя массивами чисел, то есть позволяет оценить (проанализировать), есть ли между этими массивами связь, каков ее вид (прямая или обратная, полная или неполная) и насколько эта связь сильна.

Упражнение

Испытаем встроенные функции анализа на практике, но сначала вспомним, что такое коэффициент корреляции. Коэффициентом корреляции называют число, которое определяет взаимозависимость (связь) между наборами данных и может изменяться в диапазоне от –1 до +1. Если коэффициент корреляции равен 0, значит, связь между наборами данных отсутствует. Чем ближе коэффициент корреляции к единице, тем сильнее связь. При прямой корреляции, когда возрастанию значений одного ряда соответствует возрастание значений другого ряда, коэффициент имеет положительный знак, при обратной корреляции, когда возрастанию значений одного ряда соответствует убывание значений второго ряда, коэффициент отрицательный.

1. На новом листе в Excel введите в ячейку A1 значение 1. Заполните ячейки A1…A10 арифметической прогрессией с шагом 1 так, чтобы в ячейке A10 было значение 10. Введите в ячейку B1 значение 0,5 и заполните ячейки B1…B10 арифметической прогрессией с шагом 0,5. Так мы подготовим два массива данных.
2. Даже невооруженным глазом видно, что между этими данными есть связь, потому что каждому возрастанию значения в столбце A соответствует возрастание значения в столбце B. Это правило без исключений, поэтому в результате мы должны получить по-

ложительный коэффициент корреляции, равный +1, то есть полную положительную корреляцию. Щелкните сначала в ячейке C3, а затем — на кнопке Вставить функцию рядом со строкой формул (рис. 9.8).

Рис. 9.8. Кнопка вставки функции

1. В открывшемся окне мастера функций выберите категорию Статистические и функцию КОРРЕЛ.
2. В следующем окне в поле Массив1 введите список значений столбца A (A1…A10), а в поле Массив2 — список значений столбца B (B1… B10). После щелчка на кнопке OK в ячейке C1 вы получите, как и ожидалось, значение 1.
3. Вводите в ячейки B1…B10 последовательно значения 15, 14, …, 7, 6. Наблюдайте, как изменяется коэффициент корреляции.

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

• Программа Excel оснащена богатым набором статистических функций, значение которых для быстрой и элегантной оценки различного рода вероятностных и статистических параметров данных трудно переоценить.
• Для того чтобы применять статистические функции, надо разбираться в математической статистике.

Поиск решения

Пакет поиска решения является надстройкой Excel. Это значит, что в стандартной конфигурации Excel по умолчанию данный пакет не устанавливается. Для того чтобы активизировать его, необходимо в меню кнопки Office выбрать команду Параметры ExcelСервисНадстройкиУправлениеНадстройки Excel и в открывшемся окне щелкнуть на кнопке Перейти. На экран будет выведено окно со списком установленных в Excel надстроек. Найдите в списке пункт Поиск решения, установите рядом флажок и щелкните на кнопке OK. Инструмент будет активизирован, и в группе инструментов ДанныеАнализ появится новая команда Поиск решения.

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

• такое распределение производственных ресурсов, при котором прибыль будет максимальной;
• такое распределение инвестиций, при котором риск будет минимальным;
• решения уравнений с несколькими неизвестными с заданными граничными условиями для переменных.

Основные параметры поиска решения задаются в окне Поиск решения, показанном на рис. 9.9.

Рис. 9.9. Поиск решения

• В поле Установить целевую ячейку вводится ссылка на ячейку, в которой в результате поиска решения должно быть получено искомое значение.
• Группа переключателей Равной позволяет выбрать, что именно должно считаться решением:
  • Максимальному значению — решением является максимально воз-

можное значение;

• Минимальному значению — решением является минимально возможное значение;
• Значению — решением является конкретное числовое значение в целевой ячейке, которое нужно ввести в расположенное рядом поле.

ячеек. Кнопка Предположить позволяет Excel автоматически просмотреть, от каких ячеек зависит конечный результат, и ввести их в поле Изменяя ячейки автоматически.

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

Рис. 9.10. Добавление ограничения

• Кнопки Изменить и Удалить служат для редактирования или удаления уже установленных ограничений соответственно.
• Кнопка Параметры выводит на экран окно настройки параметров поиска решения (рис. 9.11).

Рассмотрим окно настройки параметров поиска решения подробнее, поскольку параметры, настраиваемые в этом окне, оказывают серьезное влияние на точность и скорость вычислений.

• В поле Максимальное время вводится время в секундах, по истечении которого поиск решения будет прекращен даже в том случае, если решение не найдено или не оптимизировано. Допустимые значения — от 1 до 32 767.

Рис. 9.11. Окно настройки параметров поиска решения

• В поле Предельное число итераций вводится количество циклов вычислений, после которого поиск решения будет прекращен даже в том случае, если решение не найдено или не оптимизировано. Допустимые значения — от 1 до 32 767.
• В поле Относительная погрешность указывается десятичная дробь

в интервале от 0 до 1. Чем она меньше, тем выше точность вычислений.

• Значение, вводимое в поле Допустимое отклонение, — это величина

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

• Если за последние 5 итераций относительное изменение значения в целевой ячейке оказывается меньше числа, указанного в поле Сходимость, решение считается найденным.
• Флажок Линейная модель нужно установить для ускорения вычислений, если вы решаете линейную задачу оптимизации (задачу, выраженную в линейных уравнениях).
• Установка флажка Неотрицательные значения ограничивает диапазон изменения переменных только положительными значениями.
• Установите флажок Автоматическое масштабирование, если значения

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

• Флажок Показывать результаты итераций нужно установить, если вы хотите видеть весь ход решения, отслеживая значение целевой функции и переменных после каждой итерации.
• Группа переключателей Оценки позволяет выбрать метод экстраполяции:
  • Линейная — линейная экстраполяция дает более точные решения при линейных задачах;
  • Квадратичная — квадратичная экстраполяция лучше работает при нелинейных задачах.
• Группа переключателей Разности позволяет выбрать дифференциалы (производные):
  • Прямые — прямые производные следует использовать, если анализируемая функция гладкая;
  • Центральные — центральные производные следует использовать, если в анализируемой функции есть точки разрыва.
• Группа переключателей Метод поиска позволяет выбрать метод поиска решения:
  • Ньютона — метод Ньютона обеспечивает высокую скорость вычислений, но требует больших затрат памяти;
  • Сопряженных градиентов — при использовании метода сопряженных градиентов памяти нужно на порядок меньше, но и количество итераций возрастет на порядок.
• Кнопки Сохранить модель и Загрузить модель позволяют сохранять установленные параметры прямо в ячейках таблицы, называемых областью модели. Таким образом, вы можете настроить несколько различных наборов параметров и производить поиск одного и того же решения с разными моделями.

Упражнение

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

1. Откройте новый лист в Excel.
2. В ячейку A1 введите значение 2. 3. В ячейку B1 введите значение 1.
3. Для поиска решения используем функцию двух переменных z₌ x^{2 +} y³ . При задании фиксированного значения z эта функция y−x

превращается в уравнение с двумя переменными, поэтому в ячейку C1 введите следующую формулу:

=(A1^2-B1^3)/(B1-A1)

1. Щелкните в ячейке C1 и выберите команду ДанныеАнализПоиск решения. На экран будет выведено окно Поиск решения, показанное на рис. 9.9.
2. Задайте следующие параметры:
   • в поле Установить целевую ячейку введите адрес $C$1;
   • в группе Равной установите переключатель Значению и введите число 20 в расположенное рядом поле;
   • в поле Изменяя ячейки введите диапазон $A$1:$B$1 (это значение будет введено автоматически, если вы щелкнете на кнопке Предположить).
3. Щелкните на кнопке Выполнить.

Решение будет найдено практически мгновенно, что не удивительно: уравнения с двумя переменными либо не имеют решений вообще, либо имеют множество решений, и найти одно из них можно, просто последовательно изменяя значение одной (первой) переменной. Значение (начальное) второй при этом остается фиксированным. Это говорит о том, что решение таких задач зависит от начальных значений. На самом деле, если вы введете в качестве начальных значений переменных в ячейки A1 и B1 значения –1 и 1, вы получите иное решение. Это также говорит о том, что на решение подобного рода задач большое влияние оказывают граничные условия.

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

–1 и 1, выберем команду ДанныеАнализПоиск решения и зададим поиск максимального значения. Вы получите значение целевой функции 2 196 750 при значениях переменных –2,80932447795488 и –2,80932310682991. Однако, задав начальные значения 5 и 2, вы получите уже иное решение. То есть, когда функция имеет много

максимумов, путем поиска решения обнаруживается ближайший, и на этом поиск останавливается.

Попробуйте найти решение при наличии ограничивающих значений. Для ячейки A1 это должны быть два значения, >=1 и <=2, а для ячейки B1 — значения >=3 и <=4. В этом случае и максимальное, и минимальное значения будут найдены однозначно.

Пакет анализа

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

Ниже перечислены виды анализа, поддерживаемые пакетом анализа.

• Однофакторный дисперсионный анализ используется для проверки гипотезы о сходстве средних значений двух или более выборок, принадлежащих одной и той же генеральной совокупности.
• Двухфакторный дисперсионный анализ с повторениями представляет собой более сложный вариант однофакторного анализа, включающего более чем одну выборку для каждой группы данных.
• Двухфакторный дисперсионный анализ без повторения представляет собой двухфакторный анализ дисперсии, не включающий более одной выборки на группу.
• Корреляция используется для количественной оценки взаимосвязи двух наборов данных, представленных в безразмерном виде.
• Ковариация служит для вычисления среднего произведения отклонений точек данных от относительных средних. Ковариация является мерой связи между двумя диапазонами данных.
• Описательная статистика обеспечивает расчет набора элементарных статистик для одномерного набора данных (мода, медиана, дисперсия и т. д.).
• Экспоненциальное сглаживание предназначается для предсказания значения на основе прогноза для предыдущего периода, скорректированного с учетом погрешностей в этом прогнозе.
• Двухвыборочный F-тест для дисперсии применяется для сравнения дисперсий двух генеральных совокупностей.
• Анализ Фурье предназначается для решения задач в линейных системах и анализа периодических данных с использованием метода быстрого преобразования Фурье.
• Гистограмма используется для вычисления выборочных и интегральных частот попадания данных в указанные интервалы значений, при этом генерируются числа попаданий для заданного диапазона ячеек.
• Скользящее среднее служит для расчета значений в прогнозируемом периоде на основе среднего значения переменной для указанного числа предшествующих периодов.
• Генерация случайных чисел обеспечивает заполнение диапазона случайными числами, извлеченными из одного или нескольких распределений. С помощью данного инструмента можно моделировать объекты, имеющие случайную природу, по известному распределению вероятностей.
• Ранг и перцентиль — вывод таблицы, содержащей порядковый и процентный ранги для каждого значения в наборе данных. Данный инструмент может быть применен для анализа относительного взаиморасположения данных в наборе.
• Регрессия используется для анализа воздействия на отдельную зависимую переменную значений одной или более независимых переменных.
• Выборка — этот инструмент создает выборку из генеральной совокупности, рассматривая входной диапазон как генеральную совокупность.
• Двухвыборочный t-тест с одинаковыми дисперсиями (двухвыборочный t-тест Стьюдента) служит для проверки гипотезы о равенстве средних для двух выборок. Эта форма t-теста предполагает совпадение дисперсий генеральных совокупностей.
• Двухвыборочный t-тест с разными дисперсиями используется для проверки гипотезы о равенстве средних для двух выборок данных из разных генеральных совокупностей. Эта форма t-теста предполагает несовпадение дисперсий генеральных совокупностей.
• Парный двухвыборочный t-тест для средних применяется для проверки гипотезы о различии средних для двух выборок данных. При этом не предполагается равенство дисперсий генеральных совокупностей, из которых выбраны данные.
• Двухвыборочный z-тест для средних позволяет проверить гипотезу о различии между средними двух генеральных совокупностей.

Упражнение

В качестве упражнений рассмотрим несколько инструментов из пакета анализа.

1. Создайте новый лист Excel.
2. Выберите команду ДанныеАнализАнализ данных. В списке Инструменты анализа открывшегося окна выберите пункт Генерация случайных чисел и щелкните на кнопке OK.
3. Заполните окно параметров генерации следующим образом:
   • в поле Число переменных введите 1;
   • в поле Число случайных чисел введите 100;
   • в раскрывающемся списке Распределение выберите пункт Нормальное;
   • остальные параметры оставьте без изменений.
4. В качестве выходного диапазона укажите столбец A (для этого установите переключатель Выходной интервал, щелкните сначала на кнопке в конце расположенного рядом поля, затем — на заголовке столбца A и нажмите клавишу Enter). Щелкните на кнопке OK. Столбец A заполнится значениями случайных чисел, распределенных по нормальному закону.
5. Повторите шаги 2, 3 и 4, выбирая в качестве выходного диапазона поочередно столбцы B, C и D, а в качестве типа распределения — равномерное, биноминальное и распределение Пуассона. Для биноминального распределения в поле Значение р введите 0,5, а в поле Число испытаний — 100. Для распределения Пуассона в поле Лямбда введите 12.

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

Упражнение

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

1. Выберите команду ДанныеАнализАнализ данных. В списке Инструменты анализа открывшегося окна выберите пункт Гистограмма и щелкните на кнопке OK.
2. В качестве входного интервала укажите диапазон ячеек $A$1:$A$100. Установите переключатель Новый рабочий лист (задайте для листа название Гист1) и флажок Вывод графика. Затем щелкните на кнопке OK. Будет создан новый лист, на котором отобразится таблица частот и график гистограммы.
3. Повторите предыдущий шаг для оставшихся трех наборов данных, давая новым листам названия Гист2, Гист3 и Гист4.
4. В результате вы получите четыре графических представления для имеющихся у вас распределений (рис. 9.12).

Рис. 9.12. Четыре графика распределений

Упражнение

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

1. Выберите команду ДанныеАнализАнализ данных. В списке Инструменты анализа открывшегося окна выберите пункт Выборка и щелкните на кнопке OK.
2. В качестве входного интервала введите диапазон ячеек $C$1:$C$100. В группе Метод выборки установите переключатель Случайный, а в поле Число выборок введите значение 20. Установите переключатель Новый рабочий лист и дайте новому листу имя Выбор1. Затем щелкните на кнопке OK.
3. Выберите команду ДанныеАнализАнализ данных. В списке Инструменты анализа открывшегося окна выберите пункт Выборка и щелкните на кнопке OK.
4. В качестве входного интервала введите диапазон ячеек $C$1:$C$100. Установите переключатель Новый рабочий лист и дайте новому листу имя Стат1. Затем щелкните на кнопке OK. На листе Стат1 вы получите таблицу с элементарными статистиками для данной выборки из ста членов.
5. Выберите команду ДанныеАнализАнализ данных. В списке Инструменты анализа открывшегося окна выберите пункт Выборка и щелкните на кнопке OK.
6. В качестве входного интервала введите диапазон ячеек $A$1:$A$20 на листе Выбор1. Установите переключатель Новый рабочий лист и дайте новому листу имя Стат2. Затем щелкните на кнопке OK. На листе Стат2 вы получите таблицу с элементарными статистиками для данной выборки из двадцати членов.
7. Полученные данные можно свести в одну таблицу и сравнить при помощи команды ДанныеАнализАнализ данныхОписательная статистика (рис. 9.13).

Рис. 9.13. Сравнение генеральной и выборочной статистик