Formuła ogólna
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Podsumowanie
Aby zsumować najniższe n wartości w zakresie, można użyć formuły opartej na funkcji MAŁE i SUMA PROCEDURY. W ogólnej postaci wzoru (powyżej) rng reprezentuje zakres komórek zawierających wartości liczbowe, a n oznacza liczbę najniższych wartości do zsumowania. W pokazanym przykładzie E5 zawiera następującą formułę:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
która zwraca sumę trzech najmniejszych wartości w B5: B14, 60
Wyjaśnienie
W najprostszej formie SMALL zwróci „n-tą najmniejszą” wartość z zakresu. Na przykład:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
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. Na przykład:
=SMALL(A1:A10,(1,2,3))
zwróci pierwszą, drugą i trzecią najmniejszą wartość z zakresu A1: A10.
Działając od wewnątrz na zewnątrz w pokazanym przykładzie, SMALL zwraca 3 najmniejsze wartości z zakresu B5: B14:
=SMALL(B4:B14,(1,2,3))
Rezultatem jest tablica taka:
(10,20,30)
Ta tablica jest zwracana bezpośrednio do funkcji SUMPRODUCT, która sumuje liczby i zwraca sumę:
SUMPRODUCT((10,20,30)) // returns 60
Formuła tablicowa z SUMĄ
SUMPRODUCT jest często używany jak powyżej, ponieważ może obsługiwać tablice natywnie bez wprowadzania ich jako formuły tablicowej. Możesz jednak również napisać formułę tablicową z funkcją SUMA w następujący sposób:
(=SUM(SMALL(B4:B13,(1,2,3))))
To jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter, z wyjątkiem Excel 365.
Kiedy n staje się duże
Gdy n staje się duże, ręczne tworzenie stałej tablicowej staje się żmudne - wpisanie stałej tablicowej zawierającej 20 lub 30 elementów zajmie dużo czasu. W takim przypadku można użyć skrótu do zbudowania stałej tablicowej korzystającej z funkcji WIERSZ i POŚREDNI. Na przykład, aby SUMAĆ dolne 20 wartości z zakresu o nazwie „rng”, możesz napisać następującą formułę:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Tutaj INDIRECT konwertuje ciąg „1:20” na zakres 1:20, który jest zwracany bezpośrednio do SMALL.
Zmienna n
Aby skonfigurować formułę a, w której n jest zmienną w innej komórce, możesz połączyć ją wewnątrz INDIRECT. Na przykład, jeśli A1 zawiera N, możesz użyć:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Pozwala to użytkownikowi zmienić wartość n bezpośrednio w arkuszu.
