Suma warunków

[maw79]

Witam,
zaczynam przygodę z openOfficem
jak zrobić sumę warunków aby sumował po datach do odpowiedniego miesiąca wg daty.
jak w załączniku.
dziękuję za każdą pomoc

[Jermor]

Jeśli, jak piszesz, zaczynasz swoją przygodę z OpenOfficem, to zaczynasz z dość poważnego poziomu. Działania na datach sprawiają jednak róznego rodzaju problemy. Zanim jednak o twoim problemie napiszę, zasugeruję przejście na inny pakiet oprogramowania, nazwijmy "braterskiego", czyli LibreOffice. Rozwijany jest szybciej, ma bardziej rozbudowane możliwości i ma więcej funkcji, w tym także zgodnych z funkcjami Excela.
Powinieneś także poczytać dokładnie opis funkcji SUMA.WARUNKÓW().
Trzeci argument tej funkcji musi reprezentować kryterium, jakie musi być spełnione, aby dane zostały zakwalifikowane do sumowania. Zapis ">=g1" nie jest poprawny. On po prostu oznacza, że sumowane będą te komórki kolumny B, które w odpowiednim wierszu kolumny A zawierają zapis większy lub równy ciągowi tekstowemu "g1" a nie zawartości komórki G1 jak zapewne planowałeś. Poprawny zapis tego kryterium powinieneś zapisać tak: ">="&G1.
Zatem całą formułą powinna wyglądać następująco:

Kod: Zaznacz cały

=SUMA.WARUNKÓW($B$1:$B$20;$A$1:$A$20;">="&G1;$A$1:$A$20;"<"&G2)

Musisz pamiętać o tym, aby w kolumnie G, w wierszu 13. wpisać datę 1.1.2023, gdyż twoja formuła wymaga sprawdzenia, czy data jest mniejsza od tej z następnego miesiąca oraz ustawić adresy bezwzględne dla zakresów dat (kolumna A) i kwot (kolumna B), gdyż dopiero wówczas możesz skopiować tę formułę do pozostałych miesięcy.
Jeżeli przewidujesz, że gromadzone wieloletnie dane chciałbyś sumować wg miesięcy w konkretnym roku, możesz zmienić kolumnę (twoją) G tak:
Załóżmy, że w komórce H1 wpiszesz interesujący cię rok. W komórce G1 możesz wpisać formułę =DATA(H1;1;1), czyli zawierać będzie ona datę 1. stycznia roku określonego przez H1. W komórce G2 wpisz formułę =EOMONTH(G1;0)+1. Uzyskasz datę pierwszego dnia w następnym miesiącu tego roku. Powiel tę formułę aż do wiersza 13. Teraz zaznacz komórki od G1 do G13 i przypisz im formatowanie własne, kod: MMMM, zamiast dat zobaczysz nazwy miesięcy. Pamiętaj, że formatowania zmienia tylko sposób wyświetlania zawartości komórki, nie zmienia jej zawartości, czyli G1 do G13 nadal zawierają daty, które wykorzystujesz w swoich formułach.
Jeszcze jedna uwaga funkcja EOMONTH() w Apache OpenOffice ma odpowiednik w LibreOffice pod nazwą NR.SER.OST.DN.MIES(). Pakiety potrafią prawidłowo rozpoznać te funkcje, czyli plik zapisany przez OpenOffice zostanie prawidłowo wczytany przez LibreOffice i na odwrót. Nazwy funkcji zostaną dopasowane do używanego oprogramowania.

[Jan_J]

@Jermor zareagował szybciej, ale załączam odpowiedź, bo dotyczy innej strony, bardziej o poprawności wyrażeń niż o sposobie osiągniecia danego celu.

Po pierwsze, szkoda, że załączasz sam obrazek zamiast tekstu z formułą. Być może analizując jej tekst sam byś zauważył, że to

Kod: Zaznacz cały

=SUMA.WARUNKÓW(B1:B24;A1:A25; ">g1"; A1:A10"<g2")

nie trzyma się kupy. A bo

1. między A1:A10 as "<g2" nie ma średnika. Chyba myślisz o A1:A10 jako o zakresie danych konfrontowanych z warunkiem, a o "<g2" jako o opisie warunku.
2. teksty ">g1" oraz "<g2" nie spełniają swojej roli. W kolimnie G znajdują się daty, w kolumnie A również. Ale "<g2" nie oznacza „mniejszy niż wartoź znajdująca się w g2”, ponieważ "g2" w tekście nie jest wartością, tylko adresem. Dane tekstowe nie mogą zawierać adresów — tj. adresy w nich nie są rozpoznawane. Właściwsze byłoby użycie konstrukcji "<" & G2.
3. Bloki B1:B24, A1:A25 oraz A1:A10 różnią się liczbą wierszy. SUMA.WARUNKÓW tego nie zrozumie; a i autor samego pomysłu lepiej, by jasno określił sam dla siebie co chce osiągnąć.

[maw79]

Dziękuje bardzo za pomoc i tak wyczerpujące odpowiedzi.
Zrobiłem wg wskazówek ale nie liczy poprawnie ostatni wpis z danego miesiąca zalicza do następnego. Cos znowu pokręciłem.
Załączam plik.

[Jermor]

@Jan_J napisał już, że zakresy sumowania i kryteriów muszą być takiej samej wielkości. U ciebie są, ale zakres wartości sumowanych wskazujesz jako liczby od 1. do 249. wiersza, natomiast zakresy dat obejmują wpisy od 2. wiersza do 250. To nie jest błędem formuły, zakresy są takiej samej wielkości, ale może powodować błedy gdy wypełnione zostaną wszystkie komórki.
Błędny wynik otrzymujesz, gdyż w wierszu 50. wpisałeś datę należącą do roku 2020. Dane tej daty nie wchodzą do wyników sumowania warunkowego, ale wchodzą do sumowania bezpośredniego wszystkich wartości.

[Jermor]

Jeszcze jedna uwaga. W swoim zestawieniu wyróżniłeś daty ostatniego dnia miesiąca żółtym kolorem, sugerując tym samym, że między tymi wierszami znajdują się dane tego samego miesiąca. Tak jednak nie jest. Np. w obszarze marca znajduje się data z lutego (wiersz 48) w wierszu 55. wpisałeś w ogóle datę od czapy, 3.04.20222. To powoduje różnice w oczekiwanych wynikach.

[Jermor]

Trochę jestem namolny, ale chciałbym cię jednak namówić na LibreOffice. W przypadku dat masz w nim dodatkowe funkcje. Jedną z nich jest funkcja DNI.ROBOCZE() wyznaczająca ilość dni roboczych pomiędzy dwoma datami. Funkcja pozwala wybrać dni tygodnia zaliczane do dni wolnych. Można określić dowolny wykaz tych dni, chociaż u nas takimi dniami są sobota i niedziela. Ponadto można wskazać listę innych konkretnych dat, które także są dniami wolnymi. Funkcja uwzględni to w swoich wyliczeniach.
Przykład takiego rozwiązania przedstawiam na ilustracji.

obraz_2022-05-16_185649057.png (21.96 KiB) Przejrzano 2773 razy

W komórce A1 wpisujesz rok. Na tej podstawie generowane są daty w obszarze A2:A13, odpowiadają one pierwszemu dniu kolejnego miesiąca w roku z A1. Komórki A2:A13 są sformatowane kodem MMMM, więc zamiast konkretnych dat widać nazwę miesiąca.
W kolumnie C znajdują się funkcje DNI.ROBOCZE() wyznaczające ilość dni roboczych w każdym miesiącu. W obliczaniu tych dni uwzględniane są wolne soboty i niedziele oraz dni wymienione w kolumnie G. W tej kolumnie nie umieściłem dwóch oficjalnych dni wolnych: Wielkanocy i Zielonych Świątek, gdyż te święta i tak zawsze występują w niedzielę, więc są uwzględniane przez dzień tygodnia. Daty w tej kolumnie są tworzone na podstawie wpisanego roku do komórki A1. Kolumna G może zawierać i inne daty, nie tylko świąteczne, ale np. dni urlopowe.
Wersję tego przykładu przeznaczoną dla Calc LibreOffice załączam do przetestowania.
Okazuje się, że odpowiednik funkcji DNI.ROBOCZE() istnieje w Apache OpenOffice pod nieprzetłumaczoną postacią (podobnie jak EOMONTH()) NETWORKDAYS(). W odróżnieniu od wersji istniejącej w LibreOffice ma trzy argumenty, te akurat wymienione w przykładzie. W LibreOffice istnieje jeszcze czwarty argument, który jeśli jest pominięty oznacza, że dniami tygodnia wolnymi są sobota i niedziela. Formalnie ten czwarty argument, to macierz złożona z siedmiu elementów. Pierwszy odpowiada niedzieli. 0 (zero) na pozycji dnia oznacza, że ten dzień jest dniem roboczym, wartość niezerowa — dniem wolnym. Przykładowy zapis tego argumentu dla sytuacji gdy dniami wolnymi są tylko: poniedziałek, wtorek i czwartek wygląda tak: {0;1;1;0;1;0;0}.

[maw79]

Dziękuję za tak dokładne wyjaśnienia.
Przeglądałem daty ale nie zauważyłem tych dwóch błędnie wpisanych ciężko tak po sobie sprawdzać.
Te dni robocze świetna sprawa
Zainstaluję LiberyOffice i się z nią trochę pobawię.
jeszcze raz dziękuję za pomoc.

[maw79]

Teraz pracuję w LibreOffice naprawdę dobry program.
Mam pytanie jak zrobić formatowanie warunkowe aby komórkę z ostatnim wpisem w miesiącu zamalowywał np na żółto.
W excelu tak by wyglądała formułka = miesiąć ($a2) < miesiąć ($a3) a jak to wygląda w LibreOffice.

[Jermor]

Musiałbyś pokazać, jak wygląda układ danych, aby znaleźć informację, jak rozpoznać wiersz sumowania po miesiącu. Jest w niej informacja o dacie albo informacja, że jest to ostatni dzień miesiąca (np. napis "Miesiąc razem")?