Статистичні моделі базуються на використанні математичних моделей і методів, призначених для прийняття рішень під час пошуку, аналізу та оцінювання можливих варіантів рішень у ситуаціях, що характеризуються невизначеністю, пов’язаною як із статистичним характером аналізованих явищ, так і з неповнотою знань щодо значень, які можуть мати ті чи інші фактори.
Excel має значні можливості для проведення статистичного оцінювання даних. Деякі з них є вбудованими, інші доступні лише після встановлення надбудови Пакет аналізу. З допомогою останнього можна розв’язувати складні статистичні задачі. До складу пакета аналізу входять засоби проведення дисперсійного, кореляційного, коваріаційного, регресійного аналізу та низка інших засобів для розв’язання статистичних та інженерних задач.
Для аналізу даних з допомогою інструментів пакета аналізу достатньо вказати вхідні дані і вибрати певні параметри. Аналіз проводиться з допомогою відповідної статистичної макрофункції, а результат розміщується у вказаному вихідному діапазоні клітин робочого аркуша. Є в пакеті аналізу і засоби, що дають можливість показати результати аналізу графічно.
Оскільки пакет аналізу — це надбудова, то спочатку необхідно забезпечити доступ до нього: вибрати команду Сервіс/Надбудова, прокрутити список надбудов і встановити прапорець Пакет аналізу. Після цього його можна використовувати так:
1. У меню Сервіс вибирається команда Аналіз даних.
2. У списку Інструменти аналізу вибирається потрібний
рядок.
3. Уводиться вхідний та вихідний діапазони і встановлюються потрібні параметри.
Для успішного застосування процедур аналізу (правильного вибору інструмента та параметрів) необхідні певні знання у сфері статистичних та інженерних розрахунків, для яких ці інструменти було розроблено.
У маркетинговій діяльності часто виникають ситуації, за яких доводиться приймати рішення, виходячи з недостатньо визначених умов та оперуючи не завжди надійною інформацією. У таких випадках виникає потреба в оцінюванні ймовірності досягнення тих чи інших результатів. Основними методами, які дають змогу приймати маркетингові рішення з урахуванням фактора невизначеності і які підтримує Excel, є стандартне відхилення, довірчі інтервали та регресійний аналіз.
Імітаційні моделі є засобом вивчення поведінки об’єктів чи систем в умовах, що визначаються дослідником, шляхом нагромадження та аналізу даних, отримуваних у процесі проведення експериментів із моделлю. Метою імітаційного моделювання може бути вивчення впливу на об’єкт змін у його структурі та (або) у зовнішніх умовах його функціонування. Дані, нагромаджувані в процесі моделювання, використовуються або для проведення ретроспективного аналізу, або для визначення спектра допустимих сценаріїв майбутнього розвитку.
Для генерації випадкових чисел, необхідних для проведення імітаційного моделювання, можна використовувати декілька засобів Excel: функції СЛЧИС(), СЛУЧМЕЖДУ() та інструмент пакета аналізу Генерація випадкових чисел.
Функція СЛЧИС() повертає рівномірно розподілене випадкове число між 0 та 1. Нове випадкове число повертається цією функцією кожного разу, коли перераховується робочий аркуш. Якщо потрібно, щоб отримане випадкове число не змінювалося, можна ввести =СЛЧИС() у рядок формул і натиснути клавішу F9. Щоб отримати випадкове дійсне число між а і b, можна використати таку формулу: СЛЧИС() * (b – а) + а.
Функція СЛУЧМЕЖДУ (нижня межа, верхня межа) повертає розподілене ціле випадкове число між двома заданими числами. Вона має два аргументи: нижня межа — це найменше ціле число, що повертається цією функцією, а верхня межа — це найбільше ціле число, що повертається функцією.
Інструмент Генерація випадкових чисел виводить діалогове вікно, в якому потрібно вказати кількість змінних (кількість стовпців значень випадкових чисел, які треба розмістити у вихідному діапазоні); кількість випадкових чисел (кількість випадкових значень, які потрібно вивести для кожної змінної); вид розподілення (рівномірне, нормальне, Бернуллі, біноміальне, Пуассона, модельне або дискретне); параметри вибраного розподілення; випадкове розсіювання та вихідний діапазон, в якому будуть розміщені згенеровані випадкові числа.
2. Технології параметричного аналізу
методами «що—якщо»
Одне з типових застосувань електронних таблиць — виконання аналізу «що—якщо». Суть аналізу «що—якщо» полягає в отриманні відповідей на питання: як вестиме себе досліджуваний об’єкт у різних ситуаціях, кожна з яких визначається певними значеннями вхідних даних? Так, за оцінювання конкретної маркетингової ситуації часто потрібно отримати відповіді на питання такого типу: що буде, якщо збільшити різницю між собівартістю і продажною ціною або збільшити заробітну плату співробітникам на 1 %? Яке зростання необхідне, якщо до початку наступного року треба досягти збільшення обсягів продажу на певну суму? Як зміниться прибуток, якщо змінити різницю між собівартістю та ціною продажу? Як відіб’ється на собівартості продукції підвищення цін на певні матеріали або на електроенергію?
Для спрощення аналізу «що—якщо» в Excel є декілька інструментів, до яких належать: Добір параметра. Таблиця підстановки даних. Диспетчер сценаріїв.
Пошук значення на вході моделі виконується так:
1. У меню Сервіс вибирається команда Добір параметра.
2. У поле Встановити в комірці вводиться посилання на клітину, що містить формулу, результат обчислення якої повинен мати потрібне значення.
3. У поле Значення вводиться значення, яке має бути отримане на виході моделі, тобто в клітині, посилання на яку було введене на попередньому кроці.
4. У поле Змінювані значення в комірці вводиться посилання на клітину на вході моделі, значення якої потрібно добрати. Значення цієї клітини має безпосередньо або через значення інших клітин впливати на результат формули, вказаної на кроці 2.
Цей інструмент дає змогу легко проаналізувати, як зміна певних значень у формулах впливає на результати, обчислювані за цими формулами. Він є засобом швидкого обчислення декількох варіантів, а також засобом перегляду і порівняння результатів різних варіантів на одному робочому аркуші. Зазвичай таку таблицю використовують як частину деякої моделі. Існує два типи таблиць підстановки даних — з однією і з двома змінними.
Таблиця підстановки з однією змінною складається з трьох частин:
— вектора-колонки (або вектора-рядка), значення елементів якого підставляються в указану клітину (клітину введення даних). Кількість елементів цього вектора залежить від кількості значень, що підставляються у клітину введення даних;
— вектора-рядка (або вектора-колонки), елементи якого містять формули, що прямо або опосередковано посилаються на клітину введення даних. Цей вектор може мати й один елемент (складатися лише з однієї формули);
—векторів-колонок (або векторів-рядків) даних. Елементи кожного з цих векторів обчислюються за однією з формул, яка знаходиться над цим вектором-колонкою (або перед цим вектором-рядком). Якщо використовується лише одна формула, то буде лише один вектор з обчисленими даними.
Слід звернути увагу на те, що клітина введення даних може входити, а може і не входити у таблицю підстановки з однією змінною. Тобто ця клітина може розміщуватись у будь-якому місці робочого аркуша.
Таблиця підстановки з однією змінною створюється у такій послідовності:
1. В окрему колонку аркуша або в окремий рядок уводиться список значень, які потрібно підставляти у клітину введення даних.
2. Якщо список значень розташовано у колонку, то формули вводяться в рядок, починаючи з клітини, що знаходиться на клітину вище та правіше від колонки значень. Якщо список значень розташовано в рядок, то формули вводяться у колонку починаючи з клітини, що знаходиться на клітину нижче й лівіше списку значень.
3. Виділяється прямокутник клітин, що містить формули та значення для підстановки в ці формули.
4. У меню Дані вибирається команда Таблиця підстановки.
5. Якщо значення для підстановки розміщено у колонку (а формули — у рядок), то посилання на клітину введення вказується в полі Підставляти значення по рядках. Якщо ж значення для підстановки розміщено в рядок (а формули — у колонку), то посилання на клітину введення даних указується в полі Підставляти значення по колонках.
Таблиця підстановки з двома змінними створюється таким чином:
1. У клітину аркуша вводиться формула, результат обчислення якої залежить від двох клітин уведення даних.
2. У ту саму колонку нижче формули вводяться значення підстановки для першої змінної. Значення підстановки для другої змінної вводяться в рядок за формулою.
3. Виділяється прямокутник клітин, що містить формулу й обидва набори даних підстановки.
4. У меню Дані обирається команда Таблиця.
5. У поле Підставляти значення по колонках уводиться посилання на одну з клітин уведення даних.
6. У поле Підставляти значення по рядках уводиться посилання на другу клітину введення даних.
Слід мати на увазі, що таблиці підстановки перераховуються кожен раз при перерахунку аркуша навіть у тому разі, коли в них не було внесено ніяких змін. Тому у випадку, коли таблиця підстановки містить значну кількість елементів, для прискорення процесу перерахунку аркуша слід так змінити параметр Обчислення на вкладці Обчислення (меню Сервіс команда Параметри), щоб автоматично перераховувався аркуш, але не таблиці. Коли ж виникає потреба у перерахунку таблиці, натискується клавіша F9.