Sposoby zliczania wartości w arkuszu

Zliczanie jest integralną częścią analizy danych w przypadku obliczania zarówno liczby pracowników działu w organizacji, jak i liczby jednostek sprzedanych w poszczególnych kwartałach. W programie Excel jest dostępnych wiele technik umożliwiających zliczanie komórek, wierszy lub kolumn danych. Aby ułatwić dokonanie najlepszego wyboru, w tym artykule zamieszczono pełne podsumowanie metod, skoroszyt do pobrania z interakcyjnymi przykładami oraz linki do powiązanych tematów umożliwiające dokładniejsze zrozumienie.

Pobierz nasze przykłady

Możesz pobrać przykładowy skoroszyt z przykładami uzupełniającymi informacje zawarte w tym artykule. W większości sekcji tego artykułu znajdują się odwołania do odpowiedniego arkusza w przykładowym skoroszycie, który zawiera przykłady i więcej informacji.

Pobieranie przykładów zliczania wartości w arkuszu kalkulacyjnym

W tym artykule

• Zliczanie proste

  • Korzystanie z funkcji Autosumowanie
  • Dodawanie wiersza sumy częściowej
  • Zliczanie komórek na liście lub w kolumnie tabeli programu Excel przy użyciu funkcji SUMY.CZĘŚCIOWE

• Zliczanie na podstawie jednego lub wielu warunków

  • Klip wideo: korzystanie z funkcji ILE.LICZB, LICZ.JEŻELI i ILE.NIEPUSTYCH
  • Zliczanie komórek w zakresie przy użyciu funkcji ILE.LICZB
  • Zliczanie komórek w zakresie na podstawie jednego warunku przy użyciu funkcji LICZ.JEŻELI
  • Zliczanie komórek w kolumnie na podstawie jednego lub wielu warunków przy użyciu funkcji BD.ILE.REKORDÓW
  • Zliczanie komórek w zakresie na podstawie wielu warunków przy użyciu funkcji LICZ.WARUNKI
  • Zliczanie przy jednoczesnym użyciu funkcji ILE.LICZB i JEŻELI w oparciu o kryteria
  • Zliczanie przy użyciu funkcji SUMA i JEŻELI, ile razy występują różne wartości tekstowe lub liczbowe
  • Zliczanie komórek w kolumnie lub wierszu tabeli przestawnej

• Zliczanie, gdy dane zawierają wartości puste

  • Zliczanie niepustych komórek w zakresie przy użyciu funkcji ILE.NIEPUSTYCH
  • Zliczanie niepustych komórek na liście z określonymi warunkami przy użyciu funkcji BD.ILE.REKORDÓW.A
  • Zliczanie pustych komórek w ciągłym zakresie przy użyciu funkcji LICZ.PUSTE
  • Zliczanie pustych komórek w nieciągłym zakresie przy użyciu funkcji SUMA i JEŻELI

• Zliczanie unikatowych wartości

  • Zliczanie unikatowych wartości w kolumnie listy przy użyciu filtru zaawansowanego
  • Zliczanie unikatowych wartości w zakresie, które spełniają jeden lub kilka warunków, przy użyciu funkcji JEŻELI, SUMA, CZĘSTOTLIWOŚĆ, PODAJ.POZYCJĘ i DŁ

• Przypadki specjalne (zliczanie wszystkich komórek, zliczanie wyrazów)

  • Zliczanie wszystkich komórek w zakresie przy użyciu funkcji ILE.WIERSZY i LICZBA.KOLUMN
  • Zliczanie wyrazów w zakresie przy użyciu kombinacji funkcji SUMA, JEŻELI, DŁ, USUŃ.ZBĘDNE.ODSTĘPY i PODSTAW

• Wyświetlanie wyników obliczeń i zliczania na pasku stanu

Zliczanie proste

Wartości w zakresie lub tabeli można zliczać przy użyciu prostej formuły, kliknięcia przycisku lub funkcji arkusza.

Program Excel może również wyświetlać liczbę zaznaczonych komórek na pasku stanu programu Excel. Obejrzyj poniższy pokaz wideo, aby szybko dowiedzieć się, jak działa pasek stanu. Aby uzyskać więcej informacji, zobacz również sekcję Wyświetlanie obliczeń i zliczania na pasku stanu . Jeśli chcesz szybko przejrzeć dane, ale nie masz czasu na wprowadzanie formuł, możesz odwołać się do wartości wyświetlonych na pasku stanu.

Klip wideo: zliczanie komórek przy użyciu paska stanu programu Excel

Obejrzyj poniższe wideo, aby dowiedzieć się, jak wyświetlić licznik na pasku stanu.

Korzystanie z funkcji Autosumowanie

Użyj przycisku Autosumowanie, zaznaczając zakres komórek zawierających co najmniej jedną wartość liczbową. Następnie na karcie Formuły wybierz pozycję Autosumowanie>Zlicz liczby.

Program Excel zwraca liczbę wartości liczbowych w zakresie w komórce przylegającej do zaznaczonego zakresu. Zazwyczaj ten wynik jest wyświetlany w komórce po prawej stronie obok poziomego zakresu lub w komórce poniżej pionowego zakresu.

Początek strony

Dodawanie wiersza sumy częściowej

Wiersz sumy częściowej można dodać do danych programu Excel. Kliknij dowolne miejsce wewnątrz danych, a następnie wybierzpozycję Suma częściowakonspektu>danych>.

Ponadto zapoznaj się z następującymi artykułami:

• Tworzenie konspektu danych (grupowanie danych) w arkuszu
• Wstawianie sum częściowych na liście danych w arkuszu

Początek strony

Zliczanie komórek na liście lub w kolumnie tabeli programu Excel przy użyciu funkcji SUMY.CZĘŚCIOWE

Do zliczania wartości w tabeli programu Excel lub zakresie komórek należy używać funkcji SUMY.CZĘŚCIOWE . Jeśli tabela lub zakres zawiera ukryte komórki, można użyć funkcji SUMY.CZĘŚCIOWE w celu uwzględnienia lub wykluczenia tych ukrytych komórek i jest to największa różnica między funkcjami SUMA i SUMY.CZĘŚCIOWE.

Składnia SUMY.CZĘŚCIOWE wygląda następująco:

SUMY.CZĘŚCIOWE(funkcja_nr;odw1;[odw2];...)

Aby uwzględnić wartości ukryte w zakresie, należy ustawić argument function_num na 2.

Aby wykluczyć wartości ukryte w zakresie, ustaw argument function_num na wartość 102.

Początek strony

Zliczanie na podstawie jednego lub wielu warunków

Można zliczać komórki w zakresie spełniające warunki (nazywane również kryteriami) określone przy użyciu kilku funkcji arkusza.

Klip wideo: korzystanie z funkcji ILE.LICZB, LICZ.JEŻELI i ILE.NIEPUSTYCH

Obejrzyj poniższy klip wideo, aby dowiedzieć się, jak korzystać z funkcji ILE.LICZB i jak użyć funkcji LICZ.JEŻELI oraz ILE.NIEPUSTYCH do zliczania tylko komórek spełniających określone warunki.

Początek strony

Zliczanie komórek w zakresie przy użyciu funkcji ILE.LICZB

Wartości liczbowe w zakresie można zliczać przy użyciu formuły zawierającej funkcję ILE.LICZB.

W powyższym przykładzie A2, A3 i A6 to jedyne komórki zawierające wartości liczbowe w zakresie, stąd wynik to 3.

Początek strony

Zliczanie komórek w zakresie na podstawie jednego warunku przy użyciu funkcji LICZ.JEŻELI

Użyj funkcji LICZ.JEŻELI do zliczania, ile razy określona wartość pojawia się w zakresie komórek.

Początek strony

Zliczanie komórek w kolumnie na podstawie jednego lub wielu warunków przy użyciu funkcji BD.ILE.REKORDÓW

BD.ILE.REKORDÓW , funkcja zlicza komórki zawierające liczby znajdujące się w polu (kolumnie) rekordów listy lub bazy danych, które są zgodne z warunkami określonymi przez użytkownika.

W poniższym przykładzie należy zliczyć miesiące od marca 2016 włącznie, w których sprzedano ponad 400 jednostek. Pierwsza tabela w arkuszu, od A1 do B7, zawiera dane dotyczące sprzedaży.

Funkcja BD.ILE.REKORDÓW używa warunków do określenia, skąd powinny być zwracane wartości. Warunki wprowadza się zazwyczaj w komórkach arkusza, a następnie tworzy się odwołanie do tych komórek w argumencie kryteria . W tym przykładzie komórki A10 i B10 zawierają dwa warunki — jeden określający, że zwracana wartość musi być większa niż 400, a drugi określający, że miesiąc końcowy powinien być równy lub większy niż 31 marca 2016.

Należy użyć następującej składni:

=BD.ILE.REKORDÓW(A1:B7;"Koniec miesiąca";A9:B10)

Funkcja BD.ILE.REKORDÓW sprawdza dane z zakresu od A1 do B7, stosuje warunki określone w komórkach A10 i B10 i zwraca wartość 2, czyli łączną liczbę wierszy spełniających oba warunki (wiersze 5 i 7).

Początek strony

Zliczanie komórek w zakresie na podstawie wielu warunków przy użyciu funkcji LICZ.WARUNKI

Funkcja LICZ.WARUNKI jest podobna do funkcji LICZ.JEŻELI z jedną ważna różnicą: funkcja LICZ.WARUNKI umożliwia zastosowanie kryteriów do komórek w wielu zakresach i zliczenie, ile razy wszystkie kryteria zostaną spełnione. Funkcja LICZ.WARUNKI pozwala na użycie maksymalnie 127 par warunków/kryteriów.

Składnia funkcji LICZ.WARUNKI jest następująca:

LICZ.WARUNKI(zakres_kryterium1; kryterium1; [zakres_kryterium2; kryterium2],…)

Zobacz poniższy przykład:

Początek strony

Zliczanie przy jednoczesnym użyciu funkcji ILE.LICZB i JEŻELI w oparciu o kryteria

Załóżmy, że należy ustalić, ilu sprzedawców sprzedało określony produkt w danym regionie lub ile transakcji sprzedaży przekraczających określoną wartość odnotował dany sprzedawca. W tym celu można jednocześnie użyć funkcji JEŻELI i ILE.LICZB, to znaczy najpierw sprawdzić warunek za pomocą funkcji JEŻELI, a jeżeli zostanie on spełniony, to zliczyć komórki za pomocą funkcji ILE.LICZB.

Początek strony

Zliczanie przy użyciu funkcji SUMA i JEŻELI, ile razy występują różne wartości tekstowe lub liczbowe

W poniższych przykładach jednocześnie używane są funkcje JEŻELI i SUMA. Najpierw funkcja JEŻELI sprawdza wartości w niektórych komórkach, a jeżeli warunek zostanie spełniony, funkcja SUMA dodaje wartości spełniające warunki.

Przykład 1

Powyższa funkcja informuje, czy komórki C2:C7 zawierają wartości Michalski i Dudek, a następnie funkcja SUMA powinna wyświetlić sumę rekordów, dla których warunek jest spełniony. Formuła znajduje w podanym zakresie trzy rekordy dla nazwiska Michalski oraz jeden dla nazwiska Dudek i wyświetla liczbę 4.

Przykład 2

Powyższa funkcja informuje, czy zakres D2:D7 zawiera wartości mniejsze niż 9000 zł lub większe niż 19 000 zł, a następnie funkcja SUMA powinna wyświetlić sumę wszystkich tych rekordów, dla których warunek jest spełniony. Formuła znajduje dwa rekordy, D3 i D5, o wartościach mniejszych niż 9000 zł, a następnie rekordy D4 i D6 o wartościach większych niż 19 000 zł i wyświetla wynik 4.

Przykład 3

Powyższa funkcja informuje, czy zakres D2:D7 zawiera faktury na nazwisko Michalski o wartości mniejszej niż 9000 zł, a następnie funkcja SUMA powinna wyświetlić sumę rekordów, dla których warunek jest spełniony. Formuła wykrywa, że rekord C6 spełnia określony warunek, i wyświetla wynik 1.

Początek strony

Zliczanie komórek w kolumnie lub wierszu tabeli przestawnej

Tabela przestawna zawiera podsumowanie danych oraz ułatwia analizowanie i przechodzenie do szczegółów, umożliwiając wybranie kategorii, według których chcesz wyświetlić dane.

Można szybko utworzyć tabelę przestawną, zaznaczając komórkę w zakresie danych lub tabeli programu Excel, a następnie wybierając pozycję Tabela przestawna na karcie Wstawianie w grupie Tabele.

Przyjrzyjmy się przykładowemu scenariuszowi arkusza kalkulacyjnego Sprzedaż, gdzie można zliczyć wartości sprzedaży dla kategorii Golf i Tenis w konkretnych kwartałach.

1. Wprowadź następujące dane w arkuszu kalkulacyjnym programu Excel.

   

2. Zaznacz zakres A2:C8.

3. Wybierz pozycję Wstaw>tabelę przestawną.

4. Wybierz pozycje Z tabeli/zakresu, Nowy arkusz, a następnie wybierz przycisk OK.

5. Pusta tabela przestawna zostanie utworzona w nowym arkuszu.

6. W okienku Pola tabeli przestawnej wykonaj następujące czynności:

   1. Przeciągnij pozycję Sport do obszaru Wiersze.

   2. Przeciągnij pozycję Kwartał do obszaru Kolumny.

   3. Przeciągnij pozycję Sprzedaż do obszaru Wartości.

   4. Powtórz krok C.
      Nazwa pola zostanie wyświetlona jako SumaSprzedaż2 zarówno w tabeli przestawnej, jak i w obszarze wartości.
      W tym momencie okienko pól tabeli przestawnej wygląda następująco:

      

   5. W obszarze Wartości wybierz listę rozwijaną obok pozycji SumaSprzedaż2 i wybierz pozycję Ustawienia pola wartości.

   6. W oknie dialogowym Ustawienia pola wartości wykonaj następujące czynności:

      1. W sekcji Podsumuj pole wartości według wybierz pozycję Licznik.

      2. W polu Nazwa niestandardowa zmień nazwę na Licznik.

         

      3. Wybierz przycisk OK.

   W tabeli przestawnej zostanie wyświetlona liczba rekordów dla kategorii Golf i Tenis w 3. i 4. kwartale wraz z wynikami sprzedaży.

   

Początek strony

Zliczanie, gdy dane zawierają wartości puste

Korzystając z funkcji arkusza, możesz zliczać komórki, które zawierają dane lub są puste.

Zliczanie niepustych komórek w zakresie przy użyciu funkcji ILE.NIEPUSTYCH

Korzystając z funkcji ILE.NIEPUSTYCH , można zliczać tylko komórki w zakresie, które zawierają wartości.

Podczas zliczania komórek czasami konieczne jest ignorowanie pustych komórek, ponieważ tylko komórki z wartościami są znaczące. Na przykład chcesz zliczyć całkowitą liczbę sprzedawców, którzy dokonali sprzedaży (kolumna D).

ignoruje puste wartości w komórkach D3, D4, D8 i D11 i zlicza tylko komórki zawierające wartości w kolumnie D. Funkcja znajduje sześć komórek w kolumnie D zawierających wartości i wyświetla liczbę 6 jako wynik.

Początek strony

Zliczanie niepustych komórek na liście z określonymi warunkami przy użyciu funkcji BD.ILE.REKORDÓW.A

Korzystając z funkcji BD.ILE.REKORDÓW.A, można zliczać niepuste komórki w kolumnie rekordów listy lub bazy danych, które są zgodne z określonymi przez Ciebie warunkami.

W poniższym przykładzie funkcja BD.ILE.REKORDÓW.A jest używana do zliczania rekordów w bazie danych, znajdującej się w zakresie A1:B7, które są zgodne z warunkami określonymi w zakresie kryteriów A9:B10. Zgodnie z tymi warunkami wartość Identyfikator produktu musi być większa niż lub równa 2000, a wartość Oceny musi być większa niż lub równa 50.

BD.ILE.REKORDÓW.A znajduje dwa wiersze spełniające określone warunki — wiersze 2 i 4, a następnie wyświetla wartość 2 jako wynik.

Początek strony

Zliczanie pustych komórek w ciągłym zakresie przy użyciu funkcji LICZ.PUSTE

Korzystając z funkcji LICZ.PUSTE , można zliczać puste komórki w ciągłym zakresie (komórki tworzące nieprzerwaną sekwencję). Jeśli komórka zawiera formułę zwracającą pusty tekst (""), jest ona zliczana.

Podczas zliczania komórek czasami konieczne jest uwzględnianie pustych komórek, ponieważ są one znaczące. W poniższym przykładzie arkusza kalkulacyjnego dotyczącego sprzedaży artykułów spożywczych. Załóżmy, że trzeba sprawdzić, w ilu komórkach nie ma podanych danych danych dotyczących sprzedaży.

Początek strony

Zliczanie pustych komórek w nieciągłym zakresie przy użyciu funkcji SUMA i JEŻELI

Użyj kombinacji funkcji SUMA i JEŻELI . Na ogół robi się to przez użycie funkcji JEŻELI w formule tablicowej w celu określenia, czy każda komórka, której dotyczy odwołanie, zawierania wartości, a następnie przez zsumowanie liczby wartości FAŁSZ zwróconych przez formułę.

Zobacz kilka przykładów połączenia funkcji SUMA i JEŻELI we wcześniejszej sekcji Zlicz, ile razy występują różne wartości tekstowe lub liczbowe, używając jednocześnie funkcji SUMA i JEŻELI w tym temacie.

Początek strony

Zliczanie unikatowych wartości

Unikatowe wartości w zakresie można zliczać przy użyciu tabeli przestawnej, funkcji LICZ.JEŻELI, funkcji SUMA i JEŻELI razem lub okna dialogowego Filtr zaawansowany .

Zliczanie unikatowych wartości w kolumnie listy przy użyciu filtru zaawansowanego

Korzystając z okna dialogowego Filtr zaawansowany, można znaleźć unikatowe wartości w kolumnie danych. Można filtrować wartości w miejscu lub wyodrębnić je i wkleić w nowym miejscu. Następnie można zliczyć elementy w nowym zakresie przy użyciu funkcji ILE.WIERSZY.

Aby użyć filtru zaawansowanego, wybierz kartę Dane i w grupie Sortowanie & filtrowanie wybierz pozycję Zaawansowane.

Na poniższej ilustracji przedstawiono sposób użycia filtru zaawansowanego do kopiowania tylko unikatowych rekordów do nowej lokalizacji w arkuszu.

Na poniższej ilustracji kolumna E zawiera wartości skopiowane z zakresu w kolumnie D.

Aby uzyskać więcej informacji, zobacz Filtrowanie przy użyciu zaawansowanych kryteriów

Początek strony

Zliczanie unikatowych wartości w zakresie, które spełniają jeden lub kilka warunków, przy użyciu funkcji JEŻELI, SUMA, CZĘSTOTLIWOŚĆ, PODAJ.POZYCJĘ i DŁ

Użyj różnych kombinacji funkcji JEŻELI, SUMA, CZĘSTOŚĆ, PODAJ.POZYCJĘ i DŁ.

Aby uzyskać więcej informacji i przykładów, zobacz sekcję "Zliczanie wartości unikatowych przy użyciu funkcji" w artykule Zliczanie wartości unikatowych wśród duplikatów.

Początek strony

Przypadki specjalne (zliczanie wszystkich komórek, zliczanie wyrazów)

Można zliczać komórki lub liczbę wyrazów w zakresie przy użyciu różnych kombinacji funkcji arkusza.

Zliczanie wszystkich komórek w zakresie przy użyciu funkcji ILE.WIERSZY i LICZBA.KOLUMN

Załóżmy, że konieczne jest ustalenie rozmiaru dużego arkusza w celu zdecydowania, czy w skoroszycie należy używać obliczeń ręcznych, czy automatycznych. Aby zliczyć wszystkie komórki w zakresie, użyj formuły mnożącej wartości zwracane za pomocą funkcji ILE.WIERSZY i LICZBA.KOLUMN. Zobacz poniższy obraz, aby zapoznać się z przykładem:

Początek strony

Zliczanie wyrazów w zakresie przy użyciu kombinacji funkcji SUMA, JEŻELI, DŁ, USUŃ.ZBĘDNE.ODSTĘPY i PODSTAW

W formule tablicowej można użyć kombinacji funkcji SUMA,JEŻELI, DŁ,USUŃ.ZBĘDNE.ODSTĘPY i PODSTAW . W poniższym przykładzie przedstawiono wynik użycia formuły zagnieżdżonej do znalezienia liczby wyrazów w zakresie 7 komórek (z czego 3 są puste). Niektóre komórki zawierają spacje wiodące lub końcowe — funkcje USUŃ.ZBĘDNE.ODSTĘPY i PODSTAW usuwają te dodatkowe spacje przed rozpoczęciem zliczania. Zobacz poniższy przykład:

Powyższa formuła działa poprawnie, trzeba ją przekształcić w formułę tablicową. W przeciwnym razie formuła zwróci #VALUE! #POŁĄCZ!. Aby to zrobić, zaznacz komórkę zawierającą formułę, a następnie na pasku formuły naciśnij klawisze Ctrl + Shift + Enter. Program Excel doda nawias klamrowy na początku i na końcu formuły, przekształcając formułę w formułę tablicową.

Aby uzyskać więcej informacji na temat formuł tablicowych, zobacz Omówienie formuł w programie Excel i Tworzenie formuły tablicowej.

Początek strony

Wyświetlanie wyników obliczeń i zliczania na pasku stanu

Po zaznaczeniu jednej lub większej liczby komórek informacje dotyczące danych w tych komórkach są wyświetlane na pasku stanu programu Excel. Jeśli na przykład zaznaczono cztery komórki w arkuszu zawierające wartości 2, 3, ciąg tekstowy (na przykład „chmura”) i 4, wszystkie z poniższych wartości mogą być wyświetlane na pasku stanu w tym samym czasie: Średnia, Licznik, Licznik wartości liczbowych, Minimum, Maksimum i Suma. Aby pokazać lub ukryć dowolną wartość lub wszystkie wartości, kliknij pasek stanu prawym przyciskiem myszy. Te wartości przedstawiono na poniższej ilustracji.

Początek strony

Potrzebujesz dodatkowej pomocy?

Zawsze możesz zadać pytanie ekspertowi w społeczności technicznej programu Excel lub uzyskać pomoc techniczną w społecznościach.