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

Вопрос или проблема

У меня следующая структура в Excel:

Лист1 = Отчет
Лист2 = Данные1
Лист3 = Данные2
Лист4 = Данные3
Лист5 = Данные4

Данные в каждом из листов данных (Лист 2 до 5) имеют следующий фиксированный формат с изменяющимися данными:

Sr. | col1 | col2 | col3 | col4

Отчет в следующем формате:

Строки для значений Col4
Столбцы для Данные1,2,3,4…
И ячейки, показывающие количество Sr. из соответствующего листа данных в соответствии со значениями столбца и строки. (Это выполняется с помощью функции COUNTIFS на данный момент)

Что я хочу достичь:
Я хочу иметь возможность установить фильтр уникальных значений для Col1, Col2 и Col3 отдельно. То есть три разных ячейки фильтра. В этих ячейках будет раскрывающийся список проверки данных. Если пользователь выбирает значение, отчет должен показывать количество Sr, учитывая этот фильтр в соответствующем листе данных (каждой колонке в отчете). И если ничего не выбрано, игнорировать этот столбец и выбрать все данные для подсчета St.

Это похоже на наличие фильтра отчета сводной таблицы для этого значения данных.

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

Поэтому мне нужно вручную создать отчет с использованием формулы.

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

Есть ли какое-либо другое решение для достижения этого?

Вы вероятно можете достичь этого с помощью PIVOTBY или GROUPBY, но поскольку ваши точные требования неясны, я рекомендую объединить листы данных в один лист ‘Data Consolidated’:

=VSTACK(Данные1!A1:E100, Данные2!A2:E100, ..., Данные4!A2:E100)

Где нижняя правая ячейка (показана как E100 выше) потенциально различается для каждого листа данных.

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

Ответ или решение

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

Теоретическая часть. В стандартных сводных таблицах Excel предлагается возможность использования фильтров для различных значений, однако это решение становится сложным, если количество таблиц многократно увеличивается. Когда вы работаете одновременно с несколькими листами данных и не можете объединить их в одну таблицу, использование формул Excel дает гораздо более гибкие решения, хотя и требует немного большего уровня подготовки. Основными функциями, которые могут быть полезны для реализации такого фильтра, включают COUNTIFS для подсчета значений с несколькими условиями, а также использование динамических ссылок и формул массива для применения фильтров внутри таблиц.

Пример. Предположим, что у нас есть листы с идентичными заголовками и данные рассредоточены по нескольким листам: Data1, Data2, Data3 и Data4. Ваш отчет на другом листе сравнивает данные из всех источников. Если пользователь хочет видеть только те данные, которые соответствуют определенным условиям из Col1, Col2 и Col3, располагайте фильтры для каждого столбца с помощью выпадающих списков, настроенных с помощью функции "Проверка данных".

Применение. 1. Создайте выпадающие списки. На листе отчета создайте три ячейки (например, G1, H1, I1), которые будут представлять фильтры для Col1, Col2 и Col3. Используйте "Проверка данных" для настройки выпадающих списков, основанных на уникальных значениях из соответствующих столбцов.

Подготовьте данные для фильтрации. Например, если ваши данные на листе Data1 находятся в диапазоне A2:E100, указывайте формулы массива, чтобы получить все уникальные значения из каждого столбца данных.
```
=UNIQUE(Data1!B2:B100)  // для получения уникальных значений Col1
=UNIQUE(Data1!C2:C100)  // для Col2
=UNIQUE(Data1!D2:D100)  // для Col3
```
Используйте COUNTIFS для фильтрации и подсчёта. Основной функцией, которую вы будете использовать, будет COUNTIFS. Однако, чтобы избежать сложности в формуле, можно создавать условные конструкции, которые проверяют, был ли фильтр применен, и используются формулы в логических проверках:
```
=COUNTIFS(
  INDIRECT("'" & G$1 & "'!B2:B100"), IF(ISBLANK($G$1), "*", $G$1),
  INDIRECT("'" & G$1 & "'!C2:C100"), IF(ISBLANK($H$1), "*", $H$1),
  INDIRECT("'" & G$1 & "'!D2:D100"), IF(ISBLANK($I$1), "*", $I$1)
)
```
Здесь используется INDIRECT для динамической ссылки на разные ячейки и данные. Логическая проверка ISBLANK определяет, является ли текущий фильтр пустым. Если пуст, функция COUNTIFS учитывает все значения, используя символ подстановки "*".
Экстремальная автоматизация. Для более сложного анализа данных с применением фильтрации, рекомендуется изучить и применить функции фильтрации, такие как FILTER. Они могут эффективно работать с относительными и абсолютными диапазонами данных.
Объединение данных вручную. Альтернативным решением (более трудоемким) является создание нового листа, например, Data Consolidated, где данные из всех ваших листов скомпонованы в один с помощью VSTACK или аналогичным способом. После этого построение и фильтрование из одной таблицы значительно упрощается.

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