Как создать сводную таблицу в Excel: пошаговое руководство

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

Хорошая новость: научиться создавать сводную таблицу в Excel намного проще, чем вы могли подумать.

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

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

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

Для чего используются сводные таблицы?

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

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

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

1. Сравнение продаж различных продуктов.

Допустим, у вас есть рабочий лист, содержащий ежемесячные данные о продажах трех разных продуктов — продукта 1, продукта 2 и продукта 3 — и вы хотите выяснить, какой из трех приносит больше всего денег. Конечно, вы могли бы просмотреть таблицу и вручную добавить соответствующую цифру продаж к промежуточной сумме каждый раз, когда появляется продукт 1. Затем вы можете сделать то же самое для продукта 2 и продукта 3, пока не получите итоговые значения для всех из них. Кусок пирога, верно?

Теперь представьте, что ваш ежемесячный лист продаж содержит тысячи и тысячи строк. Ручная сортировка их всех может занять всю жизнь. Используя сводную таблицу, вы можете автоматически агрегировать все показатели продаж для продукта 1, продукта 2 и продукта 3 — и вычислить их соответствующие суммы — менее чем за минуту.

2. Отображение продаж продукта в процентах от общего объема продаж.

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

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

С помощью сводной таблицы вы можете настроить каждый столбец так, чтобы он давал вам процент столбца от всех трех итогов столбца, а не только итог столбца. Например, если продажи трех продуктов составили 200 000 долларов США, а первый продукт принес 45 000 долларов США, вы можете отредактировать сводную таблицу, чтобы вместо этого сказать, что этот продукт обеспечил 22,5% всех продаж компании.

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

3. Объединение повторяющихся данных.

В этом сценарии вы только что завершили редизайн блога и должны были обновить кучу URL-адресов. К сожалению, ваше программное обеспечение для создания отчетов в блогах не очень хорошо справилось с этим и в конечном итоге разделило показатели «просмотра» для отдельных сообщений между двумя разными URL-адресами. Итак, в вашей электронной таблице у вас есть два отдельных экземпляра каждого отдельного сообщения в блоге. Чтобы получить точные данные, вам нужно объединить итоги просмотров для каждого из этих дубликатов.

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

4. Получение численности сотрудников по отдельным отделам.

Сводные таблицы полезны для автоматического расчета вещей, которые вы не можете легко найти в базовой таблице Excel. Одной из таких вещей является подсчет строк, которые имеют что-то общее.

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

5. Добавление значений по умолчанию в пустые ячейки.

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

Вы можете легко настроить сводную таблицу, чтобы заполнить пустые ячейки значением по умолчанию, например, 0 долларов США или подлежит уточнению (подлежит определению). Для больших таблиц данных возможность быстро пометить эти ячейки является полезной функцией, когда многие люди просматривают один и тот же лист.

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

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

1. Введите данные в ряд строк и столбцов.
2. Отсортируйте данные по определенному атрибуту.
3. Выделите ячейки, чтобы создать сводную таблицу.
4. Перетащите поле в область «Метки строк».
5. Перетащите поле в область «Значения».
6. Уточните свои расчеты.

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

Шаг 1. Введите данные в ряд строк и столбцов.

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

Например, чтобы создать таблицу Excel с данными об эффективности сообщений в блоге, у вас может быть столбец со списком «Лучшие страницы», столбец со списком «Клики» каждого URL-адреса, столбец со списком «Показов» каждого сообщения и т. д. (Мы будем использовать этот пример в следующих шагах.)

Шаг 2. Отсортируйте данные по определенному атрибуту.

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

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

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

Выберите « ОК » в правом нижнем углу окна «Сортировка», и вы успешно переупорядочите каждую строку листа Excel по количеству просмотров, полученных каждым сообщением в блоге.

Шаг 3. Выделите ячейки, чтобы создать сводную таблицу.

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

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

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

Примечание. Если вы используете более раннюю версию Excel, «Сводные таблицы» могут находиться в разделе « Таблицы » или « Данные » в верхней части навигации, а не «Вставить». В Google Таблицах вы можете создавать сводные таблицы из раскрывающегося списка « Данные » в верхней части навигации.

Шаг 4. Перетащите поле в область «Метки строк».

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

Например, допустим, вы хотите упорядочить набор данных блога по заголовку сообщения. Для этого просто щелкните и перетащите поле «Лучшие страницы» в область «Ярлыки строк».

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

Шаг 5. Перетащите поле в область «Значения».

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

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

Шаг 6. Настройте свои расчеты.

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

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

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

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

Копаем глубже с помощью сводных таблиц

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

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

Примечание редактора: этот пост был первоначально опубликован в декабре 2018 года и обновлен для полноты.