Сводная таблица в Excel. Пошаговая инструкция. Как быстро и качественно анализировать любые данные

Или так. Добавляем поле Торговая сеть в область Строки.

А можно еще и так. Перетаскиваем в области Столбцы ∑ Значения выше поля Вендор и построение отчета меняется.

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

!Материалы к главе: файлы «Многообразие возможностей_исходник» и «Многообразие возможностей»

Делаем сводную таблицу на основе кривой выгрузки из 1С. Сумма по полю

Давайте потренируемся и рассмотрим часто встречающийся пример – обработку отчета или выгрузки из какой-нибудь учетной системы (1С Бухгалтерия, ЗУП, УНФ и т.п.). У них много общего, т.к. при сохранении из учетной системы в формат xls часто имеют ряд схожих моментов, которые затрудняют их обработку.

Итак, есть вот такая выгрузка из 1С:

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

Понять, сколько продано по каждому складу, можно отфильтровав нужный склад. Например, по складу г. Котельники, пр.1-Мега (салон) продано 9 единиц товара на общую сумму 8 991 руб.

Если попытаться создать сводную таблицу на основе неотредактированной выгрузки из 1С (поставить курсор в любую ячейку таблицы, нажать Ctrl+A, а потом во вкладке Вставка выбрать Сводная таблица => ОК), Excel выдаст следующее сообщение

Так что придется немного предварительно немного отредактировать таблицу.

Что тут важно? Во-первых, есть сгруппированные строки (1—2), которые нам вообще не нужны. Во-вторых, есть объединенные столбцы. Например, колонка Склад фактически занимает столбцы A-D. Ну и в-третьих, имеется двухуровневый заголовок: заголовки из колонок Склад, Документ продажи и Итого расположены фактически в 4-й строке, а заголовки Количество товаров и Сумма продаж со скидкой расположены в 5-й строке. Все эти группировки, объединение столбцов и строк, пустые столбцы и тому подобное – типичное явление для выгрузок из большинства учетных систем.

Давайте исправлять.

Первое, что необходимо сделать – отменить объединение на всем листе. Для этого ставим курсор в левом верхнем углу таблицы между столбцом А и строкой 1 как показана на рисунке

Нажимаем левую кнопку мыши, и весь лист становится выделенным. Далее нажимаем на вкладке Главная на стрелочку справа от кнопки Объединение столбцов => Отменить объединение ячеек.

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

То же самое делаем с ненужными пустыми столбцами. Выделяем столбцы B, C и D при помощи зажатой левой кнопки мыши => нажимаем правую кнопку мыши на любом из выделенных столбцов => Удалить.

Осталось разобраться с двухуровневым заголовком.

Что будет, если мы оставим заголовок двухуровневым?

Мы сможем создать только такую сводную таблицу, данные в которой не будут иметь с истинными ничего общего. Почему? Мы же помним, что столбцы в исходной таблице – это поля в нашей сводной, и в качестве заголовка сводная берет только первую верхнюю строку. Здесь наглядно видно, что в исходной таблице для сводной есть только три столбца (те самые, которые идут на верхнем уровне заголовка). И никаких действий, по существу, со значениями производить невозможно, так как выберем мы хоть Сумма по полю Итого, хоть Количество по полю Итого, результат всегда будет одинаково некорректный. Связано это с тем, что в область Значения попадает ячейка со значением Количество товаров, а столбец Сумма продаж со скидкой вообще не доступен для формирования в сводной.

Так что преобразование двухуровневого заголовка в одноуровневый – обязательный этап.

В нашем случае сделать это довольно легко. Выделяем при помощи зажатой левой кнопки мыши значения Склад и Документ продажи и перетаскиваем их на строку ниже

Остается только удалить теперь ненужную нам верхнюю строку и можем приступать к формированию сводной таблицы.