BD.ŚREDNIA
Dotyczy: Microsoft Office Excel 2003
Uśrednia wartości w kolumnie listy lub bazy danych, spełniające podane kryteria.
Składnia
BD.ŚREDNIA(baza danych;pole;kryteria)
Baza danych to 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 wskazuje, która kolumna jest używana w funkcji. Należy wprowadzić etykietę kolumny umieszczoną w podwójnym cudzysłowie, taką jak "Wiek" lub "Plon", lub liczbę (bez cudzysłowów) reprezentującą pozycję kolumny na liście: 1 dla pierwszej kolumny, 2 dla drugiej kolumny itd.
Kryteria to zakres komórek, który zawiera warunki określone przez użytkownika. Jako argumentu kryteriów można użyć dowolnego zakresu pod warunkiem, że zawiera on przynajmniej jedną etykietę kolumny i jedną komórkę poniżej etykiety, służącą do określania warunku.
Ważne
Podczas wpisywania tekstu lub wartości w komórce znak równości oznacza formułę, dlatego program Microsoft Excel oblicza wartość takiego wpisu. Może to jednak powodować uzyskanie nieoczekiwanych wyników filtrowania. Aby wskazać, że znak równości jest operatorem porównania tekstu lub wartości, należy w odpowiedniej komórce zakresu kryteriów wpisać kryteria w postaci wyrażenia tekstowego:
=''=wpis''
Gdzie wpis oznacza tekst lub wartość do znalezienia. Na przykład:
Wpis w komórce |
Wynik wyświetlony w programie Excel |
="=Stąpor" |
=Stąpor |
="=3000" |
=3000 |
Podczas filtrowania danych tekstowych w programie Excel nie są rozróżniane wielkie i małe litery. Przy użyciu formuły można jednak przeprowadzić wyszukiwanie z rozróżnianiem wielkości liter. Przykład można znaleźć w sekcji Filtrowanie tekstu przy użyciu wyszukiwania z rozróżnianiem wielkości liter.
W poniższych sekcjach przedstawiono przykłady kryteriów złożonych.
Wiele kryteriów w jednej kolumnie
Operator logiczny: (Sprzedawca = „Stąpor” LUB Sprzedawca = „Myrcha”)
Aby znaleźć wiersze spełniające wiele kryteriów dla jednej kolumny, należy wpisać kryteria bezpośrednio jedno pod drugim w oddzielnych wierszach zakresu kryteriów.
W poniższym zakresie danych (A6:C10) dla zakresu kryteriów (B1:B3) są wyświetlane wiersze zawierające w kolumnie Sprzedawca (A8:C10) wartość „Stąpor” lub „Myrcha”.
|
A |
B |
C |
1 |
Typ |
Sprzedawca |
Sprzedaż |
2 |
|
=Stąpor |
|
3 |
|
=Myrcha |
|
4 |
|
|
|
5 |
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
7 |
Napoje |
Karwat |
5122 zł |
8 |
Mięso |
Stąpor |
450 zł |
9 |
Bakalie |
Myrcha |
6328 zł |
10 |
Bakalie |
Stąpor |
6544 zł |
Wiele kryteriów w wielu kolumnach — wszystkie kryteria muszą być prawdziwe
Operator logiczny: (Typ = "Bakalie" ORAZ Sprzedaż > 1000)
Aby znaleźć wiersze spełniające wiele kryteriów w wielu kolumnach, należy wpisać wszystkie kryteria w tym samym wierszu zakresu kryteriów.
W poniższym zakresie danych (A6:C10) dla zakresu kryteriów (A1:C2) są wyświetlane wszystkie wiersze zawierające w kolumnie Typ wartość „Bakalie”, a w kolumnie Sprzedaż (A9:C10) wartość większą niż 1000 zł.
|
A |
B |
C |
1 |
Typ |
Sprzedawca |
Sprzedaż |
2 |
=Bakalie |
|
>1000 |
3 |
|
|
|
4 |
|
|
|
5 |
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
7 |
Napoje |
Karwat |
5122 zł |
8 |
Mięso |
Stąpor |
450 zł |
9 |
Bakalie |
Myrcha |
6328 zł |
10 |
Bakalie |
Stąpor |
6544 zł |
Wiele kryteriów w wielu kolumnach — dowolne kryteria mogą być prawdziwe
Operator logiczny: (Typ = "Bakalie" LUB Sprzedawca = "Stąpor")
Aby znaleźć wiersze spełniające wiele kryteriów w wielu kolumnach (przy czym dowolne kryteria mogą być prawdziwe), należy wpisać kryteria w różnych wierszach zakresu kryteriów.
W poniższym zakresie danych (A6:C10) dla zakresu kryteriów (A1:B3) są wyświetlane wszystkie wiersze zawierające wartość „Bakalie” w kolumnie Typ lub wartość „Stąpor” w kolumnie Sprzedawca (A8:C10).
|
A |
B |
C |
1 |
Typ |
Sprzedawca |
Sprzedaż |
2 |
=Bakalie |
|
|
3 |
|
=Stąpor |
|
4 |
|
|
|
5 |
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
7 |
Napoje |
Karwat |
5122 zł |
8 |
Mięso |
Stąpor |
450 zł |
9 |
Bakalie |
Myrcha |
6328 zł |
10 |
Bakalie |
Stąpor |
6544 zł |
Wiele zestawów kryteriów — każdy zestaw zawiera kryteria dla wielu kolumn
Operator logiczny: ((Sprzedawca = "Stąpor" ORAZ Sprzedaż >3000) LUB (Sprzedawca= "Myrcha" ORAZ Sprzedaż > 1500))
Aby znaleźć wiersze spełniające wiele zestawów kryteriów (przy czym każdy zestaw zawiera kryteria dla wielu kolumn), należy wpisać każdy zestaw kryteriów w oddzielnym wierszu.
W poniższym zakresie danych (A6:C10) dla zestawu kryteriów (B1:C3) są wyświetlane wiersze, które w kolumnie Sprzedawca zawierają wartość „Stąpor”, a w kolumnie Sprzedaż wartość większą niż 3000 zł, bądź wiersze, które w kolumnie Sprzedawca zawierają wartość „Myrcha”, a w kolumnie Sprzedaż wartość większą niż 1500 zł (A9:C10).
|
A |
B |
C |
1 |
Typ |
Sprzedawca |
Sprzedaż |
2 |
|
=Stąpor |
>3000 |
3 |
|
=Myrcha |
>1500 |
4 |
|
|
|
5 |
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
7 |
Napoje |
Karwat |
5122 zł |
8 |
Mięso |
Stąpor |
450 zł |
9 |
Bakalie |
Myrcha |
6328 zł |
10 |
Bakalie |
Stąpor |
6544 zł |
Wiele zestawów kryteriów — każdy zestaw zawiera kryteria dla jednej kolumny
Operator logiczny: ((Sprzedaż > 6000 ORAZ Sprzedaż < 6500) LUB (Sprzedaż < 500))
Aby znaleźć wiersze spełniające wiele zestawów kryteriów (przy czym każdy zestaw zawiera kryteria dla jednej kolumny), należy uwzględnić wiele kolumn o tym samym nagłówku.
W poniższym zakresie danych (A6:C10) dla zestawu kryteriów (C1:D3) są wyświetlane wiersze zawierające w kolumnie Sprzedaż (A8:C10) wartości od 6000 do 6500 oraz wartości mniejsze niż 500.
|
A |
B |
C |
D |
1 |
Typ |
Sprzedawca |
Sprzedaż |
Sprzedaż |
2 |
|
|
>6000 |
<6500 |
3 |
|
|
<500 |
|
4 |
|
|
|
|
5 |
|
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
|
7 |
Napoje |
Karwat |
5122 zł |
|
8 |
Mięso |
Stąpor |
450 zł |
|
9 |
Bakalie |
Myrcha |
6328 zł |
|
10 |
Bakalie |
Stąpor |
6544 zł |
|
Kryteria umożliwiające znajdowanie wartości tekstowych zawierających pewne znaki, ale niezawierających innych
Aby znaleźć wartości tekstowe, które zawierają pewne znaki, ale nie zawierają innych, należy wykonać co najmniej jedną z 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 Stą spowoduje wyszukanie w programie Excel elementów „Stąpor”, „Stągwie” i „Stąporków”.
Użyj symbolu wieloznacznego.
Jako kryteriów porównania można użyć następujących symboli wieloznacznych.
Zastosowanie |
Aby znaleźć element |
? (znak zapytania) |
Dowolny pojedynczy znak |
* (gwiazdka) |
Dowolna liczba znaków |
~ (tylda), a po niej znak ?, * lub ~ |
Znak zapytania, gwiazdka lub tylda |
W poniższym zakresie danych (A6:C10) dla zakresu kryteriów (A1:B3) są wyświetlane wiersze, w których pierwszymi znakami w kolumnie Typ są litery „Mi”, lub wiersze, w których drugim znakiem w kolumnie sprzedawca (A7:C9) jest „a”.
|
A |
B |
C |
1 |
Typ |
Sprzedawca |
Sprzedaż |
2 |
Mi |
|
|
3 |
|
=?a* |
|
4 |
|
|
|
5 |
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
7 |
Napoje |
Karwat |
5122 zł |
8 |
Mięso |
Stąpor |
450 zł |
9 |
Bakalie |
Myrcha |
6328 zł |
10 |
Bakalie |
Stąpor |
6544 zł |
Kryteria utworzone w wyniku użycia formuły
Jako kryterium można użyć wartości obliczonej będącej wynikiem formuły (formuła: Sekwencja wartości, odwołań do komórek, nazw, funkcji lub operatorów w komórce, które razem dają nową wartość. Formuła zawsze zaczyna się od znaku równości (=).). Należy pamiętać o następujących zasadach:
Formuła musi dawać w wyniku 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 kryteriów. Etykiety kryteriów należy pozostawić puste. Można również użyć etykiety niebędącej etykietą kolumny w zakresie (w poniższych przykładach są to Obliczona średnia i Dokładne dopasowanie).
Jeśli w formule zamiast względnego odwołania do komórki lub nazwy zakresu jest używana etykieta kolumny, w komórce zawierającej kryterium program Excel powoduje wyświetlenie wartości błędu, takiej jak #NAZWA? lub #ARG!. Ten błąd można zignorować, ponieważ nie wpływa on na sposób filtrowania zakresu.
Formuła używana dla kryteriów musi odwoływać się do odpowiedniej komórki w pierwszym wierszu (w poniższych przykładach są to C7 i A7) przy użyciu odwołania względnego (odwołanie względne: W formule jest to adres komórki oparty na względnym położeniu komórki, która zawiera formułę, i komórki, do której następuje odwołanie. Jeśli formuła zostanie skopiowana, odwołanie jest automatycznie dostosowywane. Odwołanie względne ma postać A1.).
Wszystkie pozostałe odwołania w formule muszą być odwołaniami bezwzględnymi (bezwzględne odwołanie do komórki: W formule jest to dokładny adres komórki, niezależny od położenia komórki, która zawiera formułę. Bezwzględne odwołanie do komórki ma postać $A$1.).
W poniższych podsekcjach znajdują się konkretne przykłady kryteriów utworzonych w wyniku działania formuły.
Filtrowanie wartości większych niż średnia wszystkich wartości w zakresie danych
W poniższym zakresie danych (A6:D10) dla zakresu kryteriów (D1:D2) są wyświetlane wiersze, których wartość w kolumnie Sprzedaż jest większa niż średnia wszystkich wartości w kolumnie Sprzedaż (C7:C10). „C7” w formule oznacza filtrowaną kolumnę (C) pierwszego wiersza zakresu danych (7).
|
A |
B |
C |
D |
1 |
Typ |
Sprzedawca |
Sprzedaż |
Obliczona średnia |
2 |
|
|
|
=C7>ŚREDNIA($C$7:$C$10) |
3 |
|
|
|
|
4 |
|
|
|
|
5 |
|
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
|
7 |
Napoje |
Karwat |
5122 zł |
|
8 |
Mięso |
Stąpor |
450 zł |
|
9 |
Bakalie |
Myrcha |
6328 zł |
|
10 |
Bakalie |
Stąpor |
6544 zł |
|
Filtrowanie tekstu przy użyciu wyszukiwania z rozróżnianiem wielkości liter
W wyniku wyszukiwania z rozróżnianiem wielkości liter (A10:C10) przy użyciu funkcji PORÓWNAJ w zakresie danych (A6:D10) dla zakresu kryteriów (D1:D2) są wyświetlane wiersze, które w kolumnie Typ zawierają wartość „Bakalie”. „A7” w formule oznacza filtrowaną kolumnę (A) pierwszego wiersza zakresu danych (7).
|
A |
B |
C |
D |
1 |
Typ |
Sprzedawca |
Sprzedaż |
Dokładne dopasowanie |
2 |
|
|
|
=PORÓWNAJ(A7; "Bakalie") |
3 |
|
|
|
|
4 |
|
|
|
|
5 |
|
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
|
7 |
Napoje |
Karwat |
5122 zł |
|
8 |
Mięso |
Stąpor |
450 zł |
|
9 |
Bakalie |
Myrcha |
6328 zł |
|
10 |
Bakalie |
Stąpor |
6544 zł |
|
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.
Na przykład, jeśli zakres G1:G2 zawiera etykietę kolumny Przychód w komórce G1 i kwotę 10 000 w komórce G2, to zakres taki można zdefiniować jako „DobierzPrzychód” i użyć tej nazwy jako argumentu kryteriów w funkcji bazy danych.
Chociaż zakres kryteriów może być zlokalizowany w dowolnym miejscu arkusza, nie należy umieszczać go poniżej listy. Jeśli do listy zostanie dodanych więcej informacji z wykorzystaniem polecenia Formularz, które jest dostępne w menu Dane, to nowe informacje będą dodawane w pierwszym wierszu poniżej listy. Jeśli wiersz poniżej listy nie jest pusty, program Microsoft 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ład
Przykład będzie bardziej zrozumiały po skopiowaniu go do pustego arkusza.
Utwórz pusty skoroszyt lub arkusz.
Zaznacz przykład w tym temacie Pomocy.
Uwaga Nie zaznaczaj nagłówków wierszy ani kolumn.
Zaznaczanie przykładu w Pomocy
Naciśnij klawisze CTRL+C
W arkuszu zaznacz komórkę A1 i naciśnij klawisze CTRL+V.
Aby przełączać się między wyświetlaniem wyników i wyświetlaniem formuł zwracających te wyniki, naciśnij klawisz CTRL+` (akcent słaby) albo na karcie Formuły w grupie Inspekcja formuł kliknij przycisk Pokaż formuły.
|
|
BD.ILE.REKORDÓW
Dotyczy: Microsoft Office Excel 2003
Zlicza komórki zawierające liczby znajdujące się w kolumnie listy lub bazy danych, które są zgodne z warunkami określonymi przez użytkownika.
Argument pole jest opcjonalny. Jeżeli argument pole zostanie pominięty, funkcja BD.ILE.REKORDÓW zlicza wszystkie rekordy w bazie danych zgodne z kryteriami.
Składnia
BD.ILE.REKORDÓW(baza danych;pole;kryteria)
Baza danych to 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 wskazuje, która kolumna jest używana w funkcji. Należy wprowadzić etykietę kolumny umieszczoną w podwójnym cudzysłowie, taką jak "Wiek" lub "Plon", lub liczbę (bez cudzysłowów) reprezentującą pozycję kolumny na liście: 1 dla pierwszej kolumny, 2 dla drugiej kolumny itd.
Kryteria to zakres komórek, który zawiera warunki określone przez użytkownika. Jako argumentu kryteriów można użyć dowolnego zakresu pod warunkiem, że zawiera on przynajmniej jedną etykietę kolumny i jedną komórkę poniżej etykiety służącą do określania warunku.
Ważne
Podczas wpisywania tekstu lub wartości w komórce znak równości oznacza formułę, dlatego program Microsoft Excel oblicza wartość takiego wpisu. Może to jednak powodować uzyskanie nieoczekiwanych wyników filtrowania. Aby wskazać, że znak równości jest operatorem porównania tekstu lub wartości, należy w odpowiedniej komórce zakresu kryteriów wpisać kryteria w postaci wyrażenia tekstowego:
=''=wpis''
Gdzie wpis oznacza tekst lub wartość do znalezienia. Na przykład:
Wpis w komórce |
Wynik wyświetlony w programie Excel |
="=Stąpor" |
=Stąpor |
="=3000" |
=3000 |
Podczas filtrowania danych tekstowych w programie Excel nie są rozróżniane wielkie i małe litery. Przy użyciu formuły można jednak przeprowadzić wyszukiwanie z rozróżnianiem wielkości liter. Przykład można znaleźć w sekcji Filtrowanie tekstu przy użyciu wyszukiwania z rozróżnianiem wielkości liter.
W poniższych sekcjach przedstawiono przykłady kryteriów złożonych.
Wiele kryteriów w jednej kolumnie
Operator logiczny: (Sprzedawca = „Stąpor” LUB Sprzedawca = „Myrcha”)
Aby znaleźć wiersze spełniające wiele kryteriów dla jednej kolumny, należy wpisać kryteria bezpośrednio jedno pod drugim w oddzielnych wierszach zakresu kryteriów.
W poniższym zakresie danych (A6:C10) dla zakresu kryteriów (B1:B3) są wyświetlane wiersze zawierające w kolumnie Sprzedawca (A8:C10) wartość „Stąpor” lub „Myrcha”.
|
A |
B |
C |
1 |
Typ |
Sprzedawca |
Sprzedaż |
2 |
|
=Stąpor |
|
3 |
|
=Myrcha |
|
4 |
|
|
|
5 |
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
7 |
Napoje |
Karwat |
5122 zł |
8 |
Mięso |
Stąpor |
450 zł |
9 |
Bakalie |
Myrcha |
6328 zł |
10 |
Bakalie |
Stąpor |
6544 zł |
Wiele kryteriów w wielu kolumnach — wszystkie kryteria muszą być prawdziwe
Operator logiczny: (Typ = "Bakalie" ORAZ Sprzedaż > 1000)
Aby znaleźć wiersze spełniające wiele kryteriów w wielu kolumnach, należy wpisać wszystkie kryteria w tym samym wierszu zakresu kryteriów.
W poniższym zakresie danych (A6:C10) dla zakresu kryteriów (A1:C2) są wyświetlane wszystkie wiersze zawierające w kolumnie Typ wartość „Bakalie”, a w kolumnie Sprzedaż (A9:C10) wartość większą niż 1000 zł.
|
A |
B |
C |
1 |
Typ |
Sprzedawca |
Sprzedaż |
2 |
=Bakalie |
|
>1000 |
3 |
|
|
|
4 |
|
|
|
5 |
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
7 |
Napoje |
Karwat |
5122 zł |
8 |
Mięso |
Stąpor |
450 zł |
9 |
Bakalie |
Myrcha |
6328 zł |
10 |
Bakalie |
Stąpor |
6544 zł |
Wiele kryteriów w wielu kolumnach — dowolne kryteria mogą być prawdziwe
Operator logiczny: (Typ = "Bakalie" LUB Sprzedawca = "Stąpor")
Aby znaleźć wiersze spełniające wiele kryteriów w wielu kolumnach (przy czym dowolne kryteria mogą być prawdziwe), należy wpisać kryteria w różnych wierszach zakresu kryteriów.
W poniższym zakresie danych (A6:C10) dla zakresu kryteriów (A1:B3) są wyświetlane wszystkie wiersze zawierające wartość „Bakalie” w kolumnie Typ lub wartość „Stąpor” w kolumnie Sprzedawca (A8:C10).
|
A |
B |
C |
1 |
Typ |
Sprzedawca |
Sprzedaż |
2 |
=Bakalie |
|
|
3 |
|
=Stąpor |
|
4 |
|
|
|
5 |
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
7 |
Napoje |
Karwat |
5122 zł |
8 |
Mięso |
Stąpor |
450 zł |
9 |
Bakalie |
Myrcha |
6328 zł |
10 |
Bakalie |
Stąpor |
6544 zł |
Wiele zestawów kryteriów — każdy zestaw zawiera kryteria dla wielu kolumn
Operator logiczny: ((Sprzedawca = "Stąpor" ORAZ Sprzedaż >3000) LUB (Sprzedawca= "Myrcha" ORAZ Sprzedaż > 1500))
Aby znaleźć wiersze spełniające wiele zestawów kryteriów (przy czym każdy zestaw zawiera kryteria dla wielu kolumn), należy wpisać każdy zestaw kryteriów w oddzielnym wierszu.
W poniższym zakresie danych (A6:C10) dla zestawu kryteriów (B1:C3) są wyświetlane wiersze, które w kolumnie Sprzedawca zawierają wartość „Stąpor”, a w kolumnie Sprzedaż wartość większą niż 3000 zł, bądź wiersze, które w kolumnie Sprzedawca zawierają wartość „Myrcha”, a w kolumnie Sprzedaż wartość większą niż 1500 zł (A9:C10).
|
A |
B |
C |
1 |
Typ |
Sprzedawca |
Sprzedaż |
2 |
|
=Stąpor |
>3000 |
3 |
|
=Myrcha |
>1500 |
4 |
|
|
|
5 |
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
7 |
Napoje |
Karwat |
5122 zł |
8 |
Mięso |
Stąpor |
450 zł |
9 |
Bakalie |
Myrcha |
6328 zł |
10 |
Bakalie |
Stąpor |
6544 zł |
Wiele zestawów kryteriów — każdy zestaw zawiera kryteria dla jednej kolumny
Operator logiczny: ((Sprzedaż > 6000 ORAZ Sprzedaż < 6500) LUB (Sprzedaż < 500))
Aby znaleźć wiersze spełniające wiele zestawów kryteriów (przy czym każdy zestaw zawiera kryteria dla jednej kolumny), należy uwzględnić wiele kolumn o tym samym nagłówku.
W poniższym zakresie danych (A6:C10) dla zestawu kryteriów (C1:D3) są wyświetlane wiersze zawierające w kolumnie Sprzedaż (A8:C10) wartości od 6000 do 6500 oraz wartości mniejsze niż 500.
|
A |
B |
C |
D |
1 |
Typ |
Sprzedawca |
Sprzedaż |
Sprzedaż |
2 |
|
|
>6000 |
<6500 |
3 |
|
|
<500 |
|
4 |
|
|
|
|
5 |
|
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
|
7 |
Napoje |
Karwat |
5122 zł |
|
8 |
Mięso |
Stąpor |
450 zł |
|
9 |
Bakalie |
Myrcha |
6328 zł |
|
10 |
Bakalie |
Stąpor |
6544 zł |
|
Kryteria umożliwiające znajdowanie wartości tekstowych zawierających pewne znaki, ale niezawierających innych
Aby znaleźć wartości tekstowe, które zawierają pewne znaki, ale nie zawierają innych, należy wykonać co najmniej jedną z 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 Stą spowoduje wyszukanie w programie Excel elementów „Stąpor”, „Stągwie” i „Stąporków”.
Użyj symbolu wieloznacznego.
Jako kryteriów porównania można użyć następujących symboli wieloznacznych.
Zastosowanie |
Aby znaleźć element |
? (znak zapytania) |
Dowolny pojedynczy znak |
* (gwiazdka) |
Dowolna liczba znaków |
~ (tylda), a po niej znak ?, * lub ~ |
Znak zapytania, gwiazdka lub tylda |
W poniższym zakresie danych (A6:C10) dla zakresu kryteriów (A1:B3) są wyświetlane wiersze, w których pierwszymi znakami w kolumnie Typ są litery „Mi”, lub wiersze, w których drugim znakiem w kolumnie sprzedawca (A7:C9) jest „a”.
|
A |
B |
C |
1 |
Typ |
Sprzedawca |
Sprzedaż |
2 |
Mi |
|
|
3 |
|
=?a* |
|
4 |
|
|
|
5 |
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
7 |
Napoje |
Karwat |
5122 zł |
8 |
Mięso |
Stąpor |
450 zł |
9 |
Bakalie |
Myrcha |
6328 zł |
10 |
Bakalie |
Stąpor |
6544 zł |
Kryteria utworzone w wyniku użycia formuły
Jako kryterium można użyć wartości obliczonej będącej wynikiem formuły (formuła: Sekwencja wartości, odwołań do komórek, nazw, funkcji lub operatorów w komórce, które razem dają nową wartość. Formuła zawsze zaczyna się od znaku równości (=).). Należy pamiętać o następujących zasadach:
Formuła musi dawać w wyniku 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 kryteriów. Etykiety kryteriów należy pozostawić puste. Można również użyć etykiety niebędącej etykietą kolumny w zakresie (w poniższych przykładach są to Obliczona średnia i Dokładne dopasowanie).
Jeśli w formule zamiast względnego odwołania do komórki lub nazwy zakresu jest używana etykieta kolumny, w komórce zawierającej kryterium program Excel powoduje wyświetlenie wartości błędu, takiej jak #NAZWA? lub #ARG!. Ten błąd można zignorować, ponieważ nie wpływa on na sposób filtrowania zakresu.
Formuła używana dla kryteriów musi odwoływać się do odpowiedniej komórki w pierwszym wierszu (w poniższych przykładach są to C7 i A7) przy użyciu odwołania względnego (odwołanie względne: W formule jest to adres komórki oparty na względnym położeniu komórki, która zawiera formułę, i komórki, do której następuje odwołanie. Jeśli formuła zostanie skopiowana, odwołanie jest automatycznie dostosowywane. Odwołanie względne ma postać A1.).
Wszystkie pozostałe odwołania w formule muszą być odwołaniami bezwzględnymi (bezwzględne odwołanie do komórki: W formule jest to dokładny adres komórki, niezależny od położenia komórki, która zawiera formułę. Bezwzględne odwołanie do komórki ma postać $A$1.).
W poniższych podsekcjach znajdują się konkretne przykłady kryteriów utworzonych w wyniku działania formuły.
Filtrowanie wartości większych niż średnia wszystkich wartości w zakresie danych
W poniższym zakresie danych (A6:D10) dla zakresu kryteriów (D1:D2) są wyświetlane wiersze, których wartość w kolumnie Sprzedaż jest większa niż średnia wszystkich wartości w kolumnie Sprzedaż (C7:C10). „C7” w formule oznacza filtrowaną kolumnę (C) pierwszego wiersza zakresu danych (7).
|
A |
B |
C |
D |
1 |
Typ |
Sprzedawca |
Sprzedaż |
Obliczona średnia |
2 |
|
|
|
=C7>ŚREDNIA($C$7:$C$10) |
3 |
|
|
|
|
4 |
|
|
|
|
5 |
|
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
|
7 |
Napoje |
Karwat |
5122 zł |
|
8 |
Mięso |
Stąpor |
450 zł |
|
9 |
Bakalie |
Myrcha |
6328 zł |
|
10 |
Bakalie |
Stąpor |
6544 zł |
|
Filtrowanie tekstu przy użyciu wyszukiwania z rozróżnianiem wielkości liter
W wyniku wyszukiwania z rozróżnianiem wielkości liter (A10:C10) przy użyciu funkcji PORÓWNAJ w zakresie danych (A6:D10) dla zakresu kryteriów (D1:D2) są wyświetlane wiersze, które w kolumnie Typ zawierają wartość „Bakalie”. „A7” w formule oznacza filtrowaną kolumnę (A) pierwszego wiersza zakresu danych (7).
|
A |
B |
C |
D |
1 |
Typ |
Sprzedawca |
Sprzedaż |
Dokładne dopasowanie |
2 |
|
|
|
=PORÓWNAJ(A7; "Bakalie") |
3 |
|
|
|
|
4 |
|
|
|
|
5 |
|
|
|
|
6 |
Typ |
Sprzedawca |
Sprzedaż |
|
7 |
Napoje |
Karwat |
5122 zł |
|
8 |
Mięso |
Stąpor |
450 zł |
|
9 |
Bakalie |
Myrcha |
6328 zł |
|
10 |
Bakalie |
Stąpor |
6544 zł |
|
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 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 być zlokalizowany w dowolnym miejscu arkusza, nie należy umieszczać go poniżej listy. Jeśli do listy zostanie dodanych więcej informacji z wykorzystaniem polecenia Formularz, które jest dostępne w menu Dane, to nowe informacje będą dodawane w pierwszym wierszu poniżej listy. Jeśli wiersz poniżej listy nie jest pusty, program Microsoft 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ład
Przykład będzie bardziej zrozumiały po skopiowaniu go do pustego arkusza.
Utwórz pusty skoroszyt lub arkusz.
Zaznacz przykład w tym temacie Pomocy.
Uwaga Nie zaznaczaj nagłówków wierszy ani kolumn.
Zaznaczanie przykładu w Pomocy
Naciśnij klawisze CTRL+C
W arkuszu zaznacz komórkę A1 i naciśnij klawisze CTRL+V.
Aby przełączać się między wyświetlaniem wyników i wyświetlaniem formuł zwracających te wyniki, naciśnij klawisz CTRL+` (akcent słaby) albo na karcie Formuły w grupie Inspekcja formuł kliknij przycisk Pokaż formuły.
|
|
Najważniejsze porady dotyczące programu Excel: Praca z danymi
Dotyczy: Microsoft Office Excel 2003
Dotyczy programów: |
Microsoft Office Excel 2003 |
Wiele osób chciałoby podczas pracy z programem Excel mieć do dyspozycji eksperta zawsze gotowego służyć użytecznymi poradami i wskazującego najlepsze rozwiązania dotyczące pracy z danymi. Jeżeli do nich należysz, ten artykuł jest dla Ciebie! W poniższych sekcjach znajdziesz informacje o nowych i szybszych sposobach pracy z danymi w programie Excel.
Zaznaczanie danych
Aby zaznaczyć niesąsiadujące komórki lub zakresy, zaznacz pierwszą komórkę lub zakres, a następnie zaznacz pozostałe komórki lub zakresy, przytrzymując naciśnięty klawisz CTRL.
Aby zaznaczyć tylko puste komórki w regionie, zaznacz region, kliknij polecenie Przejdź do w menu Edycja, kliknij przycisk Specjalnie, a następnie kliknij opcję Puste.
Zaznaczenie dużego zakresu jest łatwe. Wystarczy kliknąć komórkę w rogu zakresu, przewinąć do przeciwległego rogu, a następnie kliknąć komórkę w tym rogu, przytrzymując jednocześnie naciśnięty klawisz SHIFT.
Istnieje możliwość określenia kierunku, w którym będzie przesuwane zaznaczenie po wprowadzeniu danych w komórce (na przykład w prawo w przypadku pracy w wierszach). W tym celu kliknij polecenie Opcje w menu Narzędzia, upewnij się, że na karcie Edycja jest zaznaczone pole wyboru Przenoś zaznaczenie po naciśnięciu klawisza Enter, a następnie kliknij kierunek w polu Kierunek.
Do określonego obszaru arkusza kalkulacyjnego (na przykład do bieżącego regionu lub do ostatniej komórki) możesz przejść, klikając polecenie Przejdź do w menu Edycja. Następnie kliknij przycisk Specjalnie i wybierz odpowiednią opcję.
Edytowanie danych
Zawartość komórki można edytować bezpośrednio w komórce, klikając ją dwukrotnie. Nie trzeba korzystać z paska formuły.
Aby wyłączyć funkcję edycji w komórce, w menu Narzędzia kliknij polecenie Opcje, kliknij kartę Edycja, a następnie wyczyść pole wyboru Edytuj bezpośrednio w komórce.
Aby dodać kolejne wartości liczby w komórkach, przeciągnij uchwyt wypełnienia w rogu zaznaczenia, przytrzymując jednocześnie naciśnięty klawisz CTRL.
Aby dodać kolejne dni tygodnia, kwartały lub inne wartości serii w komórkach, zaznacz pierwszy element, a następnie przeciągnij uchwyt wypełnienia znajdujący się w rogu.
Aby wypełnić sąsiadujące komórki zgodnie z powtarzalnym wzorcem, utwórz niestandardowe wypełnienie serią. W tym celu kliknij polecenie Opcje w menu Narzędzia, a następnie kliknij kartę Listy niestandardowe.
Kliknięcie prawym przyciskiem myszy komórki, obiektu lub elementu wykresu powoduje wyświetlenie menu skrótów zawierającego przydatne polecenia.
Sposób wypełniania komórek danymi znajdującymi się w danej komórce lub zakresie można określić, klikając prawym przyciskiem myszy uchwyt wypełnienia znajdujący się w rogu tej komórki lub zakresu i przeciągając go, a następnie klikając opcję wypełnienia w wyświetlonym menu skrótów.
Podczas automatycznego wypełniania komórek program Excel może zwiększać jednocześnie rok i kwartał. Na przykład: 1Kw.93, Kw.193, 1. Kw. 93, 1. Kwartał 1993.
Zakres zaznaczonych komórek można usunąć, przeciągając uchwyt wypełnienia (znajdujący się w prawym dolnym rogu zaznaczenia) do góry i w lewo przy naciśniętym klawiszu SHIFT.
Zawartość komórki (lub kolumny komórek) można rozpowszechnić na wiele kolumn. W tym celu zaznacz komórkę lub kolumnę, a następnie kliknij polecenie Tekst jako kolumny w menu Dane.
Aby szybko wstawić zakres pustych komórek, przeciągnij uchwyt wypełnienia w rogu zaznaczenia, trzymając naciśnięty klawisz SHIFT.
Aby przejrzeć wszystkie nazwane komórki lub zakresy używane w skoroszycie, kliknij strzałkę obok pola Nazwa na pasku formuły.
Moduł sprawdzania pisowni można uruchomić, naciskając klawisz F7.
Aby przenieść zawartość komórek, przeciągnij obramowanie zaznaczonych komórek do innej lokalizacji. Aby skopiować zawartość komórek, podczas przeciągania przytrzymaj naciśnięty klawisz CTRL.
Możliwa jest edycja zawartości kilku arkuszy jednocześnie. W tym celu kliknij karty arkuszy przeznaczonych do edycji, przytrzymując jednocześnie naciśnięty klawisz CTRL, a następnie zmodyfikuj dane.
Przy każdym zapisie warto wykonać kopię zapasową. W tym celu w oknie dialogowym Zapisywanie jako kliknij przycisk Narzędzia, kliknij opcję Opcje ogólne, a następnie zaznacz pole wyboru Zawsze twórz kopię zapasową.
Formatowanie danych
Do tworzenia wcięcia tekstu w komórce można użyć przycisków Zwiększ wcięcie i Zmniejsz wcięcie na pasku narzędzi Formatowanie.
Łatwym sposobem zastosowania niestandardowego stylu z innego skoroszytu jest skopiowanie komórek sformatowanych za pomocą tego stylu i wklejenie ich do nowego skoroszytu.
Kliknięcie prawym przyciskiem myszy nagłówka kolumny lub wiersza spowoduje wyświetlenie menu skrótów zawierającego polecenia Szerokość kolumny, Wysokość wiersza, Ukryj i Odkryj.
Dwukrotne kliknięcie przycisku Malarz formatów na pasku narzędzi Standardowy spowoduje zaznaczenie go na stałe, co umożliwia zastosowanie tego samego formatu w wielu obszarach.
Aby zablokować wiersz w górnej części okna, zaznacz wiersz poniżej, a następnie kliknij polecenie Zablokuj okienka w menu Okno.
Aby zastosować wbudowany format tabeli do zakresu komórek, kliknij polecenie Autoformatowanie w menu Format.
Istnieje możliwość określenia domyślnej czcionki dla przyszłych skoroszytów. W tym celu w menu Narzędzia kliknij polecenie Opcje, a następnie na karcie Ogólne zmień opcje standardowej czcionki i rozmiaru.
Aby zmienić domyślny format tekstu dla całego skoroszytu, kliknij polecenie Styl w menu Format, a następnie zmień format stylu normalnego.
Aby przekonwertować wstępnie sformatowany tekst HTML na tabelę, zaznacz ten tekst, a następnie kliknij polecenie Tekst jako kolumny w menu Dane.
Wiersze i kolumny można grupować, aby ułatwić ich pokazywanie i ukrywanie. W tym celu zaznacz wiersze i kolumny, wskaż polecenie Grupy i konspekt w menu Dane, kliknij opcję Grupa, a następnie kliknij opcję Wiersze lub kolumny.
Aby wyświetlić liczby w tysiącach lub milionach, utwórz nowy format liczb. W tym celu w menu Format kliknij polecenie Komórki, a następnie kliknij kategorię Niestandardowe. W polu Typ wpisz 0, lub #, w przypadku tysięcy i 0,, lub #,, w przypadku milionów.
Wartość można wprowadzić w złotówkach, wpisując symbol złotego (zł) po tej wartości.
Wartość można wprowadzić w formacie procentowym, wpisując znak procentu (%) po tej wartości.
Liczby ujemne można wyświetlić na czerwono, klikając polecenie Komórki w menu Format. Na karcie Liczba kliknij kategorię Liczba lub Waluta, a następnie w polu Liczby ujemne wybierz kolor czerwony.
Raporty tabel i wykresów przestawnych
Nazwę pola tabeli przestawnej można zmienić, zaznaczając przycisk pola, a następnie wpisując inną nazwę.
Legendę w raporcie wykresu przestawnego można przenieść, klikając polecenie Opcje wykresu w menu Wykres, a następnie wybierając odpowiednią opcję położenia na karcie Legenda.
Raport tabeli przestawnej można dostosować, klikając polecenie Formatuj raport na pasku narzędzi Tabela przestawna.
Pola można przestawiać bezpośrednio w arkuszu bez konieczności uruchamiania Kreatora tabel przestawnych, przeciągając przyciski pól.
Ilość miejsca wymaganego przez raport tabeli przestawnej można zmniejszyć, przeciągając niektóre pola wierszy lub kolumn do obszaru upuszczania pól strony.
Szczegółowe dane dotyczące komórki można pokazać lub ukryć w obszarze danych raportu tabeli przestawnej, klikając dwukrotnie tę komórkę w tabeli.
Położenie elementu w obrębie pola tabeli przestawnej można zmienić, przeciągając jego obramowanie.
Wszelkie zmiany wprowadzone w danych arkusza źródłowego można szybko uwzględnić w raporcie tabeli przestawnej. Wystarczy kliknąć przycisk Odśwież dane na pasku narzędzi Tabela przestawna.
Funkcje i formuły
Aby uzyskać pomoc na temat wybierania funkcji i wprowadzania jej argumentów, kliknij przycisk fx (Wstaw funkcję) na pasku Formuła.
Zamiast przycisku Autosumowanie można również nacisnąć klawisze ALT+ZNAK RÓWNOŚCI (=).
Aby skopiować formułę z aktywnej komórki do wszystkich komórek w zaznaczonym zakresie, naciśnij klawisz F2, a następnie naciśnij klawisze CTRL+ENTER.
Do zakresu można wstawić sumy częściowe, klikając dowolną komórkę w zakresie, a następnie klikając polecenie Sumy częściowe w menu Dane.
Aby zmniejszyć wszystkie wartości w zakresie o 10 procent, wpisz ,9 w pustej komórce. Następnie skopiuj komórkę i zaznacz zakres. W menu Edycja wybierz polecenie Wklejanie specjalne, a następnie kliknij opcję Przemnóż.
Wyszukiwarka