W tym artykule nauczymy się znaleźć liczbę z wieloma kryteriami za pomocą logiki ISNA w programie Excel.
Scenariusz:
W prostych słowach, pracując z długimi, bezobsługowymi danymi. Musimy wyodrębnić liczbę mającą wiele kryteriów bez wartości logicznych lub powiedzieć wartości, aby wykluczyć użycie formuły w programie Excel.
Jak rozwiązać problem?
W tym artykule będziemy musieli użyć funkcji PODAJ.POZYCJĘ i funkcji SUMA PRODUKT. Teraz zrobimy formułę ze wspomnianej funkcji. Tutaj podamy listę mieszanych wartości. Musimy znaleźć liczbę wartości spełniających warunki i użyć funkcji ISNA do wykluczenia wartości.
Użyj wzoru:
| = SUMPRODUCT ( ( zakres = kryteria ) * ( ISNA ( MATCH ( rng , cri_rng , 0 )) ) |
zakres: pierwszy zakres spełniający pierwsze kryteria
kryteria : kryteria dopasowania do pierwszego zakresu
rng : drugi zakres, w którym kryteria do wykluczenia
cri_rng : wartości do wykluczenia z drugiego zakresu
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 tabelę i musimy policzyć wartości pasujące do pewnych nielogicznych warunków. Warunki są następujące.
- Musi być pracownikiem płci żeńskiej, więc tablica Płeć pasuje do F.
- Nie może obejmować pracowników z grup A i B.
Teraz użyjemy poniższej formuły, aby uzyskać liczbę wartości w tabeli spełniających wymienione kryteria.
Formuła:
| = SUMPRODUCT ( ( Płeć = H5 ) * ( ISNA ( MATCH ( Grupy , I5:I6 , 0 )) ) |
Wyjaśnienie:
- PODAJ.POZYCJĘ (Grupy,I5:I6,0) dopasowuje wszystkie wartości grup do podanych wartości kryteriów i zwraca tablicę liczb i wartości #NA.
- Funkcja MATCH zwraca indeks dla dopasowanych wartości i #NA! Błąd dla niedopasowanych wartości.
- ISNA zwraca wartość TRUE dla odpowiedniego dopasowanego #NA! Values i FALSE dla zwróconych wartości indeksów.
- Płeć = H5, zwraca wszystkie dopasowane wartości jako PRAWDA, a niedopasowane jako FAŁSZ.
- Funkcja SUMPRODUCT zwraca liczbę dopasowanych wartości TRUE odpowiadających obu tablicom.
Tutaj tablica funkcji jest podana jako nazwane zakresy, jak wspomniano w żółtych komórkach.
Nazwane zakresy użyte w formule:
Płeć (E4:E17)
ID (D4:D17)
Grupy (F4:F17)
Wartość pojedynczego argumentu podana jako odwołanie do komórki. Naciśnij Enter, aby uzyskać wynik.
Jak widać na powyższym zrzucie, liczba wynosi 3. Jest 3 pracowników, którzy są kobietami i nie należą ani do grupy A, ani do grupy B.
Teraz możemy sprawdzić wynik poprzez filtrowanie zawartości, patrz poniższy gif. Wystarczy przefiltrować tablicę tabeli i policzyć liczbę pasujących wartości lub skorzystać z powyższej formuły, aby uzyskać liczbę.
Oto wszystkie uwagi obserwacyjne dotyczące stosowania wzoru.
Uwagi:
- Formuła działa tylko z liczbami i wartościami tekstowymi.
- Tablica, która jest płcią i grupami, musi mieć tę samą długość, w przeciwnym razie funkcja SUMPRODUCT zwróci błąd.
- Operatory takie jak równa się ( = ), mniejszy niż równy ( <= ), Lepszy niż ( > ) lub nie równa się ( ) można wykonać w ramach funkcji stosowanej tylko z liczbami.
Mam nadzieję, że ten artykuł o tym, jak znaleźć liczbę z wieloma kryteriami z logiką ISNA w programie Excel, jest objaśniający. Więcej artykułów na temat funkcji LICZ.JEŻELI 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.