Тема: «Анализ данных в ms Excel»
1. Прогнозирование в Microsoft Excel
Microsoft Excel —это универсальная система обработки данных, которая может использоваться для анализа и представления данных в наглядной форме. Одной из наиболее часто используемых возможностей Excel является экстраполяция ряда данных — например, для анализа имеющихся фактических данных, оценки имеющейся тенденции их изменения и получения на этой основе краткосрочного прогноза на будущее. В Excel имеется целый ряд возможностей для проведения подобного анализа данных.
Пусть у нас имеются данные об объемах ежеквартальных продаж за три последних года, и мы хотели бы спрогнозировать динамику роста объемов продаж на ближайший год. Допустим, что наши данные представляют собой следующую таблицу (рис.1):
Рисунок 1 Исходные данные для экстраполяции
Выполнить обыкновенную линейную экстраполяцию проще всего с помощью выделения экстраполируемого ряда данных, а затем буксировки мышью маркера заполнения. Сначала выделим диапазон с исходными данными:
B3 <Ctrl>+<Shift>+<>
- именно так проще всего выделить нужный диапазон. Затем установите
протащите его на четыре ячейки вниз. В результате новые ячейки ряда будут автоматически заполнены значениями линейной экстраполяции ряда.
Того же результата можно добиться и с помощью выполнения следующих действий:
B3:B18 Правка Заполнить Прогрессия… Тип автозаполнение OK
Кстати, можно таким же образом заполнить и диапазон A15:A18 нужными текстовыми значениями. Повторите те же самые действия для столбца A — и благодаря наличию стандартных списков Excel ячейки окажутся заполненными нужными текстовыми значениями. На самом деле, можно было бы воспользоваться автозаполнением для двух столбцов одновременно — как с помощью протаскивания маркера, так и с помощью диалогового окна Прогрессия.
Вычислить все значения для линии линейного тренда в соседнем с исходной таблицей столбце можно следующим образом:
B3:B14 Правка Копировать C3 Правка Вставить C3:C18 Правка Заполнить Прогрессия… Автоматическое определение шага OK
Рисунок 2
Таким образом, можно очень легко и просто получить интересующие вас численные значения. Все прекрасно, если исходные данные являются фактическими данными за прошедшие периоды времени и уже не будут изменяться.
Если, однако, изучаемые данные могут быть уточнены или изменены по какой-либо причине, то для того чтобы получить новые, измененные значения для линии тренда, потребуется заново повторить все описанные выше действия — ведь ячейки при этом способе вычисления заполняются обыкновенными числовыми значениями, которые надо будет заново пересчитывать при изменении исходных данных. Если вы хотите получить такие значения для линии тренда, которые бы автоматически изменялись при изменении соответствующих исходных данных, то для этого потребуется использовать формулы, использующие исходные числовые значения в качестве аргументов.
Для решения этой задачи можно воспользоваться функциями рабочего листа ТЕНДЕНЦИЯ (TREND) для линейной экстраполяции или РОСТ (GROWTH) для экспоненциальной экстраполяции, а также техникой формул массив. Формулы массива способны использовать в качестве аргументов массивы, т.е. диапазоны ячеек рабочего листа, и могут возвращать в качестве результата как одно конкретное значение, так и целый массив значений. Ниже мы приведем решение для случая линейной экстраполяции с использованием формул массива.
Сначала получим значения линии тренда, аппроксимирующие уже известные данные исходного диапазона. Зададим формулу массива в соответствующем по размерам диапазоне ячеек, причем для ввода функции и ее аргумента воспользуемся Мастером функций:
E3 Вставка функции {на панели инструментов Стандартная} Категория: Статистические Функция: ТЕНДЕНЦИЯ ОК Изв_знач_y := B3:B14 ОК E3:E14 <F2>; <Ctrl>+<Shift>+<Enter>
Обратите внимание на способ, которым мы осуществляем ввод формулы массива в диапазон ячеек. После завершения работы с Мастером функций в первой ячейке выделенного диапазона оказывается введена обычная формула, а не формула массива для всех выделенных ячеек. Нажатие клавиши <F2> вновь переводит нас в режим редактирования, после чего и используется специальная комбинация клавиш <Ctrl>+<Shift>+<Enter>, которой требуется заканчивать ввод формулы массива.
Полученные значения могут быть использованы в качестве аргумента-массива "известные значения по Y" при вычислении прогноза. Однако нам еще потребуются два других аргумента: массивы известных значений по X и новых значений по X. Заготовим нужные данные в соседнем столбце:
D3 D3 := 1 D3:D18 Правка Заполнить Прогрессия… по столбцам арифметическая Шаг := 1 OK
Наконец, зададим еще одну формулу массива, вычисляющую прогнозируемые значения. На сей раз, поскольку мы хотим заняться прогнозированием, для функции ТЕНДЕНЦИЯ необходимо использовать три аргумента. Кроме известных значений исследуемой зависимости (известные значения y), необходимо также указать те точки, в которых она принимает эти значения (известные значения x), и те точки, для которых строится прогноз (новые значения x):
E15:E18 Вставка функции Категория: Статистические Функция: ТЕНДЕНЦИЯ ОК Изв_знач_y := B3:B14 Изв_знач_x := D3:D14 Нов_знач_x := D15:D18 ОК <F2>; <Ctrl>+<Shift>+<Enter>
Как нетрудно видеть, отображаемые формулами значения совпадают с уже имеющимися численными значениями в диапазоне C3:С18. Вся разница лишь в том, что при изменении исходных значений в столбце B числа в столбце C останутся прежними, в то время как формулы в столбце E автоматически изменят свои значения.
Рисунок 3 Окончательный вид рабочего листа с результатами прогнозирования
Напоследок давайте вставим на рабочий лист в качестве иллюстрации график с исходными данными и спрогнозированными значениями, а также линией тренда. Сначала построим диаграмму-график:
Вставка Диаграмма… Стандартные Тип: График Далее Диапазон данных Диапазон: B3:B18 Далее Заголовки Название диаграммы := Линейный тренд Легенда (снять флажок) Добавить легенду Готово
Хотя кнопка Готово уже нажата, работа над диаграммой еще не закончена. Прежде всего, отформатированный стандартным образом график не слишком нагляден — все значения попали в верхнюю часть диаграммы, а нижняя часть не используется. Это можно поправить с помощью форматирования оси значений. Щелкните по этой оси, а затем выполните следующие действия:
Формат Выделенная ось… Шкала минимальное значение := 200000 OK
Теперь займемся горизонтальной осью. Выделите ее, а затем отформатируйте следующим образом:
Формат Выделенная ось… Шкала Число категорий между делениями := 4 Вид Основные деления пересекают ось Промежуточные деления наружу Метки делений нет OK
А теперь, наконец, добавим на диаграмму линию тренда. Для этого щелкните по графику, а затем выполните следующие действия:
Диаграмма Добавить линию тренда… Тип Линейная Параметры показывать уравнение на диаграмме OK
Вот как теперь выглядит рабочий лист с прогнозом на основе линейной экстраполяции (рис.4):
Рисунок 4 Окончательный вид рабочего листа с результатами прогнозирования