Policz liczbę wierszy zawierających określone wartości w Excelu
Może nam być łatwo policzyć liczbę komórek o określonej wartości w arkuszu programu Excel. Jednak uzyskanie liczby wierszy zawierających określone wartości może być dość skomplikowane. W takim przypadku bardziej złożona formuła oparta na funkcjach SUMA, MMULT, TRANSPONUJ i KOLUMNA może Ci pomóc. W tym samouczku omówimy, jak stworzyć tę formułę, aby poradzić sobie z tą pracą w programie Excel.
Policz liczbę wierszy zawierających określone wartości
Na przykład masz zakres wartości w arkuszu, a teraz musisz policzyć liczbę wierszy o danej wartości „300”, jak pokazano na poniższym zrzucie ekranu:
Aby uzyskać liczbę wierszy zawierających określone wartości, ogólna składnia to:
Array formula, should press Ctrl + Shift + Enter keys together.
- data: zakres komórek do sprawdzenia, czy zawiera określoną wartość;
- X: konkretna wartość używana do zliczania wierszy.
1. Wprowadź lub skopiuj poniższą formułę do pustej komórki, w której chcesz umieścić wynik:
2. Następnie naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać poprawny wynik, patrz zrzut ekranu:
Wyjaśnienie wzoru:
=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))
- --$2$:$C$12=300: To wyrażenie sprawdza, czy wartość „300” istnieje w zakresie A2:C12, i wygeneruje wynik tablicy TRUE i False. Podwójny znak ujemny służy do zamiany TRUE na 1s i FALSE na 0s. Otrzymasz więc następujący wynik: {0,0,0;1,0,0;0,0,0;0,1,1;0,0,0;0,1,0;0,0,0 ,1,0,0;0,0,1;0,0,0;1,1,1;11}. Tablica składająca się z 3 wierszy i 1 kolumn będzie działać jako tablicaXNUMX w funkcji MMULT.
- TRANSPONUJ(KOLUMNA($A$2:$C$12)): Funkcja COLUMN jest tutaj używana do uzyskania numeru kolumny z zakresu A2:C12, zwraca ona tablicę 3-kolumnową w następujący sposób: {1,2,3}. Następnie funkcja TRANSPOSE zamienia tę tablicę na 3-wierszową tablicę {1;2;3}, działającą jako tablica2 w funkcji MMULT.
- MMULT(--($A$2:$C$12="Joanna"),PRZESUWANIE(KOLUMNA($A$2:$C$12))): Ta funkcja MMULT zwraca iloczyn macierzy powyższych dwóch tablic, otrzymasz wynik w następujący sposób: {0;1;0;5;0;2;0;1;3;0;6}.
- SUM(--(MMULT(--($A$2:$C$12="Joanna"),TRANSPOSE(COLUMN($A$2:$C$12)))>0))= SUM(--{0;1;0;5;0;2;0;1;3;0;6}>0): Najpierw sprawdź wartości w tablicy większe niż 0: Jeśli wartość jest większa niż 0, wyświetlana jest wartość TRUE; jeśli mniej niż 0, wyświetlany jest FALSE. A potem podwójny znak ujemny wymusza, aby TRUE i FALSE były 1s i 0s, więc otrzymasz to: SUM({0;1;0;1;0;1;0;1;1;0;1}). Na koniec funkcja SUMA sumuje wartości w tablicy, aby zwrócić wynik: 6.
Porady:
Jeśli chcesz policzyć liczbę wierszy zawierających określony tekst w arkuszu, zastosuj poniższą formułę i pamiętaj, aby nacisnąć Ctrl + Shift + Enter klucze razem, aby uzyskać całkowitą liczbę:
Zastosowana funkcja względna:
- SUMA:
- Funkcja Excel SUMA zwraca sumę podanych wartości.
- WIELOKROTNOŚĆ:
- Funkcja Excel MMULT zwraca iloczyn macierzy dwóch tablic.
- TRANSPONOWAĆ:
- Funkcja TRANSPOSE zwróci tablicę w nowej orientacji na podstawie określonego zakresu komórek.
- KOLUMNA:
- Funkcja COLUMN zwraca numer kolumny, w której pojawia się formuła, lub zwraca numer kolumny danego odwołania.
Więcej artykułów:
- Policz wiersze, jeśli spełniają kryteria wewnętrzne
- Załóżmy, że masz raport sprzedaży produktów z tego roku i zeszłego roku, a teraz może być konieczne policzenie produktów, w których sprzedaż w tym roku jest większa niż w zeszłym roku lub sprzedaż w tym roku jest mniejsza niż w zeszłym roku, jak poniżej pokazano zrzut ekranu. Zwykle można dodać kolumnę pomocniczą do obliczania różnicy sprzedaży między dwoma latami, a następnie użyć funkcji LICZ.JEŻELI, aby uzyskać wynik. Ale w tym artykule przedstawię funkcję SUMPRODUCT, aby uzyskać wynik bezpośrednio bez żadnej kolumny pomocniczej.
- Policz wiersze, jeśli spełnia wiele kryteriów
- Policz liczbę wierszy w zakresie na podstawie wielu kryteriów, z których niektóre zależą od testów logicznych działających na poziomie wiersza, funkcja SUMPRODUCT w programie Excel może wyświadczyć Ci przysługę.
- Policz liczbę komórek równa jednej z wielu wartości
- Przypuśćmy, że mam listę produktów w kolumnie A, teraz chcę uzyskać całkowitą liczbę określonych produktów Apple, Grape i Lemon, które są wymienione w zakresie C4: C6 z kolumny A, jak pokazano na poniższym zrzucie ekranu. Zwykle w programie Excel proste funkcje LICZ.JEŻELI i LICZ.JEŻELI nie będą działać w tym scenariuszu. W tym artykule omówię, jak szybko i łatwo rozwiązać to zadanie za pomocą kombinacji funkcji SUMPRODUCT i LICZ.JEŻELI.
Najlepsze narzędzia biurowe
Kutools dla programu Excel - pomaga wyróżnić się z tłumu
Kutools dla programu Excel oferuje ponad 300 funkcji, Pewność, że to, czego potrzebujesz, jest w zasięgu jednego kliknięcia...
Karta Office - Włącz czytanie i edycję na kartach w Microsoft Office (w tym Excel)
- Jedna sekunda, aby przełączać się między dziesiątkami otwartych dokumentów!
- Zmniejsz liczbę kliknięć myszą każdego dnia, pożegnaj się z dłonią myszy.
- Zwiększa produktywność o 50% podczas przeglądania i edytowania wielu dokumentów.
- Wprowadza wydajne karty do pakietu Office (w tym programu Excel), podobnie jak przeglądarki Chrome, Edge i Firefox.
