Porównanie okresów w Excelu: MTD, QTD i YTD w praktyce

Przez
Tadeusz Michalski
-
0
11
Rate this post

Nawigacja:

Krótka scenka z życia analityka: kiedy MTD, QTD i YTD przestają być teorią

Telefon od dyrektora sprzedaży: „Wyślij mi proszę aktualne wyniki: jak nam idzie w tym miesiącu, w kwartale i od początku roku. I porównanie z poprzednim rokiem na dzisiaj”. Arkusz z danymi ma kilkadziesiąt tysięcy wierszy, transakcje z kilku lat, kilkanaście regionów.

Po pół godzinie ręcznego filtrowania i kopiowania formuł liczby w bieżącym raporcie różnią się od tego, co było w prezentacji sprzed tygodnia. Raz sumujesz cały miesiąc, innym razem tylko do wczoraj, a YTD liczysz „od stycznia”, ale nie zawsze do tej samej daty. Zaczyna się dyskusja, czy Excel „źle liczy”, zamiast rozmowy o wyniku.

Stały, świadomy sposób liczenia MTD (Month-To-Date), QTD (Quarter-To-Date) i YTD (Year-To-Date) porządkuje ten chaos. Gdy okresy czasu są zdefiniowane w modelu danych, a nie w głowie, raporty zaczynają być spójne, przewidywalne i zautomatyzowane. Excel przestaje być loterią, a staje się narzędziem, któremu można ufać.

Podstawy MTD, QTD, YTD – co tak naprawdę porównujesz

Definicje MTD, QTD, YTD na prostych przykładach

Wspólną cechą MTD, QTD i YTD jest to, że wszystkie oznaczają sumę „od początku okresu do konkretnej daty”. Różni je tylko długość tego okresu.

  • MTD (Month-To-Date) – suma od pierwszego dnia bieżącego miesiąca do wybranej daty raportowej. Przykład: przy dacie raportowej 10.04.2024 MTD obejmuje okres 01.04.2024–10.04.2024.
  • QTD (Quarter-To-Date) – suma od pierwszego dnia bieżącego kwartału do daty raportowej. Dla daty 10.04.2024 i standardowego roku (Q2 = kwiecień–czerwiec) QTD obejmie 01.04.2024–10.04.2024.
  • YTD (Year-To-Date) – suma od pierwszego dnia bieżącego roku do daty raportowej. Dla 10.04.2024 YTD to zakres 01.01.2024–10.04.2024.

Kluczowy jest tu punkt końcowy – tzw. data raportowa. Czasem jest to zawsze „dzisiaj” (funkcja TODAY()), a czasem konkretny dzień, np. ostatni dzień poprzedniego miesiąca, kiedy zamykasz raport miesięczny.

„To-date” kontra pełne okresy kalendarzowe

Częstym źródłem nieporozumień jest mieszanie dwóch zupełnie różnych rzeczy:

  • okresów typu to-date (do bieżącej daty),
  • pełnych miesięcy, kwartałów czy lat.

Dla daty 10.04.2024 raport sprzedaży może więc zawierać jednocześnie:

  • MTD – 01.04–10.04 (niepełny miesiąc),
  • poprzedni pełny miesiąc – np. 01.03–31.03,
  • YTD – 01.01–10.04 (niepełny rok),
  • poprzedni pełny rok – 01.01.2023–31.12.2023.

Jeżeli w jednym dashboardzie obok siebie umieszczasz wartość YTD (niepełny rok) i pełny rok poprzedni, porównanie jest asymetryczne. Czasem jest to świadomy wybór (chcesz wiedzieć, jak idzie realizacja celu w danym momencie roku), ale bywa też przypadkiem – wtedy wnioski potrafią być całkowicie fałszywe.

Gdzie używać MTD, QTD, YTD w realnych raportach

Standardowe okresy analizy czasu sprawdzają się szczególnie dobrze tam, gdzie ważne jest tempo realizacji celów, monitorowanie trendu oraz szybkie porównania rok do roku.

  • MTD – bieżąca kontrola:
    • codzienny monitoring sprzedaży w miesiącu,
    • analiza kosztów operacyjnych w trakcie miesiąca,
    • kontrola produkcji vs plan miesięczny.
  • QTD – spojrzenie „strategiczne”:
    • realizacja celów sprzedażowych na poziomie kwartału,
    • raportowanie kwartalne do zarządu lub właścicieli,
    • budżety kwartalne i ich realizacja.
  • YTD – perspektywa „roczna w trakcie roku”:
    • postęp w realizacji budżetu rocznego,
    • analiza kosztów od początku roku,
    • porównanie YTD do YTD z poprzedniego roku (np. 1.01–10.04 vs 1.01–10.04).

Pytania biznesowe a wybór okresu

Dobór okresu nie jest kwestią „co ładniej wygląda”, tylko tego, na jakie pytanie próbujesz odpowiedzieć. Kilka typowych przykładów:

  • „Czy ten miesiąc będzie lepszy niż poprzedni?” – porównujesz bieżące MTD do MTD poprzedniego miesiąca lub do pełnego poprzedniego miesiąca (zależnie od etapu miesiąca).
  • „Czy dowieziemy plan kwartalny?” – porównujesz QTD do planu QTD lub do QTD roku poprzedniego.
  • „Gdzie jesteśmy z realizacją budżetu rocznego?” – patrzysz na YTD vs plan na ten sam dzień roku lub vs YTD poprzedniego roku.

Gdy pytanie jest jasno postawione, łatwiej uniknąć nieporozumień, czy używać MTD, QTD, czy YTD – i czy porównywać pełne okresy, czy tylko to, co wydarzyło się do określonej daty.

Krótki wniosek po definicjach

Wyraźne rozdzielenie MTD, QTD i YTD oraz świadomość różnicy między wartościami „to-date” a pełnymi okresami ogranicza liczbę dyskusji o tym, „czy liczby się zgadzają”. Najpierw definicja okresu, dopiero później formuła w Excelu.

Dłoń z długopisem analizuje kolorowe wykresy słupkowe i liniowe na kartce
Źródło: Pexels | Autor: Lukas Blazek

Przygotowanie danych źródłowych pod analizę okresów w Excelu

Jedna kolumna z datą – fundament każdego raportu czasowego

Bez poprawnej, spójnej kolumny z datą nie ma sensownego porównania MTD, QTD czy YTD. Dane źródłowe często przychodzą w różnych formach:

  • osobne kolumny „Rok”, „Miesiąc”, „Dzień”,
  • opisy tekstowe typu „styczeń 2024”,
  • daty jako teksty w różnych formatach (np. „01/03/2024”, „2024-03-01”).

Najlepszą praktyką jest stworzenie jednej kolumny typu data, np. DataTransakcji, która zawiera rzeczywiste wartości dat, rozpoznawane przez Excel (liczby seryjne). Z osobnych kolumn rok/miesiąc można to połączyć formułą DATE(rok;miesiąc;dzień) lub, gdy mamy tylko rok i miesiąc, przyjmując domyślnie pierwszy dzień miesiąca.

Teksty typu „styczeń 2024” należy przekształcić formułami (np. z wykorzystaniem DATEVALUE() po konwersji tekstu do formatu „1-styczeń-2024”) lub Power Query, a następnie przechowywać już jako pełne daty.

Czystość i format dat – uniknięcie cichych błędów

Niewidoczne na pierwszy rzut oka błędy dat są jedną z głównych przyczyn błędnych raportów czasowych. Kilka elementów, które trzeba uporządkować:

  • Spójny format daty – Excel może wyświetlać datę inaczej (np. 2024-03-01 vs 01.03.2024), ale kluczowe jest, aby wszystkie komórki w kolumnie były typem daty, a nie tekstem.
  • Puste wartości – w kolumnie daty puste komórki mogą zaburzyć filtrowanie lub obliczenia. Lepiej albo je wypełnić poprawną datą, albo wykluczyć z analizy (np. formułą z warunkiem „data <> 0”).
  • Formaty regionalne – import z różnych systemów (dd/mm/rrrr vs mm/dd/rrrr) potrafi odwrócić dzień i miesiąc. Dla dat z dniami do 12 Excel może „leganie” odczytać złą kolejność. W takich przypadkach często nie ma wyjścia poza świadomą konwersją, np. przez Power Query, lub dodanie kolumny pomocniczej z rozbiciem tekstu i złożeniem go funkcją DATE().

Dobrze przygotowana kolumna z datą znacznie ułatwia budowę formuł MTD, QTD i YTD – zamiast walki z formatami skupiasz się na logice.

Dlaczego tabele Excela (Ctrl+T) ułatwiają pracę z okresami

Przekształcenie zakresu danych w tabelę Excela (skrót Ctrl+T) daje kilka korzyści przy analizie okresów:

  • automatyczne rozszerzanie zakresów formuł – nowe wiersze z datami są od razu włączone w obliczenia MTD/QTD/YTD, bez ręcznej aktualizacji zakresów,
  • odwołania strukturalne – formuły są czytelniejsze, bo odnoszą się do nazw kolumn, np. Sprzedaż[Data], a nie do surowych adresów A2:A10000,
  • łatwiejsza integracja z tabelami przestawnymi i segmentami dat,
  • spójne formatowanie i filtrowanie na poziomie całego zestawu danych.

Jeśli raport ma rosnąć wraz z liczbą transakcji, tabela Excela zamiast luźnego zakresu to w zasadzie konieczność.

Kolumny pomocnicze: rok, miesiąc, kwartał, dzień roku

Wielu analityków próbuje upchać całą logikę w jedną złożoną formułę. Zamiast tego dużo prościej jest dodać kilka kolumn pomocniczych opartych na funkcjach daty:

  • Rok – =YEAR([@DataTransakcji])
  • MiesiącNumer – =MONTH([@DataTransakcji])
  • Kwartał – najprościej =INT((MONTH([@DataTransakcji])-1)/3)+1
  • DzieńRoku – np. =[@DataTransakcji]-DATE(YEAR([@DataTransakcji]);1;1)+1

Takie kolumny ułatwiają potem:

  • budowanie warunków SUMIFS (np. filtr na konkretny rok i miesiąc),
  • filtrowanie i grupowanie w tabelach przestawnych,
  • tworzenie raportów porównujących ten sam dzień roku w różnych latach.

Gdy część logiki przeniesiesz do kolumn pomocniczych, formuły liczące MTD, QTD i YTD stają się krótsze, czytelniejsze i mniej podatne na pomyłki.

Mini-wniosek o przygotowaniu danych

Jeden spójny format daty, dane w tabeli Excela oraz kilka prostych kolumn pomocniczych potrafią zmniejszyć złożoność formuł o połowę. Zamiast jednej skomplikowanej funkcji z wieloma zagnieżdżeniami pojawia się kilka logicznych kroków, które łatwo prześledzić i skontrolować.

Excel i daty – fundamenty, bez których MTD/QTD/YTD się rozsypie

Jak Excel przechowuje daty i dlaczego to ma znaczenie

Excel przechowuje daty jako liczby seryjne. Dzień 1 odpowiada wybranej dacie początkowej (w systemie Windows zwykle 1.01.1900), a kolejne dni to kolejne liczby: 2, 3, 4… Dzięki temu:

  • odejmowanie dat daje liczbę dni pomiędzy nimi,
  • dodanie liczby do daty przesuwa datę o tyle dni,
  • porównywanie dat (>, <, >=, <=) jest zwykłym porównaniem liczb.

