Сводные таблицы Excel

Сводные таблицы — это механизм отчетности, встроенный в Excel. Это лучший инструмент в Excel для анализа данных без формул. Вы можете создать базовую сводную таблицу примерно за одну минуту и начать интерактивный анализ ваших данных.

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

1. Как построить Сводную таблицу за одну минуту?

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

Начните с выбора любой ячейки в исходных данных:

Далее выполните следующие четыре шага:

• На вкладке «Вставка» ленты нажмите кнопку «Сводная таблица».
• В диалоговом окне «Создание сводной таблицы» проверьте данные и нажмите кнопку «ОК».
• Перетащите поле (например, Товар) в область «Строки»
• Перетащите числовое поле в область «Значения» (например, Продажи).

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

2. Какие должны быть идеальные исходные данные?

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

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

3. Как проверить правильно ли работает Сводная таблица?

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

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

4. Как правильно спланировать работу со сводной таблицей?

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

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

5. Как создать «динамический диапазон»?

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

Чтобы использовать таблицу для сводной таблицы:

• Выберите любую ячейку в данных с помощью сочетания клавиш Ctrl + T, чтобы создать таблицу
• Нажмите кнопку Сводная таблица на вкладке Конструктор
• Создайте свою сводную таблицу
• Преимущество: данные, которые вы добавляете в таблицу, автоматически отображаются в сводной таблице при обновлении.

6. Как используйте сводную таблицу для подсчета?

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

Чтобы получить разбивку по отделам, выполните следующие действия:

• Создайте сводную таблицу
• Добавьте отдел в поле Строки
• Добавьте поле Имя в качестве значения
• Сводная таблица будет отображать количество сотрудников по отделам

7. Как показать итоги в процентах?

Во многих сводных таблицах вам нужно показывать процент, а не количество. Например, возможно, вы хотите показать разбивку продаж по продуктам. Но вместо того, чтобы показывать общие продажи для каждого продукта, вы хотите показать продажи в процентах от общего объема продаж.

Предполагая, что у вас есть поле с именем Продажи в ваших данных, просто выполните следующие действия:

• Добавьте товар в сводную таблицу в поле Строки
• Добавьте Продажи в сводную таблицу в качестве значения
• Щелкните правой кнопкой мыши поле «Продажи» и установите «Дополнительные вычисления» на «% от общей суммы»

8. Как использовать сводную таблицу для построения списка уникальных значений?

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

Например, предположим, что у вас есть данные о продажах, и вы хотите увидеть список каждого проданного товара. Чтобы создать список продуктов:

• Создайте сводную таблицу
• Добавьте товар в поле Строки
• Сводная таблица покажет список всех товаров, которые появляются в данных о продажах

9. Как создать автономную сводную таблицу?

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

1. Обновите сводную таблицу, чтобы обеспечить актуальность кэша (Данные > Обновить все)
2. Удалите лист, содержащий данные
3. Используйте вашу сводную таблицу как обычно

10. Как сгруппировать сводную таблицу вручную?

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

Например, предположим, что у вас есть сводная таблица, которая показывает распределение сотрудников по отделам. Вы хотите еще больше сгруппировать отделы: Продажи, Закуп и Маркетинг в группу 1, а Эксплуатацию и Бухгалтерию — в группу 2. Группа 1 и группа 2 не отображаются в данных, это ваши собственные пользовательские группы.

Чтобы сгруппировать сводную таблицу в специальные группы, нужно:

• Удерживая клавишу «Ctrl», выберите каждый элемент в первой группе.
• Щелкните правой кнопкой мыши один из элементов и выберите в меню «Группа».
• Excel создает новую группу «Группа1»
• Выберите Эксплуатация и Бухгалтерия в столбце B и сгруппируйте, как указано выше.
• Excel создает другую группу, «Группа2»

11. Как сгруппировать числовые данные в диапазоны?

Одна из наиболее интересных и мощных функций, которые есть в каждой сводной таблице, — это возможность группировать числовые данные в диапазоны или сегменты.

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

• Создайте свою сводную таблицу
• Добавьте Возраст в поле Строки, Результат голосования в поле Столбцы и Имя в качестве значения
• Щелкните правой кнопкой мыши любое значение в поле Возраст и выберите Группировать.
• Введите 10 в качестве интервала в области ввода «с шагом:»
• Когда вы нажмете ОК, вы увидите данные голосования, сгруппированные по возрасту в 10-летние сегменты.

12. Как переименовать поля для лучшей читаемости?

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

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

13. Как поменять имя поля, когда Excel жалуется?

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

Например, предположим, что у вас есть поле с именем Продажи в ваших исходных данных. В качестве поля значения оно отображается как Сумма по полю Продажи, но (разумно) вы хотите, чтобы оно было «Продажи». Однако, когда вы пытаетесь использовать Продажи, Excel жалуется, что поле уже существует, и выдает сообщение об ошибке «Имя поля сводной таблицы уже существует».

В качестве простого обходного пути, просто добавьте пробел в конце вашего нового имени поля. Разница не видна, а Excel не будет жаловаться.

14. Как добавить поле более одного раза в сводную таблицу?

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

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

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

• Добавьте текстовое поле в область значений (например, имя, фамилия и т.д.)
• По умолчанию вы получите счетчик для текстовых полей
• Снова добавьте то же поле в область значений
• Щелкните правой кнопкой мыши второй экземпляр и выберите Дополнительные вычисления «% от общего итога».
• Переименуйте оба поля как хотите

15. Как автоматически форматировать все поля значений?

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

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

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

• Щелкните правой кнопкой мыши поле «Продажи» и выберите «Настройки поля значений» в меню
• Нажмите кнопку «Числовой формат» в открывшемся диалоговом окне «Параметры полей значений»
• Установите формат для учета и нажмите кнопку ОК, чтобы выйти

16. Как увидеть (или извлечь) данные любого итога?

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

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

17. Как клонировать свои сводные таблицы, когда вам нужно другое представление?

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

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

Другой способ клонировать сводную таблицу — скопировать сводную таблицу и вставить ее в другое место. Используя эти подходы, вы можете сделать столько копий, сколько захотите.

При таком клонировании сводной таблицы обе сводные таблицы совместно используют один и тот же сводный кеш. Это означает, что при обновлении любого из клонов (или оригинала) все связанные сводные таблицы будут обновлены.

18. Как отключить самостоятельное обновление сводной таблицы?

После клонирования сводной таблицы вы можете столкнуться с ситуацией, когда вы действительно не хотите, чтобы клон был связан с тем же сводным кэшем, что и оригинал.

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

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

• Вырежьте всю сводную таблицу в буфер обмена
• Вставьте сводную таблицу в совершенно новую книгу
• Обновить сводную таблицу
• Скопируйте его снова в буфер обмена
• Вставьте его обратно в оригинальную книгу
• Удалите временную книгу
• Ваша сводная таблица теперь будет использовать свой собственный сводный кэш и не будет обновляться с другими сводными таблицами в книге или совместно использовать ту же группу полей.

19. Как избавиться от ненужных заголовков?

Макет по умолчанию для новых сводных таблиц — это Сжатый макет. Этот макет будет отображать «Строки» ​​и «Столбцы» в качестве заголовков в сводной таблице. Это не самые интуитивные заголовки, особенно для людей, которые не часто используют сводные таблицы.

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

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

20. Как улучшить внешний вид таблицы?

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

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

21. Как избавиться от итогов по строкам и столбцам?

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

22. Как форматировать пустые ячейки?

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

Чтобы установить собственный символ, щелкните правой кнопкой мыши внутри сводной таблицы и выберите «Параметры сводной таблицы». Затем убедитесь, что установлен флажок «Для пустых ячеек отображать:», и введите символ, который хотите видеть. Имейте в виду, что этот параметр соответствует применяемому числовому формату.

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

23. Как отключить автообновление ширины столбцов при необходимости?

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

Обычно это удобно, но может свести вас с ума, если у вас есть другие таблицы на рабочем листе вместе со сводной таблицей, или если вы тщательно отрегулировали ширину столбцов вручную и не хотите, чтобы они изменялись.

Чтобы отключить эту функцию, щелкните правой кнопкой мыши внутри сводной таблицы и выберите «Параметры сводной таблицы». На первой вкладке параметров (или вкладки макета на Mac) снимите флажок «Автоматически изменять ширину столбцов при обновлении».