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

#1 18. 5. 2019 14:01:34

realphoto
Člen
Registrace: 4. 6. 2010
Příspěvků: 34

MATCH kopírování hodnoty z jiného listu

Nefunguje mi vzoreček, co mám špatně?
Jsem v Calcu úplná lama, teprve zjišťuji, co všechno umí.

=INDEX(kosmas.B10:B100;MATCH(B799;kosmas.H1:H100;0);1)

Mám jeden list ("kosmas") s hlášením o prodejích knih, na něm potřebuji vyhledat název knihy a vložit počet prodaných kusů do příslušné buňky v listu s přehledy prodejů.

Šlo by to i mezi sešity? Jak by pak vypadal vzoreček s kopírováním ze sešitu xxx.xls a listu yyy?

Díky!

Offline

#2 18. 5. 2019 14:32:17

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

Re: MATCH kopírování hodnoty z jiného listu

vzorec máte dobře ačkoli posun sloupců H1:100 a B10:100 je poněkud neobvyklý, není v tom chyba? nemá být H1:H100  a B1:B100 ?


Jinak na externí soubor odkážete prostě adresou =INDEX('file:///C:/kosmas.ods'#$kosmas.B1:B100;MATCH(B799;'file:///C:/kosmas.ods'#$kosmas.H1:H100;0);1)


Když otevřete oba soubory současně, můžete s tím pracovat skoro jak s druhým listem.


LibreOffice 5.4.

Offline

#3 18. 5. 2019 16:21:12

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

Re: MATCH kopírování hodnoty z jiného listu

realphoto napsal(a)

Nefunguje mi vzoreček, ........
=INDEX(kosmas.B10:B100;MATCH(B799;kosmas.H1:H100;0);1)

A) Mám jeden list ("kosmas") s hlášením o prodejích knih, na něm potřebuji vyhledat název knihy a vložit počet prodaných kusů do příslušné buňky v listu s přehledy prodejů.

B) Šlo by to i mezi sešity? Jak by pak vypadal vzoreček s kopírováním ze sešitu xxx.xls a listu yyy?

Díky!

     Celkem pochybuji o tom co se má vrátit pomocí Vašeho vzorce. Jde o Základ "INDEX" který testuje viz nápověda následující :

INDEX

INDEX vrací podoblast specifikovanou čísly řádků a sloupců nebo volitelným indexem oblasti. V závislosti na obsahu INDEX vrací odkaz nebo obsah.
Syntaxe

INDEX(Odkaz; Řádek; Sloupec; Oblast)

Odkaz je přímý odkaz nebo odkaz na pojmenovanou oblast. Pokud se rozhodnete odkazovat na vícenásobnou oblast, musíte odkaz na ní nebo její název uzavřít do závorek.

Řádek (nepovinné) určuje řádek odkazované oblasti, pro který mají být vráceny hodnoty. Pokud je nastavena hodnota nula (žádný určitý řádek), jsou vráceny všechny odkazované řádky.

Sloupec (nepovinné) určuje sloupec odkazované oblasti, pro který mají být vráceny hodnoty. Pokud je nastavena hodnota nula (žádný určitý sloupec), jsou vráceny všechny odkazované sloupce.

Oblast (nepovinné) určuje číslo podoblasti, pokud je odkazováno na vícenásobnou oblast.

     Potom rozborem vzorce dojdeme k žádanému výsledku :
