Formuła ogólna
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Podsumowanie
Aby zsumować n pierwszych wartości w zakresie kryteriów dopasowywania, można użyć formuły opartej na funkcji LARGE, opakowanej w funkcję SUMPRODUCT. W ogólnej postaci wzoru (powyżej) zakres reprezentuje zakres komórek, które są porównywane z kryteriami , wartości reprezentują wartości liczbowe, z których pobierane są najwyższe wartości, a N reprezentuje ideę N-tej wartości.
W przykładzie aktywna komórka zawiera następującą formułę:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Gdzie kolor to nazwany zakres B5: B12, a wartość to nazwany zakres C5: C12.
Wyjaśnienie
W najprostszej formie funkcja LARGE zwraca „N-tą największą” wartość w zakresie o następującej konstrukcji:
=LARGE (range,N)
Na przykład:
=LARGE (C5:C12,2)
zwróci drugą co do wielkości wartość z zakresu C5: C12, czyli 12 w pokazanym przykładzie.
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 (C5:C12, (1,2,3))
zwróci pierwszą, drugą i trzecią największą wartość C5: C12 w tablicy takiej jak ta: (12,12,10)
Tak więc sztuczka polega na filtrowaniu wartości na podstawie koloru przed uruchomieniem LARGE. Robimy to za pomocą wyrażenia:
(color=E5)
Co daje tablicę wartości PRAWDA / FAŁSZ. Podczas operacji mnożenia wartości te są przekształcane w jedynki i zera:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Ostateczny wynik jest taki, że tylko wartości związane z kolorem „czerwonym” przetrwają operację:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
a inne wartości są wymuszane na zero.
Uwaga: ta formuła nie obsługuje tekstu w zakresie wartości. Zobacz poniżej.
Obsługa tekstu w wartościach
Jeśli w dowolnym miejscu zakresu wartości znajduje się tekst, funkcja LARGE zgłosi błąd #ARG i zatrzyma działanie formuły.
Aby obsłużyć tekst w zakresie wartości, możesz dodać funkcję IFERROR w następujący sposób:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Tutaj wychwytujemy błędy z LARGE spowodowane przez wartości tekstowe i zamieniamy na zero. Używanie IF wewnątrz LARGE wymaga wprowadzenia formuły za pomocą control + shift + enter, więc przełączamy się na SUMA zamiast na SUMPRODUCT.
Uwaga: natknąłem się na tę formułę opublikowaną przez niesamowitego Barry'ego Houdiniego na stackoverflow.
