Поиск решения в excel: пример использования функции для решения задачи с неизвестными параметрами

Почему важен экономичный объем заказа (EOQ)?

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

Кроме того, вполне возможно, что EOQ — не лучший способ организовать ваш шкаф

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

Преимущества экономичного объема заказа (EOQ)

Более высокая прибыльность является основным преимуществом EOQ. Перечисленные ниже преимущества помогут вашему бизнесу сэкономить деньги и улучшить:

• Лучшее выполнение заказа: Хороший EOQ также гарантирует, что продукт будет доступен, когда он вам понадобится для заказа клиента, что позволит вам отправить его сразу же и оставить клиента довольным. В результате клиенты должны быть счастливее, и продажи могут увеличиться.
• Меньше чрезмерных заказов: Точно предвидя, что вам нужно и когда, вы можете уменьшить количество чрезмерных заказов и накопление лишних денег на складе.
• Меньше отходов: Лучшее планирование заказов должно привести к меньшему количеству устаревших запасов, особенно для предприятий, которые хранят скоропортящиеся товары, которые в конечном итоге могут испортиться.
• Снижение затрат на хранение: Когда ваш заказ соответствует вашему спросу, у вас должно быть меньше вещей для хранения. Таким образом, это может привести к снижению цен на коммунальные услуги, недвижимость, безопасность, страхование и сопутствующие расходы.
• Скидки за количество: Вы можете воспользоваться лучшими скидками на количество или оптовые заказы, которые ваши поставщики готовы предоставить, правильно спланировав и выбрав время для ваших заказов.

Проблемы с экономичным объемом заказа (EOQ)

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

• Плохие данные: Одной из основных проблем при изучении EOQ является поиск точных и надежных данных. Если в ручных или табличных системах используются неточные или устаревшие данные, расчеты могут быть неверными.
• Потенциал экономии может быть потерян из-за возможно неполных данных устаревших систем. Эту проблему может решить система управления запасами или облачная ERP.
• Расширение бизнеса: Организации с постоянными потребностями в запасах больше всего выигрывают от использования метода EOQ. Бизнес, который использует только EOQ, рискует столкнуться с нехваткой запасов из-за быстрого расширения.
• Дефицит запасов: Вы, вероятно, получите меньше заказов в первые несколько раз, когда будете использовать этот подход. Кроме того, если ваши прогнозы чрезвычайно осторожны, вы рискуете заказать слишком мало.
• Сезонные требования: Хотя это и не является непреодолимым, сезонность может сделать EOQ более сложной задачей. Это связано с тем, что существует вероятность того, что круглогодичный клиентский спрос претерпит существенные корректировки.

Надстройка поиск решения и подбор нескольких параметров Excel

​ повлияет, а там​ И нажмите ОК.​ этого:​ том, как ее​Сообщество Excel Tech Community​ проблема,​ OK. Подтвердите сброс​ выбрать метод для​ 16,5 м3 (110*0,15,​ решения. Это не​ ведь «кривая» модель​ вес всех коробок​

​Создайте формулы в ячейках,​ модели (не обязательно​ ограничений.​

1. ​ требуется найти оптимальное​ с пунктом Поиск​
2. ​ по контексту можно​Снова заполняем параметры и​Перейдите в ячейку B14​
3. ​ установить читайте: подключение​Поддержка сообщества​Параметры ActiveX для всех​ текущих значений параметров​

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

Примеры и задачи на поиск решения в Excel

1. ​ замену на новые.​ V=b1*x1*x1; V=b1*x1^0,9; V=b1*x1*x2,​ самой маленькой тары).​
2. ​ (хотя это может​ с помощью Поиска​Аналогично рассчитываем общий​

​ ограничениями (левая сторона​ формул).​ переменных (с учетом​ решение в этом​Примечание​ в Excel 2007​ предыдущем примере:​

​В появившемся диалоговом окне​ надстройки. Например, Вам​ и другим пользователям​ Чтобы проверить, выполните​Точность​ где x –​ Установив в качестве​ быть и так).​

1. ​ решения.​ объем — =СУММПРОИЗВ(B7:C7;B8:C8).​ выражения);​
2. ​Ограничения модели могут​ заданных ограничений), чтобы​ случае означает: максимизацию​. Окно Надстройки также​ ? Може была​Нажмите «Найти решение».​ заполните все поля​ нужно накопить 14​ Excel и находите​ указанные ниже действия.​

​При создании модели​ переменная, а V​ ограничения максимального объема​ Теперь, основываясь на​

Ограничение параметров при поиске решений

​ Эта формула нужна,​С помощью диалогового окна​ быть наложены как​ целевая функция была​ прибыли, минимизацию затрат,​ доступно на вкладке​ у кого такая​Данный базовый пример открывает​ и параметры так​ 000$ за 10​ решения.​откройте Excel;​ исследователь изначально имеет​ – целевая функция.​ 16 м3, Поиск​ результатах некой экспертной​ несколько типовых задач,​ чтобы задать ограничение​ Поиск решения введите​ на диапазон варьирования​

1. ​ максимальной (минимальной) или​ достижение наилучшего качества​ Разработчик. Как включить​
2. ​ ошибка ?​ Вам возможности использовать​ как указано ниже​ лет. На протяжении​
3. ​Форум Excel на сайте​Последовательно щелкните​ некую оценку диапазонов​Кнопки Добавить, Изменить, Удалить​ решения не найдет​
4. ​ оценки, в ячейки​ найти среди них​ на общий объем​ ссылки на ячейки​
5. ​ самих переменных, так​

​ была равна заданному​ и пр.​ эту вкладку читайте​https://otvet.imgsmail.ru/download/2…df7a00_800.jpg​ аналитический инструмент для​ на рисунке. Не​ 10-ти лет вы​ Answers​

exceltable.com>

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

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

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

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

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

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

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

Расположение

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

1. Нажимаете кнопку Office в верхнем левом углу экрана и переходите к Параметрам.

2. Ищете строку Надстройки и в правой части диалогового окна нажимаете кнопку Перейти. Обязательно проверьте, чтобы левее была надпись Надстройки Excel.

3. Ставите галочку напротив Поиск решения и нажимаете ОК.

4.Программа выдает предупреждение об отсутствии компонента и предлагает его установить. Соглашаетесь.

5. Дожидаетесь окончания установки.

6. Если все сделано правильно, то во вкладке Данные появится блок Анализ с кнопкой Поиск решения.

Структура

Рассмотрим подробнее основные аргументы и принцип работы функции. Основное окно содержит следующие поля:

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

1. 3. Блок добавления ограничений.
2. 4. Кнопка параметров, при нажатии которой, появляется новое окно, где можно настроить количество повторений, время выполнения, погрешность и отклонение, а также обозначить дополнительные настойки.

Использование

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

Перенесем эти сведения на рабочий лист excel.

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

1. 1. Процентная ставка.
2. 2. Период (кпер).
3. 3. Сумма платежа (плт).

Для первого года формула будет выглядеть следующим образом:

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

Воспользуемся автозаполнением и получим сумму средств после 5 лет нахождения на депозите под 4 процента годовых с ежегодным пополнением.

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

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

Как видите, изменилась только процентная ставка, хотя изменяемыми величинами были два параметра. Чтобы это исправить, в настройках необходимо поставить галочки напротив строчки Автоматическое масштабирование.

Повторяете решение с новой конфигурацией и получаете следующие данные: Как видите, чтобы достигнуть отметки в 12000$ через пять лет, необходимо найти депозит под 4,03 процента годовых и ежегодно пополнять его на сумму 2214 доллара 01 цент.

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

Ищем оптимальное решение задачи с неизвестными параметрами в Excel

«Поиск решений» — функция Excel, которую используют для оптимизации параметров: прибыли, плана продаж, схемы доставки грузов, маркетингового бюджета или рентабельности. Она помогает составить расписание сотрудников, распределить расходы в бизнес-плане или инвестиционные вложения. Знание этой функции экономит много времени и сил.

Предположим, у вас есть задача: оптимизировать расходы на производство 1 000 изделий. На это есть 30 дней и четыре работника, для которых известна производительность и оплата за изделие.

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

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

Константы — исходная информация. К ней относится удельная маржинальная прибыль, стоимость каждой перевозки, нормы расхода товарно-материальных ценностей. В нашем случае — производительность работников, их оплата и норма в 1000 изделий. Также константа отражает ограничения и условия математической модели: например, только неотрицательные или целые значения. Мы вносим константы в таблицу цифрами или с помощью элементарных формул (СУММ, СРЗНАЧ).

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

При заполнении функции «Поиск решений» важно оставить ячейки пустыми — программа сама найдет значения

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

Ограничения – условия, которые необходимо учесть при оптимизации целевой функции. К ним относятся размеры инвестирования, срок реализации проекта или объем покупательского спроса. В нашем случае — количество дней и число работников.

Теперь перейдем к самой функции.

1) Чтобы включить «Поиск решений», выполните следующие шаги:

• нажмите «Параметры Excel», а затем выберите категорию «Надстройки»;
• в поле «Управление» выберите значение «Надстройки Excel» и нажмите кнопку «Перейти»;
• в поле «Доступные надстройки» установите флажок рядом с пунктом «Поиск решения» и нажмите кнопку ОК.

2) Теперь упорядочим данные в виде таблицы, отражающей связи между ячейками. Советуем использовать цветовые обозначения: на примере красным выделена целевая функция, бежевым — ограничения, а желтым – изменяемые ячейки.

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

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

4) Заполните параметры «Поиска решений» и нажмите «Найти решение».

Совокупная стоимость 1000 изделий рассчитывается как сумма стоимостей количества изделий от каждого работника. Данная ячейка (Е13) — это целевая функция. D9:D12 — изменяемые ячейки. «Поиск решений» определяет их оптимальные значения, чтобы целевая функция достигла минимума при заданных ограничениях.

В нашем примере следующие ограничения:

• общее количество изделий 1000 штук ($D$13 = $D$3);
• число заготовок, передаваемых в работу — целое и больше нуля либо равно нулю ($D$9:$D$12 = целое, $D$9:$D$12 > = 0);
• количество дней меньше либо равно 30 ($F$9:$F$12 > окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.

Подготовка таблицы

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

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

Целевая и искомая ячейка должны быть связанны друг с другом с помощью формулы. В нашем конкретном случае, формула располагается в целевой ячейке, и имеет следующий вид: «=C10*$G$3», где $G$3 – абсолютный адрес искомой ячейки, а «C10» — общая сумма заработной платы, от которой производится расчет премии работникам предприятия.

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

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

Изготовление йогурта

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

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

В результате вычислений (с учётом дробного остатка, поскольку условие работы только с целыми числами добавлено не было), получилось, что эффективнее всего производить 1 и 3 йогурты, а второй полностью игнорировать.

Затраты на рекламу

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

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

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

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

Оптимизация игрового процесса

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

Итоговое доступное время по условиям подбора решения ограничено 4 единицами (время устанавливаем условно, не важно будут это часы, дни или месяцы). Графа «выгода» представляет собой формулу, говорящую, что будет если выделить «х» времени на сбор определённого комплекта

Задачей Excel является оптимизация максимальной (суммарной) выгоды.

В условиях имеем: требуется получить максимальную выгоду при лимите времени

Следовательно, программа определяет на каком комплекте сфокусировать внимание. Результат предсказуем: самый дорогой комплект достоин 100% временных затрат

О моделях

Этот раздел для тех, кто только знакомится с понятием Оптимизационная модель.

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

Ниже приведен небольшой ликбез по этой теме.

Надстройка Поиск решения помогает определить лучший способ сделать что-то :

• «Что-то» может включать в себя выделение денег на инвестиции, загрузку склада, доставку товара или любую другую предметную деятельность, где требуется найти оптимальное решение.
• «Лучший способ» или оптимальное решение в этом случае означает: максимизацию прибыли, минимизацию затрат, достижение наилучшего качества и пр.

Вот некоторые типичные примеры оптимизационных задач:

• Определить план производства , при котором доход от реализации произведенной продукции максимальный;
• Определить схему перевозок , при которой общие затраты на перевозку были бы минимальными;
• Найти распределение нескольких станков по разным видам работ , чтобы общие затраты на производство продукции были бы минимальными;
• Определить минимальный срок исполнения всех работ проекта (критический путь).

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

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

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

Общее определение и задачи

«ЕСЛИ» является стандартной функцией программы Microsoft Excel. В ее задачи входит проверка выполнения конкретного условия. Когда условие выполнено (истина), то в ячейку, где использована данная функция, возвращается одно значение, а если не выполнено (ложь) – другое.

Синтаксис этой функции выглядит следующим образом: «ЕСЛИ(логическое выражение; ; )» .

Пример использования «ЕСЛИ»

Теперь давайте рассмотрим конкретные примеры, где используется формула с оператором «ЕСЛИ».

1. Имеем таблицу заработной платы. Всем женщинам положена премия к 8 марту в 1000 рублей. В таблице есть колонка, где указан пол сотрудников. Таким образом, нам нужно вычислить женщин из предоставленного списка и в соответствующих строках колонки «Премия к 8 марта» вписать по «1000». В то же время, если пол не будет соответствовать женскому, значение таких строк должно соответствовать «0». Функция примет такой вид: «ЕСЛИ(B6=»жен.»; «1000»; «0»)» . То есть когда результатом проверки будет «истина» (если окажется, что строку данных занимает женщина с параметром «жен.»), то выполнится первое условие — «1000», а если «ложь» (любое другое значение, кроме «жен.»), то соответственно, последнее — «0».
2. Вписываем это выражение в самую верхнюю ячейку, где должен выводиться результат. Перед выражением ставим знак «=».

Так мы получили таблицу со столбцом, заполненным при помощи функции «ЕСЛИ».

Пример функции с несколькими условиями

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

1. Для примера возьмем все ту же таблицу с выплатами премии к 8 марта. Но на этот раз, согласно условиям, размер премии зависит от категории работника. Женщины, имеющие статус основного персонала, получают бонус по 1000 рублей, а вспомогательный персонал получает только 500 рублей. Естественно, что мужчинам этот вид выплат вообще не положен независимо от категории.
2. Первым условием является то, что если сотрудник — мужчина, то величина получаемой премии равна нулю. Если же данное значение ложно, и сотрудник не мужчина (т.е. женщина), то начинается проверка второго условия. Если женщина относится к основному персоналу, в ячейку будет выводиться значение «1000», а в обратном случае – «500». В виде формулы это будет выглядеть следующим образом: «=ЕСЛИ(B6=»муж.»;»0″; ЕСЛИ(C6=»Основной персонал»; «1000»;»500″))» .
3. Вставляем это выражение в самую верхнюю ячейку столбца «Премия к 8 марта».

Как и в прошлый раз, «протягиваем» формулу вниз.

Пример с выполнением двух условий одновременно

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

1. Например, в нашей ситуации премия к 8 марта в размере 1000 рублей выдается только женщинам, которые являются основным персоналом, а мужчины и представительницы женского пола, числящиеся вспомогательным персоналом, не получают ничего. Таким образом, чтобы значение в ячейках колонки «Премия к 8 марта» было 1000, нужно соблюдение двух условий: пол – женский, категория персонала – основной персонал. Во всех остальных случаях значение в этих ячейках будет рано нулю. Это записывается следующей формулой: «=ЕСЛИ(И(B6=»жен.»; C6=»Основной персонал»); «1000»; «0»)» . Вставляем ее в ячейку.

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

Пример использования оператора «ИЛИ»

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

1. Итак, предположим, что премия к 8 марта в 1000 рублей положена только женщинам, которые входят в число основного персонала. В этом случае, если работник — мужчина или относится к вспомогательному персоналу, то величина его премии будет равна нулю, а иначе – 1000 рублей. В виде формулы это выглядит так: «=ЕСЛИ(ИЛИ(B6=»муж.»; C6=»Вспомогательный персонал»); «0»; «1000»)» . Записываем ее в соответствующую ячейку таблицы.

«Протягиваем» результаты вниз.

Как видим, функция «ЕСЛИ» может оказаться для пользователя хорошим помощником при работе с данными в Microsoft Excel. Она позволяет отобразить результаты, соответствующие определенным условиям.

Если достаточно выполнения хотя бы одного условия (логика ИЛИ).

Как вы видели в приведенных выше примерах, подсчет ячеек, отвечающих всем указанным критериям, прост, поскольку функция СЧЕТЕСЛИМН как раз и предназначена для такой работы.

Но что если вы хотите подсчитать значения, для которых хотя бы одно из указанных условий имеет значение ИСТИНА , то есть использовать логику ИЛИ? В принципе, есть два способа сделать это — 1) сложив несколько формул СЧЕТЕСЛИ или 2) использовать комбинацию СУММ+СЧЕТЕСЛИМН с константой массива.

