Dzień dobry wszystkim, dzisiaj otwieram sekcję „Funkcje” i zacznę od funkcji LICZ.JEŻELI. Szczerze mówiąc, nie bardzo chciałem, bo o funkcjach można przeczytać po prostu w pomocy Excela. Ale potem przypomniałem sobie moje początki w Excelu i zdałem sobie sprawę, co było potrzebne. Dlaczego? Istnieje kilka powodów:

1. Funkcji jest wiele i użytkownik często po prostu nie wie czego szuka, bo... nie zna nazwy funkcji.
2. Funkcje są pierwszym krokiem do ułatwienia życia w Excelu.

Wcześniej, zanim poznałem funkcję LICZ.JEŻELI, dodałem nowa kolumna, ustaw funkcję JEŻELI i następnie zsumuj tę kolumnę.

Dlatego dzisiaj chciałbym porozmawiać o tym, jak znaleźć liczbę komórek spełniających określone kryterium bez zbędnych ruchów. Zatem sam format funkcji jest prosty:

LICZ.JEŻELI("Zakres","Kryteria")

Jeśli przy pierwszym argumencie jest to mniej więcej jasne, można podstawić zakres np. A1:A5 lub samą nazwę zakresu, to przy drugim już nie jest tak bardzo, bo możliwości określenia kryterium są dość szerokie i są często nieznane tym, którzy nie spotykają się z wyrażeniami logicznymi.

Najbardziej proste formaty„Kryteria”:

• Komórka ściśle o określonej wartości, możesz umieścić wartości („jabłko”), (B4), (36). Sprawa nie jest drażliwa, ale równa dziwna przestrzeń uwzględni już komórkę w zliczeniu.
• Mniej więcej od określonej liczby. Tutaj już zastosowano znak równości, a raczej nierówności, a mianowicie (">5");("<>10");("<=103").

Ale czasami potrzebujemy bardziej szczegółowych warunków:

• Czy jest tekst? Choć ktoś może powiedzieć, że funkcja zlicza już tylko niepuste komórki, to jeśli ustawimy warunek („*”), to przeszukiwany będzie tylko tekst, a liczby i spacje nie będą brane pod uwagę.
• Większa (mniejsza niż) średnia wartość zakresu: (">"&ŚREDNIA(A1:A100))
• Zawiera określoną liczbę znaków, na przykład 5 znaków:(”?????")
• Pewny tekst zawarte w komórce: („*słońce*”)
• Tekst zaczynający się od określonego słowa: („Ale*”)
• Błędy: („#DZIEL/0!”)
• Wartości logiczne („TRUE”)

Jeśli masz kilka zakresów, każdy z własnym kryterium, musisz użyć funkcji LICZ. Jeśli jest jeden zakres, ale jest kilka warunków, najłatwiej jest podsumować: Istnieje bardziej złożona, choć bardziej elegancka opcja - użycie formuły tablicowej:

„Oczy się boją, ale ręce działają”

Nawigacja po wpisach

Funkcja LICZ.JEŻELI: zliczanie liczby komórek według określonego kryterium w Excelu: 55 komentarzy

1. plama

   Powiedz mi, jak mogę znaleźć zduplikowane wartości bez uwzględniania wielkości liter?

2. Admin Autor posta

   Teoretycznie funkcja COUNTIF jest dokładnie tym, co szuka dopasowań, niezależnie od wielkości liter.

3. plama

   Rzecz w tym, że go nie znajduje – nie rozróżnia kapitału i małe litery i łączy je w całkowitą liczbę dopasowań...
   Powiedz mi, może powinienem umieścić jakiś symbol podczas wyszukiwania? (apostrofy i cudzysłowy nie pomagają)

4. Igor

   Jak ustawić warunek w programie Excel, aby zliczał określony zakres komórek w wierszach z określoną wartością w pierwszej kolumnie?

5. Admin Autor posta

   Właściwie Igor, COUNTS, właśnie to robi. Prawdopodobnie lepiej będzie, jeśli określisz zadanie.

6. Ania

   Podobało mi się, ale niestety coś nie wyszło, muszę podsumować różne liczby powtarzające się liczby, na przykład 123 234 345 456 Muszę policzyć, ile „1”, „2”, „3” itd. w tych liczbach, czyli tak, aby formuła rozpoznała te same liczby i je policzyła, jeśli to możliwe, napisz, co zrobić? Będę bardzo czekać. Z poważaniem, Anna Irikovna

7. Admin Autor posta

   Hm. byłoby miło zobaczyć przykład
   Ale bez tego mogę Ci podpowiedzieć - utwórz obok niej kolumnę, w której będziesz wybierać liczby grupami za pomocą formuły tekstowej. Na przykład Rightsim(A1;1). Następnie przerobić LICZ.JEŻELI w tej kolumnie.

8. Aleks

   Jak rozłożyć formułę w kolumnie, aby zakres formuły pozostał taki sam, ale zmieniło się kryterium?

9. Admin Autor postu

   LICZ.JEŻELI($A$1:$A$100,B1)

10. Ania

    Przykładem jest to, 23.12.1972, czyli to jest data urodzenia, potrzebuję zsumować liczbę dwójek, czyli nie 2+2+dwa, ale żeby były tylko trzy dwójki, to znaczy, że ostatnia komórka powinna zawierać 3, jedności 2, 3 7 9 pojedynczo, czy to możliwe? Właśnie rozwaliłem sobie głowę, jestem samoukiem, ale takie formuły są dla mnie trochę skomplikowane, jeśli nie jest to dla Ciebie bardzo trudne, proszę o próbkę wzoru w całości, przynajmniej dla jednej liczby, z szacunkiem , Anna Irikowna

11. Admin Autor posta

    Wyobraźmy sobie, że Twoja data znajduje się w komórce A1.

    Następnie liczymy, ile dwójek: = dstr(A1)-dstr(SUBSTITUTE(A1;"2″;""))

    Jeśli dotyczy to kilku liczb, lepiej na końcu zamieścić przykład tego, jak chcesz, w przeciwnym razie opcji jest wiele.

12. Ania

    dziękuję, wypróbuję teraz formułę... Spróbuję sama, jeśli w ogóle nie wyjdzie, to z wielkim ukłonem poproszę o dalsze rady)) Z poważaniem, Anna Irikovna

13. Ania

    nie działa... Czy mogę wysłać Ci to, czego potrzebuję adres e-mail? To tylko stół.. ciężko mi to opisać... Anna I.

14. Ania
15. Maks

    Dobry artykuł) Ale nadal nie mogłem zrozumieć swojego zadania. Mam 3 kolumny, jedna to Uczniowie, druga to Nauczyciele, trzecia to Oceny. Powiedz mi, jak policzyć liczbę studentów studiujących u Iwanowej, którzy otrzymali pozytywne oceny? Wygląda na to, że są 2 zakresy i 2 kryteria, ale nie rozumiem)

16. Admin Autor posta

    Użyj COUNTIFS

17. Majkot

    Dzień dobry.
    Pomóż mi ustalić zakres.
    Mam komórkę A2, która zawiera wartość tekstowa- na przykład „słońce”.
    Komórka A3 zawiera wartość „morze”. Itp.
    Jak poprawnie wpisać do formuły =SUMIF(C:C;"*słońce*";D:D) zamiast określonego zakresu („*słońce*”) zawartość komórki A2, czyli: nie =SUMIF(C:C;"*słońce*";D:D), ale zamiast „*słońce*” było odwołanie do komórki?

18. Admin Autor posta

    SUMA.JEŻELI(C:C;»*»&$A$1&»*»;D:D)

19. Dmitrij
20. Aleksander

    Cześć! Proszę o informację jak przedstawić formułę w Excelu:
    Skorygowany koszt =
    = Koszt * (K1 + K2 + ... + KN – (N – 1);
    Gdzie:
    K1, K2, KN - współczynniki różne od 1
    N – liczba współczynników różna od 1.

21. Aleksander

    Cześć! Proszę o informację jak zastosować wzór:
    =SUMA JEŚLI(C3:C14;”<1")-(СЧЁТЕСЛИ(C3:C14;"<1")-1)
    ustaw zakres wartości współczynników inny niż 1 (mniejszy niż 1, większy niż 1, ale mniejszy niż 2).

22. Michael

    Dobry wieczór!
    Powiedz mi, jak policzyć liczbę komórek, w których wskazana jest data? Oznacza to, że w kolumnie znajdują się komórki z datami (różne) i są komórki z tekstem (różne), muszę policzyć liczbę komórek z datami.
    Dziękuję!

23. Admin Autor posta

    LICZ.JEŻELI(F26:F29;"01.01.2016″)
    Czy to zadziała?

24. Julia

    Dobry wieczór! Podaj mi formułę, która liczy tylko liczby większe niż 8 (przeróbka w arkuszu czasu pracy). Oto zła opcja: =SUMIF(C42:V42;">8″)+SUMIF(C42:V42)

25. Admin Autor posta

    SUMA JEŻELI(C42:V42,">8″) =SUMA JEŻELI(C1:C2,">8″)

    Nie zapominaj, że wersja angielska używa przecinków między argumentami.

26. D.N.

    Dzień dobry Dziękujemy za formułę tablicową dla zakresu z wieloma kryteriami!
    Mam prawdopodobnie głupie pytanie, ale jak zastąpić tekst (1;2;3) odniesieniami do komórek zawierających wartości tekstowe.
    to znaczy, jeśli wprowadzę („X”; „Y”; „Z”) wszystko jest w porządku
    ale przy wpisywaniu (A1;A2;A3) pojawia się błąd,
    czy to nawiasy klamrowe?)

27. Witalij

    Dziękuję. Artykuł był bardzo pomocny.

28. Denis

    Dzień dobry. Napotkałem taki problem w funkcji COUNTIFS. Po wprowadzeniu 2 zakresów wszystko się liczy, ale po dodaniu trzeciego pojawia się błąd. Czy może być jakiś haczyk w liczbie komórek?
    Mam =COUNTIFS('kształcenie w pełnym wymiarze godzin'!R11C13:R250C13;"tak"; 'kształcenie w pełnym wymiarze godzin'!R11C7:R250C7;"budżet"; "kształcenie w pełnym wymiarze godzin"!R16C9:R30C9;"tak")
    Bez trzeciego zakresu i warunków wszystko jest w porządku.
    Z góry dziękuję.

29. Admin Autor posta

    Denis, wyrównaj zakresy, wszystkie powinny być takie same i możesz ustawić do 127 zestawów warunków.

30. AK

    Witam, proszę o pomoc w rozwiązaniu tego problemu.
    Są dwie kolumny: jedna to data, druga to godzina (format 00:00:00).
    Należy wybrać daty odpowiadające konkretnemu okresowi czasu.
    Jednocześnie powinny być 4 takie odstępy, tj. co 6 godzin.
    Czy można to ustawić w jednej formule i jeśli tak to w jakiej?

31. Admin Autor posta

    Dzień dobry.
    Oczywiście, że możesz. To prawda, nie rozumiem, potrzebujesz tego według dat czy godzin? Dwie różne formuły. A jak chcesz to rozbić? Aby odstępy były oznaczone liczbami? Jak pierwsze 6 godzin dnia to 1, drugie -2 itd.?

32. Andriej

    Z góry dziękuję, ale pytanie brzmi:
    dany:
    wiele linii, jedna kolumna
    zielony oznacza gotowe pakiety dokumentów, biały oznacza niedokończone
    zawartość komórek: różne nazwiska pracowników.
    zadanie: jak skorzystać ze wzoru, aby utworzyć tabelę: ile gotowych opakowań ma każdy pracownik?

33. Admin Autor posta

    Niestety koloru nie określa formuła. Jest to zdefiniowane bardziej precyzyjnie, ale tam trzeba napisać niestandardową formułę
    Ja bym tak zrobił – przefiltrował według koloru zielonego i w następnej kolumnie umieścił „skończone”, a następnie dodał „niedokończone” – białe komórki.
    Następnie po COUNTIF znalazłem wszystko, czego potrzebowałem.

34. Przechodzić

    Cześć! Pytanie jest takie. Istnieją 4 kolumny, które odpowiednio wskazują uczniów (kolumna A), numer szkoły (kolumna B), wyniki z chemii (kolumna C), wyniki z fizyki (kolumna D). Musisz znaleźć liczbę uczniów z określonej szkoły (na przykład 5), którzy zdobyli więcej punktów z fizyki niż z chemii. Wszystkich uczniów jest 1000. Czy można użyć jednego wzoru, aby odpowiedzieć na pytanie? Próbuję użyć COUNTIFS, ale to nie działa.

35. Admin Autor posta

    Nie, przed użyciem Countifs będziesz musiał dodać kolejną kolumnę, w której za pomocą JEŻELI będziesz mógł określić tych, którzy mają więcej punktów z fizyki niż z fizyki, a następnie użyć COUNTIFS.

36. Przechodzić

    Dobrze dziękuję

37. Aleksander

    Dzień dobry Generalnie zadanie jest takie. Tabela wskazuje nazwę wiersza po wierszu, sport i kategorię. Jak mogę utworzyć tabelę na innej karcie, która automatycznie zlicza liczbę zwolnień w każdym sporcie i jakie konkretne zwolnienia?

38. Aliona

    Cześć! Potrzebna jest pomoc.
    Istnieje kolumna z datami urodzenia w formacie 19740815, ale należy ją przekonwertować na format 15.08.1974
    Z góry dziękuję.

39. Admin Autor posta

    Dzień dobry.

    No cóż, najprościej jest Tekst w kolumnach - stała szerokość (4-2-2) - następnie dodaj kolumnę z funkcją DATA.

40. Admin Nadesłane przez Alinę

    Muszę ustawić liczbę pracowników na dany miesiąc, dokonuję selekcji według kolumny miasta i daty, ale coś robię źle.
    Powiedz mi, co trzeba naprawić?

    LICZBY('Surowe dane'!B2:B150;"Bukareszt";'Surowe dane'!E1:E150;"06.11.2014″)

41. Wiara

    Dzień dobry. Powiedz mi, jakie kryterium należy umieścić w formule SUMIF, jeśli chcesz policzyć liczbę komórek zawierających liczby z zakresu zawierającego zarówno cyfry, jak i litery.
    Dziękuję.

42. Admin

    Dzień dobry wszystkim!
    Istnieje rzeczywisty harmonogram odjazdów pracowników, wszystkie godziny pracy zapisane są w formacie „09*21″ – pełna zmiana dobowa i „21*09″ – pełna zmiana nocna.
    Zdarzają się również dni z niepełnymi zmianami, które wliczane są do wynagrodzenia według stawki godzinowej, np. „18*23″ itp.
    Format wszystkich komórek to tekst.

    Konieczne jest, aby wzór obliczył dla każdej linii (odpowiednio dla każdego pracownika) liczbę pełnych zmian w miesiącu, najlepiej jeśli uwzględnia kryteria „09*21″+”21*09″, ale jest to również możliwe dla jednego kryterium, to po prostu użyję tych kolumn, ukryję je i połączę w sumę.

    Poprzez =count, jeśli próbowałem, w oknie formuły oblicza wartość poprawnie, ale w samej komórce wyświetla głupio napisaną formułę, niezależnie od tego, jaki format ustawię - to nie pomaga.
    Próbowałem zamienić 09*21 na 09:00 - 21:00 odpowiednio w komórkach i formule, ale to też nie zadziałało.
    W formule umieściłem zarówno „09*21*”, jak i „*09*21*” – bez skutku.

    Jeśli uda Ci się coś takiego zrobić, pod warunkiem, że nagrane zostanie „09:00 - 21:00”, to ogólnie super, łatwiej będzie mi odśnieżyć jeden miesiąc, ale wtedy wszystko będzie gładko)
    i od razu pojawia się pytanie - czy istnieje wzór, dzięki któremu będzie można obliczyć całkowitą liczbę godzin w przedziale o dowolnych wartościach („18:00 - 23:00″, „12:45 - 13 :45″ itd.), z wyjątkiem powyższych „09:00 — 21:00” i „21:00 — 09:00” lub policzyć wszystkie komórki, w których liczba godzin wynosi 12 i osobno wszystkie komórki, w których liczba godzin godzin jest mniejsza niż 12.

    Z góry bardzo dziękuję, męczę się z tym już tydzień!(((

43. Admin Autor posta

    Spróbuj COUNTIF($A$1:A10;A10) - wstaw do komórki B10.

W różnych przypadkach może być konieczne policzenie liczby komórek w programie Excel. W tym artykule przyjrzymy się, jak policzyć bloki o określonych wartościach, puste i spełniające określone warunki. W tym celu użyjemy następujących funkcji: LICZBA, LICZ.JEŻELI, LICZ., LICZ.PUSTE.

Zakończony

Najpierw spójrzmy na najprostszą metodę. Jeśli chcesz policzyć ilość wypełnionych bloków w Excelu, zaznacz zakres komórek w dokumencie - są różne metody, poczytaj o nich klikając w link - i w "Pasek stanu" zwróć uwagę na pozycję „Ilość”. Zostanie tam zapisana liczba niepustych bloków: wszystkie z tekstem i liczbami są brane pod uwagę, ale puste nie.

Jeśli chcesz policzyć bloki w tabeli wypełnionej określonymi wartościami i użyć tej liczby we wzorach do obliczeń, ta metoda nie będzie działać, ponieważ dane w tabeli mogą się okresowo zmieniać. Przejdźmy więc do rozważenia funkcji.

Gdzie są wpisywane liczby?

Funkcja COUNT – zlicza bloki wypełnione wyłącznie wartościami numerycznymi. Wybierz H1, wstaw „=”, wpisz funkcję „COUNT”. Określ żądany zakres jako argument funkcji (F1:G10). Jeśli jest kilka zakresów, oddziel je znakiem „;” – (F1:G10;B3:C8) .

W sumie zapełnionych jest 20 bloków. Te zawierające tekst nie były liczone, ale liczone były te wypełnione datą i godziną.

Z określonym tekstem lub znaczeniem

Na przykład istnieje tabela pokazująca, ile kilogramów danego produktu sprzedawano dziennie. Obliczmy, ile sprzedano produktów o wadze powyżej 5 kilogramów. Aby to zrobić, musisz policzyć, ile bloków znajduje się w kolumnie Waga, gdzie wartość jest większa niż pięć. Funkcja będzie wyglądać następująco: =LICZ.JEŻELI(B2:B13,”>5″). Obliczy liczbę bloków zawierających więcej niż pięć treści.

Aby rozszerzyć funkcję na inne bloki i, powiedzmy, zmienić warunki, musisz naprawić wybrany zakres. Możesz to zrobić za pomocą linków bezwzględnych w Excelu.

– liczba komórek z wartościami ujemnymi: =LICZ.JEŻELI(B2:B13;"<0″) ;
– liczba bloków, których zawartość jest większa (mniejsza) niż w A10 (przykładowo): =LICZ.JEŻELI(B2:B13,”>”&A10);
– komórki, których wartość jest większa od 0: =LICZ.JEŻELI(B2:B13,”>0″);
– niepuste bloki z przydzielonego zakresu: =LICZ.JEŻELI(B2:B13;"<>») .

Możesz także użyć funkcji LICZ.JEŻELI, aby obliczyć w programie Excel komórki zawierające tekst. Na przykład obliczmy, ile owoców jest na stole. Wybierzmy obszar i jako kryterium określmy „owoce”. Zliczone zostaną wszystkie bloki zawierające podane słowo. Nie możesz pisać tekstu, ale po prostu zaznacz prostokąt, który go zawiera, na przykład C2.

W przypadku formuły COUNTIF wielkość liter nie ma znaczenia; zliczone zostaną komórki zawierające tekst „Owoc” i „owoc”.

Jako kryterium możesz także użyć znaków specjalnych: „*” i „?” . Dotyczą one wyłącznie tekstu.

Policzmy, ile produktów zaczyna się na literę A: „A*”. Jeśli wpiszesz „morela*”, wówczas pod uwagę zostaną wzięte wszystkie produkty zaczynające się na „morela”: sok morelowy, dżem morelowy, ciasto morelowe.

Symbol "?" Możesz zastąpić dowolną literę w słowie. Wpisując w kryteriach „f?ukt”, pod uwagę będą brane słowa „owoc”, „fuukt”, „fuukt”.

Aby policzyć słowa w komórkach składających się z określonej liczby liter, umieść w rzędzie znaki zapytania. Aby policzyć produkty, które mają w nazwie 5 liter, jako kryterium ustalamy „?????”. .

Jeśli jako kryterium ustawisz gwiazdkę, zliczone zostaną wszystkie bloki zawierające tekst z wybranego zakresu.

Z wieloma kryteriami

Funkcję COUNTIFS stosujemy, gdy trzeba podać kilka warunków, maksymalna ich liczba w Excelu wynosi 126. Jako argument: ustawiamy pierwszy zakres wartości i wskazujemy warunek, oddzielając je znakiem „;”. ustawiamy drugi zakres i piszemy dla niego warunek - = LICZBY(B2:B13,”>5″,C2:C13,„owoce”).

W pierwszym zakresie określiliśmy, że ma to być więcej niż pięć kilogramów, w drugim określiliśmy, że powinien to być owoc.

Puste bloki

Obliczenie liczby komórek zawierających wartości tekstowe lub liczbowe w Excelu nie jest takie trudne. Użyj do tego funkcji specjalnych i ustaw warunki. Za ich pomocą możesz policzyć zarówno puste bloki, jak i te, w których zapisane są określone słowa lub litery.

Oceń ten artykuł:

Załóżmy, że chcesz określić, ile razy dany tekst lub liczba pojawia się w zakresie komórek. Na przykład:

Jeśli zakres (na przykład a2:D20) ma wartości liczbowe 5, 6, 7 i 6, to liczba 6 występuje w dwóch wartościach.

Jeśli kolumna ma wartości „Baturin”, „Belov”, „Belov” i „Belov”, wówczas „Belov” ma trzy wartości.

Zliczanie liczby wystąpień pojedynczej wartości za pomocą funkcji LICZ.JEŻELI

Skorzystaj z funkcji LICZ.JEŻELI aby dowiedzieć się, ile razy określona wartość pojawia się w zakresie komórek.

Więcej informacji można znaleźć w artykule Funkcja LICZ.JEŻELI.

Policz liczbę wystąpień na podstawie wielu kryteriów, korzystając z funkcji COUNTIFS

Funkcjonować LICZBY podobny do funkcji LICZ.JEŻELI z jednym ważnym wyjątkiem: COUNTIFS umożliwia zastosowanie kryteriów do komórek w wielu zakresach i zlicza liczbę dopasowań do każdego kryterium. Dzięki funkcji COUNTIFS można zastosować do 127 par zakresów i kryteriów.

Składnia funkcji COUNTIFS jest następująca:

LICZBY(zakres_warunku1;warunek1;[zakres_warunku2;warunek2];…)

Zobacz przykład poniżej.

Więcej informacji na temat używania tej funkcji do zliczania wystąpień w wielu zakresach i z wieloma warunkami można znaleźć w artykule o funkcji COUNTIFS.

Policz liczbę wystąpień na podstawie warunków, używając funkcji COUNT i JEŻELI

Załóżmy, że chcesz ustalić, ilu sprzedawców sprzedało określony przedmiot w określonym regionie, lub chcesz dowiedzieć się, ile sprzedaży przetworzono o określonej wartości dokonanej przez konkretnego sprzedawcę. Możesz skorzystać z funkcji Jeśli I sprawdzać razem. oznacza to, że funkcja jest używana jako pierwsza Jeśli aby sprawdzić warunek, a następnie, jeśli wynik funkcji if jest prawdziwy, funkcja ta jest używana do zliczania komórek sprawdzać .

Uwagi:

Więcej informacji na temat tych funkcji można znaleźć w artykułach Funkcja COUNT i Funkcja JEŻELI.

Policz liczbę wystąpień wielu wartości tekstowych i liczbowych za pomocą funkcji SUMA i JEŻELI

W poniższych przykładach funkcji JEŚLI I SUMA są używane razem. Funkcjonować JEŚLI najpierw sprawdza wartości w określonych komórkach, a następnie, jeśli zwrócona zostanie wartość TRUE, funkcję SUMA dodaje wartości spełniające warunek.

Uwagi: Formuły w tym przykładzie należy wprowadzić jako formuły tablicowe.

Przykład 1

Powyższa funkcja oznacza, że ​​jeśli zakres C2:C7 zawiera wartości Szaszkow I Tumanow, wówczas funkcja SUMA powinna wyświetlić sumę rekordów, w których warunek jest spełniony. Formuła znajdzie w tym zakresie trzy wpisy dla „Warcaby” i jeden dla „Mgły” i wyświetlacz 4 .

Przykład 2

Powyższa funkcja oznacza, że ​​jeśli komórka D2:D7 zawiera wartości mniejsze niż 9 000 ₽ lub większe niż 19 000 ₽, to funkcja SUMA powinna wyświetlić sumę wszystkich rekordów, w których warunek jest spełniony. Formuła znajdzie dwa rekordy D3 i D5 o wartościach mniejszych niż 9 000 ₽, a następnie D4 i D6 o wartościach większych niż 19 000 ₽ i wyświetli 4 .

Przykład 3

Powyższa funkcja mówi, że D2:D7 zawiera konta Baturina o wartości mniejszej niż 9000 dolarów, a suma powinna odzwierciedlać sumę wpisów, gdzie jest spełniona. Formuła znajdzie komórkę C6 pasującą do warunku i wyświetlacza 1 .

Zliczanie liczby wystąpień wielu wartości za pomocą tabeli przestawnej

Tabeli przestawnej można używać do wyświetlania sum i zliczania liczby iteracji w tabeli przestawnej. Tabela przestawna to interaktywny sposób szybkiego podsumowywania dużych ilości danych. Można go używać do rozwijania i zwijania warstw widoków danych, aby uzyskać szczegółowe informacje o wynikach i przejść do podsumowań danych dotyczących interesujących ich kwestii. Można także przenosić wiersze do kolumn lub kolumny do wierszy („przestawiać” je), aby wyświetlić liczbę wystąpień wartości w tabeli przestawnej. Rozważmy przykładowy arkusz kalkulacyjny Sprzedaż, w którym można policzyć liczbę wartości sprzedaży dla sekcji Golf i Tenis dla poszczególnych kwartałów.

Praca z projektem VB (12)
Formatowanie warunkowe (5)
Listy i zakresy (5)
Makra (procedury VBA) (63)
Różne (39)
Błędy i usterki Excela (3)

Jak policzyć liczbę powtórzeń

W tym artykule chcę powiedzieć, jak policzyć liczbę powtórzeń wartości w tabeli lub komórce. Zacznijmy od porządku. Tam jest stół:

I konieczne jest policzenie liczby powtórzeń każdego imienia:

Co dziwne, jest to dość łatwe: Excel ma funkcję - LICZ.JEŻELI, dzięki któremu możesz to wszystko zrobić dosłownie w kilka sekund. Jeżeli w kolumnie B tabeli ma być wyświetlona liczba powtórzeń każdej nazwy, a same nazwy znajdują się w kolumnie A:
=LICZ.JEŻELI($A$2:$A$30, A2)
Zakres($A$2:$A$30) - wskaż komórki zakresu, w których zapisane są wartości, których liczbę należy policzyć. Główna cecha: ten argument może być jedynie odwołaniem do komórki lub zakresu komórek. Niedopuszczalne jest określanie dowolnej tablicy wartości.
Kryterium(A2) - wskazuje odwołanie do komórki lub bezpośrednio wartość do obliczenia. Te. Możesz także określić to w ten sposób: =COUNTIF($A$2:$A$30 ;"Apple") . Ponadto można używać znaków wieloznacznych: ? I *. Te. Określając „*banan*” jako kryterium, możesz policzyć liczbę komórek, w których pojawia się słowo „banan” (banan, banan, banany, sok bananowy, sto bananów, trzy banany i orzech itp.). Oraz poprzez określenie „banana*” – znaczenia rozpoczynającego się od „banana” (banany, sok bananowy, gaj bananowy itp.). „?” - zastępuje tylko jeden znak, tj. Określając „ban?n” możesz liczyć wiersze o wartości „banan” i o wartości „banon” itp. Jeśli jako kryterium podasz =COUNTIF($A$2:$A$30 ;"*"), wówczas zliczone zostaną wszystkie wartości tekstowe. Wartości numeryczne są ignorowane. Tych znaków wieloznacznych (* i?) nie można stosować do wartości numerycznych - tylko do tekstu. Te. jeśli jako kryterium podasz „12*”, wówczas liczby będą wynosić 1234, 123, 120 itd. nie będą liczone. Aby policzyć wartości liczbowe, użyj operatorów porównania: =COUNTIF($A$2:$A$30 ;">12")

Jak widać na drugim rysunku, imiona się tam nie powtarzają, chociaż w tabeli są zapisane wszystkie razem. Nie będę się na tym skupiał - opisałem to już w artykule Jak uzyskać listę unikalnych (niepowtarzających się) wartości? a jeśli zajdzie taka potrzeba, możesz zastosować dowolną opisaną w nim metodę.

Jeśli chcesz policzyć liczbę powtórzeń na podstawie kilku warunków (wartości), to począwszy od Excela 2007 można to łatwo zrobić za pomocą funkcji LICZ. Składnia funkcji jest prawie taka sama jak COUNTIF, tyle że jest więcej warunków i zakresów:
= LICZBY($A$2:$A$30, A2, $B$2:$B$30, B2)
zakłada się, że warunki są zapisane w kolumnie B
Zasadniczo jest to tylko lista:
=COUNTIFS(Zakres_Warunku1, Warunek1,Zakres_Warunku2, Warunek2,Zakres_Warunku3, Warunek3 itd.)

Ale. Zdarzają się przypadki, gdy lista w ogóle nie znajduje się w tabeli, ale w jednej komórce ($D$1):
Melon Kiwi Gruszka Jabłko Melon Gruszka Gruszka Arbuz Jabłko Banan Jabłko Banan Jabłko Jabłko Melon Melon Kiwi Banan Melon Arbuz Melon Kiwi Jabłko Melon Gruszka Jabłko Kiwi Arbuz
Tutaj COUNTIF zdecydowanie nie pomoże. Ale Excel jest pełen innych funkcji i wszystko można zrobić równie prosto:
=(DŁUGOŚĆ($D$1)-DŁUGOŚĆ(ZAMIENNIK($D$1 ; D3 ;"")))/DŁUGOŚĆ(D3)
DLST- zlicza liczbę znaków w określonej komórce/wierszu($D$1 , D3)
ZASTĄPIĆ(tekst; stary_tekst; nowy_tekst) - zastępuje określony znak w określonym tekście dowolną inną określoną wartością. Domyślnie zastępuje wszystkie powtórzenia określonego znaku. Na tym właśnie opiera się algorytm. Na przykładzie wartości Banan(D3) przeanalizujemy krok po kroku wzór:

• za pomocą funkcji DŁUGOŚĆ otrzymujemy liczbę znaków w wierszu z tekstem źródłowym($D$1) =(170-LENGTH(SUBSTITUTE($D$1 ; D3 ;"")))/LENGTH(D3) ;
• Korzystając z funkcji SUBSTITUTE, zamieniamy wszystkie wartości Banana(D3) w wierszu z tekstem źródłowym ($D$1) na puste, a używając LENGTH otrzymujemy numer symbolu ciągu znaków po tej zamianie =(170- 155)/DŁUGOŚĆ(D3) ;
• od całkowitej liczby znaków odejmij liczbę znaków w wierszu po zamianie i wynik pomnóż przez liczbę znaków w kryterium =(170-155)/5.

Otrzymujemy liczbę 3. Właśnie tego potrzebowaliśmy.

Czy artykuł pomógł? Udostępnij link swoim znajomym! Lekcje wideo

(„Dolny pasek”:(„textstyle”: „statyczny”, „textpositionstatic”: „bottom”, „textautohide”: true, „textpositionmarginstatic”: 0, „textpositiondynamic”: „bottomleft”, „textpositionmarginleft”: 24”, textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedance" :30, „texteffectdelay”: 500, „texteffectseparate”: fałsz, „texteffect1”: „slajd”, „texteffectslidedirection1”: „right”, „texteffectslidedistance1”: 120, „texteffecteasing1”: „easeOutCubic”, „texteffectduration1”: 600 „texteffectdelay1”: 1000, „texteffect2”: „slajd”, „texteffectslidedirection2”: „right”, „texteffectslidedistance2”: 120, „texteffecteasing2”: „easeOutCubic”, „texteffectduration2”: 600, „texteffectdelay2”: 1500”, textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; pozycja:absolutna; góra:0px; lewa:0px; szerokość:100%; wysokość:100% ; kolor tła:#333333; krycie:0,6; filtr:alfa(opacity=60);","titlecss":"display:block; pozycja:względna; czcionka: pogrubiona 14 pikseli „Lucida Sans Unicode”, „Lucida Grande”, bezszeryfowa, Arial; kolor:#fff;","opiscss":"display:blok; pozycja:względna; czcionka: 12px „Lucida Sans Unicode”, „Lucida Grande”, bezszeryfowa, Arial; kolor:#fff; margines-górny:8px;","buttoncss":"display:block; pozycja:względna; margines-top:8px;","texteffectResponse":true,"texteffectResponsesize":640,"titlecssResponse":"rozmiar czcionki:12px;","descriptioncssResponse":"display:brak !important;","buttoncssResponse": "","addgooglefonts":false"googlefonts":"","textleftrightpercentforstatic":40))

Przykład 1: Dane paska stanu

Liczba wierszy w wybranym zakresie jest wyświetlana w obszarze paska stanu. Wybierz żądany zakres Wartości Excela automatycznie obliczy liczbę wierszy, które będą wyświetlane na pasku stanu. Trzeba pamiętać, że w w tym przypadku Liczone są tylko komórki z wartościami. W poniższym przykładzie, aby obliczyć liczbę wierszy, należy wybrać jedną kolumnę, a w dolnej części okna zostanie wyświetlona liczba pozycji w wybranym zakresie.

Jest ich więcej trudne sytuacje. Na przykład, jeśli kolumny nie są całkowicie wypełnione, tj. w kolumnie są puste pozycje. Jeśli wybierzesz tylko kolumnę, puste linie nie będą liczone. Spójrzmy na poniższy przykład. Puste komórki z pierwszej kolumny odpowiadają wartościom komórek drugiej. Zaznaczmy w całości pierwszą kolumnę i przytrzymując klawisz „Ctrl” klikamy na odpowiednie komórki z wartościami z drugiej kolumny. W dolnej części okna zostanie wyświetlona cała liczba wierszy wybranego zakresu, w którym komórki nie zawierają wartości.

Jeśli zrobiłeś wszystko jak opisano powyżej, ale ilość nie jest wyświetlana, oznacza to, że musisz włączyć odpowiednią opcję. Aby to zrobić, kliknij kliknij prawym przyciskiem myszy myszką na pasku stanu i wybierz „Ilość”.

Przykład 2: Obliczenia przy użyciu wzorów

Omówiony powyżej przykład nie pozwala na zapisanie wyników obliczeń i wyświetlenie ich w komórce arkusza. Ponadto często konieczne jest uwzględnienie pustych pozycji. Aby to zrobić, wygodnie jest użyć formuły LOTS.

Formuła wygląda następująco: =String(tablica wartości).

Dzięki tej funkcji możesz wstawić żądaną komórkę i zamiast tablicy określić zakres komórek, dla których chcesz obliczyć liczbę pozycji.

Następnie naciśnij Enter.

Podczas korzystania z tej formuły brane są pod uwagę również wiersze bez wartości. Dodatkowo, nawet jeśli zaznaczony obszar zawiera więcej niż jedną kolumnę, funkcja zliczy tylko wiersze.

Dla niektórych użytkowników wygodniejsze będzie użycie tej formuły za pomocą Konstruktora formuł.

1) Ustaw kursor w miejscu, w którym będziemy wstawiać formułę, czyli tam, gdzie wyświetli się wynik i kliknij ikonę „Wstaw formułę”.

2) Otworzy się okno Konstruktora formuł. W wartości „Kategoria” ustaw „Łącza i tablice”, możesz także wybrać „Pełna lista alfabetyczna”. Znajdź funkcję „CHROW” i kliknij „OK”

3) W oknie wyboru argumentów formuły umieść kursor za tablicą i wybierz obszar z żądanym zakresem wartości. Gdy w polu argumentów funkcji pojawią się komórki zakresu, kliknij OK

4) Po wykonaniu powyższych kroków w określonej pozycji Excel automatycznie obliczy liczbę wierszy w zakresie. Obliczenia będą wykonywane do momentu usunięcia formuły z komórki.

Przykład 3: Korzystanie z formatowania i filtrowania

Z więcej trudne przypadki, gdy konieczne jest zliczenie tylko pozycji spełniających określone warunki. W takich przypadkach formatowanie za pomocą dany warunek, po czym stosowane są filtry.

1) Wybierz zakres wartości, dla którego konieczne jest spełnienie warunków.

2) Idź do panel główny i wybierz „Style”, a następnie kliknij ikonę „Formatowanie warunkowe”. W formatowanie warunkowe wybierz „Reguły zaznaczania komórek” i w naszym przypadku wybierz regułę „Większe...”

3) Pojawi się okno dialogowe umożliwiające ustawienie warunków. Po lewej stronie znajduje się wartość większa od której komórki zostaną pokolorowane określony kolor. Po prawej stronie ustawiony jest kolor. Po dokonaniu wyboru kliknij „OK”

4) Jak widać poniżej, pozycje spełniające warunki są kolorowane danym kolorem. Na panelu głównym zaznacz cały zakres, kliknij pozycję menu „Sortuj i filtruj” i wybierz „Filtruj”