Czyli grupowanie liczby dni przeterminowania

Prowadzimy listę faktur, na której kontrolujemy oczywiście ich terminy płatności. O tym, jak sprawdzić, czy faktura jest przeterminowana i zaznaczyć ją pięknym kolorkiem, już wcześniej pisałam. Dzisiaj natomiast będzie o tym, jak pogrupować przeterminowane faktury według liczby dni o jaką są przeterminowane. Konkretnie zastanowimy się nad formułą, ktą przy każdej fakturze określi jej status czy grupę przeterminowania, do której taka faktura należy. Otrzymane dane będzie można potem filtrować, sortować i oczywiście analizować formułami czy tabelą przestawną. Chcę otrzymać coś takiego:

Formatka z wynikiem

Najpierw określimy liczbę dni, o jakie faktury są przeterminowane, a następnie owe grupy. Celowo wprowadziłam tutaj 2 kolumny, gdyż uważam, że informacja o liczbie dni przeterminowania jest istotna i użytkownik może chcieć ją znać. Oczywiście, jeśli tego nie będziecie potrzebować- wszystko można skompresować do jednej formuły i wyświetlić od razu grupę przeterminowania.

Tabela grup

Grupy będę ustalała wg takiej tabelki:

Tabela z grupami

Czyli, jeśli faktura nie jest przeterminowana, czyli liczba dni przeterminowania wynosi 0 – trafi do grupy OK. Jeśli natomiast będzie przeterminowana od 1 dnia do 30 – trafi do grupy P30. Kolejny przedział to od 31 do 90 –> P90 itd. Jak będziesz tworzyć swoją tabelkę na podstawie mojej – koniecznie zwróć uwagę na przedziały, jakie trafiły do Excela (obrazek). W komórce/tabelce określamy bowiem dolną granicę przedziału. Górna zaś jest określana przez dolną granicę kolejnego przedziału. Uwaga, bo to jest tricky i jest związane z działaniem funkcji WYSZUKAJ.PIONOWO, której użyję do przypisania liczb do przedziałów (więcej o tym mówiłam podczas webinaru o WYSZUKAJ.PIONOWO).

Liczba dni przeterminowania

To mój ulubiony, chociaż bardzo prościutki moment. Najpierw określimy sobie, czy faktura w ogóle jest przeterminowana, a jeśli tak, to o ile dni. Informacja ta może przydać nam się do bieżących analiz czy po prostu do rozmowy z klientem-dłużnikiem. Dlatego poświęcam temu oddzielną kolumnę – nie ma co ich oszczędzać, mamy 16 384 kolumn w arkuszu ;).

Zakładam, że jeśli faktura jest zapłacona, czyli ma datę zapłaty, to nie jest przeterminowana. Zakładam tak nawet, gdyby została zapłacona po terminie, czyli analizujemy faktury niezapłacone na tę chwilę. Pozostałe faktury są niezapłacone, czyli będę dla nich badała ilość dni przeterminowania.

Formuła, która to sprawdzi jest następująca (komórka G6):

=JEŻELI(F6>0;0;MAX(0;$D$3-E6))

W pierwszej jej części sprawdzam, czy jest data zapłaty. Jeśli jest, oznacza to, że faktura została zapłacona, więc nie jest przeterminowana.

W drugiej części badam różnicę między datą dzisiejszą a terminem płatności. Różnicę umieściłam w funkcji MAX, ponieważ na liscie moga wystąpić faktury, które nie są przeterminowane, ale jeszcze nie są zapłacone. W takiej sytuacji dostałabym ujemną różnicę, czego nie chcę. Aby zaś w takich przypadkach dostać zero – każę funkcji MAX wybrać większą wartość z zera i wyniku różnicy.

Komentarza wymaga jeszcze dzisiejsza data. Umieściłam ją w komórce D3, zamiast użyć w formule funkcji DZIŚ(). Otrzymałabym ten sam wynik oczywiście, także śmiało tak zrób, jeśli wolisz. Ja wolę widzieć czarno na białym z jakiej daty korzystają formuły, dlatego użyłam do tego zewnętrznej komórki, którą w dodatku mogę ręcznie kontrolować w szczególnych wypadkach.

Oto wynik obliczeń:

Liczba dni przeterminowania

Grupa przeterminowania

To już będzie pikuś, ponieważ praktycznie całą pracę zrobiliśmy w kroku wcześniej. Teraz tylko pozostaje przypisanie grupy przeterminowania na podstawie liczby, którą określiliśmy krok wcześniej. I jeszcze kwestia faktur już zapłaconych: jeśli chcemy nadać im szczególny status, np” zapłacono”, musimy wrzucić wszystko w funkcję JEŻELI. Jeśli status „OK” dla takich faktur jest OK, to wystarczy samo WYSZUKAJ.PIONOWO.

A formuła jest taka (komórka H6):

=JEŻELI(F6>0;"Zapłacono";WYSZUKAJ.PIONOWO(G6;$K$9:$L$14;2))

Czyli znowu, w pierwszym argumencie JEŻELI sprawdzamy, czy faktura jest zapłacona – jeśli tak, nadajemy jej status „Zapłacono”. Jeśli nie jest – przyporządkowujemy grupę przeterminowania funkcją WYSZUKAJ.PIONOWO.

Po skopiowaniu formuły w dół, otrzymujemy następujący efekt:

Wynik

I to jest tyle. Prościutkie a jakże przydatne. Mam nadzieję! :). Co ciekawe taki sam mechanizm można zastosować do grupowania terminów płatności: na 30-dniowe, 90-dniowe itp. Excelowa funkcjonalność będzie taka sama.

I wersja wideo: