Как Сравнить Два Графика в Excel в Зависимости • Тренд как модель

Как Сравнить Два Графика в Excel в Зависимости

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Практическое задание
Файлы для самоконтроля (1.bmp, 2.bmp, 3.bmp, 4.bmp, 5.bmp)
Пример выполненного задания

эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
Вы можете выбрать экспоненциальный , линейный , логарифмический , Скользящая средняя , Сила а также многочлен Варианты типа регрессии оттуда. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Чтобы отформатировать линию тренда, щелкните ее правой кнопкой мыши и выберите Формат Trendline, Это снова откроет окно Format Trendline, из которого вы можете нажать Цвет линии, Выбрать Сплошная линия и нажмите цвет поле, чтобы открыть палитру, из которой вы можете выбрать альтернативный цвет для линии тренда.
Как Сравнить Два Графика в Excel в Зависимости • Тренд как модель

Как добавить линейную регрессию в графики Excel — Autotak

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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector