Сводные таблицы в Excel - как сделать?

Сводные (перекрёстные) таблицы – мощный и удобный инструмент анализа и обработки объёмных данных с оперативным представлением результатов в компактной и наглядной форме. Он, к сожалению, незнаком большинству пользователей программы Excel.

Ключевые определения

Название произошло от возможности интерактивного изменения (сведения) областей структуры такой таблицы. В результате создаётся возможность мгновенного изменения отображения анализируемых данных.

Структура сводной таблицы состоит из четырёх областей.

1. Левая со строками, в которых содержатся группируемые данные.
2. Верхняя со столбцами с заголовками.
3. Центральная с рассчитываемыми данными.
4. Необязательная область фильтров для извлечения нужных данных из общего массива.

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

Создание

Сводная таблица создаётся на основе файла с анализируемыми данными. В качестве такого использован Лист1 Excel с некоторыми статистическими данными по российским регионам. Обязательное условие – заголовки у всех столбцов и отсутствие пустых ячеек.

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

1. Щёлкнуть в диапазонах любую ячейку с данными, например, C2.
2. Перейти на вкладку «Вставка», и щёлкнуть инструмент «Сводная таблица».

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

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

(В скобках отметим, что на Панели инструментов появляется две новые вкладки «Анализ» и «Конструктор» под общим названием «Работа со сводными таблицами».)

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

Как видно, поля «Федеральный округ» и «Регион» стали двумя строками сводной таблицы, а остальные три поля появились в области «Значения». Она предназначена для размещения подсчитываемых статистических параметров, таковыми у нас являются «Площадь территории», «Численность населения» и «Среднемесячная заработная плата». Они подсчитываются для полей строк.

Программа Excel поместила в область «Строки» поля «Федеральный округ» и «Регион». Они уже упорядочены в алфавитном порядке по возрастанию. Поля «Площадь территории», «Численность населения», «Численность занятых в экономике» и «Среднемесячная заработная плата» оказались в области «Значения», причём для значений обеих строк по умолчанию было произведено суммирование.

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

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

Варианты отображения

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

Редактировать можно и поля, помещённые в поле «Значения». На следующем скриншоте в контекстном меню поля «Площадь территории» выбрано отображение «С нарастающим итогом».

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

Отображение части данных

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

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

В появившемся окне фильтров ввести и подтвердить налагаемое условие.

Результат представлен на следующем скриншоте.

А теперь подсчитаем среднюю заработную плату в регионах Центрального округа. Для этого сначала нужно выделить список регионов, после чего в контекстном меню, в выпадающем списке «Итоги по», щёлкнуть «Среднее».

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

Ручное перетаскивание поля в нужную область

А теперь перейдём к более радикальному редактированию. Поле «Федеральный округ» перетащим из области строк в область «Фильтры».

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

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

Результат представлен на скриншоте.