Читать книгу Сводная таблица в Excel. Пошаговая инструкция. Как быстро и качественно анализировать любые данные - Ар'лан ис'Дрекхэм - Страница 9

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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