Методическая разработка "Сводные таблицы"

Сводные таблицы. Основные настройки

Сводные таблицы обобщают данные по набору критериев. В гугл-таблицах и Excel сводные таблицы можно построить двумя способами:

— с помощью формул с условиями.

— с помощью инструмента «сводные таблицы». Он позволяет создавать сводные таблицы проще и быстрее.

Рассмотрим подробно второй, более простой способ. Все иллюстрации и инструменты, приведенные в инструкциях к этому материалу, взяты из меню Google-таблиц. В меню таблиц Excel можно найти соответствующие им команды.

Структура таблиц

В этом уроке вы сами создадите структуру таблицы, чтобы потом её заполнить. Для этого разберёмся, из каких элементов состоят таблицы.

Легенды и области значений

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

Критерии для расчётов

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

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

Расположение: в строках и в столбцах

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

В строках: если критерии располагаются в одном столбце, но в разных строках.

В столбцах: если критерии находятся в одной строке, но в разных столбцах.

💡 Как выглядят сводные таблицы

1. Во всех таблицах есть легенда, а есть область значений.

2. В сводных таблицах в легенде находятся не только заголовки строк и столбцов, но и конкретные значения критериев.

3. Если в легенде сводной таблицы критерии располагаются в одном столбце, но в разных строках, то мы говорим «критерии располагаются в строках», если в одной строке, но в разных столбцах — «критерии располагаются в столбцах».

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

• Выделить таблицу с исходными данными.

(Скачать пример с исходными данными для создания сводных таблиц можно здесь: https://cloud.mail.ru/public/cbj5/ysd7s14pF )

• Нажать в меню Вставка → Создать сводную таблицу.

• Разместить сводную таблицу на новом листе или на существующем.

Задание 1

1. Выделите все данные на листе Export.

2. Нажмите Вставка → Создать сводную таблицу.

3. Выберите вариант «новый лист» и нажмите Создать.

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

Редактор сводной таблицы

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

В заготовке будущей таблицы «Строки» и «Столбцы» — это область легенды будущей таблицы. Здесь будут критерии для расчётов.

«Значения» — это область значений будущей таблицы, здесь появятся сами расчёты.

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

Легенда в сводной таблице

Сделаем таблицу с помощью инструмента «сводные таблицы». Вот так она будет выглядеть:

В этой таблице два критерия:

1. размер скидки в процентах, данные хранятся в столбце «Скидка (%)».

2. номер месяца, данные хранятся в столбце «Месяц».

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

 Задание 2: заготовки для будущих сводных таблиц

1. На отдельном листе создайте сводную таблицу. В Строки добавьте критерий «Скидка (%)», в Столбцы — критерий «Месяц».

2. Н а отдельном листе создайте вторую сводную таблицу. Сначала добавьте в Строки критерий «Вид», а затем туда же — критерий «Скидка (%)».

3. В настройках обеих таблиц уберите все галочки в пункте «Показывать итог».

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

И у вас должны были получиться такие таблицы:

Таблица со скидками по месяцам

Таблица со скидками по товарам и услугам

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

В легенде наших таблиц есть пустые ячейки. Они появились из-за того, что перед вставкой мы выделили всю таблицу с исходными данными, в том числе незаполненные строки. Для того, чтобы убрать эти пустые ячейки есть настройка Фильтры. Нужно закинуть в Фильтры критерии, которые мы добавляли в легенду и теперь хотим их как-то поменять, — например, убрать пустые значения. Закидываем столбец «Скидка (%)» в Фильтры и настраиваем как при обычной фильтрации — убираем галочки напротив «Пустые строки».

Остался последний шаг — заполнить область значений.

Порядок заполнения области значений сводной таблицы

Продолжаем заполнять уже знакомую нам таблицу со скидками по месяцам с помощью инструмента «сводная таблица».

Шаг 1. Находим столбец со значениями и определяем, что это будут за расчёты.

Шаг 2. Перетаскиваем заголовок «Скидка (руб.)» в поле Значения.

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

Итог выглядит так:

Задание 3: минимальная скидка по месяцам

1. Откройте заготовку со сводной таблицей со скидками по месяцам. В поле Значения добавьте столбец «Скидка (руб.)».

2. В поле «Суммировать по» установите MIN (минимум) и ответьте на вопрос Каков размер минимальной скидки в 25% в феврале?

Несколько значений

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

В поле Значения мы три раза добавили столбец «Скидка (руб.)

А потом можно отдельно настроить каждое из Значений. Например, так:

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

Задание 4: сводная информация по всем скидкам

1. Откройте заготовку со сводной таблицей со скидками на товары и услуги.

2. Создайте сводную таблицу, как на картинке:

Заголовки

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

Заголовки слишком длинные и визуально сливаются в кучу

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

Задание 5: меняем заголовки в легенде

Замените заголовки в вашей сводной таблице и для четырёх столбцов установите такой формат данных, чтобы было понятно, что перед нами рубли.

Финальный результат должен выглядеть так:

Заголовки листов в файле — это тоже важная составляющая табличного этикета. Когда вы создаёте сводную страницу на новом листе, ей присваивается название «Сводная таблица 1». Это тоже не очень удобно, поэтому лучше сразу менять на говорящее название.

Заголовки единообразны, и работать с таким файлом будет неудобно

Задание 6: меняем заголовки листов

Замените автоматически сгенерированные заголовки листов «Сводная таблица №…» на любые другие. Например, «Скидки по месяцам» и «Скидки: итог».

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

💡 Алгоритм создания сводной таблицы:

1. Настройте легенду таблицы

— определите, сколько наборов критериев будет в вашей будущей таблице;

— поймите, в каких столбцах содержатся критерии, которые вам нужны;

— перетащите заголовки нужных вам столбцов в Строки и Столбцы;

— уберите пустые ячейки из легенды сводной таблицы с помощью Фильтров;

2. Заполните область значений

— определите, в каких столбцах находятся значения, с которыми вам нужно произвести расчёты;

— перетащите заголовки этих столбцов в Значения;

— в графе Суммировать по выберите тип расчётов: сумма, количество, среднее и т. д.

3. Сделайте сводную таблицу удобной для чтения:

— вручную поменяйте автоматические заголовки в легенде

— замените формат данных, если это необходимо.

Фильтрация

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

Задание 5: сводная таблица доходов по категориям

1. На новом или существующем листе создайте сводную таблицу всех доходов. Должно получиться как на скриншоте:

1. Чтобы скрыть из таблицы категории расходов, добавьте в Фильтры столбец «Операции».

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

Например, только что вы установили фильтр по столбцу «Операция», в то время как в Строках была «Категория», в Столбцах — «Месяц», а в Значениях — «Итоговая сумма». Таким образом, вы сократили количество действий.

Создайте отчёт доходов и расходов с помощью сводных таблиц.

Так он должен выглядеть: