Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

kamilos0123 · 26. 5. 2017 09:17:55

Zdravím,

potřeboval bych pomoct se vzorcem, který by mi porovnal data ze sloupce A a B v Listu1 s daty ve sloupci A a B v Listu2, pokud by hodnoty seděly přiřadil by do sloupce C v Listu1 hodnoty ze sloupce C z Listu2.

Děkuji.

Editoval kamilos0123 (29. 5. 2017 09:56:14)

ludviktrnka · 26. 5. 2017 09:21:20

To budte muset upřesnit. Zkuste napsat konkrétní případ kdy se to vyhodnotí jako shoda a případ kdy se to vyhodnotí jako neshoda.

Třeba příklad shody jak si jej domýšlím: list1.A1 = list2.A5 AND list1.B1 = list2.B20

Editoval ludviktrnka (26. 5. 2017 09:41:31)

kamilos0123 · 26. 5. 2017 13:43:48

IF list1.A2 = list2.A9 AND list1.B2 = list2.B9 THEN list2.C9 WRITE list1.C2
(možná jsem napsal nesmysl, ale snad je to z toho pochopitelné)

neutr · 26. 5. 2017 13:59:27

IF(AND(list1.A2 = list2.A9; list1.B2 = list2.B9)=1;list2.C9;"Nic")

Tento vzorec bude tam kde ho potřebujete - tedy například v listu3, nebo i v listu2. Váš zápis vypadá spíš jako zápis scriptu. Sytaxe vzorců je jiná. Je to sice čitelné ale to Write u vzorců neexistuje. Vypadá to že vzorec má být právě v list1.C2.

ludviktrnka · 26. 5. 2017 14:36:17

Tak jo, znamená to tedy že znáte předem řádky ve kterých shodu posuzujete? To je hodně jednoduchý případ, myslel jsem že to bude složitější a sice, že předem nevíte ve kterém řádku shoda nastane. To co poslal NEUTR bude posuzovat řádek 2 s řádkem 9, řádek 3 s řádkem 10 atd.... Ale možná to je tedy tak správně, jak to chcete.

Níže uvádím vzorec nezávislý na řádcích, tzn. vyhledává shodu kdekoli ve sloupcích druhého listu. (Ale má to své mouchy, no kdyby to byla cesta kterou chcete řešit tak bych to nějak dodělal)

=IF(ISERROR(MATCH(List1.A2;$List2.$A$2:$A$100;0));"neshodaA";IF(List1.B2=INDEX($List2.$A$2:$C$100;MATCH(List1.A2;$List2.$A$2:$A$100;0);2);INDEX($List2.$A$2:$C$100;MATCH(List1.A2;$List2.$A$2:$A$100;0);3);"neshodaB"))

kamilos0123 · 26. 5. 2017 14:40:03

Jak jste psal p. Trnka - nevím, ve kterém řádku shoda nastane, proto jsem tam uvedl 2 různé řádky v listech, ale asi to nebylo jasné, omlouvám se tedy NEUTR za neúplné vysvětlení.

ludviktrnka · 26. 5. 2017 15:02:50

ok, je to trochu složitější, uvedený vzorec má nevýhodu v tom, že pokud se ve sloupci A opakují stejná data tak to nalezne vždy jen první hodnotu a vyhodnotit jako neshodu dle sloupce B ale existující shodu někde dál to může "přehlédnout". Pokud se tedy data opakují pak použijte následující postup. Do pomocného sloupce dejte v listu 1 (třeba sloupec X) tento vzorec a uložte trojhmatem ctrl+shift+enter (jde o maticový vzorec)

=MIN(IF(A2=$List2.$A$2:$A$999;IF(B2=$List2.$B$2:$B$999;ROW($List2.$C$2:$C$999);1000);1000))

potom do buňek sloupce C v listu 1 už dáte jednoduchý odkaz:

=INDEX($List2.$A$1:$C$1001;X2;3)

Ve sloupci X se vám objeví čísla řádků kde nastala shoda. Pokud shoda není objeví se 1000. Ve sloupci C se pak objeví hodnoty z listu2 sloupce C přičemž tam kde není shoda se objeví hodnota z řádku 1000 (tedy asi nula). Uvedený postup najde vždy první shodu, pokud by se měla hledat poslední shoda stačí místo min napsat max a místo 1000 napsat 1

EDIT: maticový vorec se množí roztažením myší za současného stisku klávesy CTRL, nebo prostě nakopírování ctrl+c/v

EDIT: do C1000 si můžete dát vorec ="" tím eliminujete tu nepříjemnou nulu

Editoval ludviktrnka (26. 5. 2017 15:25:40)

ludviktrnka · 26. 5. 2017 15:40:25

a ještě varianta bez pomocného sloupce, přímo do sloupce C v listu1:

=OFFSET($List2.$C$1;MIN(IF(A2=$List2.$A$2:$A$5;IF(B2=$List2.$B$2:$B$5;ROW($List2.$C$2:$C$5);1000);1000))-1;0)

... uložit trojhmatem ctrl+shift+enter a poté nakopírovat do sloupce

ludviktrnka · 26. 5. 2017 15:53:21

a do třetice varianta bez maticových vorců. Hurá :-)

