KESKUSTELUT > MUUT AIHEET > MITÄHÄN SE EXCEL TAHTOO
3675. Mitähän se Excel tahtoo
iso S5.11.2007 klo 14:28
Uudessa Excel 2007:ssä oli kuulemma sellainen vika (nyt jo korjattu) että se näytti joissakin laskutoimituksissa pyöreää sataatuhatta jos laskutoimituksen tulos oli 65535 tai 65536. Solussa oli silti oikea arvo, joten jatko meni oikein jos solua käytettiin laskutoimituksissa. Minulla on Excel 2003 ja siinä paljastui peräti kummallinen piirre kun tein lähettäjistä tilastoa. Se oli etukäteen tiedossa että tyhjä lähettäjän nimi, kysymysmerkki (tai useampi) ja asteriski aiheuttavat ongelmia, mutta häirikkö <Default> yllätti täydellisesti.
Jos minulla on A-sarakkeessa lista kaikista lähettäjistä ja B-sarakkeessa kaikista erilaisista lähettäjänimistä, voin kirjoittaa vaikkapa soluun C1 kaavan
=Countif(A$1:A$6525;B1)
ja Excel laskee minulle kuinka monta kertaa solussa B1 oleva nimi esiintyy A-sarakkeella riveillä 1-6525. Kun kopioin tämän kaavan C-sarakkeelle muiden lähettäjänimien viereen, saan kunkin nimen esiintymiskerrat. Kaavassa rivinumeroiden edessä oleva taala pitää huolen siitä että kaava on absoluuttinen eikä suhteellinen, eli jokainen B-solu saa kohteekseen kaikki A-sarakkeen solut joissa on nimi.
Kysymysmerkki ja asteriski ovat jokereita. Jos nimimerkki on asteriski, se saa virheellisen tuloksen 6525, koska asteriski vastaa mitä tahansa merkkijonoa. Vastaavasti kysymysmerkki vastaa mitä tahansa yhtä merkkiä, joten nimimerkki ? saa tuloksen jossa on mukana kaikki yksimerkkiset nimet, esimerkiksi ö. Nimimerkki ?? saa tuloksen jossa on mukana kaikki kaksimerkkiset, esimerkiksi RA.
Tämä on selvää pässinlihaa, arvattavissa ja helposti oikaistavissa, mutta <Default> kelpuuttaa riville 1187 asti minkä tahansa nimen ja sen jälkeen vain ne joissa nimi on <Default>.
Tähän en näe mitään järjellistä syytä. Alussa mainitsemassani virheessä sentään luku 65536 on kakkosen 16. potenssi joten sen voi olettaa olevan altis ohjelmoijan mokauksille (2 tavua on 16 bittiä, saattaa aiheuttaa jotain), mutta 1187 on ihan "rivinumero", jaoton tosin.
Kulmasulut ovat olennaiset. Ilman niitä ja kaari-, haka- ja aaltosulkuja käyttäen Default laskee oikein. Sen sijaan kulmasulkujen sisällä saa olla jotain muutakin. Jos vaihdan f:n tilalle kirjaimia g-m, tulos on sama, mutta n ja siitä eteenpäin antaa tuloksen 1192. <Renault> antaa tuloksen 5431! <Eki> on 2074, <RA> isoilla tai pienillä kirjaimilla 5418. <presidentti>, <presidentti Halonen> ja <presidentti Tarja Halonen> ovat 4457, pelkkä <Halonen> 2092, mutta <Tarja> ja <Tarja Halonen> 6047.
Mitään sääntöä en noista pysty kehittämään. Näyttää siltä että kolme ensimmäistä kirjainta ratkaisevat arvon. Arvo kuitenkin muuttuu jos on kolme kirjainta ja blankko, mutta palaa ennalleen jos blankon jälkeen lisätään kirjaimia. <iso> ja <iso S> antavat saman 2308, mutta <iso > on 2137. Kaksi blankkoa lukitsee arvon samaan kuin 3 + blankko, eli <iso S> on edelleen 2137, samoin <iso Sekaannus> (huom: tämä systeemi taitaa nielaista tuplablankot, joten ne eivät jääne näkyviin).
< > (yksi blankko) antaa 10, <> 6525 eli kelpuuttaa kaikki. Nyt äkkiä takki päälle, se valkoinen missä on pitkät selkäpuolelta solmittavat hihat!
2. kudos5.11.2007 klo 16:22
Exceliä ei ole, mutta OpenOffice näyttää laskevan soluun C1 ne A-sarakkeen solut, jotka ovat aakkosissa ennen merkkijonoa "Default>", jos solussa B1 on <Default>
3. iso S5.11.2007 klo 19:13
Kudos to kudos! Olisi pitänyt vilkaista funktion help-tekstiä ja todeta että se on monipuolisempi kuin olen tiennytkään. Tapa ilmaista vertailu on kyllä omaperäinen, olisi kuvitellut että pienempi kuin -merkki olisi sitaattien ulkopuolella. Help sanoo asian näin (minulla on englanninkielinen Excel):COUNTIF(range,criteria)
Range is the range of cells from which you want to count cells.
Criteria is the criteria in the form of a number, expression, or text that defines which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples".
Itse olisin määritellyt syntaksin niin että kaikki sitaattien sisässä oleva on merkijonoa, kuten normaalisti, ja mahdollinen erisuuruutta osoittava operaattori olisi ulkopuolella, siis tyyliin
=Countif(A$1:A$6525;<"<Default>")
laskisi pienemmät kuin <Default>,
=Countif(A$1:A$6525;"<Default>")
lne joiden arvo on <Default> ja
=Countif(A$1:A$6525;>="<Default>")
ne joiden arvo on <Default> tai suurempi.
Vaihtoehtoisesti operaattori olisi voitu rajata erottimella, siis
=Countif(A$1:A$6525;<;"<Default>")
ja joko pakottaa merkitsemään (luultavasti) yleisin yhtäsuuruusmerkki näkyviin tai sallia sen poisjättäminen:
=Countif(A$1:A$6525;;"<Default>")
Valittu tapa johtaa vaikeuksiin jos merkkijonon alussa on erisuuruusmerkki, ja lisäksi se on niin epätavanomainen että on omiaan aiheuttamaan hämmennystä.
4. airisto5.11.2007 klo 19:30
Pieni kikka: Kun haluat äkkiä laskea esim. tekstitiedostosta, kuinka usein joku merkkijono, esim. <Default> siinä esiintyy, niin käytä find/replace-toimintoa. Se ilmoittaa, kuinka monta "korvausta" se teki. Sitten undo.
5. airisto5.11.2007 klo 19:31
Suomeksi kai Etsi/Korvaa ja Peru
6. kudos5.11.2007 klo 22:28
Kokeile airiston kikkaa vaikkapa windowsin muistiolla (notepad).Kirjoita parikymmentä riviä x-kirjaimia. Korvaa ne o-kirjaimilla.
Kuinka monta korvausta ohjelma ilmoitti tehneensä?
Mitä tapahtui, kun valitsit undo (ctrl-Z)?
Kokeile samaa wordpadilla. Miten sujuu laskenta? Entä undo?
Miten kikka toimii linuxissa vaikkapa gedit-editorilla?
Sitähän minäkin. Kikka toimii ohjelmissa, jotka toimivat kuten kikan toimivuus edellyttää.
(Undo on tarpeeton, kun merkkijonon korvaa samalla merkkijonolla.)
7. Ari5.11.2007 klo 22:31
Mulla ei ilmoittanut mitään lukumäärää.
8. Libero5.11.2007 klo 22:48
Tuossa kudoksen kommentissa on 31 o-kirjainta. Pyysin korvaamaan kaikki o-kirjaimet ö-kirjaimilla, ja samalla sain lukumäärän. :)
9. iso S6.11.2007 klo 08:47
Mitä tarkoittaa home? Riippuu kielestä. Englannissa koti, suomessa joissakin kodeissa oleva ongelma. Vastaavasti kikkojen, kaavojen ja muiden temppujen toimivuus riippuu siitä mistä ohjelmasta puhutaan.Otsikossa puhutaan Excelistä, eikä Excelin toiminta ole sidottu muiden ohjelmien toimintaan ja päinvastoin. Airiston kikka on Excelissä ihan hyvä, jos haluaa tietää yhden tietyn merkkijonon esiintymiskertojen määrän. Kikan arvo Excelissä ei himmene siitä että Notepadille ja Wordpadille solun käsite on tuntematon ja Countif(A$1:A$6525;<;"<Default>") pelkkä merkkijono eikä suoritettava kaava.
Esittämäni kaava vaatii enemmän osaamista ja sen hyöty tulee esiin vasta sitten kun halutaan tietää useiden merkkijonojen esiintymiskerrat ja nuo merkkijonot saadaan tavalla tai toisella helposti listatuksi. Silloin kaavaa kopioimalla pääsee säästämään näppäilyvaivoja ja välttämään kirjoitusvirheitä.
Sinänsä on (ainakin joidenkin) mielestä mielenkiintoista vertailla periaatteessa samankaltaisten toimintojen eroa eri ohjelmissa, ja olisi hyödyllistä jos toiminta olisi mahdollisimman samanlainen. Silloin yhden ohjelman osaamista voisi hyödyntää suoraan toisissa, ja aika paljon tällaista standardointia Windows-maailmassa onkin Esimerkkinä vaikkapa leikepöydän käyttö. Useista ohjelmista löytyy Edit-valikon alta Copy ja Paste ja niitä vastaavat näppäinkomennot Ctrl-C ja Ctrl-V.
Undo eli Peruuta on hyvä esimerkki huonosta standardoinnista. Luonnollinen ajatus on että sillä peruutetaan viimeksi tehty toimenpide. Excel, Notepad ja Wordpad ovatkin tästä yhtä mieltä, mutta kukin eri mieltä siitä mikä on viimeksi tehty toimenpide, kun käyttäjä on esimerkiksi korvannut kaikki o:t ö-kirjaimilla.
Excel: käyttäjä korvasi o-kirjaimet ö-kirjaimilla. Siis palautan kaikki muuttuneet o:t. Jätän toki rauhaan kaikki alkuperäiset ö:t.
Wordpad: Käyttäjä teki sarjan muutoksia o->ö. Peruutan viimeisen eli palautan viimeksi muuttuneen o:n. Jos käyttäjä peruuttaa lisää, palautan edelliset o:t yksi kerrallaan.
Notepad: Käyttäjä teki sarjan muutoksia. Peruutan viimeisen eli palautan viimeksi muuttuneen o:n. Jos käyttäjä peruuttaa lisää, peruutan peruutuksen ja annan ö:n takaisin.
Wordpad ja Notepad eivät ilmoita korvattujen lukumäärää, mutta se ei estä Exceliä tekemästä niin.
Undo on tarpeeton, jos merkkijonon korvaa samalla merkkijonolla, mutta mikä on "sama merkkijono"? Excelin voi pakottaa huomaamaan eron isojen ja pienien kirjaimien välillä. Ellei tee niin ja muuttaa o:t o-kirjaimiksi, sekä isot että pienet o:t muuttuvat, saadaan niiden yhteislukumäärä. Undo tarvitaan, jos alkuperäinen muoto halutaan säilyttää.
Jos laitetaan rasti ruutuun "Match case", o:n muuttaminen o:ksi "muuttaa" vain pienet o:t ja undo on tarpeeton, mutta isot O:t jäävät laskematta. Voi olla että juuri niin halutaankin, voi olla että haluttaisiin laskea molemmat.
Ohjelmat ja tilanteet vaihtelevat. Juuri siksi tietokoneen käyttö vaatii tarkkuutta, käyttipä sitä tai ei :-)
10. iso S6.11.2007 klo 10:08
Notepadissa ja Wordpadissa ei siis voi käyttää korvaamista lukumäärän laskemiseen. Jostain muusta syystä saattaa kuitenkin olla tarpeellista korvata jokin merkkijono tilapäisesti, jolloin undon pihtaileva toiminta on kiusallinen ja joudutaan tekemään vastakkainen korvaus.Jos korvaaminen on tehty tarkoituksella ja vakaasti harkiten, on syytä valita korvaavaksi merkkijonoksi sellainen jota ei taatusti tekstissä ennestään ole. Muuten vastakkainen korvaus muuttaa muutakin kuin alkuperäisen korvauksen tuloksena syntyneet. Korvaavan merkkijonon pituuden ei tarvitse olla sama kuin korvattavan, mikä helpottaa sopivan korvaajan valitsemista.
Tähän tilanteeseen joudutaan helposti silloin kun tulee aito eiku-ilmiö ja silloin ei tietenkään ole harkintaa käytetty korvaajan valinnassa. Olen ennenkin kertonut, miten hurjassa nuoruudessani huomasin käyttäneeni Basic-ohjelmassa hajamielisyyttäni Int-funktiota tilanteessa jossa olisi pitänyt käyttää Abs-funktiota. Arvelin että sama virhe on voinut toistua pitkähkössä ohjelmassa muuallakin. Nokkelana poikana en tuhlannut aikaa etsimiseen vaan korvasin INT -> ABS. Väärin toiminut ohjelma muuttui toimimattomaksi ohjelmaksi, koska kaikki PRINT-lauseet muuttuivat Basicille tuntemattomiksi PRABS-lauseiksi.
Tuossa tilanteessa oli pelastus saatavilla, koska ohjelmassa ei alunperin taatusti ollut merkkijonoa prabs. Voin siis tehdä korvauksen PRABS -> PRINT, jolloin virheellisesti muuttuneet komentosanat palautuivat ennalleen ja muutetut funktionnimet säilyivät muutettuina. Alunperin olisi pitänyt ajatella ensteks ja korvata INT( -> ABS(, koska funktion nimen jälkeen on aina alkusulku ja PRINT-komentosanan jälkeen välilyönti.
Oppia ikävä kaikki.
Kun siirtää tietoa Wordpadista leikepöydän kautta Exceliin, saattaa joutua tekemään korvaustemppuja. Esimerkiksi sitaattimerkillä ("), yhtäläisyysmerkillä tai miinusmerkillä alkavien rivien aiheuttamia ongelmia voi testata.
Melkein kaikesta selviää sillä että ennen korvaamista formatoi Excelissä sarakkeen text-muotoiseksi ja korvaa merkkijonona eli Edit - Paste special - Text. Sitaattimerkillä alkavista riveistä koituu edelleen harmia.
Rivin alkava sitaattimerkki ja rivillä mahdollisesti oleva seuraava sitaattimerkki katoavat. Myöhemmät sitaattimerkit säilyvät. Sitaattimerkit säilyvät silloinkin kun rivi ei ala sitaattimerkillä mutta niitä on myöhemmin.
Pahinta on se jos rivi alkaa sitaattimerkillä mutta rivillä ei ole toista sitaattia. Silloin kaikki seuraavat Wordpadin rivit menevät samaan Excelin soluun kunnes sitaattimerkki löytyy! Rivinvaihtojen tilalle merkkijonoon tulee merkki joka näkyy pienenä neliönä. Sen numeroarvo on 13. Rivin alkanut sitaattimerkki ja seuraava sitaattimerkki katoavat, olipa seuraava sitaattimerkki rivinsä alussa, keskellä tai lopussa (muut sillä rivillä olevat sitaattimerkit säilyvät).
Siksi ennen siirtämistä pitää muuttaa sitaattimerkit sellaiseksi merkkijonoksi jota ei tekstissä muuten ole. Vielä tähänkin jää ongelma: Excelissä takaisin muuttaminen ei onnistu, jos korvattavan merkin sisältävän merkkijonon pituus on yli 911 (mielenkiintoinen hälytysraja!). Pitemmätkin merkkijonot Excel suvaitsee napisematta, jos niissä ei ole korvattavaa. Nuo ongelmalliset pitää muuttaa yksitellen käsin.
Yksi keino kiertää ongelmat on se että tallettaa Wordpad-tiedoston txt-muotoisena ja avaa sen Excelissä niin että määrittelee Delimited - Tab, Text qualifier = None ja sarake txt-muotoiseksi. Silloin kaikki tulee kiltisti, mutta pitää kulkea tylsän välitiedoston kautta. Se on vieläkin keljumpaa jos haluaisi vain osan Wordpad-tiedoston sisällöstä.
11. iso S20.11.2007 klo 11:05
Yhteensopivuus on hieno asia, etenkin jos sitä noudatetaan.Excelissä on Trim-funktio jolla voi siivota tekstisolusta loppublankot pois. Samalla katoavat myös alkublankot jos niitä on ja tekstin sisällä olevat peräkkäiset blankot nuuttuvat yhdeksi blankoksi. Nämä sivuvaikutukset saattavat olla toivottuja tai kiusallisia, mutta ainakaan tekstin sisäisiin välilyönteihin puuttuminen ei yleensä kuulu Trim-funktion toimenkuvaan.
Excelin käyttämässä Visual Basic -makrokielessä on samaa hommaa varten kolme funktiota: Ltrim, Rtrim ja Trim. Ensimmäinen poistaa alkublankot, toinen loppublankot ja kolmas molemmat, mutta mikään näistä ei puutu tekstin sisällä oleviin peräkkäisiin blankkoihin. Trim ja Trim toimivat siis eri tavalla riippuen siitä onko kyseessä taulukon vai makron Trim. Varsin hämäävää ja joissakin tilanteissa kiusallista.
Eron huomaa esimerkiksi Lähettäjä-rivien käsittelyssä. Jos kopioi täältä juttuja teksturiin, lähettäjän nimen ja päiväyksen väliin tulee 2 blankkoa. Tämä on hyvä juttu, helpottaa päiväyksen aloituskohdan ohjelmallista tunnistamista. Jos riveistä siivotaan loppublankot Excelin funktiolla, tuo tuplablankko menetetään. Jos trimmitys tehdään makrolla, tuplablankko säilyy.
Jos syystä tai toisesta yrittää parittaa kahta tekstiä joista toinen on trimmitetty Excelin funktiolla ja toinen makron funktiolla, yksikään lähettäjä-rivi ei täsmää eivätkä muutkaan rivit joissa on peräkkäisiä blankkoja.
On se niin väärin mutta tietoista, dokumentoitu Help-teksteissä:
Trim Function (Excel)
Removes all spaces from text except for single spaces between words.
LTrim, RTrim, and Trim Function (Excel Visual Basic)
Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim).
Itse asiassa, jos lukee Excelin funktion määritelmää kuin iso S manuaalia, funktio toimii määrityksensä vastaisesti. Jos sanojen välissä on useita blankkoja, funktion pitäisi lupauksensa mukaisesti poistaa ne kaikki (tehdä yhdyssana) eikä jättää poistettujen tilalle yhtä välilyöntiä. Millä ihmeen oikeudella funktio päättää että joku tietty niistä peräkkäisistä välilyönneistä on "single space" ja muut eivät?
12. iso S20.11.2007 klo 11:39
Edellä viittasin siihen että tuplablankko lähettäjä-rivillä on hyvä asia. Sen avulla voi pienellä askartelulla tehdä lähettäjistä tilastoa.Esimerkiksi, kopioidaan Alkukirjainketju -115 Exceliin.
Lajitellaan ja poistetaan muut kuin Lähettäjä-rivit (käy näppärästi kun Lähettäjä-rivit ovat peräkkäin. Saadaan 257 riviä.
Valitaan sopiva esimerkki, jota ei esiinny lähettäjäriveillä (varmistetaan Find-toiminnolla). Tässä tapauksessa kauttaviiva kelpaa. Korvataan tuplablankot Replace-toiminnolla kyseisellä erottimella ja teksti "Lähettäjä : " tyhjällä.
Huomioidaan että Excelin ilmoittama korvausten määrä täsmää rivien määrään (257). Jos tulos on suurempi, joku huolimaton tai huumoripelle on laittanut nimimerkkiinsä tuplablankon. Nämä täytyy käydä korjaamassa käsin.
Nyt meillä on rivejä jotka ovat muotoa "nimi/päiväys kellonaika". Erotellaan osat maalaamalla sarake ja käyttämällä toimintoa Data/Text to columns.../Delimited ja määrittelemällä erottimeksi Other, / (se valitsemamme erotin).
Nyt meillä on lista lähettäjistä ja lukumäärät voi laskea haluamallaan tavalla. Yksi tapa on käyttää countif-funktiota. Jos nimelista on sarakkeessa A ja päiväykset sarakeessa B, kirjoitetaan soluun C1 kaava
=Countif(A:A;A1)
Tämä laskee, kuinka monta kertaa A1-solussa oleva arvo "[Hakro]" esiintyy A-sarakkeella: 2 kertaa. Kopioidaan kaava c-sarakkeelle niin pitkälle kuin nimiä riittää, jolloin saadaan kaikkien esiintymiskerrat.
Useammin kuin kerran esiintyneillä on useita rivejä, mutta jokaisella on oikea arvo. Tuplista pääsee eroon seuraavasti:
kirjoitetaan soluun D1 kaava
=IF(A1=A2;"Poistettava";"Ok")
ja kopioidaan tämä D-sarakkeelle niin pitkälle kuin nimiä riittää. Tämä merkkaa tuplarivit arvolla "Poistettava" ja nimen viimeisen esiintymän arvolla "Ok". Rivithän oli lajiteltu joten kaikki saman nimen esiintymät ovat peräkkäin.
Nyt kopioidaan kaava leikepöydälle ja liitetään takaisin arvona. Silloin koko roska voidaan lajitella D-sarakkeen mukaan ja tuplarivit jäävät joukon hännille. Jos vielä C-sarakekin kopioidaan itsensä päälle arvona, tuplarivit voi poistaa. kaavaa ei muuteta arvoksi, rivien poistamisen jälkeen jokaisen lukumäärä muuttuu ykköseksi ja sitten harmittaa.
Nyt voi vielä lajitella rivit laskevasti C-sarakkeen mukaan, jolloin saadaan järisyttävä havainto:
Hakro: 47
RA: 42
Arska: 35
mor: 30
EJlo: 19
Miimu: 18
matts: 13
iso S: 11
Tsööts: 10
Jaska: 6
noname: 6
Arska™: 3
tero: 3
[Hakro]: 2
noname 1: 2
[ra]: 1
Eki: 1
izmo: 1
J. Rutakainen: 1
jepsjuu: 1
juepsju: 1
Juhani Heino: 1
Matias-Myyrä: 1
nassakka: 1
Sini: 1
Hakro voitti säikeen, AKK-kuningatar RA velttoili toiseksi!
13. Ari20.11.2007 klo 12:18
iso S, joko ohjelmassasi on virhe tai se on tarkoitettu näin, mutta lähettäjistä löytyy yksi Noname-nimimerkkikin.(Tämä oli laskettu tilastossasi noname:n nimiin.)
14. iso S20.11.2007 klo 13:28
Enemmän tai vähemmän tarkoituksella. Excelissä sekä Countif että If pitävät isoja ja pieniä kirjaimia samanarvoisina. Mahdollisesti tämä on jostain optiosta parametroitavissa.Toisaalta merkkijonon etsimiseen toisesta merkkijonosta on kaksi funktiota, Find ja Search. Näistä toinen erottelee isot ja pienet kirjaimet, toinen ei. Ovat kuin Matti ja Teppo, koskaan en opi muistamaan kumpi on kumpi. Funktioiden osalta eron voi luntata Help-tekstistä jos se on tarpeen. Find on se nirsompi.
Yleensä näissä nimimerkeissä on kyseessä sama henkilö, olipa kirjoitus isoilla tai pienillä, joten esittelemälläni tavalla summaus menee automaattisesti enimmäkseen oikein. Eri asia on että kirjoitusmuodoista joku (esimerkiksi RA, ra, RA) saattaa olla kirjoittajan haluama ja muut lipsauksia.
Tässä taitaa päteä sääntö "viimeksi tullutta ensiksi palvellaan", koska lajittelukaan ei ota kantaa pienten ja isojen kirjainten paremmuuteen. Saman nimimimerkin rivit menevät aikajärjestykseen ja niistä valikoituu listalle viimeinen. Se on oikein, sehän on kirjoittajan viimeinen tahto!
15. iso S8.4.2022 klo 18:06
Excel pääsi taas yllättämään.Minulla oli suuri määrä sanoja jotka halusin konvertoida "ristikkomuotoon" eli sellaiseksi, että niissä on vain kirjaimia, ja nimeomaan suomalaisen aakkoston kirjaimia. Siis kaikki väliviivat ja apostrofit pois. Tämä käy helposti korvaa-toiminnolla (ctrl-H). Sitten pitäisi vielä korvata kaikenkarvaiset koristellut kirjaimet (É ja vastaavat) karvalakkimallilla. Siihenkin tietysti toimisi korvauksen toistaminen, jos vain tietäisi mitä kaikkia aksentteja ja muita lisukkeita kirjaimiin on lisätty.
No, olin tehnyt aiemmin makron joka hoitaa homman yhdellä iskulla, kunhan vain laittaa yhteen muuttujaan korvattavat merkit ja toiseen niiden vastineet. Korvauksen tarpeessa olevat merkit tunnistin toisella makrolla, joka merkkaa toiseen sarakkeeseen jokaisesta ulkomaista geeniperimää sisältävästä sanasta ensimmäisen merkin joka ei ole kirjain. Valmiin listan sain kun siivosin sarakkeen Poista kaksoiskappeleet -toiminnolla (nykyinen Excel-versioni on suomenkielinen).
Kun merkkasin jokaiselle korvattavalle merkille korvaajan ja ajoin makron, kaikkien sanojen kaikki kirjaimet muuttuivat Z-kirjaimiksi. Ennen se olisi ollut Zorron merkki, nytkyään enemminkin sorron merkki. Joka tapauksessa tämä ei ollut se mitä hain.
Sanalistastani löytyi seuraavat eksoottiset merkit, perässä Koodi-funktiolla saatava merkin numeroarvo:
Á 193
À 192
 194
à 195
Æ 198
? 63
Ç 199
Ð 208
É 201
È 200
Ê 202
Ë 203
Í 205
Ì 204
Î 206
Ï 207
? 63
? 63
Ñ 209
Ó 211
Ò 210
Ô 212
? 63
Õ 213
Ú 218
Ù 217
Û 219
Ý 221
? 63
Ø 216
Tarkkaavainen silmä huomaa, että viidellä merkillä on sama numeroarvo 63. Se sattuu olemaan myös kysymysmerkin numeroarvo. Minä käytin omia silmiäni, en tarkkaavaista silmää, enkä huomannut että makroon kopioidessani nuo viisi merkkiä muuttuivat kysymysmerkeiksi. Excelin funktiot ja Visual Basic (Excelin makrokieli) toimivat ilmeisesti kahdeksan bitin moodissa ja noiden merkkien numeroarvo on jotain muuta kuin 1-255. Taulukossa Excel pystyy säilyttämään ne kakistelematta, mutta numeroarvon se tulkkaa väärin.
Kurjuuden kruunaa se, että makrossa käyttämäni Selection.Replace (Visual Basic on englanninkielinen, vaikka Excel on suomenkielinen) tulkitsee kysymysmerkin jokerimerkiksi, jolle kelpaa mikä tahansa merkki. Näin ollen jokainen merkki kokee vähintään viisi muunnosta, joista vähintään neljä on vääriä. Listan 5 ensimmäistä merkkiä kokevat ensin oikean muunnoksen ja sitten 5 väärää jokerimerkistä johtuvaa muunnosta. 63-arvoiset kokevat 4 väärää muunnosta ja yhden oikean. Ø on epäonnekkain, se kokee vain 5 väärää. Sen osalta peli on menetetty ennen kuin sen oikeaa muunnosta päästään yrittämään, ei oo enää Ø;ta, vaan Z. ? on viimeinen 63-arvoinen ja siksi ainoa onnekas jolle jää voimaan haluttu muunnos.
Taas toteutui kaksi vanhaa viisautta: tietokoneen käyttö vaatii tarkkuutta, käyttipä sitä tai ei ja oppia ikävä kaikki..
16. Ari8.4.2022 klo 20:32
Olisiko tästä ollut apua?https://answers.microsoft.com/en-us/msoffice/forum /all/how-to-open-utf-8-csv-file-in-excel-without-m is/1eb15700-d235-441e-8b99-db10fafff3c2
17. iso S8.4.2022 klo 21:33
Jaa, enpä usko eikä tässä ole kysymys mistään japanilaisista tai kiinalaisista merkeistä, enkä yritä avata mitään CSV-tiedostoa. Ne ongelmalliset merkit lähtivät täältä itsensä näköisinä, mutta muuntautuivat sinne saapuessaan kyssäreiksi.Otetaan esimerkki Wikipediasta. Yksi niistä 63-merkeistä oli L, jossa on keskellä vartta vinoviiva. Liittyi varmaan johonkin puolalaiseen paikannimeen. Toinen on N, jonka päällä on oikealle kallistuva aksenttimerkki tai mikä hänen nimensä onkaan. Sellainen löytyy mm. Gdanskista.
https://fi.wikipedia.org/wiki/Gda%C5%84sk
Eipä halua linkki näkyä samassa muodossa kuin selaimen osoitekentässä, mutta ei masennuta.
Kun tuon linkin avaa, sieltä voi maalata kaupungin nimen, kopioda ja liittää Excel-taulukkoon. Täpällinen n selviää hengissä. Siitä voi kopioida pelkän n-kirjaimen toiseen soluun ja koriste pysyy päässä. Kun nyt kysyy Koodi-funktiolla merkin numeroarvoa, se on 63. Kun kysyy kysymysmerkin (?) numeroarvoa, sekin on 63. Japania, Kiinaa ja CSV-tiedostoa ei ollut näköpiirissä ja silti kahdesta eri merkistä tuli sama koodi.
Tämä ei ole minulle käytännön ongelma, noita erikoisesti käyttäytyviä merkkejä on vain muutamassa sanassa ja ne voin hoitaa yksitellen käsin. Periaatteellisella tasolla kiinnostaa, miksi Excel ei selviä niistä kuivin jaloin.
18. Ari9.4.2022 klo 07:13
Käyttääköhän Excel ANSI-muotoa? Ainakin jos muistiossa tallentaa ANSI- muodossa, erikoisemmat merkit vaihtuvat kyssäreiksi, mutta tallennettaessa kyllä kysyy tallennetaanko Unicode- muodossa ettei tule muutoksia. Tai voi varmaan tallentaa myös UTF-8 -muodossa että merkit säilyttävät alkuperäisen muotonsa. Voiko tuota käytettävää merkistöä muuttaa jostakin?
19. Ari9.4.2022 klo 07:40
Voit myös kokeilla muuttaa nuo erikoisempia kirjaimia sisältävät samat eri muotoon tuossa osoitekentässä. Laita osoitteen perään #ErikoismerkkisanaEsim:
http://sanaristikot.net/keskustelut/?ryhma=1#Käpp yrä
Muuntuu muotoon:
http://sanaristikot.net/keskustelut/?ryhma=1#K%C3% A4ppyr%C3%A4
Tuon lopun sitten kopioit Exceliin. Toimiiko?
20. iso S9.4.2022 klo 11:36
Gdanskia käsittelevän Wikipedia-artikkelin osoite näkyy selaimen osoitekentässä muodossa"https://fi.wikipedia.org/wiki/Gda?sk"
jonka koristeltu n-kirjain ilmeisesti muuttuu kysymysmerkiksi kun lähetän tämän viestin.
Kun kopioin tuon osoitteen Muistioon, se muuttuu muotoon
"https://fi.wikipedia.org/wiki/Gda%C5%84sk"
eli käsittääkseni vastaavaan muotoon kuin ehdottamallasi risuaitakikalla. Artikkelissa itsessään Gdansk säilyttää koristeensa. Jos talletan muistiotiedoston muodossa UTF-8, koriste säilyy hengissä (mutta osoitteen muuttunut mongerrus ei palaudu yhdeksi merkiksi) ja jos talletan muodossa ANSI, koriste katoaa. Tämä on itse asiassa lähellä sitä mitä tavoittelen, mutta haluan syödä ja säästää kakun, eli haluan vierekkäin sanapareja: alkuperäinen ja suomettunut sana.
Mitä tämä todistaa? Excelin suhteen ei mitään, selaimen toiminnasta jotain. Nettiosoitteissa ei ilmeisesti voi käyttää ANSI-koodistoon kuulumattomia merkkejä. Ne korvautuvat prosenttimerkkien sisällä olevalla koodauksella, jonka selain osaa palauttaa UTF-8-muotoon tai johonkin muuhun laajempaan koodistoon. Itse nettisivut toimivat eri logiikalla kuin nettiosoitteet, koska ne kopioituvat Muistioon eri tavalla kuin osoitteet.
Jos yritän käyttää risuaitakikkaa sillä tavalla kuin sen ymmärrän eli kirjoitan osoitteeksi
"https://fi.wikipedia.org/wiki/#Gda?sk"
(jossa koristeltu n muuttuu taas kysymysmerkiksi kun lähetän tämän) ja painan enter, osoite muuttuu muotoon
"https://fi.wikipedia.org/wiki/Wikipedia:Etusivu#G da%C5%84sk"
ja aukeaa sivu, jossa minua tervehditään ystävällisesti ja pyydetään apua:
Tervetuloa Wikipediaan, vapaaseen tietosanakirjaan.
Suomenkielisessä Wikipediassa on tällä hetkellä 528 763 artikkelia.
...
Miten voit auttaa
Wikipedia tarvitsee apuasi!
Tällä ei edelleenkään ole mitään tekemistä Excelin kanssa enkä ymmärrä, mitä minä kostuisin siitä että kopioisin Exceliin osoitteen muunnetun häntäosan, kun kerään alkuperäisiä sanoja.
Koodifunktion selitys johtaa Excelin (tai ainakin tämän funktion) käyttämän merkistön jäljille:
KOODI(teksti)
Palauttaa tekstijonon ensimmäisen merkin numerokoodin tietokoneen käyttämässä merkistössä.
Excelin asetuksista en löydä mitään keinoa puuttua koodistoon.
Tulee mieleen professori Reino Kurki-Suonion luento, jossa hän puhui ortogonaalisuudesta. Jos systeemissä on kaksi ominaisuusluokkaa, joilla on erilaisia jäseniä ja ne järjestetään taulukoksi niin että toinen luokka on riveinä ja toinen sarakkeina, niin ortogonaalisessa järjestelmässä jokaisella sarakkeen rivillä pitää olla rasti jos jollakin rivillä on rasti. Otetaan esimerkiksi taulukko jossa riveinä ovat viikonpäivät ja sarakkeina auton osat. Sarakkeessa "startti" pitää olla rasti jokaisen viikonpäivän kohdalla. Se ei käy, jos auto käy kaikkina päivinä muina päivinä mutta ei maanantaina.
Excelissä ja sen makrokielessä tuo ortogonaalisuus ei toteudu. Taulukon näytössä se osaa näyttää nuo ANSI-koodiston ulkopuoliset merkit ja esimerkiksi Etsi-funktio osaa löytää ne, mutta Koodi-funktio ei niitä hallitse ja Visual Basic mokaa ne jo koodia kirjoittaessa.
Kiitos hyvästä yrityksestä, mutta taitaa olla niin että toinen puhuu oopperasta ja toinen oopperan seipäistä. Nyt tiedän tuon harvoin vastaan tulevan ongelman ja pystyn hoitamaan sen.
21. Ari9.4.2022 klo 11:49
Tuo risuaitamerkin käyttäminen osoitteen välissä käytännössä katkaisee osoitteen siihen, ja sen jälkeen oleva teksti on sivulla olevan kohdan id- arvo johon selain kelaa sivun, mikäli tuo kyseinen id- arvo sieltä löytyy, kuten tähän asti viimeisin viestisi löytyy osoitteesta ja kohdasta http://sanaristikot.net/keskustelut/?id=3675#10501 74
22. iso S9.4.2022 klo 13:41
Tuntuu siltä että toistan itseäni: miten tuo liittyy mihinkään yleensä ja erityisesti esittämääni ongelmaan? Jos muistan että jossakin on puhuttu tiettyihin merkkeihin liittyvästä ongelmasta, niin miten voin siitä päätellä tuon maagisen id:n? Jollakin minulle tuntemattomalla tavalla tuon id:n epäilemättä pystyy saamaan selville jos on jo kyseisessä paikassa, mutta eihän sitä silloin tarvitse enää etsiä!
23. Ari9.4.2022 klo 14:23
Se liittyy esittämääsi viittaukseen wikipedian linkkiin, joka ei toiminut odottamallasi tavalla. Mutta tuo oli vain sivujuonne keskustelussa, joka liittyy merkistökoodausongelmaasi. Tuolla selaimen kenttään tuollalailla sijoitettavalla sanalla pystytään muuttamaan merkistökoodaus ANSI- muotoon, jota ilmeisesti Excel käyttää. Itse en voi asiaa testata kun ei minulla ole käytettävissä kyseistä ohjelmaa.
24. Ari9.4.2022 klo 14:39
Jos kokeilet avata tuon viestini 21 linkin, huomaat että selain etsii SINULLE tuon id:n osoittaman kohdan sivussa.
25. Pete9.4.2022 klo 16:20
Iso S analysoikin jo alkuperäisen ongelman ja sen mikä sen aiheuttaa. Sehän on se vaikein vaihe yleesä virheiden metsästyksessä. Itse en Exceliä käytä, mutta Libreofficen Calc ainakin väittää, että CODE (suom. KOODI) toimii luotettavasti vain ASCII-merkistöllä (eli ei sisällä mitään erikoismerkkejä, kuten ääkkösiä).Halutut funktiot olisivat siis UNICODE ja UNICHAR, mitkä niiden suomenkieliset nimet sitten ovatkaan (UNIKOODI? :). Tuo pari osaa käsitellä oikein kaikki erikoismerkit. Tosin, jos haluaa käsitellä merkkijonoja, niin en ymmärrä miksi niitä täytyy välillä muuttaa numeroiksi, mutta jos se toimii, eipä siinä mitään.
Nettiosoitteiden (URL) koodaus ei tosiaan liity tähän mitenkään, siitä voi lukea lisää vaikka englanninkielisetä wikipediasta https://en.wikipedia.org/wiki/Percent-encoding , jos kiinnostaa.
26. iso S10.4.2022 klo 13:33
Nuo uniset funktiot ovat suomenkielisessä Excelissä UNICODE ja UNICODEMERKKI. Gdanskin ännä saa åedellistä käyttämällä arvon 324, kun Koodi tarjosi samaa arvoa kuin kysymysmerkille, 63. Kysymysmerkin Unicode on 63, sama kuin Koodi.Miksi niitä sitten pitäisi muuttaa numeroiksi? Ei välttämättä tarvitsekaan, mutta se on yksi tapa jolla voin selvittää mitkä merkit eivät käyttäydy kiltisti ja näin pystyn välttämään edellä kuvaamani katastrofin. Pääosa sanastostani on kulkenut txt-tiedoston kautta, jolloin vaara on eliminoitu, mutta olen hukannut tiedon alkuperäisestä kirjoitusasusta. Ne harvat Exceliin asti selvinneet häiriköt ovat tulleet muuta reittiä, esimerkiksi netistä suoraan kopioimalla.
27. Ari10.4.2022 klo 15:25
Tekstitiedoston voi tallentaa myös Unicode- koodauksella jolloin merkkejä ei kait pitäisi kadota tai muuntua.
28. iso S10.4.2022 klo 19:09
Tuon olin jo oppinutkin, vaikka se ei ogelmaa ratkaisekaan:Lainaus: 20. iso S 9.4.2022 klo 11:36
Jos talletan muistiotiedoston muodossa UTF-8, koriste säilyy hengissä (mutta osoitteen muuttunut mongerrus ei palaudu yhdeksi merkiksi) ja jos talletan muodossa ANSI, koriste katoaa.
************************************************** ************************************
Kun itse kysyy ja itse vastaa niin ymmärtää kysymyksen ja vastauksen, jos ei muuten niin edes väärin. Kun hihani käärin ja hetkisen häärin niin löysin tämän:
Excelforum kertoo:
VBA by its nature is ANSI based. However, strings in Windows are all Unicode. The Windows operating system makes the conversion from Unicode to ANSI automatically for strings. So, to include Unicode characters in a VBA string, you can use the Character Wide (ChrW) function.
ChrW siis palauttaa Unicode-merkin, jos sille antaa merkin numerokoodin. Toisesta osumasta selvisi, että sen vastakappale on vähemmän yllättävästi AscW ("Leveä Ascii"). Kun sille syöttää merkin, se palauttaa vastaavan numerokoodin.
Excelin puolella olin selvittänyt Unicode-funktiolla, että Gdanskin ännän numerokoodi on 632. Nyt minulla on onnen avaimet käsissäni. Yksinkertainen testimakro rakentaa taulukon vasempaan yläkulmaan puolalaisen kaupungin nimen, jossa ännällä on sulka päässä:
Sub Testi()
Cells(1, 1) = "Gda" + ChrW(324) + "sk"
End Sub
Toinen testimakro osoittaa, että sulkapäisen nimen voi napata käsittelyyn ja korjata kaljupäiseksi:
Sub Testi2()
'
Dim Teksti, Korvattava, Korvaava As String
Dim Merkki, Ind As Integer
'
Korvattava = ChrW(324)
Korvaava = "n"
'
Teksti = Cells(1, 1)
Ind = InStr(Teksti, Korvattava)
If Ind > 0 Then
Mid(Teksti, Ind) = Korvaava
Cells(2, 1) = Teksti
Else
Cells(2, 1) = "Ei korvattavaa"
End If
End Sub
Alkuperäisen nimen alapuolelle putkahtaa Gdansk. Jos laitan ykkösruutuun jotain muuta, esimerkiksi Ari, kakkosruutuun tulee teksti Ei korvattavaa. Tietokone tietää. Kiitos avusta, korvausta ei ole odotettavissa!
Muuttujaan "Korvattava" voi sijoittaa ANSI-merkit suoraan ja muut samalla tavalla plussaamalla kuin nimen rakentaminen ensimmäisessä testissä. Muuttujaan "Korvaava" voi sijoittaa kaikki vastaavat riisutut mallit suoraan. Korvaustempun ympärille pitää rakentaa luuppi joka käy kaikki korvattavat merkit läpi. Tämähän ei tietysti vielä riitä, jos sanassa on kaksi taiuseampi samaa korvattavaa merkkiä, mutta sen jätän halullisten sielujen ratkaistavaksi. Ei ole vaikeaa.
29. iso S10.4.2022 klo 19:16
Aina niitä virheitä jää. Kopioin virheellisen koodin, joka vastoin odotuksia kyllä toimii. Mid-funktiolle pitäisi ilmoittaa käpälöitävä merkkijono, alkukohta ja pituus. Korrekti muoto on siisMid(Teksti, Ind, 1) = Korvaava
Ihan eri ongelna on se että sormissa ei aina ole tarpeeksivoimaa. Liian hento näppäys söi ongelmasta ännän.
KOMMENTOI