Подбор параметра в excel функция

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

1. Постройте и оформите следующую таблицу

                       

1. 1. Здесь в столбике График отмечены группы работников, во втором столбике       отмечены выходные дни у соответствующих групп (каждая группа должна иметь       два дня выходных, идущих друг за другом), в столбике Работники отмечено количество работников в каждой группе. а далее       цифра 1 означает, что данная группа в этот день работает, цифра 0 –       группа не работает. В ячейке C14 находится формула, вычисляющая общее       количество работников. В ячейке C15 – оплата труда одному человеку за неделю. В ячейке C15 – формула,       вычисляющая общую оплату работников за неделю.
   2. Введите текст в клетки. Отформатируйте информацию       в клетках и выполните обрамление (и заливку) диапазонов ячеек так, как указано       в примечаниях (пункт меню Формат).       Ширину столбца можно изменить, установив курсор мыши на разделительную линию       в строке заголовков столбцов (курсор примет вид креста со стрелками) и,       при нажатой левой кнопке мыши, переместив ее в нужную сторону. В ячейки E14:K14 вставьте формулы для вычисления       суммарного количества сотрудников, работающих в соответствующий день       недели. Для вставки Вставка –       Функция.  Используйте функцию СУММПРОИЗВ. Скопируйте введенную       формулу в остальные ячейки диапазона, используя маркер копирования в правом       нижнем углу ячейки. Для правильного копирования формулы в данном случае       необходимо, чтобы в ссылке на диапазон C6:C12       использовались абсолютные адреса, т.е. ссылка должна иметь вид $C$6:$C$12.

1. Пусть требуется решить задачу о построении графика занятости персонала парка отдыха.

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

1. Пусть требуемый уровень обслуживания определяется заданным (для каждого дня недели) числом работников (22, 17, 13, 14, 15, 18, 24 соответственно в воскресенье, понедельник,…субботу). Дополните Таблицу. В ячейки E15:K15 введите значения 22, 17, 13, 14, 15, 18, 24. Для выделения ограничений выполните обрамление диапазона E14:K15 жирной красной линией.
2. Параметры задачи.

C16 — Расходы на оплату труда.

C6:C12 — Число работников в группе (изменяемые данные – неизвестные в задаче).

C6:C12>=0 (Число работников в группе не может быть отрицательным)

C6:C12=Целое  (Число работников должно быть целым.)

E14:K14>=E15:K15 (Число ежедневно занятых работников не должно быть меньше ежедневной потребности).

Выберите Сервис – Поиск решения. Установить целевую – С16. Изменяя ячейки – С6-С12. При помощи кнопки Добавить установите три ограничения – С6:С12>=0, С6:С12=Целое, E14:K14>=E15:K15

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

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

Как пользоваться функциями подбора параметра и поиска решения в Excel

Функция поиска решения пригодится при необходимости определить неизвестную величину

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

Алгоритм решения

Итак, приступи к решению нашей задачи:

1. Для начала строим таблицу, количество строк и столбцов в которой соответствует числу продавцов и покупателей, соответственно.
2. Перейдя в любую свободную ячейку щелкаем по кнопке “Вставить функцию” (fx).
3. В открывшемся окне выбираем категорию “Математические”, в списке операторов отмечаем “СУММПРОИЗВ”, после чего щелкаем OK.
4. На экране отобразится окно, в котором нужно заполнить аргументы:
   • в поле для ввода значения напротив первого аргумента “Массив1” указываем координаты диапазона ячеек матрицы затрат (с желтым фоном). Сделать это можно, используя клавиши на клавиатуре, или просто выделив нужную область в самой таблице с помощью зажатой левой кнопки мыши.
   • в качестве значения второго аргумента “Массив2” указываем диапазон ячеек новой таблицы (либо вручную, либо выделив нужные элементы на листе).
   • по готовности жмем OK.
