СВОДНЫЕ ТАБЛИЦЫ. ПОЧЕМУ В РЕШЕНИЯХ EXCELSOFT ОНИ ПОЧТИ НЕ ИСПОЛЬЗУЮТСЯ

Сводные таблицы Excel - отличный инструмент для анализа данных. Они позволяют быстро и просто обрабатывать большие массивы однородных данных и представить результаты в различных разрезах и форматах.

Конечно, каждый инструмент предназначен для своих задач. Например, сводные таблицы - идеальны для анализа данных о продажах. Не случайно по-моему 99% примеров применения сводных таблиц - это именно об этом. 

 Но вот делать на базе сводных сложные отчеты типа БДР, или управленческий баланс на мой взгляд нецелесообразно. Слишком много у них недостатков. Вот основные:   

1. Критичность к исходным данным

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

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

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

2. В сводной таблице очень ограниченные возможности при создании формул в вычисляемых полях и объектах

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

Поэтому если вам нужно рассчитать что-то, чего изначально нет в выгруженных данных – придется делать вычисляемые столбцы в исходных данных и уже готовые результаты расчетов «тащить «в сводную. 

3. Исходные данные для сводных таблиц должны иметь заголовок в первой строке данных. Вы не можете делать сложные многоуровневые шапки таблиц. 

4. Сводная таблица имеет довольно убогий внешний вид. 

Конечно, можно настроить форматы чисел, ширины столбцов. Но при обновлении сводной таблицы все ваши настройки слетят. 

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

Конечно проблема решается созданием  таблиц-оболочек в которые с помощью функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ будут подтягиваться данные из сводных таблиц. Но тогда пропадают основные достоинства сводной - простота и быстрота.  

5. Для большинства отчетов придется строить сложную модель данных.

Быстро и просто сводная таблица строится для данных, находящихся на одном листе, например данные о продажах. Но, например, в программе ES: Финансовое планирование и учет  управленческий баланс в формате план-факт собирается на основании данных 6 листов. Для сборки такой сводной надо строить модель данных c использованием надстройки Power Pivot. А это уже не совсем простая задача и не за пару минут. 

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

Но основная причина, по которой я нечасто использую сводные таблицы в программах ExcelSoft в том, что строгие требования качеству и к структуре представления исходных данных повышают трудоемкость их ввода в разы и делают невозможным использование некоторых юзабилити-«фишек» программ ExcelSoft.

1. Исходные данные для сводных таблиц не должны содержать пустых строк. Но работать со сплошным массивом цифр визуально очень тяжело. Поэтому в программах ExcelSoft можно визуально разделять данные строками с произвольными текстовыми данными, оставлять пустые строки. Работоспособность программы от этого никак не пострадает. 
2. В исходных данных для сводной не должно быть пустых ячеек, иначе можно получить некорректный результат. Но тогда пользователю придется аккуратно заполнять все ячейки таблицы. 
3. Программы ExcelSoft стремятся обеспечить пользователям минимальную трудоемкость при вводе данных. Там, где можно обойтись без ввода данных – обходимся. 
4. Для сводных все данные в одной колонке должны быть одного типа. Программы ExceSoft позволяют в одной колонке использовать и текстовые и числовые значение. Иногда так надо. Например, в журналах учета в колонке Дата можно вводит названия закладок для быстрого перемещения по таблице. 
5. Для создания сводной таблицы требуется, чтобы в первой строке исходных данных были заголовки столбцов. В программах ExcelSoft первой строкой исходных данных может быть строка суперфильтра (инструмент, который позволяет быстро фильтровать данные), строка для расчета промежуточных итогов или строка с номерами столбцов (для поиска ошибок). На рисунке ниже представлена реальная таблица для планирования доходов и расходов из которой потом формулами массива собирается плановый бюджет доходов и расходов. Здесь есть все вышеперечисленные примеры вольного заполнения данных. 

В таблице имеются: 

• визуальные разделители (пустые строки);
• закладки с помощью которых можно быстро перемещаться по таблице, например "Транспортные 2019", "Коммунальные 2019" в колонке Дата;
• данные разных типов в одной колонке (например колонка НДС,%);
• строка суперфильтра (оранжевая строка);
• пустые ячейки в табличной части;

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

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

Так стоит ли овчинка выделки? 

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

Предвижу возражения, что формулы массива «тормозят» даже при относительно небольших объемах данных. Эта проблема решаема. По мере готовности, я выложу на сайте много приемов и трюков, которые в значительной степени решают эту проблему производительности Excel.