1. =INDEX(kosmas.B10:B100; .. Bez pochyby vracíte oblast ta je podle nejnovější nápovědy určena pouze pojmenovaným úsekem ale vím že "postaru" stačilo zadání tak jak máte, jen s tím že před jménem sešitu musí být dola. Ještě před tím stačilo uvést list do jednoduchých či dvojitých uvozovek. Tohle mi už ne W7 LO 6.2.2.2 (x64) nefunguje správně - proto to asi není uvedeno.
2. MATCH(B799;kosmas.H1:H100;0) ... Match vrátí relativní polohu prvku v poli, který se shoduje se zadanou hodnotou. Funkce vrací číselnou pozici hodnoty nalezené v vyhledávací_pole. Tedy v podstatě jde o číslo pořadí v seznamu hodnot a používáme ho například pro určení poředí z tabulky výsledků které nejsou (nemohou být) seřazeny.
3. ;1) ..... To je pokračování vzorce "INDEX."
     Připadá mi, že by vzorec měl vrátit například tituly od nějlepší k nejhorší prodejnosti, nebo skladovou zásobu a podobně. To by šlo řešit jinak. celkem ale pochybuji o tomto :

MATCHB799;...). To znamená, že v jiném (prvním) listu máte ve sloupci B seznam titulů shodný se sloupcem B v listu "kosmas". Tedy v podstatě jde o seřazení obsahu z listu "kosmas" typu TOP 100 protože jak je vidět sloupec B má nejméně 789 položek - vrátit se má jen 100. Na to by měl stačit "MATCH", nebo možná RANK (je jich více podle toho jak přidělují stejným hodnotám pořadí ap). Proto celkem váhám co a proč se má vrátit.
     Potřeba výše popsaná se dá řešit například konstrukcí ze samostatného vzorce SMALL, nebo LARGE podle toho jak potřebujete řadit. Určitě by mohlo být uvedeno více správných konstrukcí. Například pomocí SUMIF a další různé konstrukce.
     Další problematikou je možnost výskytu stejně validních položek podle prodeje. Tady jde o možnost zadat pořadí o které se dělí více titulů (zajistí RANK).
___________________________________________________________________________________


Načítání z externího zdroje lze například vložením listu z uvedeného externího zdroje, nebo jen pomocí DDE vložit oblast a podobně OLE. Moc bych nedoporučoval testovat něco jiného. Vzorce z Calcu mohou narazit na problém v Excelu. To bych doporučil spíš makro.
     Nejlepší cestou je tedy vložení listu a pak jde o řešení ve stejném sešitu. Ale ve skutečnosti může jít o potřeby kterým takové řešení nevyhovuje.


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

Offline

#4 18. 5. 2019 17:04:51

realphoto
Člen
Registrace: 4. 6. 2010
Příspěvků: 34

Re: MATCH kopírování hodnoty z jiného listu

ludviktrnka:
Ano, to byla chyba, opraveno, ale stejně vrací #N/A, ačkoli na listu "kosmas" je v buňce b11 stejný název knihy a v buňce h11 číslo 1. To tam právě chci dostat...

Offline

#5 18. 5. 2019 17:06:18

realphoto
Člen
Registrace: 4. 6. 2010
Příspěvků: 34

Re: MATCH kopírování hodnoty z jiného listu

Doplnění:
V b11 stejný jako v b799 na druhém listu.

=INDEX($kosmas.B1:B100;MATCH(B799;$kosmas.H1:H100;0);1)

Offline

#6 18. 5. 2019 18:01:58

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

Re: MATCH kopírování hodnoty z jiného listu

… jo jenže vy hledáte ve sloupci H. musíte ve vašem vzorci přehodit B a H Ale pokud chcete hledat takto doporučuji tento vzorec =VLOOKUP(B799;$Kosmas.B1:H100;7;0)


LibreOffice 5.4.

Offline

#7 18. 5. 2019 18:27:53

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

Re: MATCH kopírování hodnoty z jiného listu

Myslím že by autor realphoto měl popsat jak to má fungovat. Myslím si že v tom listu kde chce vzorce použít (jiný nežli "kosmas") potřebuje zadávat tituly možná jednotlivě proto, že to jsou již nějak vyhodnocené výstupy - například v B11 bude vyhodnocení "=MAX($kosmas.B1:B100) což je například stejný titul v různých prodejnách. Takže myslím že by pomohlo vysvětlit co se má podle čeho hledat.
     Problém určení úseku od B10 do B799 může fungovat v určitém případě správně. Jde jen o to, že první řádek úseku se počítá od řádku 10 = první řádek. Proto je výhodnější určovat úsek od prvního řádku - potom čísla návratu sedí s čísly řádků. To také vždy nejde a je možné jednoduše ve výstupu vynechané řádky připočítávat.


     Nedovolím si moc navrhovat když vím že to může být mimo mísu. Vždy mne při tom něco překvapí i když si myslím že to chápu správně. Například jeden autor popisoval určitý problém, já fabuluji co by to mohlo být, šlo obejít a podobně a pak se ukáže jednoduchá věc - Calc je registrovanou databází. Tohle mne opravdu nenapadlo. Takže ono jde o korektní popisy a tady vidím že se nejspíš na konci ukáže, že autor potřebuje něco úplně jiného. Stává se to snad u každého druhého dotazu.


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

Offline

#8 19. 5. 2019 07:20:04

realphoto
Člen
Registrace: 4. 6. 2010
Příspěvků: 34

Re: MATCH kopírování hodnoty z jiného listu

Na listu "tržby" má každý titul tři řádky - různé distributory, a 24 kolonek - 12 pro počet kusů a z toho se vedle počítá utržená částka. Já dostanu hlášení v Excelu, vložím ho na list "kosmas" a potřebuju, aby si každá kniha našla sama sebe a počet kusů, který se v daném měsíci prodal.

ludviktrnka: Díky! Takhle to funguje. Ale proč musí být rozsah B:H, když hledané hodnoty jsou stejně jen ve sloupci H?

Offline

#9 19. 5. 2019 07:44:16

realphoto
Člen
Registrace: 4. 6. 2010
Příspěvků: 34

Re: MATCH kopírování hodnoty z jiného listu

Oprava: 24 sloupců.

Offline

#10 19. 5. 2019 11:24:59

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

Re: MATCH kopírování hodnoty z jiného listu

realphoto napsal(a)

ludviktrnka: Díky! Takhle to funguje. Ale proč musí být rozsah B:H, když hledané hodnoty jsou stejně jen ve sloupci H?

     To je jednoduché u VLOOKUP (stejně HLOOKUP ap.) se určuje jako "relativní úsek" například B1:H799. Čísla určují že sloupec B je první sloupec (relativního úseku) a H je tuším osmý. Jde tedy o relativní počítání sloupců.
     Je pravidlem, že se vyhledává podle sloupce který je nalevo od sloupce hodnot které se mají vrátit. Ještě si dejte pozor na volitelné parametry. Tam se určuje zda je vyhledávací úsek seřazen vzestupně, sestupně, nebo nikoliv. Seřazené úseky jsou prohledávány rychleji. Také se nastavuje zda přesně daná hodnota, nebo nejbližší vyšší, nižší.

realphoto #8 napsal(a)

Na listu "tržby" má každý titul tři řádky - různé distributory, a 24 kolonek (opraveno na sloupce) - 12 pro počet kusů a z toho se vedle počítá utržená částka. Já dostanu hlášení v Excelu, vložím ho na list "kosmas" a potřebuju, aby si každá kniha našla sama sebe a počet kusů, který se v daném měsíci prodal.

     Prosím o vzorovou ukázku s případným popisem. Data si můžete vymyslet, ale mělo by to charakterem i strukturou být shodné se skutečným sešitem. Postněte to pomocí "uložto.cz", nebo něčím podobným. Také by chtělo upřesnit zda seznamy jsou měněny ve smyslu počtů položek. Bylo by dobré kdyby na listu "tržby" byl odkaz u jednotlivých vazeb - kam se sahá do listu "kosmas". Jde o rozsahy - zda celý sloupec, nebo je tam sloupců více.
     Nyní například nevím zda na list "tržby" je pod položku jednoho titulu potřeba sečíst výsledky všech distributorů (prodejců) do jediné položky. Zatím tomu tak rozumím a myslím že potřebujete funkci SUMIF(S) nebo i COUNTIF(S).


PS:
    Funkce VLOOKUP najde jen první výskyt položky. V jiném případě by to musel být maticový vzorec.

Editoval neutr (19. 5. 2019 11:42:16)


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

Offline

#11 19. 5. 2019 13:47:26

realphoto
Člen
Registrace: 4. 6. 2010
Příspěvků: 34

Re: MATCH kopírování hodnoty z jiného listu

neutr:
Nyní například nevím zda na list "tržby" je pod položku jednoho titulu potřeba sečíst výsledky všech distributorů (prodejců) do jediné položky. Zatím tomu tak rozumím a myslím že potřebujete funkci SUMIF(S) nebo i COUNTIF(S).
NE, každý distributor je zvláštní řádek, nic se nesčítá. Potřebuji to jen vyhledat podle názvu knihy a přenést počet prodaných kusů.

Offline

#12 19. 5. 2019 14:22:30

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

Re: MATCH kopírování hodnoty z jiného listu

Takže podmínka zní 1. distributor 2. titul a načíst nejprve počet a potom peníze?
    Toto řešení by vyžadovalo distributory ve stejném sloupci a funkci COUNTIFS ale jinak bez problému. Jenže nemám představu jak to vypadá když byste měl různé distributory v různých sloupcích (podle popisu 12 sloupců). Potom by každý titul byl na jednom řádku sloupce B. V takovém případě by stačilo hledat pomocí SUMIF(1. sloupec) + SUMIF(2. sloupec) + + + SUMIF(12. sloupec).
    Pokud by se vyskytoval každý titul ve sloupci B unikátně šel by použít jak VLOOKUP, tak INDEX v řetězení součtu z 12-ti sloupců (dvakrát - pro počet a pro peníze). jednoduše by se našel řádek kde titul je. Potom by to vzal i vzorec OFFSET, nebo i obecná konstrukce "buňka(1.sloupec)+ buňka(2. sloupec) a zřejmě také IF nebo mnoho jiných konstrukcí.


    Právě možnost že to bude jinbak mne vedla k napsání ukázky. V opůvodním vzorci byl uveden načítací řádek 799 (zřejmě ze sešitu tržby). Tam by mělo být "jen" 12 x 3 řádků - proto start na B10 a konec na B46. Proto řádky "takhle" také není správná představa. Zbývá možnost že řádek 799 je na listu "kosmas" a potom je tam stejný titul uveden vícekrát. Ale řádek 799 nebyl signován jiným listem. Proto bych raději viděl co tím myslíte.
    Pravděpodobně jde o 12 titulů a mnoho distributorů se stejným sortimentem - potom jednoznačně SUMIFS. Ale fakt si to nepředstavím.

Editoval neutr (19. 5. 2019 14:25:45)


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

Offline

#13 19. 5. 2019 14:37:55

realphoto
Člen
Registrace: 4. 6. 2010
Příspěvků: 34

Re: MATCH kopírování hodnoty z jiného listu

Psal jsem tři distributoři tři řádky, a ve sloupcích jsou pak vždy 1) počet prodaných kusů za daný měsíc" a "částka". Takže celý rok v jednom řádku.

Offline

#14 22. 5. 2019 09:59:57

lp.
Člen
Registrace: 24. 9. 2009
Příspěvků: 842

Re: MATCH kopírování hodnoty z jiného listu

Nebyla by lepší kontingenční tabulka?

Offline

#15 22. 5. 2019 10:31:35

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

Re: MATCH kopírování hodnoty z jiného listu

realphoto napsal(a)

Psal jsem tři distributoři tři řádky, a ve sloupcích jsou pak vždy 1) počet prodaných kusů za daný měsíc" a "částka". Takže celý rok v jednom řádku.

     No to je přesně ono. Pochopil jsem že tři řádky (název titulu, počet kusů, peníze). nepochopil jsem kolik je distributorů. Mohlo mne napadnout že 12 sloupců = 12 měsíců. Proto jsem to chtěl vidět.


     
     Nezlobte stále ale jeden postnutý vzor by nám ušetřil desítku dotazů. Nyní například nevím jestli má mít hlavní list 12 x 3 řádky, nebo jestli to má být také do 12-ti sloupců a nebo vše dohromady leden až prosinec? A má to být pro každý titul extra?
     Nyní mám představu, že na listu "kosmas" jsou ve sloupci B 3x stejné tituly pod sebou podle distributorů.