5. Щелкаем по ячейке, расположенной слева от самого верхнего левого элемента новой таблицы, после чего снова жмем кнопку “Вставить функцию”.
6. На этот раз нам нужна функция “СУММ”, которая также, находится в категории “Математические”.
7. Теперь нужно заполнить аргументы. В качестве значения аргумента “Число1” указываем верхнюю строку созданной для расчетов таблицы (целиком) – вручную или методом выделения на листе. Жмем кнопку OK, когда все готово.
8. В ячейке с функцией появится результат, равный нулю. Наводим указатель мыши на ее правый нижний угол, и когда появится Маркер заполнения в виде черного плюсика, зажав левую кнопку мыши тянем его до конца таблицы.
9. Это позволит скопировать формулу и получить аналогичные результаты для остальных строк.
10. Выбираем ячейку, которая находится сверху от самого верхнего левого элемента созданной таблицы. Аналогично описанным выше действиям вставляем в нее функцию “СУММ”.
11. В значении аргумента “Число1” теперь указываем (вручную или с помощью выделения на листе) все ячейки первого столбца, после чего кликаем OK.
12. С помощью Маркера заполнения выполняем копирование формулы на оставшиеся ячейки строки.
13. Переключаемся во вкладку “Данные”, где жмем по кнопке функции “Поиск решения” (группа инструментов “Анализ”).
14. Перед нами появится окно с параметрами функции:
    • в качестве значения параметра “Оптимизировать целевую функцию” указываем координаты ячейки, в которую ранее была вставлена функция “СУММПРОИЗВ”.
    • для параметра “До” выбираем вариант – “Минимум”.
    • в области для ввода значений напротив параметра “Изменяя ячейки переменных” указываем диапазон ячеек новой таблицы (без суммирующей строки и столбца).
    • нажимаем кнопку “Добавить” в блоке “В соответствии с ограничениями”.
15. Откроется небольшое окошко, в котором мы можем добавить ограничение – сумма значений первых столбцов исходной и созданной таблицы должны быть равны.
    • становимся в поле “Ссылка на ячейки”, после чего указываем нужный диапазон данных в таблице для расчетов.
    • затем выбираем знак “равно”.
    • в качестве значения для параметра “Ограничение” указываем координаты  аналогичного столбца в исходной таблице.
    • щелкаем OK по готовности.
16. Таким же способом добавляем условие по равенству сумм верхних строк таблиц.
17. Также добавляем следующие условия касательно суммы ячеек в таблице для расчетов (диапазон совпадает с тем, который мы указали для параметра “Изменяя ячейки переменных”):
    • больше или равно нулю;
    • целое число.
18. В итоге получаем следующий список условий в поле “В соответствии с ограничениями”. Проверяем, чтобы обязательно была поставлена галочка напротив опции “Сделать переменные без ограничений неотрицательными”, а также, чтобы в качестве метода решения стояло значение “Поиск решения нелинейных задач методов ОПГ”. Когда все готово, нажимаем “Найти решение”.
19. В результате будет выполнен расчет и отобразится окно с результатами поиска решения. Оцениваем их, и в случае, когда они нас устраивают, нажимаем OK.
20. Все готово, мы получили таблицу с заполненными данными и транспортную задачу можно считать успешно решенной.

Калькуляция, подбираем значение прибыли

Еще пример. Пусть дана структура цены договора: Собственные расходы, Прибыль, НДС.

Известно, что Собственные расходы составляют 150 000 руб., НДС 18%, а Целевая стоимость договора 200 000 руб. (ячейка С13 ). Единственный параметр, который можно менять, это Прибыль. Подберем такое значение Прибыли ( С8 ), при котором Стоимость договора равна Целевой, т.е. значение ячейки Расхождение ( С14 ) равно 0.

В структуре цены в ячейке С9 (Цена продукции) введена формула Собственные расходы + Прибыль ( =С7+С8 ). Стоимость договора (ячейка С11 ) вычисляется как Цена продукции + НДС (= СУММ(С9:C10) ).

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

Выделите ячейку С14 , вызовите Подбор параметра (на вкладке Данные в группе Работа с данными выберите команду Анализ «что-если?» , а затем выберите в списке пункт Подбор параметра …). В качестве целевого значения для ячейки С14 укажите 0, изменять будем ячейку С8 (Прибыль).

Теперь, о том когда этот инструмент работает. 1. Изменяемая ячейка не должна содержать формулу, только значение.2. Необходимо найти только 1 значение, изменяя 1 ячейку. Если требуется найти 1 конкретное значение (или оптимальное значение), изменяя значения в НЕСКОЛЬКИХ ячейках, то используйте Поиск решения.3. Уравнение должно иметь решение, в нашем случае уравнением является зависимость стоимости от прибыли. Если целевая стоимость была бы равна 1000, то положительной прибыли бы у нас найти не удалось, т.к. расходы больше 150 тыс. Или например, если решать уравнение x2+4=0, то очевидно, что не удастся подобрать такое х, чтобы x2+4=0

Примечание : В файле примера приведен алгоритм решения Квадратного уравнения с использованием Подбора параметра.

Функция «Подбор параметра»

Подбор параметра в Excel позволяет подобрать какой-то определенный параметр, значение которого неизвестно. Чтобы было понятней, можно привести такой пример. Допустим, есть прямоугольник со сторонами A и B. Известно, что общая площадь этой фигуры составляет 400 квадратных метров, а сторона B — 40 метров. Сторона A неизвестна и, соответственно, нужно ее найти. Для решения такой задачи необходимо заполнить рабочий лист программы теми данными, которые уже известны. Для этого нужно создать таблицу с 2 колонками и 3 строками (диапазон ячеек A1:B3).

Первый столбец будет содержать название сторон прямоугольника и букву, обозначающую его площадь (т.е. A, B и S). А во втором столбце необходимо указать известные значения:

• в соседней ячейке для стороны B (ячейка B2) написать — 40 (значение для стороны А остается пустым);
• а в соседнем поле для площади прямоугольника (поле B3) написать следующую формулу: = B1*B2 (т.е. формула для расчета площади).

Если все было сделано правильно, то в поле B3 должно быть значение 0. Затем надо выделить эту ячейку и выбрать в панели меню пункты: «Сервис — Подбор параметра». В появившемся окне нужно указать то значение, которое должно быть получено в результате, т.е. 400. В строке «Установить в ячейке» будет указано поле «B3»: менять его не нужно, так и должно быть (сюда будет выведен результат). А в строке «Изменяя значение» необходимо выбрать неизвестный параметр, т.е. поле B1. После нажатия кнопки «ОК» программа выдаст результат: сторона А — 10 метров, а в поле общей площади прямоугольника будет указано число 400.

Это была очень простая задача на уровне 3 класса, но с помощью такой функции можно решать и более сложные задачи. Например, вы решили приобрести себе автомобиль в кредит. Вы точно знаете, что сможете выплачивать ежемесячную выплату в размере 1000 $ (но не больше), а также, что банк выдает автокредит с процентной ставкой 6,5%. Суть задачи заключается в следующем: «Какова максимальная сумма машины, которую можно взять в кредит на таких условиях?». То есть теперь программа будет искать стоимость автомобиля, отталкиваясь от того, что ежемесячный платеж не должен превышать 1000 $. Такой пример является уже более сложным, а также более практичным, нежели расчет площади прямоугольника.

Подбор суммы кредита

Предположим, что нам необходимо определить максимальную сумму кредита , которую мы можем себе позволить взять в банке. Пусть нам известна сумма ежемесячного платежа в рублях (1800 руб./мес.), а также процентная ставка по кредиту (7,02%) и срок на который мы хотим взять кредит (180 мес).

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

Введем в ячейку B 6 ориентировочную сумму займа, например 100 000 руб., срок на который мы хотим взять кредит введем в ячейку B 7 , % ставку по кредиту введем в ячейку B8, а формулу =ПЛТ(B8/12;B7;B6) для расчета суммы ежемесячного платежа в ячейку B9 (см. файл примера ).

Чтобы найти сумму займа соответствующую заданным выплатам 1800 руб./мес., делаем следующее:

• на вкладке Данные в группе Работа с данными выберите команду Анализ «что-если?» , а затем выберите в списке пункт Подбор параметра …;
• в поле Установить введите ссылку на ячейку, содержащую формулу. В данном примере — это ячейка B9 ;
• введите искомый результат в поле Значение . В данном примере он равен -1800 ;
• В поле Изменяя значение ячейки введите ссылку на ячейку, значение которой нужно подобрать. В данном примере — это ячейка B6 ;
• Нажмите ОК

Что же сделал Подбор параметра ? Инструмент Подбор параметра изменял по своему внутреннему алгоритму сумму в ячейке B6 до тех пор, пока размер платежа в ячейке B9 не стал равен 1800,00 руб. Был получен результат — 200 011,83 руб. В принципе, этого результата можно было добиться, меняя сумму займа самостоятельно в ручную.

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

Иными словами, инструмент Подбор параметра позволяет сэкономить несколько минут по сравнению с ручным перебором.

Функция «Подбор параметра»

Подбор параметра в Excel позволяет подобрать какой-то определенный параметр, значение которого неизвестно. Чтобы было понятней, можно привести такой пример. Допустим, есть прямоугольник со сторонами A и B. Известно, что общая площадь этой фигуры составляет 400 квадратных метров, а сторона B — 40 метров. Сторона A неизвестна и, соответственно, нужно ее найти. Для решения такой задачи необходимо заполнить рабочий лист программы теми данными, которые уже известны. Для этого нужно создать таблицу с 2 колонками и 3 строками (диапазон ячеек A1:B3).

Первый столбец будет содержать название сторон прямоугольника и букву, обозначающую его площадь (т.е. A, B и S). А во втором столбце необходимо указать известные значения:

• в соседней ячейке для стороны B (ячейка B2) написать — 40 (значение для стороны А остается пустым);
• а в соседнем поле для площади прямоугольника (поле B3) написать следующую формулу: = B1*B2 (т.е. формула для расчета площади).

Если все было сделано правильно, то в поле B3 должно быть значение 0. Затем надо выделить эту ячейку и выбрать в панели меню пункты: «Сервис — Подбор параметра». В появившемся окне нужно указать то значение, которое должно быть получено в результате, т.е. 400. В строке «Установить в ячейке» будет указано поле «B3»: менять его не нужно, так и должно быть (сюда будет выведен результат). А в строке «Изменяя значение» необходимо выбрать неизвестный параметр, т.е. поле B1. После нажатия кнопки «ОК» программа выдаст результат: сторона А — 10 метров, а в поле общей площади прямоугольника будет указано число 400.

Это была очень простая задача на уровне 3 класса, но с помощью такой функции можно решать и более сложные задачи. Например, вы решили приобрести себе автомобиль в кредит. Вы точно знаете, что сможете выплачивать ежемесячную выплату в размере 1000 $ (но не больше), а также, что банк выдает автокредит с процентной ставкой 6,5%. Суть задачи заключается в следующем: «Какова максимальная сумма машины, которую можно взять в кредит на таких условиях?». То есть теперь программа будет искать стоимость автомобиля, отталкиваясь от того, что ежемесячный платеж не должен превышать 1000 $. Такой пример является уже более сложным, а также более практичным, нежели расчет площади прямоугольника.

Функции программы Microsoft Excel: подбор параметра

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

Скачать последнюю версию Excel

• Суть функции
• Применение функции на практике
• Решение уравнений

Суть функции

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

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

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

Применение функции на практике

Для того, чтобы понять, как работает данная функция, лучше всего объяснить её суть на практическом примере. Мы будем объяснять работу инструмента на примере программы Microsoft Excel 2010, но алгоритм действий практически идентичен и в более поздних версиях этой программы, и в версии 2007 года.

Имеем таблицу выплат заработной платы и премии работникам предприятия. Известны только премии работников. Например, премия одного из них — Николаева А. Д, составляет 6035,68 рублей. Также, известно, что премия рассчитывается путем умножения заработной платы на коэффициент 0,28. Нам предстоит найти заработную плату работников.

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

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

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

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

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

Когда все данные окна параметров заполнены, жмем на кнопку «OK».

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

Решение уравнений

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

Допустим, имеем уравнение: 15x+18x=46. Записываем его левую часть, как формулу, в одну из ячеек. Как и для любой формулы в Экселе, перед уравнением ставим знак «=». Но, при этом, вместо знака x устанавливаем адрес ячейки, куда будет выводиться результат искомого значения.

В нашем случае, формулу мы запишем в C2, а искомое значение будет выводиться в B2. Таким образом, запись в ячейке C2 будет иметь следующий вид: «=15*B2+18*B2».

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

В открывшемся окне подбора параметра, в поле «Установить в ячейке» указываем адрес, по которому мы записали уравнение (C2).

В поле «Значение» вписываем число 45, так как мы помним, что уравнение выглядит следующим образом: 15x+18x=46.

В поле «Изменяя значения ячейки» мы указываем адрес, куда будет выводиться значение x, то есть, собственно, решение уравнения (B2). После того, как мы ввели эти данные, жмем на кнопку «OK».

Как видим, программа Microsoft Excel успешно решила уравнение. Значение x будет равно 1,39 в периоде.

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

ЕСЛИОШИБКА

При работе с формулами в Excel, можно время от времени сталкиваться с различными ошибками. Так в примере ниже функция ВПР вернула ошибку #Н/Д из-за того, что в базе данных по ФИО нет искомой нами фамилии (более подробно об ошибке #Н/Д вы можете прочитать в этой статье: «Как исправить ошибку #Н/Д в Excel»)

Для обработки таких ситуаций отлично подойдет функция ЕСЛИОШИБКА. Ее синтаксис следующий:

— Значение, результат которого проверяется на ошибку.- Значение, если ошибка — В случае, если в результате работы функции получаем ошибку, то выводится не ошибка, а данное значение.

В случае с нашим примером выше, мы можем предположить, что фамилия может быть некорректной, соответственно ЕСЛИОШИБКА вернет нам предупреждение, что бы мы проверили написание фамилии.

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

Обычно при создании формулы пользователь задает значения параметров и формула (уравнение) возвращает результат. Например, имеется уравнение 2*a+3*b=x, заданы параметры а=1, b=2, требуется найти x (2*1+3*2=8). Инструмент Подбор параметра позволяет решить обратную задачу: подобрать такое значение параметра, при котором уравнение возвращает желаемый целевой результат X. Например, при a=3, требуется найти такое значение параметра b, при котором X равен 21 (ответ b=5). Подбирать параметр вручную — скучное занятие, поэтому в MS EXCEL имеется инструмент Подбор параметра .

В MS EXCEL 2007-2010 Подбор параметра находится на вкладке Данные, группа Работа с данным .

Инструмент «Поиск решения»

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

И снова рассмотрим на примере. Спланируем производственный процесс на месяц для получения максимальной прибыли. Вот наша таблица заготовка:

В таблице имеем такие поля:

1. Минимальная партия – минимальное количество товара, которое нужно произвести для обслуживания уже существующих заказов;
2. Максимальная партия – наибольшее количество товара, которое можно произвести, исходя из запасов сырья
3. Норма рабочего времени – количество человекочасов, необходимых для производства одного изделия;
4. Затраты рабочего времени – количество времени, которое будет затрачено на производство всего запланированного. Пусть у нас работает 20 работников по 8 часов 22 дня в месяце. Тогда сумма по этому полю должна составить 3520 ч.
5. Себестоимость – стоимость производства одной единицы продукции
6. Цена реализации – рыночная стоимость одной единицы продукции
7. Валовая прибыль – прибыль, которая будет получена от реализации изготовленного товара.

Для упрощения, будем считать, что спрос на товар выше производственных возможностей, и всё произведенное будет продано. Так сколько чего нам нужно произвести, чтобы получить наибольшую выгоду, а персонал трудился ровно 3520 ч? Запускаем «Поиск решения»:

1. Ищем на ленте Данные – Анализ – Поиск решения . Кликаем, откроется окно настройки;
2. В поле «Оптимизировать целевую функцию» задаем ссылку на сумму по столбцу «Валовая прибыль»;
3. В поле «До» выбираем «Максимум». В других случаях можно выбрать «минимум», или задать какое-то конкретное значение;
4. В списке «Изменяя ячейки переменных» указываем все строки столбца «Производим»
5. Далее нужно внести все оговоренные выше ограничения. Для этого жмем «Добавить» и в открывшемся окне выбираем ссылки на ячейки и параметры их ограничения:

Вносим все оговоренные ограничения, они отобразятся в списке окна настройки:

1. Суммарные затраты времени должны равняться 3520 часов;
2. Производимое количество больше или равно минимальной партии
3. Производимое количество меньше или равно максимальной партии
4. Производимое количество должно быть целым числом

1. Выбираем метод решения в соответствии с рекомендациями разработчиков внизу окна настроек. Мы выберем линейный метод. Жмем «Найти решение», по завершению поиска программа сообщает о результате.

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

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

Экспериментируя с многочисленными настройками инструмента, можно детально управлять процессом поиска. На самом деле, «Поиск решения» — очень функциональная и многогранная надстройка, познать все азы которой можно на сайте разработчика: www.solver.com.

Кстати, если Вы не нашли на ленте этот инструмент – не отчаивайтесь, его просто нужно подключить. Для этого нажмите Файл – Параметры – Надстройки . Внизу в раскрывающемся списке «Управление» выберите «Надстройки Excel» и нажмите «Перейти». В открывшемся окне поставьте галку напротив «Поиск решения» и нажмите Ок. Вот и всё, он сразу же появится ленте!

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

Экспериментируйте, а я отправляюсь писать следующий пост. До новых встреч на страницах блога officelegko.com!

ЕСЛИ

Функция ЕСЛИ является очень популярной в Excel. Она позволяет автоматически выполнять какое-либо действие, в зависимости от поставленного условия.

Функция ЕСЛИ выполняет проверку логического выражения и если выражение истинно, то  поставляется одно значение и альтернативное, если ложь. Синтаксис следующий:

— Логическое выражение — выражение, которое по итогу своего вычисления должно вырнуться значение ИСТИНА или ЛОЖЬ.- Значение, если истина — устанавливаем указанное значение, если логическое выражение вернуло ИСТИНА- Значение, если ложь — устанавливает указанное значение, если логическое выражение вернуло ЛОЖЬ.

В примере выше мы хотим определить, получили ли мы за месяц выручку больше 500 рублей или нет. В формуле  ЕСЛИ(B2>500;»Да»;»Нет») первый параметр (B2>500) проверяет, выручка за месяц больше 500 рублей или нет; второй параметр («Да») — функция вернет Да, если выручка больше 500 рублей и соответственно Нет (третий параметр), если выручка меньше.

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

Поиск оптимального решения в Excel

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

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

Функции программы Microsoft Excel: подбор параметра

Применение функции на практике

​ количество гостей, то​ гостей, которое можно​Данные​ заведение. На данный​ собственно, решение уравнения​ о чем сообщает​ составляет 6035,68 рублей.​ является Подбор параметра.​ значение B3/12 (5,5%​ рентабельность производства на​ месяцев умножив на​На данном уроке мы​ и необходимое количество​ — панель инструментов​ атрибуте «Ставка» указывается​

​ В случае, если​ в отличие от​ наш окончательный ответ​ пригласить. В следующем​выберите команду​ момент Вами набрано​ (B2). После того,​ специальное информационное окно.​ Также, известно, что​ Но, далеко не​ годовых разделено на​ уровне 20% при​ 12. Таким образом,​ научимся практически применять​

​ товаров для каждой​ «Число» — кнопка​ не годовой процент,​ требуется подбирать несколько​Диспетчера сценариев​ должен быть целым​ примере ячейка B4​Анализ «что если»​ 65 баллов, а​ как мы ввели​Подобную операцию можно проделать​

​ премия рассчитывается путем​ каждый пользователь знает​ 12 месяцев).​ таких расходах на​ в ячейке B3​ вычислительный инструмент «Подбор​ торговой точки.​ с изображением процента.​ а ежемесячный, поэтому​ значений для получения​или​ числом. Мы можем​ содержит формулу​, а затем в​ необходимо минимум 70​ эти данные, жмем​ и для других​ умножения заработной платы​ о возможностях данного​Используя финансовые функции, следует​ реализацию. В реальности​ мы получим необходимую​

​ параметр» в Excel.​Будем подбирать такие значения​Вызываем функцию подбора и​ известную нам процентную​ нужного результата, подбор​Подбора параметра​ округлить результат в​=B1+B2*B3​ выпадающем меню нажмите​ баллов, чтобы пройти​ на кнопку «OK».​ строк таблицы, если​ на коэффициент 0,28.​ инструмента. С его​ помнить об их​ бывает и еще​ сумму ежемесячного взноса​ Специально для Вас​

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

​ параметров «Эксель» не​. В следующем примере​ большую или меньшую​, которая суммирует общую​Подбор параметра​

​ отбор. К счастью,​Как видим, программа Microsoft​ известна величина премии​ Нам предстоит найти​ помощью, можно подобрать​

Решение уравнений

​ стандартах. Например, сумма​ хуже.​ для достижения цели.​ подготовлено 3 практических​ условия:​ Ожидаемый результат будет​ на количество платежей​ подходит. В этой​ видно 24 возможных​ сторону. Округлив количество​

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

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

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

​Уст​ В данной ситуации​ в периоде.​ профильной возможностью данной​ вкладке «Данные», жмем​

​ Давайте выясним, как​ ПЛТ мы использовали​ взноса, которую Вы​В текущем году продукт​На протяжении 10-ти лет​ в торговые точки​ и составлять 10​ 2007 нужные данные.​Одна из наиболее востребованных​Возможности программы Excel таковы,​ остановимся на 18-ти​ умножается на их​ановить в ячейке​ можно воспользоваться​Изучив инструмент Подбор параметра,​ функции, её можно​

​ на кнопку «Анализ​

lumpics.ru>