1.5Надстройка Пакет анализа в Excel: назначение и краткая характеристика инструментов для моделирования данных (генерация случайных чисел).

В состав Microsoft Excel входит набор средств анализа данных (так называемый пакет анализа), предназначенный для решения сложных статистических и инженерных задач. Для анализа данных с помощью этого пакета следует указать входные данные и выбрать параметры; расчет будет выполнен с помощью подходящей статистической или инженерной макрофункции, а результат будет помещен в выходной диапазон. Некоторые инструменты позволяют представить результаты анализа в графическом виде. Ниже, в таблице 1.2, приведены названия инструментов, включенные в Пакет анализа, и их краткая характеристика [7].

Таблица 1.2 Краткая характеристика инструментов Пакета анализа.

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

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

Рассмотрим применение инструмента Пакета анализа Генерация случайных чисел. Инструмент Генерация случайных чисел применяется для заполнения диапазона случайными числами, извлеченными из одного или нескольких распределений. С помощью этой процедуры можно моделировать объекты, имеющие случайную природу, по известному распределению вероятностей. Например, можно использовать нормальное распределение для моделирования совокупности данных по росту людей или использовать распределение Бернулли для двух вероятных исходов, чтобы описать совокупность результатов бросания монеты.

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

Чтобы создать случайную выборку, необходимо выбрать пункт Генерация случайных чисел в диалоговом окне Анализ данных (рис. 1.4).

Рис. 1.4. – Диалоговое окно Анализ данных

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

Рисунок 1.5. – Диалоговое окно Генерация случайных чисел для задания параметров выборки

Каждый столбец содержит 15 ячеек, диапазон значений для случайных чисел – от 50 до 100 (Всего доступно 7 различных генераторов случайных чисел).

На рисунке 1.6 представлены сгенерированные случайные числа.

Рисунок 1.6. – Два набора случайных чисел в интервале от 50 до 100

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

Рисунок 1.7. – Пример использования случайных чисел для проведения анализа различных моделей, создав на них ссылки в формулах

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

2Практическая часть

2.1Разработка функции пользователя

2.1.1Постановка задачи

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

Выполнить отладку функции и проверку ее работоспособности.

2.1.2Разработка алгоритма

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

Рисунок 2.1 – Графическое представление алгоритма

2.1.3Разработка программного кода

Разработка программного кода осуществляется в окне редактора VBA Excel.

Для входа в среду VBA Excel необходимо выполнить команду Сервис/ Макрос/ Редактор Visual Basic или воспользоваться горячей клавишей Alt+F11.

Для начала набора текста функции или подпрограммы на языке VBA необходимо в окне VBA выполнить команду Insert (Вcтавить) / Module (Модуль)

Затем воспользоваться командой Insert (Вcтавить) /Procedure (Процедуру).

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

Рисунок 2.2 - Добавление функции

При написании программного кода на языке VBA следует учитывать следующие правила:

- каждая инструкция располагается на отдельной строке, длина которой не должна содержать более 1024 символов;

- при необходимости переноса инструкции на следующую строку, в конце строки вводят символ пробела и знак «нижнее подчеркивание»;

- запрещается разделять переносом строковые константы, идентификаторы переменных и имена функций и т.п.;

- возможно не более семи продолжений одной и той же строки;

- если необходимо разместить в одной строке несколько инструкций, их разделяют двоеточием.

В тексте программного кода могут использоваться комментарии, признаком которых могут быть апостроф (‘) или ключевое слово Rem.

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

Для проверки наличия аргументов при вызове функции можно использовать функцию IsMissing(Аргумент), которая возвращает значение True, если указанный параметр был опущен, и False — в противном случае. Для необязательного параметра можно задавать значение по умолчанию, которое присваивается переменной, если аргумент отсутствует.

На рисунке 2.3 отображен программный код для функции с пояснениями.

Рисунок 2.3 – Программный код для функции с пояснениями