Jak zsumować dolne 5 lub n wartości w kolumnie w Excelu?

Anonim

W tym artykule dowiemy się, jak zsumować dolne 5 lub n wartości w kolumnie w programie Excel.

Scenariusz:

W prostych słowach, podczas pracy z liczbami danych, czasami podajemy warunek, np. kiedy musimy uzyskać sumę liczb z dołu lub 5 najniższych liczb z tablicy lub spodu tablicy. Możesz łatwo rozwiązać ten problem, korzystając z funkcji programu Excel, jak wyjaśniono poniżej.

Suma dolnych wartości N

Jak rozwiązać problem?

W tym artykule będziemy musieli użyć funkcji SUMPRODUCT. Teraz zrobimy formułę z tych funkcji. Tutaj otrzymujemy zakres i musimy obniżyć 5 wartości w zakresie i uzyskać sumę wartości.

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.

Tutaj zakres jest podany jako użycie nazwanego narzędzia Excel do zakresu.

Najpierw musimy znaleźć dolne Pięć wartości za pomocą funkcji SMALL, a następnie wykonać operację sumowania na tych 5 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 tablicę 5 najniższych wartości, która ma tablicę 5 najniższych liczb, zwraca SUMA tych liczb.

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 otrzymaliśmy sumę dolnych 10 liczb, co daje 307.

Suma wartości TOP N

Jak rozwiązać problem?

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 } } )

zakres : 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:

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.

Tutaj zakres jest podany jako użycie nazwanego narzędzia Excel do zakresu.

Najpierw musimy znaleźć pięć najwyższych wartości za pomocą funkcji LARGE, 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 ( 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 tablicę pięciu pierwszych wartości, która ma tablicę pięciu pierwszych liczb, zwraca SUMA tych liczb.

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.

Oto kilka uwag obserwacyjnych pokazanych poniżej.

Uwagi:

  1. Formuła działa tylko z liczbami.
  2. Formuła działa tylko wtedy, gdy w tabeli przeglądowej nie ma duplikatów
  3. Funkcja SUMPRODUCT traktuje wartości nieliczbowe ( takie jak tekst abc ) i wartości błędów ( takie jak #LICZBA! , #NULL! ) jako wartości null.
  4. Funkcja SUMPRODUCT traktuje wartość logiczną TRUE jako 1 i False jako 0.
  5. Argument tablica musi mieć tę samą długość, w przeciwnym razie funkcja.

Mam nadzieję, że ten artykuł o tym, jak zsumować dolne 5 lub n wartości w kolumnie w programie Excel, jest objaśniający. Więcej artykułów na temat obliczania wartości i powiązanych formuł programu Excel znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.

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 :

Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.

Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.

Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.

Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.