Способ 1. Две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН.

Подсчитаем заказы со статусами «Отменено» и «Ожидание». Чтобы сделать это, вы можете просто написать 2 обычные формулы СЧЕТЕСЛИ и затем сложить результаты:

В случае, если нужно оценить более одного параметра отбора, используйте СЧЕТЕСЛИМН.

Чтобы получить количество «отмененных» и «отложенных» заказов для клубники, используйте такой вариант:

Способ 2. СУММ+СЧЁТЕСЛИМН с константой массива.

В ситуациях, когда вам приходится оценивать множество критериев, описанный выше подход — не лучший путь, потому что ваша формула станет слишком громоздкой. Чтобы выполнить те же вычисления в более компактной форме, перечислите все свои критерии в константе массива и укажите этот массив в качестве аргумента функции СЧЕТЕСЛИМН.

Вставьте СЧЕТЕСЛИМН в функцию СУММ, вот так:

СУММ(СЧЁТЕСЛИМН(диапазон;{«условие1″;»условие2″;»условие3»;…}))

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

=СУММ(СЧЁТЕСЛИМН(E2:E11;{«Отменено»;»Ожидание»}))

Массив означает, что в начале ищем все отмененные заказы, потом ожидающие. Получается массив из двух цифр итогов. А затем функция СУММ просто их складывает.

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

Как использовать СЧЕТЕСЛИМН со знаками подстановки.

Традиционно можно применять следующие символы подстановки:

• Вопросительный знак (?) — соответствует любому отдельному символу. Используйте его для подсчета ячеек, начинающихся и или заканчивающихся строго определенными символами.
• Звездочка (*) — соответствует любой последовательности символов (в том числе и нулевой). Позволяет заменить собой часть содержимого.

Примечание. Если вы хотите сосчитать ячейки, в которых есть знак вопроса или звездочка просто как буквы, введите тильду (~) перед звездочкой или знаком вопроса в записи параметра поиска.

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

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

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

Обратите внимание, что в первом критерии мы используем знак подстановки *, поскольку рассматриваем текстовые значения (фамилии). Во втором критерии мы анализируем даты, поэтому и записываем его иначе: «»&»» (означает — не равно пустому значению)

Подготовка оптимизационной модели в MS EXCEL

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

Совет . Организуйте данные модели так, чтобы на одном листе MS EXCEL располагалась только одна модель. В противном случае, для выполнения расчетов придется постоянно сохранять и загружать настройки Поиска решения (см. ниже).

Приведем алгоритм работы с Поиском решения , который советуют сами разработчики ( ]]> www.solver.com ]]> ):

• Определите ячейки с переменными модели (decision variables);
• Создайте формулу в ячейке, которая будет рассчитывать целевую функцию вашей модели (objective function);
• Создайте формулы в ячейках, которые будут вычислять значения, сравниваемые с ограничениями (левая сторона выражения);
• С помощью диалогового окна Поиск решения введите ссылки на ячейки содержащие переменные, на целевую функцию, на формулы для ограничений и сами значения ограничений;
• Запустите Поиск решения для нахождения оптимального решения.

Проделаем все эти шаги на простом примере.