Привет! На связи Ленивый аналитик, и сегодня 3-я серия нашего сериала про то, как можно упростить свою работу в Excel.
В этой части хочу обсудить работу со сводными таблицами.
Сводные - мощный инструмент, без которого не может быть настоящей работы в Excel, т.к. круто структурируют и агрегируют данные "в автомате".
Сводные таблицы облегчают жизнь пользователя. Но могут и усложнить.
Давай проверим, не усложняешь ли ты сам себе жизнь при работе со сводными таблицами в Excel?
плохая идея - Создавать сводную таблицу из диапазона ячеек, а не столбцов.
Часто вижу сводные таблицы, у которых диапазон-источник выглядит так:
Это фиксированный диапазон ячеек от В1 до Е2585.
В таких случаях мне всегда хочется спросить - зачем так делать? Ведь если в источник добавятся данные ниже строки 2585 (а они добавятся, если это регулярно обновляемый файл!), то тут два варианта развития событий:
- эти данные не попадут в сводную, если вы не вспомнили, что нужно изменить диапазон источника
- если вы все же вспомнили об изменении диапазона - вам нужно вручную зайти и изменить его. Каждый раз при добавлении данных! Вот не лень же 😀
И если таких таблиц у вас много, то печаль...
Чтобы не усложнять себе жизнь, просто при создании сводной таблицы используйте диапазон столбцов.
Я слышала мнение пользователей, что такой подход может увеличить размер файла, но из опыта - на размер файла это не влияет.
Также иногда такой подход объясняется тем, что под источником будут располагаться другие данные, которые могут нечаянно попасть в сводную.
Отсюда вытекает следующая
плохая идея - Размещать источники для сводных таблиц один под другим
Как на картинке.
И если в первом случае, слева, проблемы возникнуть как будто не должно (табличка с днями недели не должна увеличиться, т.к. количество дней недели фиксированное), то правая верхняя табличка можно вырасти вниз. И придется сдвигать таблицу под ней. А если она является источником, то поедут все ссылки...
Поэтому стараемся не размещать таблицы-источники для сводных друг под другом. Исключением могут быть только случаи строгой фиксированности и небольшой величины таблиц.
плохая идея - Ссылаться на ячейки сводной таблицы при расчетах формулами
А самое зло - это ссылаться на итоги таблицы, например, при подсчете доли какого-то значения от итога.
Потому что у вас обязательно добавится какая-то строчка при обновлении сводной (даже если вы свято верите, что нет, она добавится... 😉поверьте опыту, это всегда случается, когда не ждешь). И расчеты поедут нафиг...
(на самом деле, так можно - но только для разового вычисления, когда вы получили результат и закрыли файл без сохранения)
И если вы это вовремя заметите - будете исправлять вручную (а мы помним, что рукоделия в Excel это всегда плохо!). А если не увидите заранее - вот тут беда... А таких таблиц может быть ой как много...
Чтобы этого избежать, используйте Дополнительные вычисления
В данном случае столбец Фактическая выручка с НДС можно еще раз добавить в Значения
Затем щелкаем правой кнопкой мыши по второму столбцу с выручкой и выбираем Дополнительные вычисления - % от суммы по столбцу
И получаем доли значений по каждой строке.
Теперь не страшно, если добавится какая-то строка - эти доли пересчитаются, и никакие формулы не съедут.
* Еще эта проблема решается при помощи Getpivotdata, но на практике я только пару раз видела, чтобы ее правильно использовали (и вообще, все же ее отключают? 😀)
еще одна плохая идея - Выделять заливкой ячейки в сводной таблице
Ну как плохая. Иногда можно, если таблица статичная и не будет обновляться.
Потому что ваше выделение может съехать, если изменится размер сводной таблицы. А может и не съехать. Надеяться на такой способ крайне наивно.
Поэтому стараемся все акценты в виде заливок ячеек делать при помощи условного форматирования. Его можно применять как к числовым значениям, так и к строкам и столбцам.
Условное форматирование в сводных таблицах имеет некоторые особенности, которые описаны в статье на моем сайте.
Так, с плохими идеями понятно, теперь перейдем к бэст практис.
хорошая идея - Создавать сводную таблицу из смарт-таблицы
Еще их называют умными таблицами или просто таблицами.
Это вот такие полосатые таблицы, которые многие считают просто красивым форматированием.
На самом деле, смарт-таблицы имеют ряд преимуществ перед обычными диапазонами, но это тема отдельной статьи.
Одно из преимуществ смарт-таблицы - из них удобно создавать сводные таблицы. При изменении размера таблицы источника - вниз или в ширину - не нужно менять диапазон-источник для сводной таблицы. Он изменится автоматически при обновлении сводной, т.к. источником являются не определенные столбцы или диапазон, а вся таблица.
Чтобы превратить обычный диапазон в смарт-таблицу, достаточно щелкнуть в любой ячейке диапазона и нажать Ctrl+T, установить галку Таблица с заголовками и нажать Ок.
И чтобы создать из смарт-таблицы сводную таблицу - не нужно ее целиком выделять! Достаточно установить курсор на любую ячейку сводной таблицы - и выбрать Вставка - Сводная таблица.
хорошая идея - Заранее предусмотреть увеличение размера сводной таблицы
Предположим, у вас есть отчет, построенный при помощи сводных таблиц.
Отчет регулярно обновляется в течение года - это значит, что количество месяцев будет увеличиваться. И если правой таблице есть куда расширяться, то левая таблица через 2 месяца упрется в правую, и при обновлении вы получите ошибку:
Самое обидное, что в таком случае не обновится не только та таблица, которая "не уместилась", а вообще все сводные в файле.
А самое неприятное - это когда сводных таблиц в файле много и часть из них - на скрытых листах или где-то еще запрятана, и ищи по всему файлу, какая таблица выдает такую ошибку.
Поэтому, создавая отчет, заранее продумывайте его структуру, предполагаемый размер сводных таблиц и оставляйте для них место с запасом.
хорошая идея - Устанавливать формат ячеек и условное форматирование через настройку
Как мы задаем формат значений в ячейках или условное форматирование для обычного диапазона? Правильно, выделяем нужные ячейки и переходим в настройки форматов.
Для сводных таблиц лучше использовать другой способ.
1. Чтобы настроить формат значений, нужно щёлкнуть правой кнопкой мыши на значении и выбрать Параметры полей значений.
Затем кнопку Числовой формат и далее установить формат.
2. Чтобы настроить условное форматирование, нужно щелкнуть на любой ячейке значений (можно на одной, не обязательно выбирать все) и выбрать нужный тип условного форматирования, например, цветовую шкалу.
Возле ячейки появится вот такой значок, нажав на который, нужно выбрать второй или третий переключатель.
Отличие их в том, что в случае выбора второго переключателя форматироваться будут также итоги.
Надеюсь, что статья была полезна и советы из нее хоть немного облегчат вашу эксельную рутину.
Подписывайтесь на мой телеграмм канал, там будут не только напоминалки о новых статьях, но и другие полезности.