Formuła programu Excel: Suma pasujących kolumn -

Formuła ogólna

=SUMPRODUCT(data*(headers=A1))

Podsumowanie

Aby zsumować wartości w kolumnach przez dopasowanie pasujących nagłówków kolumn, możesz użyć formuły opartej na funkcji SUMPRODUCT. W przedstawionym przykładzie formuła w J5 to:

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

gdzie „dane” to nazwany zakres B5: G14, a „nagłówki” to nazwany zakres B4: G4.

Formuła sumuje kolumny, w których nagłówki zaczynają się od „a”, i zwraca 201.

Wyjaśnienie

Zasadniczo ta formuła opiera się na funkcji SUMPRODUCT do sumowania wartości w pasujących kolumnach w nazwanym zakresie „dane” C5: G14. Gdyby wszystkie dane zostały dostarczone do SUMPRODUCT w jednym zakresie, wynikiem byłaby suma wszystkich wartości w zakresie:

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

Aby zastosować filtr, dopasowując nagłówki kolumn - kolumny z nagłówkami zaczynającymi się od „A” - używamy funkcji LEFT w następujący sposób:

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

To wyrażenie zwraca TRUE, jeśli nagłówek kolumny zaczyna się od „a”, lub FALSE, jeśli nie. Wynikiem jest tablica:

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

Jak widać, wartości 1, 2 i 5 odpowiadają kolumnom zaczynającym się od „a”.

Wewnątrz SUMPRODUCT ta tablica jest mnożona przez „dane”. Ze względu na rozgłaszanie wynikiem jest dwuwymiarowa tablica, taka jak ta:

(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)

Jeśli wizualizujemy tę tablicę w tabeli, łatwo zauważyć, że tylko wartości w kolumnach zaczynających się od „a” przetrwały operację, a wszystkie inne kolumny są zerowe. Innymi słowy, filtr zachowuje interesujące wartości, a resztę „anuluje”:

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

Mając tylko jedną tablicę do przetworzenia, SUMPRODUCT zwraca sumę wszystkich wartości, 201.

Suma według dopasowania ścisłego

Powyższy przykład pokazuje, jak zsumować kolumny, które zaczynają się od jednego lub kilku określonych znaków. Aby podsumować kolumnę na podstawie dokładnego dopasowania, możesz użyć prostszej formuły, takiej jak ta: