Excel-kaavojen huijauslehti: Advanced Guide

Olet siis käynyt läpi perusasiat, käsitellyt väliasioita ja nyt olet valmis painimaan isojen aseiden – Excelin edistyneiden kaavojen – kanssa! Excelissä on monia edistyneitä toimintoja ja kaavoja kehittyneitä laskelmia varten, joten on hyödyllistä saada opas, josta löydät tarvitsemasi toiminnot.

Tämä Excel-kaavojen huijauslehti kattaa kehittyneitä ennustuskaavoja, tilastoanalyysiä, tietojenkäsittelytoimintoja, virheiden käsittelyä ja paljon muuta.

Tämä opas antaa sinulle tietoa näiden edistyneiden toimintojen käytöstä. Jokaisen kaavan mukana on selkeät selitykset, syntaksi ja käytännön esimerkit, joiden avulla Excelin keskitason käyttäjistä tulee edistyneitä tehokäyttäjiä.

Lataa ja tulosta huijauslehti ja pidä se saatavilla.

Ok, aloitetaan.

Ensinnäkin päästään Array-kaavoihin.

Sisällysluettelo

• Array kaavat
  • 1. AINUTLAATUINEN toiminto
  • 2. LAJITTELU-toiminto
  • 3. SUODATINtoiminto
• Excelin funktioiden satunnaistaminen
  • 1. RANDBETWEEN-toiminto
  • 2. RANDARRAY-toiminto
• Edistyneet ennustuskaavat Microsoft Excelissä
  • 1. FORECAST.ETS-toiminto
  • 2. TREND-toiminto
• Kehittyneet tilastokaavat
  • 1. PROSENTTIILIfunktio
  • 2. KVARTIILI-funktio
• Kehittyneet tietojen analysointi- ja käsittelykaavat
  • 1. TRANSPOSE-toiminto
  • 2. TAAJUUS-toiminto
  • 3. GETPIVOTDATA-toiminto
• Kehittynyt virheenkäsittely
• Tarkennetut hakukaavat
  • 1. XLOOKUP-toiminto
  • 2. XMATCH-toiminto
• Lopulliset ajatukset

Array kaavat

Näytämme sinulle, kuinka voit lajitella ja suodattaa tietosi manuaalisesti. Kokeneet käyttäjät tekevät tämän ohjelmallisesti taulukkokaavojen avulla.

Taulukkokaavojen avulla voit suorittaa laskelmia useille soluille samanaikaisesti. Nämä ovat kolme avaintoimintoa:

1. AINUTLAATUINEN

2. JÄRJESTELLÄ

3. SUODATTAA

Jotkut näistä toiminnoista ovat käytettävissä vain Microsoft Excelin uusimmissa versioissa.

1. AINUTLAATUINEN toiminto

UNIQUE-funktio hyväksyy alueen ja palauttaa luettelon yksilöllisistä arvoista.

Oletetaan, että sinulla on vaatteiden myyntitiedot. Voit etsiä sarakkeen A yksilölliset kohteet käyttämällä tätä kaavaa:

=UNIQUE(A2:A6)

2. LAJITTELU-toiminto

LAJITTELU-toiminto lajittelee alueen sisällön. Syntaksi on:

LAJITTELU(taulukko, [lajitteluindeksi], [lajittelujärjestys], [sarakkeen mukaan])

• array : lajiteltujen arvojen alue.

• sort_index : lajiteltava sarake (oletuksena 1)

• lajittelujärjestys : 1 nouseva (oletus) tai 2 laskeva).

• by_col : TOSI, jos haluat lajitella sarakkeen mukaan (oletus) tai EPÄTOSI, jos haluat lajitella rivin mukaan.

Kolme viimeistä argumenttia ovat valinnaisia, ja oletusasetukset ovat yleensä haluamasi.

Lajittele näytetiedot ensimmäisen sarakkeen mukaan käyttämällä tätä kaavaa:

=LAJITTELU(A2:C6)

3. SUODATINtoiminto

FILTER-toiminnolla voit suodattaa alueen tietyn ehdon mukaan. Tämä on syntaksi:

=SUODATIN(joukko, sisältää, [jos_tyhjä])

• array : suodatettava alue.

• include : ehto, joka määrittää, mitkä arvot suodatetaan.

• if_empty : määrittää, mitä palautetaan, jos mikään arvo ei täytä suodatusehtoja (oletus on "").

Oletetaan, että haluat suodattaa näytetietojen rivit näyttämään vain, missä myyntiarvo on suurempi kuin 15 dollaria. Käytä tätä kaavaa:

=SUODATIN(A2:C6, C2:C6>15)

Excelin funktioiden satunnaistaminen

Välihuijauksemme näyttää kuinka käyttää RAND-funktiota, joka tuottaa satunnaisluvun väliltä 0 ja 1.

Edistyneet Excel-käyttäjät osaavat käyttää satunnaistoimintoja mallitietojen nopeaan luomiseen.

1. RANDBETWEEN-toiminto

RANDBETWEEN-toiminto on joustavampi kuin RAND, koska voit määrittää alimman ja yläosan numeroiksi jotain muuta kuin 0 ja 1.

Luodaksesi tietoja, joiden numerot ovat välillä 1–100, kirjoita tämä kaava soluun A1:

=RANDBETWEEN(1, 100)

Kopioi sitten solu niin monelle riville ja sarakkeelle kuin haluat. Satunnaistettujen lukujen ruudukon luominen kestää sekunteja:

2. RANDARRAY-toiminto

Saatat ajatella, että olisi mukavaa välttää RANDBETWEEN-toiminnon manuaalista kopioimista. Saadaksesi erittäin edistyneitä, voit käyttää uutta RANDARRAY-toimintoa Microsoft Excelin uusimmassa versiossa.

Syntaksi on:

RANDARRAY([riviä], [sarakkeita], [min], [max], [koko luku])

• rivit : rivien lukumäärä

• sarakkeet : sarakkeiden lukumäärä

• min : pienin numero

• max : suurin luku

• kokonaisluku : oletuksena on TOSI, muuten käyttää desimaalilukuja.

Voit luoda satunnaislukuja 1–100 kuudelle riville ja yhdeksälle sarakkeelle käyttämällä tätä kaavaa:

=RANDARRAY(6; 9; 1; 100; TOSI)

Edistyneet ennustuskaavat Microsoft Excelissä

Excelin ennustetoimintoja käytetään tulevien arvojen ennustamiseen olemassa olevien tietotrendien perusteella. Nämä toiminnot auttavat tunnistamaan malleja ja ennakoimaan trendejä tietojesi perusteella.

1. FORECAST.ETS-toiminto

Vanhempi ENNUSTE-funktio korvattiin joukolla uusia funktioita Excel 2016:ssa.

Voit valita toiminnon haluamasi tietyn ennustemallin perusteella. Esimerkiksi FORECAST.ETS-funktio käyttää eksponentiaalista tasoitusalgoritmia .

Syntaksi on:

FORECAST.ETS(tavoitepäivämäärä, arvot, aikajana)

• target_date : päivämäärä, jolle haluat lasketun arvon.

• arvot : historialliset tiedot.

• aikajana : päivämäärät

Oletetaan, että sarakkeessa A on päivämäärät 1. - 5. tammikuuta ja sarakkeessa B myyntimäärät. Tämä kaava ennustaa seuraavan myyntimäärän:

=FORECAST.ETS("6. tammikuuta 2023", B2:B6, A2:A6)

2. TREND-toiminto

TREND-funktio projisoi arvojoukon pienimmän neliösumman menetelmään perustuen. Se palauttaa taulukon. Syntaksi on:

TRENDI(tunnettu_y, [tunnettu_x], [uusi_x], [jatkuva])

• tunnettu_y : y-arvojen alue

• tunnettu_x : x-arvojen alue

• new_x : laskettujen arvojen alue

Usein tunnettu_y ovat datapisteitä, kun taas tunnettu_x ovat päivämäärät.

Käyttämällä samoja tietoja kuin edellisessä esimerkissä voit syöttää alla olevan kaavan soluun C2. Luodaan joukko arvoja.

=TREND(B2:B6, A2:A6)

Kehittyneet tilastokaavat

Kehittyneisiin tilastotoimintoihin kuuluu prosenttipisteiden ja kvartiilien laskeminen. Jotkut matemaattiset funktiot ovat saatavilla taaksepäin yhteensopivuutta varten, mutta on suositeltavaa käyttää uusimpia versioita.

1. PROSENTTIILIfunktio

Tämä funktio laskee prosenttiosuuden datapisteistä, jotka putoavat tietyn arvon alapuolelle. Syntaksi on:

PERCENTILE.INC(taulukko, k)

• array : solualue

• k : prosenttipiste 0 - 1

Oletetaan, että haluat laskea sarakkeen B tietojen 70. prosenttipisteen. Käytä tätä kaavaa:

=PROSENTTIILI.INC(B2:B6, 0,7)

2. KVARTIILI-funktio

Tämä funktio on muunnelma prosenttipistefunktiosta, mutta käyttää neljänneksiä tietojen jakamiseen. Tämä on syntaksi:

QUARTILE.INC(taulukko, kvartti)

• array : tietoalue

• kvartti : 1 25. prosenttipisteelle, 2 50:lle, 3 75:lle ja 4 maksimiarvolle.

Alla oleva kaava laskee sarakkeen A tietojen ensimmäisen kvartiilin.

=QUARTILE.INC(A2:A5, 1)

Kehittyneet tietojen analysointi- ja käsittelykaavat

Useiden lisätoimintojen avulla voit vaihtaa tietojen muotoa, analysoida taajuusjakaumia ja poimia tietoja pivot-taulukoista.

1. SIIRTOA

2. TAAJUUS

3. GETPIVOTDATA

1. TRANSPOSE-toiminto

Joskus haluat siirtää rivien tiedot sarakkeisiin ja päinvastoin. Voit tehdä tämän manuaalisesti tai käyttää sen sijaan TRANSPOSE-toimintoa.

Oletetaan, että soluissa A2, A3 ja A4 on tuotteet "T-paita", "huppari" ja "farkut". Haluat tehdä niistä sarakeotsikoita. Tämä funktio palauttaa arvot yhdellä rivillä:

=TRANSPOSE (A2:A4)

2. TAAJUUS-toiminto

Tämä funktio laskee arvojen taajuusjakauman tietojoukon sisällä. Tämä on syntaksi:

FREQUENCY(data_jono, bins_matriisi)

• data_array : arvoalue.

• bins_array : käytettävät intervallit.

Oletetaan, että sinulla on myyntitietoja sarakkeessa B ja haluat analysoida arvojen tiheysjakaumaa sen perusteella, kuinka monta summaa on:

• alle 20.

• 20-80.

• yli 80.

Se edustaa kolmea säiliötä ja se voidaan laskea tällä kaavalla:

=TAAJUUS(A2:A6, {20,80})

Lisätietoja taajuusjakaumasta Excelissä on tässä videossa:

3. GETPIVOTDATA-toiminto

Tämän toiminnon avulla voit poimia yhteenvetotietoja pivot-taulukoista. Tämä on syntaksi:

GETPIVOTDATA(tietokenttä, pivot_table, [field1, item1], [field2, item2], …)

• data_field : tietokenttä tai arvo, jonka haluat noutaa pivot-taulukosta.

• pivot_table : viittaus pivot-taulukkoon.

• kenttä1, kohde1 jne. : kenttä/tuote-parit suodatettavaksi.

Oletetaan, että sinulla on myytyjen tuotteiden väriin perustuva pivot-taulukko. Pura punaisten tuotteiden myynti käyttämällä tätä kaavaa:

=GETPIVOTDATA("Myynti", A1, "Väri", "Punainen")

Kehittynyt virheenkäsittely

Jopa yksinkertaisimmat Excel-kaavat voivat tuottaa virheitä. Keskitason käyttäjien tulee osata käyttää ISERROR:ia virheiden käsittelyyn. Edistyneiden käyttäjien tulee myös tuntea ERROR.TYPE-toiminto virheiden tunnistamista varten.

ERROR.TYPE-funktio auttaa tunnistamaan solun tai kaavan tietyn tyyppisen virheen.

Se palauttaa eri virhetyyppejä vastaavan numeerisen arvon, kuten #N/A, #VALUE!, #REF! ja paljon muuta.

Oletetaan, että sinulla on virhe solussa A1 ja haluat tunnistaa sen virhetyypin. Seuraava kaava palauttaa numeron, joka vastaa tiettyä virhettä:

=VIRHE.TYYPPI(A1)

Voit yhdistää tämän useisiin toimintoihin vastataksesi eri tavalla virheen tyypistä riippuen. Nämä ovat yleisimmät virheet ja niiden arvot:

1. #TYHJÄ! (yhteistä solua ei löydy alueelta)

2. #DIV/0! ( jako nollalla tai tyhjä solu)

3. #ARVO! (sopimaton tietotyyppi tai argumentti kaavassa)

4. #VIITE! (viitattu solu on poistettu tai siellä on )

5. #NIMI? (Excel ei tunnista funktiota tai aluetta)

6. #NUM! (virheellinen numeerinen arvo)

7. #N/A (arvoa ei löydy)

Oletetaan, että haluat käsitellä kolmea tiettyä virhetyyppiä. Käytä tätä kaavaa näyttääksesi tyypin perusteella tietyn virheilmoituksen:

=JOS(VIRHE(C2), JOS(VIRHE.TYYPPI(C2)=2, "Jaa nollavirheellä", IF(VIRHE.TYYPPI(C2)=3, "Virheellinen arvo virhe", IF(VIRHE.TYYPPI(C2) )=7, "Arvoa ei löydy -virhe", "Muu virhe"))), C2)

Tarkennetut hakukaavat

Aloittelija- ja keskitason huijauslehtemme kattoivat . Tässä on joitain lisäasetuksia:

1. XHAKU

2. XMATCH

1. XLOOKUP-toiminto

Tämän hakutoiminnon avulla voit etsiä arvoa alueelta ja palauttaa vastaavan arvon toisesta sarakkeesta tai alueesta.

Se tarjoaa enemmän monipuolisuutta kuin yksinkertaisemmat hakutoiminnot, kuten . Tämä on syntaksi:

XHAKU.

• lookup_value : arvo, jota haluat etsiä.

• lookup_array : haun alue.

• return_array : alue, joka näyttää vastaavan arvon.

• match_mode : tarkka vastaavuus (0), seuraavaksi pienempi (1), seuraavaksi suurempi (-1) tai jokerimerkkihaku (2).

• search_mode : -1 ylhäältä alas, 1 alhaalta ylös tai 2 binäärihakuun.

• if_not_found : asettaa arvon, joka palautetaan, jos vastaavuutta ei löydy.

Oletetaan, että haluat etsiä tietoalueelta vaatekappaleen ensimmäistä esiintymistä ja palauttaa myyntisummat. Tämä kaava etsii tekstiä "Hoodie" ja palauttaa arvon viereiseen soluun, jos se löytyy:

=XHAKU("huppari", A2:A6, B2:B6)

2. XMATCH-toiminto

Tämän toiminnon avulla voit löytää tietyn arvon sijainnin alueella tai taulukossa. Tämä on syntaksi:

XMATCH(hakuarvo, hakutaulukko, [hakutyyppi], [hakutila])

• lookup_value : arvo, jonka haluat löytää.

• lookup_array : Alue, josta haluat etsiä.

• match_type : tarkka vastaavuus (0), seuraavaksi pienin (-1), seuraavaksi suurin (1).

• search_mode : binäärihaku (1) tai lineaarinen haku (2).

Oletetaan, että haluat löytää keltaisen kohteen ensimmäisen esiintymän sarakkeen B alueelta. Käytä tätä kaavaa:

=XMATCH("Keltainen", B2:B6, 0)

Lopulliset ajatukset

Tämä huijauslehti on kattanut laajan valikoiman toimintoja tilastoanalyysistä, hakukaavoista, tiedonkäsittelytekniikoista ja virheiden käsittelystrategioista.

Annetut esimerkit ja selitykset auttavat selvittämään näiden kehittyneiden kaavojen mysteerin ja tekevät niistä myös rajallisen kokemuksen omaavien saatavilla.

Kun alat sisällyttää niitä Excel-tehtäviisi, olet matkalla parantamaan Excel-taitojasi edistyneelle tasolle.

Mutta muista, tämä huijausarkki on vain jäävuoren huippu. Todella uskomattomia asioita tapahtuu, kun tulet luoviksi, yhdistät näitä kaavoja ja räätälöit ne ratkaisemaan ainutlaatuiset haasteesi. Excel on kuin kangas, ja nämä kaavat ovat palettisi – joten mene eteenpäin, maalaa mestariteos!