Ten samouczek pokazuje, jak używaćExcel LICZ.JEŻELI i LICZ.JEŻELI Funccje w programie Excel, aby zliczyć dane spełniające określone kryteria.
LICZ.JEŻELI - przegląd funkcji
Możesz użyć funkcji LICZ.JEŻELI w programie Excel do zliczania komórek zawierających określoną wartość, zliczania komórek większych lub równych wartości itp.
(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)
LICZ.JEŻELI Składnia i argumenty funkcji:
=LICZ.JEŻELI (zakres, kryteria)
zasięg - Zakres komórek do zliczenia.
kryteria - Kryteria kontrolujące, które komórki powinny być liczone.
Co to jest funkcja LICZ.JEŻELI?
Funkcja LICZ.JEŻELI jest jedną ze starszych funkcji używanych w arkuszach kalkulacyjnych. Mówiąc prościej, świetnie sprawdza się w skanowaniu zakresu i informowaniu, ile komórek spełnia ten warunek. Przyjrzymy się, jak funkcja działa z tekstem, liczbami i datami; jak również niektóre inne sytuacje, które mogą się pojawić.
Podstawowy przykład
Zacznijmy od spojrzenia na tę listę losowych przedmiotów. Mamy kilka liczb, puste komórki i kilka ciągów tekstowych.
Jeśli chcesz wiedzieć, ile elementów dokładnie pasuje do kryteriów, możesz określić, czego chcesz szukać, jako drugi argument. Przykład takiej formuły może wyglądać tak:
=LICZ.JEŻELI(A2:A9;"Jabłko")
Ta formuła zwróci liczbę 3, ponieważ w naszym zakresie są 3 komórki, które spełniają te kryteria. Alternatywnie możemy użyć odwołania do komórki zamiast zakodować wartość. Gdybyśmy napisali „Apple” w komórce G2, moglibyśmy zmienić formułę na
=LICZ.JEŻELI(A2:A9;G2)
W przypadku liczby ważne jest, aby odróżnić liczby od liczb, które są przechowywane jako tekst. Ogólnie rzecz biorąc, podczas pisania formuł nie umieszcza się cudzysłowów wokół liczb. Tak więc, aby napisać formułę sprawdzającą liczbę 5, napisałbyś
=LICZ.JEŻELI(A2:A9; 5)
Na koniec możemy również sprawdzić puste komórki za pomocą ciągu o zerowej długości. Zapisalibyśmy tę formułę jako
=LICZ.JEŻELI(A2:A9; "")
Notatka: ta formuła zlicza zarówno komórki, które są naprawdę puste, jak i te, które są puste w wyniku formuły, na przykład funkcji JEŻELI.
Dopasowania częściowe
Funkcja LICZ.JEŻELI obsługuje użycie symboli wieloznacznych „*” lub „?” w kryteriach. Spójrzmy na tę listę smacznych wypieków:
Aby znaleźć wszystkie elementy, które zaczynają się od Apple, moglibyśmy napisać „Apple*”. Tak więc, aby uzyskać odpowiedź 3, nasz wzór w D2 to
=LICZ.JEŻELI(A2:A5;"Jabłko*")
Notatka: W funkcji LICZ.JEŻELI nie jest rozróżniana wielkość liter, więc jeśli chcesz, możesz również napisać „jabłko*”.
Wracając do naszych wypieków, możemy również dowiedzieć się, ile ciastek mamy na naszej liście. Możemy to znaleźć, umieszczając symbol wieloznaczny na początku wyszukiwanego hasła i pisząc
=LICZ.JEŻELI(A2:A5;"*koło")
Ta formuła daje wynik 2.
Możemy również użyć symboli wieloznacznych, aby sprawdzić komórki z tekstem. Wróćmy do naszej oryginalnej listy danych.
Aby policzyć liczbę komórek, które mają przynajmniej trochę tekstu, a więc nie licząc liczb lub pustej komórki, możemy napisać
=LICZ.JEŻELI(A2:A9;"*")
Widać, że nasza formuła poprawnie zwraca wynik 4.
Operatory porównania w COUNTIF
Pisząc do tej pory kryteria, sugerowaliśmy, że naszym operatorem porównania jest „=”. W rzeczywistości moglibyśmy napisać tak:
=LICZ.JEŻELI(A2:A9;"=Jabłko")
Jest to jednak dodatkowy znak do napisania, więc zwykle jest pomijany. Oznacza to jednak, że możesz użyć innych operatorów, takich jak większy niż, mniejszy niż lub nie równy. Spójrzmy na tę listę zarejestrowanych wieków:
Jeśli chcielibyśmy wiedzieć, ile dzieci ma co najmniej 5 lat, możemy napisać porównanie „większe lub równe” w następujący sposób:
=LICZ.JEŻELI(A2:A8; ">=5")
Notatka: Operator porównania jest zawsze podawany jako ciąg tekstowy i dlatego musi znajdować się w cudzysłowie.
Podobnie możesz również sprawdzić elementy, które mają mniej niż podana wartość. Jeśli musimy dowiedzieć się, ile jest mniej niż 8, możemy napisać
=LICZ.JEŻELI(A2:A8;"<8")
Daje nam to pożądany wynik 5. Teraz wyobraźmy sobie, że wszystkie 6-letnie dzieci wybierają się na wycieczkę. Ile dzieci pozostanie? Możemy to rozgryźć, używając porównania „nie równa się” w ten sposób:
=LICZ.JEŻELI(A2:A8; "6")
Teraz szybko widzimy, że mamy 6 dzieci, które nie mają 6 lat.
W tych przykładach porównawczych do tej pory ciężko kodowaliśmy wartości, które chcieliśmy. Możesz także użyć odwołania do komórki. Sztuczka polega na tym, że musisz połączyć operator porównania z odwołaniem do komórki. Załóżmy, że umieściliśmy liczbę 7 w komórce C2 i chcemy, aby nasz wzór w komórce D2 wskazywał, ile dzieci ma mniej niż 7 lat.
Nasz wzór w D2 musi wyglądać tak:
=LICZ.JEŻELI(A2:A8;"<"&C2)
Notatka: Podczas pisania tych wzorów zwróć szczególną uwagę na to, czy chcesz umieścić element w cudzysłowie, czy na zewnątrz. Operatory są zawsze w cudzysłowie, odwołania do komórek są zawsze poza cudzysłowami. Liczby są na zewnątrz, jeśli wykonujesz dokładne dopasowanie, ale wewnątrz, jeśli robisz operator porównania.
Praca z datami
Widzieliśmy, jak możesz podać tekst lub liczbę jako kryteria, ale co z tym, kiedy musimy pracować z datami? Oto krótka przykładowa lista, z którą możemy pracować:
Aby policzyć, ile dat jest po 4 maja, musimy zachować ostrożność. Komputery przechowują daty jako liczby, więc musimy upewnić się, że komputer używa właściwej liczby. Czy gdybyśmy napisali tę formułę, otrzymalibyśmy poprawny wynik?
=LICZ.JEŻELI(A2:A9; "
Odpowiedź brzmi „prawdopodobnie”. Ponieważ w naszych kryteriach pominęliśmy rok, komputer przyjmie, że mamy na myśli rok bieżący. Jeśli wszystkie daty, z którymi pracujemy, dotyczą bieżącego roku, otrzymamy poprawną odpowiedź. Jeśli jednak istnieją daty, które są w przyszłości, otrzymalibyśmy złą odpowiedź. Również po rozpoczęciu kolejnego roku ta formuła zwróci inny wynik. W związku z tym prawdopodobnie należy unikać tej składni.
Ponieważ poprawne zapisanie dat w formule może być trudne, najlepiej jest wpisać datę, której chcesz użyć w komórce, a następnie użyć tego odwołania do komórki w formule LICZ.JEŻELI. Zapiszmy więc datę 7 maja 2020 r. w komórce C2, a następnie możemy umieścić naszą formułę w C4.
Wzór w C4 to
=LICZ.JEŻELI(A2:A9; "<"&C2)
Teraz wiemy, że wynik 7 jest poprawny i odpowiedź nie zmieni się nieoczekiwanie, jeśli otworzymy ten arkusz kalkulacyjny w przyszłości.
Zanim opuścimy tę sekcję, często używamy funkcji DZIŚ podczas pracy z datami. Możemy tego użyć tak, jakbyśmy odwołali się do komórki. Na przykład możemy zmienić poprzednią formułę na następującą:
=LICZ.JEŻELI(A2:A9; "<"&DZIŚ())
Teraz nasza formuła będzie nadal aktualizowana w miarę postępów w czasie rzeczywistym, a my będziemy mieli mniej przedmiotów niż dzisiaj.
Wiele kryteriów i LICZ
Oryginalna funkcja LICZ.JEŻELI została ulepszona w 2007 r., kiedy pojawiła się funkcja LICZ.JEŻELI. Składnia między nimi jest bardzo podobna, przy czym ta druga umożliwia podanie dodatkowych zakresów i kryteriów. Możesz łatwo użyć funkcji LICZ.JEŻELI w każdej sytuacji, w której istnieje LICZ.JEŻELI. Warto wiedzieć, że istnieją obie funkcje.
Spójrzmy na tę tabelę danych:
Aby dowiedzieć się, ile osób jest na poziomach płac od 1 do 2, możesz napisać podsumowanie funkcji LICZ.JEŻELI w następujący sposób:
=LICZ.JEŻELI(B2:B7; ">=1")-LICZ.JEŻELI(B2:B7; ">2")
Ta formuła zadziała, ponieważ znajdujesz wszystko, co jest powyżej 1, ale następnie odejmujesz liczbę rekordów, które są poza twoim punktem odcięcia. Alternatywnie możesz użyć opcji LICZ.WARUNKI w ten sposób:
=LICZ.WARUNKI(B2:B7; ">=1"; B2:B7; "<=2")
Ta ostatnia jest bardziej intuicyjna w czytaniu, więc możesz chcieć skorzystać z tej trasy. Ponadto funkcja LICZ.WARUNKI jest bardziej wydajna, gdy trzeba wziąć pod uwagę wiele kolumn. Załóżmy, że chcemy wiedzieć, ile osób jest w Zarządzie i na 1. poziomie wynagrodzeń. Nie można tego zrobić za pomocą funkcji LICZ.JEŻELI; musiałbyś napisać
=LICZ.WARUNKI(A2:A7;"Zarządzanie";B2:B7;1)
Ta formuła dałaby poprawny wynik 2. Zanim opuścimy tę sekcję, rozważmy logikę typu Or. Co by było, gdybyśmy chcieli dowiedzieć się, ile osób jest w Zarządzie? Trzeba by było dodać kilka funkcji LICZ.JEŻELI, ale można to zrobić na dwa sposoby. Prostszy sposób to napisać to tak:
=LICZ.JEŻELI(A2:A7;"HR")+LICZ.JEŻELI(A2:A7;"Zarządzanie")
Możesz również skorzystać z tablicy i napisać tę formułę tablicową:
=SUMA(LICZ.JEŻELI(A2:A7; {"HR";"Zarządzanie"}))
Notatka: Formuły tablicowe należy potwierdzać za pomocą kombinacji klawiszy Ctrl+Shift+Enter, a nie tylko klawisza Enter.
Jak ta formuła będzie działać, zobaczy, że jako dane wejściowe podałeś tablicę. W ten sposób obliczy wynik dla dwóch różnych funkcji LICZ.JEŻELI i zapisze je w tablicy. Funkcja SUMA zsumuje następnie wszystkie wyniki z naszej tablicy, tworząc jedno wyjście. Zatem nasza formuła będzie oceniana w następujący sposób:
=SUMA(LICZ.JEŻELI(A2:A7; {"HR", "Zarządzanie"})) =SUMA({2, 3}) =5
Policz unikalne wartości
Teraz, gdy widzieliśmy, jak używać tablicy z funkcją LICZ.JEŻELI, możemy pójść o krok dalej, aby pomóc nam policzyć, ile unikalnych wartości znajduje się w zakresie. Najpierw spójrzmy ponownie na naszą listę działów.
=SUMA(1/LICZ.JEŻELI(A2:A7;A2:A7))
Widzimy, że istnieje 6 komórek danych, ale są tylko 3 różne pozycje. Aby matematyka zadziałała, każdy przedmiot musi być wart 1/N, gdzie N to liczba powtórzeń przedmiotu. Na przykład, jeśli każdy HR był wart tylko 1/2, to po ich zsumowaniu uzyskasz liczbę 1 dla 1 unikalnej wartości.
Wróćmy do naszej funkcji LICZ.JEŻELI, która służy do obliczania, ile razy dany element pojawia się w zakresie. W D2 napiszemy formułę tablicową
=SUMA(1/LICZ.JEŻELI(A2:A7; A2:A7))
Jak ta formuła będzie działać, dla każdej komórki w zakresie A2: A7 sprawdzi, ile razy się pojawia. W naszym przykładzie da to tablicę
{2, 2, 3, 3, 3, 1}
Następnie zamieniamy wszystkie te liczby na ułamki, dokonując dzielenia. Teraz nasza tablica wygląda tak:
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Kiedy je zsumujemy, otrzymamy pożądany wynik 3.
Odliczanie z dwoma lub wieloma warunkami - funkcja odliczania
Do tej pory pracowaliśmy tylko z funkcją LICZ.JEŻELI. Funkcja LICZ.JEŻELI może obsługiwać tylko jedno kryterium naraz. Aby LICZ.JEŻELI z wieloma kryteriami musisz użyć funkcji LICZ.JEŻELI. LICZ.JEŻELI zachowuje się dokładnie tak samo, jak LICZ.JEŻELI. Po prostu dodajesz dodatkowe kryteria. Rzućmy okiem na poniższy przykład.
=LICZ.WARUNKI(B2:B7;"=130")
LICZ. I LICZ. w Arkuszach Google
Funkcja LICZ.JEŻELI i LICZ.JEŻELI działa dokładnie tak samo w Arkuszach Google, jak w programie Excel:
