|
Содержание работы или список заданий
|
Лабораторная работа № 2
Использование команды «Поиск решения» для оптимизационных задач
Цель работы: Познакомиться с функциональными возможностями табличного процессора Microsoft Excel и общей методологией использования электронной таблицы в профессиональной работе с данными.
Задачи работы:
Уметь строить математическую модель оптимизации транспортных затрат, производить расчет по формулам в среде Microsoft Excel;
Освоить методику расчета данных с помощью команды «Поиск решения».
Требования к отчету: Итоги лабораторной работы представить в виде таблицы, полученной в результате компьютерной обработки информации.
Введение
Для решения задач оптимизации в MS Excel используется команда «Поиск решения».
Команда «Поиск решений» – функция Excel, которую используют для оптимизации параметров: прибыли, плана продаж, схемы доставки грузов, маркетингового бюджета или рентабельности. Она помогает составить расписание сотрудников, распределить расходы в бизнес-плане или инвестиционные вложения. Знание этой функции экономит много времени и сил.
Основные параметры поиска решений
Найти решение задачи можно тремя способами. Во-первых, вручную перебирать параметры, пока не найдется оптимальное соотношение. Во-вторых, составить уравнение с большим количеством неизвестных. В-третьих, вбить данные в Excel и использовать «Поиск решений». Последний способ самый быстрый и покажет максимально точное решение, если знать, как использовать функцию.
Итак, решаем задачу с помощью команды «Поиск решений» в Excel и начинаем с математической модели. В ней четыре типа данных: константы, изменяемые ячейки, целевая функция и ограничения.
Константы – исходная информация. К ней относится удельная маржинальная прибыль, стоимость каждой перевозки, нормы расхода товарно-материальных ценностей. Также константа отражает ограничения и условия математической модели: например, только неотрицательные или целые значения. Вносим константы в таблицу цифрами или с помощью элементарных формул (СУММ, СРЗНАЧ).
Изменяемые ячейки – переменные, которые в итоге нужно найти. В разных случаях бывает одна изменяемая ячейка или диапазон. При заполнении команды «Поиск решений» важно оставить ячейки пустыми – программа сама найдет значения.
Целевая функция – результирующий показатель, для которого Excel подбирает наилучшие показатели. Чтобы программа понимала, какие данные наилучшие, задаем функцию в виде формулы. Эту формулу мы отображаем в отдельной ячейке. Результирующий показатель может принимать максимальное или минимальное значения, а также быть конкретным числом.
Ограничения – условия, которые необходимо учесть при оптимизации функции, называющейся целевой. К ним относятся размеры инвестирования, срок реализации проекта или объем покупательского спроса.
Общий алгоритм решения оптимизационных задач в MS Excel следующий:
Составить математическую модель.
Ввести на рабочий лист Excel условия задачи:
создать таблицу на рабочем листе для ввода условий задачи;
ввести исходные данные, целевую функцию, ограничения и граничные условия.
Выполнить команду Данные → Анализ → Поиск решения.
Указать параметры в диалоговом окне Параметры поиска решения, выполнить решение.
Проанализировать полученные результаты.
Настройка команды «Поиск решения».
Настройка осуществляется с помощью команды Данные → Анализ → Поиск решения (рис. 1).
Рисунок 1 – Местонахождение команды
Если команда «Поиск решения» или группа Анализ отсутствует на вкладке Данные, то необходимо загрузить соответствующую надстройку:
Выбрать команду Файл → Параметры.
В диалоговом окне Параметры Ехсеl выбрать категорию Надстройки (рис. 2).
Рисунок 2 – Диалоговое окно «Параметры»
В поле Управление выбрать значение Надстройки Excel, затем кнопку Перейти.
В поле Доступные надстройки установить флажок рядом с пунктом Поиск решения (рис. 3) и нажать кнопку ОК.
Рисунок 3 – Диалоговое окно «Надстройки Excel»
После выполнения этих действий команда «Поиск решения» будет доступной в группе команд Анализ вкладки Данные (рис. 1).
Параметры команды «Поиск решения»
Как отмечалось ранее, доступ к команде «Поиск решения» осуществляется с помощью команды Данные → Анализ → Поиск решения. Данная команда отображает окно диалога Параметры поиска решения (рис. 4).
Рисунок 4 – Диалоговое окно «Параметры поиска решения»
Перед использованием рассматриваемого инструмента на листе электронной таблицы должны быть сформированы целевая функция, область изменяемых ячеек (неизвестные), значения которых будут найдены в процессе решения. Решение (изменяемые ячейки) должно находиться в определенных пределах или удовлетворять определенным ограничениям.
Параметры задачи ограничиваются такими предельными показателями:
количество неизвестных – 200;
количество формульных ограничений на неизвестные – 100;
количество предельных условий на неизвестные – 400.
В окне диалога Параметры поиска решения в поле Оптимизировать целевую функцию указывается адрес ячейки с целевой функцией. Целевая функция зависит от изменяемых ячеек и связана с ними некоторой формулой. Оптимизируется значение целевой функции до максимума, минимума, или некоторого определенного значения.
В поле Изменяя ячейки переменных указывается адрес блока ячеек, которые и будут решением.
В область В соответствии с ограничениями вводятся ограничения на решение. Кнопки Добавить, Изменить, Удалить управляют ограничениями, их действия интуитивно понятны.
Если в пределах одного рабочего листа Excel необходимо рассмотреть несколько моделей оптимизации (например, найти максимум и минимум одной функции или максимальные значения нескольких функций), то удобнее сохранить эти модели, используя кнопку Загрузить/сохранить. Диапазон для сохраняемой модели содержит информацию о целевой ячейке, об изменяемых ячейках, о каждом из ограничений и все значения окна диалога Параметры. Выбор сохраненной ранее модели для решения конкретной оптимизационной задачи осуществляется также с помощью кнопки Загрузить/сохранить.
Флажок в поле Сделать переменные без ограничений неотрицательными позволяет не вводить дополнительно ограничения на изменяемые ячейки, если их значения неотрицательны.
Поиск решения в зависимости от типа решаемых задач, позволяет использовать методы:
Симплексный метод.
Метод ОПГ (обобщенного приведенного градиента).
Эволюционный поиск решения.
Метод решения выбирается из раскрывающегося списка Выберите метод решения рассматриваемого окна диалога.
Кнопка Найти решение запускает процесс решения задачи.
Иногда в результате выполнения процедуры поиска решения само решение не находится, даже если известно, что решение существует. Часто эту проблему удается решить, изменив некоторые параметры и повторно запустив Поиск решения. Указанные параметры устанавливаются в диалоговом окне Параметры (рис. 5), которое отобразится, если в окне диалога Параметры поиска решения выбрать кнопку Параметры.
Рисунок 5 – Диалоговое окно «Параметры»
Ниже рассмотрим основные параметры вкладки Все методы.
Точность ограничения. Указывает насколько точно выполняются ограничения. Задача может быть решена быстрее, если задать меньшую точность.
Использовать автоматическое масштабирование. Служит для автоматической нормализации входных и выходных значений, значительно различающихся по величине.
Показывать результаты итераций. Если этот параметр активизирован, то после выполнения очередной итерации решение приостанавливается, и отображаются найденные результаты.
Игнорировать целочисленные ограничения. При установке этого параметра игнорируются ограничения, определяющие, что значения должны быть целыми. Применение этого параметра иногда позволяет найти решение, которое в противном случае обнаружить нельзя.
Максимальное время. Предоставляет возможность ограничить максимальное время решения задачи (в секундах). Если появится сообщение, что время на решение задачи истекло, то его можно добавить.
Число итераций. Используется для ввода максимального числа промежуточных решений, допустимых при поиске решения.
Максимальное число подзадач. Параметр предназначен для решения сложных задач. Позволяет задать максимальное количество подзадач, которые могут использоваться при применении эволюционного алгоритма.
Максимальное число допустимых решений. Параметр предназначен для решения сложных задач. Позволяет задать максимальное количество приемлемых решений, которые могут использоваться при применении эволюционного алгоритма.
Две другие вкладки диалогового окна Параметры содержат дополнительные параметры, используемые методами обобщенного приведенного градиента и эволюционного поиска.
Порядок выполнения лабораторной работы:
Теоретические сведения.
Математическая постановка задачи
Транспортная задача относится к специальным задачам линейного программирования.
Общая постановка транспортной задачи состоит в определении оптимального плана перевозок некоторого однородного груза из m пунктов отправления A1, A2,…, Am, в n пунктов назначения B1, B2,…, Bn.
При этом в качестве критерия оптимальности обычно берется либо минимальная стоимость перевозок всего груза, либо минимальное время его доставки. Рассмотрим транспортную задачу, в качестве критерия оптимальности, которой взята минимальная стоимость перевозок всего груза. Обозначим через cij тарифы перевозки единицы груза из i-го пункта отправления в j-й пункт назначения, через ai – запасы груза в i-м пункте отправления, через bj – потребности в грузе в j-м пункте назначения, а через xij количество единиц груза, перевозимого из i-го пункта отправления в j-й пункт назначения.
Тогда математическая постановка транспортной задачи состоит в определении минимального значения функции:
Z=∑_(i=1)^m ∑_(j=1)^n▒〖c_ij∙x_ij 〗 (1)
при условиях:
Z=∑_(j=1)^3▒〖x_ij=a_i (i=1,2)〗 (2)
Z=∑_(i=1)^2▒〖x_ij=b_i (j=1,2,3)〗 (3)
x_ij≥0 (i=1,2;j=1,2,3) (4)
Функция (1) называется целевой функцией задачи. Поскольку переменные удовлетворяют системам линейных уравнений (2) и (3) и условию неотрицательности (4), обеспечиваются доставка необходимого количества груза в каждый из пунктов назначения, вывоз имеющегося груза из всех пунктов отправления, а также исключаются обратные перевозки.
План Z* = (xij*) (i = 1, 2, …, m; j = 1, 2, …, n), при котором функция (1) принимает свое минимальное значение, называется оптимальным планом транспортной задачи.
Если общая потребность в грузе в пунктах назначения равна запасу груза в пунктах отправления, то есть выполняется равенство (5), то модель такой транспортной задачи называется закрытой. Если же указанное условие не выполняется, то модель транспортной задачи называется открытой.
∑_(i=1)^m▒a_i =∑_(j=1)^n▒b_j (5)
Для разрешимости транспортной задачи необходимо и достаточно, чтобы запасы груза в пунктах отправления были равны потребностям в грузе в пунктах назначения, то есть чтобы выполнялось равенство (5).
В случае превышения запаса над потребностью, то есть при неравенстве:
∑_(i=1)^m▒a_i >∑_(j=1)^n▒b_j
вводят фиктивный (і + 1)-й пункт назначения с потребностью
b_(n+1)=∑_(i=1)^m▒a_i -∑_(j=1)^n▒b_j
и соответствующие тарифы считаются равными нулю:
c_(in+1)=0 (1,…,m)
Полученная задача является транспортной задачей, для которой выполняется равенство (5).
Аналогично, при выполнении неравенства:
∑_(i=1)^m▒a_i <∑_(j=1)^n▒b_j
вводят фиктивный (m + 1)-й пункт отправления с запасом груза
a_(m+1)=∑_(j=1)^n▒b_j -∑_(i=1)^m▒a_i
и соответствующие тарифы полагают равными нулю:
c_(m+1j)=0 (1,…,n)
Этим задача с водится к транспортной задаче с закрытой моделью.
Задание:
Задача определения оптимального плана перевозок
В пунктах A и B находятся соответственно 150 и 190 т горючего. Пунктам 1, 2, 3 требуются соответственно 160, 70, 110 т. горючего. Стоимость перевозки 1 т горючего из пункта A в пункты 1, 2, 3 равна 60, 10, 40 тыс. руб. за 1 т соответственно, а из пункта B в пункты 1, 2, 3 – 120, 20, 80 тыс. руб. за 1 т соответственно. Составьте план перевозок горючего, минимизирующий общую сумму транспортных расходов.
Строим математическую модель.
Обозначим переменные:
xij – количество горючего, перевозимого с i-го предприятия j-ому потребителю (i = 1, 2, j = 1, 2, 3).
сij – тариф перевозки 1 т горючего с i-го предприятия j-ому потребителю (i = 1, 2, j = 1, 2, 3).
ai – объем производства на i-м предприятии (i = 1, 2).
bj – объем потребление j-ого потребителя (j = 1, 2, 3).
Модель рассматриваемой транспортной задачи является закрытой, т.к.
∑_(i=1)^2▒a_i =∑_(j=1)^3▒b_j
150 + 190 = 160 + 70 + 110
Тогда условия доставки и вывоза необходимого и имеющегося количества горючего обеспечивается за счет выполнения следующих соглашений:
∑_(i=1)^3▒〖x_ij=b_i (j=1,2,3)〗
∑_(j=1)^3▒〖x_ij=a_i (i=1,2)〗
x_ij≥0 (i=1,2;j=1,2,3)
При этом общая стоимость перевозок составит:
Z=∑_(i=1)^2 ∑_(j=1)^3▒〖c_ij∙x_ij 〗 (1)
Таким образом, математическая постановка данной транспортной задачи состоит в нахождении такого неотрицательного решения системы линейных уравнений, при котором целевая функция F принимает минимальное значение.
Системы с учетом исходных данных можно записать следующим образом:
x11 + x21 = 160
x12 + x22 = 70
x13 + x23 = 110
x11 + x12 + x13 = 150
x21 + x22 + x23 = 190
Целевая функция при этом имеет вид:
Z = 60x11 + 10x12 + 40x13 + 120x21 + 20x22 + 80x23
Исходные данные.
Составим для наглядности таблицу исходных данных (см. табл. 1).
Таблица 1 – Исходные данные
Поставщики Потребители Запасы
1 2 3
А 60 10 40 150
В 120 20 80 190
Потребность 160 70 110
Выполнение работы.
Выполните следующую подготовительную работу для решения транспортной задачи с помощью средства Поиск решения в табличном процессоре Microsoft Excel:
Введите в ячейки диапазона B4:D5 стоимости перевозок (см. рис. 6).
Рисунок 6 – Вид рабочего окна
Отведите ячейки диапазона B8:D9 под значения неизвестных (объемов перевозок). Ячейки должны быть пустыми!
Введите в ячейки диапазона F8:F9 объемы запасов горючего у поставщиков.
Введите в ячейки диапазона B11:D11 будет содержать оптимальный план доставки горючего. Значения этих ячеек вычисляется в процессе решения задачи.
В ячейку B14 введем формулу для целевой функции Z = 60x11 + 10x12 + 40x13 + 120x21 + 20x22 + 80x23. Для этого используем функцию СУММПРОИЗВ(B4:D5;B8:D9).
Сделать это можно, выбрав в категории Математические функции СУММПРОИЗВ и указав необходимый диапазон.
В ячейки диапазонов E8:E9 введите формулы, вычисляющие объемы запасов у поставщиков, в ячейки диапазона B10:D10 – формулы расчета объемов доставляемого топлива к потребителям (табл. 2).
Таблица 2 – Формулы для расчета
Ячейка Формула
E8 =СУММ(B8:D8)
E9 =СУММ(B9:D9)
B10 =СУММ(B8:B9)
C10 =СУММ(C8:C9)
D10 =СУММ(D8:D9)
При этом на экране должны отображаться данные, как показано на рис. 7.
Рисунок 7 – Отображение данных
Теперь для решения транспортной задачи подключаем команду MS Excel «Поиск решения». Для этого на вкладке Данные в группе Анализ выберем команду Поиск решения.
На экране отобразится диалоговое окно Параметры поиска решения, в котором установим следующие параметры:
в поле Оптимизировать целевую функцию указываем адрес ячейки со значением целевой функции – В14;
выбираем нахождение минимума целевой функции;
в поле Изменяя ячейки переменных указываем адреса ячеек со значениями искомых переменных B8:D9;
устанавливаем флажок Сделать переменные без ограничений неотрицательными;
в списке Выберите метод решения указываем Поиск решения линейных задач симплекс-методом.
Введем ограничения в диалоговое окно Параметры поиска решения.
Все ограничения указаны в условиях. Для добавления ограничений необходимо выбрать кнопку Добавить. Отобразится окно диалога Добавление ограничений.
Добавляем ограничения для системы.
В поле Ссылка на ячейки указываем адрес диапазона B10:D10, выбираем в раскрывающемся списке знак равенства =, в поле Ограничение выделяем диапазон B11:D11 и нажимаем кнопку Добавить (рис. 8). Ограничение будет добавлено в список ограничений, поля окна Добавление ограничения будут очищены для ввода следующего ограничения.
Рисунок 8 – Окно «Изменение ограничения»
В поле Ссылка на ячейки указываем адрес диапазона E8:E9, выбираем в раскрывающемся списке знак неравенства ≤, в поле Ограничение выделяем диапазон F8:F9 и нажимаем кнопку ОК (рис. 9).
Рисунок 9 – Окно «Изменение ограничения»
В результате будет принято последнее ограничение и возврат к диалоговому окну Параметры поиска решения (10).
У нас в области В соответствии с ограничениями не введены ограничения о неотрицательности искомых переменных. Это сделано сознательно, т.к. ранее в окне Параметры поиска решения установлен флажок Сделать переменные без ограничений неотрицательными.
Рисунок 10 – Окно «Параметры поиска решения»
После выбора кнопки Найти решение отобразится окно Результаты поиска решения (рис. 11). В данном окне дано сообщение, что решение найдено.
Рисунок 11 – Окно «Поиск решения»
Для сохранения полученного решения необходимо использовать переключатель Сохранить найденное решение и нажать кнопку ОК. После чего на рабочим листе отобразится решение задачи (рис. 12).
Рисунок 12 – Результаты решения задачи
В результате решения задачи получили общую стоимость перевозок 20400 денежных единиц.
При этом, экономическая интерпретация результатов будет следующая: поставщик A перевозит потребителю 1 – 150 т горючего, поставщик В – потребителям 1, 2 и 3 – 10, 70 и 110 т горючего соответственно. При этом затраты на перевозку продукции будут минимальными и составят 20400 денежных единиц.
Покажем результаты решения задачи еще в виде отчетов по результатам (рис. 13), устойчивости (рис. 14) и пределам (рис. 15).
Рисунок 13 – Отчет о результатах
Рисунок 14 – Отчет об устойчивости
Рисунок 15 – Отчет о пределах
Лабораторная работа № 4
Прогнозирование развития автотранспортного предприятия по статистическим данным
Цель работы: Освоить вывод уравнений линий тренда и получить навыки проведения регрессионного анализа.
Задачи работы:
1. Уметь строить линии тренда по табличным данным в среде Microsoft Excel;
2. Используя уравнения линий тренда, получать табличные данные по прибыли предприятия для каждой линии тренда за год.
Обеспечивающие средства: учебная база данных; персональный компьютер; табличный процессор Microsoft Excel.
Задание:
Задача 1
С таблицей данных о прибыли автотранспортного предприятия за 2012-2019 гг. (рис. 1) необходимо выполнить следующие действия:
Рисунок 1 – Данные о прибыли автотранспортного предприятия за 2012-2019 г.г.
1. Построить диаграмму.
2. В диаграмму добавить линейную и полиномиальную (квадратичную и кубическую) линии тренда.
3. Вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них.
4. Используя уравнения линий тренда, получить табличные данные по прибыли предприятия для каждой линии тренда за 2012-2021 г.г.
5. Составить прогноз по прибыли предприятия на 2020 и 2021 г.г.
Задача 2
С таблицей данных о прибыли автотранспортного предприятия за 2012-2019 гг., приведенной в задаче 1 (см. рис. 1), необходимо выполнить следующие действия:
1. Построить диаграмму.
2. В диаграмму добавить логарифмическую, степенную и экспоненциальную линии тренда.
3. Вывести уравнения полученных линий тренда, а также величины достоверности аппроксимации R2 для каждой из них.
4. Используя уравнения линий тренда, получить табличные данные о прибыли предприятия для каждой линии тренда за 2012-2019 г.г.
5. Составить прогноз о прибыли предприятия на 2020 и 2021 г.г., используя эти линии тренда.
Задача 3
С таблицей данных о прибыли автотранспортного предприятия за 2012-2019 гг., приведенной в задаче 1 (рис. 1), необходимо выполнить следующие действия.
1. Получить ряды данных для линейной и экспоненциальной линии тренда с использованием функций ТЕНДЕНЦИЯ и РОСТ.
2. Используя функции ТЕНДЕНЦИЯ и РОСТ, составить прогноз о прибыли предприятия на 2020 и 2021 гг.
3. Для исходных данных и полученных рядов данных построить диаграмму.
Задача 4
С таблицей данных о поступлении в диспетчерскую службу автотранспортного предприятия заявок на услуги за период с 1 по 11 число текущего месяца (см. рис. 10) необходимо выполнить следующие действия:
1. Получить ряды данных для линейной регрессии: используя функции НАКЛОН и ОТРЕЗОК; используя функцию ЛИНЕЙН.
2. Получить ряд данных для экспоненциальной регрессии с использованием функции ЛГРФПРИБЛ.
3. Используя вышеназванные функции, составить прогноз о поступлении заявок в диспетчерскую службу на период с 12 по 14 число текущего месяца.
4. Для исходных и полученных рядов данных построить диаграмму.
Требования к отчету: Итоги лабораторной работы представить в виде таблиц и графиков, полученных в результате компьютерной обработки информации.
Порядок выполнения лабораторной работы:
Задача 1
1. В диапазон ячеек A4:C11 рабочего листа Excel вводим рабочую таблицу, представленную на рис. 1. Выделив диапазон ячеек В4:С11, строим диаграмму (Вставка → Диаграмма → График).
2. Активизируем построенную диаграмму и после выбора типа линии тренда в диалоговом окне Линия тренда (Диаграмма → Добавить линию тренда… → Тип) поочередно добавляем в диаграмму линейную, квадратичную и кубическую линии тренда. В этом же диалоговом окне открываем вкладку Параметры, в поле Название аппроксимирующей (сглаженной) кривой вводим наименование добавляемого тренда, а в поле Прогноз вперед на: периодов задаем значение 2, так как планируется сделать прогноз по прибыли на два года вперед. Для вывода в области диаграммы уравнения регрессии и значения достоверности аппроксимации R2 включаем флажки показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2).
3. Для лучшего визуального восприятия изменяем тип, цвет и толщину построенных линий тренда, для чего воспользуемся вкладкой Вид диалогового окна Формат линии тренда. Полученная диаграмма с добавленными линиями тренда представлена на рис. 2.
Тренд – это функция заданного вида, с помощью которой можно аппроксимировать график, построенный по данным таблицы. Тренд служит для выявления тенденций развития процесса, представленного в виде диаграммы, и обеспечивает прогноз на заданный период.
4. Для получения табличных данных по прибыли предприятия для каждой линии тренда за 2012-2021 г.г. воспользуемся уравнениями линий тренда, представленными на рис. 2. Для этого в ячейки диапазона D3:F3 вводим текстовую информацию о типе выбранной линии тренда: линейный тренд, квадратичный тренд, кубический тренд. Далее вводим в ячейку D4 формулу линейной регрессии и, используя маркер заполнения, копируем эту формулу c относительными ссылками в диапазон ячеек D5:D13. Следует отметить, что каждой ячейке с формулой линейной регрессии из диапазона ячеек D4:D13 в качестве аргумента стоит соответствующая ячейка из диапазона A4:A13. Аналогично для квадратичной регрессии заполняется диапазон ячеек E4:E13, а для кубической регрессии – диапазон ячеек F4:F13. Таким образом, составлен прогноз по прибыли предприятия на 2020 и 2021 г.г. с помощью трех трендов. Полученная таблица значений представлена на рис. 3.
Рисунок 2 – Диаграмма с добавленными линиями тренда
Рисунок 3 – Таблица прогноза по прибыли предприятия на 2019 и 2020 г.г.
Задача 2
Следуя методике, приведенной при решении задачи 1, получаем диаграмму с добавленными в нее логарифмической, степенной и экспоненциальной линиями тренда (рис. 4).
Рисунок 4 – Диаграмма с добавленными в нее логарифмической, степенной и экспоненциальной линиями тренда
Далее, используя полученные уравнения линий тренда, заполняем таблицу значений по прибыли предприятия, включая прогнозируемые значения на 2020 и 2021 гг. (рис. 5).
Рисунок 5 – Таблица значений по прибыли предприятия
Задача 3
Воспользуемся рабочей таблицей задачи 1 (рис. 1). Начнем с функции ТЕНДЕНЦИЯ. Для этого выделяем диапазон ячеек D4:D11, который следует заполнить значениями функции ТЕНДЕНЦИЯ, соответствующими известным данным о прибыли предприятия, вызываем команду Функция из меню Формулы. В появившемся диалоговом окне Мастер функций выделяем функцию ТЕНДЕНЦИЯ из категории Статистические, после чего щелкаем по кнопке <ОК>. Эту же операцию можно осуществить нажатием кнопки (Вставка функции) стандартной панели инструментов. В появившемся диалоговом окне Аргументы функции вводим в поле Известные_значения_y диапазон ячеек C4:C11; в поле Известные_значения_х – диапазон ячеек B4:B11. Чтобы вводимая формула стала формулой массива, при закрытии окна Аргументы функции используем комбинацию клавиш + + .
Введенная нами формула в строке формул будет иметь следующий вид: {=ТЕНДЕНЦИЯ(C4:C11; B4:B11)}
В результате диапазон ячеек D4:D11 заполняется соответствующими значениями функции ТЕНДЕНЦИЯ (см. рис. 6).
Рисунок 6 – Функция ТЕНДЕНЦИЯ для диапазона ячеек D4:D11
Для составления прогноза о прибыли предприятия на 2020 и 2021 г.г. необходимо выполнить ниже приведенные действия.
Выделить диапазон ячеек D12:D13, куда будут заноситься значения, прогнозируемые функцией ТЕНДЕНЦИЯ. Вызвать функцию ТЕНДЕНЦИЯ и в появившемся диалоговом окне Аргументы функции ввести в поле Известные_значения_y – диапазон ячеек C4:C11; в поле Известные_значения_х – диапазон ячеек B4:B11; а в поле Новые_значения_х – диапазон ячеек B12:B13. Превратить эту формулу в формулу массива, используя комбинацию клавиш + + при закрытии окна Аргументы функции.
Введенная формула будет иметь следующий вид: {=ТЕНДЕНЦИЯ(C4:C11; B4:B11; B12:B13)}, а диапазон ячеек D12:D13 заполнится прогнозируемыми значениями функции ТЕНДЕНЦИЯ (см. рис. 7).
Рисунок 7 – Заполненный столбец с помощью функции ТЕНДЕНЦИЯ
Аналогично заполняется ряд данных с помощью функции РОСТ, которая используется при анализе нелинейных зависимостей и работает точно так же, как ее линейный аналог ТЕНДЕНЦИЯ. На рис. 8 представлена таблица в режиме показа формул.
Рисунок 8 – Таблица в режиме формул
Для исходных данных и полученных рядов данных построим диаграмму, изображенную на рис. 9.
Рисунок 9 – Диаграмма «Динамика прибыли предприятия за 2012-2021 г.г.»
Задача 4
Отметим, что, в отличие от функций ТЕНДЕНЦИЯ и РОСТ, ни одна из перечисленных выше функций (НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ) не является регрессией. Эти функции играют лишь вспомогательную роль, определяя необходимые параметры регрессии.
Для линейной и экспоненциальной регрессий, построенных с помощью функций НАКЛОН, ОТРЕЗОК, ЛИНЕЙН, ЛГРФПРИБ, внешний вид их уравнений всегда известен, в отличие от линейной и экспоненциальной регрессий, соответствующих функциям ТЕНДЕНЦИЯ и РОСТ.
1. Построим линейную регрессию, имеющую уравнение: y = mx + b, с помощью функций НАКЛОН и ОТРЕЗОК, причем угловой коэффициент регрессии m определяется функцией НАКЛОН, а свободный член b – функцией ОТРЕЗОК.
Для этого осуществляем следующие действия: заносим исходную таблицу в диапазон ячеек A4:B14; значение параметра m будет определяться в ячейке С20. Вызываем команду Функция из меню Вставка. В появившемся диалоговом окне Мастер функций выделяем функцию НАКЛОН из категории Статистические, после чего щелкаем по кнопке <ОК>. Заносим диапазон ячеек B4:B14 в поле Известные_ значения_y и диапазон ячеек А4:А14 в поле Известные_значения_х. В ячейку С20 будет введена формула: =НАКЛОН(B4:B14; A4:A14).
По аналогичной методике определяется значение параметра b в ячейке D20. И ее содержимое будет иметь следующий вид: =ОТРЕЗОК(B4:B14; A4:A14)
Таким образом, необходимые для построения линейной регрессии значения параметров m и b будут сохраняться соответственно в ячейках C20 и D20.
Далее заносим в ячейку С4 формулу линейной регрессии в виде: =$C$20*A4+$D$20
В этой формуле ячейки С20 и D20 записаны с абсолютными ссылками (адрес ячейки не должен меняться при возможном копировании). Знак абсолютной ссылки $ можно набить либо с клавиатуры, либо с помощью клавиши F4, предварительно установив курсор на адресе ячейки. Воспользовавшись маркером заполнения, копируем эту формулу в диапазон ячеек С4:С17. Получаем искомый ряд данных (рис. 10). В связи с тем, что количество заявок – целое число, следует установить на вкладке Число окна Формат ячеек (Формат → Ячейки) числовой формат с числом десятичных знаков 0.
Рисунок 10 – Таблица данных о поступлении заявок в диспетчерскую службу
Теперь построим линейную регрессию, заданную уравнением: y = mx + b, с помощью функции ЛИНЕЙН. Для этого, вводим в диапазон ячеек C21:D21 функцию ЛИНЕЙН как формулу массива: {=ЛИНЕЙН(B4:B14; A4:A14)}.
В результате получаем в ячейке C21 значение параметра m, а в ячейке D21 – значение параметра b. Вводим в ячейку D4 следующую формулу: =$C$21*A4+$D$21, копируем эту формулу с помощью маркера заполнения в диапазон ячеек D4:D17 и получаем искомый ряд данных.
2. Строим экспоненциальную регрессию, имеющую уравнение: y = bmx, с помощью функции ЛГРФПРИБЛ оно выполняется аналогично: в диапазон ячеек C22:D22 вводим функцию ЛГРФПРИБЛ как формулу массива:
{=ЛГРФПРИБЛ(B4:B14; A4:A14)}
При этом в ячейке C22 будет определено значение параметра m, а в ячейке D22 – значение параметра b; в ячейку E4 вводится формула: =$D$22*$C$22^A4.
С помощью маркера заполнения эта формула копируется в диапазон ячеек E4:E17, где и расположится ряд данных для экспоненциальной регрессии. На рис. 11 приведена таблица, где видны используемые нами функции с необходимыми диапазонами ячеек, а также формулы.
Рисунок 11 – Таблица с введенными формулами
3. Для исходных данных и полученных рядов данных построена диаграмма, изображенная на рис. 12.
Рисунок 12 – Диаграмма «Динамика поступления заявок»
|