Методы оптимальных решений. Транспортная задача в MS Excel

Есть некие предприятия и склады с грузом. Каждое предприятие, нуждается в определённом объёме нашего груза. Каждый склад доставляет тонну груза по собственному тарифу. Таким образом, нужно составить маршрут, по которому мы развезём объём груза, удовлетворяющий каждое предприятие, и при этом затратим меньше всего средств.

Так транспортная задача выглядит в своём наиболее общем и типовом виде.

транспортная задача выглядит

С – это цена за тонну. X – это то, сколько мы привезём тонн со склада на предприятие. Например, если мы примем X11 равным 5, это будет значить, что со склада А1 к потребителю B1 мы повезём 5 тонн по цене C11. Вот нам и нужно как-то распределить всё так, чтобы потратить меньше всего денег.

сколько мы привезём тонн со склада на предприятие

Включение функции

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

Для того, чтобы произвести активацию Поиска решений в программе Microsoft Excel 2010 года, и более поздних версий, переходим во вкладку «Файл». Для версии 2007 года, следует нажать на кнопку Microsoft Office в левом верхнем углу окна. В открывшемся окне, переходим в раздел «Параметры».

В окне параметров кликаем по пункту «Надстройки». После перехода, в нижней части окна, напротив параметра «Управление» выбираем значение «Надстройки Excel», и кликаем по кнопке «Перейти».

Открывается окно с надстройками. Ставим галочку напротив наименования нужной нам надстройки – «Поиск решения». Жмем на кнопку «OK».

После этого, кнопка для запуска функции Поиска решений появится на ленте Excel во вкладке «Данные».

Решение финансовых задач в Excel

Чаще всего для этой цели применяются финансовые функции. Рассмотрим пример.

Условие. Рассчитать, какую сумму положить на вклад, чтобы через четыре года образовалось 400 000 рублей. Процентная ставка – 20% годовых. Проценты начисляются ежеквартально.

Оформим исходные данные в виде таблицы:

Так как процентная ставка не меняется в течение всего периода, используем функцию ПС (СТАВКА, КПЕР, ПЛТ, БС, ТИП).

  1. Ставка – 20%/4, т.к. проценты начисляются ежеквартально.
  2. Кпер – 4*4 (общий срок вклада * число периодов начисления в год).
  3. Плт – 0. Ничего не пишем, т.к. депозит пополняться не будет.
  4. Тип – 0.
  5. БС – сумма, которую мы хотим получить в конце срока вклада.

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

Для проверки правильности решения воспользуемся формулой: ПС = БС / (1 + ставка)кпер. Подставим значения: ПС = 400 000 / (1 + 0,05)16 = 183245.

Как в эксель решить функцию. Поиск решения задач в Excel с примерами. Примеры и задачи на поиск решения в Excel

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

Рассмотрим задачу нахождения корня уравнения методом Ньютона с использованием циклических ссылок. Возьмем для примера квадратное уравнение: х2 — 5х + 6=0, графическое представление которого приведено на рис. 8. Найти корень этого (и любого другого) уравнения можно, используя всего одну ячейку Excel.

Для включения режима циклических вычислений в меню Сервис/Параметры/вкладка Вычисления включаем флажок Итерации, при необходимости изменяем число повторений цикла в поле Предельное число итераций и точность вычислений в поле Относительная погрешность (по умолчанию их значения равны 100 и 0,0001 соответственно). Кроме этих установок выбираем вариант ведения вычислений: автоматически или вручную. При автоматическом вычислении Excel выдает сразу конечный результат, при вычислениях, производимых вручную, можно наблюдать результат каждой итерации.

Рис. 8. График функции

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

где F и F1 задают соответственно выражения для вычисления значений функции и ее производной. Для нашего квадратного уравнения после ввода формулы в ячейке появится значение 2, соответствующее одному из корней уравнения (рис. 8). В нашем случае начальное приближение не задавалось, итерационный вычислительный процесс начинался со значения, по умолчанию хранимого в ячейке Х и равного нулю. А как получить второй корень? Обычно это можно сделать изменением начального приближения. Решать проблему задания начальных установок в каждом случае можно по-разному. Мы продемонстрируем один прием, основанный на использовании функции ЕСЛИ. С целью повышения наглядности вычислений ячейкам были присвоены содержательные имена (рис. 9).

В ячейку Хнач (В4) заносим начальное приближение — 5.

В ячейку Хтекущ (С4) записываем формулу:
=ЕСЛИ(Хтекущ=0;Хнач; Хтекущ-(Хтекущ^2-5*Хтекущ+6)/(2*Хтекущ-5)).

В ячейку D4 помещаем формулу, задающую вычисление значения функции в точке Хтекущ, что позволит следить за процессом решения.

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

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

значения. Чтобы обнулить значение, хранящееся в ячейке Хтекущ, нужно заново записать туда формулу. Для этого достаточно для редактирования выбрать ячейку, содержащую формулу, дважды щелкнув мышью на ней (при этом содержимое ячейки отобразится в строке формул). Щелчок по кнопке (нажатие клавиши) Enter запустит вычисления с новым начальным приближением.

2.2. Подбор параметра

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

Возьмем в качестве примера все то же квадратное уравнение х2-5х+6=0. Для нахождения корней уравнения выполним следующие действия:

В ячейку С3 (рис. 10) введем формулу для вычисления значения функции,

стоящей в уравнении слева от знака равенства. В качестве аргумента используем ссылку на ячейку С2, т.е. =С2^2-5*C2+6.

В окне диалога Подбор параметра (рис. 10) в поле Установить в ячейке введем ссылку на ячейку с формулой, в поле Значение — ожидаемый результат, в поле Изменяя значения ячейки — ссылку на ячейку, в которой будет храниться значение подбираемого параметра (содержимое этой ячейки не может быть формулой).

После нажатия на кнопку Ok Excel выведет окно диалога Результат подбора параметра. Если подобранное значение необходимо сохранить, то нажмите на Оk, и результат будет сохранен в ячейке, заданной ранее в поле Изменяя значения ячейки. Для восстановления значения, которое было в ячейке С2 до использования команды Подбор параметра, нажмите кнопку Отмена.

При подборе параметра Excel использует итерационный (циклический) процесс. Количество итераций и точность устанавливаются в меню Сервис/Параметры/вкладка Вычисления. Если Excel выполняет сложную задачу подбора параметра, можно нажать кнопку Пауза в окне диалога Результат подбора параметра и прервать вычисление, а затем нажать кнопку Шаг, чтобы выполнить очередную итерацию и просмотреть результат. При решении задачи в пошаговом режиме появляется кнопка Продолжить — для возврата в обычный режим подбора параметра.

Вернемся к примеру. Опять возникает вопрос: как получить второй корень? Как и в предыдущем случае необходимо задать начальное приближение. Это можно сделать следующим образом (рис. 11,а):

а
б
Рис. 11. Поиск второго корня

В ячейку Х (С2) вводим начальное приближение.

В ячейку Хi (С3) вводим формулу для вычисления очередного приближения к корню, т.е. =X-(X^2-5*X+6)/(2*X-5).

В ячейку С4 поместим формулу, задающую вычисление значения функции, стоящей в левой части исходного уравнения, в точке Хi.

После этого выбираем команду Подбор параметра, где в качестве изменяемой ячейки принимаем ячейку С2. Результат вычислений изображен на рис. 11,б (в ячейке С2 — конечное значение, а в ячейке С3 — предыдущее).

Однако все это можно сделать и несколько проще. Для того чтобы найти второй корень, достаточно в качестве начального приближения (рис. 10) в ячейку C2 поместить константу 5 и после этого запустить процесс Подбор параметра.

2.3. Поиск решения

Команда Подбор параметра является удобной для решения задач поиска определенного целевого значения, зависящего от одного неизвестного параметра. Для более сложных задач следует использовать команду Поиск решения (Решатель), доступ к которой реализован через пункт меню Сервис/Поиск решения.

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

Найти:
х1, х2, … , хn
такие, что:
F(х1, х2, … , хn) >
при ограничениях:
G(х1, х2, … , хn) > <>Value; Перейдем к вводу формул для решения

Дискриминант квадратного трехчлена равен b 2 -4ac

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

Квадратное уравнение имеет два корня, в случае если дискриминант больше нуля. В ячейку C3 введем формулу для x 1

Для расчета x2 введем похожую формулу, но со знаком плюс

Соответственно при введенных значениях a,b,c сначала считается дискриминант, если его значения меньше нуля выводится сообщение «Корней нет», иначе получаем значения x 1 и x 2 .

Защита листа в Excel

Нам нужно защитить лист, на котором мы производили расчеты. Без защиты нужно оставить ячейки, в которые можно вводить значения a,b,c, то есть ячейки B2 B3 B4. Для этого выделим данный диапазон и зайдем в формат ячеек, перейдем во вкладку Рецензирования, Защитить лист и уберем флажок с позиции Защищаемая ячейка. Нажмем кнопку OK, подтвердив внесенные изменения.

Этот диапазон ячеек будет не защищен при защите листа. Выполним защиту листа, для этого перейдем на вкладку Рецензирование пункт Защита листа. Пароль наберем 1234. Нажмем OK.

Теперь мы сможем изменять значения ячеек B2,B3,B4. При попытке изменения других ячеек мы получим сообщение следующего содержания: «Ячейка или диаграмма защищена от изменений. А так же совет по снятию защиты.

Так же вас может заинтересовать материал как закрепить .

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

Вот пример системы линейных уравнений:
3x + 4y = 8
4x + 8y = 1

Решение состоит в нахождении таких значений х и у , которые удовлетворяют обоим уравнениям. Эта система уравнений имеет одно решение:
x = 7,5
y = -3,625

Количество переменных в системе уравнений должно быть равно количеству уравнений. Предыдущий пример использует два уравнения с двумя переменными. Три уравнения требуются для того, чтобы найти значения трех переменных (х ,у и z ). Общие действия по решению систем уравнений следующие (рис. 128.1).

  1. Выразите уравнения в стандартной форме. Если это необходимо, используйте основы алгебры и перепишите уравнение так, чтобы все переменные отображались по левую сторону от знака равенства. Следующие два уравнения идентичны, но второе приведено в стандартном виде:
    3x — 8 = -4y
    3x + 4y = 8 .
  2. Разместите коэффициенты в диапазоне ячеек размером n x n , где n представляет собой количество уравнений. На рис. 128.1 коэффициенты находятся в диапазоне I2:J3 .
  3. Разместите константы (числа с правой стороны от знака равенства) в вертикальном диапазоне ячеек. На рис. 128.1 константы находятся в диапазоне L2:L3 .
  4. Используйте массив формул для расчета обратной матрицы коэффициентов. На рис. 128.1 следующая формула массива введена в диапазон I6:J7 (не забудьте нажать Ctrl+Shift+Enter , чтобы ввести формулу массива): =МОБР(I2:J3) .
  5. Используйте формулу массива для умножения обратной матрицы коэффициентов на матрицу констант. На рис. 128.1 следующая формула массива введена в диапазон J10:JJ11 , который содержит решение (x = 7,5 и у = -3,625): =МУМНОЖ(I6:J7;L2:L3) . На рис. 128.2 показан лист, настроенный для решения системы из трех уравнений.

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

Способ 1: матричный метод

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

14x1 +2x2 +8x4 =218
7x1 -3x2 +5x3 +12x4 =213
5x1 +x2 -2x3 +4x4 =83
6x1 +2x2 +x3 -3x4 =21

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

Аргумент «Массив» — это, собственно, адрес исходной таблицы.

Итак, выделяем на листе область пустых ячеек, которая по размеру равна диапазону исходной матрицы. Щелкаем по кнопке «Вставить функцию» , расположенную около строки формул.

Выделяем диапазон, в нашем случае состоящий из четырех ячеек. Далее опять запускаем Мастер функций , нажав значок «Вставить функцию» .

Способ 2: подбор параметров

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

Этот результат также можно проверить, подставив данное значение в решаемое выражение вместо значения x .

Способ 3: метод Крамера

Теперь попробуем решить систему уравнений методом Крамера. Для примера возьмем все ту же систему, которую использовали в Способе 1 :

Читайте также:  Анализ инвестиционного проекта в Excel скачать

14x1 +2x2 +8x4 =218
7x1 -3x2 +5x3 +12x4 =213
5x1 +x2 -2x3 +4x4 =83
6x1 +2x2 +x3 -3x4 =21

    Как и в первом способе, составляем матрицу A из коэффициентов уравнений и таблицу B из значений, которые стоят после знака «равно» .

Таким образом, как и у функции МОБР , единственным аргументом выступает ссылка на обрабатываемую таблицу.

Итак, выделяем ячейку, в которой будет выводиться определитель первой матрицы. Затем жмем на знакомую по предыдущим способам кнопку «Вставить функцию» .

Способ 4: метод Гаусса

Решить систему уравнений можно также, применив метод Гаусса. Для примера возьмем более простую систему уравнений из трех неизвестных:

14x1 +2x2 +8x3 =110
7x1 -3x2 +5x3 =32
5x1 +x2 -2x3 =17

    Опять последовательно записываем коэффициенты в таблицу A , а свободные члены, расположенные после знака «равно» — в таблицу B . Но на этот раз сблизим обе таблицы, так как это понадобится нам для работы в дальнейшем. Важным условием является то, чтобы в первой ячейке матрицы A значение было отличным от нуля. В обратном случае следует переставить строки местами.

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

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

После ввода формулы выделяем весь ряд и применяем сочетание клавиш Ctrl+Shift+Enter .

Таким образом, мы делим последнюю рассчитанную нами строку на её же третий коэффициент. После того, как набрали формулу, выделяем всю строчку и жмем сочетание клавиш Ctrl+Shift+Enter .

Жмем привычное уже нам сочетание клавиш для применения формулы массива.

Опять выделяем всю строку и применяем сочетание клавиш Ctrl+Shift+Enter .

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

Запуск инструмента Поиск решения

После того, как таблица подготовлена, находясь во вкладке «Данные», жмем на кнопку «Поиск решения», которая расположена на ленте в блоке инструментов «Анализ».

Запуск поиска решений в Microsoft Excel

Открывается окно параметров, в которое нужно внести данные. В поле «Оптимизировать целевую функцию» нужно ввести адрес целевой ячейки, где будет располагаться общая сумма премии для всех работников. Это можно сделать либо пропечатав координаты вручную, либо кликнув на кнопку, расположенную слева от поля введения данных.

Переход к вводу целевой ячейки в Microsoft Excel

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

Выбор целевой ячейки в Microsoft Excel

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

Установка значения целевой ячейки в Microsoft Excel

Ниже расположено поле «Изменяя ячейки переменных». Тут нужно указать адрес искомой ячейки, где, как мы помним, находится коэффициент, умножением на который основной заработной платы будет рассчитана величина премии. Адрес можно прописать теми же способами, как мы это делали для целевой ячейки.

Установка искомой ячейки в Microsoft Excel

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

Добавление ограничения в Microsoft Excel

После этого, открывается окно добавления ограничения. В поле «Ссылка на ячейки» прописываем адрес ячеек, относительно которых вводится ограничение. В нашем случае, это искомая ячейка с коэффициентом. Далее проставляем нужный знак: «меньше или равно», «больше или равно», «равно», «целое число», «бинарное», и т.д. В нашем случае, мы выберем знак «больше или равно», чтобы сделать коэффициент положительным числом. Соответственно, в поле «Ограничение» указываем число 0. Если мы хотим настроить ещё одно ограничение, то жмем на кнопку «Добавить». В обратном случае, жмем на кнопку «OK», чтобы сохранить введенные ограничения.

Параметры ограничения в Microsoft Excel

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

Установка неотрицательных значений в Microsoft Excel

Дополнительные настройки можно задать, кликнув по кнопке «Параметры».

Переход к параметрам поиска решений в Microsoft Excel

Здесь можно установить точность ограничения и пределы решения. Когда нужные данные введены, жмите на кнопку «OK». Но, для нашего случая, изменять эти параметры не нужно.

Параметры Поиска решения в Microsoft Excel

После того, как все настройки установлены, жмем на кнопку «Найти решение».

Переход к поиску решения в Microsoft Excel

Далее, программа Эксель в ячейках выполняет необходимые расчеты. Одновременно с выдачей результатов, открывается окно, в котором вы можете либо сохранить найденное решение, либо восстановить исходные значения, переставив переключатель в соответствующую позицию. Независимо от выбранного варианта, установив галочку «Вернутся в диалоговое окно параметров», вы можете опять перейти к настройкам поиска решения. После того, как выставлены галочки и переключатели, жмем на кнопку «OK».

Результаты поиска решений в Microsoft Excel

Если по какой-либо причине результаты поиска решений вас не удовлетворяют, или при их подсчете программа выдаёт ошибку, то, в таком случае, возвращаемся, описанным выше способом, в диалоговое окно параметров. Пересматриваем все введенные данные, так как возможно где-то была допущена ошибка. В случае, если ошибка найдена не была, то переходим к параметру «Выберите метод решения». Тут предоставляется возможность выбора одного из трех способов расчета: «Поиск решения нелинейных задач методом ОПГ», «Поиск решения линейных задач симплекс-методом», и «Эволюционный поиск решения». По умолчанию, используется первый метод. Пробуем решить поставленную задачу, выбрав любой другой метод. В случае неудачи, повторяем попытку, с использованием последнего метода. Алгоритм действий всё тот же, который мы описывали выше.

Выбор метода решения в Microsoft Excel

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Решение математических задач в Excel

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

Условие учебной задачи. Найти обратную матрицу В для матрицы А.

  1. Делаем таблицу со значениями матрицы А.
  2. Выделяем на этом же листе область для обратной матрицы.
  3. Нажимаем кнопку «Вставить функцию». Категория – «Математические». Тип – «МОБР».
  4. В поле аргумента «Массив» вписываем диапазон матрицы А.
  5. Нажимаем одновременно Shift+Ctrl+Enter — это обязательное условие для ввода массивов.

Возможности Excel не безграничны. Но множество задач программе «под силу». Тем более здесь не описаны возможности которые можно расширить с помощью макросов и пользовательских настроек.

Практические работы в MS E xcel

Лабораторный практикум предназначен для практического изучения раздела, расчеты в «Электронных таблицах MS Excel — 2007» в рамках дисциплины «Информационные технологии в профессиональной деятельности» студентами второго курса различных специальностей ГБОУ СПО Политехнический колледж №42 г. Москва.

Практикум состоит из четырех практических работ по основным темам применения MS E xcel в расчётах, ориентирован в основном на студентов, обучающихся по специальностям «Экономика и бухгалтерский учет (по отраслям)», « Операционная деятельность в логистике » и «Монтаж и техническая эксплуатация промышленного оборудования (по отраслям)». Некоторые темы практических работ могут использовать в обучении и студенты других специальностей.

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

Подбор параметра в Excel

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

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

Из расчетной части рис.1.3 видно, что при заданных исходных данных требуется ежемесячно выплачивать по 1350 руб. для погашения займа. Предположим, что по каким-то причинам кредитор имеется возможность выплачивать не более 1200 руб. в месяц. Спрашивается, какую максимальную величину ссуды может он запросить, если все прочие условия сохраняются?

Для решения этой задачи выберем команду Данные > Анализ «что если» > Подбор параметра (рис. 2.1). В верхнем поле этого окна указывается ссылка на ячейку D7, в которой устанавливается желаемый результат (в нашем случае – это -1200 руб). В нижнее поле диалогового окна вставляется ссылка на ячейку, в которой хранится значение искомого параметра, т.е. D4.

Рис. 2.1 — Диалоговое окно Подбор параметра в Excel

При нажатии клавиши ОК мы получим максимальную сумму займа, при условии выплаты ежемесячно 1 200 руб. Рис.2.2

Рис. 2.2 – Максимальная величина займа 17 783 руб.

Вывод: Выполнение анализа «что-если» в Excel обеспечивает достаточно оперативную оценку влияния того или иного аргумента на результат вычисления.

Рассмотрим пример задачи линейного программирования.

Математическая модель задачи имеет вид:

где xj – количество выпускаемой продукции j-го типа; F – функция цели; в левых частях выражений ограничений указаны величины потребного ресурса, а правые части показывают количество имеющегося ресурса.

Ввод условий задачи

Для решения задачи с помощью Excel следует создать форму для ввода исходных данных и ввести их. Форма ввода показана на рис. 2.

В ячейку F6 введено выражение целевой функции как суммы произведений значений прибыли от выпуска единицы продукции каждого типа на количество выпускаемой продукции соответствующего типа. Для наглядности на рис. 3 представлена форма ввода исходных данных в режиме вывода формул.

В ячейки F8:F10 введены левые части ограничений для ресурсов каждого вида.

clip_image004

clip_image006

Решение задачи линейного программирования

Для решения задач линейного программирования в Excel используется мощный инструмент, называемый Поиск решения. Обращение к Поиску решения осуществляется из меню Сервис, на экран выводится диалоговое окно Поиска решения (рис. 4).

clip_image008

Ввод условий задачи для поиска ее решения состоит из следующих шагов:

1 Назначить целевую функцию, для чего установить курсор в поле Установить целевую ячейку окна Поиск решения и щелкнуть в ячейке F6 в форме ввода;

2 Включить переключатель значения целевой функции, т.е. указать ее Равной Максимальному значению;

3 Ввести адреса изменяемых переменных (xj): для этого установить курсор в поле Изменяя ячейки окна Поиск решения, а затем выделить диапазон ячеек B3:E3 в форме ввода;

4 Нажать кнопку Добавить окна Поиск решения для ввода ограничений задачи линейного программирования; на экран выводится окно Добавление ограничения (рис. 5) :

— ввести граничные условия для переменных xj (xj³0), для этого в поле Ссылка на ячейку указать ячейку В3, соответствующую х1, выбрать из списка нужный знак (³), в поле Ограничение указать ячейку формы ввода, в которой хранится соответствующее значение граничного условия, (ячейка В4), нажать кнопку Добавить; повторить описанные действия для переменных х2, х3 и х4;

— ввести ограничения для каждого вида ресурса, для этого в поле Ссылка на ячейку окна Добавление ограничения указать ячейку F9 формы ввода, в которой содержится выражение левой части ограничения, наложенного на трудовые ресурсы, в полях Ограничение указать знак £ и адрес Н9 правой части ограничения, нажать кнопку Добавить; аналогично ввести ограничения на остальные виды ресурсов;

— после ввода последнего ограничения вместо Добавить нажать ОК и возвратиться в окно Поиск решения.

clip_image010

Решение задачи линейного программирования начинается с установки параметров поиска:

— в окне Поиск решения нажать кнопку Параметры, на экран выводится окно Параметры поиска решения (рис. 6);

— установить флажок Линейная модель, что обеспечивает применение симплекс-метода;

— указать предельное число итераций (по умолчанию – 100, что подходит для решения большинства задач);

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

— нажать ОК, возврат в окно Поиск решения .

clip_image012

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

clip_image014

Для рассматриваемого примера решение найдено и результат оптимального решения задачи выводится в форме ввода: значение целевой функции, соответствующее максимальной прибыли и равное 1320, указывается в ячейке F6 формы ввода, оптимальный план выпуска продукции х1=10, х2=0, х3=6, х4=0 указывается в ячейках В3:С3 формы ввода (рис. 8).

Количество использованных для выпуска продукции ресурсов выводится в ячейки F9:F11: трудовых – 16, сырья – 84, финансов – 100.

clip_image016

Если при установке параметров в окне Параметры поиска решения (рис. 6) был установлен флажок Показывать результаты итераций, то будут показаны последовательно все шаги поиска. На экран будет выводиться окно Текущее состояние поиска решения (рис. 9). При этом текущие значения переменных и функции цели будут показаны в форме ввода. Так, результаты первой итерации поиска решения исходной задачи представлены в форме ввода на рисунке 10 .

Читайте также:  Поиск по сайту примеров функций Excel

clip_image018

clip_image020

Чтобы продолжить поиск решения, следует нажимать кнопку Продолжить в окне Текущее состояние поиска решения.

Анализ оптимального решения

Прежде чем, перейти к анализу результатов решения, представим исходную задачу в форме

введя дополнительные переменные уi, представляющие собой величины неиспользованных ресурсов.

Составим для исходной задачи двойственную задачу и введем дополнительные двойственные переменные vi.

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

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

Для вызова отчета в поле Тип отчета выделить название нужного типа и нажать ОК.

1 Отчет по результатам (рис. 11) состоит из трех таблиц:

— таблица 1 содержит сведения о целевой функции; в столбце Исходно указывается значение целевой функции до начала вычислений;

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

— таблица 3 показывает результаты оптимального решения для ограничений и для граничных условий.

Для Ограничений в графе Формула приведены зависимости, которые были введены при задании ограничений в окне Поиск решения; в графе Значение указаны величины использованного ресурса; в графе Разница показано количество неиспользованного ресурса. Если ресурс используется полностью, то в графе Состояние выводится сообщение связанное; при неполном использовании ресурса в этой графе указывается не связан. Для Граничных условий приводятся аналогичные величины с той лишь разницей, что вместо неиспользованного ресурса показана разность между значением переменной xj в найденном оптимальном решении и заданным для нее граничным условием (xj³0).

Именно в графе Разница можно увидеть значения дополнительных переменных yi исходной задачи в формулировке (2). Здесь у13=0, т.е. величины неиспользованных трудовых и финансовых ресурсов равны нулю. Эти ресурсы используются полностью. Вместе с тем, величина неиспользованных ресурсов для сырья у2=26, значит, имеются излишки сырья.

clip_image026

2 Отчет по устойчивости (рис. 12) состоит из двух таблиц.

В таблице 1 приводятся следующие значения:

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

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

— коэффициенты целевой функции;

— предельные значения приращения коэффициентов целевой функции, при которых сохраняется оптимальный план выпуска.

В таблице 2 содержатся аналогичные данные для ограничений:

— величины использованных ресурсов;

Теневая цена, показывающая, как изменится целевая функция при изменении величины соответствующего ресурса на единицу;

— допустимые значения приращений ресурсов, при которых сохраняется оптимальный план выпуска продукции.

clip_image028

Отчет по устойчивости позволяет позволяет получить двойственные оценки.

Как известно, двойственные переменные zi показывают, как изменится целевая функция при изменении ресурса i-го типа на единицу. В отчете Excel двойственная оценка называется Теневой ценой.

В нашем примере сырье не используется полностью и его ресурс у2=26. Очевидно, что увеличение количества сырья, например, до 111 не повлечет за собой увеличения целевой функции. Следовательно, для второго ограничения двойственная переменная z2=0. Таким образом, если по данному ресурсу есть резерв, то дополнительная переменная будет больше нуля, а двойственная оценка этого ограничения равна нулю.

В рассматриваемом примере трудовые ресурсы и финансы использовались полностью, поэтому их дополнительные переменные равны нулю (у13=0). Если ресурс используется полностью, то его увеличение или уменьшение повлияет на объем выпускаемой продукции, и следовательно, на величину целевой функции. Двойственные оценки ограничений на трудовые и финансовые ресурсы отличны от нуля, т.е. z1=20, z3=10.

Значения двойственных оценок находим в Отчете по устойчивости, в таблице 2, в графе Теневая цена.

При увеличении (уменьшении) трудовых ресурсов на единицу целевая функция увеличится (уменьшится) на 20 единиц и будет равна

F=1320+20×1=1340 ( при увеличении).

Аналогично, при увеличении объема финансов на единицу целевая функция будет

Здесь же, в графах Допустимое увеличение и Допустимое уменьшение таблицы 2, показаны допустимые пределы изменения количества ресурсов j-го вида. Например, для при изменении приращения величины трудовых ресурсов в пределах от –6 до 3,55, как показано в таблице, структура оптимального решения сохраняется, т.е наибольшую прибыль обеспечивает выпуск Прод1 и Прод3, но в других количествах.

Дополнительные двойственные переменные также отражены в Отчете по устойчивости в графе Нормир. стоимость таблицы 1.

Если основные переменные не вошли в оптимальное решение, т.е. равны нулю ( в примере х24=0), то соответствующие им дополнительные переменные имеют положительные значения (v2=10, v4=20). Если же основные переменные вошли в оптимальное решение (х1=10, х3=6), то их дополнительные двойственные переменные равны нулю (v1=0, v3=0).

Эти величины показывают, насколько уменьшится (поэтому знак минус в значениях переменных v2 и v4) целевая функция при принудительном выпуске единицы данной продукции. Следовательно, если мы захотим принудительно выпустить единицу продукции вида Прод3, то целевая функция уменьшится на 10 единиц и будет равна 1320 -10×1 =1310.

Обозначим через Dсj изменение коэффициентов целевой функции в исходной модели (1). Эти коэффициенты определяют прибыль, получаемую при реализации единицы продукции j-го вида.

В графах Допустимое увеличение и Допустимое Уменьшение таблицы 1 Отчета по устойчивости показаны пределы изменения Dсj , при которых сохраняется структура оптимального плана, т.е. будет выгодно по-прежнему выпускать продукцию вида Продj. Например, при изменении Dс1 в пределах -12£ Dс1 £ 40, как показано в отчете, по-прежнему будет выгодно выпускать продукцию вида Прод1. При этом значение целевой функции будет F=1320+x1×Dсj =1320+10×Dсj.

3 Отчет по пределам приведен на рис. 13. В нем показывается, в каких пределах могут изменяться значения xj, вошедшие в оптимальное решение, при сохранении структуры оптимального решения. Кроме этого, для каждого типа продукции приводятся значения целевой функции, получаемые при подстановке в оптимальное решение значения нижнего предела выпуска изделий соответствующего типа при неизменных значениях выпуска остальных типов. Например, если при оптимальном решении х1=10, х2=0, х3=6, х4=0 положить х1=0 (нижний предел) при неизменных х2, х3 и х4, то значение целевой функции будет равно 60×0+70×0+120×6+130×0=720.

Далее приводятся верхние пределы изменения xj и значения целевой функции при выпуске продукции, вошедшей в оптимальное решение на верхних пределах. Поэтому везде F=1320.

Параметры задачи в функции Поиска решений

Работа такой надстройки, как Поиск решения в Excel, осуществляется в соответствии с заданными характеристиками вычисления. Наиболее важной из них является метод. Есть два их варианта. «Метод Ньютона» является настройкой по умолчанию. Он способен работать с большей памятью, но меньшими интеграциями. Поэтому для стандартных и не особо сложных уравнений он вполне подойдет.

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

Применение надстройки «Поиск решения» в Excel: Методические указания и задания к лабораторной работе по курсу «Информатика»

предполагаемое значение и повторно запустить надстройку Поиск решения.

Решение системы нелинейных уравнений

Необходимо решить систему уравнений

Система содержит уравнение окружности и уравнение прямой. Решением этой системы являются точки пересечения окружности с прямой. При этом прямая может пересечь окружность в двух точках, коснуться окружности в одной точке или не пересечь окружность вовсе. Таким образом, решений этой системы может быть не более двух или не быть вообще. Пара (х, у) является решением системы уравнения в том случае, если она является решением уравнения с двумя неизвестными:

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

Рис. 8 Решение системы нелинейных уравнений

На рис. 8 в диапазоне A2:J11 представлена таблица значений функции двух переменных. В столбце А этого диапазона находится аргумент х, в строке 2 — аргумент у. Значения функции равные 1 (при х = 1 и у = -1) и 1,06 (при х = -1 и у = 1,5) расположены ближе всего к значению 0 (по сравнению с другими значениями функции). Эти две пары были взяты за первое приближение корней. В диапазон В14:С14 была помещена первая пара, а в диапазон В15:С15 — вторая В диапазоне D14:D15 введены формулы, реализующие левую часть уравнения, формула в ячейке D14 использует значения из диапазона В14:С14, а формула в ячейке D15 -из диапазона В15:С15

Чтобы найти первый корень, нужно выделить ячейку D14, вызвать окно Поиск решения и заполнить его, как показано на рис. 9.

Рис. 9 Поиск решения системы нелинейных уравнений

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

Рис. 10 Результат решения системы уравнений

Оптимизация с помощью надстройки Поиск решения

Надстройку Поиск решения можно использовать для решения задач оптимизации. В качестве простого примера рассмотрим уравнение

На рис. 11 представлен график функции, определяемой уравнением. Эта функция имеет максимум равный 26 при x = 4.

Рис. 11 График функции

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

Рис. 12 Задание начальных значений

Задав в диалоговом окне Поиск решения указанные на рис. 13 параметры, найдем максимум. Соответствующее значение переменной x: отображается в ячейке ВЗ (рис. 13).

Рис. 13 Окно Поиска решений

Рис. 14 Результаты поиска решения

В результате работы надстройки Поиск решения получилось значение 3,99999997656181, несколько отличающееся от 4 (рис. 14). Надстройка прекращает работу, когда достигнута заданная точность (precision). Чтобы установить необходимую точность, воспользуйтесь кнопкой Параметры, расположенной в диалоговом окне Поиск решения.

Поиск экстремума функции двух переменных

В области, заданной условиями и , функция имеет максимум в точке x=1.5708 (или ) и y=0.

Чтобы найти с помощью надстройки Поиск решения координаты x и y максимума функции f(x,y), введите в ячейки предполагаемые значения координат и рассматриваемую функцию (рис. 15).

Задайте в диалоговом окне Поиск решения параметры, необходимые для нахождения значений x и y, в которых формула в ячейке B5 имеет максимум. Так как у рассматриваемой функции бесконечное количество максимумов, необходимо ограничить область поиска условиями и (рис. 16).

Результаты поиска решения верны (рис. 16).

В заданной области у рассматриваемой функции имеется также локальный максимум в точке x=-1 y=2. Если задать в качестве нулевого приближения точки около этого локального максимума (например x=-0.9 y=1.8), то надстройка найдет значение этого локального максимума.

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

Постановка задачи

Исходные данные

Для начала, давайте определимся, какие у нас есть исходные данные и что нам нужно получить на выходе. Фактически, все что у нас есть, это некоторые исторические данные. Если мы говорим о прогнозировании продаж, то историческими данными будут продажи за предыдущие периоды.

Примечание. Собранные в разные моменты времени значения одной и той же величины образуют временной ряд. Каждое значение такого временного ряда называется измерением. Например: данные о продажах за последние 5 лет по месяцам — временной ряд; продажи за январь прошлого года — измерение.

Составляющие прогноза

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

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

Эти три пункта в совокупность образуют регулярную составляющую временного ряда.

Примечание. Не обязательно все три элемента регулярной составляющей должны присутствовать в временном ряде.

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

Вывод. Чтобы комплексно описать временной ряд, необходимо учесть 2 главных компонента: регулярную составляющую (тренд + сезонность + цикличность) и случайную составляющую.

Виды моделей

Следующий вопрос, на который нужно ответить при построении прогноза: “А какие модели временного ряда бывают?”

Обычно выделяют два основных вида:

  • Аддитивная модель: Уровень временного ряда = Тренд + Сезонность + Случайные отклонения
  • Мультипликативная модель: Уровень временного ряда = Тренд X Сезонность X Случайные отклонения

Иногда также выделают смешанную модель в отдельную группу:

  • Смешанная модель: Уровень временного ряда = Тренд X Сезонность + Случайные отклонения

С моделями мы определились, но теперь возникает еще один вопрос: «А когда какую модель лучше использовать?»

Классический вариант такой:
— Аддитивная модель используется, если амплитуда колебаний более-менее постоянная;
— Мультипликативная – если амплитуда колебаний зависит от значения сезонной компоненты.

график пример адаптивной и мультипликативной модели

Решение задач Подбор параметра

Решение задач – одно из важных применений Excel. Самый простой инструмент предназначен для подбора значений и называется «что-если» анализ: задается некоторая целевая функция и ее числовое значение, Excel автоматически подбирает параметры целевой функции до получения целевого значения. Формула в целевой функции должна логически зависеть от подбираемого параметра.

В Excel встроены инструменты для решения задач статистического и инженерного анализа, сложных задач со многими неизвестными и ограничениями, в частности, решения уравнений и задач оптимизации. Эти инструменты поставляются в виде надстроек Поиск решения и Пакет анализа ; устанавливаются через пункт меню Сервис  Надстройки…, далее пометить пункты Поиск решения и Пакет анализа . Смотрите справку – клавиша F1.

Читайте также:  Примеры функция БИЗВЛЕЧЬ в Excel для выборки данных из таблицы

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

В качестве примера разберем расчет прибыли от продаж, показанный на рис. 1.20. В ячейках А3, А4 и А7 записаны формулы – формулы для наглядности продублированы в скобках. Поскольку Другие затраты и Количество здесь постоянны, то нужную прибыль можно получить подбором Цены единицы или Себестоимости единицы товара. Попробуем получить прибыль 20000, изменяя Цену единицы . Проделайте следующие действия:

Встаньте на целевую ячейку А7. Выберите пункт меню С е рвис  П одбор параметра… . Введите значение ожидаемой прибыли. Для ввода адреса изменяемой ячейки, перейдите в нижнее поле и встаньте на ячейку А9 в таблице – адрес будет записан автоматически в поле. Нажмите ОК.

В следующем окне можно либо принять, либо отменить результат подбора параметра. Результат:

Самостоятельно попробуйте подобрать Себестоимость единицы , чтобы получить прибыль 20000 при Цене единицы =200.

Далее приводятся другие применения инструмента Подбор параметра .

Задачи оптимизации с одним неизвестным

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

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

Инструмент Подбор параметра был рассмотрен в предыдущем разделе для расчета прибыли от продаж. Здесь рассмотрим его применение для решения некоторого уравнения, например 2х 3 -3х 2 +х-5=0.

Решение задачи состоит из двух этапов. На первом этапе следует ввести уравнение в ячейку (В2), а в другую ячейку (А2) ввести некоторое ориентировочное значение, здесь 1:

На втором этапе следует воспользоваться инструментом Подбор параметра :

Встаньте на ячейку В2 и вызовите его – С е рвис  П одбор параметра… .

В открывшемся окне введите искомое значение функции и адрес изменяемой ячейки:

После нажатия кнопки ОК Вы получите следующее решение:

Найденное решение приближенное, поэтому можно считать, что при х=1,918578609 значение уравнения 2х 3 -3х 2 +х-5 стремится к нулю, т.е. к 0,000107348. Смело установите для ячеек А2 и В2 числовой формат отображения данных и получите следующее:

Следует отметить, что уравнение может иметь более одного решения. Поэтому рекомендуется выполнить подбор параметра для разных начальных значений Х, указывая положительные, отрицательные, большие и малые значения. В нашем примере установите начальное значение А2=–1 и повторите подбор. Решение будет таким же.

Задача оптимизации затрат

С помощью подбора параметров можно решать задачи оптимального распределения ресурсов следующего плана. Требуется закупить составляющие (конфета карамельная, конфета шоколадная, упаковка печенья и мармелада) для комплектования подарочных наборов так, чтобы цена набора не превышала 100 руб. При этом известны соотношения цен относительно одной из компонент (здесь карамели): цена шоколадной конфеты в 2.5 раза выше цены карамели, цена печенья на 10 руб. больше карамели и цена мармелада в 8.5 раза выше цены карамели. В наборе должно быть 5-10 конфет карамель, 4-6 шоколадных конфет, 1-2 упаковки печенья и 1 упаковка мармелада. Рассчитать закупочные цены для разных комплектов.

Ниже показаны расчеты закупочных цен для максимального и минимального комплектов:

В ячейки В4:В6 запишите формулы: =B3*2.5, =B3+10 и =B3*8.5. В колонки D3:D6 поставьте формулы вычисления сумм по строкам, а в D7 вычислите общую сумму: =СУММ(D3:D6). В итоге целевая функция – стоимость набора D7 есть C3* x +C4*2.5* x +C5*( x +10)+C6*8.5* x , где x =B3 цена одной компоненты – конфеты карамель. Параметры инструмента Подбор параметра заполните так:

Подбор параметра и таблицы подстановки

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

Рассмотрим пример. Фирма производит изделия и продает их по цене 90руб. Ежемесячные постоянные затраты составляют 5000руб., переменные затраты на единицу изделия – 30руб. Необходимо определить точку безубыточности, т.е. вычислить количество изделий, при котором прибыль равна 0. Кроме того, требуется определить изменение прибыли для 10 следующих значений количества с шагом 5, а также прибыль при этих значениях количества для цен 80, 85, 95 и 100руб.

Решение. Введите в Excel исходные данные, приведенные ниже:

В точке безубыточности валовая прибыль равна валовым затратам, т.е. (В3*В4)-(В1+В2*В4)=0. Вызовите Подбор параметра , заполните параметры и нажмите ОК – в ячейке В4 будет вычислено значение 83.33 (рис. 17).

На следующем шаге рассчитаем 10 значений прибыли для следующих значений количества с шагом 5. Используем для этого таблицу подстановки с одним изменяемым параметром. Подготовьте исходные данные (рис. 2.35) : в ячейки C4:C13 запишите значения количества с шагом 5, а в колонке справа в строке выше (ячейка D3) — формулу из ячейки В7.

Примените инструмент Таблица подстановки к подготовленным данным: выделите диапазон C3:D13 (рис. 2.36), вызовите пункт Д анные  Т аблица подстановки… , укажите изменяемую ячейку ($B$4) и порядок расположения исходных данных (в строках) в окне запроса – рис. 2.36. После нажатия ОК в ячейках D4:D13 будут рассчитаны значения прибыли.

На последнем шаге рассчитаем значения прибыли для тех же значений количества при ценах 80, 85, 95 и 100руб. Используем для этого таблицу подстановки с двумя изменяемыми параметрами. Результат показан на рис. 2.37.

Для его получения подготовьте исходные данные : в ячейки F4:F13 запишите значения количества, в строке G3:J3 запишите значения цен, на пересечении строки и столбца с данными в ячейке F3 запишите формулу из ячейки В7. Примените инструмент Таблица подстановки к подготовленным данным: выделите диапазон F3:F13 (рис. 2.37), вызовите пункт Д анные  Т аблица подстановки… , укажите изменяемую ячейку по строкам ($B$4) и по столбцам ($B$3) в окне запроса – рис. 2.38. После нажатия ОК в ячейках G4:J13 будут рассчитаны значения прибыли.

Задания для самостоятельного выполнения:

Найдите решение уравнения х 3 +5х 2 -х+1=0

Найдите два решения уравнения х 2 -3х+2=0.

Найдите решение уравнения е х =20000.

Примените инструмент Таблица подстановки при решении предыдущей задачи комплектования подарочных наборов.

Практические примеры логических функций в Excel

Logic function primer 1 Практические примеры логических функций в Excel Доброго времени суток уважаемый читатель!

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

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

Ну что же давайте теперь порешаем задачки с примерами использования логических функций:

Итак, начнем решать поставленные задачи.

Задача №1:

На складе содержится группа товаров, к которому применимо определение «залежались», то есть его срок хранения подходит к концу и необходимо произвести переоценку, включить акцию или сделать скидку, срезав цену в 2 раза.

Возьмем за основу 3 условия:

  • Если срок хранения товара 8 и больше месяцев, необходимо вводить акции для увеличения продаж;
  • Если срок хранения увеличился до 10 месяцев и больше делаем скидку 50% на все позиции;
  • В случае, когда срок хранения достигает 12 месяцев, режем цену в два раза и убираем остатки со склада до момента истечения срока хранения.

Исходя из условий мы группируем наличный товар по условиям с помощью функции ЕСЛИ с тремя вложенными условиями (лишние пробелы нужно убрать):

=ЕСЛИ ( D2 >= 12; » Режем цену в 2 раза » ; ЕСЛИ ( D2 >= 10 ; » Скидка 50% » ; ЕСЛИ ( D2 >= 8; «Акционный товар»; «»)))

Logic function primer 2 Практические примеры логических функций в Excel

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

Исходя из предыдущих 3 условий, будем делать 3 скидки:

  • Скидка 20% для категории «Акционный товар»;
  • Скидка 50% для товаров, которые на складе уже 10 месяцев и больше;
  • Делим цену на два для товара, сроки хранения, которых уже «горят».

Теперь, исходя из полученных условий, можно вывести сумму скидки на товар в зависимости от времени нахождения его на складе. Для получения результата воспользуемся формулой на базе функции ЕСЛИ:

=ЕСЛИ ( E2 = «Режем цену в 2 раза» ; C2/2 ; ЕСЛИ ( E2 = «Скидка 50%» ; C2*50% ; ЕСЛИ ( E2 = «Акционный товар» ; C2*20% ; «„ )))

Logic function primer 3 Практические примеры логических функций в Excel

Теперь определим новую цену складских остатков, используя возможности игнорирования ошибок с помощью логической функции ЕСЛИОШИБКА. Для этого необходима формула:

Logic function primer 4 Практические примеры логических функций в Excel

Дополнение! Время хранения можно указывать по датам и привязать функциями времени для его автоматического определения. Это еще более автоматизирует ваши вычисления и улучшит точность и логику вычисления.

Задача №2:

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

Исходные данные для нашей таблицы будут такими:

Logic function primer 5 Практические примеры логических функций в Excel

Необходимо создать формулу, которая будет общее количество полученных балов сверять с условием по проходному балу. Также в обязательном порядке производится проверка оценки по русскому языку на соответствие и выводится результат: «Зачислен» или «Не принят».

Для написания условия необходимо в формулу ввести логическую функцию И, которая будет отслеживать правильность двух условий и функцию СУММ для суммирования итоговых оценок. Формула получится такая:

=ЕСЛИ(И(C2>=4;СУММ(C2:E2)>=$C$8);»Зачислен»;»Не принят»)

Logic function primer 6 Практические примеры логических функций в Excel

Задача №3:

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

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

=ЕСЛИ ( ИЛИ ( C13 > C4 ; D13 > D4 ; E13 > E4 ) ; » Лимит превышен » ; « В границах лимита „ )

Logic function primer 7 Практические примеры логических функций в Excel

Задача №4:

В одном из примеров мы уже производили переоценку складских запасов, а теперь стоит добавить еще и работу статистической функции СРЗНАЧ, которая будет определять товар, цена которого ниже среднего значения цены по прайсу и рекомендовать его к списанию. Применив условное форматирование можно выделить такие позиции. Для получения результата нам нужна формула:

Logic function primer 8 Практические примеры логических функций в Excel

Задача №5:

Эту задачку я предлагаю тем, кто следит за своим здоровьем и фигурой, как вы знаете, есть пословица: «Мы то, что мы едим». Это значит, что очень важно следить за своим рационом абсолютно всем кто желает жить здоровым и радоваться каждому дню. В примере я предлагаю перечень продуктов исходя из содержания в них воды, белков, жиров, углеводов и калорийной ценности определить какой продукт надо исключить из рациона, если количество жиров в нём больше «2». Логическая формула для определения нужного продукта будет такова:

=ЕСЛИ(E2>=2;»Исключить»;»Употреблять»)

Logic function primer 9 Практические примеры логических функций в Excel

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

Обращаю внимание! Все логические функции в своих примерах используют знаки сравнения «=», «<», «>», «<=», «>=» или «<>», при использовании которых получаются значения «ИСТИНА» и «ЛОЖЬ». Эти итоги позволяют создавать эффективные логические цепочки, используемые в формулах.

На этом у меня всё! Если у вас возникнет желание, что бы статья с примерами применения логических функций была более обширной, напишите об этом в комментариях, также можете написать какой пример вам бы оказался более интересен.

Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

То, как ты встречаешь поражения, определяет твой успех.
Дэвид Фегерти

Где лучше заказать решение задач по Microsoft Excel с объяснениями?

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

Дипломированные авторы фирмы «Тебе зачёт!» всегда предусматривают разнообразные виды предложенных поручений. Студенты в любой момент могут оформить заказ на решение задач по Microsoft Excel, качество которых гарантируется высокой степенью профессионализма. Решение будет выполнено максимально подробно, поэтому студенту не придётся долго вникать в готовую работу.

Ссылка на основную публикацию