Note: The other languages of the website are Google-translated. Back to English

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:

{=SUM(–(MMULT(–(data=X),TRANSPOSE(COLUMN(data)))>0))}
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:

=SUM(--(MMULT(--($A$2:$C$12=300),TRANSPOSE(COLUMN($A$2:$C$12)))>0))

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

=SUM(--(MMULT(--(ISNUMBER(SEARCH("Joanna",A2:C12))),TRANSPOSE(COLUMN($A$2:$C$12)))>0))


Zastosowana funkcja względna:

  • SUMA:
  • Funkcja Excel SUMA zwraca sumę podanych wartości.
  • 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

Chcesz szybko i perfekcyjnie zakończyć swoją codzienną pracę? Kutools dla programu Excel oferuje 300 zaawansowanych zaawansowanych funkcji (Łącz skoroszyty, sumuj według koloru, dziel zawartość komórek, konwertuj daty itd.) i oszczędzaj 80% czasu.

  • Zaprojektowany dla 1500 scenariuszy pracy, pomaga rozwiązać 80% problemów z Excelem.
  • Zmniejsz tysiące kliknięć klawiatury i myszy każdego dnia, odciąż zmęczone oczy i dłonie.
  • Zostań ekspertem Excela w 3 minuty. Nie musisz już pamiętać żadnych bolesnych formuł i kodów VBA.
  • 30-dniowy nieograniczony bezpłatny okres próbny. 60-dniowa gwarancja zwrotu pieniędzy. Bezpłatna aktualizacja i wsparcie przez 2 lata.
Wstążka programu Excel (z zainstalowanym Kutools dla programu Excel)

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 zakładki do pakietu Office (w tym Excel), podobnie jak Chrome, Firefox i nowy Internet Explorer.
Zrzut ekranu programu Excel (z zainstalowaną kartą Office)
Sortuj komentarze według
Komentarze (0)
Brak ocen. Oceń jako pierwszy!
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL