Как Построить Показательную Функцию в Excel • Что такое r по физике

excel11

Процесс подбора эмпирической формулы P(x) для опытной зависимости F(x) называется аппроксимацией (сглаживанием). Для зависимостей с одним неизвестным в Excel используются графики, а для зависимостей со многими неизвестными – пары функций из группы Статистические ЛИНЕЙН и ТЕНДЕНЦИЯ, ЛГРФПРИБЛ и РОСТ.

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

Линейная – y=cx+b. Это простейшая функция, отражающая рост и убывание данных с постоянной скоростью.

Полиномиальная – y=c0+c1x+c2x 2 +…+c6x 6 . Функция описывает попеременно возрастающие и убывающие данные. Полином 2-ой степени может иметь один экстремум (min или max), 3-ей степени – до 2-х экстремумов, 4-ой степени – до 3-х и т.д.

Логарифмическая – y=clnx+b. Эта функция описывает быстро возрастающие (убывающие) данные, которые затем стабилизируются.

Степенная – y=cx b , (х>0 и y>0). Функция отражает данные с постоянно увеличивающейся (убывающей) скоростью роста.

Экспоненциальная – y=ce bx , (e – основание натурального логарифма). Функция описывает быстро растущие (убывающие) данные, которые затем стабилизируются.

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

Как Построить Показательную Функцию в Excel • Что такое r по физике

Для всех 5 видов функций используется аппроксимация данных по методу наименьших квадратов. Подробнее о формулах расчета линии тренда и коэффициента детерминации смотрите в справке по F1, введя поиск слов «линия тренда».

В качестве примера рассмотрим зависимость продаж от рекламы, заданную следующими статистическими данными по некоторой фирме:

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

Приступим к решению: в первую очередь введите эти данные в Excel и постройте график, как на рис. 2.48. Как видно, график построен на основании диапазона B2:J2. Далее, щелкнув правой кнопкой мыши по графику, добавьте линию тренда, как показано на рис. 2.48.

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

Как Построить Показательную Функцию в Excel • Что такое r по физике

Как Построить Показательную Функцию в Excel • Что такое r по физике

Как Построить Показательную Функцию в Excel • Что такое r по физикеКак Построить Показательную Функцию в Excel • Что такое r по физике

Как Построить Показательную Функцию в Excel • Что такое r по физике

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

Как Построить Показательную Функцию в Excel • Что такое r по физике

Как Построить Показательную Функцию в Excel • Что такое r по физике

Теперь вернитесь к состоянию рис. 2.50, нажав кнопку Отменить на Панели инструментов. Попробуйте изменить формат линии тренда – установите полиномиальную линию тренда полиномом 2-ой степени – получите рис. 2.52.

Как Построить Показательную Функцию в Excel • Что такое r по физике

Как Построить Показательную Функцию в Excel • Что такое r по физике

Опять вернитесь к состоянию рис. 2.50, нажав кнопку Отменить. Для вычисления продаж при рекламе в 6 тыс. руб. запишите в ячейку К2 формулу =23,796*LN(K1)+0,5961: должно получиться 43,2 тыс. штук.

Как Построить Показательную Функцию в Excel • Что такое r по физике

Обратите внимание, что на рис. 2.50 ось Х подписана номерами периодов наблюдения, а на рис. 2.52 — значениями в точках наблюдения. Для нанесения значений на ось Х щелкните правой кнопкой мыши по графику и в выпавшем меню выберите пункт Исходные данные:

Как Построить Показательную Функцию в Excel • Что такое r по физике

В открывшемся одноименном окне, в закладке Ряд, в поле Подписи оси Х, укажите диапазон ячеек, где записаны значения Х (здесь $B$1:$K$1).

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

Концентрация ядовитого вещества в водоеме изменялась во времени согласно таблице:

Определите вид зависимости концентрации от времени и расчетную концентрацию в момент выброса.

В настоящем разделе рассматривается аппроксимация экспериментальных данных с помощью функций ЛИНЕЙН, ТЕНДЕНЦИЯ, ЛГРФПРИБЛ и РОСТ. Функции ЛИНЕЙН и ТЕНДЕНЦИЯ применяют для восстановления линейных зависимостей вида y=b+a1x1+a2x2+…+anxn, а функции ЛГРФПРИБЛ и РОСТ — для нелинейных (показательных) зависимостей вида y=ba1 X 1 a2 X 2 …an Xn .

Функции ЛИНЕЙН и ЛГРФПРИБЛ возвращают массив с т.н. регрессионной статистикой, в котором содержатся вычисленные значения параметров (b,a1,a2,…an), коэффициент детерминации R 2 и другие данные, характеризующие аппроксимирующую функцию. Формат функций ЛИНЕЙН, ЛГРФПРИБЛ и их применение поясним на примере.

Как Построить Показательную Функцию в Excel • Что такое r по физике

Последовательность действий для решения задачи следующая:

Заведите приведенную таблицу в Excel, в ячейки A1:D14.

Выделите диапазон ячеек B17:E21 (рис. 2.54) для сохранения результатов вычислений функции ЛИНЕЙН – массива регрессионной статистики.

Вызовите мастер функций, выберите статистическую функцию ЛИНЕЙН и заполните параметры функции как на рис. 2.53. Параметр Изв_знач_y содержит диапазон D2:D14, т.е. известные значения y. Параметр Изв_знач_х содержит диапазон A2:C14, т.е. известные значения х. Параметр Стат=1, поскольку мы хотим получить дополнительную статистику.

Как Построить Показательную Функцию в Excel • Что такое r по физике

После нажатия ОК встаньте на строку формул и нажмите Ctrl+Shift+Enter. В результате должен получиться массив значений, показанный на рис. 2.54. Интересующие нас коэффициенты выделены на рисунке (подробнее см. справку F1). Коэффициент детерминации R 2 =0.9725 вполне удовлетворителен. Таким образом, искомая формула имеет вид:

Как Построить Показательную Функцию в Excel • Что такое r по физике

После подбора формулы осталось вычислить стоимость при х1=42, х2=11, х3=5. В любую ячейку запишите выражение =1,36*42+0,1*11–0,21*5–19,27. В результате получится y=37.9 тыс. $.

Использование функции ТЕНДЕНЦИЯ покажем на этом же примере для расчета стоимостей различных вариантов квартир, как показано на рис. 2.55.

Как Построить Показательную Функцию в Excel • Что такое r по физике

Как Построить Показательную Функцию в Excel • Что такое r по физике

Следующий пример. Подобрать формулу для вычисления процента увеличения оборота при различных затратах на рекламу. Экспериментально известны проценты увеличения оборота при затратах в 5, 10, 15, 20 тыс.$ в 3-х масс-медиа — на телевидении, радио и в прессе:

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Если выбрать в главном меню раздел формулы , то здесь сосредоточены все известные функции, в том числе финансовые, инженерные, аналитические. Если же вы хотите что-то уточнить, я с радостью помогу!
Здравствуйте!
Пробовал зарегистрироваться, но поле с адресом эл. почты показывает, что адрес неправильный. Не знаю почему?
Вопрос к вам такой: как построить график в Excel по двум точкам?
Например,
точка А имеет координаты 3;5
точка В имеет координаты 2;7
точка С имеет координаты 4;9
точка D имеет координаты 5;8
и т.д.
Подскажите, пожалуйста.
Стоит Офис 2010
Как Построить Показательную Функцию в Excel • Что такое r по физике

Построение графиков функций в Excel | Информатика в школе

Построение графиков функций — одна из возможностей Excel. В этой статье мы рассмотрим процесс построение графиков некоторых математических функций: линейной, квадратичной и обратной пропорциональности.

Знайка, самый умный эксперт в Цветочном городе
Мнение эксперта
Знайка, самый умный эксперт в Цветочном городе
Если у вас есть вопросы, задавайте их мне!
Задать вопрос эксперту
Каждая из приведенных выше функций принимает единственный аргумент число, который характеризует угол, заданный в радианах для SIN и COS или любое значение из диапазона вещественных чисел, для которого требуется определить гиперболические синус или косинус для SINH и COSH соответственно. Если же вы хотите что-то уточнить, я с радостью помогу!
Появится область графика, кликаем на белую область правым указателем мыши, выскакивает меню, далее Выбрать данные, появляется окно Выбора источника данных, выбираем весь диапазон данных нашей синусоиды в ячейках, затем Ок.

Формулы EXCEL с примерами — Инструкция по применению

  1. Кликаем по любой незанятой ячейке, в которой будет отображаться итоговый результат деления и вводим с клавиатуры “=”. при помощи левой кнопки мыши выбираем ячейку с делимым (после знака “=” появится ее адрес); вводим слэш – “/“; …
  2. Жмем клавишу Enter, чтобы увидеть результат в выбранной ячейке.

Теперь вернитесь к состоянию рис. 2.50, нажав кнопку Отменить на Панели инструментов. Попробуйте изменить формат линии тренда – установите полиномиальную линию тренда полиномом 2-ой степени – получите рис. 2.52.

Оставить отзыв

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