Podsumowanie
Aby sumować według miesięcy w kolumnach, możesz użyć funkcji SUMA.WARUNKÓW razem z funkcją EOMONTH. W pokazanym przykładzie formuła w G5 to:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Ta formuła wykorzystuje trzy nazwane zakresy: „kwota” (D5: D15), „klient” (B5: B15) i „data” (C5: C15).
Wyjaśnienie
Funkcja SUMA.WARUNKÓW służy do sumowania wartości w zakresie na podstawie co najmniej jednego kryterium. W tym przypadku potrzebujemy trzech kryteriów:
- Nazwa klienta = klient w kolumnie F
- Data> = pierwszy dzień miesiąca (od daty w wierszu 4)
- Data <= koniec miesiąca (od daty w wierszu 4)
Kluczowy punkt: nazwy miesięcy w G4: I4 są w rzeczywistości prawidłowymi datami sformatowanymi przy użyciu niestandardowego formatu liczbowego „mmm”. To pozwala nam użyć wartości dat w G4: I4 bezpośrednio dla kryteriów # 2 i # 3 powyżej.
W SUMIFS pierwsza para zakres / kryteria służy do sprawdzania nazwy klienta:
client,$F5
Zauważ, że F5 to odniesienie mieszane z zablokowaną kolumną. Umożliwia to zmianę wiersza podczas kopiowania formuły przez tabelę, ale nazwa klienta zawsze pochodzi z kolumny F.
Druga para zakres / kryteria służy do porównywania dat z pierwszym dniem miesiąca:
date,">="&G$4
Jak wspomniano powyżej, wartości w G4: I4 to w rzeczywistości daty: 1 stycznia 2019 r., 1 lutego 2019 r. I 1 marca 2019 r. Zatem to kryterium sprawdza po prostu daty późniejsze niż pierwszy dzień miesiąca w wierszu 4. Zwróć uwagę, że to jest kolejna mieszana referencja, tym razem z zablokowanym wierszem. Umożliwia to zmianę kolumny, gdy formuła jest kopiowana w całej tabeli, ale zachowuje stały numer wiersza. Połączenie za pomocą znaku ampersand (&) jest konieczne podczas tworzenia kryteriów, które używają operatora logicznego i wartości z innej komórki.
Trzecia para zakres / kryteria służy do porównywania dat z ostatnim dniem miesiąca:
date,"<="&EOMONTH(G$4,0)
Aby uzyskać ostatni dzień każdego miesiąca, używamy funkcji EOMONTH na dacie z nagłówka kolumny w wierszu 4. Z zerowym argumentem miesięcy, funkcja EOMONTH zwraca ostatni dzień tego samego miesiąca. Ponownie, odniesienie do G4 jest mieszane, aby zapobiec zmianie wiersza.
Rozwiązanie tabeli przestawnej
Tabela przestawna byłaby doskonałym rozwiązaniem tego problemu, ponieważ może automatycznie grupować według miesięcy bez żadnych formuł. Aby zobaczyć bezpośrednie porównanie formuł z tabelami przestawnymi, zobacz ten film: Dlaczego tabele przestawne.
