W poprzednim artykule dowiedzieliśmy się, jak sumować górne lub dolne wartości N. W tym artykule próbujemy zsumować górne lub dolne wartości N za pomocą kryteriów.
Suma wartości TOP N z kryteriami
Jak rozwiązać problem?
W tym artykule będziemy musieli użyć funkcji SUMPRODUCT. Teraz zrobimy formułę z tych funkcji. Tutaj otrzymujemy zakres i kryteria. Musimy uzyskać 5 najwyższych wartości w zakresie i uzyskać sumę wartości na podstawie podanych kryteriów.
Wzór ogólny:
= SUMPRODUCT ( LARGE ( (lista = kryteria) * (zakres), { 1 , 2 ,… . ,n } } )
lista: lista kryteriów
Kryteria: kryteria do dopasowania
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:
Tutaj mamy wartości zestawu danych z A1:D50.
Najpierw musimy znaleźć pięć najwyższych wartości za pomocą funkcji LARGE, która odpowiada miastu „Boston”, 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:
= SUMPRODUCT ( LARGE ( ( Miasto = "Boston") * (ilość) , { 1 , 2 , 3 , 4 , 5 } ) )
Wyjaśnienie:
- City „Boston” odpowiada wymienionemu zakresowi City. Zwraca tablicę wartości prawda i fałsz.
- Funkcja LARGE zwraca 5 pierwszych wartości liczbowych z zakresu ilości i zwraca tablicę do funkcji SUMPRODUCT.
= PRODUKT SUMA { 193 , 149 , 138 , 134 , 123 }
- Funkcja SUMPRODUCT pobiera tablicę pięciu pierwszych wartości, przy czym tablica pięciu pierwszych liczb zwraca sumę tych liczb.
Tutaj zakres Miasto i ilość jest podany jako zakres nazwany. Naciśnij Enter, aby uzyskać SUMA pięciu pierwszych liczb.
Jak widać na powyższym zrzucie, suma ta wynosi 737. Suma wartości 193 + 149 + 138 + 134 + 123 = 737.
Możesz sprawdzić powyższe wartości w zbiorze danych za pomocą opcji filtra programu Excel. Zastosuj filtr do nagłówka Miasto i ilość i kliknij przycisk strzałki w nagłówku miasta, który się pojawi. Postępuj zgodnie z instrukcjami, jak pokazano poniżej.
Kroki:
- Wybierz komórkę Nagłówek miasta. Zastosuj filtr za pomocą skrótu Ctrl + Shift + L
- Kliknij strzałkę, która pojawi się jako opcja filtrowania.
- Wybierz opcję (Zaznacz wszystko).
- Wybierz tylko miasto Boston.
- Wybierz teraz nagłówek ilości.
- Posortuj listę od największej do najmniejszej, a zobaczysz wszystkie 5 najwyższych wartości, które obliczyliśmy za pomocą formuły.
Jak widać na powyższym gifie wszystkie 5 wartości pasujących do podanych kryteriów. Oznacza to również, że formuła działa poprawnie, aby uzyskać liczbę tych wartości
DUŻE liczby N
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 ( ( Miasto = "Boston" ) * (ilość), 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 mamy sumę pierwszych 10 liczb, co daje 1147.
Suma dolnych wartości N z kryteriami
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:
{ = SUMA ( MAŁA ( JEŻELI ( Miasto = " Boston " , ilość ) , { 1 , 2 , 3 , 4 , 5 } ) ) }
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:D50.
Tutaj zakres Miasto i ilość jest podany przy użyciu narzędzia Excel z nazwanym zakresem.
Najpierw musimy znaleźć pięć najniższych wartości za pomocą funkcji SMALL, która spełnia kryteria, 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:
{ = SUMA ( MAŁA ( JEŻELI ( Miasto = " Boston " , ilość ) , { 1 , 2 , 3 , 4 , 5 } ) ) )
NIE używaj nawiasów klamrowych ręcznie. Nawiasy klamrowe stosowane za pomocą Ctrl + Shift + Enter zamiast po prostu Wejść.
Wyjaśnienie:
- Funkcja MAŁE z funkcją JEŻELI zwraca 5 dolnych wartości liczbowych odpowiadających miastu „Boston” i zwraca tablicę do funkcji SUMA.
= SUMA ( { 23 , 27 , 28 , 28 , 30 } ) )
- Funkcja SUMA pobiera tablicę 5 najniższych wartości, która ma tablicę 5 najniższych liczb, zwraca SUMA tych liczb użytych z CTRL + SHIFT + ENTER.
Tutaj zakres Miasto i ilość jest podany jako zakres nazwany. naciskać Ctrl + Shift + Enter aby uzyskać SUMA 5 dolnych liczb, ponieważ jest to formuła tablicowa.
Jak widać na powyższym zrzucie, suma ta wynosi 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:
{ = SUMA ( MAŁA ( JEŻELI ( Miasto = "Boston" , ilość ) , WIERSZ ( ADR.POŚREDNIA ( "1:10") )) ) }
NIE używaj nawiasów klamrowych ręcznie. Użyj Ctrl + Shift + Enter zamiast używania Enter.
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ą 155.
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ę najwyższych wartości 5 lub dolnych wartości 5 z kryteriami 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.
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 stronie 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
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