Vzorec aplikace Excel: Součet shodných sloupců -

Obecný vzorec

=SUMPRODUCT(data*(headers=A1))

souhrn

Chcete-li součet hodnot ve sloupcích porovnáním odpovídajících záhlaví sloupců, můžete použít vzorec založený na funkci SUMPRODUCT. V zobrazeném příkladu je vzorec v J5:

=SUMPRODUCT(data*(LEFT(headers)=J4))

kde „data“ je pojmenovaný rozsah B5: G14 a „záhlaví“ je pojmenovaný rozsah B4: G4.

Vzorec shrnuje sloupce, kde záhlaví začínají na „a“, a vrací 201.

Vysvětlení

V základu se tento vzorec spoléhá na funkci SUMPRODUCT, aby sečetly hodnoty ve shodných sloupcích v pojmenovaném rozsahu „data“ C5: G14. Pokud by byla všechna data poskytnuta SUMPRODUCT v jednom rozsahu, výsledkem by byl součet všech hodnot v rozsahu:

=SUMPRODUCT(data) // all data, returns 387

Chcete-li použít filtr porovnáním záhlaví sloupců - sloupců se záhlavími začínajícími na „A“ - použijeme funkci LEFT takto:

LEFT(headers)=J4) // must begin with "a"

Tento výraz vrací TRUE, pokud záhlaví sloupce začíná na „a“, a FALSE, pokud ne. Výsledkem je pole:

(TRUE,TRUE,FALSE,FALSE,TRUE,FALSE)

Uvidíte, že hodnoty 1,2 a 5 odpovídají sloupcům začínajícím na „a“.

Uvnitř SUMPRODUCT je toto pole vynásobeno „daty“. Kvůli vysílání je výsledkem dvourozměrné pole, jako je toto:

(8,10,0,0,7,0;9,10,0,0,10,0;8,6,0,0,6,0;7,6,0,0,6,0;8,6,0,0,6,0;10,11,0,0,7,0;7,8,0,0,8,0;2,3,0,0,3,0;3,4,0,0,4,0;7,7,0,0,4,0)

Pokud si toto pole vizualizujeme v tabulce, snadno zjistíme, že operaci přežily pouze hodnoty ve sloupcích začínajících na „a“, všechny ostatní sloupce jsou nulové. Jinými slovy, filtr udržuje hodnoty zájmu a zbytek „ruší“:

A001	A002	A003
8	10	7
9	10	10
8	6	6
7	6	6
8	6	6
10	11	7
7	8	8
2	3	3
3	4	4
7	7	4

S pouze jediným polem ke zpracování vrátí SUMPRODUCT součet všech hodnot 201.

Součet podle přesné shody

Výše uvedený příklad ukazuje, jak sečíst sloupce, které začínají jedním nebo více konkrétními znaky. Chcete-li sečíst sloupec na základě přesné shody, můžete použít jednodušší vzorec, jako je tento: