Автора такой таблицы все устраивает. Ему удобно вводить данные в новый столбец, протянуть формулу, посмотреть на соседнем листе результаты расчета. Но проблема даже не в том, что всем остальным это не очевидно, – такие данные невозможно загрузить в сводные таблицы, на основе которых мы делаем дашборд.
Покажу еще не самый страшный пример – таблицу с данными по расходам на персонал.
● В строках таблицы – статьи расходов и подразделения, а также подытоги по ним. Они выделены цветом, но технически внутри столбца А эти категории неразличимы.
● Правее в столбцах идут месяцы в объединенных ячейках, а под ними – группировка план/факт. То есть мы имеем двухуровневую шапку таблицы.
В такую матричную структуру действительно удобно вносить данные, особенно если нужно добавить цифру в ячейку и забыть о ней. Но мы с вами думаем на несколько шагов вперед: как настроить шаблон отчета, который будет автоматически обновляться при добавлении новых данных?
Решение: исходную матричную таблицу нужно перевести в так называемый плоский формат.
Если вносить данные вручную, это добавит рутинной работы: для каждой строки с числовым показателем нужно повторять признаки категорий (подразделение, статья, месяц). И компактная матрица превратится в сотни и тысячи строк.
Хорошая новость: если вы выгружаете исходные данные из базы, то любая информационная система позволяет сделать это в том самом плоском формате.
Плоская таблица с исходными данными должна содержать:
● строку заголовков с названиями категорий;
● 5 столбцов по количеству категорий;
● неповторяющиеся данные в строках.
В строках не должно быть пустых значений: признак подразделения, статья и месяц должны указываться для каждой строки.
Перевести таблицу с группировкой в плоский формат можно несколькими способами – от ручного копирования данных в нужные ячейки до использования макросов или техник Power Pivot.
Начнем с ручного копирования данных – этот способ потребует немного времени, однако для неподготовленного пользователя он проще остальных.
Сделайте копию листа, перед тем как преобразовывать данные. В случае ошибки вы всегда сможете сверить их с исходным вариантом.
Шаг 1
Столбец А в исходной таблице содержит две категории данных – «Подразделение» и «Статья расхода». В плоской таблице они должны находиться в разных столбцах. Вот как их разделить:
Добавляем новый столбец слева от столбца А. Способ 1, самый простой: выделяем столбец А, вызываем контекстное меню правой кнопкой мыши, выбираем «Вставить». Способ 2: ставим курсор на любую ячейку в столбце А, в меню на вкладке «Главная» выбираем в разделе «Ячейки» кнопку «Вставить…» и в подменю кнопку «Вставить столбцы на лист».
В новый столбец перетаскиваем значения ячеек с названиями подразделений. Для этого выделяем ячейки, подводим курсор к границе этого блока и переносим в новое место.
Заполняем названиями подразделений пустые ячейки нового столбца в строках, где остались названия статей расходов.
Даем столбцам А и B правильные названия в строке над данными – «Подразделение» и «Статья расходов» соответственно. В этой же строке будем указывать заголовки остальных столбцов.
Шаг 2
Теперь из таблицы нужно убрать лишние данные.
Удаляем строки с суммарными значениями, то есть с общими итогами и промежуточными по подразделениям. В противном случае данные останутся суммированными несколько раз и мы получим некорректный результат.
Шаг 3
Добавляем и заполняем столбец с данными по месяцам.
Вставляем новый столбец слева от столбца С со статьями расходов.
Копируем название месяца в первую пустую ячейку нового столбца.