Резюме
Чтобы построить двустороннюю сводную таблицу, которая суммирует числовые данные с более чем одним критерием, вы можете использовать функцию СУММЕСЛИМН. В показанном примере формула в H5, скопированная в диапазоне H5: K7, выглядит так:
=SUMIFS(value,name,$G5,stage,H$4)
где значение (C5: C15), имя (B5: B15) и этап (D5: D15) являются именованными диапазонами. Результатом является таблица, в которой показаны итоговые суммы для каждого имени по этапам.
Объяснение
Функция СУММЕСЛИМН предназначена для суммирования числовых значений с использованием нескольких критериев.
В показанном примере данные в диапазоне B5: E15 показывают воронку продаж, где каждая строка представляет собой возможность, принадлежащую продавцу на определенном этапе. Формула в H5:
=SUMIFS(value,name,$G5,stage,H$4)
Первая часть формулы суммирует возможности продавца:
=SUMIFS(value,name,$G5 // sum by name
- Суммарный диапазон - это значения именованного диапазона
- Критерии в диапазоне 1 именованный диапазон имя
- Критерий 1 исходит из ячейки G5
Обратите внимание, что $ G5 - это смешанная ссылка с заблокированным столбцом и относительной строкой. Это позволяет изменять формулу по мере необходимости, когда формула копируется по всей таблице.
Следующая пара диапазон / критерий в СУММЕСЛИМН, суммы по этапам:
stage,H$4 // sum by stage
- Диапазон критериев 2 - это этап именованного диапазона.
- Критерий 2 - 4 H $
Опять же, H $ 4 - это смешанная ссылка с относительным столбцом и заблокированной строкой. Это позволяет критериям подбирать значения этапа в строке 4 по мере того, как формула копируется по таблице и вниз.
С обоими критериями вместе функция СУММЕСЛИМН правильно суммирует возможности по имени и по этапам.
Без имен диапазонов
В этом примере именованные диапазоны используются только для удобства. Без именованных диапазонов эквивалентная формула:
=SUMIFS($C$5:$C$15,$B$5:$B$15,$G5,$D$5:$D$15,H$4)
Обратите внимание, что ссылки на имя, значение и стадию теперь являются абсолютными ссылками, чтобы предотвратить изменения при копировании формулы по таблице и вниз.
Примечание: сводная таблица также может быть отличным способом решения этой проблемы.
