XMATCH<\/code> on uudempi versio, joka tukee hakusuuntaa ja binaireja hakuja selke\u00e4mmin.<\/li>\n<\/ul>\nMilloin INDEX\/MATCH<\/code> edelleen kannattaa? Kun haluamme \u00e4\u00e4rimm\u00e4ist\u00e4 suorituskyky\u00e4 isoissa tiedostoissa tai tehd\u00e4 monimutkaisia 2D-hakuja yhdist\u00e4m\u00e4ll\u00e4 rivi- ja sarakehakua (esim. INDEX(data, MATCH(kriteeri, rivit, 0), MATCH(kolumni, sarakkeet, 0))<\/code>).<\/p>\nDynaamiset haut riveitt\u00e4in ja useilla ehdoilla<\/h3>\n\n- Moniehtoinen haku
XLOOKUP<\/code>illa: yhdistet\u00e4\u00e4n ehdot avain-sarakkeeksi.<\/li>\n- Esim. apusarakkeessa `=A2&”<\/li>\n<\/ul>\n
|”&B2, ja haku <\/code>=XLOOKUP(E2&”|<\/p>\n“&F2, apuSarake, Palautus)`.<\/p>\n
\n- Ilman apusaraketta: dynaaminen suodatus
FILTER<\/code>illa ja tarvittaessa viimeinen arvo @INDEX<\/code> tai TAKE<\/code>\/DROP<\/code>:<\/li>\n=INDEX(FILTER(Taulukko[Hinta],(Taulukko[Koodi]=E2)*(Taulukko[Alue]=F2)),1)<\/code>.<\/li>\n<\/ul>\nViittaukset, alueet ja nimialueet<\/h3>\n\n- Absoluuttinen vs. suhteellinen viittaus:
$A$2<\/code> ei muutu kopioitaessa, A2<\/code> muuttuu. Pikan\u00e4pp\u00e4in F4.<\/li>\n- Nimetyt alueet: Kaavat > Nimiruutu. K\u00e4ytt\u00f6:
=SUM(Myynnit)<\/code> \u2013 lis\u00e4\u00e4 luettavuutta ja v\u00e4hent\u00e4\u00e4 virheit\u00e4.<\/li>\n- Excel-taulukot (Ctrl+T) ja rakenteelliset viittaukset:
Taulukko[Myynti]<\/code> skaalautuu automaattisesti uusiin riveihin.<\/li>\n<\/ul>\nErikoistapaukset: INDIRECT ja OFFSET \u2013 varoen<\/h3>\n\nINDIRECT<\/code> muuntaa tekstin viittaukseksi: hy\u00f6dyllinen koontin\u00e4kymiss\u00e4, mutta tekee kaavoista haihtuvia (volatile) ja suorituskyky k\u00e4rsii. K\u00e4ytet\u00e4\u00e4n harkiten.<\/li>\nOFFSET<\/code> siirt\u00e4\u00e4 viittausta suhteessa ankkuriin. Sama varoitus: haihtuva. Mieluummin INDEX<\/code> dynaamisessa alueessa, esim. =SUM(INDEX(C:C,2):INDEX(C:C,MATCH(\"zzz\",A:A)))<\/code>.<\/li>\n<\/ul>\n\u00a0Laskenta ja ehdollinen logiikka<\/h2>\n
Ehdollinen logiikka on se liima, joka erottaa raportin \u00e4lytaulukosta.<\/p>\n
IF, IFS ja SWITCH<\/h3>\n\nIF(ehto, jos_tosi, [jos_ep\u00e4tosi])<\/code> on peruspalikka. Pid\u00e4mme kaavat lyhyin\u00e4 \u2013 jos sis\u00e4kk\u00e4isi\u00e4 IF<\/code>-ehtoja on yli 2\u20133, hajotamme logiikan tai k\u00e4yt\u00e4mme IFS<\/code>.<\/li>\nIFS(ehto1, tulos1, ehto2, tulos2, \u2026)<\/code> lukee luonnollisemmin useita ehtoja.<\/li>\nSWITCH(lauseke, arvo1, tulos1, arvo2, tulos2, [oletus])<\/code> toimii luokittelussa, kun vertaillaan yhteen muuttujaan.<\/li>\n<\/ul>\nEsimerkki luokittelusta:<\/p>\n
=IFS(pisteet>=90,\"Erinomainen\",pisteet>=75,\"Hyv\u00e4\",pisteet>=60,\"Tyydytt\u00e4v\u00e4\",TRUE,\"Kehitett\u00e4v\u00e4\u00e4\")<\/code><\/p>\nAND, OR, NOT \u2013 yhdistelyyn<\/h3>\n\nAND<\/code> ja OR<\/code> rakentavat selkeit\u00e4 ehtoja: =IF(AND(A2>0,B2<>\"\"),\"Ok\",\"Tarkista\")<\/code>.<\/li>\nNOT<\/code> k\u00e4\u00e4nt\u00e4\u00e4 ehdon: hy\u00f6dyllinen “ei kuulu t\u00e4h\u00e4n” -tapauksissa.<\/li>\n<\/ul>\nSummat ja laskennat ehdoilla<\/h3>\n\nSUMIFS<\/code>, COUNTIFS<\/code>, AVERAGEIFS<\/code> ovat raportoinnin peruskolmikko.<\/li>\n- Monimutkaiset ehdot (esim. sis\u00e4lt\u00e4\u00e4 tekstin, v\u00e4lilt\u00e4-arvot, p\u00e4iv\u00e4m\u00e4\u00e4r\u00e4ikkunat) onnistuvat yhdist\u00e4m\u00e4ll\u00e4 vertailuoperaattoreita ja jokerimerkkej\u00e4:
\"*teksti*\"<\/code>, \">=\"&AlkuPvm<\/code>.<\/li>\n<\/ul>\nMatriisilogiikka dynaamisissa funktioissa<\/h3>\n\n- Dynaamisissa kaavoissa ehdot kerrotaan toisiinsa (TRUE=1, FALSE=0):<\/li>\n
=SUM(FILTER(Taulukko[Summa],(Taulukko[Alue]=\"Etel\u00e4\")*(Taulukko[Kuukausi]>=Alku)*(Taulukko[Kuukausi]<=Loppu)))<\/code>.<\/li>\n- Vaihtoehtoisesti
SUMPRODUCT<\/code> toimii my\u00f6s ilman dynaamisia toimintoja: =SUMPRODUCT((Alue=\"Etel\u00e4\")*(Kuukausi>=Alku)*(Kuukausi<=Loppu)*Summa)<\/code>.<\/li>\n<\/ul>\nLukituksen ja validoinnin perusajatus<\/h3>\n\n- Suojaa logiikka: lukitse kaavasarakkeet ja pid\u00e4 sy\u00f6t\u00f6t omissa soluissaan, mieluiten v\u00e4rikoodilla. Datan validointi (esim. alasvetolistat
UNIQUE<\/code>-arvoista) v\u00e4hent\u00e4\u00e4 virheit\u00e4 heti.<\/li>\n<\/ul>\nPro-vinkki: Kokoa luokittelun s\u00e4\u00e4nn\u00f6t pieneen “m\u00e4pp\u00e4ystaulukkoon” ja k\u00e4yt\u00e4 XLOOKUP<\/code>ia. Kun s\u00e4\u00e4nn\u00f6t muuttuvat, kaavoihin ei tarvitse koskea.<\/p>\nP\u00e4iv\u00e4m\u00e4\u00e4r\u00e4-, teksti- ja muotoilufunktiot<\/h2>\n
P\u00e4iv\u00e4m\u00e4\u00e4r\u00e4t ja teksti ovat raportoinnin ty\u00f6materiaalia. Kun hallitsemme muotoilun, saamme yhdenmukaiset, luettavat taulukot.<\/p>\n
P\u00e4iv\u00e4m\u00e4\u00e4r\u00e4t ja ty\u00f6p\u00e4iv\u00e4t<\/h3>\n\nDATE(vuosi,kk,p\u00e4iv\u00e4)<\/code>, EDATE(alku, kk_siirto)<\/code> ja EOMONTH<\/code> hoitavat laskennan kuukausitasolla.<\/li>\n- Ty\u00f6p\u00e4iv\u00e4t:
WORKDAY<\/code> ja NETWORKDAYS<\/code> (sek\u00e4 .INTL<\/code>-versiot) ottavat huomioon viikonloput ja pyh\u00e4t. Esim. =NETWORKDAYS.INTL(Alku,Loppu,\"0000011\",Pyh\u00e4Lista)<\/code> jos viikonloppu on vain su.<\/li>\n- Kuukauden tai viikon numero:
MONTH<\/code>, YEAR<\/code>, WEEKNUM<\/code> (asetus: viikon aloitusp\u00e4iv\u00e4).<\/li>\n<\/ul>\nTekstin k\u00e4sittely \u2013 uudet ty\u00f6kalut nopeuttavat<\/h3>\n\n- Perinteiset:
LEFT<\/code>, RIGHT<\/code>, MID<\/code>, LEN<\/code>, FIND<\/code>\/SEARCH<\/code>, TRIM<\/code>, CLEAN<\/code>.<\/li>\n- Modernit avustajat:
TEXTSPLIT<\/code>, TEXTBEFORE<\/code>, TEXTAFTER<\/code> pilkkovat tekstin ilman monikerroksista kaavaa.<\/li>\n- Esim. “Etunimi Sukunimi” \u2192
=TEXTSPLIT(A2,\" \")<\/code>.<\/li>\n- Yhdistely:
TEXTJOIN<\/code> (erotin ja tyhjien ohitus) ja CONCAT<\/code>\/CONCATENATE<\/code>.<\/li>\n- Muotoon muunto:
VALUE<\/code> (teksti \u2192 numero), TEXT<\/code> (numero \u2192 muotoiltu teksti), NUMBERVALUE<\/code> (aluekohtaiset desimaalierottimet).<\/li>\n<\/ul>\nEsitysmuoto ja raporttien luettavuus<\/h3>\n\nTEXT<\/code> on muotoilun sveitsil\u00e4inen linkkari: =TEXT(Pvm,\"dd.mm.yyyy\")<\/code>, =TEXT(Luku,\"#,##0.00 \u20ac\")<\/code>.<\/li>\n- Ehdollinen muotoilu hoitaa korostukset. Rakennamme s\u00e4\u00e4nn\u00f6t selkeill\u00e4 kaavoilla, esim.
=A2>Tavoite<\/code>. Pid\u00e4 s\u00e4\u00e4nn\u00f6t taulukossa l\u00e4hell\u00e4 dataa dokumentaatioksi.<\/li>\nROUND<\/code>, ROUNDUP<\/code>, ROUNDDOWN<\/code>, MROUND<\/code> auttavat esitystarkkuudessa. Talousraporteissa py\u00f6rist\u00e4mme esityssarakkeet \u2013 laskenta tehd\u00e4\u00e4n silti t\u00e4ystarkkuudella taustalla.<\/li>\n<\/ul>\nLET ja LAMBDA esimerkill\u00e4<\/h3>\n\nLET<\/code> tekee tekstikaavoista inhimillisi\u00e4:<\/li>\n=LET(n, FILTER(Hinnasto[Hinta], Hinnasto[Alue]=G2), keski, AVERAGE(n), ROUND(keski,2))<\/code><\/li>\nLAMBDA<\/code> mahdollistaa omat funktiot ilman VBA: luomme esim. LAMBDA(vero,brutto, brutto\/(1+vero))<\/code> ja annamme sille nimen ALV_Poisto<\/code>. Sitten k\u00e4yt\u00e4mme =ALV_Poisto(0,24: C2)<\/code>.<\/li>\n<\/ul>\nPieni k\u00e4yt\u00e4nt\u00f6: nime\u00e4 ratkaisusi. Kun palaamme tiedostoon puolen vuoden kuluttua, LET(kriittinen_raja: ... )<\/code> kertoo enemm\u00e4n kuin ketju B2>0.15<\/code>.<\/p>\nVirheet (N\/A, VALUE!) ja korjaus<\/h2>\n
Virheet ovat viestej\u00e4 \u2013 kun luemme ne oikein, korjaus l\u00f6ytyy nopeasti. T\u00e4ss\u00e4 t\u00e4rkeimm\u00e4t.<\/p>\n
Yleisimm\u00e4t virheet ja syyt<\/h3>\n\n#N\/A<\/code>: arvoa ei l\u00f6ydy haussa (esim. XLOOKUP<\/code>, VLOOKUP<\/code>). Usein syyn\u00e4 v\u00e4lily\u00f6nti, eri kirjainkoko, etu- tai per\u00e4tyhj\u00e4t, tai eri datatyyppi (teksti vs. numero).<\/li>\n#VALUE.<\/code>: v\u00e4\u00e4r\u00e4 tyyppi laskennassa (esim. teksti\u00e4 yritet\u00e4\u00e4n laskea yhteen).<\/li>\n#REF.<\/code>: viittaus poistettuun tai virheelliseen alueeseen.<\/li>\n#DIV\/0.<\/code>: jako nollalla \u2013 joskus haluttu “varoitus”, usein vartioidaan ehdolla.<\/li>\n#NAME?<\/code>: funktio tai nimi kirjoitettu v\u00e4\u00e4rin tai ominaisuus ei ole k\u00e4ytett\u00e4viss\u00e4 versiossa.<\/li>\n<\/ul>\nKorjaus- ja suojamuodot<\/h3>\n\nIFERROR(arvo, jos_virhe)<\/code> pehment\u00e4\u00e4 viesti\u00e4: =IFERROR(XLOOKUP(...),\"Ei l\u00f6ydy\")<\/code>. Raporteissa hyv\u00e4, mutta \u00e4lk\u00e4\u00e4mme piilottako ongelmia diagnostiikan yli.<\/li>\nIFNA<\/code> on tarkempi hakuvirheisiin: ei nielaise muita virheit\u00e4.<\/li>\n- Tarkistusfunktiot:
ISNUMBER<\/code>, ISTEXT<\/code>, ISBLANK<\/code>, ISNA<\/code>, ERROR.TYPE<\/code>. N\u00e4ill\u00e4 rakennamme “vartio-kaavoja”.<\/li>\n- Sy\u00f6tt\u00f6tarkistus: muunnamme numerotekstit oikeiksi (
VALUE<\/code>, NUMBERVALUE<\/code>), poistamme n\u00e4kym\u00e4tt\u00f6m\u00e4t v\u00e4lily\u00f6nnit (