Vyhledání cen v externím souboru

Delph · 6. 3. 2018 15:15:11

Opět zdravím
znova jsem línej a chci aby Calc pracoval za mě.
Napadlo mě oslovit dodavatele materiálů, jestli by byla možnost zasílat ceníky v excelu (máme tištěný, který ale poměrně rychle zastará, protože ceny upravují i několikrát za rok) nebo hledat na internetu položku po položce...
No a když už mám soubor, který mi umí spočítat kde co v rámci různých konfigurací skříněk, mohl by rovnou umět spočítat cenu jen pomocí zadání materiálu.

Mám představu, že bych měl soubor s tímhle výpočtem, kde by byla kolonka dejme tomu B2 "materiál" po jejím vyplnění se kolonka "D2" podívá do tohoto ceníku, najde a vrátí hodnotu platnou pro B2 a vynásobí hodnotou v "C2" která by udávala počty ks/m/m2 ... platné pro daný materiál.

Poznámka 2: teoreticky to nemusí být externí soubor, mohl bych to mít prostě jako další list, abych nemusel nějak složitě řešit cesty.

Poznámka 3: muselo by to umět hledat i delší názvy jako: Dub Bardolino H1185 18 mm

zatím nepotřebuju znát konkrétní řešení, ale možnosti: Jde / Jde obtížně / Nejde, ať vím, jestli vůbec má smysl žádat o ten soubor s cenami. A teprve jestli ho budu mít, bych se začal trápit s tím jak to provést.

Zatím mám představu o funkcích Vlookup Hlookup... ale s těmi jsme hledal jen jednoduché údaje...

ludviktrnka · 6. 3. 2018 15:50:22

Jde snadno.

neutr · 6. 3. 2018 18:27:12

Potvrzuji jak napsal pan Trnka. Jde to. Možná že dodavatelé dají k dispozici CSV, XLS, TXT. To vše by vzal Calc dobře. Někdy dělají jen čitelné PDF jako například výstupy z programů Cad Siemens "S". Viděl jsem to u verze tuším S5. Nyní snad už je zastaralá i verze S7. To by se muselo řešit jinak.

Delph · 7. 3. 2018 07:05:54

ludviktrnka a neutr - děkuju za potvrzení mé myšlenky. Teď jdu zjistit, jestli mi budou schopni a ochotni dodat ty ceníky. A podle toho to tu buď zavřu, nebo budu řešit jak se mi daří pokračovat a ptát se dal.

Delph · 16. 7. 2018 08:57:54

Tak jsem se nějak zasek na VLOOKUPu... myslím, že dělám vše dobře, ale stejně mi to háže chybu 502:

I2 - buňka má zadanou platnost jako tabulka z listu Egger v rozsahu A1:A200 abych si zajistil shodnost názvu materiálu pro VLOOKUP

J2 - =VLOOKUP(I2;$Egger.A1:A200;5) cena je v 5. sloupci.

neutr · 16. 7. 2018 09:54:58

No ten VLOOKUP potřebuje zadání pole A1:E200.

=VLOOKUP(I2;$Egger.A1:E200;5)

Opět to nemám otestované - dělám to z hlavy takže pokud jsem sekl nějakou chybu opravte mne. Funkce VLOOKUP hledá výraz v prvním sloupci zadaného pole a vrátí při pozitivním nálezu výraz z toho 5. sloupce. Takže když by to bylo postaveno od sloupce C bylo by to takto : =VLOOKUP(I2;$Egger.C1:G200;5). Ty čísla sloupců se vážou jen na ten relativně zadaný úsek. proto v tom druhém případě je prvním sloupcem oblasti sloupec "C" a tím pátým sloupec "G".

Delph · 16. 7. 2018 10:01:36

paráda, funguje! to byla ta chyba - nerozšířeno na celé pole.

neutr · 16. 7. 2018 10:14:59

Ještě bych upozornil na jeden detail. VLOOKUP, HLOOKUP a LOOKUP hledají jen první hodnotu a podle parametru setřídění. LOOKUP musí být vždy vzestupně setříděn, ale HLOOKUP a VLOOKUP mohou být nesetříděné, respektive hladají od konce ap. To je důležité vědět stejně jako to, že v souboru může být podobných výsledků více. To už je složitější, ale také se to dá sestrojit.
Například pomocí COUNTIF, nebo COUNTIFS najít počet relevantních parametrů a podle toho sestrojit počet vzorců (Vert,Hori)LOOKUP s tím že se nalezená první hodnota vyjádří číslem řádku (sloupce) a druhý sloupec nebo řádek začíná za tímto prvně nalezeným a tak dál. Ale tohle už z hlavy dám těžko bez chyby.
Proto si musíte dát dobrý pozor a testovat zejména pomocí COUNTIF(S) výchozí parametr.

Delph · 16. 7. 2018 10:35:54

To je pro mě zajímavé jako boční informace, ale naštěstí tady se žádný materiál nejmenuje stejně, takže respektive nějaké změny třeba ve struktuře povrchu jsou zaneseny do jména v podobě rozdílné koncovky.

Delph · 16. 7. 2018 12:42:34

Tak mám problém s VLOOKUPem při zobrazení výsledků:

Zjednoduším zápis v tabulce:

A / B / C / D / E
jméno/popis/ ks /sklad/ cena
LTD 1/ ... / 1 / skl / 200
LTD 2/ ... / 1 / skl / 250

No a když si nastavím materiál 1, tak mi to hodnotu najde, když materiál 2, tak to napíše N/A jako by tam nic nebylo, což není pravda...

nahrál jsem soubor do sólo vlákna, takže popíšu nastavení:

list: Součty
E2 = Kronospan
I2 = LTD Front White 101 SM -> J2 zobrazuje cenu
I2 = LTD Front White 101 PM -> J2 zobrazuje N/A

těch případů je tam víc, a docela náhodně.

Editoval Delph (16. 7. 2018 12:54:57)

Delph · 16. 7. 2018 13:21:36

a ještě doplním - správně to cenu vyhledá u první položky v seznamu, ty další už mě přijdou, jako by to losovalo náhodně, protože mi to tam vrací ceny třeba úplně z jiného řádku.

kabi · 16. 7. 2018 16:15:45

doplňte si do funkce vlookup 4. paramer s hodnotou 0. Takto funkce předpokládá, že máte sloupec s hledanými hodnotami setříděný vzestupně a hledá nejbližší nižší hodnotu k prvnímu parametru.

Editoval kabi (16. 7. 2018 16:29:47)

Delph · 17. 7. 2018 05:45:42

kabi napsal(a)

doplňte si do funkce vlookup 4. paramer s hodnotou 0. Takto funkce předpokládá, že máte sloupec s hledanými hodnotami setříděný vzestupně a hledá nejbližší nižší hodnotu k prvnímu parametru.

tak už funguje perfektně. opět mohu jen poděkovat. Ještě chvíli a už si budu moci zapsat že umím s programem Calc/Excel pořád je co se učit.

neutr · 17. 7. 2018 07:57:43

Mohu jen potvrdit Delphovo konstatování : pořád je co se učit..
Když budu hodně při zemi, tak zvládnout dokonale tabulkový procesor (celkem jedno jaký) tak je opravdu náročné. Jenom zvládat aplikaci vestavěných vzorců by vyžádalo poctivý semestr. Když si člověk uvědomí že je zde namíchaná problematika různých oborů, tak o každém takovém musí fundovaný uživatel vědět alespoň minimum nad úrovní propedeutiky.
To nehovořím o různých obslužných funkcích sešitu kterých je také hodně a jsou ve velmi široké škále operací. Jedná se ale o uživatelské znalosti které se nezahrnují jako IT obor i když mezi tyto znalosti patří "nahrávání" maker záznamníkem - což je asi diskutabilní.
"Ako sa stať machrom" (nemyslím stejnojmenné téma "na Excel" :-) By mělo být zpracováno jako manuál - ale není protože se už značně odlišují verze LO a AOO kde je nejsilnější historický archiv. Naproti tomu LO je v dynamickém rozvoji a několikrát do roka se něco mění - byť jen opravy, ale v důsledku toho nelze vytvořit uspokojivou nápovědu ani v angličtině, natož potom popis v podobě lokalizovaného manuálu.
Stejně je na tom Writer a pokusy o manuál existují - například poslední od Vlasty Otta, ale také víme že by musel být aktualizován nejméně každý rok. Stačí příklad maker. Vlastní StarBasic pomalu a jistě přestává fungovat v původní podobě. Nejznámější autor Andrew Pithonyak vydává čas od času aktualizovaný manuál, ale stejně nestíhá vše i když je to nejlepší zdroj OpenOffice.org Macro Information.

Jednoduše uživatelské znalosti a schopnosti používat kancelářský balík standardního typu přesahuje možnosti ubohoučkého řidičáku na počítač. Myslím že by byla potřeba specializované katedry v kombinaci se správu sítě - ale učit by se neměly proprietární balíky - celý svět postupně přechází na LO, respektive Linux. Poslední info přišlo, že Ruská federace přechází ve všech stupních na LO tak jako už přešla Velká Británie a mnoho dalších států, ministerstev nebo samosprávních celků.
Bohu žel hodně vysokých škol považuje LO, nebo AOO - obecně ODF za sprosté slovo. Přiživují se totiž právě tak že školí a zkouší pro tuhle firmu která pořádá kurzy řidičáku na počítač. Děti v 5. třídě výběrové školy se učí s Wordem - až nějakému opravdovému ekonomovi dojde co tenhle lobbing stojí daňové poplatníky tak vyhází půl ministerstva. MSO do osnov základních škol určitě nepatří protože to jsou miliardové investice v budoucnosti do SW.

Editoval neutr (17. 7. 2018 08:02:31)

Netwalker · 18. 7. 2018 13:56:45

neutr napsal(a)

No ten VLOOKUP potřebuje zadání pole A1:E200.
=VLOOKUP(I2;$Egger.A1:E200;5)
Opět to nemám otestované - dělám to z hlavy takže pokud jsem sekl nějakou chybu opravte mne. Funkce VLOOKUP hledá výraz v prvním sloupci zadaného pole a vrátí při pozitivním nálezu výraz z toho 5. sloupce. Takže když by to bylo postaveno od sloupce C bylo by to takto : =VLOOKUP(I2;$Egger.C1:G200;5). Ty čísla sloupců se vážou jen na ten relativně zadaný úsek. proto v tom druhém případě je prvním sloupcem oblasti sloupec "C" a tím pátým sloupec "G".

Já jen doplním, že je důležité kvůli kopírování tažením myší dodržet absolutní pozicování toho pole, jinak se to bude chovat do určitého okamžiku správně, ale určitě se po čase vyskytne chyba!
Správné zadání do funkce je:

=VLOOKUP(I2;$Egger.$A$1:$E$200;5)

Ty dolary $ se tam zadávají pomocí tlačítka F4.

Editoval Netwalker (18. 7. 2018 13:58:19)

neutr · 18. 7. 2018 17:06:27

Pro NetWalker:

Máte jistě pravdu s těmi absolutními adresacemí, ale kopírovat tažením je smyslupné jen ve dvou případech. Když hledané různé hodnoty jsou ve sloupci I2:Ixy, nebo vřádku - například číslo 2.

Zabukovaný výraz s dolary může sloužit ještě pro jiné hledané hodnoty na stejném úseku a není potřeba ho táhnout myší za růžek - stačí kopírovat - nejspíš ve sloupcích které jsou nadepsány hledanou hodnotou. Takže podle dispozice a podle potřeby, nebo zvykem uživatele.

Potřeb může být víc. Nelze tedy postup paušalizovat pro tažení za růžek.
Vzorec dá z celého úseku jednu hodnotu. Nato aby systém vzorců uměl najít více relevantních výsledků, tedy druhý, třetí .... musí být extra postavený vzorec který polem začíná právě pod předchozím nálezem.
Jestli najdeme první výskyt na řádku 16 z úseku A1:E200, tak druhý vzorec pod ním musí začínat na A17:E200. Pokud najdeme další hodnotu například na řádku 84, potom následující vzorec musí začínat na úseku A85:E200.
Vyžaduje to pomocný sloupec který zpětně vrátí řádek kde se vyskytla první hodnota a k němu přičteme jedničku. Takže to je alternativa kdy se tažení může udělat ale musí se upravit a tím se efekt snadné manipulace vytrácí.

Například pro výpisy a seřezení všech hodnot se používají jiné vzorce nebo kontingenční tabulka. Takže použít některý LOOKUP na všechny různé hodnoty je zbytečné. Význam to má jen pro několik případů se stejnou podmínkou z mnoha různých podmínek a vícenásobného počtu stejné podmínky. Ale i tak bývá lépe použít Sumif(s), nebo Countif(s) protože pak už zbývá jen hledání pořadí velikosti při stejné podmínce, nebo výskyt na řádku. Countif(s) by nám řekl kolik hodnot a tedy závislých vzorců postavit. Sumif(s)/Countif(s) by nám dal průměr. Zůstává jen pořadí u lokace.
V takových případech kde by šlo o všechny různé případy by už bylo na zvážení použít například maticové vzorce které do extra sloupce vypíší hodnoty stejné podmínky. Pod tím nejspíš součet za sloupec, pořadí nebo směrodatnou odchylku či rozptyl.

Delph toho má požehnaně ale sám si musí udělat názor co je lepší pro jeho vnímání, nebo paletu různých potřeb. Když použije metodu "F4" na celý původní vzorec tak zabetonuje i tu buňku která se má měnit (I2). Takže pak se musí dolary pro I2 smazat. Správně se vybere jen úsek ve vzorci a ten se obdaří dalary. To je o technice sestrojení vzorce a těch je jako možností vždy více.

Já si nestěžuju na nic jiného nežli na to, že takovéhly vychytávky měla obsahovat a také jich dost obsahovala naše Wiki. Docela myslím vadí, že dokolečka vysvětlujeme mnohokrát popsané případy použití, nebo potřeb. Nápověda je sice celkem někdy dobrá, ale není nad konkrétní příklady z různých úhlů pohledu.
Jde o to, že stručně popsaný nedostatek neřeší všechny varianty a možnosti. Pro začátečníka je to traumatizující - potřebuje vidět řešení které se podobá jeho potřebě. A tohle mohla Wiki obsahovat. Hledat v příspěvcích lze ale rešerše tohoto typu je pro povahou praktika neschůdná.

Netwalker · 19. 7. 2018 20:58:03

Pro neutr:
Delph chtěl vyhledávat cenu v ceníku,klasicky toto probíhá přes nějaký unikátní kód (katalogové číslo) a v tomto případě by byla chyba, kdyby bylo více vyskytů stejného katalogového čísla v daném seznamu. Úplně špatně by bylo, kdyby více položek mělo stejné katalogové číslo a různou cenu - toto je nutné při tvorbě ceníku ošetřit. Pak funkce VLOOKUP musí vrátit první a JEDINÝ výskyt.

neutr · 19. 7. 2018 21:50:19

Pro NetWalker:
Delph tam má unikátní kódy. Případ rozdílu cen stejné položky se musí řešit jinak. Do katalogu se zavádí jediná cena. Může to být cena průměrná, nebo skutečná přepočtená nohoru nebo dolu. Tohle zákazník nezjistí. Je jen otázkou ekonomického vyjádření zda posunout ceny podle vyšší ceny, nebo zprůměrovat použité materiály podle skladových zásob.
To je podpověď na všechy alternativy ale je to jen interní firemní politika. Ve výsledku bude jedna cena pro stejný index a jestli to bude férový průměr, nebo vyšší sazba (mírný zisk) je úplně vedlejší problém.
Bavil jsem se s klukem který pracuje u firmy která vyrábí díly pomocí laserových robotů. Vše co zbývá po výřezech musí do šrotu - nařízení EU. Žadné zásoby ze zbytků kvůli "černé ekonomice". Pracují s tabulemi antikora (mimo jiné) a zbytky v rozměrech 600x800 (a podobné použitelný výřezy) jdou do šrotu. To jsem nemohl rozdýchat. Tan kluk argumentoval, že měli kontrolu inspektora z EU a zbyl jim nějaký kus vinglu který dali zpět na sklad - a měli průšvih. Ten zbytek by museli buď vyúčtovat původnímu zákazníku jako dobropis, nebo vykázat nulovou cenu za materiál - prostě nepřípustné. Takže každý den odvážejí velmi drahý materiál do šrotu a to zdražuje ceny výroby.
Takže si nedělejte iluzi o tom, že by mohli v Delphové firmě (původně asi Daněk - Taros) míchat různé ceny. Zadělali by si na průser. Prostě musí nakoupit na zakázku, materiál vyúčtovat a zbytek vyhodit. Rozum to nebere, ale EU tak zajišťuje práci a brání černé ekonomice - na tohle jsem už asi starý blb. Co tomu říkají ekologové? Asi nic. Má to "logiku" ale škodlivé důsledky.

Takže Delphovi nezbývá nežli správně (ani málo, ani moc) koupit materiál, a ten naúčtovat zákazníkovi. Jde tedy o schopnost firmy vytěžit maximum z nákupů. To je schopnost konkurence. Při tom jde ještě o řemeslnou stránku věci. Nedivím se, že chce všemu rozumět do posledního detailu.
Z toho plyne Delphovo postoj a názor. Musí si být jistý. Za ním stojí zřejmě existence rodinné firmy a odpovědnost je veliká. Osobně mu držím palce. Když bude úspěšný posune se na další level. Ale za úspěch je nutné považovat i přežití.
Těžko Delphovi vtloukat do hlavy něco co by asi potřeboval znát, ale nemá prostor. Život není jen práce a čas na "učení se" je spíš vynucen potřebami a nikoliv zájmem.

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

#1 6. 3. 2018 15:15:11

Vyhledání cen v externím souboru

#2 6. 3. 2018 15:50:22

Re: Vyhledání cen v externím souboru

#3 6. 3. 2018 18:27:12

Re: Vyhledání cen v externím souboru

#4 7. 3. 2018 07:05:54

Re: Vyhledání cen v externím souboru

#5 16. 7. 2018 08:57:54

Re: Vyhledání cen v externím souboru

#6 16. 7. 2018 09:54:58

Re: Vyhledání cen v externím souboru

#7 16. 7. 2018 10:01:36

Re: Vyhledání cen v externím souboru

#8 16. 7. 2018 10:14:59

Re: Vyhledání cen v externím souboru

#9 16. 7. 2018 10:35:54

Re: Vyhledání cen v externím souboru

#10 16. 7. 2018 12:42:34

Re: Vyhledání cen v externím souboru

#11 16. 7. 2018 13:21:36

Re: Vyhledání cen v externím souboru

#12 16. 7. 2018 16:15:45

Re: Vyhledání cen v externím souboru

#13 17. 7. 2018 05:45:42

Re: Vyhledání cen v externím souboru

#14 17. 7. 2018 07:57:43

Re: Vyhledání cen v externím souboru

#15 18. 7. 2018 13:56:45

Re: Vyhledání cen v externím souboru

#16 18. 7. 2018 17:06:27

Re: Vyhledání cen v externím souboru

#17 19. 7. 2018 20:58:03

Re: Vyhledání cen v externím souboru

#18 19. 7. 2018 21:50:19

Re: Vyhledání cen v externím souboru

Zápatí