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

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

является отсылкой к количеству калорий в пиве, несмотря на то, что вы в данный момент работаете с другим листом.

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

.

Назовем колонку F в нашем листе «Calories / Калории». Ячейка F2 будет содержать количество калорий из таблицы в товаре из первой строки – пиве. Используя эту формулу, можно указать в названии товара из ячейки А2 ссылку на таблицу

и номер столбца, из которого следует выбирать значения. В нашем случае он второй по счету:

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

После ввода формулы вы увидите, что 200 калорий считались из таблицы в листе «Calories». Поставив $ в формуле перед ссылками на таблицу, вы можете скопировать формулу вниз по колонке двойным щелчком на нижнем правом углу ячейки. Оп-ля! У вас есть количество калорий для каждой позиции, как показано на рис. 1-11.

Фильтрация и сортировка

Отразив в листе продаж калорийность ваших товаров, задайтесь целью видеть, например, только товары из категории «Замороженные продукты» – иными словами, отфильтровать ваш лист. Для этого сначала выберите данные в рамках А1:F200. Наведите курсор на А1 и нажмите Shift+Ctrl+?, а затем ?. Есть способ еще проще – кликнуть наверху столбца и, удерживая клавишу мышки нажатой, переместить курсор к столбцу F, чтобы выделить все 6 столбцов.

Затем, чтобы применить автофильтрацию к этим шести колонкам, нажмите кнопку «Фильтр» из вкладки «Данные». Она похожа на серую воронку, как на рис. 1-12.

Если автофильтрация включена, можно кликнуть на выпадающем меню, которое появляется в ячейке В1, и выбрать для показа только определенные категории (в данном случае отобразятся товары из категории «Замороженные продукты»), как на рис. 1-13.

После фильтрации выделение столбцов данных позволяет нижней панели показывать краткую информацию об этих ячейках. Например, отфильтровав только замороженные продукты, можно выделить значения в столбце Е и использовать нижнюю панель, чтобы быстро узнать сумму прибыли только по этой категории товара, как на рис. 1-14.

Автофильтрация позволяет также производить сортировку. К примеру, если вы хотите рассортировать прибыль, просто кликните на меню автофильтрации в ячейке Profit/Прибыль (D1) и выберите сортировку по возрастанию (или убыванию), как на рис. 1-15.

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

Excel также предлагает интерфейс для выполнения более сложных сортировок, чем те, на которые способна автофильтрация. Чтобы использовать его, выделите данные для сортировки (снова выберите A: F) и нажмите «Сортировка» в разделе «Сортировка и фильтр» во вкладке «Данные». На экране появится меню сортировки. В MacOS для вызова этого меню нужно нажать стрелку вниз на кнопке сортировки и выбрать настройку.

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

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

Использование сводных таблиц

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

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

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

Использование формул массива

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

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

Есть замечательная функция как раз для этой операции, которая умножит и сложит все, что надо, одним махом. Называется она довольно интересно –

. В ячейку Е 1 листа с прибылью по каждой позиции добавьте заголовок «Общая комиссия Тренера». В С2 поместите

для расчета прибыли и процентов от нее:

Ого, да здесь ошибка! В ячейке видно только

. Что же случилось?

Несмотря на то, что вы выбрали две области данных одинакового размера и применили к ним функцию

, формула не видит их равенства, потому что одна область вертикальная, а другая – горизонтальная.

К счастью, в Excel есть функция для расположения массивов в нужном направлении. Она называется

. Нужно написать такую формулу:

Но нет! Все еще возникает ошибка.

Причина, по которой это происходит, такова: каждая функция Excel по умолчанию возвращает результат в виде единственного значения. Даже если

вернет первое значение в транспонированный массив. Если вы хотите видеть в результате целый массив, нужно включить

в «формулу массива». Формулы массива действительно возвращают результат в виде массива, а не единственного значения.

Вам не нужно писать

как-то иначе, чтобы все получилось. Все, что следует сделать – это вместо клавиши «Enter» после ввода формулы нажать «Ctrl+Shift+Enter». В MacOS нужное сочетание – «Command+Return».

Победа! Как видно на рис. 1-20, результатом вычислений является 57,60 долларов. Но я бы округлил его до 50 – неужели Тренеру нужно столько носков?