Jeżeli data jest przechowywana jako tekst (na przykład „2024-03-01” w formacie tekstowym), Excel nie potraktuje jej poprawnie w funkcjach typu SUMIFS, które operują na kryteriach datowych. Część funkcji będzie wtedy działać, część nie – stąd biorą się „magiczne” błędy, których trudno się doszukać.

Najważniejsze funkcje daty i ich praktyczne użycie

Przy analizie MTD, QTD i YTD pojawia się kilka funkcji, z których korzysta się bez przerwy:

  • TODAY() – zwraca dzisiejszą datę. Idealne źródło „daty raportowej” dla dynamicznych raportów, które mają się same odświeżać.
  • EOMONTH(data; przesunięcie) – zwraca ostatni dzień miesiąca:
    • EOMONTH(TODAY();0) – ostatni dzień bieżącego miesiąca,
    • EOMONTH(TODAY();-1) – ostatni dzień poprzedniego miesiąca.

    • Daty początku i końca okresu – jak złapać właściwe granice

    W pewien poniedziałek rano manager zapytał: „Ale od kiedy dokładnie liczysz ten QTD?”. Okazało się, że jeden raport startował od 1.01, a drugi od początku kalendarzowego kwartału. Liczby się różniły, a nikt nie był winny – po prostu każdy miał inną „datę startu”.

    Żeby uniknąć takiej sytuacji, najpierw trzeba ustalić i technicznie wyznaczyć początek i koniec okresu. Kilka praktycznych formuł bazowych:

  • Pierwszy dzień bieżącego miesiąca:
    =DATE(YEAR(TODAY());MONTH(TODAY());1)
  • Ostatni dzień bieżącego miesiąca:
    =EOMONTH(TODAY();0)
  • Pierwszy dzień bieżącego roku:
    =DATE(YEAR(TODAY());1;1)
  • Ostatni dzień bieżącego roku:
    =DATE(YEAR(TODAY());12;31)

Dla kwartałów dochodzi tylko krok z wyznaczeniem numeru kwartału, a potem przełożeniem go na miesiąc startowy i końcowy. To można „zalaminować” w prostą formułę lub przenieść do kolumny pomocniczej.

Dynamiczne okresy vs. „twarde” daty

W raportach dziennych i tygodniowych wygodniejsze są okresy dynamiczne oparte na TODAY(). Raport otwierasz jutro – daty od razu „przeskakują”. Przy budżetach rocznych lub analizach historycznych przydają się natomiast daty stałe, wpisane na sztywno lub trzymane w osobnej tabeli parametrów.

Dobry kompromis w codziennej pracy to komórka z datą raportową, np. B1 = DataRaportu, którą można czasem nadpisać ręcznie (np. dla symulacji „co byłoby na koniec miesiąca”). Formuły odwołują się wtedy do tej jednej komórki:

  • =DATE(YEAR($B$1);MONTH($B$1);1) – początek miesiąca raportowego,
  • =EOMONTH($B$1;0) – koniec miesiąca raportowego,
  • =DATE(YEAR($B$1);1;1) – początek roku raportowego.

Taki prosty parametr dramatycznie ułatwia testowanie formuł MTD/QTD/YTD i odtwarzanie starych wersji raportu.

Granice „do dnia” – jak porównywać niepełne okresy

Najczęstszy zgrzyt przy YTD: ktoś porównuje pełny poprzedni rok do bieżącego YTD. Efekt? „W tym roku mamy dramatyczny spadek”, choć tak naprawdę jest dopiero maj. Rozwiązaniem jest praca na wartościach do tego samego dnia roku.

Przykład prostego podejścia w formułach:

  • Dzień roku dla daty raportowej (może być w osobnej komórce):
    = $B$1 - DATE(YEAR($B$1);1;1) + 1
  • Data „porównywalna” rok wcześniej (ten sam dzień roku, rok wstecz):
    =DATE(YEAR($B$1)-1;1;1) + (<DzienRoku> - 1)

Jeśli w danych transakcyjnych masz kolumnę DzieńRoku, filtrujesz wtedy:

  • YTD bieżący: rok = rok z B1 oraz DzieńRoku ≤ DzieńRoku z B1,
  • YTD poprzedni: rok = rok z B1 – 1 oraz DzieńRoku ≤ DzieńRoku z B1.

Błąd „porównywania pełnego roku do uciętego YTD” znika, a dyskusja wraca na właściwe tory – do faktycznej różnicy sprzedaży czy kosztów.

MTD w praktyce – podstawowa formuła na jednej tabeli

Typowy scenariusz: masz tabelę Sprzedaż z kolumnami DataTransakcji i Kwota. Manager pyta: „Ile mamy sprzedaży MTD?”. W najprostszym ujęciu potrzebujesz sumy dla bieżącego miesiąca, od jego pierwszego dnia do daty raportowej.

Przy założeniu, że w komórce $B$1 jest data raportowa, a dane są w tabeli Sprzedaż:

  • Początek miesiąca:
    =DATE(YEAR($B$1);MONTH($B$1);1)
  • Proste MTD (sumowanie po dacie transakcji):
    =SUMIFS(Sprzedaż[Kwota]; Sprzedaż[DataTransakcji]; ">=" & DATE(YEAR($B$1);MONTH($B$1);1); Sprzedaż[DataTransakcji]; "<=" & $B$1)

Formuła opiera się na dwóch granicach: od pierwszego dnia miesiąca do daty raportowej włącznie. Przestawiając datę w B1, natychmiast widzisz MTD na dowolny dzień.

MTD dla poprzedniego miesiąca – uczciwe porównanie „na ten sam dzień”

Gdy raport tworzony jest w połowie miesiąca, naturalne jest pytanie: „A jak było w tym samym momencie poprzedniego miesiąca?”. Chodzi o porównanie MTD vs MTD z poprzedniego miesiąca, nie do pełnego miesiąca.

Jedna z prostszych metod to użycie funkcji EDATE lub EOMONTH i przesunięcie dat:

  • Data porównawcza miesiąc wcześniej (ta sama liczba dnia, poprzedni miesiąc):
    =EDATE($B$1;-1)
  • Początek poprzedniego miesiąca:
    =DATE(YEAR(EDATE($B$1;-1));MONTH(EDATE($B$1;-1));1)
  • MTD poprzedniego miesiąca (do „tego samego dnia”):
    =SUMIFS(Sprzedaż[Kwota]; Sprzedaż[DataTransakcji]; ">=" & DATE(YEAR(EDATE($B$1;-1));MONTH(EDATE($B$1;-1));1); Sprzedaż[DataTransakcji]; "<=" & EDATE($B$1;-1))

Dzięki temu porównujesz na przykład 1–10 marca do 1–10 lutego, a nie 1–10 marca do całego lutego. Różnice procentowe są wtedy znacznie bardziej miarodajne.

MTD w tabeli przestawnej – filtr daty zamiast formuł

Gdy dane są już w tabeli przestawnej, nie zawsze trzeba pisać złożone formuły. W wielu przypadkach wystarczy odpowiednio ustawić filtr daty. Załóżmy, że masz tabelę przestawną opartą na kolumnie DataTransakcji i polu sumy Kwota.

Możliwe podejście krok po kroku:

  1. Przeciągnij DataTransakcji do obszaru filtrów lub wierszy.
  2. Na strzałce przy DataTransakcji wybierz filtr daty typu „Ten miesiąc” lub „Od początku miesiąca do…” (w zależności od wersji Excela).
  3. Jeżeli filtr predefiniowany nie daje opcji „do konkretnej daty”, zastosuj filtr „Pomiędzy” i wprowadź:
    • początek miesiąca (np. 2024-03-01),
    • datę raportową (np. 2024-03-10).

Tak skonfigurowaną tabelę przestawną można później łączyć z wykresami i segmentami (slicerami), aby dynamicznie przełączać się między MTD, poprzednim miesiącem czy całym rokiem.

Rozszerzony MTD – filtr po produktach, regionach i kanałach

W prawdziwych raportach MTD to nie tylko jedna liczba. Potrzebna jest sprzedaż MTD po produktach, regionach, kanałach czy handlowcach. Na poziomie formuł oznacza to dodanie kolejnych kryteriów do SUMIFS.

Dla przykładu, jeśli tabela Sprzedaż ma dodatkowe kolumny Region i Produkt, a w komórkach D2 i E2 trzymasz wybrane wartości filtrów:

=SUMIFS(
    Sprzedaż[Kwota];
    Sprzedaż[DataTransakcji]; ">=" & DATE(YEAR($B$1);MONTH($B$1);1);
    Sprzedaż[DataTransakcji]; "<=" & $B$1;
    Sprzedaż[Region]; $D$2;
    Sprzedaż[Produkt]; $E$2
)

Zmiana Regionu lub Produktu natychmiast przelicza całą sekcję raportu, wciąż w ramach tego samego okresu MTD. Jedna dobrze zdefiniowana funkcja czasu staje się wtedy „silnikiem”, który napędza kilkanaście różnych widoków.

QTD – od prostego kwartału kalendarzowego do niestandardowych okresów

Przy kwartale sytuacja wygląda podobnie jak przy MTD, tylko zakres czasu jest szerszy. W najprostszej wersji pracujesz na kwartałach kalendarzowych, czyli:

  • I kwartał: miesiące 1–3,
  • II kwartał: miesiące 4–6,
  • III kwartał: miesiące 7–9,
  • IV kwartał: miesiące 10–12.

Aby policzyć QTD, trzeba więc ustalić początek tego kwartału i zsumować dane od tej daty do daty raportowej.

Wyznaczenie początku kwartału na bazie daty raportowej

Przy założeniu, że w B1 mamy datę raportową, najpierw obliczasz numer kwartału, a potem tworzysz datę jego pierwszego dnia. Można to zrobić bezpośrednio w formule:

  • Numer kwartału (opcjonalnie w komórce pomocniczej):
    =INT((MONTH($B$1)-1)/3)+1
  • Pierwszy dzień kwartału (wersja „w jednej linijce”):
    =DATE(YEAR($B$1); (INT((MONTH($B$1)-1)/3)*3)+1; 1)

Formuła na początek kwartału wyciąga rok z daty raportowej, wyznacza, który to blok trzech miesięcy, a potem przelicza go na miesiąc startowy (1, 4, 7 albo 10).

QTD w SUMIFS – prosty wariant z tabelą

Mając początek kwartału, QTD liczy się dokładnie tak, jak MTD, tylko na innym zakresie dat. Dla tabeli Sprzedaż i daty raportowej w B1:

=SUMIFS(
    Sprzedaż[Kwota];
    Sprzedaż[DataTransakcji]; ">=" & DATE(YEAR($B$1); (INT((MONTH($B$1)-1)/3)*3)+1; 1);
    Sprzedaż[DataTransakcji]; "<=" & $B$1
)

Jeżeli wolisz czytelność, rozbij formułę na dwa kroki – w osobnej komórce policz początek kwartału, nadaj jej nazwę (np. PoczatekKwartału), a w SUMIFS użyj już tej nazwy. Z punktu widzenia utrzymania pliku to dużo bardziej praktyczne rozwiązanie.

Kwartały niestandardowe – kalendarz finansowy a Excel

W wielu firmach kwartały nie pokrywają się z kalendarzem. Rok finansowy może zaczynać się w kwietniu, lipcu albo październiku. Tekst „Q2 2024” może więc oznaczać zupełnie inne miesiące niż w standardowym kalendarzu. Jeśli Excel nie „nauczy się” tego kalendarza, QTD będzie mylące.

Najbardziej przejrzystym podejściem jest dodanie do danych tabeli kalendarza finansowego. Zawiera ona co najmniej:

  • pełną datę,
  • rok finansowy,
  • kwartał finansowy (np. FQ1, FQ2),
  • miesiąc finansowy (czasem inny niż kalendarzowy).

Taka tabela kalendarza może powstać w osobnym arkuszu. Łączysz ją z danymi transakcyjnymi po dacie (w Power Query lub w modelu danych), a następnie pracujesz w raportach na polach „RokFinansowy” i „KwartalFinansowy” zamiast na surowej dacie. Wtedy QTD to po prostu suma dla danego roku finansowego i kwartału, czy to w SUMIFS, czy w miarach Power Pivot.

QTD w tabeli przestawnej z grupowaniem po kwartałach

Jeśli zostajesz przy kwartale kalendarzowym i nie korzystasz z niestandardowego kalendarza, wiele da się załatwić samym grupowaniem dat w tabeli przestawnej.

Prosty schemat pracy:

  1. Wstaw tabelę przestawną na podstawie tabeli z danymi.
  2. Umieść DataTransakcji w obszarze wierszy.
  3. Kliknij prawym przyciskiem na jedną z dat i wybierz Grupuj….
  4. Zaznacz „Miesiące” i „Kwartały” (oraz „Lata”, jeśli raport ma kilka lat).

QTD dzień-do-dnia – porównanie z poprzednim kwartałem „do tej samej daty”

W poniedziałek rano dyrektor sprzedaży mówi: „QTD wygląda dobrze, ale pokaż mi, jak stoimy względem poprzedniego kwartału na ten sam dzień”. Na jednym wykresie chcesz zobaczyć, czy bieżący kwartał „wyprzedza” czy „goni” poprzedni, bez czekania na jego zakończenie.

Technicznie potrzebujesz więc dwóch zakresów:

  • QTD bieżącego kwartału – od początku kwartału do daty raportowej,
  • QTD poprzedniego kwartału – od początku poprzedniego kwartału do „tej samej liczby dnia kwartału”.

Pierwszy zakres jest już zdefiniowany – początek aktualnego kwartału masz z wcześniejszej formuły. Drugi wymaga lekkiej sztuczki z przesunięciem o trzy miesiące.

Przyjmijmy ponownie datę raportową w $B$1. Pomocniczo wyznacz:

  • Data przesunięta o jeden kwartał wstecz (ta sama data kalendarzowa, ale trzy miesiące wcześniej):
    =EDATE($B$1;-3)
  • Początek poprzedniego kwartału:
    =DATE(YEAR(EDATE($B$1;-3)); (INT((MONTH(EDATE($B$1;-3))-1)/3)*3)+1; 1)

Teraz możesz policzyć QTD poprzedniego kwartału „do tej samej daty kwartału”:

=SUMIFS(
    Sprzedaż[Kwota];
    Sprzedaż[DataTransakcji]; ">=" & DATE(
        YEAR(EDATE($B$1;-3));
        (INT((MONTH(EDATE($B$1;-3))-1)/3)*3)+1;
        1
    );
    Sprzedaż[DataTransakcji]; "<=" & EDATE($B$1;-3)
)

W ten sposób, analizując 15. dzień bieżącego kwartału, porównujesz go do 15. dnia poprzedniego. Linia trendu w dashboardzie pokazuje wtedy realne przyspieszenie lub spowolnienie, a nie jedynie różnice między pełnymi kwartałami.

QTD w podziale na segmenty – ten sam „silnik czasu”, wiele widoków

Na spotkaniu zarząd pyta: „OK, QTD dla firmy wygląda dobrze, ale które segmenty tak naprawdę ciągną wynik w górę?”. Liczba zbiorcza przestaje wystarczać – trzeba rozbić QTD na kraje, dywizje i kluczowych klientów.

Nic nie stoi na przeszkodzie, żeby formułę QTD rozbudować podobnie jak MTD. Dla tabeli Sprzedaż z kolumnami Region, Segment, Klient i parametrami w komórkach:

  • $D$2 – wybrany Region,
  • $E$2 – wybrany Segment,
  • $F$2 – wybrany Klient (opcjonalnie).

Formuła przyjmuje postać:

=SUMIFS(
    Sprzedaż[Kwota];
    Sprzedaż[DataTransakcji]; ">=" & DATE(YEAR($B$1); (INT((MONTH($B$1)-1)/3)*3)+1; 1);
    Sprzedaż[DataTransakcji]; "<=" & $B$1;
    Sprzedaż[Region]; $D$2;
    Sprzedaż[Segment]; $E$2;
    Sprzedaż[Klient]; $F$2
)

Podpinając do takich parametrów listy rozwijane, w kilka sekund zmieniasz perspektywę: ten sam algorytm QTD zasila całą galerię widoków – od poziomu całej organizacji po pojedyncze konto klienckie.

Dynamiczne przełączanie MTD/QTD/YTD – jedno źródło, wiele okresów

Na dashboardzie CFO chce przycisk „MTD / QTD / YTD”. Nie interesują go trzy oddzielne raporty – ma być jedno miejsce, gdzie zmienia się tylko perspektywa czasu. Z punktu widzenia Excela sprowadza się to do jednego przełącznika i trzech różnych par dat granicznych.

Najprostsza konstrukcja to tabela pomocnicza z typami okresów i wybór użytkownika w pojedynczej komórce.

  1. W osobnym miejscu utwórz małą tabelę (np. w zakresie H2:I4):
    OkresKod
    MTD1
    QTD2
    YTD3
  2. W komórce $C$1 wstaw listę rozwijaną (Dane → Sprawdzanie poprawności → Lista) z wartościami MTD,QTD,YTD.
  3. W osobnych komórkach wylicz DatęOdDatęDo na podstawie wyboru.

Załóżmy, że:

  • $B$1 – data raportowa,
  • $C$1 – wybrany okres (tekst: „MTD”, „QTD”, „YTD”).

W komórce $B$2 (DataOd) można zbudować formułę na bazie IFS (Office 365) albo zagnieżdżonych IF:

=IFS(
    $C$1="MTD"; DATE(YEAR($B$1);MONTH($B$1);1);
    $C$1="QTD"; DATE(YEAR($B$1); (INT((MONTH($B$1)-1)/3)*3)+1; 1);
    $C$1="YTD"; DATE(YEAR($B$1);1;1)
)

Jeśli Excel nie obsługuje IFS, użyj klasycznego łączenia IF:

=IF(
    $C$1="MTD";
    DATE(YEAR($B$1);MONTH($B$1);1);
    IF(
        $C$1="QTD";
        DATE(YEAR($B$1);(INT((MONTH($B$1)-1)/3)*3)+1;1);
        DATE(YEAR($B$1);1;1)
    )
)

W komórce $B$3 (DataDo) ustaw po prostu:

=$B$1

Główna formuła sumująca sprzedaż według dynamicznego okresu sprowadza się wtedy do:

=SUMIFS(
    Sprzedaż[Kwota];
    Sprzedaż[DataTransakcji]; ">=" & $B$2;
    Sprzedaż[DataTransakcji]; "<=" & $B$3
)

Dzięki temu jednym przełącznikiem sterujesz całym raportem. Każdy wykres, który opiera się na tych samych granicach dat, reaguje automatycznie na zmianę MTD/QTD/YTD.

Parametry DatyOd/DatyDo jako nazwy – czytelniejszy model raportu

W większym pliku, gdzie te same granice czasu są używane w kilkunastu formułach, odwoływanie się do $B$2 i $B$3 szybko staje się uciążliwe. Łatwiej zarządzać dwiema nazwami niż śledzić dziesiątki odwołań do komórek.

Praktyczny zabieg:

  1. Zaznacz komórkę z datą początkową (np. $B$2) i w polu nazwy (po lewej od paska formuły) wpisz DataOdOkres, zatwierdź Enterem.
  2. Analogicznie dla końca zakresu ($B$3) wpisz DataDoOkres.
  3. W każdej formule SUMIFS, AVERAGEIFS czy COUNTIFS używaj już nazw, np.:
    =SUMIFS(
    Sprzedaż[Kwota];
    Sprzedaż[DataTransakcji]; ">=" & DataOdOkres;
    Sprzedaż[DataTransakcji]; "<=" & DataDoOkres
)

Gdy za rok ktoś odziedziczy plik, szybciej zrozumie, co oznacza „DataOdOkres” niż „$B$2”. Ten prosty krok redukuje ryzyko przypadkowego rozjechania się zakresów dat między sekcjami raportu.

YTD – punkt widzenia CFO i operacyjnego managera

Podczas przeglądu roku CFO patrzy na YTD jak na „termometr strategii”, a manager sprzedaży – jak na „licznik do premii”. Dla obu YTD to po prostu suma od pierwszego dnia roku do daty raportowej, ale oczekiwania wobec raportu bywają zupełnie różne.

Z technicznego punktu widzenia YTD jest najprostsze:

  • początek roku: zawsze 1 stycznia danego roku (lub inny stały dzień w przypadku roku finansowego),
  • koniec zakresu: bieżąca data raportowa.

Dla roku kalendarzowego i tabeli Sprzedaż formuła SUMIFS wygląda następująco:

=SUMIFS(
    Sprzedaż[Kwota];
    Sprzedaż[DataTransakcji]; ">=" & DATE(YEAR($B$1);1;1);
    Sprzedaż[DataTransakcji]; "<=" & $B$1
)

Reszta to już tylko kwestia „opakowania”: jedni chcą wykres skumulowany, inni – tabelę z porównaniem YTD vs plan, kolejni – YTD vs poprzedni rok. Logika czasu pozostaje identyczna.

YTD vs poprzedni rok – porównanie „do tego samego dnia roku”

Prezes dostaje raport YTD i pyta: „A jak to wyglądało w zeszłym roku w tym samym momencie?”. Porównanie do pełnego poprzedniego roku w marcu zwykle wypada słabo, ale niewiele mówi – sensowna jest kontrastowa para: YTD bieżącego roku vs YTD poprzedniego do tej samej daty.

Schemat jest podobny jak przy porównaniu MTD/QTD:

  • YTD bieżący rok: od 1 stycznia bieżącego roku do daty raportowej,
  • YTD poprzedni rok: od 1 stycznia poprzedniego roku do „daty raportowej rok wcześniej”.

Przy dacie raportowej w $B$1 możesz użyć:

  • Data raportowa rok wcześniej:
    =EDATE($B$1;-12)
  • Początek poprzedniego roku:
    =DATE(YEAR(EDATE($B$1;-12));1;1)

YTD poprzedniego roku policzysz wtedy jako:

=SUMIFS(
    Sprzedaż[Kwota];
    Sprzedaż[DataTransakcji]; ">=" & DATE(YEAR(EDATE($B$1;-12));1;1);
    Sprzedaż[DataTransakcji]; "<=" & EDATE($B$1;-12)
)

Dopiero takie zestawienie pokazuje realną dynamikę – czy 15 marca tego roku jest lepszy od 15 marca roku ubiegłego, a nie od „całego poprzedniego roku”.

YTD w tabeli przestawnej – filtr relatywny zamiast ręcznego zakresu

Przy raportach operacyjnych analityk często dostaje prośbę: „zrób YTD, ale żebym nie musiał zmieniać daty za każdym razem”. W tabeli przestawnej część pracy mogą przejąć filtry relatywne dat.

Jeśli Twoje dane zawierają pełną historię kilku lat, a tabela przestawna ma pole daty w obszarze filtrów lub wierszy, spróbuj takiej konfiguracji:

  1. Umieść DataTransakcji w obszarze filtrów.
  2. Na strzałce filtra wybierz Filtry dat → Ten rok.
  3. Jeżeli Excel oferuje filtr typu „Od początku roku do dzisiaj”, skorzystaj z niego – inaczej użyj „Pomiędzy” i ustaw ręcznie:
    • datę początku roku (np. 01.01.2024),
    • datę raportową (np. 15.03.2024).

Przy codziennie odświeżanym raporcie, gdzie data raportowa równa się „dziś”, taki filtr działa jak automatyczny YTD – wystarczy odświeżyć tabelę przestawną po otwarciu pliku.

Rok finansowy YTD – przesunięty początek roku w praktyce

W spółce giełdowej zarząd żyje rokiem finansowym, który startuje w lipcu. Gdy pytają o YTD, chodzi im o okres od 1 lipca do daty raportowej, a nie od 1 stycznia. Jeśli Excel trzyma się roku kalendarzowego, nieporozumienia są gwarantowane.

Jeżeli nie masz rozbudowanej tabeli kalendarza finansowego, można zbudować proste YTD finansowe funkcyjnie. Załóżmy, że rok finansowy zaczyna się 1 lipca. Dla daty w $B$1 potrzebujesz najpierw ustalić rok finansowy:

=YEAR($B$1) + IF(MONTH($B$1)>=7;1;0)

To jest rok „oznaczenia” (np. FY2024), ale do YTD kluczowy jest początek aktualnego roku finansowego. Możesz go wyznaczyć tak:

=IF(
    MONTH($B$1)>=7;
    DATE(YEAR($B$1);7;1);
    DATE(YEAR($B$1)-1;7;1)
)

Następnie w SUMIFS użyj tego początku jako granicy:

Najczęściej zadawane pytania (FAQ)

Co to jest MTD, QTD i YTD w Excelu i czym się różnią?

Wyobraź sobie, że dyrektor pyta: „pokaż mi wyniki za ten miesiąc, za kwartał i od początku roku – na dziś”. Właśnie to opisują skróty MTD, QTD i YTD. Wszystkie oznaczają sumę „od początku danego okresu do konkretnej daty raportowej”, ale różnią się długością tego okresu.

MTD (Month-To-Date) liczy od pierwszego dnia bieżącego miesiąca do daty raportowej. QTD (Quarter-To-Date) zbiera dane od pierwszego dnia bieżącego kwartału do tej daty. YTD (Year-To-Date) sumuje wszystko od 1 stycznia bieżącego roku do dnia raportu. Kluczowy jest więc nie tylko początek okresu, ale przede wszystkim ustalony, jednoznaczny „dzień końcowy”, według którego liczysz wszystkie te miary.

Jak policzyć MTD, QTD i YTD w Excelu na zwykłej tabeli (bez Power Pivot)?

Typowy scenariusz: masz tabelę z kolumnami DataTransakcji i Kwota, a szef pyta o wynik „na dziś”. Najprostsze podejście to jedna, dobrze zdefiniowana komórka z datą raportową (np. B1) i formuły SUMA.WARUNKÓW oparte na tej dacie.

Przykładowo, dla tabeli Excela o nazwie Sprzedaż:

  • MTD: =SUMA.WARUNKÓW(Sprzedaż[Kwota];Sprzedaż[DataTransakcji];">="&DATA(ROK($B$1);MIESIĄC($B$1);1);Sprzedaż[DataTransakcji];"<="&$B$1)
  • QTD – potrzebna kolumna pomocnicza z kwartałem lub formuła określająca początek kwartału, np. przez funkcję INT((MIESIĄC($B$1)-1)/3)*3+1 w części startowej daty.
  • YTD: =SUMA.WARUNKÓW(Sprzedaż[Kwota];Sprzedaż[DataTransakcji];">="&DATA(ROK($B$1);1;1);Sprzedaż[DataTransakcji];"<="&$B$1)

Najważniejsze, aby wszystkie formuły korzystały z tej samej „daty raportowej” i jednej kolumny typu data. Dzięki temu raport można łatwo przeliczyć „na wczoraj”, „na koniec miesiąca” itp., zmieniając tylko jeden dzień w kalendarzu.

Jak poprawnie porównywać MTD, QTD i YTD rok do roku (YoY) w Excelu?

Typowy błąd to porównanie bieżącego YTD (niepełny rok) do pełnego roku poprzedniego. Wtedy wynik wygląda często „za słabo”, choć sprzedaż idzie zgodnie z planem – po prostu porównujesz 4 miesiące do 12. Kluczem jest porównywanie tego samego wycinka kalendarza.

Przykład dla YTD: jeśli data raportowa to 10.04.2024, to:

  • bieżący YTD obejmuje 01.01.2024–10.04.2024,
  • poprzedni YTD powinien obejmować 01.01.2023–10.04.2023.

