- niezbędnik -, b'logger, pan od komputerów, Techblog

Sumowanie warunkowe w LibreOffice.

No prosz, myślałem, że nic mnie nie zaskoczy już w takim stopniu, aż tu nagle wtem.

Problem: w pierwszej kolumnie [np. A1:A100] mamy wartości, w drugiej [B1:B100] jakiekolwiek znaczniki [przyjmijmy znak „x”] określające, czy wartość po lewej stronie z kolumny A ma być sumowana. Komórka wynikowa ma zwracać sumę tych komórek z kolumny A, dla których odpowiadające im komórki w kolumnie B posiadają znacznik. SUMA.JEŻELI() w pierwszej chwili odmówiła współpracy, szukając rozwiązania znalazłem coś takiego:

=suma((A1:A100)*(B1:B100="x")). Dowcip polega na zatwierdzeniu całości nie Enterem, ale poprzez Ctrl-Shift-Enter, co powoduje włączenie formuł wektorowych. Prosto, łatwo, elegancko i inspirująco. Pomysł wzięty stąd.

Napisane przez torero

9 myśli w temacie “Sumowanie warunkowe w LibreOffice.”

  1. Nie jestem specem od arkuszy kalkulacyjnych, ale jeszcze fajniej i eleganciej byłoby mieć tam funkcje wyższego rzędu jak map, filter czy reduce. Ale zdaje się, że na chwilę obecną składnia formuł nie jest chyba w stanie tego obsłużyć. A byłby to na pewno krok w ciekawym kierunku. Chociaż jak teraz o tym myślę, to w sumie nie takie proste w przypadku arkusza. Wygląda jak ciekawy problem do przemyślenia. 🙂

    Polubienie

    Odpowiedz

  4. A wystarczyło przeczytać pomoc dla funkcji SUMA.JEŻELI…

    =SUMIF(B1:B100;"x";A1:A100)

    Ewentualnie można było wykorzystać SUMA.WARUNKÓW (SUMIFS), którego składnia jest bardziej czytelną. SUMA.WARUNKÓW jest obsługiwana przez LibreOffice Calc od wersji 4.0, wydanej w lutym 2013 roku.

    =SUMIFS(A1:A100;B1:B100;"x")

    Polubienie

    Odpowiedz

  5. @Mirek: pisałem właśnie, że SUMA.JEŻELI() nie zadziałała – zasugerowałem się przykładem z jakiegoś helpa, w którym drugi argument to „>0” i przez parę minut próbowałem wpisać tam porównanie z „x”, zamiast, jak piszesz, po prostu „x”. Ale… nie ma tego złego, co by na dobre nie wyszło, nauczyłem się czegoś nowego – i to imo znacznie przydatniejszego, niż pojedyncza funkcja, bo jakimś naprawdę niepojętym dla mnie cudem formuły wektorowe jakoś dotychczas mnie omijały.

    Polubienie

    Odpowiedz

  6. To może zacznijmy od początku – czym jest „x”? Jakie kryteria mają spełniać komórki, aby ich sąsiedzi zostali uwzględnieni w sumie?

    Polubienie

    Odpowiedz

  7. Nie tędy droga. „x” jest arbitralnie określonym znacznikiem kwalifikacji do sumy w określonej kolumnie. Kolumn z „x” jest więcej – kolumna A zawiera wydatki, w główkach kolumn B, C, etc. są sumy określonych grup wydatków. A dla pojedynczej analizy wyciągać armat nie mam czasu.

    Polubienie

    Odpowiedz

  8. Ale jesteś uparty. Rozumiem że cieszysz się z tego, że nauczyłeś się nowej sztuczki. Rozumiem, że po nabyciu nowej umiejętności wszędzie widzisz problemy, które można za jej pomocą rozwiązać. Ale przez to nie dostrzegasz, że wkręcasz śruby przy pomocy młotka.

    Moja podstawowa teza brzmi: formuły macierzowe są odpowiedzią jedynie w kilku przypadkach granicznych. Zazwyczaj ten sam cel można osiągnąć przy pomocy normalnych formuł lub dedykowanych narzędzi dostarczonych przez pakiet. Formuły oraz dedykowane narzędzia są generalnie preferowane ze względu na czytelność rozwiązania, łatwość modyfikacji i utrzymania, wydajność (dzisiaj już raczej pomijalne), przenośność oraz brak „quirk tax”, że tak to nazwę.

    Dla przypadku podstawowego – sumowanie wartości w kolumnie gdy wartości w innej kolumnie spełniają jedno kryterium – odpowiedzią jest SUMA.JEŻELI.

    Dla przypadku bardziej zaawansowanego – sumowanie wartości w kolumnie gdy wartości w innej kolumnie spełniają więcej niż jedno kryterium, lub wartości z wielu kolumn muszą spełniać kryteria – odpowiedzią jest SUMA.WARUNKÓW.

    Gdy sumować trzeba z wielu kolumn jednocześnie, można wykorzystać tabele przestawne albo sumy częściowe. Lub stworzyć kolumnę tymczasową z sumą wartości w pozostałych kolumnach i w ten sposób zredukować problem do formy już przedstawionej.

    Fakt faktem, że SUMA.JEŻELI i SUMA.WARUNKÓW nie radzą sobie zbyt dobrze w sytuacjach, gdy kryteria mają być połączone spójnikami innymi niż koniunkcja. Formuła macierzowa może być atrakcyjna gdy chodzi o spójnik alternatywy. Dla dowolnie skomplikowanych warunków logicznych i tak jedynym rozwiązaniem jest kolumna tymczasowa.

    Polubienie

    Odpowiedz

  9. Chciałbym zauważyć, iż dzielnie polemizujesz z argumentem, który tutaj nie padł. W komciu #7 ani nigdzie wyżej nie napisałem ani słowa o tym, że uważam formuły wektorowe za lepsze, tylko odpowiadałem na Twoje pytanie z #6.

    F.w. owszem, cieszą jak każda nowa zabawka – i zapewne dla utrwalenia jakiś czas pobawię się nimi ponadnormatywnie [tabliczka mnożenia FTW!, czysto estetyczny urok tego rozwiązania w porównaniu z edycją formuł mnie zwyczajnie urzeka]. Do szerszej analizy napisałem jednak kiedyś dedykowany młotek w pythonie, o czym już tutaj kiedyś wspominałem, natomiast arkusze wykorzystuję relatywnie rzadziej, nie ma więc potrzeby kruszyć kopii o przewagę rozwiązania X nad Y, bo to prace dorywcze, prockooszczędne i w większej części po prostu już opracowane. Peace 🙂

    Polubienie

    Odpowiedz

Dodaj komentarz