Редактор электронных таблиц MS Excel.
Графики функций. Поиск зависимости между данными при помощи графиков функций.

Два типа графиков функций в Excel. Создание графиков функций. Зависимость между двумя рядами данных. Линия тренда.

Будем считать, что функции, графики которых нам надо строить всегда расположены в столбцах. Причем в столбце А пусть находятся значения переменной x, а в столбцах В, С… значения функций f₁, f₂, … соответственно.

Два типа графиков функций в Excel. Создание графиков функций.

В Excel’е есть специальный тип диаграммы "график". При помощи него можно построить график зависимости данных в столбце (например, В) от номера строчки. Независимо от того, какие подписи оси категорий мы зададим. (Даже если по оси Х будут подписаны значения из столбца А, график не будет выражать зависимость f₁(x) в общем случае). Строится как самая обычная диаграмма. (См. примеры 1 и 2). Можно строить несколько графиков функций на одной диаграмме.

Если столбец А содержит арифметическую прогрессию, то можно считать, что график построенной функции выражает зависимость f₁(x).

Такой метод построения графика функции удобен, если дана протабулированная функция.

На шаге 2 в качестве диапазона надо указать соответствующие ячейки столбца В, а на вкладке “Ряд” в строчке “Подписи по оси Х” указать соответствующие ячейки столбца А.

Второй метод построения графика зависимости более универсален. Надо строить диаграмму типа “Точечная”. (Если выбрать третий вид, то она совсем будет похожа на график.)

На втором шаге в качестве диапазона указываем столбец В. А на вкладке ряд в качестве “Значения Х” указываем столбец А.

Если мы хотим построить несколько графиков (например, графики функций f₁ и f₂) на одной диаграмме, надо нажать кнопку “Добавить”, и для добавленого ряда указать в графе “Значения Y” - столбец С, в графе “Значения Х” - столбец А.

Аналогично с помощью диаграммы типа “Точечная” можно нарисовать график какой-нибудь нетривиальной зависимости. Например, f₁(f₂). См. пример №3.

Линия тренда.

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

Для этого поступим так. Построим график этой зависимости (можно График, можно Точечная), воспользуемся пунктом Меню=>Диаграмма=>Добавить линию тренда. Выберем тип тренда. Excel построит на диаграмме еще один график, показывающий, к какой зависимости ближе всего исследуемые данные.

Например, если мы выберем тип тренда “Линейная”, то Excel построит линейную функцию, которая лучше всего описывает исследуемую зависимость. На диаграмме это будет выглядеть так: это будет такая прямая линия, что график построенной функции отклоняется от нее наименьшим образом. См. пример 4.

Примечание: Excel использует метод наименьших квадратов. Т.е. Excel строит тренд с условием, что сумма квадратов расстояний от реальных точек до линии тренда минимальна для всех линий данного типа.

Как понять, что за тренд построен? Очень просто. Надо вывести его уравнение (“Формат линии тренда”, вкладка “Параметры”, соответствующая галка). И вы узнаете приблизительное уравнение зависимости между вашими данными.

Как понять, какой тип тренда надо использовать? На той же вкладке “параметры” окна “формат линии тренда” включаем галку “поместить на диаграмму величину достоверности аппроксимации (R^2)”. После этого в подписи к тренду появится эта самая величина достоверности. Чем она больше, тем лучше построенный тренд. См. пример 5.

Зачем нужны линии тренда? или
Как проводить маркетинговые исследования подручными способами?

Линия тренда - один из самых простых, но довольно эффективных способов исследования разных зависимостей в Excel.

Например, даны статистические данные о средней зарплате и о тратах на продукты питания за несколько лет. Можно исследовать, зависят ли расходы на питание от средней зарплаты и как. Во-первых, надо отметить точки, соответствующие данным (построить точечную диаграмму). Если точки получились не хаотично разбросаны по всей диаграмме, а более-менее по какой-то линии, можно попробовать построить тренд этой зависимости (добавить линию тренда: всех типов по очереди и посмотреть, при каком типе наибольший R²). При большом коэффициенте R² (например, R²>0,9) можно считать, что мы нашли подходящюю зависимость. Практически так поступают на практике.

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

В экономических задачах такой подход используется очень часто.

Больше о том, как проводить подобные исследования, можно узнать на Эконометрике.

Примеры построения графиков функций.

Примеры выполнены в файле ex7.xls . Примеры 1- 3 на листе “Пример”, примеры 4- 5 на листе “Тренд”.

Пример №1.

Построение графика функции f₁.

1. Меню=>Вставка=>Диаграмма…
2. Шаг 1. Тип - График, Вид - первый. Далее.
3. Шаг 2. Диапазон: =Пример!$B$2:$B$22, в столбцах.
4. На вкладке “Ряд”: Имя: “=Пример!$B$1”,
   Подписи по оси Х“=Пример!$A$2:$A$22”.
5. Шаг 3. Легенду убрать. Линии сетки основные в двух направлениях, Заголовки убрать. Готово.

Растягиваем до необходимого размера. Теперь будем подправлять то, что нам не понравилось.

1. Формат оси категорий. Вкладка “Выравнивание” 90° .
2. Формат оси значений. Вкладка “Шкала”. Минимальное значение 1.
3. Формат области построения. Заливка белым.
4. Формат рядов данных. Вкладка “Вид”. Толщина линии - третья, цвет - красный.

Получилось:

Пример №2.

Построим на одной диаграмме два графика функций сразу. И f₁, и f₂.

1. Меню=>Вставка=>Диаграмма…
2. Шаг 1. Тип - График, Вид - первый. Далее.
3. Шаг 2. Диапазон: =Пример!$B$2:$C$22, в столбцах.
4. На вкладке “Ряд”: Имя: “=Пример!$B$1” (для Ряда 1), “=Пример!$C$1” (для Ряда 2). Подписи по оси Х“=Пример!$A$2:$A$22”.
5. Шаг 3. Легенда - внизу. Линии сетки основные в двух направлениях, Заголовки убрать. Готово.

Растягиваем до необходимого размера. Теперь будем подправлять то, что нам не понравилось.

1. Формат оси категорий. Вкладка “Выравнивание” 90° .
2. Формат области построения. Заливка белым.
3. Формат рядов данных. Вкладка “Вид”. Толщина линии - третья. (Для двух графиков).

Получилось:

Пример №3.

Поиск зависимости между f₁ и f₂.

Чтобы построить график зависимости f₂(f₁), надо использовать тип диаграммы “Точечная”.

1. Меню=>Вставка=>Диаграмма…
2. Шаг 1. Тип - Точечная, Вид - третий (со сглаженными линиями, без маркеров). Далее.
3. Шаг 2. Диапазон: =Пример!$B$2:$C$22, в столбцах.
4. На вкладке “Ряд”: Имя: “f2(f1)”.
5. Шаг 3. Легенду убрать. Линии сетки убрать. Заголовки убрать. Готово.

Поправляем:

1. Формат области построения. Заливка белым.

Получилось.

Пример №4.

Поиск зависимости с помощью линии тренда. (см.лист “Тренд”)

Найдем зависимость между x и f₁. Для этого можно использовать как тип диаграммы График, так и Точечная, т.к. значения в столбце A образуют арифметическую прогрессию.

1. Меню=>Вставка=>Диаграмма…
2. Шаг 1. Тип - Точечная, Вид - первый. Далее.
3. Шаг 2. Диапазон: =Тренд!$A$2:$B$22, в столбцах.
4. На вкладке “Ряд”: Имя: “f1(x)”.
5. Шаг 3. Легенда внизу. Линии сетки основные в двух направлениях. Заголовки убрать. Готово.

Поправляем.

1. Формат области построения. Заливка белым.
2. Формат оси категорий. Минимальное значение 1, максимальное 2.
3. Формат оси значений. Минимальное значение 10.

Добавляем линию тренда. Похоже, что необходим линейный тренд.

1. Меню=>Диаграмма=>Добавить линию тренда.
2. Тип линейный.
3. На вкладке “Параметры”. Показывать уравнение на диаграмме.

Поправляем:

1. Выносим уравнение за область диаграммы.

Получилось.

Точки лежат достаточно близко к линии тренда, значит тип тренда мы выбрали хорошо.

Пример №5.

Поиск зависимости f₁(f₂) с помощью линии тренда.

1. Меню=>Вставка=>Диаграмма…
2. Шаг 1. Тип - Точечная, Вид - первый. Далее.
3. Шаг 2. Диапазон: =Тренд!$B$2:$B$22, в столбцах.
4. На вкладке “Ряд”: Имя: “f1(f2)”. Значения X “=Тренд!$C$2:$C$22”.
5. Шаг 3. Легенда внизу. Линии сетки основные в двух направлениях. Заголовки убрать. Готово.

Поправляем:

1. Формат области построения. Заливка белым.
2. Формат оси категорий. Минимальное значение 2.
3. Формат оси значений. Минимальное значение 10.

Добавляем линию тренда. По точкам не сразу понятно, какой тренд добавлять. Добавим линейный, затем логарифмический и посмотрим, при каком величина R^2 ближе к 1. Такой и лучше.

1. Меню=>Диаграмма=>Добавить линию тренда.
2. Тип линейный.
3. На вкладке “Параметры”. Показывать уравнение на диаграмме. Поместить на диаграмму R^2.

Получили R²=0,9705. Неплохо.

1. Формат линии тренда. Тип логарифмическая.

Поличили R²=0,9867. Лучше. Такой тренд и оставим.

Поправляем:

1. Выносим уравнение за область диаграммы.
2. Формат линии тренда. На вкладке параметры убираем значок “поместить на диаграмму R^2”.

Получилось.