Konfigurowanie zakresu kryteriów

Excel udostępnia rodzinę funkcji bazodanowych, których nazwy zaczynają się od litery D: DSUM, DCOUNT, DAVERAGE, DGET i inne.

Działają one na tabeli ułożonej jak mała baza danych: u góry znajduje się wiersz nagłówków kolumn, a poniżej rekordy. Zamiast wpisywać warunki bezpośrednio w formule, wskazuje się osobny zakres kryteriów w arkuszu, który określa, co ma zostać dopasowane.

Ta lekcja jest poświęcona prawidłowemu tworzeniu tego zakresu kryteriów, ponieważ każda funkcja D jest od niego zależna.

Każda funkcja D ma te same trzy argumenty:

• database — cała tabela wraz z wierszem nagłówków.
• field — kolumna, na której ma działać funkcja (nazwa nagłówka w cudzysłowie albo numer kolumny).
• criteria — zakres zawierający reguły dopasowania.

Postać jest więc zawsze taka: DSUM(database, field, criteria). Argument criteria sprawia początkującym najwięcej problemów, dlatego zajmiemy się nim najpierw.

Zakres kryteriów to po prostu niewielki blok komórek zawierający co najmniej dwa wiersze:

• Górny wiersz zawiera nagłówki kolumn, które muszą dokładnie odpowiadać nagłówkom bazy danych.
• Wiersze poniżej zawierają warunki.

Załóżmy, że dane sprzedażowe mają nagłówki Region, Rep, Amount. Aby dopasować tylko region East, zakres kryteriów powinien składać się z dwóch ustawionych pionowo komórek: u góry Region, a poniżej East.

Nagłówek w zakresie kryteriów musi mieć identyczną pisownię jak nagłówek bazy danych. Jeśli kolumna danych nazywa się Amount, ale w kryteriach wpisano Amounts lub amt, funkcja D nie znajdzie kolumny i może zwrócić błąd albo zero.

Najbezpieczniej jest skopiować komórkę nagłówka z tabeli i wkleić ją do zakresu kryteriów. Gwarantuje to dokładną zgodność tekstu, włącznie z ewentualnymi końcowymi spacjami.

Załóżmy, że zakres A1:C13 zawiera tabelę z nagłówkami Region, Rep, Amount. W komórce E1 wpisz Region, a w komórce E2 wpisz East. Ten dwukomórkowy blok, E1:E2, jest zakresem kryteriów.

Teraz DSUM zsumuje tylko wiersze dotyczące regionu East. Funkcja odczyta nagłówek w E1, stwierdzi, że odpowiada on kolumnie Region, a następnie uwzględni tylko wiersze, w których Region ma wartość East.

Domyślnie warunek tekstowy taki jak East dopasowuje wartości, które zaczynają się od tego tekstu. Dlatego East dopasuje również Eastern.

Aby wymusić dokładne dopasowanie, otocz wartość porównaniem w stylu formuły: wpisz ="=East" w komórce kryteriów. Możesz także używać symboli wieloznacznych: E* dopasowuje wszystko, co zaczyna się od E, a ?at dopasowuje Cat, Bat lub Hat.

Kryteria nie ograniczają się do tekstu. W przypadku liczb można używać operatorów porównania:

• >1000 dopasowuje kwoty większe niż 1000.
• <=50 dopasowuje wartości nie większe niż 50.
• <>0 dopasowuje wszystko, co nie jest równe zero.

Umieść nagłówek, na przykład Amount, u góry, a tekst porównania pod nim. Funkcja D porównuje wartość każdego rekordu z tą regułą.

Warunki umieszczone obok siebie w tym samym wierszu są łączone za pomocą AND — wszystkie muszą być spełnione.

Aby dopasować region East z kwotą większą niż 1000, utwórz dwukolumnowy zakres kryteriów: w górnym wierszu umieść nagłówki Region i Amount, a w wierszu poniżej wartości East i >1000. Rekord zostanie uwzględniony tylko wtedy, gdy dotyczy regionu East i ma wartość większą niż 1000.

Warunki umieszczone w osobnych wierszach są łączone za pomocą OR — wystarczy dopasowanie do dowolnego wiersza.

Aby dopasować region East lub West, umieść nagłówek Region u góry, następnie East w kolejnym wierszu, a West w jeszcze następnym. Zakres kryteriów obejmuje teraz trzy wiersze, a rekord zostanie uwzględniony, jeśli pasuje do którejkolwiek wartości.

Pamiętaj, aby uwzględnić wszystkie te wiersze w argumencie criteria.

Można łączyć oba układy. Załóżmy, że chcesz uzyskać wynik (East AND >1000) OR (West AND >500).

Użyj dwóch kolumn: Region i Amount. W jednym wierszu umieść East oraz >1000, a w następnym West oraz >500. Każdy wiersz jest grupą AND, a osobne wiersze działają jak OR. Taki układ siatki pozwala funkcjom D wyrażać złożoną logikę bez zagnieżdżania funkcji.

Uważaj na następujące pułapki:

• Pomijanie wiersza nagłówków — zakres kryteriów potrzebuje nagłówków, a nie tylko warunków.
• Wybieranie pustego wiersza w zakresie kryteriów — pusty wiersz warunków dopasowuje każdy rekord, zwracając wszystkie dane.
• Literówki w nagłówkach, przez które przestają one odpowiadać nagłówkom tabeli.
• Umieszczanie bloku kryteriów bezpośrednio przy tabeli danych, tak że zakresy się nakładają.

Trzymaj zakres kryteriów w osobnym, przejrzystym obszarze arkusza.

Zakres kryteriów jest najważniejszym elementem każdej funkcji D. Najważniejsze informacje:

• Potrzebuje wiersza nagłówków, którego nazwy muszą dokładnie odpowiadać nazwom w tabeli.
• Warunki umieszcza się w wierszach poniżej — mogą to być tekst, symbole wieloznaczne lub porównania, takie jak >1000.
• Ten sam wiersz = AND, osobne wiersze = OR.
• Należy unikać pustych wierszy warunków, ponieważ dopasowują one wszystko.

Po przygotowaniu prawidłowego zakresu kryteriów można przekazywać go do funkcji DSUM, DCOUNT, DAVERAGE i DGET w kolejnych lekcjach.