suma.jeżeli() kryteria na kilku kolumnach

Użytkowanie arkusza kalkulacyjnego
arek
Posty: 50
Rejestracja: wt cze 02, 2009 5:23 pm

suma.jeżeli() kryteria na kilku kolumnach

Post autor: arek »

ta funkcja suma.jeżeli() jest bardzo pomocna ale trafiłem na jej ograniczenie. A oto sytuacja jaką chcę rozwiązać. jest kolumna A (zawiera liczby) B (etykiety) i C (dodatkowy warunek). Potrzebuje tak napisać funkcję suma.jeżeli() by jeżeli w komórka w C nie jest pusta i w B jest wybrana etykieta (z góry znana) to ma sumować liczby z kolumny A. jak coś takiego robić?
Jan_J
Posty: 4557
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: Jan_J »

Jak napisałeś, sumowanie warunkowe testuje pojedynczą komórkę dla każdego składnika, i to tylko prostym testem typu = <> > < itp.
Większą elastyczność dają formuły wektorowe, zatwierdzane <Ctrl+Shift+Enter> lub z klauzulą [x]Macierz w asystencie. W tym przypadku można skorzystać z interpretacji wartości logicznych 0==False, 1==True, i zastąpić sumowanie warunkowe sumowaniem zwykłym, w którym składniki będą iloczynem tego, co chcemy dodać, przez zerojedynkowy wskaźnik decydujący, które elementy brać do sumy. Czyli mniej więcej
=SUMA((A1:A1000)*(B1:B1000="klucz")*(C1:C1000<>"")) <Ctrl+Shift+Enter>
JJ
LO (7.6) ∙ AOO (4.1) ∙ Python (3.11|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
arek
Posty: 50
Rejestracja: wt cze 02, 2009 5:23 pm

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: arek »

Dzięki, działa świetnie. Gdzie można więcej poczytać o tych operacjach macierzowych?

A tak przy okazji zastanawiam się jak zrobić by warunek był nie ORAZ ale LUB tj. jeżeli w komórka w C nie jest pusta LUB w B jest wybrana etykieta (z góry znana) to ma sumować liczby z kolumny A.
Jan_J
Posty: 4557
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: Jan_J »

To nie jest bezpieczna zabawka. Niektóre funkcje współpracują z notacją wektorową, inne nie. Trochę jest w helpie, porządnego opisu drukiem prawdę mówiąc nigdzie nie widziałem.

Jedną z pułapek jest analogia: True -> 1, False -> 0, więc AND -> *. Ale już OR -> + jest nieuprawnione, bo przecież 1+1 == 2, a skąd biedak ma wiedzieć, że nam nie chodzi o liczby tylko o działania logiczne? Zaś jeżeli wynik konwertuje się na liczbę, będącą współczynnikiem decydującym o uwzględnieniu w sumie, to już kompletna klapa...

Wyjść jest wiele. By nie szukać daleko, np. =SUMA(A1:A1000*((B1:B1000="klucz")+(C1:C1000<>"")>0))
Ale za każdym razem wskazane jest zachowanie najwyższego stopnia ideologicznej czujności.
JJ
LO (7.6) ∙ AOO (4.1) ∙ Python (3.11|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
mgostek
Posty: 2
Rejestracja: wt lip 14, 2009 9:55 pm

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: mgostek »

Świetna rzecz.
O coś takiego mi chodziło a bezsensownie dosyć długo próbowałem z suma jeżeli. :crazy:
Teraz wystarczy wpisać tyle warunków ile potrzeba i wszystko samo się zliczy :D
Kiedyś próbowałem coś takiego na około zrobić, zrobiłem ale natworzyłem kilka wierszy danych lub ręcznie określałem zakres komórek dla parametru 1 i dałem suma jeżeli dla parametru 2. I to był koniec. A tu można od razu taki efekt mieć.
Pozdro
bskladanek
Posty: 10
Rejestracja: czw maja 13, 2010 6:46 am

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: bskladanek »

witam,
Podepnę się pod temat żeby nie tworzyć nowego:)
Mam taki problem, przybliżę na przykładzie:
Jest Sobie kolumna A i w niej mamy Tego typu kody: AS 987 , BG 564 , AZ 450 itd około 200 wierszy.
Zależy mi na ułożeniu takiej funkcji która będzie zliczała tylko wybrane przeze mnie liczby , czyli np. 450, 987. Takich liczb chciałbym wyróżnić około 50.
Czy jest to możliwe? proszę o radę i jeżeli jest to wykonalne o jakąś przykładowo ułożoną funkcję.
Z góry dziękuje
Bartek
Open Office 3.1 Portable / Windows XP
Jan_J
Posty: 4557
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: zliczanie dopasowań do któregokolwiek wzorca z listy

Post autor: Jan_J »

Da się.
Najpierw umożliwiamy użycie wyrażeń regularnych w formułach: Narzędzia → Opcje → Calc → Oblicz
[x] Włącz wyrażenia regularne w formułach

Teraz załóżmy, że mamy w kolumnie A 1000 wierszy postaci AB 344, X 187, S 22 itp.
Liczby (lub szerzej: końcowe części kodów), które nas interesują wpiszmy do kolumny B. Dla ułatwienia wpisujmy teksty składające się z cyfr, a nie liczby, np. '11, '122, '344 itd. -- w przeciwnym razie będzie trzeba je konwertować na tekst w formułach wyszukiwania.

Jak zliczyć ile kodów w A1:A1000 kończy się liczbą z B1? Standardowo

Kod: Zaznacz cały

=LICZ.JEŻELI($A$1:$A$1000; ".* " & B1 & "$")
Drugi argument jest wyrażeniem, które mówi: najpierw cokolwiek, potem spacja, potem nasz kod i dalej już nic.

Jeżeli założymy, że mamy kody w B1:B10 i dla każdego z nich chcemy wykonać osobne zliczenie, robimy to albo kopiując powyższą formułę odpowiednią liczbę razy (nie pokażę jak, mam nadzieję, że to oczywistość), albo za pomocą składni rozszerzonej

Kod: Zaznacz cały

=LICZ.JEŻELI($A$1:$A$1000; ".* " & B1:B10 & "$")
tj. zatwierdzając ją przez <Ctrl+Shift+Enter>.

Jeżeli chcemy dostać tylko jedną liczbę, będącą łącznym wynikiem zliczenia, wystarczy nałożyć sumowanie na powyższą formułę. Czyli albo =suma(obszar z wynikami zliczania), albo bez marnowania miejsca na zbędne wyniki cząstkowe

Kod: Zaznacz cały

=SUMA(LICZ.JEŻELI($A$1:$A$1000; ".* " & B1:B10 & "$"))
z zatwierdzeniem jak poprzednio przez <Ctrl+Shift+Enter>.

Wszystkie podane przykłady wymagają, by kody w kolumnie B nie zawierały spacji (mogą natomiast zawierać dowolne inne znaki, niekoniecznie cyfry), i by dopasowywane do nich końcowe fragmenty kodów w kolumnie A były poprzedzone spacją (tj. AG 122 dobrze, AG122 źle).
JJ
LO (7.6) ∙ AOO (4.1) ∙ Python (3.11|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
bskladanek
Posty: 10
Rejestracja: czw maja 13, 2010 6:46 am

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: bskladanek »

super, dziękuje , dokładnie to czego potrzebowałem.
Widzę że dużo jeszcze mi zostało do nauczenia :D
Open Office 3.1 Portable / Windows XP
sega007
Posty: 2
Rejestracja: sob cze 30, 2012 11:34 am

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: sega007 »

Witam! Ponieważ debiutuje na tym forum, nie chcąc tworzyć nowego tematu to odświeżam ten. Jako, że jest to dopiero początek przygody z tym pakietem, więc nie zdążyłem jeszcze poznać funkcji, jakie można przypisać komórkom. Mam takie "schody" jak na obrazku. Dwie kolumny, na dole tych kolumn ich suma. Chodzi o to , by w prawej kolumnie na dole (50PLN)była suma wpisów "nie" z tej kolumny do których przypisana są wartości liczbowe z kolumny lewej.Czyli zależy mi na zsumowaniu kwot, które nie są zapłacone. Chyba jasno się wyraziłem. Z góry dziękuję za pomoc.
Załączniki
bez tytułu.JPG
bez tytułu.JPG (8.17 KiB) Przejrzano 42496 razy
OpenOffice 3.3 na Windows XP
Jan_J
Posty: 4557
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: Jan_J »

Klasyczne sumowanie warunkowe, bez tricków o jakich mowa w tym wątku

Kod: Zaznacz cały

=suma.jeżeli(obszar_statusu_rozliczeń; "nie"; obszar_kwot)
JJ
LO (7.6) ∙ AOO (4.1) ∙ Python (3.11|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
sega007
Posty: 2
Rejestracja: sob cze 30, 2012 11:34 am

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: sega007 »

Bardzo dziękuję za szybką pomoc i przepraszam za nie wstrzelenie się z moim wątkiem w ten temat, no ale jak napisałem to na razie "początki początku" z pakietem biurowym.
OpenOffice 3.3 na Windows XP
likidis
Posty: 5
Rejestracja: pt lip 06, 2012 11:56 am

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: likidis »

Witam, staram się ogarnąć temat ale nie bardzo mi to idzie.

Otóż dostałem plik excel'owski, w którym to jest zastosowana formuła:
1. SUMA.WARUNKÓW(''Przyjęcia i wydania''!E:E;''Przyjęcia i wydania''!D:D;"=przyjęcie";''Przyjęcia i wydania''!C:C;B2)
2. SUMA.WARUNKÓW(''Przyjęcia i wydania''!E:E;''Przyjęcia i wydania''!D:D;"=wydanie";''Przyjęcia i wydania''!C:C;B2)

Stety, w OO posypały się totalnie formuły i pokazuje coś takiego:
1. =sumifs($'Przyjęcia i wydania'.E$1:E$1048576;$'Przyjęcia i wydania'.D$1:D$1048576;"=przyjęcie";$'Przyjęcia i wydania'.C$1:C$1048576;B19)
2. =sumifs($'Przyjęcia i wydania'.E$1:E$1048576;$'Przyjęcia i wydania'.D$1:D$1048576;"=wydanie";$'Przyjęcia i wydania'.C$1:C$1048576;B20)


Jak naprawić, poprawić formuły w OO żeby działały?

Z góry dziękuję za odpowiedź.
OpenOffice 3.3 na Windows XP
Jan_J
Posty: 4557
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: Jan_J »

sumifs() czyli suma.warunków() to wynalazek wprowadzony do nowszych Excelów. W OOo/LO nie ma takiej funkcji.

Skorzystaj z funkcji suma() w składni tablicowej. Coś w rodzaju

Kod: Zaznacz cały

=suma(obszar_sumowania*(obszar_1_warunku=wartość_1)*(obszar_2_warunku=wartość_2))
z zatwierdzeniem przez <Ctrl+Shift+Enter>.
JJ
LO (7.6) ∙ AOO (4.1) ∙ Python (3.11|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
likidis
Posty: 5
Rejestracja: pt lip 06, 2012 11:56 am

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: likidis »

Wiem, że nie ma tego w OO, dlatego przeglądałem ten temat, podobne tematy i nie bardzo chce mi to wyjść, albo wywala mi wartość 0 albo wartość dużo za dużą, albo BŁĄD 508/504/511.

Generalnie chodzi o to, że w pliku ma być zapisywane wydawanie/przyjmowanie towarów.
kolumna E - ilość sztuk
kolumna D - przyjęcie/wydanie (wpisane słownie która z tych dwóch czynności)
kolumna C - nazwa towaru

W osobnej zakładce powinno samo wyliczać jaki jest łączny stan wydań/przyjęcia towaru.
OpenOffice 3.3 na Windows XP
Jan_J
Posty: 4557
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: Jan_J »

Z

Kod: Zaznacz cały

SUMA.WARUNKÓW(''Przyjęcia i wydania''!E:E;''Przyjęcia i wydania''!D:D;"=przyjęcie";''Przyjęcia i wydania''!C:C;B2)
przetłumaczyłbym na

Kod: Zaznacz cały

=SUMA('Przyjęcia i wydania'.E1:E10000*('Przyjęcia i wydania'.D1:D10000="przyjęcie")*('Przyjęcia i wydania'.C1:C10000=B2))
<Ctrl+Shift+Enter>
Zwróć uwagę na cudzysłowy: pojedyncze ' wokół nazwy arkusza i podwójne " (ale jeden znak) wokół stałych tekstowych. Po nazwie arkusza w adresie jest kropka, inaczej niż w Excelu.
Zamiast 10000 wpisz maksymalną liczbę wierszy jaką zamierzasz analizować (nie może być większa niż 1048576). A jeszcze lepiej: nazwij kolumny i używaj tych nazw w formułach. Zaznaczasz kolumnę i w linii adresowej bloku, tam gdzie widać np. a1:a400, wpisujesz nazwę jaką jej nadajesz. Wtedy formuła mogłaby wyglądać dosłownie tak:

Kod: Zaznacz cały

=SUMA(ilosc*(operacja="przyjęcie")*(towar=B2))
Tu już jesteśmy dość blisko SQL-owego

Kod: Zaznacz cały

select sum(ilosc) from terminal where operacja='przyjęcie' and towar=$towar$;
Jednak pełnej symetrii nie ma: SUM() wektorowe z warunkiem OR (LUB) wymaga nieco innego postępowania.

Słowa "przyjęcie" i "wydanie" też zapisałbym gdzieś na boku w roboczym obszarze, żeby miały swoje adresy: nie lubię wstawiać umownych stałych do kodu.
JJ
LO (7.6) ∙ AOO (4.1) ∙ Python (3.11|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
likidis
Posty: 5
Rejestracja: pt lip 06, 2012 11:56 am

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: likidis »

Działa :) Mam jednak pytanie jeszcze jedno do tej tabeli. Otóż kilka rzeczy zlicza mi całkiem inaczej niż powinno. Jak już wyłapałem to podczas filtrowania tabeli (tej z której brane są dane), to oprócz dobrych pól, pokazuje mi też te "nowe", które dziś dopisałem poprzez OO, mimo, że filtrowanie nie powinno ich puścić...
OpenOffice 3.3 na Windows XP
Jan_J
Posty: 4557
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: Jan_J »

Czy mówiąc “filtr” masz na myśli działanie *(...)*(...) z Twojej formuły?

Jakby zliczył mniej, to by nie było dziwne. Gdzieś możesz mieć literówkę w danych.

Ale więcej? sprawdź Narzędzia→Calc→Oblicz→Kryteria wyszukiwania odnoszą się do całych komórek, ale wg mojej wiedzy ta opcja nie wpływa na warunki opisane formułą tablicową.
JJ
LO (7.6) ∙ AOO (4.1) ∙ Python (3.11|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
likidis
Posty: 5
Rejestracja: pt lip 06, 2012 11:56 am

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: likidis »

Chodzi o zwykły filtr (sortowanie). Np daję żeby pokazywało mi tylko A które są wydane. Pokazuje mi ileś tam wierszy A które są wydane i kilka B. Przez to (sam nie wiem jakim cudem) wlicza mi te kilka B przy używaniu formuły którą podałeś wyżej do A, i w B przez to brakuje kilku egzemplarzy.
OpenOffice 3.3 na Windows XP
Jan_J
Posty: 4557
Rejestracja: pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: Jan_J »

likidis pisze:Chodzi o zwykły filtr (sortowanie). Np daję żeby pokazywało mi tylko A które są wydane. Pokazuje mi ileś tam wierszy A które są wydane i kilka B. Przez to (sam nie wiem jakim cudem) wlicza mi te kilka B przy używaniu formuły którą podałeś wyżej do A, i w B przez to brakuje kilku egzemplarzy.
Nie.
Sortowanie polega na zmianie kolejności elementów pewnego ciągu.
Filtrowanie polega na dopuszczeniu do dalszego etapu przetwarzania tylko elementów spełniających pewien warunek.

Pytałem o to, czy (a) Twój “filtr” jest realizowany przez funkcje filtra z arkusza (Dane→Filtr), czy (b) nazywasz tak część formuły decydującą o wyborze partii towaru przy sumowaniu, tzn. *(operacja=''')*(towar=B2).

Nie bardzo widzę, gdzie formuła tablicowa mogłaby “przepuścić zbyt wiele danych”. Spróbuj wyizolować na roboczej kopii arkusza minimalny zestaw źle filtrujących się danych.

A jeżeli dane są wcześniej przefiltrowane, to trzeba by się przyjrzeć konstrukcji tego filtra. Ale to osobny problem, na inny wątek.
Btw. Dane ukrywane autofiltrem są nadal przeliczane przez formuły. Może o to chodzi?
JJ
LO (7.6) ∙ AOO (4.1) ∙ Python (3.11|3.10) ∙ Unicode 15 ∙ LᴬTEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Rocky|CentOS)
likidis
Posty: 5
Rejestracja: pt lip 06, 2012 11:56 am

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: likidis »

Problem rozwiązany. Formuła nie przepuszcza zbyt dużej ilości danych, po prostu źle spojrzałem i nie wziąłem pod uwagę, że już dolicza produkty, które to dopisałem do listy.

Co do samego filtru to już wiem gdzie problem - jak pisało na forach zagranicznych dotyczących OO, to OO ma problem z plikami, które są pierwotnie excelowskie. Gdy przekopiowałem dane do totalnie nowej tabeli, robionej od początku w OO, filtrowanie działało dobrze.
OpenOffice 3.3 na Windows XP
kalka889
Posty: 1
Rejestracja: wt kwie 09, 2013 11:52 am

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: kalka889 »

Witam
Proszę Was o pomoc. Potrzebuję stworzyć formułę myślę, że będzie to Suma.Jeżeli, ale nie jestem pewna bo nie wychodzi:) Może opiszę sytuację:
Jeżeli w komórkach (F3;F200) jest tekst "osobiście" oraz jeśli w komórkach (M3;M200) jest literka "M" to aby w komórce O3 zliczało ich ilość takich komórek. Jeżeli Wartości są inne to aby nic się nie działo:)
Mam nadzieję, że w miarę jasno to napisałam. Bardzo proszę o pomoc, gdyż męczę się z tym już godzinę, a przyznam szczerze, nie znam się na tym za dobrze. Pozdrawiam i z góry dziękuję:)
OpenOffice 3.1
Husar
Posty: 203
Rejestracja: śr mar 06, 2013 3:48 am

Re: suma.jeżeli() kryteria na kilku kolumnach

Post autor: Husar »

właściwszą funkcją będzie licz.warunki(zakres1;kryteria1;zakres2;kryteria2;...)
czyli w Twoim przypadku w O3 wpisz:

Kod: Zaznacz cały

=licz.warunki(F3:F200;"osobiście";M3:M200;"m")
Pozdrawiam
Roman

Daj znać [SOLVED], kiedy Twój problem zostanie rozwiązany
LO 7.2.x/AOO 4.1.x. używane na Ubuntu 20.04LTS i M$ Windows 10
ODPOWIEDZ