W tym artykule dowiemy się, jak podsumować górne lub dolne wartości N przy danej tablicy liczb w programie Excel. Jeśli chcesz zsumować najwyższe 4, najlepsze 5 lub najwyższe wartości N w danym zakresie, ten artykuł pomoże to zrobić.
Suma wartości TOP N
W tym artykule będziemy musieli użyć funkcji SUMPRODUCT. Teraz zrobimy formułę z tych funkcji. Tutaj otrzymujemy zakres i musimy uzyskać górne 5 wartości w zakresie i uzyskać sumę wartości.
Wzór ogólny:
= SUMPRODUCT ( DUŻY ( zakres, { 1 , 2 ,… . ,n } } )
zasięg: Zakres wartości
Wartości : liczby oddzielone przecinkami, np. jeśli chcesz znaleźć 3 najwyższe wartości, użyj { 1 , 2 , 3 }.
Przykład: Suma 5 najwyższych wartości zakresu
Przetestujmy tę formułę, uruchamiając ją na poniższym przykładzie.
Tutaj mamy zakres wartości od A1:A23.
Zakres A1:A23 zawiera wartości i nosi nazwę wartości.
Najpierw musimy znaleźć pięć najwyższych wartości za pomocą funkcji LARGE, a następnie wykonać operację sumowania na tych 5 wartościach. Teraz użyjemy poniższej formuły, aby uzyskać sumę 5 najwyższych wartości.
Użyj formuły:
= PRODUKT SUMA ( DUŻY ( zakres , { 1 , 2 , 3 , 4 , 5 } ) )
Wyjaśnienie:
- Funkcja LARGE zwraca 5 pierwszych wartości liczbowych i zwraca tablicę do funkcji SUMPRODUCT.
= PRODUKT SUMA ( { 149 , 123 , 100 , 87 , 85 } ) )
- Funkcja SUMPRODUCT pobiera do zsumowania tablicę pięciu najwyższych wartości.
Tutaj zakres jest podany jako zakres nazwany. Naciśnij Enter, aby uzyskać SUMA pierwszych 5 liczb.
Jak widać na powyższym zrzucie, suma ta wynosi 544. Suma wartości 149 + 123 + 100 + 87 + 85 = 544.
Powyższy proces służy do obliczenia sumy kilku liczb od góry. Ale obliczyć dla n (duża) liczba wartości w długim zakresie.
Użyj wzoru:
= SUMPRODUCT ( LARGE ( zakres, WIERSZ ( POŚREDNIA ( "1:10") ) )
Tutaj generujemy sumę 10 najwyższych wartości, uzyskując tablicę od 1 do 10 { 1; 2 ; 3 ; 4 ; 5 ; 6 ; 7 ; 8 ; 9; 10 } za pomocą funkcji WIERSZ I POŚREDNI programu Excel.
Tutaj otrzymaliśmy sumę pierwszych 10 liczb { 149 ; 123; 100 ; 87; 85; 82; 61 ; 58; 54; 51 }), co daje 850.
Suma dolnych wartości N
Jak rozwiązać problem?
Wzór ogólny:
= PRODUKT SUMA ( MAŁY ( zakres, { 1 , 2 ,… . ,n } } )
Zakres: zakres wartości
Wartości : liczby oddzielone przecinkami, np. jeśli chcesz znaleźć 3 dolne wartości, użyj { 1 , 2 , 3 }.
Przykład:
Wszystko to może być trudne do zrozumienia. Przetestujmy więc tę formułę, uruchamiając ją na poniższym przykładzie.
Tutaj mamy zakres wartości od A1:A23.
Oto zakres, który jest podawany za pomocą nazwanego narzędzia Excel zakresu.
Najpierw musimy znaleźć pięć najniższych wartości za pomocą funkcji SMALL, a następnie wykonać operację sumowania na tych pięciu wartościach. Teraz użyjemy następującego wzoru, aby uzyskać sumę
Użyj formuły:
= PRODUKT SUMA ( MAŁY ( zakres , { 1 , 2 , 3 , 4 , 5 } ) )
Wyjaśnienie:
- Funkcja MAŁY zwraca 5 dolnych wartości liczbowych i zwraca tablicę do funkcji SUMA.
= PRODUKT SUMA ( { 23 , 27 , 28 , 28 , 30 } ) )
- Funkcja SUMPRODUCT pobiera do zsumowania tablicę pięciu najniższych wartości.
Tutaj zakres jest podany jako zakres nazwany. Naciśnij Enter, aby uzyskać SUMA 5 dolnych liczb.
Jak widać na powyższym zrzucie, suma ta wynosi 136. Suma wartości 23 + 27 + 28 + 28 + 30 = 136.
Powyższy proces służy do obliczenia sumy kilku liczb od dołu. Ale obliczyć dla n (duża) liczba wartości w długim zakresie.
Użyj wzoru:
= SUMPRODUCT ( MAŁY ( zakres, WIERSZ ( POŚREDNIA ( "1:10") ) )
Tutaj generujemy sumę dolnych 10 wartości poprzez pobranie tablicy od 1 do 10 { 1; 2 ; 3 ; 4 ; 5 ; 6 ; 7 ; 8 ; 9; 10 } za pomocą funkcji WIERSZ I POŚREDNI programu Excel.
Tutaj mamy sumę dolnych 10 liczb, które dadzą 307.
Oto kilka uwag obserwacyjnych pokazanych poniżej.
Uwagi:
- Formuła działa tylko z liczbami.
- Formuła działa tylko wtedy, gdy w tabeli przeglądowej nie ma duplikatów
- Funkcja SUMPRODUCT traktuje wartości nieliczbowe ( takie jak tekst abc ) i wartości błędów ( takie jak #LICZBA! , #NULL! ) jako wartości null.
- Funkcja SUMPRODUCT traktuje wartość logiczną TRUE jako 1 i False jako 0.
- Tablica argumentów musi mieć taką samą długość jak funkcja.
Mam nadzieję, że ten artykuł o tym, jak zwrócić sumę górnych wartości n lub dolnych n wartości w programie Excel, jest objaśniający. Więcej artykułów na temat funkcji SUMPRODUCT znajdziesz tutaj. Podziel się swoim zapytaniem poniżej w polu komentarza. Pomożemy Ci.
Jak korzystać z funkcji SUMA PRODUKT w programie Excel: Zwraca SUMA po pomnożeniu wartości w wielu tablicach w programie Excel.
SUMA, jeśli data jest między : Zwraca SUMA wartości między podanymi datami lub okresem w programie Excel.
Suma, jeśli data jest większa niż podana: Zwraca SUMA wartości po podanej dacie lub okresie w programie Excel.
2 sposoby sumowania według miesiąca w Excelu: Zwraca SUMA wartości w danym miesiącu w programie Excel.
Jak sumować wiele kolumn z warunkiem: Zwraca SUMA wartości w wielu kolumnach mających warunek w programie Excel
Jak używać symboli wieloznacznych w programie Excel : Policz komórki pasujące do fraz za pomocą symboli wieloznacznych w programie Excel
popularne artykuły
50 skrótów Excela, aby zwiększyć produktywność
Edytuj listę rozwijaną
Absolutne odniesienie w Excelu
Jeśli z formatowaniem warunkowym
Jeśli z symbolami wieloznacznymi
Przeglądaj według daty
Konwertuj cale na stopy i cale w programie Excel 2016
Dołącz imię i nazwisko w programie Excel
Policz komórki, które pasują do A lub B
