Formuła ogólna
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Podsumowanie
Aby zsumować najwyższe wartości w zakresie, możesz użyć formuły opartej na funkcji LARGE, opakowanej w funkcję SUMPRODUCT. W ogólnej postaci wzoru (powyżej) rng reprezentuje zakres komórek zawierających wartości liczbowe, a N reprezentuje ideę N-tej wartości.
W przykładzie aktywna komórka zawiera następującą formułę:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Wyjaśnienie
W najprostszej formie LARGE zwróci „N-tą największą” wartość w zakresie. Na przykład formuła:
=LARGE(B4:B13, 2)
zwróci drugą co do wielkości wartość z zakresu B4: B13, która w powyższym przykładzie jest liczbą 9.
Jednakże, jeśli podasz "stałą tablicową" (np. Stałą w postaci (1, 2, 3)) do LARGE jako drugi argument, LARGE zwróci tablicę wyników zamiast pojedynczego wyniku. A więc formuła:
=LARGE(B4:B13,(1,2,3))
zwróci pierwszą, drugą i trzecią największą wartość z zakresu B4: B13. W powyższym przykładzie, gdzie B4: B13 zawiera liczby 1-10, wynikiem LARGE będzie tablica (8,9,10). SUMPRODUCT następnie sumuje liczby w tej tablicy i zwraca sumę, która wynosi 27.
SUMA zamiast SUMPRODUCT
SUMPRODUCT to elastyczna funkcja, która umożliwia używanie odwołań do komórek dla k wewnątrz funkcji LARGE.
Jeśli jednak używasz prostej zakodowanej na stałe tablicy stałej, takiej jak (1,2,3), możesz po prostu użyć funkcji SUMA:
=SUM(LARGE(B4:B13,(1,2,3)))
Pamiętaj, że musisz wprowadzić tę formułę jako formułę tablicową, jeśli używasz odwołań do komórek, a nie stałej tablicowej dla k wewnątrz LARGE.
Kiedy N staje się duże
Gdy N staje się duże, ręczne tworzenie stałej tablicowej staje się żmudne - jeśli chcesz podsumować 20 lub 30 pierwszych wartości na dużej liście, wpisanie stałej tablicowej z 20 lub 30 elementami 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, jeśli chcesz SUMAĆ 20 pierwszych wartości z zakresu o nazwie „rng”, możesz napisać następującą formułę:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Zmienna N
Przy niewystarczających danych ustalona wartość N może powodować błędy. W takim przypadku możesz wypróbować następującą formułę:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Tutaj używamy MIN z COUNT, aby zsumować 3 pierwsze wartości lub liczbę wartości, jeśli jest mniejsza niż 3.
