Сумма значений из строк, если критерии найдены в горизонтальном диапазоне

Question

У меня есть электронная таблица с несколькими записями строк, содержащими пары country/value (A2:C11) и записями строк, представляющими regions перечисляющие эти countries горизонтали (14:16):

То, что я хотел бы сделать, это суммировать все значения страны для каждого региона:

Концептуально я знаю, что мне нужно делать, например, для Северной Америки (B19):

• фильтр B2: диапазон B11, где B находится в строке 14
• суммируйте значения из C2:11, соответствующие отфильтрованному диапазону B2:B11

Что в этом случае даст 10 (C2) + 11 (C5) + 12 (C10) = 33

Я считаю, что мне нужны функции поиска и справочные функции, но я просто не знаю, с чего начать:

Может кто-нибудь дать мне пример для B19 (или хотя бы список функций, которые я должен использовать)?

PS: я не хочу использовать VBA, просто формулы

Answer 1

Попробуйте эту формулу в B19 скопировать

=SUMPRODUCT(SUMIF(B$2:B$11,INDEX(B$14:C$16,MATCH(A19,A$14:A$16,0),0),C$2:C$11))

• MATCH дает правильную строку в таблице два
• тогда INDEX дает вам все страны в этом ряду.
• Затем часть INDEX/MATCH формирует аргумент критерия для SUMIF , и поскольку критерий представляет собой диапазон значений, SUMIF возвращает массив (сумму для каждой страны отдельно)...... поэтому SUMPRODUCT используется для суммирования массива, избегая "запись массива"

Answer 2

Хорошо, поскольку в каждом регионе более 1 страны, вам понадобится довольно длинная формула. Я предлагаю использовать как SUMIF() и VLOOKUP() .

=SUMIF($B$2:$B$11,VLOOKUP(A19,$A$14:$C$16,2,FALSE),$C$2:$C$11)+
 SUMIF($B$2:$B$11,VLOOKUP(A19,$A$14:$C$16,3,FALSE),$C$2:$C$11)

Первый SUMIF() вычисляет сумму только для Канады, а второй только для США. Добавление двух дает сумму для региона.

VLOOKUP() извлекает название страны из названия региона, вы заметите, что в VLOOKUP() различается, где первый - 2 а второй - 3 (непосредственно перед false). Конечно, вы можете заменить FALSE на 0 чтобы вывести те же результаты.

---

Кроме того, если вы хотите более короткую формулу массива, она также работает:

=SUM(SUMIF($B$2:$B$11,IF(A19=$A$14:$A$16,$B$14:$C$16),$C$2:$C$11))

Так как это формула массива, вам придется использовать Ctrl+Shift+Enter

Answer 3

Вы можете попробовать это:

=SUMPRODUCT($C$2:$C$11,--(IFERROR(MATCH($B$2:$B$11,$B14:$C14,0),0)<>0))

в ячейку B19 и перетащите его вниз. Если у вас более 2 стран на регион, вам нужно соответственно увеличить $B14:$C14 .

Примечание: это формула массива, поэтому вы копируете ее в ячейку и нажимаете Ctrl+Shift+Enter, чтобы она заработала.

• IFERROR(MATCH(...,0)) возвращает массив, элементы которого показывают положение каждой записи первой таблицы в соответствующей строке второй таблицы.
• Логическое условие преобразует все нули в FALSE а все остальные числа в TRUE
• Часть -- преобразует значения TRUE и FALSE в 1 и 0 соответственно
• SUMPRODUCT работает с двумя массивами: первый - это исходный диапазон, а второй - массив 0/1 который охватывает условия, которые мы ввели ранее.

Вот хороший пост, который объясняет логику таких формул:http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html