Technicznie w Excelu możesz to zrobić, odejmując rok w filtrze daty (ROK(DataTransakcji)=ROK(DataRaportowa)-1) i jednocześnie pilnując dnia roku (np. przez kolumnę pomocniczą z funkcją DZIEŃ.ROKU). Zasada jest jedna: porównuj MTD do MTD, QTD do QTD, YTD do YTD – zawsze „do tego samego dnia kalendarza”, a nie do pełnych, zamkniętych okresów, jeśli szukasz sensownego porównania bieżącego tempa.

Jak przygotować kolumnę z datą, żeby MTD, QTD i YTD działały poprawnie?

Częsta sytuacja: dane przychodzą jako tekst „styczeń 2024” albo w trzech kolumnach „Rok”, „Miesiąc”, „Dzień”. Formuły wyglądają dobrze, ale MTD wychodzi puste lub z dziwnymi wartościami, bo Excel nie widzi prawdziwych dat. Podstawą jest jedna, spójna kolumna typu data.

Jeśli masz rok, miesiąc i dzień w osobnych kolumnach, zbuduj datę funkcją DATA(Rok;Miesiąc;Dzień). Jeśli masz tylko rok i miesiąc, możesz przyjąć np. pierwszy dzień miesiąca: DATA(Rok;Miesiąc;1). Teksty typu „styczeń 2024” dobrze jest najpierw przekształcić w coś w stylu „1-styczeń-2024” i użyć DATA.WARTOŚĆ() lub Power Query. Gdy cała kolumna jest już prawdziwą datą (a nie tekstem), filtry dat, tabele przestawne i formuły MTD/QTD/YTD zaczynają działać przewidywalnie.

Dlaczego porównanie MTD do pełnego poprzedniego miesiąca bywa mylące?

Wyobraź sobie 5 dzień miesiąca: sprzedaż wygląda „tragicznie” przy porównaniu do całego poprzedniego miesiąca, bo naturalnie masz tylko kilka dni danych. To typowy przykład asymetrycznego porównania – niepełny okres kontra pełny.

Masz tu dwa sensowne podejścia:

  • porównać MTD bieżącego miesiąca do MTD poprzedniego miesiąca (np. 1–5 marca vs 1–5 kwietnia) – pokazuje tempo na tym samym etapie miesiąca,
  • porównać bieżące MTD do pełnego poprzedniego miesiąca – ale świadomie, traktując to jako prognozę („czy idziemy tempem lepszym niż miesiąc temu?”), a nie bezpośrednie „lepsze/gorsze”.

Jeśli nie nazwiesz wprost, co porównujesz, dyskusja szybko zamienia się w „Excel źle liczy”. Gdy w legendzie i nagłówkach jasno oznaczysz MTD vs pełny miesiąc, odbiorcy raportu znacznie rzadziej wyciągają błędne wnioski.

Czy lepiej używać tabel Excela (Ctrl+T) do liczenia MTD, QTD i YTD?

Gdy raport rośnie z kilkuset do kilkudziesięciu tysięcy wierszy, ręczne zakresy typu A2:A10000 zaczynają się mścić: nowe dane nie wchodzą do formuł, a MTD za „ten miesiąc” nie uwzględnia ostatnich transakcji. Tabela Excela (Ctrl+T) rozwiązuje większość z tych problemów „z automatu”.

Po zamianie zakresu na tabelę:

  • zakresy w SUMA.WARUNKÓW czy innych funkcjach rozszerzają się same przy dopisywaniu nowych wierszy,
  • formuły stają się czytelniejsze, bo używają nazw kolumn (np. Sprzedaż[Data]),
  • łatwiej łączysz dane z tabelą przestawną i segmentami dat (szybkie filtrowanie MTD, QTD, YTD).

W praktyce, jeśli dane mają być aktualizowane cyklicznie, praca na tabelach zamiast „gołych” zakresów jest niemal obowiązkowa, żeby MTD, QTD i YTD nie wymagały ręcznego poprawiania po każdym imporcie.

Najważniejsze punkty

  • MTD, QTD i YTD to różne długości tego samego podejścia: sumujesz od początku miesiąca, kwartału lub roku do konkretnej daty raportowej, a nie „do końca okresu z kalendarza”.
  • Kluczowe jest precyzyjne ustalenie daty raportowej (np. dzisiaj, ostatni dzień poprzedniego miesiąca); dopiero do niej dobiera się formuły, inaczej te same dane dają różne wyniki w kolejnych raportach.
  • „To-date” (MTD/QTD/YTD) i pełne miesiące/kwartały/lata to dwa różne światy – mieszanie ich w jednym dashboardzie bez jasnego opisu prowadzi do mylnych wniosków, zwłaszcza przy porównaniach rok do roku.
  • Dobór okresu (MTD, QTD czy YTD) wynika z pytania biznesowego: czy chcesz ocenić bieżące tempo (np. MTD vs poprzedni miesiąc), dowiezienie planu kwartalnego (QTD) czy realizację budżetu rocznego (YTD).
  • Definicję okresu trzeba ustalić przed pisaniem formuł – jasno zdefiniowane zakresy MTD/QTD/YTD znacząco ograniczają spory typu „Excel źle liczy” i ujednolicają raportowanie w zespole.
  • Fundamentem sensownych porównań MTD/QTD/YTD jest pojedyncza, poprawna kolumna dat (typ data) w danych źródłowych; rozproszone pola rok/miesiąc/dzień czy daty w formacie tekstowym generują ciche, trudne do wychwycenia błędy.
  • Stały, świadomy sposób liczenia okresów czasowych zmienia Excel z „loterii” w przewidywalne narzędzie – przy tych samych danych zawsze dostajesz te same liczby, niezależnie od tego, kto przygotowuje raport.

Przeczytaj także: