W tym artykule dowiemy się, jak zliczać komórki, które nie są równe wielu rzeczom w Excelu.
W prostych słowach rozważmy scenariusz, w którym musimy policzyć dane na podstawie określonych kryteriów w tablicy. zliczanie komórek, które nie zawierają wartości, aby złapać wartość komórki za pomocą funkcji programu Excel wyjaśnionej tutaj z przykładem. Użyj symboli wieloznacznych do przechwytywania ciągów i wykonywania na nich funkcji.
W tym celu będziemy używać następujących funkcji.
- Funkcja SUMA PRODUKT
- Funkcja PODAJ.POZYCJĘ
- funkcja ISNA
- ILE.NIEPUSTYCH funkcjiSEO
- LICZ.WARUNKI, funkcja
Teraz dowiemy się o nich w skrócie. Na to samo mamy różne metody. Więc zobaczymy to jeden po drugim.
Pierwsza metoda
Policz komórkę, która nie zawiera wartości z zakresu. Tutaj mamy na to formułę. Użyjemy funkcji MATCH, aby dowiedzieć się, gdzie są wartości, które należało policzyć, a funkcja ISNA wyłapuje błąd #NA. Następnie SUMPRODUCT zwraca sumę wartości.
- Funkcja SUMA PRODUKT
- Funkcja PODAJ.POZYCJĘ
- funkcja ISNA
Wzór ogólny:
| = SUMPRODUCT ( -- ( ISNA ( MATCH ( zakres , wartości , 0)) ) |
Zmienne:
zakres: tablica komórek do zliczenia
wartości: tablica wartości do zignorowania w zakresie.
Przykład
Rozumiemy tę funkcję na przykładzie.
Tutaj mamy owoce w jednej kolumnie, a warzywa w drugiej. Pierwszą żądzą są warzywa na liście i musimy liczyć tylko owoce, nie biorąc pod uwagę warzyw.
Użyj wzoru:
| = PRODUKT SUMA ( -- ( ISNA ( DOPASUJ ( owoce , warzywa , 0 )) ) ) |
owoce: nazwany zakres dla tablicy B2:B10
Warzywa: nazwany zakres używany w tablicy D3:D5
Wyjaśnienie:
- Funkcja PODAJ.POZYCJĘ zwraca pozycję każdego indeksu, w którym wartości są dopasowane w zakresie lub zwraca błąd #NA, jeśli nie znaleziono.
- Funkcja ISNA przechwytuje błąd #NA i zwraca TRUE dla błędu #NA i FALSE w innym miejscu.
- -- operator konwertuje wszystkie TRUE na 1s i FALSE na 0s.
- Funkcja SUMPRODUCT zwraca sumę wszystkich liczb w tablicy, która w rzeczywistości jest liczbą komórek, w których wartości nie są brane pod uwagę.
Jak widać formuła zwraca liczbę owoców nie uwzględniając wartości z warzyw.
Druga metoda:
Rozważymy inne metody, ponieważ różne rodzaje problemów mają różne rozwiązania. Korzystanie z funkcji LICZ.WARUNKI w programie Excel.
Wzór ogólny:
| = LICZ.WARUNKI ( zakres, "wartość1", zakres, "wartość2",… ) |
Zmienne:
zakres: tablica komórek do zliczenia
value1: wartość do zignorowania w zakresie
value2: wartość do zignorowania w zakresie
Przykład
Rozumiemy tę funkcję na przykładzie.
Tutaj mamy owoce w jednej kolumnie, a warzywa w drugiej. Pierwsza kolumna zawiera kilka warzyw na liście i musimy liczyć tylko owoce, nie biorąc pod uwagę warzyw.
Użyj wzoru:
| = LICZ.WARUNKI ( owoce , "" & D3, owoce , "" & D4 , owoce , "" & D5 ) |
owoce: nazwany zakres dla tablicy B2:B10
D3 , D4 i D5 to odwołania do komórek używane w formule
Wyjaśnienie:
- Tutaj zakres jest sprawdzany z poszczególnymi wartościami i zwraca liczbę komórek.
- operator to nie równa się
- Operator &, łączy wartości z wartością odwołania do komórki.
Użyj wzoru:
| = LICZ.WARUNKI ( owoce , "" & D3, owoce , "" & D4 , owoce , "" & D5 ) |
owoce: nazwany zakres dla tablicy B2:B10
D3, D4 i D5 to odwołania do komórek używane w formule
Wyjaśnienie:
- Tutaj zakres jest sprawdzany z poszczególnymi wartościami i zwraca liczbę komórek.
- operator to nie równa się
- & połącz operator z wartością używaną przez odwołanie do komórki.
Jak widać formuła zwraca liczbę owoców nie uwzględniając wartości z warzyw.
Trzecia metoda
Rozważymy inne metody, ponieważ różne rodzaje problemów mają różne rozwiązania. Użycie funkcji ILE.NIEPUSTYCH w programie Excel. Tutaj logika polega na obliczeniu liczby komórek mających wartości, a następnie odjęciu ich od liczby wszystkich wartości w zakresie.
Wzór ogólny:
| = ILE.LICZB (zakres) - SUMPRODUCT ( LICZ.JEŻELI ( zakres, wartości ) |
Zmienne:
zakres: tablica komórek do zliczenia
wartości: tablica wartości do zignorowania w zakresie.
Przykład
Rozumiemy tę funkcję na przykładzie.
Tutaj mamy owoce w jednej kolumnie, a warzywa w drugiej. Pierwszą żądzą są warzywa na liście i musimy liczyć tylko owoce, nie biorąc pod uwagę warzyw.
Użyj wzoru:
| = ILE.LICZB (owoce) - SUMA PRODUKT ( ODLICZANIE ( owoce , warzywa ) ) |
owoce: nazwany zakres dla tablicy B2:B10
Warzywa: nazwany zakres używany w tablicy D3:D5
Wyjaśnienie:
- Funkcja LICZ.JEŻELI zlicza komórki w owocach zawierających warzywa.
- SUMPRODUCT otrzymuje sumę biorąc pod uwagę wszystkie wartości.
- Funkcja ILE.NIEPUSTYCH oblicza liczbę niepustych komórek w zakresie.
- Różnicę otrzyma liczba komórek (owoców) nie posiadających wartości (warzywa).
Jak widać formuła zwraca liczbę owoców nie uwzględniając wartości z warzyw.
Mam nadzieję, że zrozumiałeś, jak liczyć komórki, które nie są równe wielu rzeczom w Excelu. Zapoznaj się z dodatkowymi artykułami na temat funkcji zliczania programu Excel tutaj. Prosimy o przesłanie zapytania lub opinii dotyczącej powyższego artykułu.
LICZ.IFS z zakresem kryteriów dynamicznych: Policz komórki z dynamicznym zakresem kryteriów za pomocą funkcji LICZ.WARUNKI.
LICZNIKI Dopasowanie dwóch kryteriów: Policz komórki mające wiele kryteriów za pomocą funkcji LICZ.WARUNKI.
LICZNIKI Z LUB dla wielu kryteriów: Policz komórki za pomocą funkcji logicznych z funkcją LICZ.WARUNKI.
Funkcja LICZ.WARUNKI w programie Excel: użycie funkcji LICZ.WARUNKI do zliczania komórek mających wiele kryteriów
Jak korzystać z Countif w VBA w programie Microsoft Excel: Użyj funkcji LICZ.JEŻELI w VBA, aby zliczyć komórki.
Jak używać symboli wieloznacznych w programie Excel: Łap frazy i słowa kluczowe za pomocą wyjaśnionych symboli wieloznacznych w programie Excel
popularne artykuły
50 skrótów Excela, aby zwiększyć produktywność
Edytuj listę rozwijaną
Jeśli z formatowaniem warunkowym
Jeśli z symbolami wieloznacznymi
Przeglądaj według daty