Exceli valemid & Funktsioonid: õppige põhinäidete abil

Valemid ja funktsioonid on Exceli arvandmetega töötamise ehituskivid. See artikkel tutvustab teile valemeid ja funktsioone.

Selles artiklis käsitleme järgmisi teemasid.

• Mis on Exceli valemid?
• Excelis valemitega töötamisel tuleb vältida vigu
• Mis on Exceli funktsioon?
• Funktsioonide tähtsus
• Ühised funktsioonid
• Numbrilised funktsioonid
• Stringi funktsioonid
• Kuupäev ja kellaaeg funktsioonid
• V Otsingu funktsioon

Õpetuste andmed

Selle õpetuse jaoks töötame järgmiste andmekogumitega.

Kodutarvete eelarve

S / N	PUNKT	KOGUS	HIND	KOKKU	Kas see on taskukohane?
1	Mangod	9	600
2	Apelsinid	3	1200
3	Tomatid	1	2500
4	Toiduõli	5	6500
5	Toonik	13	3900

Majaehituse projekti ajakava

S / N	PUNKT	ALGUS KUUPÄEV	LÕPP KUUPÄEV	KESTUS (PÄEVAD)
1	Uuringumaa	04.02.2015	07.02.2015
2	Lay Foundation	10.02.2015	15.02.2015
3	Katusetööd	27.02.2015	03.03.2015
4	Maalimine	09.03.2015	21.03.2015

Mis on Exceli valemid?

FORMULID EXCELis on avaldis, mis toimib väärtuste suhtes lahtri aadresside ja operaatorite vahemikus. Näiteks = A1 + A2 + A3, mis leiab väärtuste vahemiku summa lahtrist A1 kuni lahtrini A3. Näide valemist, mis koosneb diskreetsetest väärtustest nagu = 6 * 3.

=A2 * D2 / 2

SIIN,

• ütleb Excelile, et see on valem ja ta peaks seda hindama.
• "A2" * D2" viitab lahtriaadressidele A2 ja D2, seejärel korrutab nendes lahtriaadressides leitud väärtused.
• "/" on jagamise aritmeetikaoperaator
• "2" on diskreetne väärtus

Valemid praktiline harjutus

Vahesumma arvutamiseks töötame kodu eelarve näidisandmetega.

• Looge Excelis uus töövihik
• Sisestage ülaltoodud kodutarvete eelarves näidatud andmed.
• Teie tööleht peaks välja nägema järgmine.

Nüüd kirjutame valemi, mis arvutab vahesumma

Määrake fookus lahtrisse E4

Sisestage järgmine valem.

=C4*D4

SIIN,

• "C4*D4" kasutab aritmeetilise operaatori korrutamist (*) lahtri aadresside C4 ja D4 väärtuse korrutamiseks.

Vajutage sisestusklahvi

Saad järgmise tulemuse

Järgmine animeeritud pilt näitab, kuidas lahtriaadressi automaatselt valida ja sama valemit teistele ridadele rakendada.

Excelis valemitega töötamisel tuleb vältida vigu

1. Pidage meeles jagamise , korrutamise, liitmise ja lahutamise sulgude (BODMAS) reegleid. See tähendab, et väljendeid hinnatakse kõigepealt sulgudes. Aritmeetikaoperaatorite puhul hinnatakse kõigepealt jagamist, millele järgneb korrutamine, seejärel liidetakse ja lahutatakse viimasena. Selle reegli abil saame ülaltoodud valemi ümber kirjutada = (A2 * D2) / 2. See tagab, et kõigepealt hinnatakse A2 ja D2, seejärel jagatakse need kahega.
2. Exceli arvutustabeli valemid töötavad tavaliselt arvandmetega; andmete valideerimise abil saate täpsustada, millist tüüpi teave peaks lahtri aktsepteerima, st ainult numbrid.
3. Selleks, et töötaksite valemites viidatud õigete lahtriaadressidega, võite klaviatuuril vajutada klahvi F2. See toob esile valemis kasutatud lahtriaadressid ja saate ristkontrollida, kas need on soovitud lahtriaadressid.
4. Kui töötate paljude ridadega, võite kasutada kõigi ridade seerianumbreid ja lehe allosas on rekordarv. Peaksite võrdlema seerianumbrite arvu kirjete koguarvuga, et teie valemid sisaldaksid kõiki ridu.

Vaadake 10 parimat Exceli arvutustabeli valemit

Mis on Exceli funktsioon?

FUNCTION IN EXCEL on eelnevalt määratletud valem, mida kasutatakse konkreetsete väärtuste jaoks kindlas järjekorras. Funktsiooni kasutatakse kiirete ülesannete täitmiseks, näiteks lahtrivahemiku summa, loendamine, keskmine, maksimaalne väärtus ja minimaalsed väärtused. Näiteks allpool olev lahter A3 sisaldab funktsiooni SUM, mis arvutab vahemiku A1: A2 summa.

• SUM numbrivahemiku liitmiseks
• KESKMINE antud arvude vahemiku keskmise arvutamiseks
• COUNT antud vahemikus olevate üksuste arvu lugemiseks

Funktsioonide tähtsus

Exceliga töötades suurendavad funktsioonid kasutajate tootlikkust . Oletame, et soovite saada ülaltoodud kodutarvete eelarve kogusummat. Selle lihtsustamiseks võite kokkuvõtte saamiseks kasutada valemit. Valemi abil peaksite lahtrid E4 kuni E8 ükshaaval viitama. Peaksite kasutama järgmist valemit.

= E4 + E5 + E6 + E7 + E8

Funktsiooniga kirjutaksite ülaltoodud valemi kujul

=SUM (E4:E8)

Nagu näete ülaltoodud funktsioonist, mida kasutatakse lahtrivahemiku summa saamiseks, on summa saamiseks palju tõhusam kasutada funktsiooni kui valemi abil, mis peab viitama paljudele lahtritele.

Ühised funktsioonid

Vaatame mõnda enimkasutatavat funktsiooni ms exceli valemites. Alustame statistiliste funktsioonidega.

S / N	FUNKTSIOON	KATEGOORIA	KIRJELDUS	KASUTAMINE
01	SUM	Math & Trig	Lisab kõik lahtrivahemiku väärtused	= SUM (E4: E8)
02	MIN	Statistiline	Leiab lahtrivahemiku minimaalse väärtuse	= MIN (E4: E8)
03	MAX	Statistiline	Leiab maksimaalse väärtuse lahtrivahemikust	= MAX (E4: E8)
04	KESKMINE	Statistiline	Arvutab lahtrivahemiku keskmise väärtuse	= KESKMINE (E4: E8)
05	COUNT	Statistiline	Loendab lahtrite arvu lahtrite vahemikus	= COUNT (E4: E8)
06	LEN	Tekst	Tagastab stringi teksti märkide arvu	= LEN (B7)
07	SUMIF	Math & Trig	Lisab kõik lahtrite väärtused, mis vastavad määratud kriteeriumidele. = SUMIF (vahemik, kriteeriumid, [summa_vahemik])	= SUMIF (D4: D8, "> = 1000", C4: C8)
08	KESKMINE	Statistiline	Arvutab keskmise väärtuse lahtrite vahemikus, mis vastavad määratud kriteeriumidele. = AVERAGEIF (vahemik, kriteeriumid, [keskmine_vahemik])	= KESKMINE FIF (F4: F8, "Jah", E4: E8)
09	PÄEVAD	Kuupäev Kellaaeg	Tagastab kahe kuupäeva vaheliste päevade arvu	= PÄEVAD (D4, C4)
10	KOHE	Kuupäev Kellaaeg	Tagastab praeguse süsteemi kuupäeva ja kellaaja	= KOHE ()

Numbrilised funktsioonid

Nagu nimigi ütleb, toimivad need funktsioonid arvandmetel. Järgmises tabelis on toodud mõned levinumad arvfunktsioonid.

S / N	FUNKTSIOON	KATEGOORIA	KIRJELDUS	KASUTAMINE
1	ISNUMBER	Teave	Tagastab tõese, kui sisestatud väärtus on arvuline, ja vale, kui see pole arvuline	= ISNUMBER (A3)
2	RAND	Math & Trig	Genereerib juhusliku arvu vahemikus 0 kuni 1	= RAND ()
3	ÜMBER	Math & Trig	Ümardab kümnendarvu määratud arvu kümnendkohtadeni	= RING (3.14455,2)
4	KESKMINE	Statistiline	Tagastab antud numbrite hulga keskel oleva arvu	= KESKMINE (3,4,5,2,5)
5	PI	Math & Trig	Tagastab matemaatikafunktsiooni PI väärtuse (π)	= PI ()
6	VÕIM	Math & Trig	Tagastab arvu, mis on tõstetud astmeks. POWER (arv, võimsus)	= VÕIM (2,4)
7	MOD	Math & Trig	Tagastab jäägi, kui jagate kaks numbrit	= MOD (10,3)
8	ROMA	Math & Trig	Teisendab numbri rooma numbriteks	= ROMAN (1984)

Stringi funktsioonid

Neid Exceli põhifunktsioone kasutatakse tekstiandmete manipuleerimiseks. Järgmises tabelis on esitatud mõned tavalised stringifunktsioonid.

S / N	FUNKTSIOON	KATEGOORIA	KIRJELDUS	KASUTAMINE	KOMMENTAAR
1	VASAK	Tekst	Tagastab stringi algusest (vasakust) määratud arvu tähemärke	= VASAK ("GURU99", 4)	Jätsid "GURU99" 4 tähemärki
2	PAREM	Tekst	Tagastab stringi lõpust (paremast küljest) määratud arvu tähemärke	= PAREM ("GURU99", 2)	Paremal "GURU99" 2 tähemärki
3	KESK	Tekst	Toob märgi arvu stringi keskelt määratud algusasendist ja pikkusest. = MID (tekst, algusnumber, numbrimärgid)	= KESK ("GURU99", 2,3)	Märkide 2 kuni 5 toomine
4	ISTEXT	Teave	Tagastab väärtuse Tõene, kui pakutav parameeter on Tekst	= ISTEXT (väärtus)	väärtus - kontrollitav väärtus.
5	LEIA	Tekst	Tagastab tekstistringi algpositsiooni teises tekstistringis. See funktsioon on tõstutundlik. = LEIA (leia_tekst, sees-tekst, [algusnumber])	= LEIA ("oo", "Katusetööd", 1)	Leidke oo jaotisest "Katusetööd", tulemus on 2
6	ASENDA	Tekst	Asendab stringi osa teise määratud stringiga. = REPLACE (vana_tekst, algusnumber, numbrimärgid, uus_tekst)	= REPLACE ("Katusetööd", 2,2, "xx")	Asenda "oo" sõnaga "xx"

Kuupäeva ja kellaaja funktsioonid

Neid funktsioone kasutatakse kuupäeva väärtuste manipuleerimiseks. Järgmises tabelis on toodud mõned levinumad kuupäevafunktsioonid

S / N	FUNKTSIOON	KATEGOORIA	KIRJELDUS	KASUTAMINE
1	KUUPÄEV	Kuupäev Kellaaeg	Tagastab numbri, mis tähistab Exceli koodis kuupäeva	= KUUPÄEV (2015,2,4)
2	PÄEVAD	Kuupäev Kellaaeg	Leidke kahe kuupäeva vaheliste päevade arv	= PÄEVAD (D6, C6)
3	KUU	Kuupäev Kellaaeg	Tagastab kuu kuupäeva väärtusest	= KUU ("04.04.2015")
4	MINUT	Kuupäev Kellaaeg	Tagastab minutid ajaväärtusest	= MINUT ("12:31")
5	AASTA	Kuupäev Kellaaeg	Tagastab aasta kuupäeva väärtusest	= YEAR ("04.02.2015")

Funktsioon VLOOKUP

Funktsiooni VLOOKUP kasutatakse kõige vasakpoolsemas veerus vertikaalse otsingu teostamiseks ja teie määratud veerust sama rea ​​väärtuse tagastamiseks. Seletame seda võhikukeeles. Kodutarvete eelarves on seerianumbri veerg, mis identifitseerib eelarve iga üksuse üheselt. Oletame, et teil on toote seerianumber ja soovite teada toote kirjeldust, saate kasutada funktsiooni VLOOKUP. Funktsioon VLOOKUP toimiks järgmiselt.

=VLOOKUP (C12, A4:B8, 2, FALSE)

SIIN,

• "=VLOOKUP" kutsub vertikaalse otsingu funktsiooni
• "C12" määrab väärtuse, mida otsitakse kõige vasakpoolsest veerust
• "A4:B8" määrab tabelimassiivi koos andmetega
• "2" määrab veeru numbri koos rea väärtusega, mille funktsioon VLOOKUP tagastab
• "FALSE," ütleb funktsioonile VLOOKUP, et otsime pakutava otsingu väärtuse täpset vastet

Allpool olev animeeritud pilt näitab seda toimimises

Laadige ülaltoodud Exceli kood alla

Kokkuvõte

Excel võimaldab teil andmetega manipuleerida valemite ja / või funktsioonide abil. Funktsioonid on üldiselt valemite kirjutamisega võrreldes produktiivsemad. Funktsioonid on valemitega võrreldes ka täpsemad, kuna vigade tegemise varu on väga minimaalne.

Siin on loetelu olulistest Exceli valemitest ja funktsioonidest

• Funktsioon SUM = =SUM(E4:E8)

• MIN funktsioon = =MIN(E4:E8)

• Funktsioon MAX = =MAX(E4:E8)

• Funktsioon KESKMINE = =AVERAGE(E4:E8)

• Funktsioon COUNT = =COUNT(E4:E8)

• PÄEVADE funktsioon = =DAYS(D4,C4)

• Funktsioon VLOOKUP = =VLOOKUP (C12, A4:B8, 2, FALSE)

• Funktsioon KUUPÄEV = =DATE(2020,2,4)