• Zapisz się do newslettera, aby otrzymywać powiadomienia o nowościach na blogu
    Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Suma przeterminowanych należności

Czyli coś, czego nie wiesz o SUMIE.WARUNKÓW

Tę funkcję wszyscy znają. Należy ona do TOP10, no może TOP20 najczęściej używanych funkcji w Excelu. SUMA.WARUNKÓW, bo o niej mowa, jest bardzo prosta i niesamowicie użyteczna. Problem pojawia się jednak, gdy za jej pomocą chcemy podsumować wartości na podstawie komórek, które… nie mają wartości, są PUSTE.

Jakie wtedy zastosować kryterium?

Odpowiem na to pytanie w tym odcinku na przykładzie określania sumy przeterminowanych należności. Oto formatka:

Formatka

Formatka

Do dzieła!

Najpierw określmy sobie, jakie należności będziemy traktowali jako przeterminowane. Na przykładzie powyższej formatki, ja widzę 2 warunki:

  1. Termin płatności już minął
  2. Faktura nie została zapłacona

Przekładając to na język Excela mamy odpowiednio:

  1. Data w kolumnie Termin płatności jest mniejsza od daty dzisiejszej (jeśli jest równa – dajemy klientowi szansę ;))
  2. Nie wpisano wartości w kolumnie Data zapłaty

W obu “językach”, ludzkim i excelowym, mamy 2 warunki. Czyli będziemy chcieli sumować wartości z kolumny Należność, jeśli oba powyższe warunki będą spełnione. Jest to idealny case do użycia funkcji SUMA.WARUNKÓW (gdyby był tylko jeden warunek – wystarczyłaby SUMA.JEŻELI).

Argumenty tej funkcji będą następujące:

  1. Należność – jako sumowana kolumna
  2. Termin płatności – jako kolumna dla pierwszego kryterium
  3. kryterium mówiące o tym, że termin płatności ma być mniejszy od dzisiejszej daty
  4. Data zapłaty -jako kolumna dla drugiego kryterium
  5. kryterium mówiące o tym, że ma być wpisana data zapłaty (zakładamy, że tutaj będą wpisywane tylko daty)

A formuła, która to wszystko robi wygląda tak:

=SUMA.WARUNKÓW(D7:D17;B7:B17;"<"&C3;C7:C17;"=")

Zwróć uwagę na ostatni argument: “=”, ponieważ to o niego tutaj się rozchodzi!

Dokładnie tak: jeśli w argumencie Kryterium wpiszesz “=” w jednej z tych funkcji: SUMA.WARUNKÓW, SUMA.JEŻELI, LICZ.WARUNKI, LICZ.JEŻELI, funkcje te zinterpretują to jako szukaj pustych komórek. Przyznam, że dość nieintuicyjne, ale cóż – that’s the way it is 🙂

Po wpisaniu formuły, otrzymamy taki wynik:

Wynik

Wynik: suma przeterminowanych należności

Rozwiązania alternatywne

Tę sytuację można rozwiązać oczywiście również za pomocą kolumny pośredniczącej, w której można umieścić funkcje JEŻELI i ORAZ i wyświetlić albo tę należność albo zero. Wyniki wtedy podsumujemy po prostu funkcją SUMA. To rozwiązanie byłoby przydatne, gdybyśmy potrzebowali mieć dodatkową informację, która należność jest przeterminowana (i ile).

Alternatywa z JEŻELI i ORAZ

Alternatywa wykorzystująca JEŻELI i ORAZ

Użyta w tym rozwiązaniu funkcja to:

=JEŻELI(ORAZ(B7<$C$3;C7="");D7;0)

Można też oczywiście zrobić tak, jak w tym wpisie – będziemy mieli wtedy informację o tym, która FV jest przeterminowana i to jeszcze zaznaczymy ją kolorem. Tutaj wystarczy SUMA.JEŻELI według statusu Przeterminowana (lekko zmodyfikowałam formatkę, żeby pasowała do case’a):

Alternatywa z SUMA.JEŻELI

Alternatywa z SUMA.JEŻELI

Formuła użyta we wskazanej komórce to po prostu:

=SUMA.JEŻELI(D6:D16;"przeterminowana";E6:E16)

Co kto lubi i potrzebuje :). Enjoy!

A oto wersja z gotowcem do pobrania:

I wersja wideo:

 

 

 

 

 

Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o o nowościach na blogu.

Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Tagi , , , , , , , , .Dodaj do zakładek Link.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *