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

Представление об идеальной сводной таблице в Excel у всех разное. 

Однако, есть mast have приемы, которые отличают профессионала от новичка — проверьте, используете ли их вы?

 

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

 

То, за что любят сводные таблицы — их “автоматичность”. Другими словами, достаточно обновить сводную таблицу, чтобы получить полные данные в нужных разрезах.

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

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

идеальная сводная таблица в excel

Добавим данные о еще одной статье затрат (Командировки) и обновим сводную таблицу. Она не изменилась, новые данные в сводную таблицу не попали. Это произошло потому, что диапазон сводной таблицы мы заранее ограничили определенными ячейками.

идеальная сводная таблица в excel

Выхода из данной ситуации два:

  • преобразовать источник в “умную” таблицу (Ctrl + T). В этом случае все добавляемые строки и столбцы будут “автоматом” залетать в сводную таблицу.
  • задавать в качестве диапазона для сводной таблицы не диапазон ячеек, а диапазон столбцов. В этом случае все добавляемые в источник строки будут автоматически попадать в сводную (новые столбцы попадать не будут, придется обновлять источник). Считается, что этот способ нежелателен, т.к. утяжеляет обработку. Однако, из моей практики, это практически не отражается на работе файла. К тому же, “умные” таблицы могут “подвесить” файл не меньше.

Есть еще вариант с динамическими диапазонами, но как правило, двух вышеуказанных достаточно. 

 


   Сообщество Excel Analytics | обучение Excel

    Канал на Яндекс.Дзен 


 

 Сводные таблицы не перекроют друг друга, даже если в исходники добавятся новые данные

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

идеальная сводная таблица в excel

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

Эту особенность необходимо заранее просчитывать, когда создаете несколько сводных таблиц на одном листе.

 

 Форматы числовых данных настроены

 

В данному примере неплохо бы “причесать” числовые данные, настроив форматы.

идеальная сводная таблица в excel

Форматы чисел лучше настраивать не из основного меню, а из меню сводной таблицы: правая кнопка мыши — Параметры полей значенийЧисловой формат. Так форматы гарантированно не слетят при добавлении новых строк и столбцов в сводную таблицу.

идеальная сводная таблица в excel

 

 Для пользовательских дашбордов используете срезы вместо фильтров

 

На самом деле разницы в функционале между фильтрами и срезами нет. И для “внутреннего” использования в промежуточных расчетах фильтры зачастую даже удобнее. 

Однако, в сводных таблицах, которые видят потребители ваших отчетов, лучше использовать срезы

идеальная сводная таблица в excel

Причины:

  • фильтрация срезом происходит быстрее, чем фильтром. Чтобы отфильтровать таблицу фильтром по одному критерию, нужно три раза нажать кнопку, а срезом — один раз.
  • срезы просто красивее. К тому же, их внешний вид можно отдельно настроить под цветовую гамму вашего отчета или дашборда.

 

 Все вычисления — только внутри сводной таблицы

 

Делать дополнительные вычисления с данными сводной таблицы вне диапазона сводной — очень распространенная практика. На рисунке видно, что столбец Доля месяца создан вне сводной таблицы.

идеальная сводная таблица в excel

На самом деле, иногда это даже допустимо, например, если вы точно знаете, что сводная таблица не будет изменяться в размере.

В основном же для дополнительных вычисления лучше использовать возможности вычисляемых полей и вычисляемых объектов, дополнительных вычислений, а также при необходимости применять возможности языка DAX.

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

идеальная сводная таблица в excel

Результат дополнительных вычислений.

идеальная сводная таблица в excel

 

 Сводная таблица отражает не более трех числовых показателей и не более трех категорий данных

 

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

Лучше создать несколько сводных таблиц, объединив их срезами для фильтрации данных.

 

 Сводную таблицу не засоряют ошибки

 

Ошибки вычислений портят внешний вид сводной таблицы.

идеальная сводная таблица в excel

И если у вас не стоит цель подсветить возможные проблемы при помощи ошибок, то лучше не показывать их, заменив на нули или пустое поле. 

И если в обычной таблице убрать ошибки можно при помощи конструкции ЕСЛИОШИБКА, то в сводной нужно щелкнуть правой кнопкой мыши по сводной таблице, выбрать Параметры сводной таблицы и на вкладке Макет и формат установить галочку Для ошибок отображать — и указать значение.

идеальная сводная таблица в excel

 

 Условное форматирование сводной таблицы настроено на все ее данные

 

Это тоже частая ошибка, когда выделяются ячейки сводной таблицы, выбирается некий тип условного форматирование — но не указывается, к каким ячейкам его применять. И при обновлении сводной таблицы такое форматирование чаще всего “слетает”.

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

 

идеальная сводная таблица в excel

 

 Названия столбцов релевантны содержимому

 

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

идеальная сводная таблица в excel

Это же касается и автоматически присвоенных имен для значений, например столбец Сумма по полю Сумма лучше переименовать.

 

идеальная сводная таблица в excel

В этой статье мы рассмотрела основные моменты, на которые нужно обращать внимание при создании сводных таблиц в excel.