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

В Excel проблемы оптимизации решаются с помощью встроенного модуля под названием «Поиск решения».

• В Windows «Поиск решения» можно подключить, пройдя во вкладку «Файл» (в Excel 2007 это верхняя левая кнопка Windows) → Параметры → Надстройки. Нажав «Доступные надстройки» в выпадающем меню, отметьте «Поиск решения».

• В MacOS «Поиск решения» добавляется из меню «Инструменты», в котором следует выбрать «Надстройки», а затем Solver.xlam.

Кнопка «Поиск решения» появится в разделе «Анализ» вкладки «Данные» в любой версии Excel.

Отлично! Включив «Поиск решения», можете приступать к оптимизации. Представьте, что вам велели потреблять 2400 ккал в день. Какое минимальное количество покупок нужно сделать в вашем киоске, чтобы набрать дневную норму? Очевидно, самый простой выход – купить 10 сэндвич-мороженых по 240 ккал в каждом, но можно ли набрать норму, совершив меньше 10 покупок?

«Поиск решения» знает ответ!

Для начала сделайте копию листа «Calories»/«Калории», назовите его «Калории – решение» и удалите из копии все, кроме таблицы калорийности. Чтобы сделать копию листа в Excel, просто кликните правой клавишей мышки на заголовке листа, который хотите скопировать (внизу), и выберите в появившемся меню «Переместить» или «Копировать». Так вы получите новый лист, как на рис. 1-21.

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

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

В ячейке С16 просуммируйте количество покупок таким образом:

>=SUM(C2:C15) /

>=СУММ(C2:C15)

Под данной формулой можно подсчитать количество килокалорий в этих покупках (которая должна, по вашему разумению, равняться 2400), используя формулу >SUMPRODUCT/СУММПРОИЗВ:

>=SUMPRODUCT(B2:B15,C2:C15) /

>=СУММПРОИЗВ(B2:B15,C2:C15)

Таким образом получается лист, изображенный на рис. 1-22.

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