EXCEL: Как сделать сводную таблицу в Excel
Функция сводных таблиц в Excel — огромная помощь в работе со сложными, неупорядоченными и объемными массивами данных. Она позволяет упорядочить и суммировать данные, чтобы сосредоточиться на наиболее значимых фактах. Благодаря данной функции самый необъятный массив предстанет в ясной, сжатой и управляемой форме. В данной статье мы рассмотрим особенности создания сводных таблиц в Excel, чтобы Вы могли использовать эту функцию в своей работе
«Сводные таблицы помогут Вам автоматически вычислить итоговые значения без необходимости создания формул, что делает их очень полезными. Несмотря на то, что это может показаться сложным, на самом деле это довольно просто. Давайте разберемся вместе»
Шлычков Константин
Эксперт и преподаватель Excel
Особенности сводных таблиц
Функция сводных таблиц в Excel представляет собой мощный инструмент для обработки больших объемов данных. Она позволяет быстро и наглядно анализировать информацию, добавлять и удалять поля, переворачивать строки и столбцы, а также рассчитывать различные показатели без необходимости использования функций вручную. Тем более, сводная таблица намного проще и понятнее любых формул.
С помощью сводных таблиц можно фильтровать, группировать и упорядочивать данные по различным параметрам, таким как даты или наименования продуктов. Это позволяет избежать ручной сортировки и подсчета данных, а вместо этого быстро провести анализ по каждой категории.
Одной из главных особенностей сводных таблиц является возможность разворачивать и сворачивать уровни представления данных, чтобы просмотреть более подробные сведения об итогах. Это делает анализ данных более гибким и удобным.
С помощью сводных таблиц можно фильтровать, группировать и упорядочивать данные по различным параметрам, таким как даты или наименования продуктов. Это позволяет избежать ручной сортировки и подсчета данных, а вместо этого быстро провести анализ по каждой категории.
Одной из главных особенностей сводных таблиц является возможность разворачивать и сворачивать уровни представления данных, чтобы просмотреть более подробные сведения об итогах. Это делает анализ данных более гибким и удобным.
Как сделать сводную таблицу в Excel
Шаг 1. Настройка данных
Это важный этап в создании сводной таблицы, именно от него будет зависеть то, насколько корректно и полноценно она будет работать.
Это важный этап в создании сводной таблицы, именно от него будет зависеть то, насколько корректно и полноценно она будет работать.
- Для более удобного создания сводной таблицы в Excel рекомендуется преобразовать набор данных в умную таблицу, в дальнейшем это облегчит работу со сводной. Для этого выберите любую ячейку с данными и нажмите сочетание клавиш «Ctrl» и английскую букву «T» и после «ОК».
- Проверьте, чтобы заголовки столбцов были уникальными и осмысленными, и имелись в каждом столбце — в дальнейшем это может Вам в составлении сводной таблицы.
- Удалите пустые значения и промежуточные итоги, объединения ячеек.
- Приведите данные в правильный формат: если записи в столбце «Дата» будут формата текст, группировка по месяцам или годам будет невозможна.
Шаг 2. Определение целей
Для того чтобы работать со сводной таблицей было проще, необходимо запланировать, какие вычисления нам нужны.
В качестве примера мы будем использовать данные поставок инструментов из разных стран, с датой поставки и ценой.
Для того чтобы работать со сводной таблицей было проще, необходимо запланировать, какие вычисления нам нужны.
В качестве примера мы будем использовать данные поставок инструментов из разных стран, с датой поставки и ценой.
Допустим, сводная таблица необходима нам для того чтобы ответить на следующие вопросы:
- Какая страна поставляет нам больше всего товаров? Для ответа на этот вопрос, нам необходимо получить количество по столбцу «Товар» и группировку по столбцу «Страна».
- Какая средняя цена заказанных товаров? Для ответа на этот вопрос необходимо получить среднее значение по столбцу «Цена».
Шаг 3. Создание сводной таблицы.
Создание сводной таблицы в Excel можно осуществить несколькими способами. Один из них – выбрать рекомендуемый макет. Для этого необходимо находясь в любой ячейке исходных данных умной таблицы, перейти на вкладку «Вставка» и выбрать «Рекомендуемые сводные таблицы».
В появившемся диалоговом окне можно ознакомиться с рекомендуемыми макетами и выбрать подходящий. Чаще всего их достаточно, чтобы удовлетворить стандартные запросы: сосчитать количество товара, суммы закупок и др.
Создание сводной таблицы в Excel можно осуществить несколькими способами. Один из них – выбрать рекомендуемый макет. Для этого необходимо находясь в любой ячейке исходных данных умной таблицы, перейти на вкладку «Вставка» и выбрать «Рекомендуемые сводные таблицы».
В появившемся диалоговом окне можно ознакомиться с рекомендуемыми макетами и выбрать подходящий. Чаще всего их достаточно, чтобы удовлетворить стандартные запросы: сосчитать количество товара, суммы закупок и др.
Если какой-то из макетов Вам подходит, просто нажмите «ОК» в диалоговом окне и Ваши данные преобразуются в выбранный формат, при этом сводная таблица будет создана на новом листе. Далее мы подробно расскажем о том, как ее можно отредактировать.
Второй способ: настроить сводную таблицу самостоятельно
Если рекомендации Excel Вам не подошли, сводную таблицу можно создать вручную: для этого нажмите на любую из ячеек исходных данных, которые хотите использовать, перейдите на вкладку «Вставка» в верхней части экрана и нажмите на кнопку «Сводная таблица». Появится диалоговое окно с настройкой параметров.
Если же нужный макет отсутствует, можно создать сводную таблицу вручную. Для этого необходимо выделить данные, находящиеся в исходной таблице, перейти на вкладку «Вставка» и выбрать «Сводная таблица». В появившемся диалоговом окне необходимо выбрать поля, которые будут использоваться в сводной таблице.
Если рекомендации Excel Вам не подошли, сводную таблицу можно создать вручную: для этого нажмите на любую из ячеек исходных данных, которые хотите использовать, перейдите на вкладку «Вставка» в верхней части экрана и нажмите на кнопку «Сводная таблица». Появится диалоговое окно с настройкой параметров.
Если же нужный макет отсутствует, можно создать сводную таблицу вручную. Для этого необходимо выделить данные, находящиеся в исходной таблице, перейти на вкладку «Вставка» и выбрать «Сводная таблица». В появившемся диалоговом окне необходимо выбрать поля, которые будут использоваться в сводной таблице.
Таблица или диапазон. Выделите диапазон ячеек, который Вы хотели бы отформатировать в сводную таблицу. Если Excel не определяет верный диапазон автоматически, его можно выделить вручную или нажать сочетание клавиш «Ctrl» и «A».
Расположение. Далее следует определиться, хотите ли Вы разместить сводную таблицу на новом листе или на уже открытом. Для этого выберите один из пунктов: «Новый лист» или «Существующий лист» и укажите место для сводной. Удобнее всего создавать таблицу на новом листе.
Расположение. Далее следует определиться, хотите ли Вы разместить сводную таблицу на новом листе или на уже открытом. Для этого выберите один из пунктов: «Новый лист» или «Существующий лист» и укажите место для сводной. Удобнее всего создавать таблицу на новом листе.
Модель данных. Галочку с этого пункта необходимо снять, в данном случае она нам не понадобится.
Нажмите кнопку «ОК». Откроется новый лист Excel: слева Вы увидите диапазон будущей сводной таблицы, а справа панель «Поля сводной таблицы», с которым мы продолжим работу далее.
Поля сводной таблицы
Далее необходимо поместить поля из списка в соответствующие зоны на нижней части панели «Поля сводной таблицы». Чтобы отфильтровать данные, нужно переместить соответствующее поле в зону «Фильтры». Для отображения данных в столбцах таблицы нужно поместить поля в зону «Столбцы», а для отображения данных в строках — в зону «Строки». В зоне «Значения» выбираются данные, которые будут отображаться в ячейках таблицы, например, сумма. Порядок перемещения и количество полей в каждой области может быть другим.
Чтобы понять, как поля будут представлены в сводной таблице, рассмотрим пример. Мы выбрали поле «Месяцы» для столбцов, поле «Страна» для строк и поле «Цена» для значений. Такое распределение полей создаст таблицу, отображающую сумму покупок по месяцам (столбцы) и странам (строки).
Фильтрация и сортировка
Excel автоматически добавляет к сводной таблице функцию автофильтрации, которая представлена кнопками фильтрации у названий строк и столбцов. Эта функция позволяет отбирать нужные записи и сортировать их в нужном порядке.
Кроме того, можно использовать дополнительный способ фильтрации. При перемещении какого-либо поля исходной таблицы в область «Фильтры» в нижней части панели «Поля сводной таблицы», сверху от таблицы появляется фильтр с выпадающим списком, содержащим значения из этого поля.
В данном случае, поле «Товар» было перемещено в область «Фильтры», что позволяет осуществлять фильтрацию информации в сводной таблице по определенному товару. Например, можно узнать, на какие суммы его закупили по месяцам и в каких странах он закупается.
Если Вы добавили несколько полей для строк или столбцов в сводную таблицу, Excel предоставляет возможность временно скрыть или раскрыть показатели по второму дополнительным полям с помощью маркеров свертывания (-) и (+). При желании данную функцию можно отключить: на вкладке «Анализ сводной таблицы», нажмите на «Показать» и выберите «Кнопки +/-».
Другие функции расчета итогов вместо стандартной суммы
Excel автоматически применяет функцию Сумма к числовым значениям, которые добавляются в область «Значения» в сводной таблице. В случае, если в этой области находятся нечисловые данные, такие как текст, дата или логические значения, Excel использует функцию Количество.
Помимо функций Сумма и Количество, Excel предоставляет несколько других функций для вычисления значений в области «Значения» сводной таблицы, включая Среднее, Максимум, Минимум, Произведение и другие.
Для выбора нужной функции необходимо нажать на стрелочку рядом с полем в области «Значения», затем выбрать «Параметры полей значений…» в меню и выбрать нужную функцию из списка. После этого нужно нажать «ОК».
Для выбора нужной функции необходимо нажать на стрелочку рядом с полем в области «Значения», затем выбрать «Параметры полей значений…» в меню и выбрать нужную функцию из списка. После этого нужно нажать «ОК».
Дополнительные вычисления
Excel имеет дополнительную функцию для отображения итоговых значений, например, в виде процентов или долей.
Если Вы хотите применить данную функцию, нужно щелкнуть правой кнопкой мыши на столбец со значениями, к которым Вы хотите ее применить, выбрать «Дополнительные вычисления» и выбрать нужную функцию из списка. После этого нажмите «ОК», и Вы увидите измененное отображение значений в таблице.
Как открыть или закрыть меню «Поля сводной таблицы»
Если Вы хотите освободить рабочее пространство, Вы можете закрыть «Поля сводной таблицы», нажав крестик «Закрыть» в правом верхнем углу панели. Однако, чтобы снова ее открыть, нужно выполнить несколько дополнительных шагов.
Чтобы вернуть панель «Поля сводной таблицы», следует выбрать любую ячейку таблицы, нажать правую кнопку мыши и выбрать опцию «Показать список полей» в самом низу контекстного меню.
Обновление сводной таблицы
Необходимо помнить, что при изменении данных в исходной таблице обновление сводных таблиц не происходит автоматически.
Поэтому при добавлении новых записей, сводную таблицу следует обновить вручную: выберите ее любую ячейку, на вкладке «Анализ сводной таблицы» в группе «Данные» нажмите кнопку «Обновить».
Поэтому при добавлении новых записей, сводную таблицу следует обновить вручную: выберите ее любую ячейку, на вкладке «Анализ сводной таблицы» в группе «Данные» нажмите кнопку «Обновить».
Изменить стиль сводной таблицы
Внешний вид таблицы можно изменить, чтобы данные были представлены в более наглядной и понятной форме.
Для этого необходимо перейти во вкладку «Конструктор» и в разделе «Стили сводной таблицы» нажать на стрелку, чтобы увидеть весь список доступных стилей. Для большего удобства, при наведении курсора на изображения, таблица временно будет принимать выбранный стиль.
Если ни один из предложенных вариантов дизайна не подошел, Вы можете создать стиль самостоятельно — для этого выберите опцию «Создать стиль сводной таблицы…» в конце списка.
Для очистки созданного или выбранного стиля нажмите «Очистить» в раскрывающемся списке стилей.
Если ни один из предложенных вариантов дизайна не подошел, Вы можете создать стиль самостоятельно — для этого выберите опцию «Создать стиль сводной таблицы…» в конце списка.
Для очистки созданного или выбранного стиля нажмите «Очистить» в раскрывающемся списке стилей.
Поздравляем! Вы научились создавать сводные таблицы и заметно облегчили себе предстоящую работу с данными. Надеемся, что мы развеяли мифы о том, что сводные таблицы — это сложно, и пробудили в Вас желание углубиться в изучении обширного функционала Excel!
