Пример функция тенденция в excel

ТЕНДЕНЦИЯ (функция ТЕНДЕНЦИЯ)

Функция тенденция возвращает значения вдоль линейного тренда. Он подходит к прямой линии (с использованием метода наименьших квадратов) в массивах известные_значения_y и известные_значения_x. Функция тенденция возвращает значения y, а также линию для указанного массива новые_значения_x.

Примечание: Если у вас установлена текущая версия Office 365, вы можете ввести формулу в левую верхнюю ячейку диапазона выходных данных (в этом примере — ячейка E16), а затем нажмите клавишу Ввод , чтобы подтвердить формулу как формулу динамических массивов. В противном случае необходимо ввести формулу в качестве устаревшей формулы массива, сначала выбрав диапазон вывода (E16: E20), введите формулу в верхнюю левую ячейку выходного диапазона (E16), а затем нажмите клавиши CTRL + SHIFT + ВВОД , чтобы подтвердить его. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

= ТЕНДЕНЦИЯ (известные_значения_y; [известные_значения_x]; [новые_значения_x]; [Конст])

Аргументы функции ТЕНДЕНЦИЯ описаны ниже.

Набор значений y, которые уже известны в соотношении y = mx + b

Если массив “известные_значения_y” содержит один столбец, каждый столбец массива “известные_значения_x” интерпретируется как отдельная переменная.

Если массив “известные_значения_y” содержит одну строку, каждая строка массива “известные_значения_x” интерпретируется как отдельная переменная.

Необязательный набор значений x, которые, возможно, уже известны в соотношении y = mx + b

Массив известные_значения_x может включать одно или более множеств переменных. Если используется только одна переменная, то аргументы “известные_значения_y” и “известные_значения_x” могут быть диапазонами любой формы при условии, что они имеют одинаковую размерность. Если используется более одной переменной, то аргумент “известные_значения_y” должен быть вектором (то есть диапазоном высотой в одну строку или шириной в один столбец).

Если аргумент “известные_значения_x” опущен, то предполагается, что это массив <1;2;3;. >того же размера, что и “известные_значения_y”.

Новые значения x, для которых тенденция возвращает соответствующие значения y.

Аргумент “новые_значения_x”, так же как и аргумент “известные_значения_x”, должен содержать по одному столбцу (или строке) для каждой независимой переменной. Таким образом, если “известные_значения_y” — это один столбец, то “известные_значения_x” и “новые_значения_x” должны иметь одинаковое количество столбцов. Если “известные_значения_y” — это одна строка, то аргументы “известные_значения_x” и “новые_значения_x” должны иметь одинаковое количество строк.

Если аргумент “новые_значения_x” опущен, то предполагается, что он совпадает с аргументом “известные_значения_x”.

Если опущены оба аргумента — “известные_значения_x” и “новые_значения_x”, — то предполагается, что это массивы <1;2;3;. >того же размера, что и “известные_значения_y”.

Логическое значение, указывающее, нужно ли принудительно использовать константу b равным 0

Если аргумент “конст” имеет значение ИСТИНА или опущен, то b вычисляется обычным образом.

Если аргумент “конст” имеет значение ЛОЖЬ, то b полагается равным 0 и значения m подбираются таким образом, чтобы выполнялось условие y = mx.

Сведения о том, как в Microsoft Excel подходящее для данных линия, можно найти в разделе ЛИНЕЙН.

Функцию ТЕНДЕНЦИЯ можно использовать для аппроксимации полиномиальной кривой, проводя регрессионный анализ для той же переменной, возведенной в различные степени. Например, пусть столбец A содержит значения y, а столбец B содержит значения x. Можно ввести значение x^2 в столбец C, x^3 в столбец D и т. д., а затем провести регрессионный анализ столбцов от B до D со столбцом A.

Формулы, которые возвращают массивы, должны быть введены как формулы массива с помощью клавиш CTRL + SHIFT + ВВОД, если только вы не хотите использовать текущую версию Office 365, а затем просто нажмите клавишу Ввод.

При вводе константы массива для аргумента (например, “известные_значения_x”) следует использовать точки с запятой для разделения значений в одной строке и двоеточия для разделения строк.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Источник: support.office.com

Примеры функции ТЕНДЕНЦИЯ в Excel для прогнозирования данных

Функция ТЕНДЕНЦИЯ в Excel используется при расчетах последующих значений для рассматриваемого события и возвращает данные в соответствии с линейным трендом. Функция выполняет аппроксимацию (упрощение) прямой линией диапазона известных значений независимой и зависимой переменных с использованием метода наименьших квадратов и прогнозирует будущие значения зависимой переменной Y для указанных последующих значений независимой переменной X. Рассматриваемая функция не используется для получения статистической характеристики модели тренда и математического описания.

Линейным трендом называется распределение величин в изучаемой последовательности, которое может быть описано функцией типа y=ax+b. Поскольку функция ТЕНДЕНЦИЯ выполняет аппроксимацию прямой линией, точность результатов ее работы зависит от степени разброса значений в рассматриваемом диапазоне.

Примеры использования функции ТЕНДЕНЦИЯ в Excel

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

Вид исходной таблицы данных:

Для прогноза курса валют на 7-й месяц используем следующую функцию (обычная запись, Enter для вычислений):

  • B3:B8 – диапазон известных значений курса валюты;
  • A3:A8 – диапазон месяцев, для которых известны значения курса;
  • A9 – значение, соответствующее номеру месяца, для которого необходимо выполнить расчет.

В результате получим:

Прогноз посещаемости с помощью функции ТЕНДЕНЦИЯ в Excel

Пример 2. В кинотеатре фильмы показывают в различные сеансы, которые начинаются в 12:00, 16:00 и 21:00 соответственно. Каждый фильм имеет собственный рейтинг, в виде оценки от 1 до 10 баллов. Известны данные о посещаемости нескольких последних сеансов. Предположить, какой будет посещаемость для следующих фильмов:

  1. Рейтинг 7, сеанс 12:00;
  2. Рейтинг 9,5, сеанс 21:00;
  3. Рейтинг 8, сеанс 16:00.

Таблица исходных данных:

Для расчета используем функцию:

  1. Перед вводом функции необходимо выделить ячейки C11:C13;
  2. Расчет производим на основе диапазона значений A2:B10 (учитывается как время сеанса, так и рейтинг фильма)

В результате получим:

Не забывайте, что ТЕНДЕНЦИЯ является массивной функцией поэтому после ее ввода не забудьте выполнить ее в массиве. Для этого жмем не просто Enter, а комбинацию клавиш Ctrl+Shift+Enter. Если в строке формул по краям функции появились фигурные скобки <>, значит функция выполняется в массиве и все сделано правильно.

Прогнозирование производства продукции на графике Excel

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

Читайте также:  Excel функция дата

Для определения количества единиц продукции, которые будут выпущены на протяжении последующих 3-х месяцев используем функцию:

Построим график на основе имеющихся данных и отобразим линию тренда с уравнением:

Введем в ячейке C8 формулу =193,5*A8+2060,5. В результате получим:

Аналогично с помощью подстановки значения независимой переменной в уравнение рассчитаем все остальные величины.

Данный пример наглядно демонстрирует принцип работы функции ТЕНДЕНЦИЯ.

Функция ТЕНДЕНЦИЯ в Excel и особенности ее использования

Функция ТЕНДЕНЦИЯ используется наряду с прочими функциями прогноза в Excel (ПРЕДСКАЗ, РОСТ) и имеет следующий синтаксис:

= ТЕНДЕНЦИЯ(известные_значения_y; [известные_значения_x]; [новые_значения_x]; [конст])

  • известные_значения_y – обязательный аргумент, характеризующий диапазон исследуемых известных значений зависимой переменной y из уравнения y=ax+b.
  • [известные_значения_x] – необязательный для заполнения аргумент, характеризующий диапазон известных значений независимой переменной x из уравнения y=ax+b.
  • [новые_значения_x] – необязательный аргумент, характеризующий одно значение или диапазон данных, для которых необходимо определить соответствующие значения зависимой переменной y.
  • [конст] – необязательный аргумент, принимающий на вход логические значения:
    1. ИСТИНА (значение по умолчанию, если явно не указано обратное) – функция ТЕНДЕНЦИЯ выполняет расчет коэффициента b из уравнения y=ax+b обычным методом.
    2. ЛОЖЬ – функция ТЕНДЕНЦИЯ использует упрощенный вариант уравнения – y=ax (коэффициент b = 0).
  1. Рассматриваемая функция интерпретирует каждый столбец или каждую строку из диапазона известных значений x в качестве отдельной переменной, если аргументом известное_y является диапазон ячеек из только одного столбца или только одной строки соответственно.
  2. Аргументы [известное_ x] и [новое_x] должны содержать одинаковое количество строк либо столбцов соответственно. Если новые значения независимой переменной явно не указаны, функция ТЕНДЕНЦИЯ выполняет расчет с условием, что аргументы [известное_ x] и [новое_ x] принимают одинаковые значения. Если оба эти аргумента явно не указаны, рассматриваемая функция использует массивы <1;2;3;…;n>с размерностью, соответствующей размерности известное_y.
  3. Данная функция может быть использована для аппроксимации полиномиальных кривых.
  4. ТЕНДЕНЦИЯ является формулой массива. Для определения нескольких последующих значений необходимо выделить диапазон соответствующего количества ячеек и для отображения результата использовать комбинацию клавиш Ctrl+Shift+Enter.
  5. В качестве аргумента [известное_ x] могут быть переданы:
  • Только одна переменная, при этом два первых аргумента функции ТЕНДЕНЦИЯ могут являться диапазонами любой формы, но обязательным условием является одинаковая размерность (количество элементов).
  • Несколько переменных, при этом в качестве аргумента известное_y должен быть передан вектор значений (диапазон из только одной строки или только одного столбца).

Источник: exceltable.com

Построение функции тренда в Excel. Быстрый прогноз без учета сезонности

Глядя на любой набор данных распределенных во времени (динамический ряд), мы можем визуально определить падения и подъемы показателей, которые он содержит. Закономерность подъемов и падений называется трендом, который может говорить о том, увеличиваются или уменьшаются наши данные.

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

Базовые понятия

Думаю, еще со школы все знакомы с линейной функцией, она как раз и лежит в основе тренда:

Y — это объем продаж, та переменная, которую мы будем объяснять временем и от которого она зависит, то есть Y(t);

t — номер периода (порядковый номер месяца), который объясняет план продаж Y;

a0 — это нулевой коэффициент регрессии, который показывает значение Y(t), при отсутствии влияния объясняющего фактора (t=0);

a1 — коэффициент регрессии, который показывает, на сколько исследуемый показатель продаж Y зависит от влияющего фактора t;

E — случайные возмущения, которые отражают влияния других неучтенных в модели факторов, кроме времени t.

Построение модели

Итак, мы знаем объем продаж за прошедшие 9 месяцев. Вот, что из себя представляет наша табличка:

Следующее, что мы должны сделать — это определить коэффициенты a0 и a1 для прогнозирования объема продаж за 10-ый месяц.

Определение коэффициентов модели

Строим график. По горизонтали видим отложенные месяцы, по вертикали объем продаж:

В Google Sheets выбираем Редактор диаграмм -> Дополнительные и ставим галочку возле Линии тренда. В настройках выбираем ЯрлыкУравнение и Показать R^2.

Если вы делаете все в MS Excel, то правой кнопкой мыши кликаем на график и в выпадающем меню выбираем «Добавить линию тренда».

По умолчанию строится линейная функция. Справа выбираем «Показывать уравнение на диаграмме» и «Величину достоверности аппроксимации R^2».

Вот, что получилось:

На графике мы видим уравнение функции:

y = 4856*x + 105104

Она описывает объем продаж в зависимости от номера месяца, на который мы хотим эти продажи спрогнозировать. Рядом видим коэффициент детерминации R^2, который говорит о качестве модели и на сколько хорошо она описывает наши продажи (Y). Чем ближе к 1, тем лучше.

У меня R^2 = 0,75. Это средний показатель, он говорит о том, что в модели не учтены какие-то другие значимые факторы помимо времени t, например, это может быть сезонность.

Прогнозируем

Чтобы рассчитать продажи за 10-ый месяц, подставляем в функцию тренда 10 вместо x. То есть,

y = 4856*10 + 105104

Получаем 153664 продажи в следующем месяце. Если добавим новую точку на график, то сразу видим, что R^2 улучшился.

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

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

Источник: thisisdata.ru

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Метод наименьших квадратов в Excel — использование функции ТЕНДЕНЦИЯ

Метод наименьших квадратов — это математическая процедура составления линейного уравнения, максимально соответствующего набору упорядоченных пар, путем нахождения значений для a и b, коэффициентов в уравнении прямой. Цель метода наименьших квадратов состоит в минимизации общей квадратичной ошибки между значениями y и ŷ. Если для каждой точки мы определяем ошибку ŷ, метод наименьших квадратов минимизирует:

где n = число упорядоченных пар вокруг линии. максимально соответствующей данным.

Это понятие проиллюстрировано на рисунке

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

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

Поскольку своей целью мы определили задачу узнать, увеличивается ли со временем число предметов, «Месяц» будет независимой переменной, а «Число предметов» — зависимой.

С помощью метода наименьших квадратов определяем уравнение, максимально соответствующее данным, путем вычисления значений a, отрезка на оси y, и b, наклона линии:

где xср — среднее значение x, независимой переменной, yср — среднее значение y, независимой переменной.

В таблице ниже суммированы необходимые для этих уравнений вычисления.

Кривая эффекта для нашего примера с ванной будет определяться следующим уравнением:

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

Ожидание в отношении числа предметов в течение следующего полугода (месяца 16) будет вычисляться так:

ŷ = 5.13 + 0.976x = 5.13 + 0.976(16)

20.7 = 21 предмет

Так что, пора нашему герою предпринимать какие-нибудь действия.

Функция ТЕНДЕНЦИЯ в Excel

Как вы уже, наверное, догадались в Excel имеется функция для расчета значения по методу наименьших квадратов. Это функция называется ТЕНДЕНЦИЯ. Синтаксис у нее следующий:

ТЕНДЕНЦИЯ (известные значения Y; известные значения X; новые значения X; конст)

известные значения Y – массив зависимых переменных, в нашем случае, количество предметов на столике

известные значения X – массив независимых переменных, в нашем случае это месяц

новые значения X – новые значения X (месяца) для которого функция ТЕНДЕНЦИЯ возвращает ожидаемое значение зависимых переменных (количество предметов)

конст — необязательный. Логическое значение, которое указывает, требуется ли, чтобы константа b была равна 0.

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

Источник: exceltip.ru

Использование встроенных функций Excel

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

В Excel имеется несколько функций для построения линейной регрессии, в частности:

· НАКЛОН и ОТРЕЗОК.

А также несколько функций для построения экспоненциальной линии тренда, в частности:

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

Задание. С таблицей данных о прибыли автотранспортного предприятия за 2000–2007 гг. (см. табл. 4.1) необходимо выполнить следующие действия:

1) получить ряды данных для линейной и экспоненциальной линии тренда с использованием функций ТЕНДЕНЦИЯ и РОСТ;

2) используя функции ТЕНДЕНЦИЯ и РОСТ, составить прогноз о прибыли предприятия на 2008 и 2009 гг.;

3) для исходных данных и полученных рядов данных построить диаграмму.

Методика выполнения. Воспользуемся исходной таблицей (см. рис. 4.4). Начнем с функции ТЕНДЕНЦИЯ.

1. Выделяем диапазон ячеек D4:D11, который следует заполнить значениями функции ТЕНДЕНЦИЯ, соответствующими известным данным о прибыли предприятия.

2. Вызываем команду Функция из меню Вставка. В появившемся диалоговом окне Мастер функций выделяем функцию ТЕНДЕНЦИЯ из категории Статистические, после чего щелкаем по кнопке ОК. Эту же операцию можно осуществить нажатием кнопки Вставка функции стандартной панели инструментов.

3. В появившемся диалоговом окне Аргументы функции вводим в поле Известные_значения_y диапазон ячеек C4:C11; в поле Известные_значения_х – диапазон ячеек B4:B11.

4. Чтобы вводимая формула стала формулой массива, используем комбинацию клавиш Ctrl + Shift + Enter.

Введенная нами формула в строке формул будет иметь следующий вид: =<ТЕНДЕНЦИЯ(C4:C11;B4:B11)>.

В результате диапазон ячеек D4:D11 заполняется соответствующими значениями функции ТЕНДЕНЦИЯ (рис. 4.7).

Рис. 4.7. Значения функций ТЕНДЕНЦИЯ и РОСТ

Для составления прогноза о прибыли предприятия на 2008 и 2009 гг. необходимо:

1) выделить диапазон ячеек D12:D13, куда будут заноситься значения, прогнозируемые функцией ТЕНДЕНЦИЯ;

2) вызвать функцию ТЕНДЕНЦИЯ и в появившемся диалоговом окне Аргументы функции ввести в поле Известные_значения_y – диапазон ячеек C4:C11; в поле Известные_значения_х – диапазон ячеек B4:B11; а в поле Новые_значения_х – диапазон ячеек B12:B13.

3) превратить эту формулу в формулу массива, используя комбинацию клавиш Ctrl + Shift + Enter.

Введенная формула будет иметь следующий вид:

а диапазон ячеек D12:D13 заполнится прогнозируемыми значениями функции ТЕНДЕНЦИЯ (см. рис. 4.7).

Аналогично заполняется ряд данных с помощью функции РОСТ, которая используется при анализе нелинейных зависимостей и работает точно так же, как ее линейный аналог ТЕНДЕНЦИЯ.

На рис. 4.8 представлена таблица в режиме показа формул.

Рис. 4.8. Таблица в режиме показа формул

Для исходных данных и полученных рядов данных построена диаграмма, изображенная на рис. 4.9.

Рис. 4.9. Графическое изображение линий тренда Прибыли
предприятия
, функций ТЕНДЕНЦИЯ и РОСТ

Задание для самостоятельной работы. С таблицей данных о прибыли автотранспортного предприятия (см. табл. 4.1) необходимо выполнить следующие действия:

1) получить ряды данных для линейной регрессии, используя функции НАКЛОН и ОТРЕЗОК, в также используя функцию ЛИНЕЙН;

2) получить ряд данных для экспоненциальной регрессии с использованием функции ЛГРФПРИБЛ;

3) составить прогноз о прибыли за 2008–2009 гг., используя вышеназванные функции;

4) построить диаграмму для исходных и полученных рядов данных.

Отметим, что, в отличие от функций ТЕНДЕНЦИЯ и РОСТ, ни одна из перечисленных выше функций (НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ) не является регрессией. Эти функции играют лишь вспомогательную роль, определяя необходимые параметры регрессии.

Для линейной и экспоненциальной регрессий, построенных с помощью функций НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ, внешний вид их уравнений всегда известен, в отличие от линейной и экспоненциальной регрессий, соответствующих функциям ТЕНДЕНЦИЯ и РОСТ.

Рекомендуемая литература: [1, 2, 5, 6, 15].

Лабораторная работа № 5
Модели линейной оптимизации в MS EXCEL

Цель: приобрести навыки в использовании настройки Поиск решения с условием максимизации или минимизации целевой функции.

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

· сформулировать цели, которые должны быть достигнуты в результате реализации найденного решения;

· указать, что считать решением проблемы (решение должно гарантировать достижение целей);

· выявить и описать возможности достижения целей;

· выявить и описать факторы, от которых может зависеть решение проблемы;

· выявить и описать ограничения, препятствующие достижению целей;

· описать возможные альтернативные способы решения проблемы.

Эти пункты составляют формальную модель проблемы. Таким образом, формальная модель – это четкое описание вашей проблемы, в которой необходимо выделить перечисленные пункты.

Очень часто математическая постановка экономических задач, связанных с управлением, может быть сформулирована в общем виде следующим образом.

Пусть имеет место некоторая целевая функция z, которая зависит от параметров х = (х1, х2, …, хn),удовлетворяющих некоторым ограничениям α:

Требуется найти такие значения параметров или функций,которые обращают величину z в максимум или минимум. Такие задачи — отыскание значений параметров, обеспечивающих экстремум функции при наличии ограничений, наложенных на аргументы, – носят общее название задач математического программирования и решаются ме­тодами теории исследования операций.

Среди задач математического программирования самы­ми простыми являются задачи линейного программирова­ния (ЗЛП).

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

Инструментом для решений задач оптимизации в MS Ехсеl служит надстройка Поиск решения. Процедура поиска решения позволяет найти оптимальное значение фор­мулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках.

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

Решение поставленной задачи состоит из выполнения следующих действий:

1) анализа ситуации и формализации исходной проблемы (поставить проблему, четко определить цели, возможные решения и факторы, влияющие на решение проблемы);

2) построения математической модели (перевод формальной модели на четкий язык математических отношений);

3) анализа математической модели и получения математического решения проблемы (анализ построенной математической модели, построение компьютерной модели задачи);

4) анализа математического решения проблемы и формирование управленческого решения (на основе математического решения принимается управленческое решение).

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

Задание. Предприятие «Олимп» имеет месячный цикл производства. Необходимо определить, сколько в месяц необходимо производить краски типа А и типа Б. Производственная мощность позволяет выпускать в месяц суммарно 500 т краски всех типов. Тонна краски типа А приносит в среднем 2000 руб. прибыли, а одна тонна краски типа Б – 2500 руб. Заказ на краску типа А – не менее 200 т в месяц (по договорам на поставку), краски типа Б нельзя производить более 150 т, так как большее количество трудно реализовать. По рецептуре на изготовление краски типов А и Б тратится три вида сырья (табл. 5.1).

Источник: lektsii.org

Учите КОМПЬЮТЕР вместе с нами!

Учимся работать в Excel, Word, VBA. Финансовая математика, статистика, полезные подсказки

Страницы

воскресенье, 18 ноября 2018 г.

Линейный прогноз с помощью функции ТЕНДЕНЦИЯ() в Excel

О том, как строить линейный прогноз с помощью функции ТЕНДЕНЦИЯ() вы узнаете из нашего видеоурока на канале “Учите компьютер вместе с нами”, или можете воспользоваться рекомендациями ниже.

Рис. 2. Определение аргументов функции ТЕНДЕНЦИЯ() для случая парной регрессии

Итак, заполнив окно, как показано на рис. 2 и нажав кнопку “ОК”, мы в ячейке В18 получим прогнозное значение У=983,0 для 17-ого периода времени. Далее, растягиваем вниз введенную в ячейку В18 формулу до ячейки В21. В результате, прогнозные значения У на последующие 4 периода, ячейки В18:В21, примут значения, как показано на рис. 3.

Рис. 3. Прогноз для случая парной регрессии

И в заключение, чтобы логически завершить этот пример, построим по данным полученной таблицы точечную диаграмму. рис. 4. Синими маркерами обозначены исходные значения У, а красными – его прогноз. Как видим, прогноз имеет ярко выраженный линейный характер (красные маркеры выстроились по прямой линии).

Рис. 4. Диаграмма результатов прогнозирования показателя У для случая парной регрессии

В следующем примере рассмотрим такую ситуацию:

– имея данные по динамике перечисленных показателей за последние 24 месяца нам необходимо рассчитать возможное значение спроса У, в зависимости от наиболее вероятных значений цены Х1 и дохода потребителей Х2.

Поскольку результативный показатель У зависит сразу от двух факторов Х1 и Х2, в данном случае будем иметь множественную регрессию. Исходные данные введены в ячейки А2:С25, рис. 5.

Рис. 5. Исходные данные для случая множественной регрессии

В ячейках А26:В29 содержатся наиболее вероятные значения цены товара и дохода потребителей на следующие 4 периода. Соответственно, в ячейках С26:С29 нам необходимо с помощью функции ТЕНДЕНЦИЯ() найти прогнозные значения спроса.

Выделяем ячейку С26 и с помощью мастера функций вводим в нее функцию ТЕНДЕНЦИЯ() со следующими значениями аргументов, рис. 6.

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

Источник: videolections.blogspot.com