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

#1 25. 12. 2018 21:21:48

microfox
Člen
Registrace: 25. 12. 2018
Příspěvků: 5

Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

Zdravím,
řeším problém s načtením dat z listu "Kontakty" a to na listu "Faktura". Jde mi o to, že když si pole se seznamem rozkliknu, tak tam mám čísla a když vyberu, tak se mi údaje vyplní. Osobně bych uvítal, abych místo čísel v tom rozevíracím formuláři měl jména lidí čehož jsem schopen docílit, že oblast dat je Kontakty.B2:B500 jenže mi to nevrací číslo a proto se u kolonek kde se mají údaje doplnit ukáže chyba. V html existuje něco jako value, ale jak to funguje v calcu netuším. Dokázal by mi s tím někdo pomoct to překopat tak, aby v tom formuláři na rozkliknutí se ukazovali jména z listu "Kontakty" a údaje se doplnily?

Přikládám dokument http://leteckaposta.cz/606577128

Editoval microfox (28. 12. 2018 13:50:00)

Offline

#2 26. 12. 2018 09:17:33

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

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

Celkem běžné řešení je například pomocí VLOOKUP. Viz Řešení pomocí VlookUp



PS Váš Index je při tom ignorován, ale dá se najít řešení jiným vzorcem pokud by to bylo potřeba. Ten výběr je proveden pomocí DATA > PLATNOST. Jakmile dáte kurzor do políčka se jménem objeví se šipka s výběrem. Vše ostatní se už načítá jen pomocí VLOOKUP.

Editoval neutr (26. 12. 2018 09:21:00)


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 26. 12. 2018 10:18:50

microfox
Člen
Registrace: 25. 12. 2018
Příspěvků: 5

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

aha, moc děkuji! Přiznám se, že na funkci VLOOKUP jsem včera koukal, jen jsem úplně nevěděl jak postupovat.

Offline

#4 27. 12. 2018 19:18:46

microfox
Člen
Registrace: 25. 12. 2018
Příspěvků: 5

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

Narazil jsem na problém - když jsem začal přihazovat kontakty, tak mi to nepochopitelně u některých lidí začalo dávat ulice nebo jiná data jiných a nebo to napsalo N/A. Zkrátka nefunguje to tak, jak bych si představoval. Co by se s tím dalo dělat ? Přikládám https://uloz.to/!OGpijf3mD6Jt/testovaci-faktura-ods

Vytvořil jsem si tabulku s kontakty 1 až 500 a vůbec se to při výběru na listu Faktura_převodem nepřepne

Offline

#5 27. 12. 2018 22:56:00

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

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

Celkem jednoduchý problém. Použil jste ke kontrole čísla - ale ve formátu TEXT. Když ta svá pole v prvním listu přeformátujete podle volby FORMÁT > BUŇKY > VŠE(nebo ČÍSLO) tak Vám to bude chodit bez problému. Spíš doporučuji špatně naformátovaný list smazat a vytvořit jiný se stejným jménem.


Já jsem pro ukázku naformátoval ten Váš první list na "formát VŠE". Má to problém - přepíše se první buňka (zadá se například vzorec) a ta se následně musí vykopírovat do všech ostatních. Proto doporučuji použít nový list.


U Vás byla chyba formátu čísla jako TEXT "@". Tohle vyplněné číslem VLOOKUP nepobral. Když by tam byl opravdu text tak by se to stát nemělo, ale formát čísla "TEXT" už to nebere. Přes to implicitní nastavení pobere VLOOKUP (a jemu podobné) bez problémů. Můžete mít problémy s tím, že někdy je lepší používat setříděné seznamy. Nějakých 500 řádků není problém - ale v řádu stovek tisíc už by to vadilo. Ukázka je tady Chyba formátu buňky

Editoval neutr (27. 12. 2018 22:58:06)


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

#6 28. 12. 2018 11:40:09

microfox
Člen
Registrace: 25. 12. 2018
Příspěvků: 5

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

Stál jsem si Vaši ukázku a stejnej problém i u Vás. Když vyberu první dvě položky na listu faktura, tak se data doplní OK. Když vyberu položku č.3 - tedy LQSM, tak se data nedoplní a vyplní to akorát #N/A. Stejně tak je to i u dalších položek

Tak nevím jestli je problém v LibreOffice jako takovejch. Mám verzi 6.0.0.3
Nový list jsem vytvořil a nic se nezměnilo - stejný problém

Editoval microfox (28. 12. 2018 11:42:25)

Offline

#7 28. 12. 2018 12:17:04

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

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

Ne - já jsem tam zanechal vzorce. Když toto načtete do paměti a vložíte jen jako čísla a text tak se to stávat nebude. Ty vzorce se přepočítávají po entru.


Když tam zanecháte jen čísla - tak jak jste měl původně, ale "skutečně" čísla (zarovnají se doprava) tak to bude fungovat. Vzorce tam máte jen proto, abyste viděl, že nemusíte moc textu vymýšlet. Dělal jsem to dost dloho večer tak jsem to málo zakomentoval.


Problém původního řešení spočívá v tom, že "Textová" čísla se řadí "lexikálně" = "alfabeticky", tedy například 1, 11, 111,...podobně 2, 22, 222 a tak dál. Když by to byla čísla která jsou skutečně formátem čísla problém by nenastal.


Je to správně funkce VLOOKUP musí fungovat na implicitní formát - tedy ten který je nastaven v prázdných buňkách nových listů. Bylo by špatné když by to fungovalo jen na explicitních formátech. Pravdou je že u mne to na verzi: 6.1.3.2 (x64), ID sestavení: 86daf60bf00efa86ad547e59e09d6bb77c699acb Vlákna CPU: 2; OS: Windows 6.1; Vykreslování UI: výchozí; ... chodilo bez problému. Problém je v tom, že se u Vás může po ENTER objevit přepočítávání vzorců. Takže vezměte celý list zdroje do paměti - nejlépe dialog Vložit jinak "Jen čísla, text + Datum a čas". Tím přepočítávání odstraníte a bude to chodit. U mne to chodilo dobře i se vzorci - jen musíte ve faktuře nově vybrat pomocí roletky - a bez entru.


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

#8 28. 12. 2018 12:44:28

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

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

to nedohledání správných údajů způsobují dvě věci:
1. generování jména pomocí randbetween, kdy po výběru hodnoty (jména) v listu 'Faktura_PŘEVODEM' dojde v listu 'adressbook' k přepočtu všech buněk a funkce vlookup poté nemůže dohledat odpovídající řetězec
2 ve funkci vlookup musí být ve čtvrtém parametru uvedena nula, nyní je tam dvojka

Editoval kabi (28. 12. 2018 12:46:34)

Offline

#9 28. 12. 2018 13:49:42

microfox
Člen
Registrace: 25. 12. 2018
Příspěvků: 5

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

Díky kabi ! Udělal jsem si úplně novej list s kontaktama, ovšem u jednoho člověka u kterého IČ začíná nulou jsem si musel nastavit formátování buňky jako text jinak ta nula zmizí když je to jako číslo což je logické. A hlavně bod č.2 kde upozorňuješ na čtvrtý parametr to mi hodně pomohlo :-))))
Teď to funguje jak to fungovat má a snad už žádná komplikace nenastane - za mě asi VYŘEŠENO

Offline

#10 28. 12. 2018 15:02:11

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

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

Pro kabi:
VLOOKUP rozeznává pouze "nic", nebo "True" kdy se vyžaduje vzestupně setříděný úsek, jinak je to celkem jedno co tam je - to plyne z nápovědy vzorce. Ale LibreOffice upřesňuje opačně :

LibreOffice HELP napsal(a)

Sorted is an optional parameter that indicates whether the first column in the array is sorted in ascending order. Enter the Boolean value FALSE or zero if the first column is not sorted in ascending order. Sorted columns can be searched much faster and the function always returns a value, even if the search value was not matched exactly, if it is between the lowest and highest value of the sorted list. In unsorted lists, the search value must be matched exactly. Otherwise the function will return this message: Error: Value Not

     Ale původní význam byl ještě jiný - hledání první hodnoty od začátku, nebo od konce a pak teprve hledání v neseřazeném seznamu od začátku do konce. VLOOKUP a podobné vzorce vrátí první nalezenou hodnotu a končí. Také bývaly a zřejmě jsou problémy s tím, že VLOOKUP (ale i ostatní) přeskakují a nehledají stejným způsobem čísla a text. Tím nechci omlouvat tu dvojku.


     Já jsem testoval dvojku protože "0" a "1" vedly původně na vzestupné a sestupné vyhledávání (podle řazení) a chodilo mi to jak na čísla tak text - a výběr do vzorců funguje bez entru.


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 28. 12. 2018 17:32:37

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

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

to neutr:
je to trochu jinak a jedno to právě není. Nápověda k funkci vlookup byla vždy zmatená (v OO, AOO i LO) a i nyní (po upřesnění v LO) si v českém překladu odporuje mezi popisem funkce a uváděným příkladem (jestli je vrácena poslední hodnota menší nebo následující) nebo informací "Matice je odkaz na oblast obsahující minimálně dva sloupce" (sloupec může být samozřejmě jeden - lze využít např. ke zjištění existence). Čtvrtý parametr je v nápovědě uváděn jako nepovinný parametr, přičemž právě on má hlavní vliv na výsledek vyhledávání. Navíc je právě čtvrtý parametr velmi nešťastně pojmenovaný "ŘAZENÍ". On s řazením samozřejmě souvisí, ale tento parametr řeší hlavně to, jestli bude vyhledána přesně odpovídající hodnota nebo někdy pouze přibližná.
Rozdíl je následující:
1. V případě, kdy čtvrtý parametr má hodnotu nula nebo "nepravda", pak vyhledává přesně odpovídající hodnotu v dané matici, pokud ji nenajde, vrací "#N/A" (je jedno, jestli je matice setříděná nebo není).
2. Pokud má parametr hodnotu jedna nebo "pravda", využívá zřejmě nějakou metodu půlení intervalů k rychlejšímu vyhledání požadované hodnoty. Daná matice tedy z tohoto důvodu musí být setříděná vzestupně. Pokud není, funkce vrací nesmyslné hodnoty. A nyní ten zásadní rozdíl, pokud funkce nenajde v matici (i setříděné) přesně odpovídající hodnotu, vrací jako výsledek nejbližší nižší hodnotu (čehož se využívá pro intervalové hledání), ale pokud požadujete přesně odpovídající hodnotu, můžete si tímto zadělat na značné problémy.

Editoval kabi (28. 12. 2018 17:40:29)

Offline

#12 28. 12. 2018 18:21:47

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

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

Pro kabi :
     Kdysi to tady popisoval "lp." ale pak přišla standardizace která to všechno zamíchala. Já jsem tu dvojku řešil v původní "reklamaci" od microfox(e) - ta čísla v textovém formátu. Nastavoval jsem tam od nuly, následně jedničku a dvojku. Stále to ukazovalo chybu, což by se stát nemělo. Bral bych i špatné řazení - ale ve výsledku byla vždy chyba. Tím tam zůstala dvojka a začal jsem řešit co je to za problém. Původní vzorec jsem řešil bez parametru a pak jsem tam dal ze zvyku dvojku a dál mne ani nenapadlo testovat jinou možnost.


     Takto jsem si uvědomil, že ta čísla jsou v textovém formátu. Udělal jsem první sloupec na číslo a začalo to chodit - nic jiného jsem už neřešil a vložil jsem tam vzorce které mi také chodily s tou dvojkou, ale bylo před půlnocí tak jsem to dál netestoval a poslal. Takhle tam zůstala dvojka a já jsem šel klidně spát. Po reklamaci jsem postup se vzorci otevřel a znovu otestoval - vše chodilo, ale šel jsem dál - vložil jsem ze vzorců text a pak také čísla. Tam to začalo haprovat, takže jsem otestoval Vaše řešení s nulou - a chodilo. Takže jsem šel dál do nápovědy a zjistil, že i když se obě nápovědy příliš neliší tak ta z portálu zdůrazňuje "nulu a false" zatímco ta ze vzorce "true + bez 4. parametru", což by měl být logický opak - ale není. Vše ostatní by mělo být opakem. Takže podle nápovědy u vzorce je dvojka správně, ale podle nápovědy z webu dvojka patří k řazeným seznamům.
     Faktem je že pod těmito schematy stále chodí původní rozvržení a algoritmus který měl chodit od začátku do konce nechodil kvůli nekonzistentním textovým číslům. Binární dělení (polovinové) platí pouze pro seřazené seznamy, ale jak je vidět u textových čísel - prohledávání pomalejší (postupně) nefunguje. Máte pravdu, že v tom byl a je zmatek. Zřetelně se nastavilo prohledávání lexikální (kvůli formátu čísla '@') ale LO četlo čísla v textovém formátu jako čísla (dá se s nimi počítat).
     Takže tohle asi už víme a musíme prověřovat nejlépe vždy znovu - a já jsem to neudělal. Pravda je někde na půl cesty : Když zadáme hledání bez seřazení tak by se neměla objevit chyba - nejvýš špatný výsledek. Když jsou data v implicitním formátu tak to chodí ale formáty zejména Datum a Čas by to mělo brát také - nejspíš jako čísla, ale dá se pochybovat, že to zvládne VLOOKUP ap. z importu CSV.
     Otázka je co má být správně v nápovědě : - určitě se dopracujeme k regulárním výrazům, ale ani to nemusí fungovat a nápověda je nedůvěryhodná. Chtělo by to otestovat jaká je to chyba a nebo upřesnit nápovědu tak aby popsala syntaktické "špeky". Je to nejspíš bug, ale stačilo by vyčíst z nápovědy kdy to funguje tak a kdy onak. Problém se týká dvou základních druhů řazení numerické / lexikální a další je "urychlené" pomocí algoritmu který využívá řazené seznamy, nebo pomalé jeden výraz po druhém. No nic - vyplatí se prověřovat důsledně.

Editoval neutr (28. 12. 2018 18:47:31)


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 28. 12. 2018 19:10:23

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

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

to neutr:
dopřesním to ještě více. Ta chyba s tou dvojkou byla od prvního postnutého souboru. Funkce dvojku ve čtvrtém parametru nezná, tedy ho vyhodnotí, jako kdyby nebyl a použije implicitní hodnotu pro funkci, což je bohužel hodnota jedna neboli "true" - tedy funkce vrací výsledek i když nenajde přesně odpovídající hodnotu (matice s adresami bohužel není setříděna,

microfox napsal(a)

Narazil jsem na problém - když jsem začal přihazovat kontakty, tak mi to nepochopitelně u některých lidí začalo dávat ulice nebo jiná data jiných a nebo to napsalo N/A.

).
Druhá, docela zásadní věc je ta, že hledaná hodnota a matice musí mít stejný formát, viz

microfox napsal(a)

Vytvořil jsem si tabulku s kontakty 1 až 500 a vůbec se to při výběru na listu Faktura_převodem nepřepne

. Adresář s čísly má formát text (psal jste), ale pole ve faktuře má formát číslo, tedy stačí v poli s roletkou změnit formát na text.
Jinak vlookup umí vyhledávat v textových i číselných (datových) buňkách.

Offline

#14 28. 12. 2018 21:11:18

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

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

kabi napsal(a)

to neutr:
dopřesním to ještě více. Ta chyba s tou dvojkou byla od prvního postnutého souboru.


     To jsem kabi přiznal v prvním odstavci posledního příspěvku

neutr napsal(a)

Původní vzorec jsem řešil bez parametru a pak jsem tam dal ze zvyku dvojku a dál mne ani nenapadlo testovat jinou možnost.

- oč Vám jde?


Funkce dvojku ve čtvrtém parametru nezná, tedy ho vyhodnotí, jako kdyby nebyl a použije implicitní hodnotu pro funkci, což je bohužel hodnota jedna neboli "true" - tedy funkce vrací výsledek i když nenajde přesně odpovídající hodnotu (matice s adresami bohužel není setříděna,
    ... pokud tam nějaký parametr být musí (bez parametru je vlastnost dána) a chci jinou hodnotu - dám cokoliv jiného. Podle nápovědy vzorce je dvojka a cokoliv číselného to ostatní. Pokud se vezme popis z webu je to tak, že dvojka a vše mimo definované (ale čísla) je určující pro seřazené seznamy - tím se dostává dvojka (a cokoliv nad jedničku) do opaku - vyslovená chyba "vyššího čísla" by se ohlásila - napadlo Vás proč se chyba nehlásí? Je to proto, že je to Optional parametr tedy nepovinný a stačí že tam je, protože bez něj je dán význam jednoznačně - Oč Vám jde?


microfox napsal(a)

Narazil jsem na problém - když jsem začal přihazovat kontakty, tak mi to nepochopitelně u některých lidí začalo dávat ulice nebo jiná data jiných a nebo to napsalo N/A.

).
    .. To je asi pravda - ale ukázka chyb používala už jenom čísla v textovém formátu a z toho jsem vycházel. kabi otestujte si tu svou teorii na původním vzoru z Popis chyby. Pak poznáte že ve vzorci nepomůže nic - ani bez parametru, nula, jednička ani dvojka - a chyba byla jen dvojka že?


Druhá, docela zásadní věc je ta, že hledaná hodnota a matice musí mít stejný formát,
    .. To není pravda VLOOKUP najde řádek v matici a čte posun sloupců - o formátu je jen čtený 1. sloupec (ten musí mít shodný formát položek) - zbytek se načítá bez ohledu na formát - rozměry musí být shodné ale když jde o tabulku je to nesmysl (problém vzniká jen u LOOKUP kde se definují 2 sloupce bez souvislého úseku - Oč Vám jde?


viz

microfox napsal(a)

Vytvořil jsem si tabulku s kontakty 1 až 500 a vůbec se to při výběru na listu Faktura_převodem nepřepne

. Adresář s čísly má formát text (psal jste), ale pole ve faktuře má formát číslo, tedy stačí v poli s roletkou změnit formát na text.
    .. To jste vzal kde? Já tam vidím pouze VŠECHNY HODNOTY; CELÁ ČÍSLA; DESETINNÁ ČÍSLA; DATUM; ČAS; OBLAST BUNĚK; SEZNAM A DÉLKA TEXTU (zde je možné nastavit délku od do ap. - nikoliv text jako vlastnost) - neměl jste na mysli něco jiného - třeba čísla - která ovšem zastupují skutečný text. Autor testoval čísla aby nemusel vymýšlet textové položky.


kabi napsal(a)

Jinak vlookup umí vyhledávat v textových i číselných (datových) buňkách.


    Ano pokud je Vám to divné - podívejte se na původní zadání od microfox Popis chyby. Jde o to, že PLATNOST DAT určuje načtení formátu do vzorce a zde nastává ten problém. Ale můžete testovat třeba ručně kopírovaný seznam, nebo číslo - no z toho mne hlava nebolí ale uživatele který má postup zopakovat asi bude. Bez roletky by se vzorec splést neměl a čím pak budeme zadávat - klasickým způsobem (zápis do buňky), nebo nějakým výběrem z roletky třeba ListBox, ComboBox, nebo právě použitou PLATNOSTÍ?


     Ukažte se kabi a vyřešte problém Popis chyby podle sebe - rád se přiučím. S tím problém nemám - ale Vás asi něco trápí a souvisí to s osobností že?


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

#15 29. 12. 2018 07:12:32

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

Re: Pole se seznamem - načtení dat z adresáře - VYŘEŠENO

microfox napsal(a)

Díky kabi ! Udělal jsem si úplně novej list s kontaktama, ovšem u jednoho člověka u kterého IČ začíná nulou jsem si musel nastavit formátování buňky jako text jinak ta nula zmizí když je to jako číslo což je logické. .....

     Když potřebujete udržet u čísel nuly na začátku tak se to řeší pomocí FORMÁT > BUŇKY > KARTA ČÍSLA > KATEGORIE (VYBRAT VŠE, NEBO ČÍSLA) > dole pod KATEGORIÍ jsou MOŽNOSTI > položka ÚVODNÍ NULY. Zde si nastavíte počet míst které potřebujete a čísla menší dostanou správný počet "nul" tak aby byla opticky shodná délka všech čísel.


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

Zápatí