Несмотря на то, что вы выбрали две области данных одинакового размера и применили к ним функцию SUMPRODUCT/СУММПРОИЗВ, формула не видит их равенства, потому что одна область вертикальная, а другая горизонтальная.
К счастью, в Excel есть функция для расположения массивов в нужном направлении. Она называется TRANSPOSE/ТРАНСП. Нужно написать такую формулу:
=SUMPRODUCT(B2:B15,TRANSPOSE('FeeSchedule'!B2:O2)) /
=СУММПРОИЗВ(B2:B15,ТРАНСП('Комиссия'!B2:O2))
Но нет! Все еще возникает ошибка.
Причина, по которой это происходит, такова: каждая функция Excel по умолчанию возвращает результат в виде единственного значения. Даже если TRANSPOSE/ТРАНСП вернет первое значение в транспонированный массив. Если вы хотите видеть в результате целый массив, нужно включить TRANSPOSE/ТРАНСП в «формулу массива». Формулы массива действительно возвращают результат в виде массива, а не единственного значения.
Вам не нужно писать SUMPRODUCT/СУММПРОИЗВ как-то иначе, чтобы все получилось. Все, что следует сделать это вместо клавиши «Enter» после ввода формулы нажать «Ctrl+Shift+Enter». В MacOS нужное сочетание «Command+Return».
Победа! Как видно на рис. 1-20, результатом вычислений является 57,60 долларов. Но я бы округлил его до 50 неужели Тренеру нужно столько носков?
Решение задач с помощью «Поиска решения»
Многие техники, которым вы научитесь в этой книге, могут быть сведены к моделям оптимизации. Проблема оптимизации из разряда тех, для которых нужно подобрать лучшее решение (наилучший способ инвестирования, минимизировать траты вашей компании и т. д.). Применительно к моделям оптимизации приходится часто пользоваться словами «минимизировать» и «максимизировать».
В рамках науки о данных многие приемы, чего бы они ни касались искусственного интеллекта, извлечения и анализа данных или прогнозирования на деле состоят из некоторой подготовки данных и стадии подбора модели, которая на самом деле и является моделью оптимизации. Так что, по моему мнению, имеет смысл сначала научиться оптимизации. Но просто взять и выучить все об оптимизации невозможно. Мы проведем углубленное исследование оптимизации в главе 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/СУММПРОИЗВ: