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

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

В рамках науки о данных многие приемы, чего бы они ни касались – искусственного интеллекта, извлечения и анализа данных или прогнозирования – на деле состоят из некоторой подготовки данных и стадии подбора модели, которая на самом деле и является моделью оптимизации. Так что, по моему мнению, имеет смысл сначала научиться оптимизации. Но просто взять и выучить все об оптимизации невозможно. Мы проведем углубленное исследование оптимизации в главе 4, после того, как вы немного «поиграете» с проблемами машинного самообучения в главах 2 и 3. Но чтобы заполнить пробелы, неплохо было бы немного попрактиковаться в оптимизации уже сейчас – для пробы.

В 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.

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

Заметка

Окно поиска решений в Excel 2011, показанное на рис. 1-23, выглядит примерно так же, как и в Excel 2010 и 2013. В Excel 2007 интерфейс немного другой, но единственное существенное отличие заключается в отсутствии окна выбора алгоритма. Зато можно выбрать «Линейную модель» в параметрах поиска решений. Обо всех этих элементах мы поговорим позже.

Основные элементы, которые нужны «Поиску решения» для решения проблемы, как показано на рис. 1-23, – это ячейка для результата, направление оптимизации (минимализация или максимализация), несколько условных переменных, которые «Поиск решения» может изменять, и какие-либо условия.

Наша цель – минимизировать количество покупок в ячейке С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 позиций, но их минимальное количество останется неизменным.