Jak policzyć liczbę określonych wartości​​w Excelu. Zliczanie wartości w kolumnie w Microsoft Excel

Data: 29.02.2016 Kategoria:

Cześć przyjaciele. Dziś uczymy się liczyć komórki w Excelu. Funkcje te rozwiązują szeroki zakres zadań dla przedstawicieli wielu zawodów. Wykonując obliczenia pośrednie, stają się podstawą do automatyzacji Twoich obliczeń. Znam wielu menedżerów, którzy wykorzystują funkcje liczenia do zarządzania imponującą linią produktów.

Jeśli potrzebujesz tylko znać liczbę wartości bez używania ich w obliczeniach, wygodnie jest wyświetlić dane na pasku stanu:


Liczba wartości na pasku stanu

Możesz wybrać wskaźniki wyświetlane w rzędzie, klikając je kliknij prawym przyciskiem myszy myszy.


Dostosowanie paska stanu

Jeśli potrzebujesz użyć liczby wartości w dalszych obliczeniach, skorzystaj z funkcji opisanych poniżej. Dla wygody notacji przyjmiemy, że tablica danych, dla której jest przechowywany licznik, to . W swoich formułach możesz użyć żądanego zakresu danych zamiast nazwy.

Jak policzyć liczbę komórek w programie Excel?

Istnieją dwie funkcje do liczenia liczby komórek w programie Excel:

  1. ROWS(array) – zlicza liczbę wierszy w wybranym zakresie, niezależnie od tego, czym wypełnione są jego komórki. Formuła daje wynik tylko dla prostokątnej tablicy sąsiednich komórek, w przeciwnym razie ;

Liczenie liczby linii
  1. KOLUMNA(tablica)- podobny do poprzedniego, ale zlicza liczbę kolumn tablicy

W programie Excel nie ma funkcji określania liczby komórek w tablicy, ale można to łatwo obliczyć, mnożąc liczbę wierszy przez liczbę kolumn: =LICZBA(tablica)*LICZBAKOLUMN(tablica).

Jak liczyć puste komórki w Excelu?


Liczenie pustych komórek

Funkcja uważa komórkę za pustą, jeśli nic w niej nie jest napisane lub formuła wewnątrz niej zwraca pusty ciąg.

Jak policzyć liczbę wartości i liczb w Excelu?


Zliczanie wartości liczbowych

Jeśli chcesz określić liczbę komórek zawierających wartości, użyj funkcji LICZBA(wartość1, wartość2,…). W przeciwieństwie do poprzedniej funkcji zlicza nie tylko liczby, ale także dowolną kombinację znaków. Jeśli komórka nie jest pusta, zostanie zliczona. Jeśli komórka zawiera formułę zwracającą zero lub pusty ciąg, funkcja uwzględni ją również w swoim wyniku.


Policz niepuste komórki

Jak liczyć komórki z warunkiem w Microsoft Excel

  • Tablica to zakres komórek do zliczenia. Możesz określić tylko prostokątny zakres sąsiednich komórek;
  • Kryteria - warunek, według którego odbywa się selekcja. Wpisz warunki tekstowe i liczbowe ze znakami porównania w cudzysłowie. Piszemy równość do liczby bez cudzysłowów. Na przykład:
    • ">0" - policz komórki z liczbami większymi od zera
    • „Excel” - policz komórki, w których jest napisane słowo „Excel”
    • 12 - liczba komórek z liczbą 12

Liczba komórek z warunkiem

Jeśli musisz wziąć pod uwagę kilka warunków, użyj funkcji LICZ.WARUNKI(tablica1, kryterium1 [tablica2]; [kryterium1]…). Funkcja może zawierać do 127 par tablica-kryteria.

Jeśli używasz różnych tablic w jednej takiej funkcji, wszystkie muszą zawierać tę samą liczbę wierszy i kolumn.


Zliczanie wartości według wielu warunków

Jak określić najczęściej występującą liczbę

Aby znaleźć liczbę, która występuje najczęściej w tablicy, w Excelu dostępna jest funkcja MODA(numer1, numer2,…). Rezultatem jego wykonania będzie ta sama liczba, która występuje najczęściej. Aby określić ich liczbę, możesz użyć kombinacji formuł sumujących i formuł tablicowych.

Jeśli jest kilka takich numerów, zostanie wyświetlony ten, który pojawia się wcześniej niż pozostałe na liście. Funkcja działa tylko z danymi liczbowymi.


Często występujący numer

Oto być może cała lista funkcji, które chcę wam zaprezentować w tym poście. Wystarczą, aby rozwiązać najpopularniejsze problemy z liczeniem komórek. Połącz je z innymi funkcjami (np. ), aby uzyskać maksymalne rezultaty.

W następnym artykule omówimy. Przyjdź i przeczytaj, nawet jeśli jesteś pewien, że wiesz o tym wszystko. Myślę, że będzie coś właśnie dla Ciebie!


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 można policzyć liczbę powtórzeń wartości w tabeli lub komórce. Zacznijmy w kolejności. Tam jest stół:

I musisz policzyć liczbę powtórzeń każdego imienia:

Co dziwne, ale zrobienie tego jest bardzo proste: Excel ma funkcję - LICZ.JEŻELI, dzięki któremu możesz to wszystko zrobić w kilka sekund. Jeżeli liczba powtórzeń każdego nazwiska musi być wyświetlana w kolumnie B tabeli, a same nazwy znajdują się w kolumnie A:
=LICZ.JEŻELI($A$2:$A$30,A2)
Zakres($A$2:$A$30) - wskazane są komórki zakresu, w których przechowywane są wartości, których liczbę należy obliczyć. główna cecha: ten argument może być tylko odwołaniem do komórki lub zakresu komórek. Niedozwolone jest określenie dowolnej tablicy wartości.
Kryterium(A2) — wskazuje odwołanie do komórki lub bezpośrednio wartość do obliczenia. Tych. możesz również określić to w następujący sposób: =LICZ.JEŻELI($2$:$A$30;"Jabłko"). Możesz także użyć symboli wieloznacznych: ? oraz *. Tych. określając „*banan*” jako Kryterium, możesz policzyć liczbę komórek, w których występuje słowo „banan” (banan, banan, banany, sok bananowy, sto bananów, trzy banany i orzech itp.). Oraz określenie "banan*" - wartości zaczynające się od "banan" (banany, sok bananowy, gaj bananowy itp.). "?" - zastępuje tylko jeden znak, tj. określając "ban?n" możesz liczyć wiersze z wartością "banan" i wartością "banon" itp. Jeśli określisz =LICZ.JEŻELI($A$2:$A$30;"*") jako kryterium, wtedy wszystkie wartości tekstowe. Wartości liczbowe są ignorowane. Tych symboli wieloznacznych (* i?) nie można stosować do wartości liczbowych — tylko do tekstu. Tych. jeśli określisz „12*” jako kryterium, to liczby 1234, 123, 120 itd. nie będą liczone. Do zliczania wartości liczbowych należy używać operatorów porównania: =LICZ.JEŻELI(2$A$2:$A$30;">12")

Jak widać na drugiej rycinie, nazwiska nie są tam powtórzone, choć w tabeli wszystkie są pomieszane. Nie będę się nad tym rozwodził - omówiłem to już w artykule Jak uzyskać listę unikalnych (niepowtarzalnych) wartości? a jeśli to konieczne, możesz skorzystać z dowolnej z opisanych w nim metod.

Jeśli chcesz policzyć liczbę powtórzeń na podstawie kilku warunków (wartości), to począwszy od programu Excel 2007 można to łatwo zrobić za pomocą funkcji LICZ.WARUNKI. Składnia funkcji jest prawie taka sama jak LICZ.JEŻELI, tylko jest więcej warunków i zakresów:
=LICZ.WARUNKI($A2:$A$30;A2;$B$2:$B$30;B2)
zakłada się, że warunki są zapisane w kolumnie B
W zasadzie to tylko lista:
= LICZ.WARUNKI(Warunek_Zakres1, Warunek1, Warunek_Zakres2, Warunek2, Warunek_Zakres3, 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 Arbuz Jabłko Jabłko Bananowe Jabłko Bananowe Jabłko Melon Kiwi Melon Banan Melon Arbuz Kiwi Melon Jabłko Melon Gruszka Jabłko Kiwi Arbuz
COUNTIF na pewno tu nie pomoże. Ale Excel jest pełen innych funkcji i wszystko można zrobić tak prosto:
=(DLSTR($D$1)-DLSTR(ZAMIANA($D$1 ; D3 ;"")))/DLSTR(D3)
DLSTR- zlicza liczbę znaków w określonej komórce/linii ($D$1 , D3)
ZASTĄPIĆ(tekst; stary_tekst; nowy_tekst) - zastępuje określony znak w określonym tekście dowolnym innym ustalić wartość. Domyślnie zastępuje wszystkie wystąpienia określonego znaku. Na tym właśnie opiera się algorytm. Na przykładzie wartości Banana(D3), krok po kroku analiza wzoru:

  • używając funkcji DLSTR otrzymujemy ilość znaków w linii z tekstem źródłowym ($D$1) = (170-LEN (SUBSTITUTE ($D$1 ; D3 ; ""))) / DLSTR (D3) ;
  • za pomocą funkcji SUBSTITUTE zastępujemy wszystkie wartości Banana(D3) w wierszu z tekstem źródłowym ($D$1) pustym i za pomocą DLSTR otrzymujemy ilość znaków w ciągu po tym zastąpieniu =(170-155) /DLSTR(D3) ;
  • odejmij od całkowitej liczby znaków liczbę znaków w ciągu po zamianie i pomnóż wynik przez liczbę znaków w kryterium =(170-155)/5 .

Otrzymujemy numer 3. Właśnie tego potrzebowaliśmy.

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

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slajd","texteffecteasing":"łatwośćOutCubic","texteffectduration":600,"texteffectslidedirection":"w lewo","texteffectslidedodległość" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slajd","texteffectslidedirection1":"prawo","texteffectslidedistance1":120,"texteffecteasing1":"łatwośćOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slajd","texteffectslidedirection2":"prawo","texteffectslidedistance2":120,"texteffecteasing2":"łatwośćKubicowa","texteffectduration2":600,"texteffectdelay2"::1500," textcss":"wyświetlanie:blok; dopełnienie:12px; text-align:lewo;","textbgcss":"wyświetlanie:blok;pozycja:bezwzględna;góra:0px;lewo:0px;szerokość:100%;wysokość:100% ; kolor tła:#333333; krycie:0,6; filtr:a lpha(przezroczystość=60);","titlecss":"wyświetlanie:blok; pozycja:względna; czcionka: pogrubiona 14px \"Lucida Sans Unicode\", \"Lucida Grande\",sans-serif,Arial; kolor:#fff;","descriptioncss":"wyświetlanie:blok; pozycja:względna; czcionka:12px \"Lucida Sans Unicode\", \"Lucida Grande\", sans-serif, Arial; kolor:#fff; margines-góra:8px;","buttoncss":"wyświetlanie:blok; pozycja:względna; margines górny:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"rozmiar czcionki:12px;","descriptioncssresponsive":"wyświetlacz:brak !ważne;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

Funkcja LICZ.JEŻELI w programie Excel służy do zliczania komórek w rozważanym zakresie zawierających dane zgodne z kryteriami przekazanymi jako drugi argument tej funkcji i zwraca odpowiednią wartość liczbową.

Funkcja LICZ.JEŻELI może służyć do analizowania wartości liczbowych, ciągów tekstowych, dat i innych typów danych. Za jego pomocą można określić liczbę niepowtarzających się wartości w zakresie komórek, a także liczbę komórek z danymi tylko częściowo spełniającymi określone kryterium. Na przykład arkusz kalkulacyjny Excel zawiera kolumnę z nazwami klientów. Aby określić liczbę klientów o tej samej nazwie co Iwanow, możesz wprowadzić funkcję =LICZ.JEŻELI(A1:A300;”*Iwanow*”). Symbol „*” oznacza dowolną liczbę dowolnych znaków przed i po podłańcuchu „Iwanow”.

Przykłady użycia funkcji LICZ.JEŻELI w programie Excel

W Excel arkusz kalkulacyjny zawiera dane o sprzedaży towarów w sklepie sprzęt AGD na dzień. Określ, jaka część sprzedawanych produktów to sprzęt firmy Samsung.

Widok oryginalnej tabeli danych:

Do obliczeń wykorzystujemy wzór:

LICZ.JEŻELI(C3:C17;"Samsung")/A17

Opis argumentów:

  • C3:C17 - zakres komórek zawierający nazwy firm sprzedanego sprzętu;
  • „Samsung” – kryterium wyszukiwania (dokładne dopasowanie);
  • A17 to komórka przechowująca numer ostatniej sprzedaży, odpowiadający całkowitej liczbie sprzedaży.

Wynik obliczeń:

Procentowy udział sprzedanych produktów sprzętu Samsung wynosi 40%.



Zliczanie liczby określonej wartości komórki w Excelu pod warunkiem

Na podstawie wyników zdanych egzaminów należy sporządzić tabelę zawierającą dane o liczbie studentów, którzy zdali przedmiot odpowiednio o 5, 4, 3 punkty, a także tych, którzy przedmiotu nie zdali.

Widok oryginalnego stołu:

Najpierw wybierz komórki E2:E5, wprowadź poniższą formułę:

LICZ.JEŻELI(B3:B19;D2:D5)

Opis argumentów:

  • B3:B19 - zakres komórek z ocenami do egzaminu;
  • D2:D5 - zakres komórek zawierający kryteria zliczania liczby dopasowań.

W rezultacie otrzymujemy tabelę:

Analiza statystyczna frekwencji za pomocą funkcji LICZ.JEŻELI w Excelu

Arkusz kalkulacyjny programu Excel przechowuje dane o wyświetleniach stron witryny dziennie przez użytkowników. Określ liczbę użytkowników witryny dziennie, a także ile razy dziennie użytkownicy z domyślnymi loginami i user_1 odwiedzali witrynę.

Widok oryginalnego stołu:

Ponieważ każdy użytkownik ma swój unikalny identyfikator w bazie danych (Id), obliczmy liczbę użytkowników witryny na dzień za pomocą poniższej formuły tablicowej i naciśnij kombinację klawiszy Ctrl+Shift+Enter, aby ją obliczyć:

Wyrażenie 1/LICZ.JEŻELI(A3:A20;A3:A20) zwraca tablicę liczb ułamkowych 1/liczba_wystąpień, na przykład dla użytkownika o pseudonimie sam wartość ta wynosi 0,25 (4 wystąpienia). Łączna suma tych wartości, obliczona przez funkcję SUMA, odpowiada liczbie unikalnych wystąpień, czyli liczbie użytkowników w serwisie. Otrzymana wartość:


Aby określić liczbę stron przeglądanych przez użytkowników domyślnie i user_1, piszemy formułę:

W wyniku obliczeń otrzymujemy:


Funkcje korzystania z funkcji LICZ.JEŻELI w programie Excel

Funkcja ma następującą notację składni:

LICZ.JEŻELI( zakres; kryterium)

Opis argumentów:

  • zakres to wymagany argument, który przyjmuje odwołanie do co najmniej jednej komórki, w której chcesz określić liczbę dopasowań z określonym kryterium.
  • kryterium - warunek, według którego dokonywane jest obliczenie liczby dopasowań w rozpatrywanym zakresie. Warunkiem może być Wyrażenie logiczne, wartość liczbowa, ciąg tekstowy, wartość daty, odwołanie do komórki.

Uwagi:

  1. Podczas liczenia liczby wystąpień w zakresie zgodnie z dwoma różnymi warunkami, zakres komórek można uznać za zestaw zawierający co najmniej dwa nienakładające się podzbiory. Na przykład w tabeli „Meble” musisz znaleźć liczbę stołów i krzeseł. Do obliczeń używamy wyrażenia =LICZ.JEŻELI(B3:B200;"*tabela*")+LICZ.JEŻELI(B3:B200;"*krzesło*").
  2. Jeśli jako kryterium określono ciąg tekstowy, należy pamiętać, że wielkość liter nie jest istotna. Na przykład funkcja LICZ.JEŻELI(A1:A2;"Pietrow") zwróci wartość 2, jeśli komórki A1 i A2 zawierają odpowiednio ciągi "Piotr" i "Piotr".
  3. Jeśli jako argument kryterium zostanie przekazane odwołanie do pustej komórki lub pustego ciągu „”, wynikiem obliczenia dla dowolnego zakresu komórek będzie wartość liczbowa 0 (zero).
  4. Funkcja może być używana jako formuła tablicowa, jeśli chcesz obliczyć liczbę komórek z danymi, które spełniają jednocześnie kilka kryteriów. Ta funkcja zostanie omówiona w jednym z przykładów.
  5. Rozważana funkcja może być wykorzystana do określenia liczby dopasowań zarówno według jednego, jak i kilku kryteriów wyszukiwania jednocześnie. W tym drugim przypadku używane są dwie lub więcej funkcji LICZ.JEŻELI, których zwrócone wyniki są dodawane lub odejmowane. Na przykład komórki A1:A10 przechowują sekwencję wartości od 1 do 10. Aby obliczyć liczbę komórek z liczbami większymi niż 3 i mniejszymi niż 8, wykonaj następujące kroki: