Линейная функция Excel, как использовать

Обновлено: 21.11.2024

В этом примере показано, как решить систему линейных уравнений в Excel. Например, у нас есть следующая система линейных уравнений:

5x + 1г + 8z = 46
4x - 2y = 12
6x + 7y< /td> + 4z = 50

В матричной записи это можно записать как AX = B

5 1 8 x 46
с A = 4 -2 0 , X = y , B = 12
6 7 4 z 50

Если A-1 (обратное A) существует, мы можем умножить обе части на A-1, чтобы получить X = A-1 B. Чтобы решить эту систему линейных уравнений в Excel, выполните следующие шаги. <р>1. Используйте функцию MINVERSE, чтобы получить обратную матрицу A. Сначала выберите диапазон B6:D8. Затем вставьте функцию MINVERSE, показанную ниже. Завершите, нажав CTRL + SHIFT + ENTER.

Примечание. Строка формул указывает, что ячейки содержат формулу массива. Таким образом, вы не можете удалить один результат. Чтобы удалить результаты, выберите диапазон B6:D8 и нажмите Delete.

<р>2. Используйте функцию МУМНОЖ, чтобы получить произведение матриц A-1 и B. Сначала выберите диапазон G6:G8. Затем вставьте функцию МУМНОЖ, показанную ниже. Завершите, нажав CTRL + SHIFT + ENTER.

<р>3. Поставить все это вместе. Сначала выберите диапазон G6:G8. Затем вставьте формулу, показанную ниже. Завершите, нажав CTRL + SHIFT + ENTER.

<р>4. Если у вас Excel 365 или Excel 2021, просто выберите ячейку G6, введите ту же формулу, что и выше, и нажмите Enter. Прощай, фигурные скобки.

Примечание: эта формула динамического массива, введенная в ячейку G6, заполняет несколько ячеек. Ух ты! Такое поведение в Excel 365/2021 называется сбросом.

Во многих случаях цели и/или ограничения в модели оптимизации являются линейными функциями переменных. Это означает, что функцию можно записать в виде суммы членов, где каждый член состоит из одной переменной решения, умноженной на (положительную или отрицательную) константу. Алгебраически мы можем написать:

где ai, называемые коэффициентами, обозначают постоянные значения, а xi обозначают переменные решения. . Обычный пример: =СУММ(C1:C5), где C1:C5 – переменные решения, а все ai равны 1. Обратите внимание, что линейную функцию необязательно записывать в точном виде показано выше на электронной таблице. Например, если ячейки C1 и C2 являются переменными решения, B1 = C1+C2 и B2 = A1*B1, где A1 является константой в задаче, тогда B2 является линейной функцией (=A1*C1+ A1*C2).

Геометрически линейная функция всегда представляет собой прямую линию в n-мерном пространстве, где n — количество переменных решения. Ниже представлен перспективный график 2x1 +1x2. Как отмечалось выше, линейная функция всегда выпукла.

Помните, что ai должно быть постоянным только в задаче оптимизации, т. е. не зависеть ни от одной из переменных решения. Например, предположим, что функция имеет вид =B1/B2*C1 + (D1*2+E1)*C2, где только C1 и C2 являются переменными решения, а остальные ячейки содержат константы (или формулы, которые не зависят от переменные). Это по-прежнему будет линейной функцией, где a1 = B1/B2 и a2 = (D1*2+E1) — коэффициенты, а x1 = C1 и x2 = C2 — это переменные.

Функция СУММПРОИЗВ в Excel точно вычисляет алгебраическое выражение, показанное выше. Если бы мы поместили формулу =B1/B2 в ячейку A1, а формулу =(D1*2+E1) в ячейку A2, то мы могли бы записать приведенный выше пример функции как:

При использовании СУММПРОИЗВ для вычисления значения линейной цели или ограничения убедитесь, что один диапазон аргументов состоит только из ячеек переменной решения, а другой диапазон аргументов состоит только из ячеек, которые не являются (и не зависят от) ячеек переменной решения.

Функция FORECAST.LINEAR относится к категории Excel Статистические функции Функции Список наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает сотни функций, которые важно знать аналитику Excel. Он рассчитает или предскажет будущую стоимость, используя линейную регрессию.

По финансовому моделированию Бесплатное руководство по финансовому моделированию В этом руководстве по финансовому моделированию представлены советы и передовые методы работы с Excel по предположениям, факторам, прогнозированию, связыванию трех утверждений, анализу DCF и т. д. Функция FORECAST.LINEAR может быть полезна при расчете статистического значения. сделанного прогноза. Например, если мы знаем прошлые доходы и расходы, которые составляют определенный процент от продаж, мы можем спрогнозировать будущие суммы с помощью этой функции.

Формула

=ПРОГНОЗ.ЛИНЕЙНЫЙ(x, известные_y, известные_x)

Функция FORECAST.LINEAR использует следующие аргументы:

  1. X (обязательный аргумент). Это числовое значение x, для которого мы хотим спрогнозировать новое значение y.
  2. Known_y’s (обязательный аргумент) — зависимый массив или диапазон данных.
  3. Known_x’s (обязательный аргумент) — это независимый массив или диапазон данных, которые нам известны.

Как использовать функцию FORECAST.LINEAR в Excel?

В качестве функции рабочего листа FORECAST.LINEAR можно ввести как часть формулы в ячейку рабочего листа. Чтобы понять использование функции, давайте рассмотрим пример:

Пример

Предположим, что нам даны данные о доходах (известные значения x) и расходы (известные значения y). Мы можем использовать функцию ПРОГНОЗ.ЛИНЕЙНЫЙ, чтобы предсказать дополнительную точку на прямой линии наилучшего соответствия набору известных значений x и y.

Используя данные о доходах за январь 2019 г., мы можем спрогнозировать расходы за тот же месяц с помощью функции ПРОГНОЗ.ЛИНЕЙНЫЙ.

Используемая формула:

Мы получаем следующие результаты:

Функция FORECAST.LINEAR рассчитает новое значение y, используя простое уравнение прямой линии:

Значения x и y являются выборочными средними (средними) известных значений x и y.

Несколько советов о функции FORECAST.LINEAR:

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по функции Excel FORECAST.LINEAR. Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

  • Все статьи об Excel Ресурсы Excel Изучайте Excel онлайн с помощью сотен бесплатных руководств, ресурсов, руководств и шпаргалок по Excel! Ресурсы CFI — лучший способ изучить Excel на своих условиях.
  • Шаблоны моделирования Excel Шаблоны Excel и финансовых моделей Загрузите бесплатные шаблоны финансовых моделей. Библиотека электронных таблиц CFI включает в себя шаблон финансовой модели из 3 отчетов, модель DCF, график задолженности, график амортизации, капитальные затраты, проценты, бюджеты, расходы, прогнозирование, диаграммы, графики, расписания, оценка, сопоставимый анализ компаний, другие шаблоны Excel
  • Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel.Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

Функция ПРОГНОЗ (или ПРОГНОЗ.ЛИНЕЙНЫЙ) в Excel прогнозирует будущее значение по линейному тренду. Функция FORECAST.ETS в Excel прогнозирует будущее значение с помощью экспоненциального тройного сглаживания, которое учитывает сезонность.

Примечание: функция ПРОГНОЗ является старой функцией. Microsoft Excel рекомендует использовать новую функцию ПРОГНОЗ.ЛИНЕЙНАЯ, которая дает точно такой же результат.

ПРОГНОЗ.ЛИНЕЙНЫЙ

<р>1. Приведенная ниже функция ПРОГНОЗ.ЛИНЕЙНЫЙ прогнозирует будущее значение в соответствии с линейным трендом.

Объяснение: когда мы перетаскиваем функцию FORECAST.LINEAR вниз, абсолютные ссылки ($B$2:$B$11 и $A$2:$A$11) остаются прежними, а относительная ссылка (A12) изменяется на A13 и А14.

<р>2. Введите значение 89 в ячейку C11, выберите диапазон A1:C14 и вставьте точечный график с прямыми линиями и маркерами.

Примечание. Когда вы добавляете линию тренда на диаграмму Excel, Excel может отображать уравнение на диаграмме. Это уравнение предсказывает одни и те же будущие значения.

ПРОГНОЗ.ETS

Функция ПРОГНОЗ.ETS в Excel 2016 или более поздних версиях — отличная функция, которая может обнаруживать сезонные закономерности.

<р>1. Приведенная ниже функция FORECAST.ETS прогнозирует будущую стоимость с помощью экспоненциального тройного сглаживания.

Примечание: последние 3 аргумента являются необязательными. Четвертый аргумент указывает длину сезонного паттерна. Значение по умолчанию 1 означает, что сезонность определяется автоматически.

<р>2. Введите значение 49 в ячейку C13, выберите диапазон A1:C17 и вставьте точечный график с прямыми линиями и маркерами.

<р>3. Вы можете использовать функцию FORECAST.ETS.SEASONALITY, чтобы найти продолжительность сезонной модели. Увидев диаграмму, вы, вероятно, уже знаете ответ.

Вывод: в этом примере при использовании функции ПРОГНОЗ.ETS можно также использовать значение 4 в качестве четвертого аргумента.

Прогноз

Используйте инструмент "Лист прогнозов" в Excel 2016 или более поздней версии, чтобы автоматически создать визуальный лист прогнозов.

<р>1. Выберите диапазон A1:B13, показанный выше.

<р>2. На вкладке "Данные" в группе "Прогноз" нажмите "Таблица прогнозов".

Excel открывает диалоговое окно, показанное ниже.

<р>3. Укажите, когда заканчивается прогноз, установите доверительный интервал (по умолчанию 95 %), автоматически определите сезонность или вручную задайте продолжительность сезонного паттерна и т. д.

Этот инструмент использует функцию ПРОГНОЗ.ETS и рассчитывает те же будущие значения. Нижняя и верхняя доверительные границы являются приятным бонусом.

Объяснение: в период 13 мы можем быть уверены на 95%, что количество посетителей будет между 86 и 94.

Читайте также: