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

Эти задачи сродни запросам «aggregate» или «group by», используемым в традиционных базах данных SQL. Но наши данные – еще не база. Это – электронная таблица. И здесь нам на помощь приходят сводные таблицы.

После фильтрации вы начинаете с выделения данных, которыми хотите манипулировать. В нашем случае – данных о продажах в области А1:F20. Во вкладке «Вставить» (вкладка «Данные» в MacOS) выберите «Сводная таблица» и создайте ее на новом листе. Несмотря на то, что новые версии Excel позволяют вставлять сводную таблицу в существующий лист, ее, как правило, помещают на отдельном, если нет явной причины сделать иначе.

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

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

Допустим, теперь вам нужно знать объем выручки за каждый товар. Для этого перетащите ссылку Item/«Товар» в конструкторе сводных таблиц в поле строк, а ссылку Price/«Цена» – в поле данных. Это значит, что вы будете работать с доходом, сгруппированным по названию товара.

Изначально сводные таблицы создавались для простого подсчета количества записей о ценах внутри группы. Например, на рис. 1-17 есть 20 строк о пиве.

Нужно изменить подсчет количества записей на сумму значений. Чтобы это сделать в Windows, используйте выпадающее меню из ссылки «Цена» в поле данных и выберите в нем «Параметры поля данных». В MacOS нужно нажать маленькую кнопочку «i». В этом меню среди множества других опций можно выбрать сумму.

А что, если вам захотелось разбить эти суммы по категориям? Для этого в конструкторе перетащите ссылку «Категории» в поле столбцов. В итоге получается таблица, показанная на рис. 1-18. Заметьте, что сводная таблица на рисунке автоматически суммирует для вас строки и столбцы.

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

Когда требуемый отчет появился у вас в виде сводной таблицы, вы всегда можете выделить значения и вставить их в другой лист для дальнейшей работы. В нашем примере можно скопировать таблицу (А5:В18 в MacOS) и с помощью «Специальной вставки» перенести значения на новый лист под названием «Прибыль по каждой позиции» (рис. 1-19).

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

В методичке по торговле с лотка есть графа под названием «Комиссия». Оказывается, тренер О'Шонесси позволяет вам торговать с лотка, только если вы отправляете ему некую часть своей прибыли (возможно, чтобы облегчить его затраты на носки без пяток, которые он привык покупать). Графа «Комиссия» отображает, сколько процентов прибыли он забирает с каждой продажи.

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

Есть замечательная функция как раз для этой операции, которая умножит и сложит все, что надо, одним махом. Называется она довольно интересно – SUMPRODUCT/СУММПРОИЗВ. В ячейку Е 1 листа с прибылью по каждой позиции добавьте заголовок «Общая комиссия Тренера». В С2 поместите SUMPRODUCT/СУММПРОИЗВдля расчета прибыли и процентов от нее:

=SUMPRODUCT(B2:B15,'Fee Shedule'!B2:O2) /

=СУММПРОИЗВ(В2:В15,'Комиссия'!В2:О2)

Ого, да здесь ошибка! В ячейке видно только #Value/#Значение. Что же случилось?

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