Построение XY-диаграммы данных в Excel

Исходным пунктом моделирования трендов в Excel является построение

диаграммы. Рассмотрим этот процесс для Excel 97 (см. рис. 7.3).

• Щелчок по кнопке «Далее» активизирует второй шаг «Мастера диаграмм» (в нижней части рис. 7.3.), где предоставляется возможность уточнить или задать исходный диапазон (если он не был задан еще до щелчка по пиктограмме «Мастера диаграмм»). Здесь же уточняется, как следует воспринимать данные (по столбцам или по строкам). После этого щелчком по кнопке «Далее» активизируется третий шаг «Мастера диаграмм».

• На третьем шаге «Мастера диаграмм» устанавливаются все специальные параметры диаграммы (заголовки, оси, линии сетки, легенда, подписи данных). Выберем вкладку «Заголовки» и уберем установленный в ней по умолчанию заголовок "Уровни". В окне "Ось Х (категорий)" введем "Годы", а в окне "Ось Y (значений)" введем "Значения уровней". Результат отражен на рис. 7.4 вверху. На том же шаге установим или изменим еще некоторые параметры (по желанию). Для того, чтобы освободить больше места для основной части диаграммы, можно переместить легенду из правой части вниз. Щелкнем по вкладке «Легенда» и в ее контуре «Размещение» активизируем переключатель «Внизу». Результат этого действия показан в средней части рис. 7.4. Хорошо видно, что основная зона диаграммы автоматически увеличилась. Щелкнем по кнопке «Далее» и активизируем четвертый шаг «Мастера диаграмм».

• На четвертом шаге «Мастера диаграмм» (рис. 7.4 внизу) определяется, где разместить диаграмму: на отдельном или на имеющемся листе электронной таблицы. Диаграмма по умолчанию размещается на имеющемся листе, если мы щелкнем по кнопке «Готово». В результате получаем диаграмму, вид которой показан на рис. 7.5.

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

В Excel придется сделать это вручную, иначе в случаях степенной, экспоненциальной и ряда других регрессий мы получим необъяснимые результаты. Вставим впереди один столбец, скопируем в него исходный ряд периодов (1982-1992), а затем в столбце-оригинале, на который ориентирована диаграмма, вместо 1982 наберем 1, вместо 1983 наберем 2, выделим диапазон клеток В1..В2 и растянем его вниз до позиции 1992 года, где должно теперь отразится значение 11. Диаграмма соответствующим образом должна измениться (рис. 7.5 внизу).

График данных построен, и в него можно встраивать различные тренды, а затем выбирать тренд.

Практические рекомендации, которые полезно учесть при моделировании трендов средствами Excel:

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

2. Форматируя линии показателей, не увлекайтесь жирными линиями, поскольку на них будут накладываться линии трендов. Расхождения будут видны более четко при тонких линиях, к тому же выбирать пересекающиеся линии можно будет значительно легче. Однако, если на одном графике вы разместите несколько показателей, то применяйте также различные типы штриховых, штрихпунктирных тонких (и в крайнем случае толстых линий).

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