=IFERROR(OFFSET($List2.$C$1;SUMPRODUCT($List2.$A$2:$A$1000=A2;$List2.$B$2:$B$1000=B2;ROW($List2.$C$2:$C$1000))-1;0);"")

(zde jen malá nevýhoda oproti výše uvedeným maticovým řešením. V případě více shod současně to dává nesmslný výsledek)

Editoval ludviktrnka (26. 5. 2017 15:55:07)

neutr · 26. 5. 2017 16:16:39

Ono to není zřejmé ani nyní. Je otázkou kolik hledání se bude provádět ze sloupce C listu1. Dál je otázkou zda se může vyskytnout nějaká shoda která by založila na zkřížení výsledků.

Existuje možnost že se vyskytne výsledek neshody - tedy že se nenajde relace v listu 2 která by odpovídala zadání z listu1? Zase pokud je "shod" více je možné vrátit různé výsledky (odkazy)?
Pokud by se nacházely "neshody" je nutné testovat Iserror. Pokud by existovaly z nějakého důvodu různé návratové hodnoty (redundace podmínek vyhledávání) - pak by existovalo více správných návratových odkazů. Ty by se musely testovat pomocí Countif.

Osobně myslím že bez správné ukázky se budeme bavit hodně dlouho akademicky o nesmyslech. Tedy já jsem tušil že to bude složitější, ale dotaz byl precizní. Takže jsem si myslel že autor ví přesně co potřebuje.

ludviktrnka · 26. 5. 2017 16:34:43

Ano je to typické. Málokdo si uvědomuje, že konkrétní řešení je spjato s konkrétními daty. Podle složitosti či jednoduchosti zdroje dat a taky dle jeho rozsahu se volí jednoduchá či složitá řešení. To by se mělo napsat někam tučnými písmeny hned na začátek diskuzního fóra.

kamilos0123 · 29. 5. 2017 09:15:50

dio, je to těžší než jsem si myslel, maticové vzorce slyším prvně, musel jsem googlit a stejně mi to moc nepomohlo.

Co jsem zapomněl uvést ale vy jste s tím počítali je že některé data se opakují.
Ano, opakují, jak v listu1 tak i v listu2.

Myslel jsem že mi bude stačit vzorec
=IF(ISERROR(MATCH(List1.A2;$List2.$A$2:$A$100;0));"neshodaA";IF(List1.B2=INDEX($List2.$A$2:$C$100;MATCH(List1.A2;$List2.$A$2:$A$100;0);2);INDEX($List2.$A$2:$C$100;MATCH(List1.A2;$List2.$A$2:$A$100;0);3);"neshodaB")) od p. Trnky.

Nestačí, i když je shoda ve sloupci A v obou listech, tak se mě vzorec snaží přesvědčit o opaku, je to pravděpodobně kvůli opakujícím se hodnotám nebo nečemu jinému.?

ludviktrnka · 29. 5. 2017 09:19:31

Ano je to kvůli opakujícím se hodnotám ve sloupci A (v listu2). Zkuste výše uvedenou třetí variantu.

=IFERROR(OFFSET($List2.$C$1;SUMPRODUCT($List2.$A$2:$A$1000=A2;$List2.$B$2:$B$1000=B2;ROW($List2.$C$2:$C$1000))-1;0);"")

kamilos0123 · 29. 5. 2017 09:55:17

Funguje, děkuji Vám oběma.

ludviktrnka · 29. 5. 2017 10:02:02

akorát POZOR! Pokud v listu 2 budou dva stejné řádky to znamená shoda sloupce A a sloupce B existuje více než jedenkrát, pak daný vzorec nevrátí chybu ale nesmyslný výsledek.

kamilos0123 · 29. 5. 2017 10:27:43

I když by se to v mém případě stát nemělo, nesmyslný výsledek se rozumí čemu? např. náhodná kombinace znaků?

ludviktrnka · 29. 5. 2017 10:33:31

vesledkem fukce sumproduct je číslo řádku v němž nastala shoda jak ve sloupci A tak ve sloupci B. Pokud by nastala shoda na více řádcích tak je výsledkem součet čísel těchto řádků. Takže např. při shodě na řádku č. 10 a řádku č. 15 je výsledek funkce číslo 25, a tím pádem hodnota z řádku č.25, což je samozřejmě zcela nesmyslné. Tuto nevýhodu eliminují výše uvedená řešení s maticovými funkcemi.

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

#1 26. 5. 2017 09:17:55

Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#2 26. 5. 2017 09:21:20

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#3 26. 5. 2017 13:43:48

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#4 26. 5. 2017 13:59:27

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#5 26. 5. 2017 14:36:17

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#6 26. 5. 2017 14:40:03

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#7 26. 5. 2017 15:02:50

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#8 26. 5. 2017 15:40:25

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#9 26. 5. 2017 15:53:21

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#10 26. 5. 2017 16:16:39

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#11 26. 5. 2017 16:34:43

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#12 29. 5. 2017 09:15:50

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#13 29. 5. 2017 09:19:31

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#14 29. 5. 2017 09:55:17

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#15 29. 5. 2017 10:02:02

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#16 29. 5. 2017 10:27:43

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

#17 29. 5. 2017 10:33:31

Re: Porovnávání 2 sloupců na 2 listech - VYŘEŠENO

Zápatí