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

#1 28. 9. 2013 10:05:47

kamowski
Člen
Registrace: 29. 3. 2013
Příspěvků: 38

vyhľadávanie PSČ - VYRIEŠENÝ

dobrý deň, potrebujem poradiť s funkciou na vyhľadávanie
v tabuľke mám:
v stĺpci A1:A4200 Psč v tvare xxx xx
v stĺpci B1:B4200 mestá

v D1 zadám psč a potrebujem aby mi do E1 doplnilo mesto
stĺpec A1:A4200 s psč mám zoradený zostupne
v E1 som použil vzorec vlookup(D1;A1:B4200;2;1)

problém:
nakoľko jedno psč je priradené viacerým obciam (majú spoločnú poštu) funkcia mi zobrazí len jednu obec aj to nie podľa abecedného poradia
pr.:
013 11    Babkov 
013 11    Lietavská Lúčka
013 11    Lietavská Svinná
013 11    Porúbka 
-----------------
OTÁZKA:
je možné, ako výstup funkcie vlookup, dostať zoznam, z ktorého by som si mohol vybrať?
ako na to?
-----------------
guláš:
skúsil som to aj naopak, zadať obec a nech mi vyhľadá psč
stĺpce som zamenil, v A boli obce a v B boli psč, zoradené podľa A
výsledok je tiež zmätočný, pretože máme obce s rovnakým názvom a opäť je nutné ponúknuť zoznam z ktorého by som si vybral
tu ma napadá, že k výberu by bolo potrebné doplniť aj tretí stĺpec C1:C4200, ktorý by obsahoval "Okres" a tým by sa obec jednoznačne identifikovala
pr.:
Abrahámovce 086 41
Abrahámovce 059 72
.....doplniť na:
Abrahámovce 086 41 BJ (okres Bardejov)
Abrahámovce 059 72 KK (okres Kežmarok)
toto riešenie sa mi zdá ale ešte zložitejšie

Editoval kamowski (1. 10. 2013 10:35:03)

Offline

#2 28. 9. 2013 14:08:36

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

Re: vyhľadávanie PSČ - VYRIEŠENÝ

Lze to udělat dokonce poměrně velkým počtem způsobů. Rozhodnutí který je nejlepší záleží na Vás, ale měl by jste vzít do úvahy k čemu je to potřebné.
    Samozřejmě se nabízí řešení pomocí databáze. Tu bychom asi zvolili v případě, že by šlo o firemní agendu kterou používá více lidí.
    Další klasickou metodou je použití standardního filtru, nebo autofiltru. To už podle potřeb může vyžadovat určitý pomocný sloupec se vzorci, nebo pomocnými údaji. Ale lze to udělat také bez pomocných vzorců. Směrovací čísla mohou posloužit například k vytvoření kontingenční tabulky. Zase to chce trošku důvtipu, ale neliší se to příliš od obvyklého filtrování.
    Existuje také několik různých metod řešení vzorcem. Některé jste asi zkoušel. Pokusím se Vám to doplnit pokud budete mít zájem, ale mohu uvést i jiná řešení.
    V neposlední řadě je to makro, kterým je to řešitelné celkem komfortně. Postupy v makrech se mohou také odlišovat. V případě, že by jste měl o tohle zájem nabídnu řešení, které zvládnete sám pomocí záznamníku maker, nebo vytvořím přímo pokud mi to sem postnete (je to chvilka práce).


    To poslední řešení není složitější. Jde jen o to, abyste dostal to co potřebujete do Calcu. Jakmile budete mít hotové sloupce s okresem a zkratkou. Jednoduše spojíte vše do jediného textu, který bude v jediné buňce. Například v A1 bude PSČ, v B1 bude jméno, v C1 zkratka okresu a v D1 vplný název okresu.
Například do F1 vložíte vzorec =A1 &"  "& B1 &"  "& C1 &" "& E1. Mezi uvozovkami ponecháte tolik místa, kolik potřebujete. Pokud by jste chtěl zachovat formát PSČ s mezerou, musel by jste Místo jednoduchého odkazu na A1 vložit formulu Left(A1;3) &" "& Right(A1;2). Celý vzorec :
=Left(A1;3) &" "& Right(A1;2) &"  "& B1 &"  "& C1 &" "& E1
Samozřejmě pokud chcete pozměnit pořadí, přehodíte původní jednoduché odkazy. Následně vykopírujete do celého úseku. Nezapomeňte po vykopírování buňky se složeným výrazem načíst do paměti a vložit zpět už jen jako text.
Tip - Původní směrovací čísla upravte například tak, že odstraníte vše co je na druhém a dalším místě pomocí =Value(Left(A1;1)). Dostanete jen první číslo z PSČ. Na to potom postavíte autofiltr (maximálně 10 možností výběru - to je přehledné). Samozřejmě můžete použít také první dvě číslice, ale to už je výběr asi ze stovky možností, ale také by to šlo například spojit s první volbou.


    Podobně si můžete vypomoci s názvem obce, nebo s okresem. Když tam necháte jen první písmeno, bude tam asi méně než 26 položek pod výběrem autofiltru. Když postavíte autofiltr správně, tak se Vám v závislých celých názvech objeví jen možnosti podle volby prvého písmene.



    Pokud by jste měl zájem o něco specifického, tak napište. Já bych to sem možná napsal vše, ale bylo by toho víc nežli unese jedna stránka.


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

#3 29. 9. 2013 00:08:12

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

Re: vyhľadávanie PSČ - VYRIEŠENÝ

Pokud to má být vzorec

=IF(ROW()<=COUNTIF(A$1:$A$100;D1);INDEX($B$1:$B$100;SMALL(IF(D1=$A$1:$A$100;ROW($A$1:$A$100);10000000);ROW()));"")

(je maticový, pomalý, ulož trojhmatem ctrl-shift-enter)

Rychlejší varianta je s pomocným sloupcem třeba E. V něm spočti čísla řádků. kde se hledaná hodnota vyskytuje:
Pokud jsou hodnoty seřazené :

E1: =IF(ISNUMBER(MATCH(D1;$A$1:$A$100;0));MATCH(D1;$A$1:$A$100;0);"")
E2: =IF(AND(ISNUMBER(G1);INDEX($A$1:$A$100;G1+1)=$D$1);G1+1;"")
E3: kopie předchozího

G1: =IF(ISNUMBER(G1);INDEX($B$1:$B$100;G1);"")   
dál kopírovat

Offline

#4 30. 9. 2013 12:23:10

kamowski
Člen
Registrace: 29. 3. 2013
Příspěvků: 38

Re: vyhľadávanie PSČ - VYRIEŠENÝ

ďakujem za odpovede, no nepomohol som si sad
skúsim to napísať ešte raz

vyhľadávanie podľa PSČ
A       B                             C                   D
980 44    Abovce                        082 52              Abranovce
925 45    Abrahám                       toto zadám          toto nájde
086 41    Abrahámovce 
059 72    Abrahámovce 
038 22    Abramová 
082 52    Abranovce 
913 05    Adamovské Kochanovce
067 32    Adidovce 
023 11    Blažková 
023 11    U Holých
023 11    U Kľukov
023 11    U Kordišov
023 11    U Polievkov
023 11    U Rulcov
023 11    Zákopčie 

alebo
C                    D
023 11               Blažková 
toto zadám.          U Holých
                     U Kľukov
                     U Kordišov
                     U Polievkov
                     U Rulcov
                     Zákopčie 
                     toto nájde a ponúkne výber (v roletke?)




vyhľadávanie podľa mesta
A                       B               C                  D
Ábelová              985 13          Abrahámovce        059 72
Abovce              980 44          toto zadám         086 41
Abrahám              925 45                             toto nájde a ponúkne výber
Abrahámovce              059 72
Abrahámovce              086 41
Abramová              038 22
Abranovce              082 52
Adamovské Kochanovce     913 05
Adidovce              067 32

je to trošku rozhádzané, za čo sa ospravedlňujem, lepšie som to sem nevedel vložiť a nenašiel som spôsob, ako vložiť list z calc-u

Offline

#5 30. 9. 2013 12:40:42

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

Re: vyhľadávanie PSČ - VYRIEŠENÝ

Problém je v tom, že neuvádíte jakou metodu jste použil. Vzorec, nebo filtr? Já Vám nabízím několik naprosto odlišných metod a každá funguje. Když by jste použil pomocný sloupec, tak to umí i obyčejné vzorce - tak jak uvádí lp. Ale je možné postavit několik jiných variant na vzorce bez "matic", nebo s "maticovou syntaxí".
     Předpokládám, že tou "rolkou" myslíte prvek "seznam", kterému se říká také roletka a podobně. V takovém případě ne. Pokud chcete použít roletku, tak na to se musí zase jinak.
     Ovšem je možné, že tou "rolkou" myslíte autofiltr. Ano. Když správně postavíte autofitr, nabídne Vám výběr. Tedy za určitého předpokladu, že je to první výběr. Jinak při druhém výběru hned vidíte to co zůstává. (Jedině, že by některý prvek vyhledávání (například podle jednoho čísla) dával víc než je vidět na stránce. Pak by to bylo potřeba.


     Upřesněte prosím jakou metodu máte na mysli.


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

#6 30. 9. 2013 13:56:38

kamowski
Člen
Registrace: 29. 3. 2013
Příspěvků: 38

Re: vyhľadávanie PSČ - VYRIEŠENÝ

ako som písal v prvom príspevku, použil som vlookup
pre dáta z druhého príspevku:
vlookup(c2;a2:b4200;2;1) pre vyhľadávanie podľa psč a ten istý vzorec aj pre vyhľadávanie podľa mesta, len v tom prípade prehodím stĺpce a<->b
vyhľadávacie kritérium zadám len do c2 a výsledok má byť len v d2
áno, roletkou myslím "seznam", ak totiž nájde viac možností, musím mať možnosť výberu
na tej roletke netrvám, ak mi možnosť výberu bude ponúknutá iným spôsobom
filtre som nikdy nepoužíval
vzorce od lp som vyskúšal ale bez očakávaného výsledku sad zrejme som niečo nepochopil big_smile

Offline

#7 30. 9. 2013 16:45:29

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

Re: vyhľadávanie PSČ - VYRIEŠENÝ

Co máš za verzi OO?
OO dokáže vždy překvapit. Testoval jsem na jiném stroji. Teď jsem zkusil LO 3.5 (novější jsem zatím neinstaloval) a zjistil jsem že vzorec vrací chybu

=ROW()

v maticovém vzorci nebere???

Toto by fungovat mělo (do D1):

=IF(ROW(A1)<=COUNTIF(A$1:$A$100;$D$1);INDEX($B$1:$B$100;SMALL(IF($D$1=$A$1:$A$100;ROW($A$1:$A$100);10000000);ROW(A1)));"")

Když to budeš kopírovat dolů (myší), stiskni při tom ctrl. Tak se kopíruje celý vzorec, jinak se jen rozšíří pole. (tj. s ctrl dostaneš 1 vzorec = 1 buňka, bez ctrl dostaneš 1 vzorec = 1 pole buněk)


Co se týká roletky.

Vytvoř jméno (vložit - názvy - definovat)
Vyplň název (třeba "roletka")
vyplň oblast vzorcem:

=IF(ROW($A$1:$A$20)<=COUNTIF($A$1:$A$100;$D$1);INDEX($B$1:$B$100;SMALL(IF($D$1=$A$1:$A$100;ROW($A$1:$A$100);10000000);ROW($A$1:$A$20)));"")

přidej.


do d1 zadej kontrolu dat (data - kontrola vstupních dat)
Povolit: rozsah buněk
Zdroj: =roletka

OK

roletka bude obsahovat až 20 jmen.

(funguje na LO 3.5)

Pokud budeš mazat nebo přidávat řádky na začátku tabulky, může se počet vypisovaných jmen měnit.
Můžeš to pojistit náhradou

ROW($A$1:$A$20)

za

ROW(INDIRECT("A1"):INDIRECT("A20"))

Změno A20 za jiný počet řádků můžeš měnit max počet jmen v roletce.

Pro pohodlí je asi vhodné pojmenovat oblasti s PSC a jmény obcí. Jinak při změně rozsahu bude těch oprav možná docela dost.

Jinak, roletka a vzorec se v jedné buňce moc nesnášejí. Můžeš třeba do D2 napsat

=roletka

a vložit trojhmatem (ctrl-shift-enter) a vypíše to až 20 nalezených jmen.

Offline

#8 1. 10. 2013 05:12:59

j-pastierik
Člen
Registrace: 15. 11. 2004
Příspěvků: 761

Re: vyhľadávanie PSČ - VYRIEŠENÝ

Všimol som si, že máte neutriedené PSČ - toto môže spôsobovať problémy.

Offline

#9 1. 10. 2013 07:42:32

kamowski
Člen
Registrace: 29. 3. 2013
Příspěvků: 38

Re: vyhľadávanie PSČ - VYRIEŠENÝ

to lp:
vďaka, takto mi to funguje smile
mám LOo 4.1.1.2 (LM13 Mate)
niektoré funkcie (row, indirect) mi nie sú celkom jasné, keď budem mať čas, určite sa na to pozriem ale fungujú smile
trochu som si to prispôsobil:
do K1:K10 (aby mi to nezavadzalo) som dal:
=IF(ROW(A1)<=COUNTIF(A$1:$A$1000;$C$1);INDEX($B$1:$B$1000;SMALL(IF($C$1=$A$1:$A$1000;ROW($A$1:$A$1000);10000000);ROW(A1)));"")
pomenoval ako "roletka"
v C1 zadám psč
v D1 mám Dáta - platnosť - "roletka" a pekne mi to vyhľadá obce so zadaným psč smile
super, ešte raz ďakujem
_____________
to j-pastierik:
keď som použil funkciu vlookup, dáta som si zoradil buď podľa psč alebo podľa mesta a prehodil stĺpce podľa kritéria, na základe ktorého som chcel vyhľadávať
mal som také tušenie, že to bude chcieť pomocný stĺpec ale nemal som ani šajnu, aký smile
holt, človek sa do smrti učí smile
_____________
to neutr:
ako by ste to riešil pomocou databázy?
hľadal som aj na tejto stránke články o Base ale je ich ako šafránu a spojenie calcu a base = 0
ak by ste sem napísal polopatistický postup, ako vytvoriť db s psč, ako to prepojiť s calcom a používať, pomohlo by to nie len mne (použil by som to aj v iných tabuľkách)
čítal som si sériu článkov:
http://www.openoffice.cz/navody/openoff … bnej+firme, kde autor uvažoval o použití db ale nejak to "išlo do stratena"
článkov a návodov na nete určite zopár bude ale bez znalosti eng. sú mi na nič sad

Editoval kamowski (1. 10. 2013 07:47:06)

Offline

#10 1. 10. 2013 08:55:42

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

Re: vyhľadávanie PSČ - VYRIEŠENÝ

K tomu vlookup. Je tam ještě jeden problém. Mrkni příležitostně jak to funguje. Když je 4. parametr jedna najde obvykle nějakou hodnoru, která ale nemusí být rovna hledané. Takže buď

vlookup(D1;A1:B4200;2;0)

nebo

=if(vlookup(D1;A1:B4200;1;1)=D1;vlookup(D1;A1:B4200;2;1);"Neznámé PSC")

První vartianta je pomalejší, ale nepotřebuje setřídění a při malém počtu záznamů to nemusí být problém, druhá je hodně rychlá (cca 1000 - 10000x rychlejší než předchozí), ale v nesetříděném seznamu nemusí PSC najít, i když tam bude.

Offline

#11 1. 10. 2013 09:04:06

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

Re: vyhľadávanie PSČ - VYRIEŠENÝ

Souhlasím.
     Dokonce jsem v situaci, kdy chystám něco hodně podobného a při tom zkoumám právě to, co už bylo napsáno dříve. Jedním z těch autorů je právě pan Miroslav Gešvantner kterého vzpomínáte v odkazu. Již dříve jsem měl pro jiné účely vyřešeno nekolik různých způsobů vyhledávání z odlišných vstupů. Jedním z možných prostředků je klasická databáze. (Dnes upřednostňuji Calc před ostatními řešeními, ale dříve jsem se snažil řešit co nejvíce věcí právě databázemi.)
     Řešení pomocí databází je "klasikou", ale dnes také "popelkou". Takže zřejmě brzo by to mělo být publikováno asi ve formě článku s přílohami a téma bude administrativní činnost. Dokonce jsem si tam naplánoval, že přidám funkční databázi PSČ a tisk obálek tak aby byla návaznost na databázi kontaktů která je už základní instalaci předpřipravená (importy a sjednocení z adresářů poštovních klientů, web klientů, mobilních telefonů - SMS a nezávislých adresářů). Pan Miroslav Gešvantner uvádí práci - tisk dopisů, ale možná to ještě o něco rozšířím. Měl by to být soubor šablon a souborů, které se nainstalují a můžete přímo pracovat aniž by jste něco vymýšleli. Tedy jen musíte udělat importy ze svých vlastních (firemních?) adresářů. Až to bude pošlu Vám odkaz. Jenom na dokreslení tam asi bude také několik jiných způsobů vyhledávání. To je zese připraveno pro vzor zpracování výstupů (reportů) z eShopů ap.


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

#12 1. 10. 2013 10:48:16

kamowski
Člen
Registrace: 29. 3. 2013
Příspěvků: 38

Re: vyhľadávanie PSČ - VYRIEŠENÝ

článok by bol super ale ako píšem vyššie, "polopatisticky" popísať postup
- ako vytvorím databázu napr. z tabuľky v calcu
- ako tú vytvorenú databázu spojím s iným zošitom calcu a ako z nej dostanem dáta do listu alebo naopak, ako cez list calcu do nej pridám ďalšie dáta
skrátka vysvetliť ten príncíp
a veeeeľa obrázkov prosím big_smile
čítal som aj tú sériu článkov o base s príkladom "knihovňa" ale to prepojenie s calcom mi tam chýbalo

ak pridáte funkčné databázy (psč, tlač obálok...) ako píšete, fajn, ale to by mal byť len bonus
hotové riešenia sú "bezbolestné" a vítané ale chcel by som sa niečo aj priučiť aby som to mohol potom aplikovať v rôznych iných prípadoch a myslím, že takých je nás viac

Offline

#13 15. 10. 2014 06:47:44

kamowski
Člen
Registrace: 29. 3. 2013
Příspěvků: 38

Re: vyhľadávanie PSČ - VYRIEŠENÝ

to neutr:
ako to vyzerá s tým článkom?
je to už rok a akosi ho neviem nájsť?
vyšiel už alebo sa na ňom ešte pracuje?
doplniť ten článok od pána Gešvantnera alebo vypracovať niečo úplne nové by bolo super
off topic:
zatiaľ riešim firemnú agendu takto:
http://www.linux-mint-czech.cz/fakturac … linuxe-ii/

Editoval kamowski (15. 10. 2014 06:48:44)

Offline

#14 15. 10. 2014 11:43:37

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

Re: vyhľadávanie PSČ - VYRIEŠENÝ

Zastavil jsem to, protože LO dostalo databázi FIREBIRD. S tou nemám zkušenosti a nemohu se k tomu dostat. Mám rozpracováno několik rozšíření a šablon.
     Dokonce jsem dostal povolení přepracovat jeden zavedený mzdový program (pro Windows - je udělaný v Basicu a je prakticky morálně zastaralý) do OpenOffice. To by byl přesně průlom do státní správy. Tam bych potřeboval pracovat s Access2Base (zase jiná problematika) ale velice potřebná kvůli přechodům z programů Windows. Ještě jsem se k tomu pořádně nedostal, jen jsem pracoval na grafice (podmínka aby moje verze graficky odpovídala té jejich). Nestíhám kvůli plno věcem.
     Zastavil jsem se několikrát při vydání noých verzí - zejména LO. Už několik let mám připraveně a prakticky hotové rozříření - kombinatorické vzorce pro Calc. Kolem vánoc jsem zjistil, že mi mnoho maker nefunguje - příčina byla zejména v chybě generalFunction.COUNT, ale i jiné. Několik měsíců jsem testoval a opravoval - a vyšlo zase mezi tím několik nových verzí.
     Mám už strach, že mně ten prudký vývoj a chyby s tím spojené zničí. Prostě jsem zpomalil. Když vyjde nová verze testuji asi 50 základních maker a když chodí tak je to dobré a dál netestuji. Stejně to vždy nějaký čas a elán spolkne.



     Dostávám se do situace, že slibuji něco, co by mělo být snadné a není, ale snad se k tomu dostanu. Musím něco dodělat a pak se pustím do access2base a mezd. Odtud už je jenom krok k těm ostatním věcem.
     Takže se musím omluvit za nesplněné sliby ........ :-(

Editoval neutr (15. 10. 2014 11:46:46)


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

#15 16. 10. 2014 06:30:35

Ondra.kl
Člen
Registrace: 30. 9. 2008
Příspěvků: 933

Re: vyhľadávanie PSČ - VYRIEŠENÝ

neutr napsal(a)

Mám už strach, že mně ten prudký vývoj a chyby s tím spojené zničí

Jsem rád že LO někdo vnímá podobně jako já smile

Offline

Zápatí