Вопрос или проблема
У меня есть рабочая книга с несколькими листами. На каждом листе вы можете выбрать пользователя из выпадающего списка и затем выбрать элемент из другого выпадающего списка. На любом листе может быть несколько случаев выбора пользователя и элемента.
Мне нужно иметь сводный лист, где вы можете выбрать пользователя из выпадающего списка, и Excel отобразит суммарное количество для каждого элемента на листах.
Я могу собирать одиночные результаты, используя следующую формулу:
=COUNTIFS('15'!$C:$C, E4, '15'!$D:$D, E5)
Мои листы озаглавлены с 1 до 31.
Чтобы использовать COUNTIFS на нескольких листах с одинаковой структурой столбцов, объедините его с SUMPRODUCT и INDIRECT.
Сначала перечислите все имена листов в столбце A вашего сводного листа, которые вы хотите включить в функцию COUNTIFS. Например, поскольку ваши рабочие листы названы с 1 до 31, введите “Список листов” в ячейку A1, затем введите значения с 1 по 31 в ячейки A2 по A32.
Пример COUNTIFS, который вы предоставили, теперь можно изменить, чтобы включить все рабочие листы, перечисленные в диапазоне A2:A32 следующим образом:
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&A2:A32&"'!C2:C1000"), E4, INDIRECT("'"&A2:A32&"'!D2:D1000"), E5))
ПРИМЕЧАНИЕ: так как эта формула вычисляется на 31 листе, избегайте использования ссылок на весь столбец, таких как “C:C” или “D:D”; вместо этого выберите разумное количество строк, которое достаточно далеко расширяется ниже ваших данных, но не настолько далеко, чтобы замедлить производительность. В этом примере я использовал “C2:C1000” и “D2:D1000”.
Чтобы достичь окончательного сводного листа, как показано на предоставленном скриншоте, используйте правильное сочетание абсолютных и относительных ссылок на диапазоны, чтобы иметь возможность копировать формулу по всем строкам и столбцам сводной области. Например, с листом списка в диапазоне A2:A32, списком пользователей в диапазоне C2:C3 и списком элементов в диапазоне D1:F1, используйте следующую формулу в ячейке D2, затем скопируйте её по и вниз, чтобы завершить отчет:
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$2:$A$32&"'!C2:C1000"), $C2, INDIRECT("'"&$A$2:$A$32&"'!D2:D1000"), D$1))
Окончательные результаты должны выглядеть примерно так:
Ответ или решение
Чтобы аккумулировать общее количество элементов по всем листам в Excel при использовании нескольких выпадающих списков, можно воспользоваться формулой, которая объединяет возможности функций COUNTIFS, SUMPRODUCT и INDIRECT. Давайте подробно рассмотрим, как это реализовать в вашем случае, при условии, что листы пронумерованы от 1 до 31 и выбрана структура данных, как описано в вашем вопросе.
Шаги для создания сводного листа:
-
Подготовьте список листов:
- На вашем сводном листе, введите в колонку A названия всех листов, которые вы хотите учитывать. Например, начиная с ячейки A2 и до A32, введите числа от 1 до 31, представляющие имена ваших листов.
-
Настройте выпадающие списки:
- Убедитесь, что у вас уже есть выпадающий список пользователей, от которых вы хотите получить консолидацию данных, и другой список для элементов.
-
Используйте формулу:
- Для получения суммы occurrences выбранного пользователя и элемента по всем листам, используйте следующую формулу в ячейке нужного диапазона сводного листа. Допустим, вы хотите разместить итоговые данные, начиная с ячейки D2, и у вас:
- $C$2: Пользователь выбранного выпадающего списка.
- D$1: Элемент выбранного выпадающего списка.
Формула:
=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$2:$A$32&"'!C2:C1000"), $C2, INDIRECT("'"&$A$2:$A$32&"'!D2:D1000"), D$1)) - Для получения суммы occurrences выбранного пользователя и элемента по всем листам, используйте следующую формулу в ячейке нужного диапазона сводного листа. Допустим, вы хотите разместить итоговые данные, начиная с ячейки D2, и у вас:
Дополнительные советы:
-
Оптимизация диапазонов: Избегайте использования полного диапазона столбцов (напр., C:C, D:D), так как это может замедлить производительность. Вместо этого используйте обоснованные диапазоны, как, например, C2:C1000 и D2:D1000, чтобы охватить все ваши данные.
-
Настройка ячеек: Обратите внимание на применение абсолютных и относительных ссылок (например, $C2 для пользовательского ряда), чтобы формула могла быть скопирована через весь диапазон без изменения требований.
-
Проверка и тестирование: Убедитесь, что формула возвращает правильные результаты, проверяя их на небольшом наборе данных, прежде чем применять к большему объему информации.
С помощью этой настройки, вы получите динамический сводный отчет, который будет автоматически обновляться при изменении данных на отдельных листах или при изменении выбора в выпадающих списках на сводном листе. Эта стратегия, основанная на использовании комбинации INDIRECT и SUMPRODUCT, предоставляет мощный и гибкий инструмент для управления данными в многолистовом формате Excel.