Funkce IF a AND - VYŘEŠENO

binczech · 19. 2. 2012 19:56:28

Zdravím, použil jsem tuto funkcni:

=IF(AND(Zápasy!B2:B183="Benátky n Jizerou";Zápasy!G2:G183="Odehráno");+1;+0)

Co chci, aby mi to dělalo? Chci, aby mi přičetlo +1, když v nějaké buňce od B2 - B183 najde Benátky n Jizerou a zároveň v tom samém řádku musí být napsáno od G2 - G183 Odehráno. Pokud není, tak se přičte +0. Děkuji za pomoc!!!

lp. · 19. 2. 2012 20:59:24

Zkuste popsat lépe co vlastně chcete. Chcete přičíst 1, ale k čemu? Ve výrazu máte pole, ale není zřejmé, co se má s výsledkem stát. Pokud chcete někam zapsat 1 pokud hrály Benátky n.J., tak prostě zadejte vzorec:

=IF(AND(Zápasy!B2="Benátky n Jizerou";Zápasy!G2="Odehráno");+1;+0)

a zkopírujte ho do dalších 181 řádků.

binczech · 19. 2. 2012 21:26:32

Chci, aby mi započítavalo počet odehraných zápasů do buňky D2 na 1. listu, tudíž konkrétně pro Benátky. Já vím, jde tam vypisovat funkci if(and(+if(and(..., ale já těch kol mám moc, k tomu ještě musím mít funkce pro Vyhrané zápasy atd. Takže jestli by se dala udělat funkce, že by hledala v sloupci slovo Benátky n Jizerou a když by našla, tak v tom samém řádku by muselo být odehráno, když ano, tak se přičte +1, když ne, tak se přičte +0 .

lp. · 19. 2. 2012 21:37:21

Tak zkuste třeba

=SUMPRODUCT((Zápasy!B2:B183="Benátky n Jizerou");(Zápasy!G2:G183="Odehráno"))

(v zapsaném vzorci funkce AND vyhodnotí dohromady 1. i 2. podmínku. Protože nehrají jen Benátky, bude výsledek vždy FALSE a IF vrátí vždy 0.

AND v maticových vzorcích se nahrazuje násobením

=IF((Zápasy!B2:B183="Benátky n Jizerou")*(Zápasy!G2:G183="Odehráno");+1;+0)

V tomto případě by výsledkem bylo pole, pro dosažení výsledku be nutno IF "obalit" příslušnou funkcí

=SUM(IF((Zápasy!B2:B183="Benátky n Jizerou")*(Zápasy!G2:G183="Odehráno");+1;+0))

- vše zadává jako matice

Pokud si uvědomíte automatickou konverzi, zjistíte, že to IF je vlastně zbytečné

=SUM((Zápasy!B2:B183="Benátky n Jizerou")*(Zápasy!G2:G183="Odehráno"))

- matice - ctrl-shift-enter.

SUMPRODUCT vlastně dělá totéž, jen se nemusí zadávat jako matice)

binczech · 19. 2. 2012 21:44:31

Zkusil jsem vaše vzorce, ale nepomáhá, přestože mám v řádku Benátky n Jizerou a pár buněk vedle Odehráno, pořád mi v buňce, které to má sčítat ukazuje 0.

neutr · 19. 2. 2012 21:44:32

To je dost divný vzorec. Měly by tam být do podmínky "AND" vnořeny vyhledávací funkce. Třeba Lookup, Hlookup, Vlookup, Countif, nebo třeba Match. Také asi syntaxe není úplně košer. Navíc by tam mohly být značky dolaru (Alt+F4) pro pole ve kterém se hledá.
Mezitím naskákaly další příspěvky, ale problém je v tom, že musíte hledat ve stejném žádku "G" jako má "B". Ve sloupci "G" je asi víc stejných záznamů "odebráno" - to je ta chyba.

Editoval neutr (19. 2. 2012 21:57:47)

binczech · 19. 2. 2012 22:02:45

Prostě potřebuju, aby mi nějaká funkce hledala v sloupci B a sloupci E "Benátky n Jizerou", pokud je najde, aby zkontroloval, zda je v tom samém řádku slovo Odehráno, pokud ano, tak +1, pokud ne, tak +0

lp. · 19. 2. 2012 22:14:17

binczech napsal(a)

Zkusil jsem vaše vzorce, ale nepomáhá, přestože mám v řádku Benátky n Jizerou a pár buněk vedle Odehráno, pořád mi v buňce, které to má sčítat ukazuje 0.

Všechny vzorce jsou maticové (mimo SUMPRODUCT), vkládají se trojhmatem ctrl-shift-enter.
Správně zadaný vzorec se zobrazuje ve složených závorkách (ty se nevkládají).

Další možná chyba může být v přidaných mezerách v textu (nebo jiných neviditelných znacích).

Některé verze měly problém s konverzí logického výrazu na číslo, tam pomáhalo např. 1*(log. výraz).

neutr · 19. 2. 2012 22:19:17

Tohle skuste

=IF(INDIRECT(ADDRESS(MATCH("Benátky nad Jizerou";B2:B183;1);4;1;2))="Odebráno";1;0)

. Možná to budete muset adresovat od od B1 - ale je to jedno - například B1:B1000000.

Editoval neutr (19. 2. 2012 22:26:39)

lp. · 19. 2. 2012 22:27:22

neutr napsal(a)

To je dost divný vzorec.

SUMPRODUCT je normální funkce, ostatní jsou maticové.

neutr napsal(a)

Měly by tam být do podmínky "AND" vnořeny vyhledávací funkce. Třeba Lookup, Hlookup, Vlookup, Countif, nebo třeba Match.

Jediný COUNTIF by mohl pomoci

=COUNTIF(Zápasy!B2:B183 & Zápasy!G2:G183; "Benátky n JizerouOdehráno")

(Opět vložit maticově)

neutr napsal(a)

Také asi syntaxe není úplně košer. Navíc by tam mohly být značky dolaru (Alt+F4) pro pole ve kterém se hledá.

Dolary by tam být mohly, ale když se vzorec nekopíruje, tak tam být nemusí.

neutr napsal(a)

Ve sloupci "G" je asi víc stejných záznamů "odebráno" - to je ta chyba.

To není chyba, ale zřejmě vlastnost. Proto tam nejsou vhodné běžné vyhledávací funkce.

binczech · 19. 2. 2012 22:34:46

To lp. : Tak

 =SUMPRODUCT((B2:B183="Benátky n Jizerou");(G2:G183="Odehráno"))+SUMPRODUCT((E2:E183="Benátky n Jizerou");(G2:G183="Odehráno"))

pomohlo. v OpenOffice sčítá jak má, ale teď musím sehnat adekvátní náhradu v excellu, který je v google documents, protože v tom to mám, abych to mohl zveřejnit na webu.

lp. · 19. 2. 2012 22:39:00

neutr napsal(a)

Tohle skuste =IF(INDIRECT(ADDRESS(MATCH("Benátky nad Jizerou";B2:B183;1);4;1;2))="Odebráno";1;0). Možná to budete muset adresovat od od B1 - ale je to jedno - například B1:B1000000.

Vzorec MATCH("Benátky nad Jizerou";B2:B183;1) Pozor na "1". Předpokládá se setříděný seznam. Pokud setříděný není, tak nemusí funkce nic najít i když v seznamu Benátky budou.

lp. · 19. 2. 2012 22:43:45

binczech napsal(a)

To lp. : Tak =SUMPRODUCT((B2:B183="Benátky n Jizerou");(G2:G183="Odehráno"))+SUMPRODUCT((E2:E183="Benátky n Jizerou");(G2:G183="Odehráno")) pomohlo. v OpenOffice sčítá jak má, ale teď musím sehnat adekvátní náhradu v excellu, který je v google documents, protože v tom to mám, abych to mohl zveřejnit na webu.

Operaci OR nahradíte sčítáním

=SUMPRODUCT((B2:B183="Benátky n Jizerou")+(E2:E183="Benátky n Jizerou");(G2:G183="Odehráno"))

V excelu nefunguje přetypování

=SUMPRODUCT(1*(B2:B183="Benátky n Jizerou")+(E2:E183="Benátky n Jizerou");1*(G2:G183="Odehráno"))

by mělo fungovat v calcu i v excelu, v googledoc jsem to nezkoušel.

binczech · 19. 2. 2012 22:52:44

lp. napsal(a)

Operaci OR nahradíte sčítáním
=SUMPRODUCT((B2:B183="Benátky n Jizerou")+(E2:E183="Benátky n Jizerou");(G2:G183="Odehráno"))
V excelu nefunguje přetypování
=SUMPRODUCT(1*(B2:B183="Benátky n Jizerou")+(E2:E183="Benátky n Jizerou");1*(G2:G183="Odehráno"))
by mělo fungovat v calcu i v excelu, v googledoc jsem to nezkoušel.

Ano, máte pravdu, v Excelu mi to funguje, ale v google documents nikoliv.

neutr · 19. 2. 2012 22:57:46

Pro lp:
Ta jednička hraje úlohu trochu jinak. Hledá poslední buňku s tímto výrazem. Takže když to tam bude v seznamu 2x, tak vyhodí číslo 2. zápisu. Když tam bude "-1" tak to vezme obráceně - od prvého výskytu. Když by tam byl stejný řetězec vícekrát, tak se musí také rozšířit podmínky. Také by to mohlo být ošetřeno již uvedeným countif. Navíc "Match" má stejnou syntax i v tom Excelu. Jen asi Excek uvádí česky názvy funkcí (např. IF = Když, OR = nebo ap.)
Pole musí být od jedničky - takže od B1:Bx.

binczech · 19. 2. 2012 23:00:09

Tak to SUM/RPDUCT funguje perfektně jak v Office tak v Excelu, ale ještě přesvědčit google documents, aby mi to také bralo...

lp. · 19. 2. 2012 23:14:33

neutr napsal(a)

Pro neutr:
Ta jednička hraje úlohu trochu jinak. Hledá poslední buňku s tímto výrazem. Takže když to tam bude v seznamu 2x, tak vyhodí číslo 2. zápisu. Když tam bude "-1" tak to vezme obráceně - od prvého výskytu.

Máte pravdu, ale jen pokud máte setříděný seznam (vzestupně nebo sestupně). Pokud zvolíte tento parametr, funkce při hledání používá (nejspíš) nějakou metodu půlení intervalu. Při každém porovnávání volí další interval podle vztahu hledaného a porovnávaného prvku.

Zkuste najít Benátky v např. v seznamu

Praha
Tábor
Litvínov
Aš
Benátky

=MATCH("Benátky";A1:A5)

vrátí #N/A

(Častá chyba, metoda je velice rychlá, ale musí se dodržet podmínky užití.)

binczech · 19. 2. 2012 23:31:36

Tak nevěděl by někdo, jak přepsat

=SUMPRODUCT(1*(B2:B183="Benátky n Jizerou")+(E2:E183="Benátky n Jizerou");1*(G2:G183="Odehráno"))

, aby mi to bral i Google Documents?

lp. · 19. 2. 2012 23:45:05

Sumproduct lze zadat maticově i v calcu i v excelu a fungují stejně, jako když se zadají normálně.

V gogoledoces to zřejmě neplatí a musí se to zadat maticově:

=ArrayFormula(SUMPRODUCT((B2:B183="Benátky n Jizerou")+(E2:E183="Benátky n Jizerou");(G2:G183="Odehráno")))

i

=ArrayFormula(SUMPRODUCT(1*(B2:B183="Benátky n Jizerou")+(E2:E183="Benátky n Jizerou");1*(G2:G183="Odehráno")))

fungují (vzorce v googledocs).

Editoval lp. (19. 2. 2012 23:47:29)

neutr · 19. 2. 2012 23:52:42

Pro lp:
Jo souhlas, možná to tak je. Mně se spíš nelíbí, že Sumproduct vrací počet nalezených záznamů. Když budou dva stejné zápisy tak je zase sečte - hodí 2x větší numero. I když to funguje. Já bych se spíš přiklonil k tomu vyhledávat pomocí Match (nebo Hlookup ap.) tak, že mi Countif vrátí počet výskytů, a podle toho bych aktivoval počet "Match-ů" druhý by začínal polem od prvního, třetí na za součtem 1.+2. a td. Asi by to šlo udělat i na tlačítko - iterovat match. Mně však napadá, že "binczech" potřebuje nasimulovat něco v Ajaxu.

neutr · 20. 2. 2012 00:05:42

pro: binczech
Můžu se zeptat proč v Googledocs? Excel i Calc je možné dát například na nějaké úložiště, aby k tomu mohli také ostatní (nejlépe na Wiki - tam snad neřádí FBI). Calc je navíc zdarma takže mne napadá, že jde asi o těžbu dat z netu. Calc je možné přizpůsobit všem potřebám spojeným s rozpisem zápasů a je ryvhlejší. Googledocs umí navíc jen volnější přístup k netu - hlavně vyhledávání (zápasů?) na webu - a o tohle tady asi nejde. Zajímá mne důvod proč užít pomaljší možnost.

Editoval neutr (20. 2. 2012 00:09:47)

binczech · 20. 2. 2012 07:34:29

neutr napsal(a)

pro: binczech
Můžu se zeptat proč v Googledocs?...

Jelikož Google Documents mohu kdekoliv pohodlně upravit a navíc také ho můžu perfektně zveřejnit na webu. Mám to pouze k statistickým důvodům, o žádné těžby dat z netu se nejedná.

lp. napsal(a)

Sumproduct lze zadat maticově i v calcu i v excelu a fungují stejně, jako když se zadají normálně.
V gogoledoces to zřejmě neplatí a musí se to zadat maticově:
=ArrayFormula(SUMPRODUCT((B2:B183="Benátky n Jizerou")+(E2:E183="Benátky n Jizerou");(G2:G183="Odehráno")))
i
=ArrayFormula(SUMPRODUCT(1*(B2:B183="Benátky n Jizerou")+(E2:E183="Benátky n Jizerou");1*(G2:G183="Odehráno")))
fungují (vzorce v googledocs).

Děkuji mnohokrát, funguje perfektně. Zkusil jsem se osamostatnit, že bych zkusil poupravit funkci, aby mi počítala výhry. A konkrétně jen u Benátek. Zadal jsem výsledek Chomutov 1 3 Benátky, do stavu jsem samozřejmě napsal Odehráno. Započítalo výhru Benátkám. Pak jsem to přepsal na Chomutov 3 1 Benátky a opět započítalo výhru Benátkám. (Pozn. tu funkci mám jen u Benátek ve sloupečku V=výhry)

=ArrayFormula(SUMPRODUCT((Zápasy!B2:B183="Benátky n Jizerou")+(Zápasy!E2:E183="Benátky n Jizerou");(Zápasy!G2:G183="Odehráno");(Zápasy!C2:C183>Zápasy!D2:D183)+(Zápasy!D2:D183>Zápasy!C2:C183)))

Editoval binczech (20. 2. 2012 07:38:28)

neutr · 20. 2. 2012 08:42:11

Nevím o tom, že by to bylo rychlejší (mám to ověřeno - neř se to z netu načte - jeje to je doba), ale problematika úprav je asi dost velké mínus zejména proto, že podle toho co píšeš budeš dělat také další úpravy. Calc je možné uložit jako html, nebo PDF, a kvaltem odeslat pomocí FTP, takže také nevidím nějaký extra důvod pro Googledocs, zejména když ho nebudou upravovat externí uživatelé ap.
Já jsem se až dnes podíval na ty obrázky a došlo mi, že je to zápis celé ligy a ne jen jediného turnaje. Proto se skutečně hledaný řetězec najde vícekrát ve stejném sloupci (v případě jediného turnaje by se vyskytovalo nanejvých 1x v každém sloupci - tedy v "B" jako domácí a pak v "E" jako host). Pak ale dojde na to, že budeš potřebovat výpis všech utkání stejného družstva a těch bude víc. Tam už sumprodukt nepomůže - ten je bezpečný v podstatě jen pro hledání s negativním výsledkem - ne pro více stejných příležitostí. Dá se sice zjistit kolik je takových záznamů, které podmínce vyhovují, ale "udělat výpis utkání jednoho družstva s tímto mechanizmem nelze. Dojde na vyhledávací funkce.
Je teda možné si to usnadnit například pomocí autofiltru a zobrazí se všechny zápisy se stejným jménem pod sebou. Další možností je celé pole setřídit podle sloupce "domácí" a atd. Takže jde vlastně o to, co se bude po vyhledání dít dál. Je například možné spustit autofiltr (obecně filtr) pomocí makra a stejně tak třeba třídění. Lze také "skrýt" řádky, které nás nezajímají - což je vlastně obdoba autofiltru ap.
Ale budiž myslím si, že "lp" zabodoval a Ty bys měl téma označit jako "VYŘEŠENÉ".

binczech · 20. 2. 2012 08:50:15

Možná jsem zapomněl poznamenat, že tuto tabulku budou upravovat i další lidé. Nicméně věděl by někdo, kde jsem udělal v mém posledním příspěvku chybu?

neutr · 20. 2. 2012 08:58:09

Teď tomu zase nerozumím - nechodí to tak jsk jsi napsal?
Pokud to budou upravovat další lidé, tak to chce uložit celý Calc stejně jako obrázky. Externisté si to stáhnou, upraví a vloží zpět. Tak jako tak to vychází nastejno. Calc se dá záheslovat, aby úpravy dělali jen oprávnění, nebo lze na na některých "úložištích (jako je uložto.cz)" povolit určité skupině editorů.

Editoval neutr (20. 2. 2012 08:59:13)

OpenOffice.cz - web věnovaný kancelářským balíkům OpenOffice.org

#1 19. 2. 2012 19:56:28

Funkce IF a AND - VYŘEŠENO

#2 19. 2. 2012 20:59:24

Re: Funkce IF a AND - VYŘEŠENO

#3 19. 2. 2012 21:26:32

Re: Funkce IF a AND - VYŘEŠENO

#4 19. 2. 2012 21:37:21

Re: Funkce IF a AND - VYŘEŠENO

#5 19. 2. 2012 21:44:31

Re: Funkce IF a AND - VYŘEŠENO

#6 19. 2. 2012 21:44:32

Re: Funkce IF a AND - VYŘEŠENO

#7 19. 2. 2012 22:02:45

Re: Funkce IF a AND - VYŘEŠENO

#8 19. 2. 2012 22:14:17

Re: Funkce IF a AND - VYŘEŠENO

#9 19. 2. 2012 22:19:17

Re: Funkce IF a AND - VYŘEŠENO

#10 19. 2. 2012 22:27:22

Re: Funkce IF a AND - VYŘEŠENO

#11 19. 2. 2012 22:34:46

Re: Funkce IF a AND - VYŘEŠENO

#12 19. 2. 2012 22:39:00

Re: Funkce IF a AND - VYŘEŠENO

#13 19. 2. 2012 22:43:45

Re: Funkce IF a AND - VYŘEŠENO

#14 19. 2. 2012 22:52:44

Re: Funkce IF a AND - VYŘEŠENO

#15 19. 2. 2012 22:57:46

Re: Funkce IF a AND - VYŘEŠENO

#16 19. 2. 2012 23:00:09

Re: Funkce IF a AND - VYŘEŠENO

#17 19. 2. 2012 23:14:33

Re: Funkce IF a AND - VYŘEŠENO

#18 19. 2. 2012 23:31:36

Re: Funkce IF a AND - VYŘEŠENO

#19 19. 2. 2012 23:45:05

Re: Funkce IF a AND - VYŘEŠENO

#20 19. 2. 2012 23:52:42

Re: Funkce IF a AND - VYŘEŠENO

#21 20. 2. 2012 00:05:42

Re: Funkce IF a AND - VYŘEŠENO

#22 20. 2. 2012 07:34:29

Re: Funkce IF a AND - VYŘEŠENO

#23 20. 2. 2012 08:42:11

Re: Funkce IF a AND - VYŘEŠENO

#24 20. 2. 2012 08:50:15

Re: Funkce IF a AND - VYŘEŠENO

#25 20. 2. 2012 08:58:09

Re: Funkce IF a AND - VYŘEŠENO

Zápatí