Rozbalovací seznam hodnot v buňce - VYŘEŠENO

kabi · 1. 6. 2017 11:51:00

Je to tím, že funkce vlookup bez zadaného 4. parametru vyhledává v seznamu, který musí být seřazený vzestupně (blíže nápověda k vlookup). Řešením je si seřadit oddíl U... vzestupně nebo do funkce vlookup doplnit črvrtý pasrametr (nulu).

hdplot · 1. 6. 2017 11:59:47

Oddíl U seřazený vzestupně je "U" mezera a pak čísla vzestupně. Stejně je to i pro I - "I" mezera a čísla vzestupně. Přesto I funguje vždy a U jenom někdy.

Nicméně doplnění čtvrtého parametru pomohlo, takže to vidím jako vyřešené - Díky

kabi · 1. 6. 2017 12:19:32

jen doplním, pozor na význam slova "seřazeno". To, že vidíte data U... poskládaná za sebou podle rozměru neznamená, že jsou seřazena. Zkuste si ten blok označit a seřadit vzestupně.

hdplot · 1. 6. 2017 12:45:24

Zkusil jsem a chápu význam slova "seřadit". Nechápu ale proč u "U" profilů mu to vadí a chová se jinak než u "I" nebo "UPE" profilů které taky vlastně seřazené nebyly. (Ale to už je jen tak pro zajímavost, nevadí když zůstane příčina neznámá. Důležité je že to začalo fungovat)

neutr · 1. 6. 2017 15:54:19

Faktem je že systém funguje pro stejné typy zadání a řazení. Ale uprostřed fungujících dat je několik řádků nefunkčních. To se nedá zdůvodnit nějakými parametry vzorce. Jestli to funguje do U 160 a pak až od U 300 tak to popírá logiku chyby ve vzorci nebo v zadání typu vyhledávací hodnoty.
U mne to chodilo hned při smazání mezery. Hledal jsem odlišnost jakéhokoliv typu u těch řádků s chybou. To #N/A znamená chybu odkazované hodnoty - hledaná hodnota pro vzorec neexistuje. To je paradox protože roletka ji načte. Vzorec u ostatních případů tuto hodnotu najde (a je to stejný vzorec).
U těch několika případů ji prostě nenajde. Nejlepší vysvětlení by se nabízelo v tom, že značka obsahuje něco jiného v základním sloupci (2. list) a roletka tam něco přidá, nebo překreslí nějaký znak. Chyba #N/A po vyhodnocení pomocí =NA() vrátí chybu číslo 508. Tato chyba by měla být Chybějící závorka, např. uzavírající závorka bez odpovídající otevírací závorky., ale správně by to mělo vrátit zřejmě chybu 504 = Chyba v parametru seznamů Parametr funkce je neplatného typu, např. text místo čísla nebo odkaz k doméně místo odkazu k buňce.

Když si prohlédnete DATA > FORMULÁŘ (a postavíte se někam do úseku A50:G68) tak to vrátí obsah výběru. Tam nic extra neobjevíte - roletka to čte korektně ale vzorec ne. Napadá mne jediná možnost - některý netisknutelný znak. Ten se tam mohl vloudit například při kopírování odjinud. Já jsem tam ale nic takového nenašel ačkoliv bitové operátory umí záludnosti.
Nejprve jsem obsah překopíroval pod tabulku abych ji později opsal. Následně jsem vykopíroval řádek U 160 (který je správně) do chybových řádků. Ty jsem následně manuálně (klávesnicí) přepsal na původní hodnoty - a chyba tam byla stále!

Takže vylučuji klasický typ chyby. Mohlo by to být něco v XML - někdy se provádí ochrana znakem který smaže předchozí bit. Nebo něco jiného a stejně náhodného. Je zajímavé ře to dělá u všech systémů LO, AOO a u různých OS. Takže to může být nějaký pozůstatek dřívějších operací.
Možná by se vyplatilo celý sešit udělat znovu a pokud možno nekopírovat nic z původního sešitu a ani z jiných formátovaných textů. Dobrým postuperm by mohlo být uložit 2. list jak CSV. Tento soubor pak načíst do 3. listu (jako 3. list ze zdroje). Následně doplnit manuálně formáty a pbrázky. Původní list smazat a ten nový přepsat na "Tabulka rozměrů".

kabi · 1. 6. 2017 16:58:01

Na základě zkušeností pouze mohu při použití funkce vlookup doporučit dodržet dvě základní zásady:
1. v případě, že ve čtvrtém parametru není uvedena nula (nemusí se vyhledávat přesná hodnota z prvního parametru), je nutné mít odkazovaný rozsah vždy seřazený vzestupně. V případě, že není nalezena přesná shoda, funkce vrátí nejbližší nižší nalezenou hodnotu - pozor v české nápovědě je uvedena nejbližší vyšší hodnota.
2. použít ve čtvrtém parametru nulu (hledá pouze přesnou hodnotu z prvního parametru). Funkce si nárokuje více času.

V případě, že nepoužijete hodnotu nula ve čvrtém parametru a odkazovaná oblast není setříděna, se funkce vlookup chová nepředvídatelně - je možné, že se jedná o chybu. Právě u oddílu U... je vidět, jak nebezpečné je nesetřídění souboru. Chování tohoto oddílu je až na hodnotu U 160 v pořádku:
U 30 - U 80 - najde hodnotu a vrátí správná data
U 100 - U 140 - nenajde hodnotu, protože předpokládá, že je oblast setříděna a jako první hodnotu vidí již U 30, takže vrací #N/A (není žádná nejbližší nižší)
U 160 - tuto hodnotu nějakým způsobem najde a vrací správné hodnoty ???
U 180 - U 280 - nenajde hodnotu (stejné jako u U 100 - U 140
U 300 - U 400 - najde nejbližší nižší hodnotu (tedy ne správnou) od hodnoty U 30 a U 40 (opět na základě předpokladu setříděné oblasti).

U oblastí I... a IPE... jsou mimo prvního záznamu již všechny ostatní setříděné správně. I tak by podle logiky uvedené v předchozím odstavci měl vlookup kromě hodnoty I 80 a IPE 80 vrátit vždy #N/A, ale kupodivu vrací správné hodnoty. A právě proto ty dvě zásady.

neutr · 1. 6. 2017 18:44:11

Je to tak i když je to divné protože volitelný parametr se pro setříděné hodnoty vyplňovat nemusí. Je vidět že některé hodnoty řádků vrací a některé ne.
Je pravdou že po zadání nuly čte správně vše - pak by ale neměl být 4. parametr volitelný ale povinný. Je také možné že jde o zastaralý překlad - jinak je to chyba.
VLOOKUP bezvariantně hledá v prvním sloupci oblasti a tam jsou data opravdu setříděna. Když potom jednou vrací správně a jindy nesprávně nebo vůbec tak je to zřejmě chyba i když jak jsem uvedl - možná jde jen o nedostatek překladu.
................ Tak tohle jsem si myslel.

PS
Tak mi to nedalo a úsek jsem seřadil podle sloupce A. A je to tak. Třídění je logicky alfabetické a tak seřazený seznam začíná od :
U 100
U 120
U 140
U 160
U 180
U 200
U 220
U 240
U 260
U 280
U 30
U 300
U 320
U 40
U 400
U 50
U 60
U 65
U 80
Ano jde o vzestupné seřazení - ale alfabeticky. "U" je stejné ale 100 začíná jedničkou proto je první v pořadí, pak přijde 120 .... následně hodnoty s dvojkou. A tak se stane ře po 30 je 300, po 40 je 400 a tak dál.
Takže stačí seřadit podle sloupce A a funguje to i bez 4. parametru. Spletl jsem se přestože tuhle vlastnost textových řetězců znám. Ani nevím jak si vynadat :-(

Editoval neutr (1. 6. 2017 19:04:42)

kabi · 3. 6. 2017 06:00:09

Pro začínající uživatele funkce vlookup bych ještě doplnil, že použití/nepoužití 4. parametru má rozhodující vliv na výsledek hledání. Obojí má své opodstatnění a využití, opět bych odkázal na nápovědu k funkci.
- při použití 4. parametru s hodnotou 0 funkce vždy hledá "přesnou" (v uvozovkách proto, že funkce není case sensitive (nerozlišuje velikost písmen)) hodnotu v odkazované oblasti. V případě nenalezení vrací chybu #N/A.
- nepoužití 4. parametru (nebo hodnota 1 ve 4.parametru) hledá ve vzestupně seřazené odkazované oblasti. Přesná shoda nemusí být vždy nalezena, přesto funkce vrací nejbližší nižší hodnotu - toho se využívá při intervalovém hledání. Může to však také znamenat, že funkce vrátí chybnou hodnotu.

lp. · 4. 6. 2017 01:51:06

kabi to v podstatě vysvětlil.

Malé doplnění. VLOOKUP, ... používají při přibližném hledání, stejně jako funkce LOOKUP nějakou variantu binárního dělení (nebudu vysvětlovat co to je), proto ten požadavek na setříděný seznam hodnot. Jsou předvídatelné.

V uvedeném případě pro U 100 se nejprve porovná hodnotu U 160. Ta je větší, tak zkusí U 65. Ta je také větší, tak zkusí U 50 atd. až dojde na začátek seznamu.

Pro U 160 tuto hodnotu najde na první pokus a dál nehledá.

Co s tím.
a) setřídit - nepohodlné pro obsluhu i správu
b) čísla zarovnat zprava, tj. vlevo od čísel doplnit potřebné mezery.
c) upravit vzorce:

C14:
=VLOOKUP(VALUE(MID($A14;FIND(" ";$A14;1);1000));$'Tabulka rozměrů'.$B$50:$G$68;1)

d) použít přesnou shodu (4. par = 0) - pro malý počet přesně zadaných hodnot rychlé přibližné hledání nepřináší žádnou výhodu.

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

#51 1. 6. 2017 11:51:00

Re: Rozbalovací seznam hodnot v buňce - VYŘEŠENO

#52 1. 6. 2017 11:59:47

Re: Rozbalovací seznam hodnot v buňce - VYŘEŠENO

#53 1. 6. 2017 12:19:32

Re: Rozbalovací seznam hodnot v buňce - VYŘEŠENO

#54 1. 6. 2017 12:45:24

Re: Rozbalovací seznam hodnot v buňce - VYŘEŠENO

#55 1. 6. 2017 15:54:19

Re: Rozbalovací seznam hodnot v buňce - VYŘEŠENO

#56 1. 6. 2017 16:58:01

Re: Rozbalovací seznam hodnot v buňce - VYŘEŠENO

#57 1. 6. 2017 18:44:11

Re: Rozbalovací seznam hodnot v buňce - VYŘEŠENO

#58 3. 6. 2017 06:00:09

Re: Rozbalovací seznam hodnot v buňce - VYŘEŠENO

#59 4. 6. 2017 01:51:06

Re: Rozbalovací seznam hodnot v buňce - VYŘEŠENO

Zápatí