Ten artykuł zawiera opis składni formuły i zastosowania funkcji BD.ILE.REKORDÓW.A w programie Microsoft Excel.
Opis
Liczy niepuste komórki znajdujące się w polu (kolumnie) rekordów listy lub bazy danych, które są zgodne z warunkami określonymi przez użytkownika.
Argument pole jest opcjonalny. Jeśli argument pole zostanie pominięty, funkcja BD.ILE.REKORDÓW.A zlicza wszystkie spełniające kryteria rekordy w bazie danych.
Składnia
BD.ILE.REKORDÓW.A(baza danych;pole;kryteria)
W składni funkcji BD.ILE.REKORDÓW.A występują następujące argumenty:
-
Baza danych Argument wymagany. Zakres komórek, które tworzą listę lub bazę danych. Baza danych to lista powiązanych danych, na której wiersze pokrewnych informacji to rekordy, a kolumny danych to pola. Pierwszy wiersz listy zawiera etykiety poszczególnych kolumn.
-
Pole Argument opcjonalny. Wskazuje, która kolumna jest używana w funkcji. Należy wprowadzić etykietę kolumny umieszczoną w podwójnym cudzysłowie, na przykład "Wiek" lub "Plon", lub liczbę (bez cudzysłowów) reprezentującą pozycję kolumny na liście: 1 dla pierwszej kolumny, 2 dla drugiej itd.
-
Kryteria Argument wymagany. Zakres komórek zawierający warunki określone przez użytkownika. Jako argumentu „kryteria” można użyć dowolnego zakresu pod warunkiem, że zawiera przynajmniej jedną etykietę kolumny i jedną komórkę poniżej etykiety, w której określa się warunek.
Spostrzeżenia
-
Do określenia warunku można użyć dowolnego zakresu jako argumentu kryterium, o ile tylko zawiera on co najmniej jedną etykietę kolumny i co najmniej jedną komórkę poniżej etykiety kolumny.
Jeśli na przykład zakres G1:G2 zawiera etykietę kolumny Przychód w komórce G1 i kwotę 10 000 zł w komórce G2, można nadać zakresowi nazwę „DobierzPrzychód” i użyć jej jako argumentu kryteriów w funkcjach bazy danych.
-
Chociaż zakres kryteriów może znajdować się w dowolnym miejscu arkusza, nie należy umieszczać go poniżej listy. Jeśli do listy zostanie dodanych więcej informacji, nowe informacje będą dodawane do pierwszego wiersza poniżej listy. Jeśli wiersz poniżej listy nie jest pusty, program Excel nie może dodawać nowych informacji.
-
Należy upewnić się, że zakres kryteriów nie zachodzi na listę.
-
Aby przeprowadzić operację na całej kolumnie w bazie danych, w zakresie zawierającym kryteria, należy wprowadzić pusty wiersz poniżej etykiet kolumn.
Przykłady
Skopiuj przykładowe dane z poniższej tabeli i wklej je w komórce A1 nowego arkusza programu Excel. Aby formuły wyświetlały wyniki, zaznacz je, naciśnij klawisz F2, a następnie naciśnij klawisz Enter. Jeśli to konieczne, możesz dostosować szerokości kolumn, aby wyświetlić pełne dane. Jeśli kopiujesz jakikolwiek z poniższych przykładów do programu Excel, upewnij się, że zostały zaznaczone wszystkie komórki tabeli, włącznie z komórką znajdującą się w skrajnym górnym lewym rogu.
Drzewo |
Wysokość |
Wiek |
Plon |
Zysk |
Wysokość |
="=jabłoń" |
>10 |
<16 |
|||
="=grusza" |
|||||
Drzewo |
Wysokość |
Wiek |
Plon |
Zysk |
|
Jabłoń |
18 |
20 |
14 |
105,0 |
|
Grusza |
12 |
12 |
10 |
96,0 |
|
Wiśnia |
13 |
14 |
9 |
105,0 |
|
Jabłoń |
14 |
15 |
10 |
75,0 |
|
Grusza |
9 |
8 |
8 |
76,8 |
|
Jabłoń |
8 |
9 |
6 |
45,0 |
|
Formuła |
Opis |
Wynik |
|||
=BD.ILE.REKORDÓW.A(A4:E10;"Zysk";A1:F2) |
Zlicza wiersze (1) zawierające „Jabłoń” w kolumnie A o wysokości >10 i <16. Tylko wiersz 8 spełnia te trzy warunki. |
1 |
Przykłady kryteriów
-
Program Excel interpretuje wprowadzony w komórce ciąg =tekst jako formułę i próbuje ją obliczyć. Aby wprowadzić ciąg =tekst tak, aby program Excel go nie obliczał, zastosuj następującą składnię:
=''= wpis ''
Gdzie wpis określa szukany tekst lub szukaną wartość. Przykłady:
Wartość wpisana do komórki |
Wynik wyświetlony w programie Excel |
="=Kretowicz" |
=Kretowicz |
="=3000" |
=3000 |
-
Podczas filtrowania danych tekstowych program Excel nie rozróżnia wielkich i małych liter. Przy użyciu formuły można jednak przeprowadzić wyszukiwanie z rozróżnianiem wielkości liter.
Przykłady złożonych kryteriów można znaleźć w poniższych sekcjach.
Wiele kryteriów w jednej kolumnie
Warunek logiczny: (Sprzedawca = "Kretowicz" LUB Sprzedawca = "Myrcha")
Aby znaleźć wiersze spełniające wiele kryteriów dla jednej kolumny, wpisz kryteria bezpośrednio jedno pod drugim w osobnych wierszach zakresu kryteriów.
W poniższym zakresie danych (A6:C10) użyto zakresu kryteriów (B1:B3) do zliczenia wierszy zawierających wartości „Kretowicz” i „Myrcha” w kolumnie Sprzedawca.
|
Sprzedawca |
|
="=Kretowicz" |
||
="=Myrcha" |
||
|
||
Kategoria |
Sprzedawca |
Sprzedaż |
Napoje |
Stąpor |
5122 zł |
Mięso |
Kretowicz |
450 zł |
warzywa |
Myrcha |
6328 zł |
Warzywa |
Kretowicz |
6544 zł |
Formuła |
Opis |
Wynik |
'=BD.ILE.REKORDÓW.A(A6:C10;2;B1:B3) |
Zlicza wiersze (3) w zakresie A6:C10, które spełniają dowolny z warunków "Sprzedawca" w wierszach 2 i 3. |
=BD.ILE.REKORDÓW.A(A6:C10;2;B1:B3) |
Wiele kryteriów w wielu kolumnach, gdy wszystkie kryteria muszą być spełnione
Warunek logiczny: (Typ = "Warzywa" ORAZ Sprzedaż > 2000)
Aby znaleźć wiersze spełniające wiele kryteriów w wielu kolumnach, wpisz wszystkie kryteria w tym samym wierszu zakresu kryteriów.
W poniższym zakresie danych (A6:C12) użyto zakresu kryteriów (A1:C2) do zliczenia wierszy zawierających wartość „Warzywa” w kolumnie Kategoria i wartość większą niż 2000 zł w kolumnie Sprzedaż.
Kategoria |
Sprzedawca |
Sprzedaż |
="=Warzywa" |
>2000 |
|
Kategoria |
Sprzedawca |
Sprzedaż |
Napoje |
Stąpor |
5122 zł |
Mięso |
Kretowicz |
450 zł |
Warzywa |
Myrcha |
935 zł |
Warzywa |
Kretowicz |
6544 zł |
Napoje |
Myrcha |
3677 zł |
Warzywa |
Kretowicz |
3186 zł |
Formuła |
Opis |
Wynik |
'=BD.ILE.REKORDÓW.A(A6:C12;;A1:C2) |
Zlicza wiersze (2) w zakresie A6:C12, które spełniają kryteria w wierszu 2 (="Warzywa" i >2000). |
=BD.ILE.REKORDÓW.A(A6:C12; ;A1:C2) |
Wiele kryteriów w wielu kolumnach, gdy wystarczy spełnienie dowolnego z kryteriów
Warunek logiczny: (Typ = "Warzywa" LUB Sprzedawca = "Kretowicz")
Aby znaleźć wiersze spełniające wiele kryteriów w wielu kolumnach, gdy wystarczy spełnienie dowolnego kryterium, wpisz kryteria w różnych wierszach zakresu kryteriów.
W poniższym zakresie danych (A6:C10) zakres kryteriów (A1:B3) składa się ze wszystkich wierszy zawierających wartość „Warzywa” w kolumnie Typ lub wartość „Kretowicz”.
Kategoria |
Sprzedawca |
|
="=Warzywa" |
||
="=Kretowicz" |
||
Kategoria |
Sprzedawca |
Sprzedaż |
Napoje |
Stąpor |
5122 zł |
Mięso |
Kretowicz |
675 zł |
warzywa |
Myrcha |
937 zł |
Warzywa |
Myrcha |
|
Formuła |
Opis |
Wynik |
'=BD.ILE.REKORDÓW.A(A6:C10;"Sprzedaż";A1:B3) |
Zlicza wiersze (2) w zakresie A6:C10, które spełniają dowolny warunek z zakresu A1:C3, gdzie pole „Sprzedaż” nie jest puste. |
=BD.ILE.REKORDÓW.A(A6:C10;"Sprzedaż";A1:B3) |
Wiele zestawów kryteriów, gdy każdy zestaw zawiera kryteria dla wielu kolumn
Warunek logiczny: ((Sprzedawca = "Kretowicz" ORAZ Sprzedaż > 3000) LUB (Sprzedawca = "Myrcha" ORAZ Sprzedaż > 1500))
Aby znaleźć wiersze spełniające wiele zestawów kryteriów, gdy każdy zestaw zawiera kryteria dla wielu kolumn, wprowadź każdy zestaw kryteriów w oddzielnych wierszach.
W poniższym zakresie danych (A6:C10) użyto zakresu kryteriów (B1:C3) do zliczenia wierszy zawierających wartość „Kretowicz” w kolumnie Sprzedawca i wartość większą niż 3000 zł w kolumnie Sprzedaż, a także wierszy zawierających wartość „Myrcha” w kolumnie Sprzedawca i wartość większą niż 1500 zł w kolumnie Sprzedaż.
Kategoria |
Sprzedawca |
Sprzedaż |
="=Kretowicz" |
>3000 |
|
="=Myrcha" |
>1500 |
|
Kategoria |
Sprzedawca |
Sprzedaż |
Napoje |
Stąpor |
5122 zł |
Mięso |
Kretowicz |
450 zł |
warzywa |
Myrcha |
6328 zł |
Warzywa |
Kretowicz |
6544 zł |
Formuła |
Opis |
Wynik |
'=BD.ILE.REKORDÓW.A(A6:C10;;B1:C3) |
Zlicza wiersze (2) w zakresie A6:C10, które spełniają wszystkie warunki z zakresu B1:C3. |
=BD.ILE.REKORDÓW.A(A6:C10; ;B1:C3) |
Wiele zestawów kryteriów, gdy każdy zestaw zawiera kryteria dla jednej kolumny
Warunek logiczny: ((Sprzedaż > 6000 ORAZ Sprzedaż < 6500) LUB (Sprzedaż < 500))
Aby znaleźć wiersze spełniające wiele zestawów kryteriów, gdy każdy zestaw zawiera kryteria dla jednej kolumny, użyj wielu kolumn z tym samym nagłówkiem kolumny.
W poniższym zakresie danych (A6:C10) użyto zakresu kryteriów (C1:D3) do zliczenia wierszy zawierających w kolumnie Sprzedaż wartości od 6000 zł do 6500 zł oraz wartości mniejsze niż 500 zł.
Kategoria |
Sprzedawca |
Sprzedaż |
Sprzedaż |
>6000 |
<6500 |
||
<500 |
|||
Kategoria |
Sprzedawca |
Sprzedaż |
|
Napoje |
Stąpor |
5122 zł |
|
Mięso |
Kretowicz |
450 zł |
|
warzywa |
Myrcha |
6328 zł |
|
Warzywa |
Kretowicz |
6544 zł |
|
Formuła |
Opis |
Wynik |
|
'=BD.ILE.REKORDÓW.A(A6:C10;;C1:D3) |
Zlicza wiersze (2), które spełniają warunki z wiersza 2 (>6000 i <6500) lub warunek z wiersza 3 (<500). |
=BD.ILE.REKORDÓW.A(A6:C10; ;C1:D3) |
Kryteria umożliwiające znalezienie wartości tekstowych zawierających pewne wspólne znaki, ale nie inne
Aby znaleźć wartości tekstowe, które mają jednakowe niektóre znaki, ale nie inne, wykonaj jedną lub kilka spośród następujących czynności:
-
Wpisz jeden lub więcej znaków bez znaku równości (=), aby znaleźć wiersze, które zawierają w kolumnach wartości tekstowe rozpoczynające się tymi znakami. Na przykład wpisanie jako kryterium tekstu Kre spowoduje wyszukanie w programie Excel elementów „Kretowicz”, „Krem” i „Kreślarz”.
-
Użyj symbolu wieloznacznego.
Jako kryteria porównawcze mogą służyć podane niżej symbole wieloznaczne.
Użyj |
Aby znaleźć |
? (znak zapytania) |
Dowolny pojedynczy znak Na przykład k?t umożliwia znalezienie wyrazów „kit” i „kot”. |
* (gwiazdka) |
Dowolna liczba znaków Na przykład k*c umożliwia znalezienie wyrazów „koc” i „kopiec”. |
~ (tylda), a po niej znak ?, * lub ~ |
Znak zapytania, gwiazdka lub tylda Na przykład ciąg fy91~? powoduje wyszukanie ciągu „fy91?” |
W poniższym zakresie danych (A6:C10) użyto zakresu kryteriów (A1:B3) do zliczenia wierszy zaczynających się od znaków „Mi” w kolumnie Typ oraz wierszy, w których drugi znak w kolumnie Sprzedawca to „y”.
Kategoria |
Sprzedawca |
Sprzedaż |
Mi |
||
?y* |
||
Kategoria |
Sprzedawca |
Sprzedaż |
Napoje |
Stąpor |
5122 zł |
Mięso |
Kretowicz |
450 zł |
warzywa |
Myrcha |
6328 zł |
Warzywa |
Kretowicz |
6544 zł |
Formuła |
Opis |
Wynik |
'=BD.ILE.REKORDÓW.A(A6:C10;;A1:B3) |
Zlicza wiersze (3), które spełniają dowolny z warunków z zakresu A1:B3. |
=BD.ILE.REKORDÓW.A(A6:C10; ;A1:B3) |
Kryteria utworzone jako wynik formuły
Jako kryterium można użyć wartości obliczonej, będącej wynikiem formuła. Warto zapamiętać następujące ważne kwestie:
-
Wynikiem formuły musi być wartość PRAWDA lub FAŁSZ.
-
Ponieważ jest używana formuła, należy wprowadzić ją w standardowy sposób. Nie należy wpisywać wyrażenia typu:
=''= wpis ''
-
Nie należy używać etykiety kolumny jako etykiety kryterium; albo należy pozostawić etykietę kryterium pustą, albo użyć etykiety, która nie jest etykietą kolumny w zakresie (w podanych niżej przykładach: Obliczana średnia i Dokładne dopasowanie).
Jeśli w formule jest używana etykieta kolumny zamiast względnego odwołania do komórki lub nazwy zakresu, to w komórce zawierającej kryterium program Excel wyświetla wartość błędu, taką jak #NAZWA? lub #ARG!. Można ten błąd zignorować, ponieważ nie wpływa on na sposób filtrowania zakresu.
-
W formule, która pełni funkcję kryterium, należy użyć odwołanie względne do odpowiedniej komórki w pierwszym wierszu.
-
Wszystkie pozostałe odwołania w formule muszą być odwołaniami bezwzględnymi.
Filtrowanie wartości większych niż średnia wszystkich wartości z przedziału danych
W poniższym zakresie danych (A6:C10) użyto zakresu kryteriów (C1:C2) do zliczenia wierszy zawierających w kolumnie Sprzedaż wartość większą niż średnia wszystkich wartości Sprzedaż (C7:C10). Średnia jest obliczana w komórce C4, a wynik jest w komórce C2 łączony z formułą =">"&C4 w celu utworzenia używanych kryteriów.
Sprzedaż |
||
=ZŁĄCZ.TEKSTY(">";C4) |
||
Obliczona średnia |
||
=ŚREDNIA(C7:C10) |
||
Kategoria |
Sprzedawca |
Sprzedaż |
Napoje |
Stąpor |
5122 zł |
Mięso |
Kretowicz |
450 zł |
warzywa |
Myrcha |
6328 zł |
Warzywa |
Kretowicz |
6544 zł |
Formuła |
Opis |
Wynik |
'=BD.ILE.REKORDÓW.A(A6:C10;;C1:C2) |
Zlicza wiersze (3), które spełniają warunek (>4611) z zakresu C1:C2. Warunek w komórce C2 został utworzony przez złączenie znaku =">" z komórką C4, która zawiera średnią obliczoną dla zakresu C7:C10. |
=BD.ILE.REKORDÓW.A(A6:C10; ;C1:C2) |