Представление об идеальной сводной таблице в Excel у всех разное.
Однако, есть mast have приемы, которые отличают профессионала от новичка — проверьте, используете ли их вы?
Как построить сводную таблицу в Excel
Область для построение сводной таблицы не нужно обновлять при добавлении строк в исходную таблицу
То, за что любят сводные таблицы — их “автоматичность”. Другими словами, достаточно обновить сводную таблицу, чтобы получить полные данные в нужных разрезах.
Однако, если при создании сводной таблицы указать только тот диапазон ячеек, в котором есть данные на момент создания таблицы, то при добавлении данных в исходную таблицу автоматически они в сводной таблице не появятся.
Пример, на картинке сводная таблица создана на основе имеющихся сейчас данных.
Добавим данные о еще одной статье затрат (Командировки) и обновим сводную таблицу. Она не изменилась, новые данные в сводную таблицу не попали. Это произошло потому, что диапазон сводной таблицы мы заранее ограничили определенными ячейками.
Выхода из данной ситуации два:
- преобразовать источник в “умную” таблицу (Ctrl + T). В этом случае все добавляемые строки и столбцы будут “автоматом” залетать в сводную таблицу.
- задавать в качестве диапазона для сводной таблицы не диапазон ячеек, а диапазон столбцов. В этом случае все добавляемые в источник строки будут автоматически попадать в сводную (новые столбцы попадать не будут, придется обновлять источник). Считается, что этот способ нежелателен, т.к. утяжеляет обработку. Однако, из моей практики, это практически не отражается на работе файла. К тому же, “умные” таблицы могут “подвесить” файл не меньше.
Есть еще вариант с динамическими диапазонами, но как правило, двух вышеуказанных достаточно.
Статья опубликована на моем сайте https://excel-analytics.ru/
Сообщество ВКонтакте
Сводные таблицы не перекроют друг друга, даже если в исходники добавятся новые данные
Если на листе несколько сводных таблиц, то может возникнуть ситуация, когда при добавлении данных в таблицу-источник возникнет такая ошибка:
Ошибка возникла из-за того, что в таблицу-источник были добавлены две новые статьи затрат. А так как нижняя таблица расположена очень близко к верхней, у верхней таблицы просто нет возможности добавить две новые строки.
Эту особенность необходимо заранее просчитывать, когда создаете несколько сводных таблиц на одном листе.
Форматы числовых данных настроены
В данному примере неплохо бы “причесать” числовые данные, настроив форматы.
Форматы чисел лучше настраивать не из основного меню, а из меню сводной таблицы: правая кнопка мыши — Параметры полей значений — Числовой формат. Так форматы гарантированно не слетят при добавлении новых строк и столбцов в сводную таблицу.
Для пользовательских дашбордов используете срезы вместо фильтров
На самом деле разницы в функционале между фильтрами и срезами нет. И для “внутреннего” использования в промежуточных расчетах фильтры зачастую даже удобнее.
Однако, в сводных таблицах, которые видят потребители ваших отчетов, лучше использовать срезы.
Причины:
- фильтрация срезом происходит быстрее, чем фильтром. Чтобы отфильтровать таблицу фильтром по одному критерию, нужно три раза нажать кнопку, а срезом — один раз.
- срезы просто красивее. К тому же, их внешний вид можно отдельно настроить под цветовую гамму вашего отчета или дашборда.
Все вычисления — только внутри сводной таблицы
Делать дополнительные вычисления с данными сводной таблицы вне диапазона сводной — очень распространенная практика. На рисунке видно, что столбец Доля месяца создан вне сводной таблицы.
На самом деле, иногда это даже допустимо, например, если вы точно знаете, что сводная таблица не будет изменяться в размере.
В основном же для дополнительных вычисления лучше использовать возможности вычисляемых полей и вычисляемых объектов, дополнительных вычислений, а также при необходимости применять возможности языка DAX.
Для данного примера задачу вычислить долю месяца можно решить применением дополнительных вычислений. Просто добавим в Значения поле Сумма еще раз и применим к нем Дополнительные вычисления.
Результат дополнительных вычислений.
Сводная таблица отражает не более трех числовых показателей и не более трех категорий данных
На самом деле, число три тут условно, но попытки запихнуть в одну сводную все возможные значения и разрезы данных приводит к том, что таблица “не читается”.
Лучше создать несколько сводных таблиц, объединив их срезами для фильтрации данных.
Сводную таблицу не засоряют ошибки
Ошибки вычислений портят внешний вид сводной таблицы.
И если у вас не стоит цель подсветить возможные проблемы при помощи ошибок, то лучше не показывать их, заменив на нули или пустое поле.
И если в обычной таблице убрать ошибки можно при помощи конструкции ЕСЛИОШИБКА, то в сводной нужно щелкнуть правой кнопкой мыши по сводной таблице, выбрать Параметры сводной таблицы и на вкладке Макет и формат установить галочку Для ошибок отображать — и указать значение.
Условное форматирование сводной таблицы настроено на все ее данные
Это тоже частая ошибка, когда выделяются ячейки сводной таблицы, выбирается некий тип условного форматирование — но не указывается, к каким ячейкам его применять. И при обновлении сводной таблицы такое форматирование чаще всего “слетает”.
После того, как вы выбрали условное форматирование, нужно щелкнуть по значку в правом нижнем углу выделение и выбрать нижний или средний переключатель. Так форматирование гарантированно не слетит при обновлении сводной таблицы и будет распространяться на все указанные данные.
Названия столбцов релевантны содержимому
При создании сводной таблицы, если в строках только элемент (в нашем примере это месяц), столбцу автоматически присваивается имя Название строк. Лучше его поменять на релевантное.
Это же касается и автоматически присвоенных имен для значений, например столбец Сумма по полю Сумма лучше переименовать.
В этой статье мы рассмотрела основные моменты, на которые нужно обращать внимание при создании сводных таблиц в excel.
