Fórum pro uživatele kancelářského balíku OpenOffice | LibreOffice
 

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

kamilos0123
Člen
Registrace: 14. 1. 2016
Příspěvků: 23

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

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)


LibreOffice

Offline

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

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

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

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)


LibreOffice 5.2.2.2

Offline

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

kamilos0123
Člen
Registrace: 14. 1. 2016
Příspěvků: 23

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

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é)


LibreOffice

Offline

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

neutr
Člen
Registrace: 8. 3. 2007
Příspěvků: 2,678

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

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.


Pokud je Váš problém vyřešen, označte prosím svůj příspěvek za "VYŘEŠENÝ"
Zlepšíte tak orientaci na fóru při vyhledávání řešení problémů
JAK OZNAČIT TÉMA ZA VYŘEŠENÉ

Offline

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

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

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

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"))

LibreOffice 5.2.2.2

Offline

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

kamilos0123
Člen
Registrace: 14. 1. 2016
Příspěvků: 23

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

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í.


LibreOffice

Offline

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

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

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

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)


LibreOffice 5.2.2.2

Offline

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

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

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

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


LibreOffice 5.2.2.2

Offline

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

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

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

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)


LibreOffice 5.2.2.2

Offline

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

neutr
Člen
Registrace: 8. 3. 2007
Příspěvků: 2,678

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

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.


Pokud je Váš problém vyřešen, označte prosím svůj příspěvek za "VYŘEŠENÝ"
Zlepšíte tak orientaci na fóru při vyhledávání řešení problémů
JAK OZNAČIT TÉMA ZA VYŘEŠENÉ

Offline

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

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

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

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.


LibreOffice 5.2.2.2

Offline

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

kamilos0123
Člen
Registrace: 14. 1. 2016
Příspěvků: 23

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

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.?


LibreOffice

Offline

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

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

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

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);"")


LibreOffice 5.2.2.2

Offline

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

kamilos0123
Člen
Registrace: 14. 1. 2016
Příspěvků: 23

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

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


LibreOffice

Offline

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

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

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

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.


LibreOffice 5.2.2.2

Offline

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

kamilos0123
Člen
Registrace: 14. 1. 2016
Příspěvků: 23

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

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ů?


LibreOffice

Offline

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

ludviktrnka
Člen
Registrace: 9. 7. 2009
Příspěvků: 520

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

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.


LibreOffice 5.2.2.2

Offline

Zápatí