Создание сводных таблиц в Microsoft Excel

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

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

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

Вариант 1: Обычная сводная таблица

Мы рассмотрим процесс создания на примере Microsoft Excel 2010, но алгоритм применим и к другим современным версиям этого приложения.

1. Возьмем за основу таблицу заработной платы сотрудников предприятия. Содержит имена сотрудников, пол, категорию, дату и сумму платежа. То есть каждому эпизоду выплат для одного сотрудника соответствует отдельная строка. Нам нужно сгруппировать хаотично размещенные данные в этой таблице в сводную таблицу, при этом информация будет взята только за третий квартал 2016 года. Давайте посмотрим, как это сделать на конкретном примере.
2. Прежде всего, давайте преобразуем исходную таблицу в динамическую. Это необходимо для того, чтобы при добавлении строк и других данных они автоматически вставлялись в сводную таблицу. Наведите курсор на любую ячейку, затем в блоке «Стили» на ленте нажмите кнопку «Форматировать как таблицу» и выберите предпочитаемый стиль таблицы.
3. Откроется диалоговое окно, предлагающее указать координаты положения стола. Однако по умолчанию координаты, предлагаемые программой, покрывают всю таблицу. Так что нам просто нужно согласиться и нажать «ОК». Но пользователи должны знать, что при желании они могут изменить эти параметры здесь.
4. Таблица становится динамической и автоматически расширяется. Он также получает имя, которое при желании пользователь может изменить на то, что ему подходит. Вы можете просмотреть или изменить название на вкладке «Дизайн».
5. Чтобы начать создание напрямую, выберите вкладку «Вставка». Здесь мы нажимаем на первую кнопку на ленте, которая называется «Сводная таблица». Откроется меню, в котором вы должны выбрать, что мы будем создавать: таблицу или график. Наконец, нажмите «Сводная таблица».
6. В новом окне нам все еще нужно выбрать диапазон или имя таблицы. Как видите, программа уже назвала имя нашей таблицы, так что делать здесь больше нечего. Внизу диалогового окна вы можете выбрать место, где будет создана сводная таблица: на новом листе (по умолчанию) или на том же листе. Конечно, в большинстве случаев гораздо удобнее хранить его на отдельном листе.
7. После этого на новом листе откроется форма для создания сводной таблицы.
8. В правой части окна находится список полей, а ниже четыре области: имена строк, имена столбцов, значения, фильтр отчета. Мы просто перетаскиваем нужные нам поля таблицы в соответствующие области с помощью мыши. Нет четкого правила, какие поля следует переместить, потому что все зависит от исходной таблицы и конкретных действий, которые могут измениться.
9. В данном конкретном случае мы переместили поля «Пол» и «Дата» в область «Отфильтровать отчет», «Категория персонала» — в «Названия столбцов», «Имя» — в «Названия строк», «Сумма заработной платы». — в «Ценности». Следует отметить, что все арифметические вычисления данных, извлеченных из другой таблицы, возможны только в последней области. По мере того как мы производили эти манипуляции путем переноса полей в область, сама таблица в левой части окна соответственно изменялась.
10. Получилась вот такая сводная таблица. Над ним отображаются фильтры по полу и дате.

Вариант 2: Мастер сводных таблиц

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

1. Перейдите в пункт меню «Файл» и нажмите «Параметры».
2. Перейдите в раздел «Панель быстрого доступа» и выберите команды из команд на ленте. В списке элементов найдите «Мастер сводных таблиц и диаграмм». Выберите его, нажмите кнопку «Добавить», затем нажмите «ОК».
3. В результате наших действий на «Панели быстрого доступа» появился новый значок. Щелкаем по нему.
4. Затем открывается «Мастер сводной таблицы». Существует четыре варианта источника данных, из которого будет формироваться сводная таблица, из которых мы указываем соответствующий. Внизу вы должны выбрать, что мы будем создавать: сводную таблицу или диаграмму. Сделаем выбор и перейдем «Далее».
5. Появится окно с диапазоном таблицы с данными, которые вы можете изменить при желании. В этом нет необходимости, поэтому перейдите в «Далее».
6. Затем «Мастер сводных таблиц» предлагает выбрать место, где будет размещен новый объект: на том же листе или на новом. Делаем выбор и подтверждаем его кнопкой «Готово».
7. Это откроет новый лист с точно такой же формой, как и при обычном способе создания сводной таблицы.
8. Все дальнейшие действия выполняются по такому же алгоритму, описанному выше (см. Вариант 1).

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

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

1. Чтобы привести таблицу в нужную форму, нажмите кнопку рядом с фильтром «Дата». В нем установите галочку рядом с надписью «Выбрать несколько элементов». Затем снимите галочки со всех дат, выходящих за пределы периода третьего квартала. В нашем случае это просто свидание. Подтверждаем действие.
2. Точно так же мы можем использовать гендерный фильтр и выбрать, например, только несколько мужчин для отношений.
3. Сводная таблица выглядит так.
4. Чтобы продемонстрировать, что вы можете управлять информацией в таблице, как вам нравится, снова откройте форму списка полей. Перейдите на вкладку «Параметры» и нажмите «Список полей». Переместите поле «Дата» из области «Фильтр отчета» в «Имена строк» ​​и поменяйте местами поля между полями «Категория персонала» и «Пол». Все операции выполняются простым перетаскиванием элементов.
5. Теперь таблица выглядит совсем иначе. Столбцы разделены по полу, строки — по месяцам, и теперь можно фильтровать по категории сотрудников.
6. Если имя строки перемещено в список полей, а дата расположена выше имени, то именно даты платежа будут разделены на имена сотрудников.
7. Вы также можете просмотреть числовые значения таблицы в виде гистограммы. Для этого выберите ячейку с числовым значением, перейдите на вкладку «Главная», нажмите «Условное форматирование», выберите пункт «Гистограммы» и укажите желаемый вид.
8. Гистограмма отображается в одной ячейке. Чтобы применить правило гистограммы ко всем ячейкам в таблице, нажмите кнопку, которая появляется рядом с гистограммой, и в открывшемся окне переместите переключатель в положение «Во все ячейки».
9. В результате наша сводная таблица стала выглядеть более презентабельно.

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