Zakresy dat w funkcjach kryteriów

Rzeczywiste raporty niemal zawsze dotyczą okna czasowego: sprzedaży w tym kwartale, zamówień z zeszłego miesiąca czy rejestracji między dwiema datami. Poznane funkcje kryterialne — SUMIFS, COUNTIFS i AVERAGEIFS — doskonale sobie z tym radzą, gdy wiadomo, jak wyrazić przedział dat.

Klucz polega na tym, że przedział dat to w rzeczywistości dwa kryteria dotyczące tej samej kolumny dat: data musi przypadać na dzień początkowy lub późniejszy oraz na dzień końcowy lub wcześniejszy.

Arkusze kalkulacyjne przechowują daty jako liczby seryjne — dzień 1 to 1 stycznia 1900 (lub 1899 w Sheets), a każdy kolejny dzień zwiększa tę wartość o jeden. Dlatego daty można porównywać za pomocą > i < dokładnie tak samo jak zwykłe liczby.

Zatem po 1 stycznia oznacza po prostu liczbę seryjną większą od liczby odpowiadającej tej dacie. To najważniejsza informacja, dzięki której filtrowanie według dat działa.

Załóżmy, że kolumna A zawiera Datę zamówienia, a kolumna C — Kwotę. Aby zsumować sprzedaż ze stycznia 2024 roku, należy podać kolumnę A dwukrotnie: raz dla daty nie wcześniejszej niż 1 stycznia, a drugi raz dla daty nie późniejszej niż 31 stycznia.

Daty należy umieszczać w funkcji DATE(year, month, day), aby uniknąć niejednoznaczności związanej z ustawieniami regionalnymi. Te dwa kryteria tworzą warunek AND i obejmują wyłącznie wiersze z danego miesiąca.

Można spróbować wpisać bezpośrednio ">=1/1/2024". Często to działa, ale takie rozwiązanie jest podatne na błędy — arkusz może odczytać tę wartość jako tekst albo nieprawidłowo zinterpretować kolejność dnia i miesiąca.

Solidny wzorzec to ">="&DATE(2024,1,1). Funkcja DATE tworzy rzeczywistą liczbę seryjną, a operator & łączy z nią operator porównania. To rozwiązanie działa niezawodnie zarówno w Excelu, jak i w Arkuszach Google, niezależnie od ustawień regionalnych.

Daty wpisane na stałe sprawdzają się w przypadku ustalonego raportu, ale elastyczny raport powinien pobierać datę początkową i końcową z komórek. Datę początkową należy umieścić w F1, a końcową w F2.

Teraz arkusz steruje okresem. Zmiana wartości F1 lub F2 powoduje ponowne obliczenie każdej sumy. Jak zawsze, operator należy połączyć z komórką za pomocą & — nazwy komórki nigdy nie należy umieszczać w cudzysłowie.

Czasami potrzebne jest tylko jedno ograniczenie. Wszystko od określonej daty wymaga pojedynczego kryterium większe lub równe. Wszystko do określonej daty wymaga pojedynczego kryterium mniejsze lub równe.

Spowoduje to uwzględnienie wszystkich zamówień złożonych w dniu wskazanym w F1 lub później, bez górnego ograniczenia — jest to przydatne w metrykach typu „sprzedaż od momentu uruchomienia”.

Kryteria dotyczące dat można swobodnie łączyć z kryteriami tekstowymi i liczbowymi. Aby zsumować sprzedaż w regionie East w określonym przedziale dat, należy dodać parę dotyczącą regionu obok dwóch par dotyczących dat.

Kolejność nie wpływa na wynik — Excel ocenia wszystkie kryteria jako jeden duży warunek AND. W tym przypadku trzy pary kryteriów korzystają ze wspólnego average_range lub sum_range.

Aby zsumować cały rok, należy ustawić granice na pierwszy i ostatni dzień tego roku. Początek okresu można utworzyć za pomocą funkcji DATE, a następnie określić koniec okresu.

W przypadku pojedynczego miesiąca należy użyć pierwszego dnia miesiąca jako dolnej granicy, a pierwszego dnia następnego miesiąca z operatorem ścisłego "<" jako górnej granicy — to wygodny sposób na uniknięcie problemu z miesiącami mającymi 28, 30 lub 31 dni.

W przypadku raportów kroczących granice można wyznaczać za pomocą funkcji TODAY(). Aby policzyć zamówienia z ostatnich 30 dni, dolną granicą jest dzisiejsza data pomniejszona o 30, a górną — dzisiejsza data.

Ponieważ TODAY() odświeża się każdego dnia przy ponownym obliczeniu arkusza, przedział automatycznie przesuwa się do przodu — nie trzeba go ręcznie edytować.

Jeśli kolumna dat w rzeczywistości przechowuje datę i godzinę (na przykład znacznik czasu), wiersz datowany późno 31 stycznia ma liczbę seryjną nieco większą od wartości odpowiadającej całemu dniu 31 stycznia. Ograniczenie "<="&DATE(2024,1,31) wykluczyłoby taki wiersz.

Bezpiecznym rozwiązaniem jest wzorzec „następny dzień i ścisłe mniej niż”: "<"&DATE(2024,2,1) obejmuje każdą chwilę stycznia, w tym znaczniki czasu.

Ten sam wzorzec przedziału dat działa z AVERAGEIFS. Aby znaleźć średnią wartość zamówienia w określonym przedziale, należy podać kolumnę kwot jako average_range i dodać dwa kryteria dotyczące dat w kolumnie dat.

Należy pamiętać o pułapce pustego okresu: jeśli w danym przedziale nie ma zamówień, AVERAGEIFS zwraca #DIV/0!. Otoczenie formuły funkcją IFERROR pozwala zachować przejrzystość pulpitu nawigacyjnego filtrowanego według czasu, gdy okres nie zawiera danych.

Przypomnijmy sobie niezawodny sposób porównywania z datą w funkcjach kryterialnych.

Można teraz filtrować funkcje SUMIFS, COUNTIFS i AVERAGEIFS według czasu:

• Przedział dat to dwa kryteria dotyczące tej samej kolumny dat (>= początek i <= koniec).
• Daty należy tworzyć za pomocą DATE(y,m,d), a operatory dołączać za pomocą ">="&.
• W przypadku miesięcy należy używać górnej granicy w postaci następnego dnia i ścisłego mniej niż ("<"&DATE(...)), aby poprawnie obsługiwać znaczniki czasu.
• Funkcja TODAY() umożliwia tworzenie kroczących przedziałów, takich jak ostatnie 30 dni.

Na tym kończy się omówienie rodziny funkcji IFS obsługujących wiele kryteriów.