Назовем колонку F в нашем листе «Calories / Калории». Ячейка F2 будет содержать количество калорий из таблицы в товаре из первой строки пиве. Используя эту формулу, можно указать в названии товара из ячейки А2 ссылку на таблицу Calories!$A$1:$B$15 и номер столбца, из которого следует выбирать значения. В нашем случае он второй по счету:
=VLOOKUP(A2,Calories!$A$1:$B$15,2,FALSE) /
=ВПР(A2,Calories!$A$1:$B$15,2,ЛОЖЬ)
FALSE/ЛОЖЬ в конце формулы означает, что вам не подходят приблизительные значения «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 позволяют вставлять сводную таблицу в существующий лист, ее, как правило, помещают на отдельном, если нет явной причины сделать иначе.