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

Решение задач с помощью «Поиска решения»

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

В рамках науки о данных многие приемы, чего бы они ни касались – искусственного интеллекта, извлечения и анализа данных или прогнозирования – на деле состоят из некоторой подготовки данных и стадии подбора модели, которая на самом деле и является моделью оптимизации. Так что, по моему мнению, имеет смысл сначала научиться оптимизации. Но просто взять и выучить все об оптимизации невозможно. Мы проведем углубленное исследование оптимизации в главе 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 позиций, но их минимальное количество останется неизменным.

OpenSolver: хотелось бы обойтись без него, но это невозможно

Вообще эта книга писалась для работы исключительно со встроенным поиском решений 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/ВПР и другими функциями поиска ячейки, сортировкой и фильтрацией данных, создавать сводные таблицы и диаграммы, работать с формулами массива и поняли, как и когда прибегать к помощи «Поиска решения».

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

1. Разговор с клиентами обо всякой чепухе (о спорте, отпуске, барбекю… конечно, я не имею в виду, что жареное мясо – полная ерунда).

2. Сведение данных в Excel.

Вы можете не знать всего о школьном футболе (я определенно не знаю), но если вы усвоите эту главу, смело отправляйте второй пункт в нокаут.

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

2. Кластерный анализ, часть I: использование метода k-средних для сегментирования вашей клиентской базы

Я работаю в индустрии почтового маркетинга для сайта под названием MailChimp.com. Мы помогаем клиентам делать новостную рассылку для своей рекламной аудитории. Каждый раз, когда кто-нибудь называет нашу работу «почтовым вбросом», я чувствую на сердце неприятный холод.

Почему? Да потому что адреса электронной почты – больше не черные ящики, которые вы забрасываете сообщениями, будто гранатами. Нет, в почтовом маркетинге (как и в других формах онлайн-контакта, включая твиты, посты в Facebook и кампании на Pinterest) бизнес получает сведения о том, как аудитория вступает в контакт на индивидуальном уровне, с помощью отслеживания кликов, онлайн-заказов, распространения статусов в социальных сетях и т. д. Эти данные – не просто помехи. Они характеризуют вашу аудиторию. Но для непосвященного эти операции сродни премудростям греческого языка. Или эсперанто.

Как вы собираете данные об операциях с вашими клиентами (пользователями, подписчиками и т. д.) и используете ли их данные, чтобы лучше понять свою аудиторию? Когда вы имеете дело с множеством людей, трудно изучить каждого клиента в отдельности, особенно если все они по-разному связываются с вами. Даже если бы теоретически вы могли достучаться до каждого лично, на практике это вряд ли осуществимо.

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

Кластерный анализ – это сбор различных объектов и разделение их на группы себе подобных. Работая с этими группами – определяя, что у их членов общего, а что отличает их друг от друга – вы можете многое узнать о беспорядочном имеющемся у вас массиве данных. Это знание поможет вам принимать оптимальные решения, причем на более детальном уровне, нежели раньше.

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

Одно из моих любимых применений кластеризации – это кластеризация изображений: сваливание в кучу файлов изображений, которые «выглядят одинаково» для компьютера. К примеру, в сервисах размещения изображений типа Flickr пользователи производят кучу контента и простая навигация становится невозможной из-за большого количества фотографий. Но, используя кластерные техники, вы можете объединять похожие изображения, позволяя пользователю ориентироваться между этими группами еще до подробной сортировки.

Контролируемое или неконтролируемое машинное обучение?

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

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

Если вместо этого я скажу: «Вот что я знаю о своих клиентах и вот как определить, разные они или одинаковые. Расскажи-ка что-нибудь интересненькое», – то это отсутствие контроля.

В данной главе рассматривается самый простой способ кластеризации под названием метод k-средних, который ведет свою историю из 50-х годов и с тех пор стал дежурным в открытии знаний из баз данных (ОЗБД) во всех отраслях и правительственных структурах.

Метод k-средних – не самый математически точный из всех методов. Он создан, в первую очередь, из соображений практичности и здравого смысла – как афроамериканская кухня. У нее нет такой шикарной родословной, как у французской, но и она зачастую угождает нашим гастрономическим капризам. Кластерный анализ с помощью k-средних, как вы вскоре убедитесь, – это отчасти математика, а отчасти – экскурс в историю (о прошлых событиях компании, если это сравнение относится к методам обучения менеджменту). Его несомненным преимуществом является интуитивная простота.

Посмотрим, как работает этот метод, на простом примере.

Девочки танцуют с девочками, парни чешут в затылке

Цель кластеризации методом k-средних – выбрать несколько точек в пространстве и превратить их в k группы (где k – любое выбранное вами число). Каждая группа определена точкой в центре вроде флага, воткнутого в Луну и сигнализирующего: «Эй, вот центр моей группы! Присоединяйтесь, если к этому флагу вы ближе, чем к остальным!» Этот центр группы (с официальным названием кластерный центроид) – то самое среднее из названия метода k-средних.

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

Герои нашего примера – ученики средней школы Макакне, пришедшие на танцевальный вечер под романтическим названием «Бал на дне морском», – рассеяны по актовому залу, как показано на рис. 2–1. Я даже подрисовал в Photoshop паркет, чтобы было легче представить ситуацию.

А вот примеры песен, под которые эти юные лидеры свободного мира будут неуклюже танцевать (если вдруг вам захочется музыкального сопровождения, к примеру, на Spotify):

• Styx: Come Sail Away

• Everything But the Girl: Missing

• Ace of Base: All that She Wants

• Soft Cell: Tainted Love

• Montell Jordan: This is How We Do It

• Eiffel 65: Blue

Теперь кластеризация по k-средним зависит от количества кластеров, на которое вы желаете поделить присутствующих. Давайте остановимся для начала на трех кластерах (далее в этой главе мы рассмотрим вопрос выбора k). Алгоритм размещает три флажка на полу актового зала некоторым допустимым образом, как показано на рис. 2–2, где вы видите 3 начальных флажка, распределенных по полу и отмеченных черными кружками.

В кластеризации методом k-средних танцоры привязаны к ближайшему для них кластерному центру, так что между двумя любыми центрами на полу можно нарисовать демаркационную линию. Таким образом, если танцор находится на одной стороне линии, он принадлежит к одной группе, если по другую сторону – то уже к другой (как на рис. 2–3).

Используя эти демаркационные линии, разделим танцоров на группы и раскрасим соответствующим образом, как на рис. 2–4. Эта диаграмма, разделяющая пространство на многоугольники, определенные близостью к тому или иному кластерному центру, называется диаграммой Вороного.

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

Алгоритм кластеризации методом k-средних перемещает кластерные центры по полу, пока не достигнет наилучшего результата.

Как определить «наилучший результат»? Каждый присутствующий отстоит на сколько-то от своего кластерного центра. Чем меньше среднее расстояние от участников до центра их группы, тем лучше результат.

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

Так что если диаграмма на рис. 2–4 выглядит довольно бледно, «Поиск решения» может внезапно расположить центры как на рис. 2–5. Таким образом среднее расстояние между каждым танцором и его центром немного уменьшится.

Очевидно, что рано или поздно «Поиск решения» поймет, что центры должны быть размещены в середине каждой группы танцоров, как показано на рис. 2–6.

Отлично! Вот так выглядит идеальная кластеризация. Кластерные центры находятся в центре каждой группы танцоров, минимизируя среднее расстояние между танцором и ближайшим центром. Теперь, когда кластеризация закончена, время перейти к развлекательной части, а именно: попытке понять, что же эти кластеры означают.

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

Таким образом, метод k-средних позволил вам разделить множество посетителей танцев на группы и скоррелировать характеристики каждого посетителя с принадлежностью к определенному кластеру, чтобы понять причину разделения.

Теперь вы наверняка говорите себе: «Да ладно, что за глупости. Я уже до начала знал ответ». Вы правы. В этом примере – да. Я специально привел такой «игрушечный» пример, будучи уверенным, что вы можете решить его, просто взглянув на точки. Действие происходит в двумерном пространстве, в котором кластеризация производится элементарно с помощью глаз.

Но что, если вы держите магазин, реализующий тысячи товаров? Некоторые покупатели совершили одну или две покупки за последние два года. Другие – десятки. И каждый покупал что-то свое.

Как вы кластеризируете их на таком «танцполе»? Начнем с того, что этот танцпол не двумерный, и даже не трехмерный. Это тысячемерное пространство реализации товара, в котором покупатель приобрел или не приобрел товар в каждом измерении. Видите, как быстро проблема кластеризации начинает выходить за пределы способностей «глазного яблока первого разряда», как любят говорить мои друзья-военные.

Реальная жизнь: кластеризация методом k-средних в электронном маркетинге

Давайте перейдем к более предметному случаю. Я занимаюсь электронным маркетингом, поэтому приведу пример из жизни MailChimp.com, в которой работаю. Этот же самый пример будет работать и на данных о розничной торговле, преобразовании рекламного трафика, социальных сетей и т. д. Он взаимодействует практически с любым типом данных, связанных с донесением до клиентов рекламного материала, после чего они безоговорочно выбирают вас.

Оптовая Винная Империя Джоуи Бэг О'Донатса

Представьте на минуту, что вы живете в Нью-Джерси, где держите Оптовую Винную Империю Джоуи Бэг О'Донатса. Это импортно-экспортный бизнес, целью которого является доставка огромного количества вина из-за границы и продажи его определенным винным магазинам по всей стране. Этот бизнес работает таким образом, что Джоуи путешествует по всему миру в поисках невероятных сделок с большим количеством вина. Он отправляет его к себе в Джерси, а пристроить присланное в магазины и получить прибыль – ваша забота.

Вы находите покупателей разными способами: страница на Facebook, аккаунт в Twitter, порой даже прямая рассылка – ведь электронные письма «раскручивают» большинство видов бизнеса. В прошлом году вы отправляли одно письмо в месяц. Обычно в каждом письме описываются две или три сделки, скажем, одна с шампанским, а другая с мальбеком. Некоторые сделки просто удивительны – скидка составляет 80 % или больше. В итоге вы заключили около 32 сделок за год и все они прошли более-менее гладко.

Но то, что дела идут просто хорошо, не значит, что они не могут идти лучше. Было бы нелишне чуть глубже понять мотивы своих покупателей. Конечно, взглянув на конкретный заказ, вы видите, что некий Адамс купил сколько-то игристого в июле с 50 %-ной скидкой, но не можете определить, что подвигло его на покупку. Понравился ли ему минимальный объем заказа в одну коробку с шестью бутылками или цена, которая еще не поднялась до своего максимума?

Было бы неплохо иметь возможность разбить список клиентов на группы по интересам. Тогда вы бы могли отредактировать письма к каждой группе отдельно и, возможно, раскрутили бы бизнес еще больше. Любая подходящая данной группе сделка могла стать темой письма и идти в первом абзаце текста. Такой тип целевой рассылки может вызвать форменный взрыв продаж!

Но как разделить список рассылки? С чего начать?

Есть возможность дать компьютеру сделать работу за вас. Используя кластеризацию методом k-средних, вы можете найти наилучший вариант разбиения на группы, а затем попытаться понять, почему же он лучший.