Excel-kaavat Cheat Sheet: Intermediate Guide

Joten olet onnistunut hallitsemaan – kiitos sinulle! Nyt olet päättänyt nostaa asioita ylös ja sukeltaa edistyneempiin asioihin, eikö niin? No, olet onnekas, sillä olemme koonneet kätevän Excel-huijausarkin juuri sinua varten!

Tämä Microsoft Excelin keskitason huijauslehti kattaa laajan valikoiman aiheita, jotka parantavat taitosi numeerisessa analyysissä, tekstinkäsittelyssä sekä monimutkaisessa päivämäärä- ja aikalogiikassa.

Käyttämällä Excel Intermediate Cheat Sheet -taulukkoa työpöydän viitteenä voit sukeltaa syvälle Microsoft Excelin tehokkaampiin kaavoihin ja toimintoihin.

Muista nyt lukea, mutta myös tulostaa ja tallentaa alla oleva huijauslehti!

Aloitetaan!

Sisällysluettelo

• Keskitason matematiikan kaavat
  • Ehdollinen logiikka
• Tilastolliset välikaavat
  • 1. MINA ja MAXA
  • 2. COUNTA ja COUNTIF
• Excel-kaavat talousanalyysiin
  • 1. TUOTEtoiminto
  • 2. QUOTIENT- ja LOG-funktiot
  • Toiminnot sijoitusskenaarioihin
• Excelin keskitason päivämäärä- ja aikakaavat
  • 1. Välikuukauden toiminnot
  • 2. Väliviikon toiminnot
  • 3. Väliaikatoiminnot
• Excelin funktiot ehdolliseen ja loogiseen algebraan
  • 1. JOS-toiminto
  • 2. JA toiminto
  • 3. TAI toiminto
  • 4. EI toiminto
• Viitaavat solut
  • 1. EPÄSUORA toiminto
  • 2. INDEKSItoiminto
  • 3. OFFSET-toiminto
• Välitekstikaavat
  • 1. TEXTJOIN-toiminto
  • 2. REPLACE-toiminto
  • 3. KORVAUStoiminto
• Keskitason hakukaavat
• Kaavat Microsoft Excel -virheiden käsittelyyn
  • 1. IFERROR-toiminto
  • 2. IFNA-toiminto
• Kaavan ja funktioiden pikanäppäimet
• Solujen välimuotoilu
• Lopulliset ajatukset

Keskitason matematiikan kaavat

Aloittelijan huijauslehtemme kattoi yleisimmät aloittelijan matemaattiset funktiot, kuten SUM-funktion. Keskitason käyttäjien tulisi tutustua näihin monimutkaisempiin toimintoihin:

• ABS() palauttaa luvun itseisarvon.

• SQRT() palauttaa luvun neliöjuuren

• RAND() tarjoaa satunnaisluvun väliltä 0 ja 1

Ehdollinen logiikka

Keskitason käyttäjien tulee myös osata suorittaa matemaattisia laskelmia useiden ehtojen perusteella käyttämällä näitä toimintoja:

• SUMIFS(summa_alue, ehdot_väli1, ehdot1, [….])

• COUNTIFS(kriteerit_väli1, ehdot1, [ehdot_alue2, ehdot2], …)

• AVERAGEIFS(kriteerit_väli1, ehdot1, [ehdot_alue2, ehdot2], …)

Funktiot arvioivat annetut kriteerit ja tuottavat loogisen arvon TOSI tai EPÄTOSI.

Oletetaan, että sinulla on seuraavat myyntitiedot sarakkeissa A, B ja C:

Laske punaisten t-paitojen myynnin summa käyttämällä SUMIFS-kaavaa:

=SUMIFS(C2:C6, A2:A6, "T-paita", B2:B6, "punainen")

Laske punaisilla t-paidoilla olevien rivien määrä käyttämällä COUNTIFS-kaavaa:

=LASKET (C2:C6, A2:A6, "T-paita", B2:B6, "punainen")

Laske punaisten t-paitojen keskimääräinen myynti käyttämällä AVERAGEIFS-kaavaa:

=AVERAGEIFS(C2:C6, A2:A6, "T-paita", B2:B6, "punainen")

Tilastolliset välikaavat

Perustoimintojen, kuten MIN ja MAX, lisäksi Excelissä on laajempi valikoima tilastokaavoja keskitason käyttäjille. Tässä on joitain hyödyllisimpiä:

• MINA

• MAXA

• COUNTA

• COUNTIF

1. MINA ja MAXA

Yleisemmin käytetyt MIN- ja MAX-funktiot ohittavat tekstiarvot.

Vaihtoehtoiset MINA- ja MAXA-funktiot ottavat huomioon sekä tekstin että numerot etsiessään suurimpia tai pienimpiä arvoja. Tekstiarvot arvioidaan ikään kuin ne olisivat nollia.

Erot näkyvät alla olevassa kuvassa. Ensimmäinen rivi arvioi vähimmäisarvon 10, kun taas toisen rivin arvo on 0 tekstiarvon vuoksi.

2. COUNTA ja COUNTIF

COUNTA-funktiota käytetään alueella.

COUNTIF-funktio on vähemmän spesifinen, koska sitä käytetään laskemaan tietyn ehdon tai kriteerin täyttävien solujen lukumäärä alueella.

Voit käyttää näitä toimintoja myös sarakkeen erillisten arvojen laskemiseen. Tämä video näyttää, kuinka se tehdään.

Excel-kaavat talousanalyysiin

On olemassa useita kaavoja, joita haluat käyttää suoritettaessa taloudellista analyysiä, kuten investointien ennustamista:

• TUOTE(numero1, [numero2…])

• QUOTIENT(osoittaja, nimittäjä)

• LOG(numero, [kantaosa])

1. TUOTEtoiminto

Oletetaan, että haluat laskea 1 000 dollarin alkusijoituksen (solu B1) tulevan arvon 4 prosentin vuotuisella korolla (solu B2) viiden vuoden kuluttua (solu B3).

Käytä TUOTE-funktiota laskeaksesi tulevaisuuden arvo tällä kaavalla:

=B1 * TUOTE(1 + B2)^B3

Tämä kaava palauttaa sijoituksesi tulevaisuuden arvon.

2. QUOTIENT- ja LOG-funktiot

Seuraava askel on laskea, kuinka monta vuotta kestää, ennen kuin sijoituksen arvo kaksinkertaistuu annetulla korolla.

Käytä QUOTIENT-funktiota yhdessä LOG-funktion kanssa laskeaksesi tämän seuraavasti:

=osamäärä(LOKI(2) / LOK(1 + B2), 1)

Tämä kaava palauttaa 17 näytetietojen kanssa, mikä osoittaa, että kestää 17 vuotta, ennen kuin sijoituksen arvo kaksinkertaistuu 4 prosentin vuosikorolla.

Tämä kuva :

Toiminnot sijoitusskenaarioihin

Nämä ovat joitakin toimintoja, jotka soveltuvat tiettyihin taloudellisiin skenaarioihin. Yksi niistä voi olla juuri sitä mitä tarvitset:

• NPV – NPV (Net Present Value) -toiminto laskee sijoituksen nettonykyarvon diskonttokorkoon perustuvien tulevien kassavirtojen sarjan perusteella.

• ACCRINT – ACCRINT -toiminto laskee jaksollista korkoa maksavan arvopaperin kertyneen koron. Tämä on hyödyllistä määritettäessä arvopaperille ansaittua korkoa viimeisestä maksupäivästä tiettyyn maksupäivään asti.

• INTRATE – INTRATE -toiminto laskee koron täysin sijoitetulle arvopaperille.

• PMT – PMT- toiminto laskee velkapaperin kokonaismaksun.

• IRR – IRR- funktio antaa sisäisen tuottoprosentin.

• TUOTTO – TUOTTO -toiminto tarjoaa arvopaperin tuoton koron, nimellisarvon ja maturiteetin perusteella.

Excelin keskitason päivämäärä- ja aikakaavat

Excelin peruspäivämäärä- ja aikafunktiot sisältävät nykyisen päivämäärän NOW- ja TODAY-funktiot. Keskitason käyttäjien tulisi myös tietää, kuinka komponentteja voidaan poimia tietystä päivämäärästä käyttämällä:

• PÄIVÄ(päivämäärä)

• KUUKAUSI(päivämäärä)

• YEAR(päivämäärä)

Kaava =KUUKAUSI("23. huhtikuuta 2023") palauttaa tuloksen 4 neljännelle kuukaudelle. Vastaavasti PÄIVÄ- ja VUOSI-funktiot palauttavat 23 ja 2023.

1. Välikuukauden toiminnot

Keskitason käyttäjät käsittelevät joskus kuukauden lopun lisäämistä tai etsimistä.

• PÄIVÄMÄÄRÄ(alkamispäivä, kuukausien_määrä)

• KUUKAUSI(alkamispäivä, kuukausien_määrä)

Esimerkiksi kaava =EDATE("23. huhtikuuta 2023", 2) laskee päivämäärän kaksi kuukautta myöhemmin.

Kaava =EOKUUKAUSI(23.4.2023”, 2) laskee kuukauden lopun kahden kuukauden kuluttua.

Tuloksena on ”30.6.2023”, jossa otetaan huomioon, että kesäkuussa on vain kolmekymmentä päivää. Jos määrität 3 kuukautta, tulos on "31.7.2023".

2. Väliviikon toiminnot

Voit käyttää SUMMA- ja WEEKDAY-toimintojen yhdistelmää laskeaksesi kokonaisten työpäivien lukumäärän tietyn päivämääräalueen sisällä.

WEEKDAY-funktio palauttaa viikonpäivän tietylle päivälle. Toinen parametri määrittää numerointijärjestelmän oletuksena sunnuntaista lauantaihin.

Jos sinulla on sarja päivämääriä solualueella B1:B40, käytä tätä kaavaa työpäivien laskemiseen:

=SUMMA(–(VIIKKOPÄIVÄ(B1:B40,2)>5))

3. Väliaikatoiminnot

YEARFRAC-funktio laskee kahden päivämäärän välisen vuoden murto-osan.

AIKAARVO-funktiota käytetään muuttamaan tekstinä esitetty aika desimaaliluvuksi, joka edustaa 24 tunnin vuorokauden osuutta.

Tuloksena on Excel-sarjanumero, jossa 1 edustaa täyttä 24 tunnin päivää, 0,5 tarkoittaa 12 tuntia, 0,25 edustaa 6 tuntia ja niin edelleen.

Oletetaan, että sinulla on aika tekstimuodossa "17:45" solussa A1 ja haluat muuntaa sen desimaaliluvuksi . Tämä on kaava:

=AIKAARVO(A1)

Arvo 0,74 edustaa osuutta 24 tunnin vuorokaudesta, joka on kulunut 15 minuuttia ennen kello kuutta.

Take to muuttaaksesi aikasolun aikamuodosta tekstimuotoon. Haluat ehkä muotoilla tämän prosentteina selvyyden vuoksi.

Excelin funktiot ehdolliseen ja loogiseen algebraan

Ehdolliset ja loogiset funktiot ovat välttämättömiä päätöksenteossa Excelissä. Seuraavat toiminnot voidaan yhdistää Excelin peruskaavojen kanssa tehokkaan logiikan aikaansaamiseksi:

1. IF(ehto, arvo_jos_tosi, arvo_jos_epätosi):

2. JA(ehto1, ehto2,…)

3. TAI(ehto1, ehto2,…)

4. EI (ehto1, ehto2,…)

IF-funktio arvioi ehdon ja palauttaa erilaisia ​​arvoja riippuen siitä, onko ehto tosi vai epätosi.

JA-, TAI- ja EI-funktiot tarkistavat, mitkä ehdot ovat tosia, ja antavat sinun tehdä päätöksiä niiden mukaisesti.

Tässä on joitain esimerkkejä aiemmin käyttämiimme myyntitietoihin perustuen.

1. JOS-toiminto

Oletetaan, että haluat näyttää tekstin "Alhainen myynti", jos tuotteiden myynti on alle 15. Jos myynti on suurempi, haluat näyttää "High Sales" uudessa sarakkeessa.

Käytä tätä kaavaa ja kopioi se muille riveille:

=IF(C2<15, "Alhainen myynti", "Suuri myynti")

2. JA toiminto

Oletetaan, että haluat näyttää TOSI tai EPÄTOSI t-paidoissa, joissa on vähintään 15 myyntiä. Käytä tätä kaavaa:

=JA(A3=”T-paita”, C3>=15)

3. TAI toiminto

Oletetaan, että haluat testata useita ehtoja ja palauttaa TOSI, jos jokin ehdoista täyttyy, ja muutoin EPÄTOSI, niin käytät TAI-funktiota.

Esimerkiksi, jos meillä on testin pisteet soluissa A1 ja B1 ja haluamme tietää, onko jompikumpi pistemäärä yli 80, voisimme käyttää: =TAI( A1>80, B1>80) . Tämä palauttaa TRUE, jos jompikumpi pistemäärä (tai molemmat) on yli 80, ja EPÄTOSI, jos molemmat ovat 80 tai vähemmän.

4. EI toiminto

Oletetaan, että haluat tarkistaa, ettei tuote ole huppari. Käytä tätä kaavaa:

=EI(A2="huppari")

Viitaavat solut

Aloittelija oppii nopeasti käyttämään absoluuttisia ja suhteellisia viittauksia, kuten $A$1 tai B2. Keskitason käyttäjien tulee tutustua epäsuorien viittausten, indeksien ja siirtymien käyttöön:

1. EPÄSUORA(ref_text)

2. INDEKSI(väli, rivin_numero, sarakkeen_nm.)

3. OFFSET(viitesolu, rivit, sarakkeet):

1. EPÄSUORA toiminto

EPÄSUORA-funktio palauttaa tekstinä syötetyn soluviittauksen arvon. Voit viitata soluun B3 käyttämällä tätä kaavaa:

= EPÄSUORA("B3")

Etuna yksinkertaisiin soluviittauksiin verrattuna on, että tämä toiminto antaa sinulle dynaamisen viittauksen. Tämä tarkoittaa, että Excel päivittää viitteen automaattisesti, kun laskentataulukon rakenne muuttuu, esim. rivi poistetaan.

2. INDEKSItoiminto

INDEKSI-funktiota käytetään viittaamaan soluihin tietyllä alueella rivi- ja sarakenumeroiden perusteella.

Jos haluat viitata soluun B3 kuusirivisessä taulukossa, käytä tätä kaavaa:

=INDEKSI(B1:B6, 3)

3. OFFSET-toiminto

OFFSET-funktio palauttaa solun tai alueen, joka on tietyn määrän rivejä ja sarakkeita poispäin viitesolusta.

Jos haluat näyttää solun arvon, joka on kaksi solua poikki ja yksi solu alaspäin A1:stä, käytä tätä kaavaa:

=SIIRTO(A1; 2; 1)

Tässä ovat kaavat toiminnassa:

Välitekstikaavat

Aloittelijoiden tulisi tuntea tekstifunktiot, kuten LEFT, joka poimii yhden tai useamman merkin merkkijonon vasemmalta puolelta. Tehokäyttäjän tulee tuntea seuraavat toiminnot:

1. TEXTJOIN(erotin, ohita_tyhjä, teksti1, [teksti2, …])

2. KORVAUS(vanha_teksti, aloitusnumero, merkkien_määrä, uusi_teksti)

3. KORVAA(teksti, vanha_teksti, uusi_teksti, [instanssin_numero])

1. TEXTJOIN-toiminto

TEXTJOIN-funktio ketjuttaa solut antamasi erottimen kanssa. Voit myös määrittää, missä tyhjät solut jätetään huomioimatta.

Luo pilkuilla eroteltu luettelo joistakin sarakkeen A kohteista käyttämällä tätä kaavaa:

=TEKSTIJOIN(", ", TOSI, A2:A4)

2. REPLACE-toiminto

Korvaa-toiminnolla voit määrittää kohteeksi vaihdettavan merkkijonon aloituspaikan ja pituuden.

Jos haluat korvata merkkijonon kaksi ensimmäistä merkkiä "XX", käytä tätä kaavaa:

=TEKSTIJOIN(A2; 1; 2; "XX")

3. KORVAUStoiminto

Korvaava funktio korvaa tietyt esiintymät toisessa tekstimerkkijonossa uudella tekstimerkkijonolla.

Käytä tätä kaavaa korvataksesi sanan "farkut" sanalla "leggingsit":

=KORVAA(A3, "farkut", "leggingsit")

Tässä ovat kaavat toiminnassa:

Keskitason hakukaavat

Yleiset hakutoiminnot, kuten VLOOKUP ja MATCH, käsitellään aloittelijan huijausarkissa.

Keskitason käyttäjien tulisi tutustua CHOOSE-hakutoimintoon:

• VALITSE(indeksin_määrä, arvo1, [arvo2, …])

VALITSE-funktio palauttaa arvon arvoluettelosta, joka perustuu määritettyyn indeksinumeroon.

Oletetaan, että sinulla on sarake t-paitakokoja, jotka on merkitty 1, 2 tai 3. Haluat näyttää luokan pienet, keskikokoiset ja suuret samalla rivillä. Käytä tätä kaavaa:

=VALITSE(A1, "pieni", "keskikokoinen", "suuri")

Kaavat Microsoft Excel -virheiden käsittelyyn

Aloittelijan cheatsheet listasi tyypilliset virheilmoitukset, jotka näet työskennellessäsi Excelin kanssa. Keskitason käyttäjien tulisi pystyä käyttämään kaavoja käsitelläkseen virheet sulavasti.

1. IFERROR(arvo, jos_virhe)

2. IFNA(arvo, arvo_jos_na)

1. IFERROR-toiminto

Nollalla jakovirheet ovat yleisiä , mutta Excelin oletusnäyttö #DIV/0! ei ole selvää kaikille käyttäjille.

Käytä IFERROR-funktiota korvataksesi vakiovirheen "ei voi jakaa nollalla" tällä kaavalla:

=IFORROR(A1/B1, "ei voi jakaa nollalla")

Tämä kuva näyttää raakavirheen kolmannella rivillä ja IFERROR-funktion, joka käsittelee neljättä riviä.

2. IFNA-toiminto

Excelin IFNA-funktiota käytetään #N/A-virheiden havaitsemiseen ja käsittelemiseen.

Tämän huijausarkin toisessa osassa näytettiin VALITSE-toiminto. Tämä funktio palauttaa N/A-virheen, jos indeksinumero ei ole kokonaisluku.

Oletetaan, että t-paidan kooksi on annettu 1,5, jolloin käyttäjä luulee virheellisesti, että tämä näyttää luokan jossain pienen ja keskikokoisen väliltä. Sen sijaan se näyttää #N/A -virheen.

Käytä tätä kaavaa saadaksesi hyödyllisemmän virheen:

=IFNA(VALITSE(A2, "Matala", "Keskitaso", "Korkea"), "Virheellinen indeksi")

Kaavan ja funktioiden pikanäppäimet

On olemassa useita pikanäppäimiä, joiden avulla voit työskennellä tehokkaammin laskentataulukon kanssa.

• F2 : Muokkaa aktiivista solua ja sijoita lisäyskohta solun sisällön loppuun.

• F9 : Laske ja näytä kaavan valitun osan tulos.

• Ctrl + Vaihto + Enter : Kirjoita taulukkokaava.

• Vaihto + F3 : Avaa Lisää funktio -valintaikkuna.

• Esc : Peruuta kaavan syöttäminen ja palauta solun alkuperäinen sisältö.

Jotkin näistä Excel-pikanäppäimistä eivät ehkä ole saatavilla kaikilla kielillä tai näppäimistöasetteluilla.

Solujen välimuotoilu

In financial analysis, proper formatting for cells containing numbers, dates, and currencies is crucial. To format cells, follow these steps:

1. Select the cells you want to format.

2. Right-click on the selected cells and choose “Format Cells.”

3. Select the appropriate category (e.g., Number, Currency, Date) and apply the desired format.

Some common formatting types to consider in finance include:

• Currency: $1,234.56

• Percentage: 12.34%

• Accounting: ($1,234.00)

• Date: 10-May-2023

Final Thoughts

As you become more comfortable with the various formulas and functions highlighted in this , you’ll find yourself better equipped to handle increasingly complex tasks and projects.

Practice is key to mastering any skill, so don’t be afraid to experiment with these formulas in your day-to-day work. They will bring your expertise from beginner level to intermediate level.

This is just a taste of what Excel’s intermediate functions can do to simplify your work, streamline your analyses, and help you uncover insights from your data.

The real magic happens when you start combining the functions in our cheat sheet and tailor them to suit your unique needs. So, don’t stop here. Keep exploring, keep experimenting, and you’ll find that Excel is not just a tool, it’s a game-changer!

Feel like you want to take things to the next level? Check our