Joudun työssäni pusaamaan todella paljon Helsingin alueen postinumeroiden ja niiden katuosoitteiden parissa, eli syöttämään niitä Exceliin eri tarkoituksessa.
Jos saisin kaikki postinumerot ja osoitteet johonkin lokiin, esimerkiksi Tietojen kelpoisuus alasvetovalikkoon, miten saisin H-sarakkeeseen syöttämäni osoitteen perusteella I-sarakkeeseen alueen postinumeron automaagisesti?
Onko kukaan törmännyt tällaiseen tarpeeseen koskaan?
Jos saisin kaikki postinumerot ja osoitteet johonkin lokiin, esimerkiksi Tietojen kelpoisuus alasvetovalikkoon, miten saisin H-sarakkeeseen syöttämäni osoitteen perusteella I-sarakkeeseen alueen postinumeron automaagisesti?
Onko kukaan törmännyt tällaiseen tarpeeseen koskaan?
Vastaukset 39
Vanhimmat
PHAKU funktio tekee tuon. Sinulla pitää vain olla taulukko jossain josta tieto haetaan.
=PHAKU([haettava arvo];[mistä haetaan];[monesko sarake];0)
Ei tule onnistumaan, puutteellinenkin osoite- ja postinumerorekisteri on toista sataa megaa kooltaan, Exceli menee kykkyyn paljon vähemmästäkin. Jonkun jakopiirin tiedot ehkä onnistuu, mutta ei koko rekisterin käsittely.
Tietokanta tai joku muu vastaava tapa tarvitaan tuommoiseen.
Ennen oli sellainen keltakantinen postinumeroluettelo, saakohan sen Excel-taulukkona jostain? Siinä oli muistaakseni kadut lueteltu aakkosjärjestyksessä kunnittain, ja sitten tietenkin kyseisen kadun postinumero.
Ehkä tuota postin verkkopalvelua voisi jotenkin hyödyntää, tuonnehan voi tökkiä urliin hakuja, esim:
http://www.verkkoposti.com/e3/postinumeroluettelo?po_commune_radio=zip&streetname=&po_commune=helsinki&zipcode=
Arvelin JOS- tai PHAKU funktioiden olevan kelvolliset työkalut, mutta en sitten päässyt alkua pidemmälle kokeiluissani.
On totta, että pelkästään jo Hesan osoite- ja postinumerorekisteri on aika mittava, mutta mitenköhän pivot auttaisi asiaa? Tuleeko ehdotuksia mieleen..
Voisi kuvitella, että VB:llä saisi jotenkin asiaa helpotettua, mutta kun pitäisi vaan osata sitä. :)
Missä muodossa tuo rekisteri on?
https://www.quora.com/How-do-you-deal-with-large-data-sets-in-Excel
Sattu vaan googlatessa silmään.
Kiitos linkistä, tuota voisi ruveta jotenkin hyödyntämään.
Ensimmäisenä tulee mieleen, että onnistuisikohan nuo (kaikki linkin takana olevat suomalaiset) osoitteet laittaa Tietojen kelpoisuuden tarkistaminen -taulukkoon.. ehkä onnistuisi. Aika massiivinenhan tuosta osoiterekisteristä tulisi..
Tuossa on ainakin yksi ongelma; Tietojen kelpoisuuden tarkistaminen -taulukosta poimittua nimeä ei voi solussa enää editoida, eli jos haen kadun nimen ei siihen voi enää lisätä osoitteen numeroa tai muita kohteen tietoja. Vai voiko tuon kiertää?
Yritetääs kerran vielä.
Minkälainen olisi JOS-kaava, kun ajatus on tämä:
Jos kirjoittaa H -sarakkeelle, mille tahansa riville jonkun kadun nimen, jotka löytyy tallennettuna esim. Taul2:sta sarakkeesta A, pitäisi I -sarakkeelle saada ko. kadun nimen viereen automaattisesti ilmestymään vastaava postinumero, jotka taas löytyy tallennettuna Taul2 sarakkeelta B kyseisen kadun nimen vierestä.
Itse kaavan voisi varmaan kirjoittaa johonkin soluun, jossa se ei ole "tiellä".
Vaikuttaa yksinkertaiselta haasteelta, mutta onnistuuko tämä JOS-kaavalla?
Olikohan tarpeeksi sekavasti kysytty? :)
https://vimeo.com/193840730
Tuommosen rustasin jospa se auttaa, vaikka onkin Linux ja LibreOfficen Calc kysymyksessä. Kato kokoruudussa niin saa selvää paremin.
Linux Mint 18 Sarah
Xfce 64-bit
Hyvältä näyttää. Otan ja kokeilen jossain mutkassa tuota mallia.
Kiitos etukäteen mielenkiinnosta.
Minulla olisi näemmä samankaltainen tarve kaavalle kuin "Kooditilastoijalla" säikeessä "Koodit talteen", eli tätä kaavaa: =PHAKU(A1;Taul2!A:B;2;0) -olen koittanut taivuttaa omaan osoite- ja postinumero taulukkooni.
Olen saanut onnistumaan muutamia hakuja, kuten kaava osoittaa, mutta jo kymmenen riviä Taul2:ssa torppaa kokeiluni.
Eli, Taul2:ssa mulla on A-sarakkeessa katuosoitteet ja B-sarakkeessa postinumerot.
Taul1:ssä hakusarake A1:stä alaspäin, johon siis kirjoitan hakusanan, eli osoitteen ja B-sarakkeeseen tulee haun tulos (postinumero) B1:stä alaspäin. (kaava kopioitu alaspäin)
Koodi olisi mitä mainioin tarpeeseeni, mutta kun se ei tunnu taipuvan kuin muutamaan kokeiluriviin.. ;((
Mikä avuksi?
Onko hakuarvo täsmällinen? PHAKU-funktion viimeinen parametri voi olla joko epätosi (0), jolloin haetaan tarkkaa arvoa, tai tosi (1), jolloin haetaa "sinne päin" olevaa arvoa.
Aiheesta lisää tässä: https://support.office.com/fi-fi/article/PHAKU-funktio-0bbc8083-26fe-4963-8ab8-93a18ad188a1
Sinun kannattaa varmaan nyt selvittää SUHTEELLISEN ja SUORAN viitauksen erot. Tässä on ihan hyvä video siihen.
https://www.youtube.com/watch?v=2w9ll0dRbtM
Nyt kun katot tätä minun videota, niin huomaat kuinka minä muutan nuo lomakkeen 2 sarakeviittauksen suoraksi-viittaukseksi siinä lopussa suoraan kaavariville.
https://vimeo.com/193840730
("Koodi olisi mitä mainioin tarpeeseeni, mutta kun se ei tunnu taipuvan kuin muutamaan kokeiluriviin.. ;((")
Jos vika on tuossa viittaus-tavassa, sen ei pitäisi taipua ollenkaan, vain se yksi solu toimii oikein ja kaikki kopioidut, ja raahaamalla alaspäin lisätyt väärin.
Joten viittaukst nyt ensin kuntoon, vika on varmaan siinä.
Linux Mint 18 Sarah
Xfce 64-bit
Tässä tuo kaava vielä selvyydeksi:
=PHAKU(A1;Taul2!A:B;2;0)
Hakusanasolu on Taul1 A1 ja Taul1 B1:een olen laittanut tuon kaavan, jonka olen sitten hiirellä kopioinut alaspäin, jolloin A-sarakkeseen voi tehdä useita hakuja allekkain.
Onkohan tämä miten sallittua tai suositeltavaa edes?
Ideana olisi siis saada Taul1 A-sarakkeeseen useita satoja "hakusanarivejä", joille tulee B-sarakkeeseen aina osoitetta vastaava postinumero.
Tiedot löytyy Taul2 A- sarakkeella osoite ja B sarakkeelta postinumero.
Toistaiseksi testailussa on vain muutama sata osoite- ja postinumerorivejä.
Olen koittanut erilaisia variaatioita..
=PHAKU(A1;Taul2!A:B;2;0)
=PHAKU(A1;Taul2!A:B;2;1)
=PHAKU(A1;Taul2!A1:B200;2;0)
=PHAKU(A1;Taul2!A1:B200;2;1)
sama lopputulos -toimii erittäin epäluotettavasti tai ei ollenkaan.
Kyllä tuon ensimmäisen pitäisi toimia kuten sen kuuluistan junan vessan.
Jos se ei sitten tässä tapaukssa toimi, niin millä tavalla ei toimi? Antaako kaava virheilmoituksen, vai tuleeko väärä arvo, tyhjää vai mitä?
Jotain teen takuulla väärin, en vain oivalla mitä.
Voisitko kokeilla saatko toimimaan. Alla lyhyt osoite- ja postinumerolista, joka sijoitetaan Taul1 A ja B sarakkeisiin.
Ainonkatu 00100
Aurorankatu 00100
Väinämöisenkatu 00100
Vänrikki Stoolin katu 00100
Yliopistonkatu 00100
Yrjönkatu 00100
Albertinkatu 00120
Annankatu 00120
Uudenmaankatu 00120
Yrjönkatu 00120
Bernhardinkatu 00130
Erottaja 00130
Unioninkatu 00130
Harakka 00140
Iso Puistotie 00140
Itäinen Puistotie 00140
Jääkärinkatu 00140
Kalliolinnantie 00140
Kapteeninkatu 00140
Vuorimiehenpuistikko 00140
Albertinkatu 00150
Ei ole Ekseliä juuri tällä koneella, mutta tarkoitit varmaan Taul2:sta? Ei noissa arvoissa mitään sellaista ole mikseivät toimisi.
Juu, Taul2:a juurikin tarkoitin.. meni pähkäillessä Taulut sekaisin. ;D
Kiitos korjauksesta.
Huomasitko että kaveri neuvoo sinulle PHAKU() Funktiota
ja minä neuvon HAKU() Funktiota
Jotta asiat ei menisi sekasin siirryn syrjään ja jään odottamaan kun olet valmis kokeilemaan tätä HAKU() Funktiota
Linux Mint 18 Sarah
Xfce 64-bit
Mitä eroa tuolla on?
Nimimerkki: "Yksi-kerrallaan-parempi" -missä viestissä tai kommentissa neuvot HAKU() Funktiota? En löytänyt tästä threadista.
Kokeilin kuitenkin tuota pelkkää HAKU() -funktiota, mutta tähän tarpeeseen se ei sovellu, kaavariviltä tulee herjaa liian pitkästä kaavasta.. tjms..
PHAKU() Funktio on 4 parametrin funktio
HAKU() Funktio on 3 parametrin funktio
Tuosta syystä oletin sen olevan helpompi omaksua.
Laittamani video kuvaa HAKU() Funktion käyttöä
https://vimeo.com/193840730
Tuo oli jo kolmas linkii tässä ketjussa tuohon samaan videoon
Sinulla on tuo lista näin
Ainonkatu 00100
Aurorankatu 00100
Väinämöisenkatu 00100
Elis samassa solussa Kadun nimi, ja Postinumero.
Nuo on oltava eri solussa, kuten tuolla avauksessa mainittiin I-solu ja H-solu
ÄLÄ SÄIKÄHDÄ TÄTÄ VIITUSTEN ERINLAISUUTTA mutta NE ON PAKKO OSATA
Jos käytät vain yhtä hakusolua, ei tarvita suhteellisen sarake-tiedon muutosta suoraan osoitusmuotoon.
A1 = suhteelline osoitus muoto
$A1 = on suora-osoitus A-sarakkeeseen
$A$1 = on suora-osoitus A-sarakkeen riviin 1
A$1 = suora osoitus riviin 1 missä sarakkeessa hyväänsä
Nämä osoitusmuodot pätee myös tuohon PHAKU() Funktioon, ja viittaaminen toiseen soluun on ehdottomasti hallittava, muuten ei taulukkolasketa onnistu.
Kerro mikä tuossa videossa meni yli ymmärryksen, minä tiedän jo että tuo viittaus tavan muutos suoraan kaavariville oli huono ratkaisu, joka saatto mennä vaikeaksi hahmottaa, mitä siinä tehtiin.
Voin tarvittaessa videoida tuon uudestaan, ja muokata videota tekstein että tuo suluviittaus tulisi paremmin esille.
Linux Mint 18 Sarah
Xfce 64-bit
Tässä tuohon Taulukon1 B1 soluun tuleva kaava
=HAKU(A1;Taulukko2.A$2:A$46;Taulukko2.B$2:B$46)
Vielä kuva solusta johon kaava tulee: http://but.fi/62v
Tuossa on tarvittava suora-viittaus, joten se kestää laajentaa, hiirellä vetänen alas päin.
Linux Mint 18 Sarah
Xfce 64-bit
Hei! Hyvä aihe.
Minulla olisi tarve hakea toisin päin, eli tarvitsisin tietyn postinumeroalueen kaikki katuosoitteet.
Mikähän olisi paras tapa toteuttaa se? Kokeilin vaihtaa katuosoitteiden ja postinumeroiden sarakkeet, mutta saan PHAKU-toiminnolla vain yhden katuosoitteen, joka näyttäisi olevan ensimmäinen.
Kyllä minulla ainakin tuo ensimmäinen kaava toimi. Aluksi kun kopioin nuo sinun osoitteesi tuosta ja liitin ne Exceliin ja jaoin ne "Teksti sarakkeisiin"-toiminnolla eri sarakkeisiin ja kun sitten aloin hakemaan postinumeroja ei kaava pelannutkaan. Sitten huomasin että katunimien perään oli jäänyt yksi välilyönti, minkä takia kaava ei löytänyt sitä Taul2 A-sarakkeelta. Tuossa alla kuvakaappaus Excelistä.
http://aijaa.com/z4hc1Z
"Sitten huomasin että katunimien perään oli jäänyt yksi välilyönti"
Libre Calcissa toimii seuraava:
=VLOOKUP(TRIM(A1);TRIM(Sheet2.A:B);2;0)
Tuo poistaa kaikki turhat välilyönnit ja näkymättömät merkit syötteiden edestä ja takaa, niin hakuarvosta kuin haettavistakin.
Tämä oli hyvä pointti. En kylläkään kokeillut tuota kaavaasi Mikki-Officessa, mutta sen sijaan poistin katunimien perästä turhan välilyönnin, niin jopas rupesi tämä kaava pelittämään oikein: =PHAKU(A1;Taul2!A:B;2;0).
Nyt olisi hyvä' rautalankaneuvo tarpeen.. Miten saan Taul2 A-sarakkeessa olevien katunimien perästä sen turhan välilyönnin jollain kaavalla pois?
Korostan sanaa: RAUTALANKANEUVO. :)
=POISTA.VÄLIT(teksti) (TRIM englanniksi)
=POISTA.VÄLIT(" Katunimi 1 2 2 ")
-> "Katunimi 1 2 2"
Eli tee tuo jokaiselle solulle, ja kopioi sitten ne takaisin A-sarakkeen soluihin "liitä määräten" toiminnolla "vain teksti" (tai arvot, en muista kumpi se nyt olikaan).
Väännetään oikein rautalangasta.
Taul2 C1 kirjoita kaava =POISTA.VÄLIT(A1). Jos nimilista on pitkä niin helpoiten saat kaavan kopioiduksi alaspäin näin: valitse solu C1, tuplaklikkaa sitä solun ympärillä olevan "reunuksen" oikeassa alanurkassa olevaa pistettä, kaava kopioituu alaspäin niin pitkälle kuin A ja B on dataa. Kopioi nyt nämä C:llä olevat "puhdistetut" solut (nopea tapa valita on että valitset C1 ja sitten Ctrl Vaihto pohjaan ja klikkaat "nuoli alas"- näppäintä),Kopioi ja Liitä määräten (vain arvot) A1 alkaen. Nyt voit tyhjentää C-sarakkeen.
PHAKU.. tänx. Tein tarkalleen, kuten rautalankaohjeesi sanoi.. Sain siis tehtyä kuvion oikein; määrätty liittäminen - vain arvot, mutta en onnistunut saamaan Taul2 A-sarakkeen solujen osoitteiden perästä tyhjää välilyöntiä pois yhdeltäkään riviltä.
Turha hakata päätä liian pitkään seinään. Helpommalla pääsee kun käy koko aineiston läpi käsin. Rajallinen määrä niitä osoitteita kuitenkin on. Ota vaikka tavoitteeksi 10 osoitetta per (arki)päivä, niin voit sen avulla laskea milloin homma on valmis.
Tee Exelillä rekisteristä .dbf tiedosto ja siirrä se Accesiin. Sitten vaan mieleisesi käyttöliittymä ja sulla on erinomainen osoiterekisteri käytössäsi. http://aijaa.com/kbcjeK
Tai sitten käy vaan katsomassa postinumerot Postin webbisivuilta.
Tein Pythonilla funktion, joka hakee webistä katuosoitteen ja kunnan perusteella postinumeroita. Laitoin ohjelmakoodin ohjelmointipalstalle, jos jotakuta aihe kiinnostaa.
http://keskustelu.suomi24.fi/t/14627058/postinumeroiden-noutaminen-pythonilla-webista
LibreOfficella voi ajella Python-makroja
Joku voi käyttää wlanin salasanana kadunimee postinumeroo?
onneks on aika vaikeeta tehdä kattava listaus niistä ;)
Lapset sotkevat luokattomilla avauksilla, asialliset takasin ylös.
=TRIM(SUBSTITUTE(A1;CHAR(160);CHAR(32)))
=POISTA.VÄLIT(VAIHDA(A1;MERKKI(160);MERKKI(32)))
Poistaa tekstistä ei ASCII koodiin kuuluvat välilyönnit(eli vaihtaa unicode 160 merkin ASCII 32:ksi ja ajaa sen jälkeen TRIM -kaavan
=INDEKSI(F4:F24;VASTINE(A1;E4:E24;0))
Hakee E4:E24 alueelta A1 -solun arvoa ja palauttaa vastaukseksi F4:F24 alueelta tuloksen(eli saman rivin seuraavan sarakkeen sisällön)
A1 -solussa voi käyttää myös korvausmerkkejä eli: Yliop* löytää Yliopistonkatu -sisältöisen solun (valitsee listalta ensimmäisen sopivan, joten kannattaa käyttää viereisessä solussa tarkistukseksi kaavaa =INDEKSI(E4:E24;VASTINE(A1;E4:E24;0)) jolloin näkee mikä oli vastaava katunimi listalla.