Jak sumować tylko N największych wartości

Użyteczność standardowej funkcji SUMA kończy się wtedy, gdy potrzebujemy sumować jedynie wybrane wartości spełniające pewne kryteria. Dokładnie tak jest w naszym przykładzie.

Mamy za zadanie obliczyć łączną liczbę pasażerów podróżujących londyńskim metrem w godzinach szczytu. Ustaliliśmy, że za godziny szczytu przyjmiemy siedem godzin z największym ruchem. Musimy zatem wyznaczyć sumę jedynie siedmiu największych wartości z każdej kolumny dla dni tygodnia poniedziałek – piątek. Wartości te zostały zaznaczone na zielono za pomocą formatowania warunkowego.

Zastosowanie funkcji SUMA oraz ręczne wybranie komórek nie wchodzi w grę. Takie rozwiązanie jest po prostu za mało elastyczne. Musi więc znaleźć lepsze rozwiązanie. Okazuje się, że takich rozwiązań jest kilka. Sprawdź szczegóły poniżej.

1. JEŻELI oraz MAX.K

W pierwszym rozwiązaniu zbudujemy formułę złożoną z dwóch funkcji: SUMA.JEŻELI oraz MAX.K.

=SUMA.JEŻELI(pon;”>=” & MAX.K(pon; 7))

gdzie „pon” jest nazwą obszaru „B5:B:28”.

Wewnętrzna funkcja MAX.K(pon; 7) zwraca siódmą największą wartość we wskazanym zakresie. Natomiast funkcja SUMA.JEŻELI sumuje jedynie te wartości, które są od niej większe lub równe. Czyli dokładnie o to nam chodziło.

Możemy jeszcze bardziej uelastycznić tą formułę ustawiając 7 jako dodatkowy parametr

2. SUMA.ILOCZYNÓW oraz POZYCJA.NAJW

Znów użyjemy kombinacji dwóch funkcji, ale tym razem będą to: SUMA.ILOCZYNÓW oraz POZYCJA.NAJW.

=SUMA.ILOCZYNÓW(pon*(POZYCJA.NAJW(pon; pon)<=7))

Funkcja POZYCJA.NAJW zwraca pozycję danej liczby na liście liczb. Jeśli zastosujemy tą funkcję do całego wektora „pon”, to każdej liczbie z tego zakresu będzie przypisana jej pozycja. Wystarczy wybrać jedynie te, które są na co najmniej siódmej pozycji. Postępując w ten sposób otrzymamy wektor logiczny: {FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;…

Na koniec użyjemy funkcji SUMA.ILOCZYNÓW, aby zsumować wektor powstały z przemnożenia naszego wektora danych „pon” oraz wektora logicznego. Dzięki temu dostaniemy szukaną wartość.

3. Inne kombinacje funkcji

Możesz też użyć innych kombinacji funkcji z dwóch opisanych rozwiązań np. SUMA.ILOCZYNÓW oraz MAX.K. Spróbuj pokombinować. Sprawdź, które rozwiązanie jest dla Ciebie najbardziej intuicyjne.

4. Własna funkcja napisana w VBA

Jeśli często używasz kombinacji tych samych funkcji, np. takich jak opisane wcześniej, możesz napisać własną funkcję w VBA. Dzięki temu będziesz mógł łatwiej i szybciej znaleźć lub obliczyć szukaną wartość.

Sprawdź przykład takiej funkcji poniżej

Function GetXLargest_Vec(Ref_Tab As Range, Ref_Num As Integer) As Long
   'Ta funkcja działa poprawnie jeśli wejściowa tablica Ref_Tab jest wektorem danych/kolumną

    Dim X_Large As Long
    Dim Temp_Cell As Range
    Dim Result As Long

    'Określ wartość numer X (Ref_Num) w zbiorze danych Ref_Tab
    X_Large = Application.WorksheetFunction.Large(Ref_Tab, Ref_Num)
    'funkcja LARGE jest angielską wersją funkcji MAX.K

    Result = 0
    For Each Temp_Cell In Ref_Tab               'Pętla przechodzi przez wszystkie komórki w zakresie Ref_Tab
        If Temp_Cell >= X_Large Then            
            Result = Result + Temp_Cell         'i sumuje te komórki, które są co najmniej takie jak X_Large
        End If
    Next Temp_Cell
        
    GetXLargest_Vec = Result

End Function

Oczywiście taką funkcję możesz poprawić, aby była jeszcze bardziej elastyczna. Dobrze by było, gdyby potrafiła zwrócić od razu sumę N największych wartości dla każdej kolumny z zakresu. Potrafisz to zrobić?

Jeśli szukasz więcej przykładów, to zapraszam na kurs online. Podczas kursu Excel Średnio Zaawansowany lub Excel Zaawansowany dowiesz się jak rozwiązywać podobne problemy za pomocą skomplikowanych i zagnieżdżonych formuł..