Youtubezilla.ru

Мастер бытовой техники
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

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

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

Эти инструменты 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.

Читайте так же:
Как удалить пароли сохраненные в Яндекс Браузере

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

Чуть-чуть истории и теории.

Вы задумывались когда-нибудь — откуда и зачем в головах людей, живших в XVI…XVII веках, родились понятия дифференциалов, производных, интегралов? Объяснение, в общем-то, достаточно простое и понятное – эти ученые искали аналитические пути решения прикладных практических задач. И успешно находили.

Мне сегодня видится приблизительно такая «лестница» с качественными «ступенями инструментов» математики для решения практических и научных задач, которую изобрело человечество:

1. Арифметика — сложение, вычитание, умножение, деление.

2. Алгебра – применение элементарных функций (степенной, логарифмической, тригонометрической, …) и алгебраических уравнений функции одной переменной.

3. Гауссовские системы линейных уравнений.

4. Численные методы решения трансцендентных уравнений.

5. Численные методы решения систем трансцендентных уравнений функций нескольких переменных.

6. Дифференцирование и интегрирование функций одной переменной.

7. Дифференцирование и интегрирование функций нескольких переменных.

8. Системы дифференциальных и интегральных уравнений.

9. Масса разнообразных новых и старых специальных методик и подходов мне не известных и известных, но, безусловно, существующих и работающих.

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

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

Каждый из методов имеет свои достоинства и недостатки — они подробно описаны в литературе, и углубляться в них мы не будем. Скажу только, что из вышеперечисленных методов мне на практике довелось использовать все. При решении различных (в основном геометрических и теплотехнических) задач по разным причинам было удобно использовать то один, то другой подход. Метод Ньютона хорош своей быстрой сходимостью и простотой формулы. Комбинированный метод секущих-хорд на основе итерационной формулы Ньютона не требует нахождения производных, быстро «сходится», и главное – не требует анализа функции на сходимость. Метод половинного деления медленно сходится, но не требует никакого предварительного анализа функции.

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

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

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

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

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

Читайте так же:
Устранить неполадки Mfc110u.dll (бесплатная загрузка)

Встаньте на целевую ячейку А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.

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

Особенности применения функции: пошаговый обзор с объяснением на примере карточки товаров

Чтобы рассказать подробнее о том, как работает «Подбор параметра», воспользуемся программой Microsoft Excel 2016 года. Если у вас установлена более поздняя или ранняя версия приложения, в таком случае могут незначительно отличаться лишь некоторые этапы, при этом принцип действия остается таким же.

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

Подбор параметра в Excel. Функция "Подбор параметра"

  1. Когда появилось всплывающее окошко, в поле «Установить в ячейке» прописываем нужный адрес ячейки. В нашем случае это сумма скидки. Чтобы долго не прописывать его и периодически не менять раскладку клавиатуры, делаем клик по нужной ячейке. Значение автоматически отобразится в нужном поле. Напротив поля «Значение» указываем сумму скидки (300 рублей).

Важно! Окно «Подбор параметра» не работает без установленного значения.

Подбор параметра в Excel. Функция "Подбор параметра"

  1. В поле «Изменение значения ячейки» прописывается тот адрес, в котором планируем выводить первоначальное значение цены на товар. Подчеркиваем, что это окошко должно непосредственно участвовать в формуле расчетов. После убеждаемся, что все значения проставлены верно, нажимаем кнопку «ОК». Для получения первоначального числа старайтесь использовать ячейку, которая состоит в таблице, так легче будет составлять формулу.
Читайте так же:
Как на Авито продлить объявление бесплатно?

Подбор параметра в Excel. Функция "Подбор параметра"

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

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

Пример

Аргументом функции ПЛТ « ставка » выступает процентная ставка в 10% годовых (ячейка B4). Поэтому необходимо разделить на 12 месяцев , чтобы получить ежемесячную процентную ставку .

Срок выплачивания ипотеки составляет 60 месяцев (ячейка B5) — это аргумент « кпер » .

А аргумент « пс » соотвествует общей сумме ипотеки, то есть $150,000 (ячейка B3).

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

В итоге получаем, что нам необходимо каждый месяц на протяжении 5 лет (60 месяцев) выплачивать за ипотеку $3,187.06 .

Но представьте, что такую сумму мы не в состоянии платить. Наш максимум составляет $1,500 .

Возникает небезынтересный вопрос : «А какое же значение таблицы необходимо уменьшить и насколько, чтобы ежемесячная выплата ипотеки не превышала $1,500?»

Конечно, можно, как говорится, воспользоваться методом «тыка» , но зачем это делать, когда Excel предлагает нам пустить в оборот инструмент по подбору параметра ?

Срезы

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

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

    Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].

Вставка среза в Excel

  1. В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.
    Вставка срезов

Форматирование срезов

  1. Выделить срез.
  2. На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

Форматирование срезов

  1. Выбрать кнопку с подходящим стилем форматирования.

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

Пример использования функции ВЫБОР

В качестве аргументов функции ВЫБОР могут использоваться как значения, так и ссылки на интервал.
Разберем по отдельности особенности работы формулы выбора для каждого из типов аргументов.

Форма значений

Предположим, что нам необходимо получить день недели по порядковому номеру дня, соответственно для номера индекса от 1 до 7 мы сопоставляем значения от понедельника до воскресенья:

Функция ВЫБОР. Форма значений.

Ссылочная форма

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

Функция ВЫБОР. Ссылочная форма.

В данном случае формула ВЫБОР(3;B2;B3;B4;B5) вернет значение третьего элемента списка, т.е. B4, и в качестве результата мы получим сумму диапазона B2:B4.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector