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

#1 20. 3. 2019 16:44:49

bajtik
Člen
Registrace: 22. 10. 2017
Příspěvků: 24

Vyhledání řádku podle více parametrů - VYŘEŠENO

Dobrý den, píši s prosbou, mám tabulku nazvanou Katalog se sloupci řekněme

[EAN][ŠÍŘKA][VÝŠKA][DÉLKA][BARVA]

Do tohoto katalogu často vstupuji z jiných tabulek funkcí VLOOKUP a na základě EANu si nechám vypsat ostatní parametry. Nyní bych to potřeboval naopak - na základě šířky, výšky, délky a barvy vypsat EAN. Poradíte, prosím, jak to, pokud možno elegantně, zařídit?

Editoval bajtik (21. 3. 2019 15:00:57)

Offline

#2 20. 3. 2019 17:19:11

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

Re: Vyhledání řádku podle více parametrů - VYŘEŠENO

Zde je asi problém s možností více správných EANů. Mělo by to jít řešit maticovým vzorcem, ale musí se to "doladit". K tomu je potřeba ukázka. Nevýhodou je to, že výsledky budou rozházené v příslušných řádcích. Ty ale mohou být "blizko sebe".


     Další možností je najít COUNTIFS. Tím získáta počet položek vyhovujících podmínkám. Potom ručně kopírovat HLOOKUP vzorce s tím že používáme pomocný sloupec kde najdeme řádek prvního výskytu. Budou li dva nalezené - bude HLOOKUP startovat na dalším řádku. Takže vzorcem to jde na několik způsobů a může to být i pod sebou.


     Podobne by to šlo řešit pomocí IFS, nebo i jinými vzorci. V neposlední řadě jde o počet položek. Když jich bude moc (tisíce) tak je makro nejlepším řešením.


     Samostatným řešením je zmíněné makro které je nejrychlejším a nejsnadnějším na obsluhu. Je k tomu potřeba samozřejmě dobrý popis, nebo spíš ukázku.


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

#3 20. 3. 2019 17:37:43

bajtik
Člen
Registrace: 22. 10. 2017
Příspěvků: 24

Re: Vyhledání řádku podle více parametrů - VYŘEŠENO

Děkuji za reakci, šířka, výška, délka a barva je úplný popis výrobku, tedy každé kombinaci odpovídá pouze jeden jediný EAN. Jde mi o to, že obchodní oddělení (zatím) s EANy nepracuje, tedy mi při týdenní uzávěrce předají tabulku objednávek se sloupci [zákazník][šířka][výška][délka][barva][množství]


Zatímco sklad a expedice se orientuje podle EANů, tedy potřebuji dle šířky, výšky, délky a barvy dešifrovat EAN a do expedice předat jen [zákazník][ean][množství].


Rozhodně upřednostňuji řešení vzorcem.

Editoval bajtik (20. 3. 2019 17:40:56)

Offline

#4 20. 3. 2019 18:07:08

kabi
Člen
Registrace: 1. 6. 2017
Příspěvků: 119

Re: Vyhledání řádku podle více parametrů - VYŘEŠENO

pokud ty parametry jednoznačně identifikují výrobek, vytvořte si pomocný sloupec, kde všechny parametry zkombinujete, např. =šířka&"#"&výška&"#"&délka&"#"&barva a použijte standardně funkci vlookup pro dohledání eanu.

Offline

#5 20. 3. 2019 22:11:02

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

Re: Vyhledání řádku podle více parametrů - VYŘEŠENO

Zkuste SUMPRODUCT(sloupec1=kriterium1;sloupec2=krit2;sloupec3=krit3;sloupec4=krit4;sloupecEAN) Vysledkem bude suma vsech vyhovujicich cisel ean, ale pokud je vysledek jediny pak bude vysledkem prave to co potrebujete.


Zhruba takto: =SUMPRODUCT(data.$B$2:$B$30=A2;data.$C$2:$C$30=B2;data.$D$2:$D$30=C2;data.$E$2:$E$30=D2;data.$A$2:$A$30)

Samozřejmě jak bylo řečeno výše, dost záleží na množství dat, toto je použitelné max do několika málo tisíc. Přes 5000 už to bude asi docela náročné.

Editoval ludviktrnka (21. 3. 2019 08:06:12)


LibreOffice 6.2.

Offline

#6 21. 3. 2019 13:32:23

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

Re: Vyhledání řádku podle více parametrů - VYŘEŠENO

Tak jsem se dostal k velkému počtu podivností. Spíš nežli popisovat detaily problémů jsem vytvořil vzor který snad odpovídá jak bajtíkovu problému, tak řešení od kabiho i Ludvíka Trnky. Tady Vzorce vícenásobné volby.


     Našel jsem 3 podobná řešení - ale vždy s pomocným sloupcem. Celkem jsem byl překvapen chybou SUMIFS, nebo tím, že vzorce neragují na skládaná řešení stringů pro úseky. Dokonce mne vyděsil i vzorec INDIRECT. Mám to provedeno na W7(64) a nejnovější LO 6.2.0.3.
     Ještě jsem otestoval i dost matic, ale našel jsem jen taková řešení který vyplivnou pomocný sloupec. Takže bajtík si může vybrat asi ze tří různých řešení, ale problém se vzorci může být na bug. Já už toho mám dnes plné zuby.


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

#7 21. 3. 2019 14:11:46

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

Re: Vyhledání řádku podle více parametrů - VYŘEŠENO

Sumproduct funguje i bez pomocného sloupce a netváří se nijak problémově (na množství dat samozřejmě záleží).


LibreOffice 6.2.

Offline

#8 21. 3. 2019 15:00:32

bajtik
Člen
Registrace: 22. 10. 2017
Příspěvků: 24

Re: Vyhledání řádku podle více parametrů - VYŘEŠENO

Moc vám všem díky, kabiho řešení s pomocným sloupcem jsem někde našel už včera, před položením dotazu, ale popravdě se mi moc nelíbí. Hlavně fakt, že by pomocný sloupec musel být v katalogu hned první (před EANem), tudíž bych musel celý katalog o sloupec posunout a dnes už si z něj bere data tolik všemožných sešitů, že by mi trvalo týden ověřit, že všechno funguje tak jak má.


Řešení od Ludvíka mi funguje parádně, průběžně jej dneska přes den testuji, vždycky když mám chvilku, a zatím jsem nenarazil na žádné větší komplikace. Tedy Ludvíkovi děkuji dvojnásob, zdá se, že je vyřešeno. Katalog má asi 600 řádků, takže výkon není problém.


Moc jste mi pomohli, budu vás chválit, kudy budu chodit smile

Offline

#9 22. 3. 2019 10:47:29

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

Re: Vyhledání řádku podle více parametrů - VYŘEŠENO

ludviktrnka napsal(a)

Zkuste SUMPRODUCT(sloupec1=kriterium1;sloupec2=krit2;sloupec3=krit3;sloupec4=krit4;sloupecEAN) Vysledkem bude suma vsech vyhovujicich cisel ean, ale pokud je vysledek jediny pak bude vysledkem prave to co potrebujete.


Zhruba takto: =SUMPRODUCT(data.$B$2:$B$30=A2;data.$C$2:$C$30=B2;data.$D$2:$D$30=C2;data.$E$2:$E$30=D2;data.$A$2:$A$30)

Samozřejmě jak bylo řečeno výše, dost záleží na množství dat, toto je použitelné max do několika málo tisíc. Přes 5000 už to bude asi docela náročné.

     Mne by zajímala ukázka vzorce stačil by vzor pana Trnky. Nerozchodil jsem to a nechápu jak se to povedlo bajtíkovi. Jde o maličkosti? Nemyslím a stejně tak nejde o mnou vytvořené chyby při manipulaci se vzorci. Vzorec SUMPRODUKT má popis v nápovědě následující :

nápověda napsal(a)

=SUMPRODUCT(A1:B3;C1:D3) vrátí 397.
Výpočet: A1*C1 + B1*D1 + A2*C2 + B2*D2 + A3*C3 + B3*D3
Funkci SUMPRODUCT je možné použít pro výpočet skalárního součinu dvou vektorů.
Funkce SUMPRODUCT vrací jediné číslo, není třeba ji zadávat jako maticovou funkci.
Podrobnější vysvětlení nahoře.
Zkopíroval jsem přímo ukázanou matici v nápovědě a výsledek se potvrdil. Totéž jsem testoval v AOO a je tam úplně stejný popis. Ale AOO nemá zavedené funkce SUMIFS a mnoho dalších. Ani maticí jsem to nerozchodil.

     Zde se jedná o první ukázku vzorce jak pan Trnka uvádí. Níž upřesňuje vzorec :

=SUMPRODUCT(data.$B$2:$B$30=A2;data.$C$2:$C$30=B2;data.$D$2:$D$30=C2;data.$E$2:$E$30=D2;data.$A$2:$A$30)

. Nebyl jsem líný přesně nasimulovat tak aby to bylo shodné se vzorcem. Ale výsledek vyplivl jen nulu a po úprave (odkaz na hledané hodnoty) vypadl první řádek dat.
     Vím co je to za úpravu - takto se zadávaly matice ještě před změnami vzorců. Ale výsledek nevrací nic použitelného. Takže SUMPRODUKT slouží nyní ke zcela jinému účelu a po úpravě na kterou jsme si zvykli to dává nesmysl. Nechtěl jsem to takto napsat, ale když bajtík panu Trnkovi děkuje tak si připadám jako hňup. Uváděl jsem raději příklad se SUMIFS ale uvedl jsem, že jsem ten sešit postavil i pro demonstraci příkladu pana Trnky. Jen jsem to tam záměrně jako vzorec nenapsal.
     Je sice možné, že bajtík i pan Trnka mají staré instalace někde na verzi LO 5, nebo AOO kde by to bylo možné pozitivně testovat. Ale o tom celkem pochybuji - jen takovou možnost připouštím.


     Problém vzorců které používají součin, nebo součet (SUMPRODUKT, SUMIFS ap.) je v tom, že stejný výsledek mohou dát součiny nebo součty různých členů. Pro představu 3*8 = 6*4. Když se něco takového objeví v katalogu může to být fatální malér ztráta. Proto je k těmto vzorcům bezpodmínečně nutné postavit kontrolu - nejlépe pomocí COUNTIF.


Pro bajtíka:
     Pomocný sloupec před EANem vyžaduje pouze VLOOKUP. Řešení z druhého listu (sloupec) G může být třeba v posledním sloupci "AMJ". Může mít tisíce aktivních vzorců a nic se neděje (Poslaný vzor obsahuje 10x více řádků, nežli potřebujete Vy a navíc jsou v sešitě 2x). Stačí jenom podle potřeby vystříhnout buňky G1 a G2 - a ty vložit tam kam potřebujeme. Totéž lze udělat stejně jako jsem to nasimuloval v sešitě - tedy do sloupce AMI dát "=Ax" a do stejného řádku sloupce AMJ dát složený string podle "kabiho" + VLOOKUP podle doporučení. Vzorec se samozřejmě umístí na rozdíl od pomocných sloupců tam kde ho potřebujeme.


Přeji úspěch a nezlobte se - rád bych věděl jak se to podařilo rozchodit.


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

#10 22. 3. 2019 11:24:52

bajtik
Člen
Registrace: 22. 10. 2017
Příspěvků: 24

Re: Vyhledání řádku podle více parametrů - VYŘEŠENO

Mě to funguje takto https://uloz.to/!Gk48dTq1gYuT/sumproduct-ods v LO i AOO

Součet v případě duplicitního záznamu by byl problém, ale katalog udržuji dost svědomitě (jak jsem psal, bere si z něj data kdeco - vlastně veškerá automatizace od zpracování objednávek, přes nákup materiálu, výrobu, fakturaci až po expedici).

Offline

#11 22. 3. 2019 11:32:52

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

Re: Vyhledání řádku podle více parametrů - VYŘEŠENO

Výsledek nula vrátí funkce pokud je suma nula, tzn. nic nevyhovuje kritériím v prvních argumentech. Je to rozdíl oproti jiným běžným vyhledávacím funkcím kde je výsledkem #N/A


LibreOffice 6.2.

Offline

#12 22. 3. 2019 13:20:44

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

Re: Vyhledání řádku podle více parametrů - VYŘEŠENO

Jj - tak jsem našel ten problém. Stačilo abych natagoval úseky vedle sebe. Já jsem jako poslední úsek zadával čísla barev která jsou až za textovým tvarem - tím vznikla mezera. Po přepsání na textový tvar barvy to chodí. Tutéž chybu mel vzorec SUMIFS. Omlouvám se a díky.


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

#13 22. 3. 2019 15:23:16

kabi
Člen
Registrace: 1. 6. 2017
Příspěvků: 119

Re: Vyhledání řádku podle více parametrů - VYŘEŠENO

jenom dodám, že pro řešení od ludviktrnka, které bajtik použil, je nutné mít eany v číselném formátu. Nefungovalo by to s textovými formáty (např. EAN128 aj.). A jelikož i EAN13 může začínat nulami, je potřeba s tím počítat a nastavit správně formáty buněk.
V případě, že není vhodné použít vlookup, lze z toho kombinovaného pomocného sloupce (který lze umístit kamkoliv) za pomoci vzorce složeného z INDIRECT a MATCH zjistit odpovídající číslo řádku a získat hodnotu eanu.

Offline

Zápatí