Домашняя страница Undo Do Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Статьи об Excel

МЕНЮ САЙТА
  • 1
  • 2
  • 3

КАТЕГОРИИ РАЗДЕЛА

ОПРОСЫ
Какой версией Excel Вы пользуетесь?
Всего ответов: 56880
Главная » Статьи » Эффективная работа в Excel » Инструменты Excel

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

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

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

Если Вы раньше не использовали Поиск решения, то Вам потребуется установить соответствующую надстройку.
 
Сделать это можно так:
для версий старше Excel 2007 через команду меню  Сервис --> Надстройки;
начиная с Excel 2007 через диалоговое окно Параметры Excel
 
 
Начиная с версии Excel 2007 кнопка для запуска Поиска решения появится на вкладке Данные.
 
 
В версиях до Excel 2007 аналогичная команда появится в меню Сервис
 
 
Разберём порядок работы Поиска решения на простом примере.
 

Пример 1. Распределение премии

 

Предположим, что Вы начальник производственного отдела и Вам предстоит по-честному распределить премию в сумме 100 000 руб. между сотрудниками отдела пропорционально их должностным окладам. Другими словами Вам требуется подобрать коэффициент пропорциональности для вычисления размера премии по окладу.
 
 
Первым делом создаём таблицу с исходными данными и формулами, с помощью которых должен быть получен результат. В нашем случае результат - это суммарная величина премии. Очень важно, чтобы целевая ячейка (С8) посредством формул была связана с искомой изменяемой ячейкой (Е2). В примере они связаны через промежуточные формулы, вычисляющие размер премии для каждого сотрудника (С2:С7).
 
 
 
Теперь запускаем Поиск решения и в открывшемся диалоговом окне устанавливаем необходимые параметры. Внешний вид диалоговых окон в разных версиях несколько различается:
 
 
Начиная с Excel 2010
 
 
До Excel 2010
 
  1. Целевая ячейка, в которой должен получиться желаемый результат. Целевая ячейка может быть только одна
  2. Варианты оптимизации:  максимальное возможное значение, минимальное возможное значение или конкретное значение. Если требуется получить конкретное значение, то его следует указать в поле ввода
  3. Изменяемых ячеек может быть несколько: отдельные ячейки или диапазоны. Собственно, именно в них Excel перебирает варианты с тем, чтобы получить в целевой ячейке заданное значение
  4. Ограничения задаются с помощью кнопки Добавить. Задание ограничений, пожалуй, не менее важный и сложный этап, чем построение формул. Именно ограничения обеспечивают получение правильного результата. Ограничения можно задавать как для отдельных ячеек, так и для диапазонов. Помимо всем понятных знаков =, >=, <=, при задании ограничений можно использовать варианты цел (целое), бин (бинарное или двоичное, т.е. 0 или 1), раз (все разные - только начиная с версии Excel 2010).

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


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

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

 

Разберём еще одну задачу оптимизации (получение максимальной прибыли)

 

Пример 2. Мебельное производство (максимизация прибыли)

 

Фирма производит две модели А и В сборных книжных полок.
Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки.
Для каждого изделия модели А требуется 3 м² досок, а для изделия модели В - 4 м². Фирма может получить от своих поставщиков до 1700 м² досок в неделю.
Для каждого изделия модели А требуется 12 мин машинного времени, а для изделия модели В - 30 мин. в неделю можно использовать 160 ч машинного времени.
Сколько изделий каждой модели следует выпускать фирме в неделю для достижения максимальной прибыли, если каждое изделие модели А приносит 60 руб. прибыли, а каждое изделие модели В - 120 руб. прибыли?
 

 

Порядок действий нам уже известен.
Сначала создаем таблицы с исходными данными и формулами. Расположение ячеек на листе может быть абсолютно произвольным, таким как удобно автору. Например, как на рисунке
 
 
 
Запускаем Поиск решения и в диалоговом окне устанавливаем необходимые параметры
 
 
  1. Целевая ячейка B12 содержит формулу для расчёта прибыли
  2. Параметр оптимизации - максимум
  3. Изменяемые ячейки B9:C9
  4. Ограничения: найденные значения должны быть целыми, неотрицательными; общее количество машинного времени не должно превышать 160 ч (ссылка на ячейку D16); общее количество сырья не должно превышать 1700 м² (ссылка на ячейку D15). Здесь вместо ссылок на ячейки D15 и D16 можно было указать числа, но при использовании ссылок какие-либо изменения ограничений можно производить прямо в таблице
  5. Нажимаем кнопку Найти решение (Выполнить) и после подтверждения получаем результат
 
 
 
Но даже если Вы правильно создали формулы и задали ограничения, результат может оказаться неожиданным. Например, при решении данной задачи Вы можете увидеть такой результат:
 
 
 
И это несмотря на то, что было задано ограничение целое. В таких случаях можно попробовать настроить параметры Поиска решения. Для этого в окне Поиск решения нажимаем кнопку Параметры и попадаем в одноимённое диалоговое окно
 

 

Первый из выделенных параметров отвечает за точность вычислений. Уменьшая его, можно добиться более точного результата, в нашем случае - целых значений. Второй из выделенных параметров (доступен, начиная с версии Excel 2010) даёт ответ на вопрос: как вообще могли получиться дробные результаты при ограничении целое? Оказывается Поиск решения это ограничение просто проигнорировал в соответствии с установленным флажком.
 

Пример 3. Транспортная задача (минимизация затрат)

 

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

Дано: запасы песка на карьерах; потребности в песке стройплощадок; затраты на транспортировку между каждой парой «поставщик-потребитель».

 

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

 

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

 

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

 

Запускаем Поиск решения и устанавливаем необходимые параметры (см. рисунок)

 

Нажимаем Найти решение (Выполнить) и получаем результат, изображенный ниже

 

Иногда транспортные задачи усложняются с помощью дополнительных ограничений. Например, по каким-то причинам невозможно возить песок с карьера 2 на стройплощадку №3. Добавляем ещё одно ограничение $D$13=0. И после запуска Поиска решения получаем другой результат

 

И последнее, на что следует обратить внимание, это выбор метода решения. Если задача достаточно сложная, то для достижения результата может потребоваться подобрать метод решения
 
Начиная с Excel 2010
 
 
До Excel 2010
 
1           2      3
 
В заключение предлагаю попробовать свои силы в применении Поиска решения и решить с его помощью старинную задачу:
Крестьянин на базаре за 100 рублей купил 100 голов скота. Бык стоит 10 рублей, корова 5 рублей, телёнок 50 копеек. Сколько быков, коров и телят купил крестьянин?
 
 
Категория: Инструменты Excel | Добавил: Pelena (08.09.2012)
Просмотров: 282182 | Комментарии: 17 | Теги: задачи оптимизации, транспортная задача, максимизация прибыли, минимизация затрат, поиск решения | Рейтинг: 4.8/12


Всего комментариев: 14
Спам-сообщение скрыто. Показать
0   Спам
1    Алексей   (11.01.2013 19:26) [ Материал]
   Спасибо!

0   Спам
2    Екатерина   (26.05.2013 20:59) [ Материал]
   О, спасибо!

Спам-сообщение скрыто. Показать
0   Спам
3    Юлия   (03.06.2013 19:13) [ Материал]
   Расчитываю задачу. У меня в EXCEL , через поиск решения. В параметрах нет Линейной и неотрицательного значения! Как установить, либо найти?

Спам-сообщение скрыто. Показать
0   Спам
4    Pelena   (03.06.2013 23:08) [ Материал]
   Не поняла, какой версии Excel. До 2007 неотрицательные надо задавать явно (>=0)

Спам-сообщение скрыто. Показать
0   Спам
5    Pelena   (18.02.2014 10:48) [ Материал]
   С вопросами по Excel обращайтесь на форум

0  
6    Ojegrant   (18.06.2014 11:49) [ Материал]
   Спасибо, конечно, но у вас тут такая куча ошибок в примере в указании параметров для поиска решения. Если сделать напрямую как в примере - не сработает. Пришлось потратить кучу лишнего времени чтобы разобраться в ваших ошибках и все заработало.

0  
7    Pelena   (18.06.2014 12:42) [ Материал]
   Конкретней можно? О каком примере речь? В чём ошибки?

Спам-сообщение скрыто. Показать
0   Спам
8    Liam   (22.08.2014 19:32) [ Материал]
   Спасибо! hands

Спам-сообщение скрыто. Показать
0   Спам
9    Денис Ф   (26.09.2014 17:48) [ Материал]
   1.9.90 !))))
спасибо за урок!!!))

0  
10    Pelena   (26.09.2014 20:44) [ Материал]
   Верно))

Спам-сообщение скрыто. Показать
0   Спам
11    Молодкина   (28.07.2015 12:41) [ Материал]
   Добрый день. Подскажите пожалуйста , как можно автоматический обновлять прайс листы в EXCEL? Или в какой то другой программе? пример: загружаю прайс лист, устанавливаю какие то формулы и программа выдает мне уже к примеру готовые прайс лист с другими ценами)
УМООООЛЯЯЯЮ

0  
12    Pelena   (28.07.2015 14:50) [ Материал]
   Ваш вопрос не имеет отношения к этой статье. Создайте тему на форуме, приложите файл с примером, тогда Вам помогут

Спам-сообщение скрыто. Показать
0   Спам
13    makao   (29.12.2015 17:31) [ Материал]
   Спасибо за урок!

У меня получилось 7 5 10

Спам-сообщение скрыто. Показать
0   Спам
14    makao   (29.12.2015 17:32) [ Материал]
   А нет, ошибся в формуле 1 9 90

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!