W tym artykule dowiemy się, jak liczyć wiersze, jeśli pasuje co najmniej n liczb w programie Excel.
Scenariusz:
W prostych słowach, podczas pracy z danymi liczbowymi. Musimy wyodrębnić liczbę wierszy mających co najmniej, co najwyżej lub dokładnie n wartości z danego wiersza spełniającego wymienione kryteria w Excelu. Na przykład przy obliczaniu liczby uczniów, którzy w testach osiągają wyniki poniżej średniej. Możemy wykonać tego rodzaju problemy z liczeniem na długiej liście, korzystając z opisanej poniżej formuły Excela.
Użyte funkcje formuł i składnia formuły:
W użyciu będzie następująca funkcja. Więc dowiedz się trochę o tym, zanim go użyjesz.
- Funkcja SUMA
- Funkcja MMULT
- Funkcja TRANSPONUJ
- KOLUMNA, funkcja
Skonstruujemy formułę z podanych funkcji.
Użyj wzoru:
| { =SUMA ( -- ( MMULT ( -- ( ( dane ) < krytyczne ) , TRANSPONUJ ( KOLUMNA ( dane ) 0 ) ) = n )) } |
Uwaga: w przypadku nawiasów klamrowych ( { } ) Posługiwać się Ctrl + Shift + Enter podczas pracy z tablicami lub zakresami w programie Excel. Spowoduje to domyślnie generowanie nawiasów klamrowych we wzorze. NIE próbuj na stałe kodować znaków nawiasów klamrowych.
data : wiązka liczb jako dane.
= n : dokładna liczba wystąpień (użyj <= co najwyżej lub >= przynajmniej dla wystąpień)
crit : kryteria do dopasowania
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 dane sprzedażowe dla konkretnego ID sprzedaży z rzędu. Musimy znaleźć liczbę identyfikatorów sprzedaży, w których sprzedawca nie zakończył sprzedaży 40 dokładnie 2 razy. Tutaj kryterium o jest <40, a występowanie wynosi 2 (=2 dla dokładnego wystąpienia)
Formuła:
| { =SUMA ( -- ( MMULT ( -- ( (C4:H15) < 40 ) ), TRANSPONUJ ( KOLUMNA (C4:H15) 0 )) = 2 )) |
Wyjaśnienie:
- Funkcja COLUMN zwraca tablicę numerów kolumn od pierwszej komórki w tablicy do ostatniej komórki w tablicy. { 3; 4 ; 5 ; 7 ; 8}
- ^0 , ta operacja z dowolnymi numerami tablicy zwraca tablicę złożoną z jedynek.
- Funkcja TRANSPOSE konwertuje wiersze tablicy macierzy na kolumny i kolumny na wiersze. Tutaj tablica 1s w wierszu jest konwertowana na tablicę 6 (1s) w kolumnach. { 1 ; 1 ; 1 ; 1 ; 1 ; 1 }
- -- ( (C4:H15) < 40 ) , ta operacja zwraca wartości jako 1 jako wartości PRAWDA (kryteria dopasowania) i 0 jako wartości Fałsz.
- Funkcja MMULT wykonuje mnożenie macierzy z 2 tablic i zwraca wartości, jak pokazano poniżej.
= SUMA ( -- ( { 1 ; 0 ; 1 ; 0 ; 2 ; 1 ; 0 ; 0 ; 1 ; 2 ; 3 ; 3 } = 2) )
- Funkcja SUMA zlicza wartości w tablicy pasujące do 2, jeśli wystąpienie jest wymagane.
- Użyj Ctrl + Shift + Enter zamiast , aby uzyskać wynik. Ponieważ jest to formuła tablicowa.
Tutaj tablice do funkcji są podane jako referencje do tablicy. Użyj podwójnego (--) ręcznie Naciśnij Enter, aby uzyskać wynik.
Jak widać na powyższym obrazku, jest 2 sprzedawców, którzy nie zrealizowali nawet 40 sprzedaży. Jest to zliczanie do odpowiednich wierszy w danych, w których 2 liczby są mniejsze niż 40. Możesz dostosować występowanie liczby i kryteria, które mają być sprawdzane w wierszach tabeli. Poniżej znajduje się przykład uzyskania Count z różnymi kryteriami i różnymi wystąpieniami.
Policz wiersze pasujące do sprzedaży powyżej 100 z 3 jako dokładnym wystąpieniem:
Teraz chcielibyśmy dowiedzieć się, ilu sprzedawców osiąga umiarkowanie dobre wyniki w sprzedaży. W tym celu musimy uzyskać liczbę sprzedawców, którzy dokonali sprzedaży powyżej 100 dokładnie 3 razy.
Użyj wzoru:
| = SUMA ( -- ( MMULT ( -- ( (C4:H15) > 100 ) ), TRANSPONUJ ( KOLUMNA (C4:H15) 0 )) = 3 )) |
> 100 : liczba jest większa niż 100.
= 3 : występowanie równe 3
Tutaj tablice do funkcji są podane jako referencje do tablicy. Użyj podwójnego (--) ręcznie Naciśnij Enter, aby uzyskać wynik.
Jak widać, 3 sprzedawców radzi sobie umiarkowanie dobrze. Jest to liczba w odpowiednich wierszach danych, w których 3 sprzedaż jest większa niż 100.
Oto wszystkie uwagi obserwacyjne dotyczące stosowania wzoru.
Uwagi:
- Formuła działa tylko z liczbami.
- Funkcje MMULT i TRANSPOSE są funkcjami macierzy, więc sprawdź przed użyciem.
- Jeśli formuła zwraca błąd #ARG, sprawdź, czy w formule muszą znajdować się nawiasy klamrowe, jak pokazano w przykładach w artykule.
- Znak negacji (--) zmienia wartości, TRUE lub 1 na FAŁSZ lub 0 i FAŁSZ lub 0 na PRAWDA lub 1 .
- Operacje takie jak równa się ( = ), mniejszy niż równy ( <= ), Lepszy niż ( > ) lub nie równa się ( ) można wykonać w ramach zastosowanej formuły, wyłącznie z liczbami.
Mam nadzieję, że ten artykuł o tym, jak liczyć wiersze, jeśli n pasujących wartości w programie Excel jest objaśniający. Więcej artykułów na temat formuł liczenia 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 stronie e-mail
Znajdź ostatni wiersz danych z liczbami w Excelu : W zakresie wartości tekstowych znajdź ostatnie dane w programie Excel.
Jak korzystać z funkcji SUMA PRODUKT w programie Excel: Zwraca SUMA po pomnożeniu wartości w wielu tablicach w programie Excel.
LICZ.IFS z zakresem kryteriów dynamicznych : Policz komórki na podstawie innych wartości komórek w programie Excel.
LICZNIKI Dopasowanie dwóch kryteriów : Policz komórki spełniające dwa różne kryteria na liście w programie Excel.
LICZNIKI Z LUB dla wielu kryteriów : Policz komórki spełniające wiele kryteriów za pomocą funkcji LUB.
Funkcja LICZ.WARUNKI w programie Excel : Policz komórki w zależności od innych wartości komórek.
Jak korzystać z Countif w VBA w programie Microsoft Excel : Policz komórki przy użyciu kodu Visual Basic for Applications.
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ść : Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak używać tFunkcja 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 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.
Jak korzystać z funkcji SUMIF w programie Excel? : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.