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

Milos_M

Člen

Registrace: 13. 12. 2009

Příspěvků: 4

porovnanie údajov v listoch

Prosím o radu:

Mám dva listy v Calc-u, v každom mám cca 5000 riadkov. Položky sú v riadkoch jednotlivých listov. List 1 aj list 2 majú spoločný minimálne jeden číslený údaj, umistnený v stĺpci B listu 1 aj dva. Ale list 1 nemusí mať všetky položky listu 2 a opačne. Riadky môžu byť v listoch navzájom v rozličnom poradí.

Potreboval by som poradiť akú funkciu použiť na porovnanie oboch listov podľa stĺpca B. Ak by bola zhoda - to ma nezaujíma, ale ak by sa číslený údaj v stĺpci listu 2 B nevyskytoval v liste 1, aby ma to nejakým spôsobom na túto situáciu upozornilo (zmena farby písma, alebo boldom), resp. inak.

hanus

Člen

Místo Olomouc

Registrace: 29. 11. 2006

Příspěvků: 573

Web

Re: porovnanie údajov v listoch

Je nutné, aby ten údaj ve sloupci B byl unikátní, tzn. nějaké ID které se na těch 5000 řádcích v rámci jednoho listu neopakuje. Pokud je toto splněno, můžete použít funkci VLOOKUP např. takto:
do nějakého pomocného sloupce v List1 vložte na první řádek vzorec
=IF(ISNA(VLOOKUP(B1;'List2'.$B$1:$B$5000;1;0));"CHYBÍ v Listu2";"")
a rozkopírujte ho po řádek 5000

do nějakého pomocného sloupce v List2 vložte na první řádek vzorec
=IF(ISNA(VLOOKUP(B1;'List1'.$B$1:$B$5000;1;0));"CHYBÍ v Listu1";"")
a rozkopírujte ho po řádek 5000

Místo toho textu "CHYBÍ v ..." bych si tam možná dal raději hodnotu 1 a udělal sumu nad tím pomocných sloupcem, čímž získáte počet chybějících ID ve druhém listu, tedy např.
=IF(ISNA(VLOOKUP(B1;'List1'.$B$1:$B$5000;1;0));1;"")

Více k fci VLOOKUP naleznete v nápovědě a i tady ve fóru se už několikrát probírala.

PS: Tu unikátnost hodnot ve sl.B si můžete ověřit např. pomocí COUNTIF:
=COUNTIF($B$1:$B$5000;B1)
více vizte nápověda.

Editoval hanus (14. 12. 2009 09:41:00)

lp.

Člen

Registrace: 24. 9. 2009

Příspěvků: 844

Re: porovnanie údajov v listoch

Použijte COUNTIF např. spolu s podmíněným formátem ve sloupci B List2

Buňka List2.B1:
Podmínka: Vzorec: =COUNTIF(List1.$B:$B;B1)=0
Styl podle libosti

Editoval lp. (14. 12. 2009 11:21:41)

lp.

Člen

Registrace: 24. 9. 2009

Příspěvků: 844

Re: porovnanie údajov v listoch

Počet chybějících hodnot dostanete jednoduše maticovým vzorcem:

{=SUM(COUNTIF(List1.$B1:$B$5000;List2.$B$1:INDEX(List2.$B$1:$B$5000;COUNTA(List2.$B$1:$B$5000)))=0)}

(Skládané úvozovky nevkládat, vzorec se vkládá pomocí ctrl-shift-enter)

"obyčejný" vzoreček vypadá takto:

=SUMPRODUCT(COUNTIF(List1.$B1:$B$5000;List2.$B$1:INDEX(List2.$B$1:$B$5000;COUNTA(List2.$B$1:$B$5000)))=0)

(stačí normálně vložit)

Pokud jsou mezi hodnotami v listu 2 prázdné buňky, bude lépe počítat tento vzorec
=SUMPRODUCT(COUNTIF(List1.$B1:$B$5000;List2.$B$1:$B$5000)=0)-COUNTBLANK(List2.$B$1:$B$5000)

Editoval lp. (14. 12. 2009 13:18:15)

Milos_M

Člen

Registrace: 13. 12. 2009

Příspěvků: 4

Re: porovnanie údajov v listoch

Ďakujem veľmi pekne všekým prispievateľom, veľmi ste mi pomohli.

Ešte by ma zaujímalo, či je možné vzorec upraviť tak, aby po prvotnom spárovaní hodôt v liste 1 a 2 na základe stĺpca B - porovnal aj druhotne obsah stĺpca K oboch listov. Ak by došlo k zmene v stĺpci K tak aby ma na to upozornil.

lp.

Člen

Registrace: 24. 9. 2009

Příspěvků: 844

Re: porovnanie údajov v listoch

Dotaz jsem nepochopil

Milos_M

Člen

Registrace: 13. 12. 2009

Příspěvků: 4

Re: porovnanie údajov v listoch

Skúsim to zjednodušiť:

Mám dva listy, každý po cca 5000 riadkov, v riadku dva stĺpce A,B.

Stĺpec A obsahuje unikátny údaj v liste 1 aj 2. List 1 nemusí mať všetky riadky listu 2 a naopak. Nemusia byť ani v tom istom poradí. 

Stĺpec B listov obsahuje tiež údaje, ktoré môžu, ale nemusia byť v jednotlivých listoch v danom stĺpci zhodné.

Otázka je, či sa dá spraviť to, že:
zoberie hodnotu napr A1 z listu 2, nájde daný riadok (s unikátnym A pre list 1 aj 2) v liste 1. Ak budú stĺpce B listu 1 aj listu 2 rovnaké, nespraví nič. Ak nie upozorní.

lp.

Člen

Registrace: 24. 9. 2009

Příspěvků: 844

Re: porovnanie údajov v listoch

Viz výše - to dělá funkce VLOOKUP.

hanus

Člen

Místo Olomouc

Registrace: 29. 11. 2006

Příspěvků: 573

Web

Re: porovnanie údajov v listoch

VLOOKUP umí kromě párování vracet i odpovídající údaj z libovolného jiného sloupce daného řádku, je k tomu potřeba přizpůsobit vzorec (prohledávanou oblast a číslo vráceného sloupce z této oblasti). Ve vašem případě tedy např.
=IF(ISNA(VLOOKUP(A2;$List1.$A$2:$B$5000;2;0));"CHYBÍ";IF(VLOOKUP(A2;$List1.$A$2:$B$5000;2;0)=B2;"OK";"LIŠÍ SE"))

pro lepší představu vizte
http://ooo.e-hanus.cz/parovani_hodnot.01.ods
je tam nastaveno i podmíněné formátování pro zvýrazňování odchylek.

Milos_M

Člen

Registrace: 13. 12. 2009

Příspěvků: 4

Re: porovnanie údajov v listoch

Perfektné, ďakujem hanus, presne to som potreboval.