Джон Форман - Много цифр. Анализ больших данных при помощи Excel

Наша цель – минимизировать количество покупок в ячейке С16. Ячейки, значение которых может меняться, находятся в пределах С2:С15. Условие же состоит в том, что значение С17 – общего количества килокалорий – должно равняться 2400. Также нужно добавить условие, что результат должен быть положительным и целым – мы ведь считаем покупки в штуках, так что придется отметить галочкой опцию «Неотрицательные значения» в меню параметров поиска решения Excel 2007 и добавить целочисленность как условие решения. Так или иначе, мы не можем купить 1,7 бутылок газировки. (Всю глубину условия целочисленности вы познаете в главе 4).

Чтобы добавить условие общего количества килокалорий, нажмите «Добавить» и задайте ячейке С17 значение 2400, как показано на рис. 1-24.

Точно так же можно добавить условие целочисленности для С2:С15, как показано на рис. 1-25.

Нажмите ОК.

В Excel 2010, 2011 и 2013 убедитесь, что метод решения установлен на «Поиск решения линейных задач симплекс-методом». Это наиболее подходящий для нашей задачи метод, так как она линейна . Под линейностью я подразумеваю, что для решения проблемы нужны только линейные комбинации значений из С2:С15 (суммы, произведения значений и констант количества килокалорий и т. д.).

Если бы в нашей модели встречались нелинейные комбинации (вроде квадратного корня из решения, логарифма или экспоненты), то мы могли бы использовать какой-нибудь другой алгоритм, предлагаемый Excel. Подробно этот вариант рассматривается в главе 4.

В Excel 2007 обозначить задачу как линейную можно, нажав на «Линейную модель» внизу окна «Параметры поиска решений». В итоге должно получиться то, что изображено на рис. 1-26.

Отлично! Самое время нажать кнопку «Выполнить». Excel найдет решение практически мгновенно. Как явствует из рис. 1-27, результат равняется 5. Ваш Excel может выбрать какие-то другие 5 позиций, но их минимальное количество останется неизменным.

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

Это значит, что все описанное в книге работает для стандартного «Поиска решения» Excel 2007 и Excel 2011 на MacOS, но в Excel 2010 и 2013 встроенный поиск решения вдруг начинает жаловаться на то, что оптимизируемая линейная модель слишком велика для него (я заранее сообщу, какие модели из рассматриваемых в книге настолько сложны).

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

Для установки OpenSolver зайдите на http://opensolver.org и загрузите оттуда архив. Распакуйте файл в папку и в любое время, когда понадобится решить «увесистую» модель, просто внесите ее в электронную таблицу и дважды кликните на файле opensolver.xlam, после чего во вкладке «Данные» появится новый раздел OpenSolver. Теперь нажмите на кнопку «Решить». Как показано на рис. 1-28, я применил OpenSolver в Excel 2013 к модели из предыдущего раздела, и он считает, что можно купить 5 кусков пиццы.

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

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