Как Построить Формулу Зависимости в 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)
Пример выполненного задания

[expert_bq id=»1570″]К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Сделаем график в Excel, который отобразит количество проданных единиц каждым из сотрудников за Январь. Выделяем соответствующий столбец в таблице, переходим на вкладку «Вставка» и кликаем по кнопочке «График» . Выберите один из предложенных вариантов. Для каждого графика есть подсказка, которая поможет определиться, в каком случае его лучше использовать. Используем «График с маркерами» .

Kat&Pop — Склад — Информатика — Excel. Графики функций

• Выполнить команду меню Сервис > Поиск решения, чтобы вызвать диалоговое окно Поиск решения (рис. 4.2)
• Установить курсор в поле Установить целевую ячейку диалогового окна и щелкнуть мышкой на целевой ячейке Е7 (рис. 4.2).
• Установить курсор в поле Изменяя ячейки диалогового окна и выделить диапазон изменяемых ячеек С3:С6.
• Установить курсор в поле Ограничения и щелкнуть на кнопке Добавить . В появившееся диалоговое окно, показанное на рис. 4.3, вводить поочередно все ограничения (рис. 4.4).
• Щелкнуть на кнопке Выполнить диалогового окна Поиск решения.

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