Formuła ogólna
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Podsumowanie
Aby zsumować n dolnych wartości w zakresie kryteriów dopasowania zakresu, można użyć formuły tablicowej opartej na funkcji MAŁE, opakowanej w funkcję SUMA. W ogólnej postaci wzoru (powyżej), zakres1 reprezentuje zakres komórek w porównaniu z kryteriami , zakres2 zawiera wartości liczbowe, z których pobierane są najniższe wartości, a N reprezentuje „n-ty”.
W przykładzie aktywna komórka zawiera następującą formułę:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Gdzie kolor to nazwany zakres B5: B12, a wartość to nazwany zakres C5: C12.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
Wyjaśnienie
W najprostszej formie funkcja SMALL zwraca „N-tą najmniejszą” wartość w zakresie o następującej konstrukcji:
=SMALL (range,N)
Na przykład:
=SMALL (C5:C12,2)
zwróci drugą najmniejszą wartość z zakresu C5: C12, czyli 5 w pokazanym przykładzie.
Jeśli jednak podasz „stałą tablicową” (np. Stałą w postaci (1, 2, 3)) jako SMALL jako drugi argument, funkcja SMALL zwróci tablicę wyników zamiast pojedynczego wyniku. A więc formuła:
=SMALL (C5:C12, (1,2,3))
zwróci pierwszą, drugą i trzecią najmniejszą wartość C5: C12 w tablicy takiej jak ta: (4,5,7).
Tak więc sztuczka polega na filtrowaniu wartości na podstawie koloru przed uruchomieniem SMALL. Robimy to za pomocą wyrażenia opartego na funkcji JEŻELI:
IF(color=E5,value)
To buduje tablicę wartości wprowadzonych do SMALL. Zasadniczo do tablicy trafiają tylko wartości skojarzone z kolorem czerwonym. Gdzie kolor równa się „czerwony”, tablica zawiera liczbę, a jeśli kolor nie jest czerwony, tablica zawiera wartość FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
Funkcja SMALL ignoruje wartości FALSE i zwraca 3 najmniejsze wartości w tablicy: (4,8,10). Funkcja SUMA zwraca wynik końcowy, 22.
