Excelin kaavat ja funktiot

Kaavat ja funktiot ovat Excelin leipää. He ajavat lähes kaikkea mielenkiintoista ja hyödyllistä, mitä ikinä teet laskentataulukossa. Tässä artikkelissa esitellään peruskäsitteet, jotka sinun on tiedettävä hallitaksesi Excelin kaavoja. Lisää esimerkkejä täällä .

Mikä on kaava?

Excelin kaava on lauseke, joka palauttaa tietyn tuloksen. Esimerkiksi:

 
=1+2 // returns 3

 
=6/3 // returns 2

Huomautus: kaikkien Excelin kaavojen tulee alkaa yhtäläisyysmerkillä (=).

Soluviittaukset

Yllä olevissa esimerkeissä arvot ovat 'kovakoodattuja'. Tämä tarkoittaa, että tulokset eivät muutu, ellet muokkaa kaavaa uudelleen ja muuta arvoa manuaalisesti. Yleensä tätä pidetään huonona muotona, koska se piilottaa tiedot ja vaikeuttaa laskentataulukon ylläpitoa.

Käytä sen sijaan soluviittauksia, jotta arvoja voidaan muuttaa milloin tahansa. Alla olevassa näytössä C1 sisältää seuraavan kaavan:

 
=A1+A2+A3 // returns 9

Huomaa, koska käytämme soluviittauksia A1: lle, A2: lle ja A3: lle, näitä arvoja voidaan muuttaa milloin tahansa ja C1 näyttää silti tarkan tuloksen.

Kaikki kaavat palauttavat tuloksen

Kaikki Excelin kaavat palauttavat tuloksen, vaikka tulos olisi virhe. Alla kaavaa käytetään prosentuaalisen muutoksen laskemiseen. Kaava palauttaa oikean tuloksen kohdissa D2 ja D3, mutta palauttaa #DIV/0! virhe D4: ssä, koska B4 on tyhjä:

On olemassa erilaisia ​​tapoja käsitellä virheitä. Tässä tapauksessa voit antaa puuttuvan arvon kohtaan B4 tai 'havaita' virheen IFERROR -toiminto ja näytä ystävällisempi viesti (tai ei mitään).

Kopioi ja liitä kaavat

Soluviittausten kauneus on, että ne päivittyvät automaattisesti, kun kaava kopioidaan uuteen paikkaan. Tämä tarkoittaa, että sinun ei tarvitse syöttää samaa peruskaavaa uudestaan ​​ja uudestaan. Alla olevassa näytössä E1: n kaava on kopioitu leikepöydälle Control + C:

Alla: kaava, joka on liitetty soluun E2 ja Control + V. Huomaa, että soluviittaukset ovat muuttuneet:

Sama kaava liitetään E3: een. Solujen osoitteet päivitetään uudelleen:

Suhteelliset ja absoluuttiset viittaukset

Yllä olevia soluviittauksia kutsutaan suhteellinen viittauksia. Tämä tarkoittaa, että viittaus on suhteessa soluun, jossa se elää. Kaava kohdassa E1 on:

 
=B1+C1+D1 // formula in E1

Kirjaimellisesti tämä tarkoittaa 'solua 3 saraketta vasemmalle' + 'solua 2 saraketta vasemmalle' + 'solua 1 saraketta vasemmalle'. Siksi, kun kaava kopioidaan alas soluun E2, se toimii edelleen samalla tavalla.

Suhteelliset viitteet ovat erittäin hyödyllisiä, mutta toisinaan et halua, että soluviittaus muuttuu. Soluviittausta, joka ei muutu kopioidessa, kutsutaan nimellä ehdoton viittaus . Jotta viittaus olisi ehdoton, käytä dollarisymbolia ($):

 
=A1 // relative reference =$A // absolute reference

Esimerkiksi alla olevassa näytössä haluamme kertoa jokaisen sarakkeen D arvon 10: llä, joka syötetään kohtaan A1. Käyttämällä absoluuttista viitettä A1: lle me lukitsemme sen, jotta se ei muutu, kun kaava kopioidaan E2: een ja E3:

Tässä ovat lopulliset kaavat kohdissa E1, E2 ja E3:

 
=D1*$A // formula in E1 =D2*$A // formula in E2 =D3*$A // formula in E3

Huomaa viittaus D1 -päivityksiin, kun kaava kopioidaan, mutta viittaus A1 ei koskaan muutu. Nyt voimme helposti muuttaa arvon A1, ja kaikki kolme kaavaa lasketaan uudelleen. Alle A1: n arvo on muuttunut arvosta 10 arvoon 12:

Tämä yksinkertainen esimerkki osoittaa myös, miksi arvojen kovakoodaaminen kaavaksi ei ole järkevää. Tallentamalla arvon A1 yhteen paikkaan ja viittaamalla A1: ään an ehdoton viittaus , arvoa voidaan muuttaa milloin tahansa ja kaikki siihen liittyvät kaavat päivittyvät välittömästi.

Vihje: voit vaihtaa suhteellisen ja absoluuttisen syntaksin välillä F4 -näppäin .

Kaavan syöttäminen

Kaavan syöttäminen:

1. Valitse solu
2. Anna yhtäläisyysmerkki (=)
3. Kirjoita kaava ja paina enter.

Soluviittausten kirjoittamisen sijaan voit osoittaa ja napsauttaa alla olevan kuvan mukaisesti. Huomautusviitteet ovat värikoodattuja:

Kaikkien Excelin kaavojen tulee alkaa yhtäläisyysmerkillä (=). Ei yhtäläisyysmerkkiä, ei kaavaa:

Kuinka muuttaa kaavaa

Kaavan muokkaamiseen on kolme vaihtoehtoa:

1. Valitse solu, muokkaa kaavapalkki
2. Kaksoisnapsauta solua, muokkaa suoraan
3. Valitse solu, paina F2 , muokkaa suoraan

Riippumatta siitä, mitä vaihtoehtoa käytät, vahvista muutokset painamalla Enter -näppäintä. Jos haluat peruuttaa kaavan ja jättää sen muuttamatta, napsauta Escape -näppäintä.

Video: 20 vinkkiä kaavojen syöttämiseen

Mikä on funktio?

Kun työskentelet Excelissä, kuulet sanat 'kaava' ja 'toiminto', joita käytetään usein, joskus keskenään. Ne liittyvät läheisesti toisiinsa, mutta eivät aivan samoja. Teknisesti kaava on minkä tahansa lauseke, joka alkaa yhtäläisyysmerkillä (=).

Toisaalta funktio on kaava, jolla on erityinen nimi ja tarkoitus. Useimmissa tapauksissa toiminnoilla on nimet, jotka heijastavat niiden aiottua käyttöä. Esimerkiksi luultavasti tiedät SUM -toiminto jo, joka palauttaa annettujen viittausten summan:

 
= SUM (1,2,3) // returns 6 = SUM (A1:A3) // returns A1+A2+A3

The KESKI -toiminto , kuten odotit, palauttaa annettujen viittausten keskiarvon:

 
= AVERAGE (1,2,3) // returns 2

MIN- ja MAX -toiminnot palauttavat minimi- ja maksimiarvot:

 
= MIN (1,2,3) // returns 1 = MAX (1,2,3) // returns 3

Excel sisältää satoja erityistoimintoja . Jos haluat aloittaa, katso 101 Excel -avaintoimintoa .

Funktion argumentit

Useimmat toiminnot edellyttävät tuloja tuloksen palauttamiseksi. Näitä syötteitä kutsutaan argumentteiksi. Funktion argumentit näkyvät funktion nimen jälkeen suluissa pilkuilla erotettuna. Kaikki toiminnot vaativat vastaavat avaavat ja sulkevat sulut (). Malli näyttää tältä:

 
=FUNCTIONNAME(argument1,argument2,argument3)

Esimerkiksi COUNTIF -toiminto laskee kriteerit täyttävät solut ja ottaa kaksi argumenttia, valikoima ja kriteeri :

 
= COUNTIF (range,criteria) // two arguments

Alla olevassa näytössä alue on A1: A5 ja kriteerit ovat 'punaisia'. C1: n kaava on:

 
= COUNTIF (A1:A5,'red') // returns 2

Video: COUNTIF -toiminnon käyttäminen

Kaikkia argumentteja ei vaadita. Argumentit hakasulkeissa ovat valinnaisia. Esimerkiksi YEARFRAC -toiminto palauttaa murto -osan vuosista aloitus- ja lopetuspäivän välillä ja ottaa 3 argumenttia:

 
= YEARFRAC (start_date,end_date,[basis])

Aloitus- ja lopetuspäivä ovat pakollisia argumentteja, perusta on valinnainen argumentti. Katso alla esimerkki siitä, kuinka YEARFRAC -laskentataulukkoa käytetään nykyisen iän laskemiseen syntymäajan perusteella.

Toiminnon syöttäminen

Jos tiedät toiminnon nimen, aloita kirjoittaminen. Tässä on vaiheet:

1. Kirjoita yhtäläisyysmerkki (=) ja aloita kirjoittaminen. Excel näyttää luettelon vastaavista toiminnoista kirjoittaessasi:

Kun näet haluamasi toiminnon luettelossa, valitse nuolinäppäimillä (tai jatka kirjoittamista).

2. Hyväksy toiminto kirjoittamalla Sarkain -näppäin. Excel suorittaa toiminnon loppuun:

3. Täytä vaaditut argumentit:

4. Vahvista kaava painamalla Enter:

Toimintojen yhdistäminen (sisäkkäin)

Monet Excel -kaavat käyttävät useampaa kuin yhtä funktiota, ja funktiot voivat olla sisäkkäin 'toistensa sisällä. Esimerkiksi alla on syntymäaika B1: ssä ja haluamme laskea nykyisen iän B2:

The YEARFRAC -toiminto laskee vuosia alkamis- ja päättymispäivänä:

Voimme käyttää B1: tä aloituspäivänä ja käyttää sitten TODAY -toiminto antaa lopetuspäivä:

Kun painamme Enter -näppäintä vahvistaaksemme, saamme nykyisen iän tämän päivän perusteella:

 
= YEARFRAC (B1, TODAY ())

Huomaa, että käytämme TODAY -toimintoa syöttääksesi päättymispäivän YEARFRAC -funktiolle. Toisin sanoen TODAY -funktio voidaan sisäistää YEARFRAC -funktion sisälle, jolloin saadaan lopetuspäivä -argumentti. Voimme viedä kaavan askeleen pidemmälle ja käyttää INT -toiminto desimaaliluvun katkaisemiseksi:

 
= INT ( YEARFRAC (B1, TODAY ()))

Tässä alkuperäinen YEARFRAC -kaava palauttaa funktion INT funktion 20.4 ja INT -funktio palauttaa lopputuloksen 20.

Huomautuksia:

1. Yllä olevien kuvien nykyinen päivämäärä on 22. helmikuuta 2019.
2. Sisäkkäin IF -toiminnot ovat klassinen esimerkki sisäkkäisiä toimintoja .
3. The TODAY -toiminto on harvinainen Excel -toiminto, jossa ei ole pakollisia argumentteja.

Avain takeaway: Kaavan tai funktion tulos voidaan syöttää suoraan toiseen kaavaan tai funktioon.

Matematiikan operaattorit

Alla olevassa taulukossa näkyvät Excelissä saatavilla olevat vakiomuotoiset matemaattiset operaattorit:

Loogiset operaattorit

Loogiset operaattorit tukevat vertailuja, kuten 'suurempi kuin', 'pienempi kuin' jne. Excelissä käytettävissä olevat loogiset operaattorit on esitetty seuraavassa taulukossa:

Video: Kuinka rakentaa loogisia kaavoja

Toimintojen järjestys

Kaavaa ratkaistessaan Excel noudattaa toimintojen järjestystä. Ensinnäkin kaikki suluissa olevat lausekkeet arvioidaan. Seuraava Excel ratkaisee kaikki eksponentit. Eksponenttien jälkeen Excel suorittaa kertolaskun ja jaon, sitten yhteen- ja vähennyslaskut. Jos kaava sisältää ketjutus , tämä tapahtuu tavallisten matemaattisten operaatioiden jälkeen. Lopuksi Excel arvioi loogiset operaattorit , jos läsnä.

1. Suluet
2. Eksponentit
3. Kertolasku ja jako
4. Yhteenlasku ja vähennyslasku
5. Ketjutus
6. Loogiset operaattorit

Vinkki: voit käyttää Arvioi ominaisuus katsella Excelin ratkaisemaan kaavoja askel askeleelta.

Muunna kaavat arvoiksi

Joskus haluat päästä eroon kaavoista ja jättää vain arvot tilalle. Helpoin tapa tehdä tämä Excelissä on kopioida kaava ja liittää se käyttämällä Liitä erityinen> Arvot. Tämä korvaa kaavat palautetuilla arvoilla. Voit käyttää a pikanäppäin liittääksesi arvot tai käytä nauhan Aloitus -välilehden Liitä -valikkoa.

Video: Liitä erityiset pikavalinnat

Excelin ehdollinen muotoilu ei ole sama kuin toinen solu

Mitä seuraavaksi?

Alla on oppaita, joiden avulla saat lisätietoja Excelin kaavoista ja funktioista. Tarjoamme myös online -videokoulutus .

• 29 vinkkiä kaavojen käsittelyyn ja toiminnot ( videoversio täältä )
• 500 kaavan esimerkkiä täydellisin selityksin
• 101 tärkeää Excel -toimintoa
• Opas kaikkiin Excel -toimintoihin (Työn alla)
• Excel -kaavan virheet (esimerkkejä ja korjauksia)
• Kaavaehdot - 50 esimerkkiä
• Ehdollisen muotoilun kaavat
• F9: n käyttäminen kaavan virheenkorjaukseen (video)
• Excel -kaavan virheet ja korjaukset (video)