Термин «Сводная таблица» особо нам ничего не говорит. Простое определение может прозвучать так: Сводная таблица — это отчет, позволяющий просмотреть данные в более удобном и понятном виде. Чаще всего сводные таблицы используются для организации отображения данных из БД, но также есть возможность создавать сводные таблицы и с данных находящихся в книге. Важное требование, это структурно-организованная таблица (по типу реестра), где имеются поля и записи (строки). Например, таблица, перечисляющая сотрудников, имеет поля: Фамилия, Имя, Отчество, Должность, Дата рождения, Зарплата и т.д.
Процесс создания сводной таблицы прост и в тоже время сложен, но когда Вы научитесь их строить — это станет основным Вашим инструментом для представления больших массивов данных. Хочу сразу предупредить, что в письменной форме достаточно сложно объяснить процесс создания, поэтому рекомендую после прочтения ознакомиться с видеодемонстрацией. Также невозможно в одной статье охватить все возможности работы со сводными таблицами, поэтому статья будет не одна.
Для создания первой сводной таблицы возьмем за основу книгу, созданную на уроке «Создание списка выбора в Excel 2003», ссылка здесь.
Открываем книгу на листе «Вариант 1»
Открываем «Данные – Сводная таблица…»
Откроется окно мастера создания сводных таблиц. Ставим все как на снимке:
Жмем «Далее»
В следующем окне необходимо указать диапазон данных, из которых необходимо построить сводную таблицу. С помощью мыши выделяем в таблице «Вариант 1» все строки и столбцы(по умолчанию уже выделено).
В видео-примере я указал диапазон ‘Вариант 1’!$A:$G
Это необходимо в том случае, если таблица постоянно дополняется данными, а постоянно строить таблицу может быть очень затруднительно или просто лень 🙂 Таким образом, я указал диапазон столбцов, но диапазон строк ограничен только возможностями Excel (в 2003 это 65536 строк, в 2007-2010 более 1млн. строк). Но у такого способа есть небольшой недостаток, в таблицах появляется критерий «пусто» (увидите далее). Хотя мне он особо не мешает.
Жмем «Далее>»
На этом шаге указываем, где создать таблицу. Оставляем «новый лист». Так же можно сразу построить макет (на мой взгляд это удобнее делать описанным далее способом, он более наглядный) или задать некоторые параметры таблице. Но все это можно в дальнейшем поправить.
Жмем «Готово».
Мы увидим следующую картину
Это макет нашей таблицы. В левой части должны содержаться критерии, например наименования контрагентов, типы операции и т.д. В верхней части так же текстовые критерии. Разница в том, что левая часть будет отражаться лентой, разбивая данные, а верхняя позволит нам осуществлять выбор критерия, основная (большая) область содержит данные (суммы, кол-во и т.д.). Чуть выше область позволяет разделить эти данные, например, по дате или месяцам и т.д.
Построение таблицы осуществляется путем перетаскивания полей из «Список полей сводной таблицы» в нужные зоны. Перетянем поля в следующие зоны:
Тип операции – тащим в левую зону
Поставщик – так же в левую, но немного правее Типа операции;
Наименование товара тащим в верхнюю зону;
Кол-во и Сумму тащим в самую большую зону поочередно;
Для разбивки по датам перетянем поле Дата в зону чуть выше области данных;
В результате получим такую таблицу:
Получилась слишком переполненная таблица итогами и по полям Сумма и Кол-во считается не сума значений, а их кол-во.
Исправляем.
Для того чтоб изменить вариант расчета, необходимо навести на строку «Количество по полю Кол-во» и «Количество по полю Сумма» указатель мыши таким образом чтобы он принял вид черной стрелки:
Щелкнув один раз левой кнопкой мыши все строки группы «Количество по полю Кол-во» должны выделиться как на снимке выше.
Теперь жмем правой кнопкой мыши и в контексте выбираем пункт «Параметры поля»
В открывшемся окне параметров вычесления выберем «Сумма»
То же самое проделайте и для строк группы «Количество по полю Сумма»/
Теперь скроем излишние строки итогов. Для этого также выделяем группы:
И в контекстном меню выбираем пункт «Скрыть»
В результате должны получить таблицу следующего вида:
Все. Теперь с помощью критериев отбора можно просмотреть различную информацию. Например, посмотрим, кто нам привозит молоко сгущенное, в каком кол-ве и на какую сумму. Для этого щелкнем в поле наименование товара по изображению стрелки и в списке выберем:
Получиться таблица вида:
Изменяя набор и порядок расположения полей, мы можем отобразить и рассчитать данные в любом удобном для нас виде, будь-то данные в разрезе дня, поставщика, наименования, общий итог и т.д. Я понимаю, что тема довольно сложна и описать ее в подробностях очень сложно. Поэтому смотрим для закрепления видео демонстрацию ниже.
На этом пока все. В дальнейшем мы научимся строить таблицы, используя данные из баз данных и на основе сводных таблиц строить диаграммы.
Прикрепленный файл: svodnaya_excel.zip
Видео: Строим сводную таблицу в Excel