Как создать фильтр отчетов в Excel с помощью формулы для стабильного сводного анализа

Интеграция данных в Excel: эффективное создание отчетов с фильтрами

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

Структура рабочих листов

Для начала у нас есть структура Excel с несколькими листами:

• Sheet1: Отчет
• Sheet2: Data1
• Sheet3: Data2
• Sheet4: Data3
• Sheet5: Data4

На листах данных (Data1–Data4) информация представлена в виде таблицы:

Sr. | col1 | col2 | col3 | col4

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

Цели отчетности

Цель состоит в том, чтобы реализовать уникальные фильтры для значений col1, col2 и col3. Пользователь должен иметь возможность выбирать значения из этих столбцов. Если значение выбрано, отчет будет отображать количество Sr. с учетом этого фильтра. Если ничего не выбрано, отчет должен игнорировать соответствующий фильтр и выводить все данные.

Проблема: использование функции COUNTIFS

Сложность возникает при использовании функции COUNTIFS, так как количество условий может усложнить формулы в отчетах. Тем не менее, существуют альтернативные методы для решения этой задачи.

Решение: использование объединения данных

Если у вас есть доступ к Microsoft 365, можно объединить данные из нескольких листов в один "Консолидированный лист" с помощью функции VSTACK, что позволит работать с одним набором данных:

=VSTACK(Data1!A1:E100, Data2!A2:E100, ..., Data4!A2:E100)

Это создаст единый источник данных, что упростит анализ и создание отчетов.

Более сложные формулы для динамических фильтров

Для создания динамических отчетов мы можем использовать более сложные формулы. Например, вы можете использовать следующую формулу:

=REDUCE("Data"&{1,2,3,4}, A7:A10,
 LAMBDA(a,b,
        VSTACK(a,
               BYCOL(
                     (HSTACK(Data1:Data4!E2:E99)=b)*
                     IF(B1="",
                        1,
                        HSTACK(Data1:Data4!B2:B99)=B1)*
                     IF(B2="",
                        1,
                        HSTACK(Data1:Data4!C2:C99)=B2)*
                     IF(B3="",
                        1,
                        HSTACK(Data1:Data4!D2:D99)=B3),
                     SUM))))

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

Заключение

Создание динамического отчета в Excel с использованием данных из нескольких листов — это эффективный подход для анализа. Несмотря на сложности с формулами и функциями фильтрации, подход с объединением данных и использованию функций Microsoft 365 предоставляет мощные инструменты для работы с данными. Применяйте эти методы, чтобы создать интуитивно понятные и функциональные отчеты, которые будут полезны в бизнесе или научной деятельности.

Источник