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

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

Эти задачи сродни запросам «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).

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