16+
ComputerPrice
НА ГЛАВНУЮ СТАТЬИ НОВОСТИ О НАС




Яндекс цитирования


Версия для печати

Модуль поиска не установлен.

Пророчество или статистика, график и прогноз

21.04.2004

А.М. Хайт

В грядущее проникнув...

Кому из нас не хочется знать, что нас ждет завтра, послезавтра, через год. Мы замолкаем, когда радио передает прогноз погоды. Мы слушаем сообщения об экономических событиях и прогнозируем, поднимется или упадет курс доллара, цена на нефть, насколько поднимутся цены на основные продукты, одежду, мебель. "Знал бы прикуп - жил бы в Сочи", гласит карточная народная мудрость. А ведь она тоже о прогнозе: сумеешь верно оценить, что там есть невидимое, но важное - ты победитель, тебе гарантирован успех. Собственно, наука и старается создать такую модель явлений социальных и природных, которые позволяют поточнее предсказать, что же там, в будущем, произойдет. И ей, науке, это в какой-то мере удается. Заранее известно, когда будут лунные и солнечные затмения, графики приливов и отливов в любом порту близ океана позволяют обеспечить безопасную проводку и стоянку судов.

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

Смотри - и увидь

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

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

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

Итак, пусть мы собрали какие-то сведения о том, что происходило с 1990 по 2002 год, например, об объемах продаж компьютерной техники и сопутствующих материалов в Северо-Западном регионе России в долларах. Полученные сведения вносятся в книгу Excel.

На основании имеющейся информации мы хотим составить прогноз объема продаж на следующие три года (выделены курсивом). Как сделать это попроще? А как вы поступили бы, если бы никаких компьютерных инструментов у вас не было? Ведь и в древнюю докомпьютерную эпоху предсказатели были. И некоторые из этих "древних" предсказателей строили график, считая, что между годом (абсолютным или относительным) и объемом продаж есть какая-то зависимость. Относительный год - приведенный к 1989 как к нулю, т.е. совпадающий с номером по порядку.

Если график получался достаточно гладким, оракул просто продолжал его вперед (по лекалу) на три года и изрекал предсказание. Давайте заставим электронные таблицы сделать то же самое. Построим точечную диаграмму, взяв в качестве аргумента (оси Х) порядковый номер от 1989 до 2002 года, т.е. с 1-го по 13-й. При построении такой диаграммы, на втором шаге мастера следует отказаться от автоматического определения диапазона данных, а перейти к вкладке "Ряд". В первую очередь удаляем все ряды, сделанные по воле программистов компании Microsoft, а не по нашей воле. Теперь добавляем "Ряд1", дав полю "Имя" значение C1 (ячейка, в которой находится название "объем продаж..."). Имя "Ряд1" тут же заменяется содержимым выбранной ячейки. Для заполнения поля "Значения Х" ведем мышкой с прижатой левой клавишей по диапазону А2:А14, а поля "Значения Y" - по диапазону С2:С14. Модель графика будет видна, как показано на рис. 1.

На третьем шаге мастера диаграмм обычно подписывают заголовки, изменяют название диаграммы. Тому, кто готовит графический прогноз, нужнее вкладка "Линии сетки". Основные линии, параллельные оси Y, задаются по умолчанию, а промежуточные линии оси Х рекомендуется задать (рис. 2). Чтобы прогнозируемый график был нагляднее, выведите его на отдельный лист.

Вот график готов. Не спешите искать лекала и продлевать его. Авторы Excel, многократно незаслуженно охаянные программисты Билла Гейтса, предусмотрели нужды Кассандры XX-XXI века. Но сначала делаем график просто нагляднее. Для этого щелкнем правой кнопкой мышки на области построения диаграммы, в открывшемся контекстном меню выберем первый пункт - Формат области построения, а в открывшемся окне заливку "обычная" (рис. 3). Во-первых, вы избавитесь от совершенно лишней в данном случае заливки области построения диаграммы, что сделает ее нагляднее и сэкономит картридж при печати, а во-вторых, посмотрите на возможности работы над диаграммой с помощью контекстного меню. Это мощнейшее средство знакомо далеко не всем, а оно очень полезно. Щелкнув правой кнопкой мыши на построенной кривой, можно с помощью контекстного меню изменить толщину этой линии, ее цвет, сделать линию пунктирной и расставить на ней маркеры. Все это для наглядности неплохо. Но для прогноза действительно важным пунктом контекстного меню является пункт "Добавить линию тренда" (или просто "тренд" в некоторых версиях) (рис. 4). Среди многочисленных аппроксимирующих кривых (рис. 5) нужно подобрать ту, которая более всего соответствует построенному графику. Это можно сделать перебором вариантов, причем для выбранной в настоящем примере полиномиальной кривой можно варьировать степень интерполяционного полинома. И снова не спешите строить тренд, а затем снимать вручную показания, хотя это уже достаточно удобно. Выберите вкладку "Параметры" и закажите построение прогноза вперед на три года (или другой требуемый от вас период), а заодно потребуйте вывести на диаграмму уравнение линии тренда и величину достоверности R2 (рис. 6). В результате вы получите не только нужный график, но и оценку его достоверности, и, что очень важно, - аппроксимирующую функцию. Для большего удобства можете щелкнуть на записи этой функции левой кнопкой мыши и изменить параметры шрифта, сделав их хорошо читаемыми (рис. 7). Значение R2=0.9965 очень близко к 1, что свидетельствует о высоком качестве нашего подбора и о предсказуемости дальнейшего. Если подобрать нужный тип тренда и параметры кривой не удается - не предсказывайте ничего. Это будет не научный прогноз, можете закрывать Excel и ставить на огонь кофе, чтобы взбодриться и получить материал для гадания.

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

"Ведь надобно ж зависеть от других..."

Построенная зависимость связывает объемы продаж со временем. Как бы все происходит само собой: пройдет год, другой - и если все пойдет без серьезных изменений, то и объемы продаж с необходимостью возрастут. Вы только заранее приготовьтесь к соответствующим событиям. Например, кошелек пообъемнее купите.

В жизни, однако, все происходит несколько иначе, и причиной тех или иных событий является не только временной ход, но и наши действия. Например, инвестиции в развитие продукции высоких технологий. Пусть такие инвестиции составляли по годам приведенные ниже числа в млн. долларов (курсивом помечены планируемые вложения). Резонно подозревать, что именно благодаря инвестициям достигался рост объема продаж, показанный (повторенный) в той же таблице.

Можно, конечно, построить прогноз роста объема продаж в зависимости от инвестиций так, как мы делали раньше, а можно воспользоваться такой интересной функцией электронных таблиц, как ПРЕДСКАЗ (рис.8). Эта функция позволяет не только посчитать прогнозируемый объем, но, варьируя параметр Х, т.е. расходы, увидеть сосчитанную линейным трендом прибыль. Ничто не мешает воспользоваться автозаполнением для этой формулы, зафиксировав предварительно известные из опыта диапазоны затрат и объемов продаж. Тот же результат может быть получен и при использовании функции ТЕНДЕНЦИЯ.

Впрочем, есть более мощные средства, предсказывающие поведение величины в зависимости не от одного, а от нескольких факторов. Чтобы воспользоваться ими, следует обратиться к пункту меню СЕРВИС - АНАЛИЗ ДАННЫХ, выбрав инструмент РЕГРЕССИЯ. Для того чтобы этим инструментом воспользоваться, нужно лишь расположить влияющие факторы рядом, в соседних столбцах. В нашем примере просматриваются два влияющих фактора: время (номер п/п) и объем инвестиций. В результате работы предложенного механизма, будут сосчитаны коэффициенты линейной регрессии для каждого параметра. Поместите их на отдельный лист, так проще и нагляднее, да и режимом умолчания рекомендуется. Фрагмент результата, выданного пакетом Анализа, показан на рисунке 9. Для нас важнейшие сведения - это коэффициенты регрессии.

Теперь умножим почленно каждый из коэффициентов на соответствующее новое значение (те ресурсы, которые готовы израсходовать и номера еще не прожитых лет), сложим результат и предскажем объем продаж. У нас всего два члена в сумме плюс Y пересечение (свободный член), который просто прибавляется. А может быть, не два, а до 15, тогда ввод почленных произведений окажется скучной и сравнительно длинной процедурой. Вообще, для умножения и почленного сложения существует такая замечательная функция, как СУММПРОИЗВ, позволяющая произвести такую операцию быстро и без расходования лишнего места. Одна беда: эта функция оперирует с массивами, помещенными в столбцы или строки. Если же надо умножить вектор-строку на вектор-столбец, СУММПРОИЗВ вам не помощник. Но помощник все же есть! Наше действие - это произведение матриц! В данном случае - одной строки на один столбец, так что можно обойтись и без формулы массива. Учтите, именно строку надо умножать на столбец, действия над матрицами не перестановочны! И не забудьте затем прибавить Y.

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



статьи
статьи
 / 
новости
новости
 / 
контакты
контакты