Анализ данных

4.1 Таблица данных

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

4.1.1 Таблицы данных с двумя переменными

Таблицы данных с двумя переменными создаются с целью исследования влияния на результат одной формулы сразу двух переменных.

4.1.2 Таблицы данных с одной переменной

Таблицы данных с одной переменной создаются с целью исследования влияния на результат одной формулы одной переменной.

4.2 Диспетчер сценариев

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

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

4.3.1 Подбор параметра в ячейке

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

4.3.2 Поиск оптимального значения в ячейке

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

При поиске решения имеется возможность задать одно или несколько ограничений‑ условий, которые должны выполняться при поиске решения. Ячейки рабочего листа, используемые для поиска решения, должны быть обязательно связаны формулами.

4.4 Статистический анализ

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

4.4.1 Оценка параметров распределения

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

4.4.2 Генерация случайных чисел

Для генерации последовательности случайных чисел с равномерным распределением в интервале от 0 до 1 можно использовать встроенную функцию СЛЧИС(RAND). Надстройка Пакет анализа позволяет генерировать случайные числа следующих типов распределений: равномерное, нормальное, Бернулли, биномиальное, Пуассона.

4.4.3 Линейная и экспоненциальная регрессия

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

4.5 Упражнения по теме 4

4.5.1 Подбор параметра для достижения результата

При выполнении задания 4.5.1 мы попрактиковались в использовании функции Подбор параметра.

4.5.2 Анализ данных с помощью таблиц данных

При выполнении задания 4.5.2 мы попрактиковались в использовании анализа данных.

4.5.3 Построение графиков на основе таблиц данных

При выполнении задания 4.5.3 мы попрактиковались в построении графика по данным таблицы.

4.5.4 Решение задачи оптимизации

При выполнении задания 4.5.4 мы попрактиковались в использовании команды Поиск решения

4.5.5 Работа со сценариями

При выполнении задания 4.5.5 мы попрактиковались в использовании Диспетчера сценариев.

4.5.6 Статистический анализ данных

При выполнении задания 4.5.6 мы попрактиковались в использовании Статистического анализа.