Sposoby zliczania wartości w arkuszu

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

Obejrzyj poniższy klip wideo, aby dowiedzieć się, jak wyświetlić statystykę na pasku stanu.

Korzystanie z funkcji Autosumowanie

Użyj funkcji Autosumowanie , zaznaczając zakres komórek zawierający co najmniej jedną wartość liczbową. Następnie na karcie Formuły kliknij pozycję Autosumowanie > zliczanie liczb.

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

Do danych programu Excel możesz dodać wiersz sumy częściowej. Kliknij dowolne miejsce wewnątrz danych, a następnie kliknij pozycję Dane > Sumy częściowe.

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

Funkcja SUMY.CZĘŚCIOWE służy do zliczania wartości w tabeli lub zakresie komórek programu Excel. 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ć dla argumentu function_numwartość 2.

Aby wykluczyć wartości ukryte z zakresu, ustaw dla argumentu function_numwartość 102.

Początek strony

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 komórki A2, A3 i A6 są jedynymi komórkami, które zawierają wartości liczbowe w zakresie, dlatego wynikiem jest 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 , aby zliczyć, 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

Funkcja BD.ILE.REKORDÓW zlicza komórki zawierające liczby w polu (kolumnie) rekordów na liście lub w bazie danych, które są zgodne z warunkami określonymi przez użytkownika.

W poniższym przykładzie można znaleźć liczbę miesięcy z uwzględnieniem lub później niż marzec 2016, w których sprzedano ponad 400 sztuk. Pierwsza tabela w arkuszu, od A1 do B7, zawiera dane sprzedaży.

Funkcja BD.ILE.REKORDÓW używa warunków do określenia, skąd mają być zwracane wartości. Warunki są zwykle wprowadzane w komórkach w samym arkuszu, a następnie należy odwołać się do tych komórek w argumacie 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 druga określająca, ż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 BCOUNT sprawdza dane z zakresu od A1 do B7, stosuje warunki określone w A10 i B10 i zwraca wartość 2, czyli całkowitą 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ższe funkcje informują, 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 dla sprzedawcy o nazwisku Myrcha za mniej niż 9000 zł, wówczas funkcja SUMA powinna wyświetlić sumę rekordów, w których warunek jest spełniony. Formuła wykrywa, że rekord C6 spełnia określony warunek, i wyświetla wynik 1.

Dodatkowe porady zawierają następujące artykuły z bazy wiedzy Knowledge Base:

• XL: Użycie funkcji SUMA(JEŻELI()) jako funkcji tablicowej zamiast funkcji LICZ.JEŻELI() z operatorem ORAZ

• XL: Jak zliczać wystąpienia liczby lub tekstu w zakresie

Początek strony

Zliczanie komórek w kolumnie lub wierszu w tabeli przestawnej

Tabela przestawna zawiera podsumowanie danych i ułatwia analizowanie i przechodzenie do szczegółów danych, umożliwiając wybranie kategorii, dla 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 klikając przycisk Tabela przestawna w grupie Tabele na karcie Wstawianie.

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. Kliknij pozycję Wstawianie > Tabela przestawna.

4. W oknie dialogowym Tworzenie tabeli przestawnej kliknij pozycję Zaznacz tabelę lub zakres, kliknij pozycję Nowy arkusz, a następnie kliknij pozycję OK.

   Pusta tabela przestawna zostanie utworzona w nowym arkuszu.

5. 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 kliknij 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. Kliknij 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 niepustych komórek w zakresie przy użyciu funkcji ILE.NIEPUSTYCH

Funkcja ILE.NIEPUSTYCH służy do zliczania tylko komórek w zakresie zawierającym wartości.

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

Funkcja ILE.NIEPUSTYCH ignoruje wartości puste 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 wynik 6 .

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 użyto funkcji BD.ILE.REKORDÓW.A w celu zliczenia w bazie danych rekordów zawartych w zakresie A1:B7, które spełniają warunki określone w zakresie kryteriów A9:B10. Warunkiem jest to, że wartość identyfikatora produktu musi być większa lub równa 2000, a wartość Oceny musi być większa lub równa 50.

Funkcja BD.ILE.REKORDÓW.A znajduje dwa wiersze spełniające warunki — wiersze 2 i 4 oraz wyświetla wartość 2 jako wynik.

Początek strony

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

Użyj funkcji LICZ.PUSTE , aby zwrócić liczbę pustych komórek w ciągłym zakresie (komórki są ciągłe, jeśli wszystkie są połączone w ciągłej sekwencji). 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 sprzedaży artykułów spożywczych. załóżmy, że chcesz sprawdzić, ile komórek nie zawiera podanych wartości 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 funkcji JEŻELI . Na ogół można to zrobić za pomocą funkcji JEŻELI w formule tablicowej w celu określenia, czy każda komórka, do której odwołuje się odwołanie, zawiera wartość, a następnie sumując liczbę wartości FAŁSZ zwróconych przez formułę.

Zobacz kilka przykładów kombinacji funkcji SUMA i JEŻELI we wcześniejszej sekcji Zliczanie wystąpień wielu wartości tekstowych lub liczbowych przy użyciu funkcji SUMA i JEŻELI w tym temacie .

Początek strony

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, kliknij kartę Dane , a następnie w grupie Sortowanie & Filtr kliknij 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 spełniających co najmniej jeden warunek przy użyciu funkcji JEŻELI, SUMA, CZĘSTOŚĆ, 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łady, zobacz sekcję "Zliczanie unikatowych wartości przy użyciu funkcji" w artykule Zliczanie unikatowych wartości wśród duplikatów.

Początek strony

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 zwracane wartości przy użyciu funkcji WIERSZE i KOLUMNY . Zobacz poniższy obraz na przykład:

Początek strony

Zliczanie wyrazów w zakresie przy użyciu 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 których 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 wystąpieniem jakichkolwiek zliczania. Zobacz poniższy przykład:

Aby powyższa formuła działała poprawnie, musisz ją utworzyć jako formułę tablicową, w przeciwnym razie formuła zwróci #VALUE! . W tym celu kliknij komórkę zawierającą formułę, a następnie na pasku Formuły naciśnij klawisze Ctrl + Shift + Enter. Program Excel dodaje nawias klamrowy na początku i na końcu formuły, co czyni ją 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