Практическая ситуация: из разных филиалов ежедневно поступают отчеты и их надо объединить в один общий отчет. Отчеты филиалов имеют общий формат.
Например, что-то в это роде
Для получения быстрой аналитики можно воспользоваться сводными таблицами Excel. Чтобы сформировать сводную таблицу в которой будут все три филиала нужно их собрать на одном листе. Для этого создаем новый лист "Свод" и копируем туда данные из листов с отчетами.
Важно!!! Так как эта сводная таблица будет обновляться каждый день, то нужно ее сделать "умной", тогда не нужно будет каждый раз переписывать ссылки в формулах.
Делаем умную таблицу.
Практические советы по работе с экономическими данными в Excel, читайте в моей книге "Excel для экономистов. 10 статей про отчеты, сверки, сопоставления"
Копируем на лист "Свод" таблицу с первым филиалом, выделяем ее, заходим во вкладку "Вставка" и нажимаем кнопку "Таблица"
Ваша таблица примет следующий вид (если такой вид не нравится можно его поменять)
Теперь просто копируем данные из отчетов двух оставшихся филиалов, они тоже примут такой же формат. Кроме того, если ввести в эту таблицу формулу (например, средняя цена, то она будет автоматически копироваться при добавлении новых данных)
Если какой-то диапазон не попал при копировании в умную таблицу, ее границы можно легко расширить, достаточно потянуть за правый крайний угол (там есть небольшой синий значок)
В итоге получается такая умная таблица
На основании этой таблицы можно сделать сводные таблицы, которые позволят получать быструю аналитику. Например, такую
Или такую (все зависит от целей анализа)
Сводные таблицы, конечно, удобны для быстрой аналитики, но работать с ними не всегда удобно: сложно добавлять формулы, строки могут меняться в зависимости от состава данных, поэтому я больше люблю работать с таблицами, в которые данные подтягиваются при помощи формул
При помощи формул мы можем сделать, например, такую таблицу (такой же формат можно сделать и в сводной, кому что нравится)
Вот эта же таблица с формулами
Используется формула СУММЕСЛИМН(Таблица2[Сумма];Таблица2[Менеджер];$B$2;Таблица2[Номенклатура];A3)
Таблица2 - это автоматические название нашей умной таблицы (при необходимости его можно поменять, но я на практике никогда этого не делаю за ненадобностью).
В квадратных скобках [ ] ссылки на столбцы из умной таблицы
B2 и А3 ссылки на критерий поиска: конкретного менеджера (в данном случае Менеджера 1) и конкретную номенклатуру (в данном случае Чайник).
Если ссылку на менеджера закрепить знаками доллара $B$2, то формула легко и правильно скопируется вниз по столбцу.
По строке закрепление долларами не помогает (проверьте сами). В данном примере для копирования по строке я во все столбцы по строке 3 скопировала формулу из ячейки В3 без знака равно, потом в каждой формуле поставила знак равно, изменила ссылку на нужного Менеджера, закрепила ее знаком доллара и скопировала вниз по столбцу. (В сети есть рекомендации по доработке формулы, чтобы ссылка на умную таблицу тянулась и по строке, я не использовала, так как формула и без того длинная)
Подсветка красным - это условное форматирование (максимальные продажи по строке номенклатура)
В этой таблице по полученным данным можно добавлять любые формулы (например, мотивацию, штрафы, доли и прочее).
Если появится новая номенклатура или новый менеджер, то нужно будет заводить новую строку или столбец, автоматически он не добавиться.
Проверим, как работают умные таблицы при вводе новых данных. В умную таблицу добавим продажи пульта по "Менеджеру 1" за 3.02.19 на сумму 2000 руб. (Если вы буде вводить прямо под умной таблицей, ее диапазон автоматически расширится, если не расширится расширьте его сами, как я писала выше)
После этого сводные таблицы нужно обновить, а в отчете с формулами данные поменяются сами
Для более быстрого поиска нужной статьи я сделала подборки:
Бюджетирование
Excel для работы с данными
Себестоимость и цены
Учет закупок и расчетов в Excel
Примеры в Excel и файлы к статьям можно найти по этой ссылке
