Сводные таблицы - один из самых эффективных инструментов в MS Excel. Они могут за считанные секунды преобразовать миллион строк данных в лаконичный отчет. Помимо быстрых сводок, сводные таблицы позволяют буквально на лету изменять анализ, перетаскивая поля из одной области отчета в другую.
Электронная таблица Excel также является одним из самых недооцененных инструментов. Большинство пользователей не знают о возможностях, которые находятся у них под рукой. Представьте, что сводные таблицы еще не были изобретены. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте продукции всего четыре позиции. Продукцию регулярно покупают десятки клиентов, которые находятся в разных регионах. Каждая транзакция вносится в базу данных и является отдельной строкой.
Ваш директор просит вас подготовить сводный отчет о продажах всей продукции по регионам (периметру). Задача может быть решена следующим образом.
Сначала создайте макет таблицы, т.е. заголовок, состоящий из уникальных значений товаров и регионов. Сделайте копию колонки товаров и удалите дубликаты. Затем используйте специальную вставку для транспонирования столбца в строку. Аналогично работайте с регионами, только без транспонирования. Мы получим заголовок отчета.
Заполните эту таблицу, т.е. сложите поступления по соответствующим товарам и регионам. Это несложно сделать с помощью функции SUMMESLYMN. Добавьте также итоговые суммы. Вы получите сводный отчет о продажах в разделе "Регион-продукты".
Вы выполнили задание и показали отчет директору. Он смотрит на стол и предлагает несколько отличных идей.
- Можете ли вы показать прибыль вместо выручки?
- Можно ли показать товары по строкам, а регионы по столбцам?
- Можно ли составить такие таблицы для каждого отдельного менеджера?
Даже если вы опытный пользователь Excel, создание новых отчетов займет у вас много времени. Не говоря уже о возможности совершения ошибок. Однако если вы знаете, как сделать электронную таблицу в Excel, то ответ - да, это занимает у меня 5 минут, может быть, меньше.
Давайте рассмотрим, как создать электронную таблицу в Excel.
Создание сводной таблицы в Excel
Откройте исходные данные. Вы можете создать электронную таблицу из обычного диапазона, но более разумно преобразовать его в таблицу Excel. Это сразу же решит проблему автоматического сбора новых данных. Выделите любую ячейку и перейдите на вкладку Вставка. В левой части ленты находятся две кнопки: Сводная таблица и рекомендуемые сводные таблицы.
Если вы не знаете, как упорядочить имеющиеся данные, вы можете воспользоваться командой Recommended Crosstabs. Excel отобразит эскизы возможных макетов на основе ваших данных.
Нажмите на соответствующую опцию, и сводная таблица готова. Все, что вам нужно, - это правильно его составить, поскольку маловероятно, что стандартный шаблон будет именно таким, как вы ожидаете. Если вы хотите построить перекрестную таблицу с нуля или если у вас более старая версия программы, нажмите кнопку Crosstab. Появится окно, в котором нужно указать исходный диапазон (если активировать любую ячейку в таблице Excel, она укажет себя сама) и местоположение будущей сводной таблицы (по умолчанию будет выбран новый лист).
Обычно здесь ничего не нужно менять. После нажатия кнопки OK будет создан новый лист Excel с пустым макетом листа.
Макет таблицы настраивается на панели "Поля сводной таблицы" в правой части листа.
В верхней части панели находится список всех доступных полей, т.е. столбцов в исходных данных. Если вы хотите добавить новое поле в макет, вы можете установить флажок, и Excel сам определит, где должно располагаться это поле. Однако он не всегда угадывает, поэтому лучше перетащить мышь в соответствующее место на макете. Поля также можно удалить, отменив выделение или перетащив их обратно.
Сводная таблица состоит из 4 областей в нижней части панели: значения, строки, столбцы, фильтры. Давайте подробнее рассмотрим их назначение.
Область значений - это центральная часть сводной таблицы со значениями, которые были получены путем агрегирования исходных данных выбранным способом.
В большинстве случаев агрегирование выполняется с помощью Summation. Если все данные в выбранном поле являются числовыми, Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, вместо суммирования будет рассчитан подсчет ячеек. В нашем примере каждая ячейка представляет собой сумму всех соответствующих продуктов в регионе.
Существуют и другие способы вычисления ячеек в сводной таблице. Существует около 20 типов (среднее, минимальное значение, пропорция и т.д.). Существует несколько способов изменить метод расчета. Самый простой способ - щелкнуть правой кнопкой мыши в любой ячейке нужного поля в самой сводной таблице и выбрать другой метод агрегирования.
Область строк - Названия строк, которые находятся в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). Область строки может содержать несколько полей, в этом случае таблица является многоуровневой. Здесь обычно размещаются качественные переменные, такие как названия продуктов, месяцев, регионов и т.д.
Область столбцов - подобно строкам показывает уникальные значения выбранного поля, только по столбцам. Имена столбцов также обычно являются качественными характеристиками. Например, годы и месяцы, группы товаров.
Область фильтрации - используется, как следует из названия, для фильтрации. Например, сам отчет показывает продукты по регионам. Вы хотите ограничить сводную таблицу определенным сектором, периодом или руководителем. Затем вы помещаете себя в область фильтра и выбираете нужное значение из выпадающего списка.
Добавляя и удаляя поля в этих областях, вы можете настроить любой фрагмент ваших данных за считанные секунды.
Давайте посмотрим, как это работает в действии. Давайте создадим таблицу, аналогичную уже созданной с помощью функции SUMMESLYMN. Для этого перетащите поле 'Revenue' в область Value, поле 'Area' (регион продаж) в область Rows и поле 'Goods' в область Columns.
В результате получилась настоящая электронная таблица.
На его создание ушло буквально 5-10 секунд.
Работа со сводными таблицами в Excel
Изменить существующую электронную таблицу так же просто. Посмотрим, насколько легко исполнятся желания режиссера.
Замените выручку на прибыль.
Товары и области также можно менять местами, перетаскивая мышью.
Существует несколько инструментов для фильтрации сводных таблиц. В данном случае мы просто поместили поле 'Manager' в область фильтра.
На все про все ушло несколько секунд. Вот как работать с перекрестными таблицами. Конечно, не все задачи настолько тривиальны. Бывают случаи, когда необходимо использовать более сложную агрегацию, добавить вычисляемые поля, условное форматирование и т.д. Но это уже в другой раз.
Источник данных сводной таблицы Excel
Для успешной работы с электронными таблицами исходные данные должны отвечать ряду требований. Они должны иметь название над каждым полем (столбцом) для их идентификации. А теперь несколько полезных советов.
1. наилучшим форматом для данных является электронная таблица Excel. Это хорошо, потому что каждое поле имеет имя, и при добавлении новых строк они автоматически включаются в электронную таблицу.
2. избегайте повторения групп в качестве столбцов. Например, все даты должны быть в одном поле, а не разбиты по месяцам в отдельных колонках.
3. устраните пробелы и пустые ячейки, иначе строка может выпасть из анализа.
4. Примените правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датами. В противном случае у вас возникнут проблемы с группировкой и математической обработкой. Но Excel поможет вам здесь, потому что он делает довольно хорошую работу по определению формата данных.
Как правило, требований немного, но вы должны знать о них.
Обновление данных в сводной таблице Excel
Если вы внесете изменения в источник (например, добавите новые строки), электронная таблица не будет обновлена, пока вы не щелкните правой кнопкой мыши
или
на вкладке Данные нажмите кнопку Обновить все.
Это сделано намеренно, поскольку crosstab занимает много места в памяти. Для экономии ресурсов компьютера он работает не напрямую с источником, а с кэшем, который содержит снимок исходных данных.
Умение работать с электронными таблицами в Excel, даже на таком базовом уровне, может значительно повысить скорость и качество обработки больших массивов данных.
