Excelove formule & Funkcije: Učite se z osnovnimi PRIMERI

Formule in funkcije so gradniki dela s številskimi podatki v Excelu. Ta članek vas seznani s formulami in funkcijami.

V tem članku bomo obravnavali naslednje teme.

• Kaj so formule v Excelu?
• Napake, ki se jim je treba izogniti pri delu s formulami v Excelu
• Kaj je funkcija v Excelu?
• Pomen funkcij
• Skupne funkcije
• Numerične funkcije
• String funkcije
• Date Time funkcije
• V Funkcija iskanja

Podatki o vajah

Za to vadnico bomo delali z naslednjimi nabori podatkov.

Proračun za oskrbo z domom

S / N	POSTAVKA	KOL	CENA	VSEBINA	Je dostopno?
1.	Mango	9.	600
2.	Pomaranče	3.	1200
3.	Paradižnik	1.	2500
4.	Olje za kuhanje	5.	6500
5.	Tonična voda	13.	3900

Urnik projekta gradnje hiš

S / N	POSTAVKA	ZAČETNI DATUM	KONČNI DATUM	TRAJANJE (DNI)
1.	Raziskovalno zemljišče	02.04.2015	02.07.2015
2.	Lay Foundation	02.10.2015	15.2.2015
3.	Krovstvo	27.2.2015	03.03.2015
4.	Slika	03.09.2015	21. 3. 2015

Kaj so formule v Excelu?

FORMULAS IN EXCEL je izraz, ki deluje na vrednosti v območju naslovov celic in operatorjev. Na primer, = A1 + A2 + A3, ki najde vsoto obsega vrednosti od celice A1 do celice A3. Primer formule, sestavljene iz diskretnih vrednosti, kot je = 6 * 3.

=A2 * D2 / 2

TUKAJ,

• pove Excelu, da je to formula, in jo mora oceniti.
• "A2" * D2" se sklicuje na naslove celic A2 in D2, nato pomnoži vrednosti, najdene v teh naslovih celic.
• "/" je delitveni aritmetični operater
• "2" je diskretna vrednost

Formula praktična vaja

Za izračun vmesne vsote bomo delali z vzorčnimi podatki za domači proračun.

• Ustvarite nov delovni zvezek v Excelu
• Vnesite podatke, prikazane v zgornjem proračunu za gospodinjske potrebščine.
• Vaš delovni list naj bo videti takole.

Zdaj bomo napisali formulo, ki izračuna vmesni seštevek

Nastavite fokus na celico E4

Vnesite naslednjo formulo.

=C4*D4

TUKAJ,

• "C4*D4" uporablja množenje aritmetičnega operaterja (*) za množenje vrednosti naslova celice C4 in D4.

Pritisnite tipko Enter

Dobili boste naslednji rezultat

Naslednja animirana slika prikazuje, kako samodejno izbrati naslov celice in uporabiti isto formulo za druge vrstice.

Napake, ki se jim je treba izogniti pri delu s formulami v Excelu

1. Ne pozabite na pravila oklepajev deljenja , množenja, seštevanja in odštevanja (BODMAS). To pomeni, da so izrazi v oklepajih najprej ovrednoteni. Pri aritmetičnih operatorjih se najprej izračuna delitev, ki ji sledi množenje, nato je seštevanje in odštevanje zadnje, ki ga je treba oceniti. Z uporabo tega pravila lahko zgornjo formulo prepišemo kot = (A2 * D2) / 2. To bo zagotovilo, da se A2 in D2 najprej ovrednotita in nato delita z dvema.
2. Formule preglednic Excel običajno delujejo s številskimi podatki; potrditev podatkov lahko izkoristite za določitev vrste podatkov, ki naj jih sprejme celica, tj. samo številke.
3. Če želite zagotoviti, da delate s pravilnimi naslovi celic, na katere se sklicujejo formule, lahko na tipkovnici pritisnete tipko F2. To bo označilo naslove celic, uporabljenih v formuli, in lahko navzkrižno preverite, ali so to želeni naslovi celic.
4. Ko delate z več vrsticami, lahko uporabite serijske številke za vse vrstice in na dnu lista imate število zapisov. Primerjajte število serijskih številk s skupnim zapisom, da zagotovite, da so vaše formule vse vrstice.

Oglejte si 10 najboljših formul preglednic Excel

Kaj je funkcija v Excelu?

FUNCTION IN EXCEL je vnaprej določena formula, ki se uporablja za določene vrednosti v določenem vrstnem redu. Funkcija se uporablja za hitre naloge, kot je iskanje vsote, štetja, povprečja, največje vrednosti in najmanjših vrednosti za obseg celic. Na primer, spodnja celica A3 vsebuje funkcijo SUM, ki izračuna vsoto obsega A1: A2.

• SUM za seštevanje obsega števil
• AVERAGE za izračun povprečja določenega obsega števil
• COUNT za štetje števila elementov v določenem obsegu

Pomen funkcij

Funkcije povečajo produktivnost uporabnikov pri delu z excelom . Recimo, da bi radi dobili skupni znesek za zgornji proračun za oskrbo z domovi. Za poenostavitev lahko s formulo dobite vsoto. Z uporabo formule bi morali eno po eno sklicevati na celice E4 do E8. Uporabiti bi morali naslednjo formulo.

= E4 + E5 + E6 + E7 + E8

S funkcijo bi zgornjo formulo zapisali kot

=SUM (E4:E8)

Kot lahko vidite iz zgornje funkcije, ki se uporablja za pridobivanje vsote obsega celic, je veliko bolj učinkovito uporabiti funkcijo za pridobitev vsote kot uporaba formule, ki se bo morala sklicevati na veliko celic.

Skupne funkcije

Oglejmo si nekatere najpogosteje uporabljene funkcije v formulah ms excel. Začeli bomo s statističnimi funkcijami.

S / N	FUNKCIJA	KATEGORIJA	OPIS	UPORABA
01	SUM	Matematika in trig	Doda vse vrednosti v obsegu celic	= SUM (E4: E8)
02	MIN	Statistični	Poišče najmanjšo vrednost v območju celic	= MIN (E4: E8)
03	NAJVEČ	Statistični	Poišče največjo vrednost v območju celic	= MAX (E4: E8)
04	POVPREČNO	Statistični	Izračuna povprečno vrednost v območju celic	= POVPREČNO (E4: E8)
05	ŠT	Statistični	Šteje število celic v območju celic	= ŠTEVILO (E4: E8)
06	LEN	Besedilo	Vrne število znakov v besedilnem nizu	= LEN (B7)
07	SUMIF	Matematika in trig	Doda vse vrednosti v obsegu celic, ki ustrezajo določenim merilom. = SUMIF (obseg, merila, [obseg_seštevanja])	= SUMIF (D4: D8, "> = 1000", C4: C8)
08	AVERAGEIF	Statistični	Izračuna povprečno vrednost v obsegu celic, ki ustrezajo določenim merilom. = AVERAGEIF (obseg, merila, [povprečni_obseg])	= AVERAGEIF (F4: F8, "Da", E4: E8)
09	DNEVI	Datum čas	Vrne število dni med dvema datumoma	= DNI (D4, C4)
10.	ZDAJ	Datum čas	Vrne trenutni sistemski datum in uro	= ZDAJ ()

Numerične funkcije

Kot že ime pove, te funkcije delujejo na numeričnih podatkih. Naslednja tabela prikazuje nekatere pogoste številske funkcije.

S / N	FUNKCIJA	KATEGORIJA	OPIS	UPORABA
1.	ŠTEVILO	Informacije	Vrne True, če je navedena vrednost številska, in False, če ni številčna	= ŠTEVILO (A3)
2.	RAND	Matematika in trig	Ustvari naključno število med 0 in 1	= RAND ()
3.	OKROGLA	Matematika in trig	Zaokroži decimalno vrednost na določeno število decimalnih mest	= KROG (3.14455,2)
4.	MEDIJAN	Statistični	Vrne številko sredi nabora danih številk	= MEDIAN (3,4,5,2,5)
5.	PI	Matematika in trig	Vrne vrednost matematične funkcije PI (π)	= PI ()
6.	MOČ	Matematika in trig	Vrne rezultat števila, zvišanega v potenco. MOČ (število, moč)	= MOČ (2,4)
7.	MOD	Matematika in trig	Vrne ostanek, ko delite dve številki	= MOD (10,3)
8.	ROMAN	Matematika in trig	Pretvori število v rimske številke	= ROMAN (1984)

String funkcije

Te osnovne funkcije excel se uporabljajo za obdelavo besedilnih podatkov. Naslednja tabela prikazuje nekatere pogoste funkcije nizov.

