suma.jeżeli() kryteria na kilku kolumnach

Użytkowanie arkusza kalkulacyjnego

suma.jeżeli() kryteria na kilku kolumnach

Postprzez arek » Pn lis 30, 2009 5:23 pm

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ć?
arek
 
Posty: 50
Dołączył(a): Wt cze 02, 2009 5:23 pm

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

Postprzez Jan_J » Pn lis 30, 2009 6:18 pm

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 6.2 ∙ AOO 4.1.7 ∙ Python (3.8|2.7) ∙ Unicode 12 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 4145
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

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

Postprzez arek » Pn lis 30, 2009 9:06 pm

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.
arek
 
Posty: 50
Dołączył(a): Wt cze 02, 2009 5:23 pm

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

Postprzez Jan_J » Wt gru 01, 2009 12:20 pm

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 6.2 ∙ AOO 4.1.7 ∙ Python (3.8|2.7) ∙ Unicode 12 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 4145
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

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

Postprzez mgostek » Pn wrz 05, 2011 2:58 pm

Ś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
mgostek
 
Posty: 2
Dołączył(a): Wt lip 14, 2009 9:55 pm

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

Postprzez bskladanek » Pt wrz 23, 2011 1:55 pm

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
bskladanek
 
Posty: 10
Dołączył(a): Cz maja 13, 2010 6:46 am

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

Postprzez Jan_J » Pt wrz 23, 2011 2:23 pm

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   Rozszerz widokZwiń widok
=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   Rozszerz widokZwiń widok
=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   Rozszerz widokZwiń widok
=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 6.2 ∙ AOO 4.1.7 ∙ Python (3.8|2.7) ∙ Unicode 12 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 4145
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

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

Postprzez bskladanek » Pn wrz 26, 2011 6:59 am

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
bskladanek
 
Posty: 10
Dołączył(a): Cz maja 13, 2010 6:46 am

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

Postprzez sega007 » So cze 30, 2012 12:03 pm

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) Przeglądane 38343 razy
OpenOffice 3.3 na Windows XP
sega007
 
Posty: 2
Dołączył(a): So cze 30, 2012 11:34 am

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

Postprzez Jan_J » So cze 30, 2012 4:35 pm

Klasyczne sumowanie warunkowe, bez tricków o jakich mowa w tym wątku
Kod: Zaznacz cały   Rozszerz widokZwiń widok
=suma.jeżeli(obszar_statusu_rozliczeń; "nie"; obszar_kwot)
JJ
LO 6.2 ∙ AOO 4.1.7 ∙ Python (3.8|2.7) ∙ Unicode 12 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 4145
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

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

Postprzez sega007 » So cze 30, 2012 10:23 pm

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
sega007
 
Posty: 2
Dołączył(a): So cze 30, 2012 11:34 am

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

Postprzez likidis » Pt lip 06, 2012 12:11 pm

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
likidis
 
Posty: 5
Dołączył(a): Pt lip 06, 2012 11:56 am

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

Postprzez Jan_J » Pt lip 06, 2012 12:51 pm

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   Rozszerz widokZwiń widok
=suma(obszar_sumowania*(obszar_1_warunku=wartość_1)*(obszar_2_warunku=wartość_2))
z zatwierdzeniem przez <Ctrl+Shift+Enter>.
JJ
LO 6.2 ∙ AOO 4.1.7 ∙ Python (3.8|2.7) ∙ Unicode 12 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 4145
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

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

Postprzez likidis » Pt lip 06, 2012 1:06 pm

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
likidis
 
Posty: 5
Dołączył(a): Pt lip 06, 2012 11:56 am

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

Postprzez Jan_J » Pt lip 06, 2012 1:20 pm

Z
Kod: Zaznacz cały   Rozszerz widokZwiń widok
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   Rozszerz widokZwiń widok
=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   Rozszerz widokZwiń widok
=SUMA(ilosc*(operacja="przyjęcie")*(towar=B2))

Tu już jesteśmy dość blisko SQL-owego
Kod: Zaznacz cały   Rozszerz widokZwiń widok
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 6.2 ∙ AOO 4.1.7 ∙ Python (3.8|2.7) ∙ Unicode 12 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 4145
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

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

Postprzez likidis » Pt lip 06, 2012 1:45 pm

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
likidis
 
Posty: 5
Dołączył(a): Pt lip 06, 2012 11:56 am

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

Postprzez Jan_J » Pt lip 06, 2012 1:49 pm

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 6.2 ∙ AOO 4.1.7 ∙ Python (3.8|2.7) ∙ Unicode 12 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 4145
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

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

Postprzez likidis » Pt lip 06, 2012 1:57 pm

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
likidis
 
Posty: 5
Dołączył(a): Pt lip 06, 2012 11:56 am

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

Postprzez Jan_J » Pt lip 06, 2012 2:52 pm

likidis napisał(a):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 6.2 ∙ AOO 4.1.7 ∙ Python (3.8|2.7) ∙ Unicode 12 ∙ LATEX 2ε ∙ XML ∙ Unix tools ∙ Linux (Fedora|CentOS|SUSE)
Jan_J
 
Posty: 4145
Dołączył(a): Pt maja 22, 2009 1:20 pm
Lokalizacja: Wrocław

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

Postprzez likidis » Pn lip 09, 2012 10:53 am

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
likidis
 
Posty: 5
Dołączył(a): Pt lip 06, 2012 11:56 am

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

Postprzez kalka889 » Wt kwi 09, 2013 12:01 pm

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
kalka889
 
Posty: 1
Dołączył(a): Wt kwi 09, 2013 11:52 am

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

Postprzez Husar » Wt kwi 09, 2013 8:37 pm

właściwszą funkcją będzie licz.warunki(zakres1;kryteria1;zakres2;kryteria2;...)
czyli w Twoim przypadku w O3 wpisz:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
=licz.warunki(F3:F200;"osobiście";M3:M200;"m")
Pozdrawiam
Roman

Daj znać [SOLVED], kiedy Twój problem zostanie rozwiązany
LO 6.1.x/AOO 4.1.x. używane na Ubuntu 18.04LTS i M$ Windows 10
Husar
 
Posty: 203
Dołączył(a): Śr mar 06, 2013 3:48 am


Powrót do Calc

Kto przegląda forum

Użytkownicy przeglądający ten dział: Brak zidentyfikowanych użytkowników i 1 gość