Excel

Excel-kaavat ja -toiminnot

Excel Formulas Functions

Kaavat ja toiminnot ovat Excelin leipä ja voi. Ne ajavat melkein kaiken mielenkiintoisen ja hyödyllisen, mitä ikinä teet laskentataulukossa. Tässä artikkelissa esitellään peruskäsitteet, jotka sinun on tiedettävä, jotta voit hallita Excelin kaavoja. Lisää esimerkkejä täältä .

Mikä on kaava?

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



 
=1+2 // returns 3

Peruskaavan esimerkki - 1 + 3 = 3



 
=6/3 // returns 2

Peruskaavan esimerkki - 6/3 = 2

Huomaa: Kaikkien Excelin kaavojen on aloitettava yhtäläisyysmerkillä (=).



Soluviitteet

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äpitämistä.

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

Kaava soluviitteillä



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

Kaikki kaavat palauttavat tuloksen

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

Kaavan tulos voi olla virhe

On olemassa erilaisia ​​tapoja käsitellä virheitä. Tässä tapauksessa voit antaa puuttuvan arvon B4: stä tai 'saada kiinni' virheestä 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 sijaintiin. Tämä tarkoittaa, että sinun ei tarvitse kirjoittaa samaa peruskaavaa uudestaan ​​ja uudestaan. Alla olevassa näytössä E1: n kaava on kopioitu leikepöydälle Control + C: llä:

E1-kaava kopioitu leikepöydälle

Alla: kaava liitetty soluun E2 Control + V: llä. Huomaa, että soluviitteet ovat muuttuneet:

E1-kaava liitetty muotoon E2

Sama kaava liitetty E3: een. Solun osoitteet päivitetään uudelleen:

E1: n kaava liitetty muotoon E3

Suhteelliset ja absoluuttiset viitteet

Yllä olevia soluviittauksia kutsutaan suhteellinen viitteet. Tämä tarkoittaa, että viite on suhteessa soluun, jossa se elää. Yllä olevan kaavan E1 mukainen kaava on:

 
=B1+C1+D1 // formula in E1

Kirjaimellisesti tämä tarkoittaa 'solu 3 saraketta vasemmalla' + 'solu 2 saraketta vasemmalla' + 'solu 1 sarake vasemmalla'. Siksi, kun kaava kopioidaan alas soluun E2, se jatkaa toimintaansa samalla tavalla.

Suhteelliset viitteet ovat erittäin hyödyllisiä, mutta on aikoja, jolloin et halua soluviitteen muuttuvan. Soluviittausta, joka ei muutu kopioitaessa, kutsutaan ehdoton viite . Käytä absoluuttista vertailua käyttämällä dollarin symbolia ($):

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

Esimerkiksi alla olevassa näytössä haluamme kertoa kukin sarakkeen D arvo 10: llä, joka syötetään A1: ään. Käyttämällä absoluuttista viitettä A1: lle 'lukitsemme' kyseisen viitteen, joten se ei muutu, kun kaava kopioidaan E2: een ja E3: een:

Absoluuttinen vertailuesimerkki

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

 
=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: een ei koskaan muutu. Nyt voimme helposti muuttaa arvoa A1: ssä, ja kaikki kolme kaavaa laskevat uudelleen. A1: n alapuolella oleva arvo on muuttunut 10: stä 12: een:

Absoluuttinen vertailuesimerkki sen jälkeen kun arvon A1 muutetaan

Tämä yksinkertainen esimerkki osoittaa myös, miksi ei ole järkevää koodata arvoja kaavaksi. Tallentamalla arvo A1: een yhteen paikkaan ja viittaamalla A1: ään merkillä ehdoton viite , arvoa voidaan muuttaa milloin tahansa, ja kaikki siihen liittyvät kaavat päivittyvät välittömästi.

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

Kaavan syöttäminen

Kaavan syöttäminen:

  1. Valitse solu
  2. Syötä yhtälömerkki (=)
  3. Kirjoita kaava ja paina Enter.

Soluviittausten kirjoittamisen sijaan voit osoittaa ja napsauttaa alla näkyvällä tavalla. Huomautusviitteet ovat värikoodattuja:

Kaavan kirjoittaminen piste- ja napsautusviitteillä

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

Unohdettu syöttää yhtäsuuri merkki ei tarkoita kaavaa, vain tekstiä

Kaavan muuttaminen

Kaavan muokkaamiseksi sinulla on 3 vaihtoehtoa:

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

Vahvista muutokset, kun olet valmis, painamalla Enter-näppäintä. Jos haluat peruuttaa ja jättää kaavan muuttumattomaksi, napsauta Escape-näppäintä.

Video: 20 vinkkiä kaavojen syöttämiseen

Mikä on funktio?

Kun työskentelet Excelissä, kuulet sanojen 'kaava' ja 'toiminto' käyttämisen usein, joskus vaihdettavissa. Ne ovat läheisessä yhteydessä 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 funktioilla on nimiä, jotka heijastavat niiden käyttötarkoitusta. Esimerkiksi, luultavasti tiedät SUM-toiminto jo, joka palauttaa annettujen viitteiden summan:

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

AVERAGE-toiminto , kuten voit odottaa, palauttaa annettujen viitteiden keskiarvon:

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

MIN- ja MAX-toiminnot palauttavat vastaavasti vähimmäis- ja enimmäisarvot:

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

Excel sisältää satoja erityisiä toimintoja . Aloita katsomalla 101 keskeistä Excel-toimintoa .

Funktion argumentit

Useimmat toiminnot vaativat tuloja tuloksen palauttamiseksi. Näitä syötteitä kutsutaan argumenteiksi. Funktion argumentit näkyvät funktion nimen jälkeen sulkeissa pilkuilla erotettuna. Kaikki toiminnot edellyttävät vastaavia sulkeita (). Kuvio näyttää tältä:

 
=FUNCTIONNAME(argument1,argument2,argument3)

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

 
= COUNTIF (range,criteria) // two arguments

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

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

COUNTIF vaatii kaksi argumenttia, alueen ja ehdot

Video: Kuinka käyttää COUNTIF-toimintoa

Kaikkia argumentteja ei vaadita. Esitetyt argumentit hakasulkeissa ovat valinnaisia. Esimerkiksi YEARFRAC-toiminto palauttaa aloituspäivän ja lopetuspäivän välisen vuosien lukumäärän ja sisältää 3 argumenttia:

 
= YEARFRAC (start_date,end_date,[basis])

Aloitus- ja lopetuspäivä ovat pakollisia argumentteja, pohja on valinnainen argumentti. Alla on esimerkki siitä, miten YEARFRACia käytetään nykyisen iän laskemiseen syntymäpäivän perusteella.

Toiminnon syöttäminen

Jos tiedät funktion nimen, aloita kirjoittaminen. Tässä ovat vaiheet:

1. Syötä yhtälömerkki (=) ja ala kirjoittaa. Excel luetteloi vastaavat toiminnot kirjoittaessasi:

Kun kirjoitat, Excel näyttää vastaavat toiminnot

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

2. Hyväksy toiminto kirjoittamalla sarkainnäppäin. Excel suorittaa toiminnon loppuun:

Siirry valittuun toimintoon painamalla sarkainta

3. Täytä vaaditut argumentit:

Syötä vaaditut argumentit

4. Vahvista kaava painamalla Enter-näppäintä:

Paina Enter vahvistaaksesi ja syöttääksesi toiminnon

Toimintojen yhdistäminen (sisäkkäin)

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

Tarvitaan kaava laskemaan nykyinen ikä B2: ssa

YEARFRAC-toiminto laskee vuodet alkamis- ja lopetuspäivillä:

YEARFRAC laskee vuodet alkamis- ja lopetuspäivämäärillä

Voimme käyttää B1: tä aloituspäivänä ja käyttää sitten TÄNÄÄN-toiminto toimittaa lopetuspäivä:

B1 aloituspäivämäärälle, TODAY-toiminto lopetuspäivän toimittamiseksi

Kun painamme Enter vahvistaaksesi, saamme nykyisen iän tämän päivän päivämäärän perusteella:

 
= YEARFRAC (B1, TODAY ())

YEARFRAC- ja TODAY-toiminnot nykyisen iän laskemiseksi

Huomaa, että käytämme TODAY-toimintoa lopetuspäivän syöttämiseen YEARFRAC-funktiolle. Toisin sanoen TODAY-funktio voidaan sijoittaa YEARFRAC-funktioon lopetuspäivämäärän antamiseksi. Voimme viedä kaavan yhden askeleen pidemmälle ja käyttää INT-toiminto pilkkoa desimaaliarvo:

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

YEARFRAC ja TÄNÄÄN INT: n sisällä

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

Huomautuksia:

  1. Yllä olevien kuvien nykyinen päivämäärä on 22. helmikuuta 2019.
  2. Sisäkkäin JOS toimii ovat klassinen esimerkki pesimistoiminnot .
  3. TÄNÄÄN-toiminto on harvinainen Excel-funktio ilman vaadittuja argumentteja.

Avaimen takeaway: Kaavan tai funktion tulos voidaan syöttää suoraan toiseen kaavaan tai toimintoon.

Matematiikkaoperaattorit

Alla olevassa taulukossa esitetään Excelissä käytettävissä olevat vakio matemaattiset operaattorit:

Symboli Operaatio Esimerkki
+ Lisäys = 2 + 3 = 5
- Vähennyslasku = 9-2 = 7
* Kertolasku = 6 * 7 = 42
/ Divisioona = 9/3 = 3
^ Eksponentio = 4 ^ 2 = 16
() Sulkeet = (2 + 4) / 3 = 2

Loogiset operaattorit

Loogiset operaattorit tarjoavat vertailuja, kuten 'suurempi kuin', 'vähemmän kuin' jne. Excelissä käytettävissä olevat loogiset operaattorit näkyvät alla olevassa taulukossa:

Operaattori Tarkoitus Esimerkki
= Yhtä kuin = A1 = 10
Ei ole yhtä suuri kuin = A110
> Suurempi kuin = A1> 100
< Vähemmän kuin = A1<100
> = Suurempi tai yhtä suuri kuin = A1> = 75
<= Pienempi kuin tai yhtä suuri kuin = A1<=0

Video: Kuinka rakentaa loogisia kaavoja

Toimintojen järjestys

Kaavaa ratkaistessaan Excel noudattaa järjestystä, jota kutsutaan operaatioiden järjestykseksi. Ensin arvioidaan suluissa olevat lausekkeet. Seuraava Excel ratkaisee kaikki eksponentit. Eksponenttien jälkeen Excel suorittaa kertomisen ja jakamisen, sitten yhteenlaskemisen ja vähentämisen. Jos kaava sisältää ketjutus , tämä tapahtuu normaalien matemaattisten operaatioiden jälkeen. Lopuksi Excel arvioi loogiset operaattorit , jos läsnä.

  1. Sulkeet
  2. Eksponentit
  3. Kertolasku ja jako
  4. Yhteen-ja vähennyslasku
  5. Ketjutus
  6. Loogiset operaattorit

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

Muunna kaavat arvoiksi

Joskus haluat päästä eroon kaavoista ja jättää vain arvot paikoilleen. Helpoin tapa tehdä tämä Excelissä on kopioida kaava ja liittää se sitten käyttämällä Liitä erityinen> Arvot. Tämä korvaa kaavat palauttamillaan arvoilla. Voit käyttää a pikanäppäin arvojen liittämiseen tai käytä Liitä-valikkoa valintanauhan Koti-välilehdessä.

Video: Liitä erityiset pikakuvakkeet

Excelin ehdollinen muotoilu ei ole sama kuin toinen solu

Mitä seuraavaksi?

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

Kirjailija Dave Bruns


^