S / N	FUNKCIJA	KATEGORIJA	OPIS	UPORABA	PRIPOMBA
1.	LEVO	Besedilo	Vrne število določenih znakov z začetka (leva stran) niza	= LEVO ("GURU99", 4)	Levo 4 znaki "GURU99"
2.	PRAV	Besedilo	Vrne število določenih znakov s konca (desna stran) niza	= DESNO ("GURU99", 2)	Desno 2 znaka "GURU99"
3.	SREDNJI	Besedilo	Pridobi število znakov iz sredine niza z določenega začetnega položaja in dolžine. = MID (besedilo, začetna_številka, številski znaki)	= MID ("GURU99", 2,3)	Pridobivanje znakov od 2 do 5
4.	ISTEXT	Informacije	Vrne True, če je priloženi parameter besedilo	= ISTEXT (vrednost)	vrednost - vrednost, ki jo je treba preveriti.
5.	NAJTI	Besedilo	Vrne začetni položaj besedilnega niza znotraj drugega besedilnega niza. Ta funkcija razlikuje med velikimi in malimi črkami. = POIŠČI (najdi_besedilo, znotraj_besedila, [start_num])	= POIŠČI ("oo", "Krovstvo", 1)	Poiščite oo v "Krovstvo", rezultat je 2
6.	ZAMENJATI	Besedilo	Nadomešča del niza z drugim določenim nizom. = ZAMENI (staro_besedilo, začetna_številka, številski znaki, novo_besedilo)	= REPLACE ("Krovstvo", 2,2, "xx")	Zamenjajte "oo" z "xx"

Funkcije datuma in časa

Te funkcije se uporabljajo za manipulacijo z datumskimi vrednostmi. Naslednja tabela prikazuje nekatere pogoste funkcije datuma

S / N	FUNKCIJA	KATEGORIJA	OPIS	UPORABA
1.	DATUM	Datum čas	Vrne številko, ki predstavlja datum v Excelovi kodi	= DATUM (2015,2,4)
2.	DNEVI	Datum čas	Poiščite število dni med dvema datumoma	= DNI (D6, C6)
3.	MESEC	Datum čas	Vrne mesec iz vrednosti datuma	= MESEC ("2.4.2015")
4.	MINUTE	Datum čas	Vrne minute iz časovne vrednosti	= MINUTA ("12:31")
5.	LETO	Datum čas	Vrne leto iz vrednosti datuma	= LETO ("02.02.2015")

VLOOKUP funkcija

Funkcija VLOOKUP se uporablja za izvedbo navpičnega pogleda navzgor v najbolj levem stolpcu in vrnitev vrednosti v isti vrstici iz stolpca, ki ste ga določili. Razložimo to v laičnem jeziku. Proračun za oskrbo z domom ima stolpec s serijsko številko, ki enolično identificira vsako postavko v proračunu. Recimo, da imate serijsko številko elementa in bi radi vedeli opis artikla, lahko uporabite funkcijo VLOOKUP. Tukaj je, kako bi delovala funkcija VLOOKUP.

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

TUKAJ,

• "=VLOOKUP" pokliče funkcijo navpičnega iskanja
• "C12" določa vrednost, ki jo je treba iskati v najbolj levem stolpcu
• "A4:B8" določa polje tabele s podatki
• "2" določa številko stolpca z vrednostjo vrstice, ki jo vrne funkcija VLOOKUP
• "FALSE," pove funkciji VLOOKUP, da iščemo natančno ujemanje s priloženo vrednostjo iskanja

Spodnja animirana slika to prikazuje v akciji

Prenesite zgornjo kodo Excel

Povzetek

Excel vam omogoča obdelavo podatkov s pomočjo formul in / ali funkcij. Funkcije so na splošno bolj produktivne v primerjavi s pisanjem formul. Funkcije so tudi bolj natančne v primerjavi s formulami, ker je možnost napak zelo minimalna.

Tu je seznam pomembnih Excelovih formul in funkcij

• Funkcija SUM = =SUM(E4:E8)

• MIN funkcija = =MIN(E4:E8)

• MAX funkcija = =MAX(E4:E8)

• POVPREČNA funkcija = =AVERAGE(E4:E8)

• Funkcija COUNT = =COUNT(E4:E8)

• Funkcija DAYS = =DAYS(D4,C4)

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

• DATE funkcija = =DATE(2020,2,4)