realphoto 19. 5. 2019 13:47:26 napsal(a)

Na listu "tržby" má každý titul tři řádky - různé distributory ??? z toho mám pochopit že je každý titul 3x bezprostředně opakovaný za sebou, nebo má jeden distributor svůj sloupec titulů???, a 24 kolonek - 12 pro počet kusů a z toho se vedle počítá utržená částka. Já dostanu hlášení v Excelu, vložím ho na list "kosmas" a potřebuju, aby si každá kniha našla sama sebe a počet kusů, který se v daném měsíci prodal.

realphoto 19. 5. 2019 07:20:04 napsal(a)

....Já dostanu hlášení v Excelu, vložím ho na list "kosmas" a potřebuju, aby si každá kniha našla sama sebe a počet kusů, který se v daném měsíci prodal.


     Já nevím co si myslet. Potřebujete na listu tržby jiné seřazení výstupů popřípadě v nějaké kumulaci za měsíce nebo co se má zjistit když se nic nesčítá? Fakt nevím co chcete udělat.

Editoval neutr (22. 5. 2019 10:39:18)


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

Offline

#16 22. 5. 2019 11:36:31

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

Re: MATCH kopírování hodnoty z jiného listu

realphoto napsal(a)

Ale proč musí být rozsah B:H, když hledané hodnoty jsou stejně jen ve sloupci H?

K rozsahu: V prvním sloupci (B) je vyhledávaná hodnota (nejspíše název knihy), ve sloupci H je pak hodnota která je výsledkem tohoto vzorce, takže se zjednodušeně zadává celý rozsah (může být i libovolně větší např. B:Z, výsledek vzorce bude stejný. Změnou čísla ve třetím atributu pak vzorec vrací hodnoty z jiných sloupců, takže vzorec se snadno modifikuje v případě potřeby.


LibreOffice 5.4.

Offline

#17 22. 5. 2019 18:47:44

realphoto
Člen
Registrace: 4. 6. 2010
Příspěvků: 34

Re: MATCH kopírování hodnoty z jiného listu

A dá se zadat i vyhledávání pouze konkrétních znaků? Kniha se totiž někdy nejmenuje u všech úplně stejně, ale třeba u "Domácí marmelády od Hanky" vím, že "marme" neobsahuje stopro žádný jiný titul a bude to ve všech hlášeních stejné.

Offline

#18 23. 5. 2019 23:28:40

lp.
Člen
Registrace: 24. 9. 2009
Příspěvků: 842

Re: MATCH kopírování hodnoty z jiného listu

Dá. Podívejte se na regulární výrazy ve funkcích. Nedoporučuji.

Ostatně soudím, že
1) kontingeční tabulky by vyřešila Vaše problémy několika kliky.
2) Pokud se sám považujete za lamu, tak vzorce přes více souborů nejsou dobrý nápad.

Doporučení:
1) Začněte s uspořádáním dat. Nejlépe na jeden list jednoho sešitu. Maker na takový přesun najdete mraky.
2) Srovnejte si kvalitu dat. Opravte nebo připojte základní název titulu, třeba i pomocí regulárních výrazů.
3) Přehledy generujte pomocí kontingenční tabulky.

Offline

#19 1. 6. 2019 11:48:11

realphoto
Člen
Registrace: 4. 6. 2010
Příspěvků: 34

Re: MATCH kopírování hodnoty z jiného listu

Soubor přeorganizovat nemůžu. Asi se vykašlu na tabulky a zkusím vymyslet lepší systém. Díky.

Offline

Zápatí