Exceli valemid: mis on Exceli valemid ja kuidas neid kasutada

Termin "Exceli valemid" võib viidata mis tahes kombinatsioonile operaator Excel ja/või Exceli funktsioonid.

Exceli valem sisestatakse tabeli lahtrisse, sisestades = märgi, millele järgneb nõutavad operaatorid ja/või funktsioonid. See võib olla nii lihtne kui põhilisand (nt "=A1+B1") või Exceli operaatorite ja mitme pesastatud Exceli funktsiooni kompleksne kombinatsioon.

Exceli operaatorid

Exceli operaatorid teostavad toiminguid arvväärtuste, teksti või lahtriviidetega. Exceli operaatoreid on nelja erinevat tüüpi.

Sono küsimus:

• Aritmeetiline operaatorite
• Tekstioperaatorid
• Võrdlusoperaatorid
• Võrdlusoperaatorid

Kirjeldame nelja tüüpi operaatoreid:

Aritmeetiline operaatorite

Exceli aritmeetilised operaatorid ja nende hindamise järjekord on näidatud järgmises tabelis.

Aritmeetiliste operaatorite tähtsus

Ülaltoodud tabelist on näha, et protsendi- ja astenditetehed on kõige kõrgemad, järgnevad korrutamis- ja jagamistehed ning seejärel liitmise ja lahutamise operaatorid. Seetõttu hinnatakse Exceli valemite hindamisel, mis sisaldavad rohkem kui ühte aritmeetilist operaatorit, esmalt protsent- ja eksponentsiaaltehteid, seejärel korrutamise ja jagamise operaatoreid. Lõpuks hinnatakse liitmise ja lahutamise operaatoreid.

Aritmeetiliste operaatorite hindamise järjekord on Exceli valemi tulemusel suur erinevus. Sulgusid saab aga kasutada selleks, et sundida valemi osi esmalt hindama. Kui osa valemist on sulgudes, on valemi sulgudes olev osa kõigi ülalloetletud operaatorite suhtes ülimuslik. Seda illustreerivad järgmised näited.

Aritmeetiliste operaatorite näited

Exceli tekstioperaator

Exceli konkatenatsioonioperaator (tähistatakse sümboliga &) ühendab tekstistringid, et luua täiendav üks tekstistring.

Konkatenatsioonioperaatori näide

Järgmine valem kasutab tekstistringide ühendamiseks konkatenatsioonioperaatorit "SMITH" " ja "John"

Exceli võrdlusoperaatorid

Selleks kasutatakse Exceli võrdlusoperaatoreid defitingimusi, näiteks funktsiooni kasutamisel IF Excelist. Need operaatorid on loetletud järgmises tabelis:

Võrdlusoperaatorite näited

Allolevad tabelid näitavad funktsiooniga kasutatavate võrdlustehitajate näiteid IF Excelist.

Võrdlusoperaatorid

Exceli viiteoperaatoreid kasutatakse arvutustabeli vahemikele viitamisel. Võrdlusoperaatorid on:

Näited viiteoperaatoritest

Näide 1 – Exceli vahemiku operaator

Järgmise arvutustabeli lahter C1 näitab vahemiku operaatorit, mille jaoks kasutatakse defilõpetage intervall A1-B3. Seejärel antakse vahemik funktsioonile SUM Exceli, mis lisab lahtritesse väärtused A1-B3 ja tagastab väärtuse 21.

Näide 2 – Exceli liitoperaator

Rakk C1 järgmisest arvutustabelist on näidatud ametiühingu operaator, mida kasutatakse jaoks define vahemik, mis koosneb kahe vahemiku rakkudest A1-A3 e A1-B1. Saadud vahemik edastatakse seejärel funktsioonile SUM Excelis, mis summeerib kombineeritud vahemikus olevad väärtused ja tagastab väärtuse 12.

Pange tähele, et Exceli ühenduse operaator ei tagasta tõelist matemaatilist ühendust, näiteks lahtrit A1, mis sisaldub mõlemas vahemikus A1-A3 e A1-B1 arvestatakse summa arvutamisel kaks korda).

Näide 3 – Exceli ristmiku operaator

Järgmise arvutustabeli lahter C1 näitab ristmiku operaatorit, mille jaoks kasutatakse defilõpetage lahtritele loodud vahemik vahemike ristumiskohas A1-A3 e A1-B2. Saadud vahemik (vahemik A1-A2) suunatakse seejärel funktsioonile SUM Exceli, mis summeerib ristumisvahemikus olevad väärtused ja tagastab väärtuse 4.

Lisateavet Exceli operaatorite kohta leiate veebisaidilt Microsoft Office'i veebisait.

Exceli funktsioonid

Excel pakub suurt hulka sisseehitatud funktsioone, mida saab kasutada konkreetsete arvutuste tegemiseks või arvutustabeliandmete teabe tagastamiseks. Need funktsioonid on jaotatud kategooriatesse (tekst, loogika, matemaatika, statistikajne), mis aitavad teil Exceli menüüst vajaliku funktsiooni leida.

Allpool anname kategooriate kaupa rühmitatud Exceli funktsioonide täieliku loendi. Kõik funktsioonide lingid viivad teid spetsiaalsele lehele, kust leiate funktsiooni kirjelduse koos kasutusnäidete ja levinumate vigade üksikasjadega.

Exceli statistilised funktsioonid:

Arv ja sagedus

• COUNT: tagastab arvväärtuste arvu etteantud lahtrite või väärtuste komplektis;
• COUNTA: tagastab mittetühikute arvu antud lahtrite või väärtuste komplektis;
• COUNTBLANK: tagastab tühjade lahtrite arvu antud vahemikus;
• COUNTIF: tagastab lahtrite arvu (antud vahemikust), mis vastavad antud kriteeriumile;
• COUNTIFS: tagastab lahtrite arvu (antud vahemikust), mis vastavad määratud kriteeriumikomplektile (uus Excel 2007-s);
• FREQUENCY: tagastab massiivi, mis näitab antud massiivi väärtuste arvu, mis jäävad määratud vahemikku;

Maksimumi ja miinimumi otsimine

• MAX: tagastab suurima väärtuse esitatud numbrite loendist
• MAXA: tagastab suurima väärtuse esitatud väärtuste loendist, loendades teksti ja loogilist väärtust FALSE väärtusena 0 ja loendades loogilist väärtust TRUE väärtusena 1
• MAXIFS: tagastab ühe või mitme kriteeriumi alusel määratud loendi väärtuste alamhulga suurima väärtuse. (Uus Excel 2019-st)
• MIN: tagastab väikseima väärtuse esitatud numbrite loendist
• MINA: tagastab väikseima väärtuse esitatud väärtuste loendist, lugedes teksti ja loogilise väärtuse FALSE väärtuseks 0 ja loogilise väärtuse TRUE väärtuseks 1
• MINIFS: tagastab ühe või mitme kriteeriumi alusel määratud loendi väärtuste alamhulga väikseima väärtuse. (Mis on uut rakenduses Excel 2019)
• LARGE: tagastab antud K väärtuse K-s SUURIMA väärtuse esitatud numbrite loendist
• SMALL: tagastab antud K väärtuse K-nda VÄIKEima väärtuse esitatud numbrite loendist

keskmine

• AVERAGE: tagastab esitatud numbrite loendi keskmise
• AVERAGEA: tagastab esitatud arvude loendi keskmise, loendades teksti ja loogilise väärtuse FALSE väärtuseks 0 ning loogilise väärtuse TRUE väärtuseks 1
• AVERAGEIF: arvutab lahtrite keskmise antud vahemikus, mis vastavad antud kriteeriumile (uus Excel 2007-s)
• AVERAGEIFS: arvutab pakutud vahemikus olevate lahtrite keskmise, mis vastab mitmele kriteeriumile (uus Excel 2007-s)
• MEDIAN: tagastab esitatud numbrite loendi mediaani (keskmise väärtuse).
• MODE: Arvutab antud arvude loendi režiimi (kõige sagedasema väärtuse), mis on asendatud funktsiooniga Mode.Sngl Excel 2010-s)
• MODE.SNGL: arvutab esitatud numbrite loendi režiimi (kõige sagedasema väärtuse) (uus Excel 2010-s: asendab funktsiooni Mode)
• MODE.MULT: tagastab massiivi või andmevahemiku kõige sagedasemate väärtuste vertikaalse massiivi (uus Excel 2010-s)
• GEOMEAN: tagastab antud arvude komplekti geomeetrilise keskmise
• HARMEAN: tagastab esitatud arvude komplekti harmoonilise keskmise
• TRIMMEAN: tagastab antud väärtuste komplekti sisemise keskmise

Permutatsioonid

• PERMUT: tagastab antud arvu objektide permutatsioonide arvu
• PERMUTATIONA: tagastab permutatsioonide arvu teatud objektide arvu jaoks (koos kordustega), mida saab kogu objektide hulgast valida (uus Excel 2013-s)

Usaldusintervallid

• CONFIDENCE: tagastab populatsiooni keskmise usaldusvahemiku, kasutades normaaljaotust (asendatud Excel 2010 funktsiooniga Confidence.Norm)
• CONFIDENCE.NORM: tagastab populatsiooni keskmise usaldusvahemiku, kasutades normaaljaotust (Uus Excel 2010-s: asendab usaldusfunktsiooni)
• CONFIDENCE.T: tagastab populatsiooni keskmise usaldusvahemiku, kasutades Studenti t-jaotust (uus Excel 2010-s)

Protsentiilid ja kvartiilid

• PERCENTILE: tagastab väärtuste K-da protsentiili etteantud vahemikus, kus K on vahemikus 0–1 (kaasa arvatud) (asendatud funktsiooniga Percentile.Inc Excel 2010-s)
• PERCENTILE.INC: tagastab väärtuste K-da protsentiili antud vahemikus, kus K on vahemikus 0–1 (kaasa arvatud) (Uus Excel 2010-s: asendab funktsiooni protsentiili)
• PERCENTILE.EXC: tagastab väärtuste K-protsentiili antud vahemikus, kus K on vahemikus 0–1 (välja arvatud) (Uus Excel 2010-s)
• QUARTILE: tagastab antud arvukomplekti määratud kvartiili, mis põhineb protsentiili väärtusel 0–1 (kaasa arvatud) (asendatud Excel 2010 funktsiooniga Quartile.Inc)
• QUARTILE.INC: tagastab antud arvukomplekti määratud kvartiili, mis põhineb protsentiili väärtusel 0–1 (kaasa arvatud) (Uus Excel 2010-s: asendab funktsiooni Kvartiil)
• QUARTILE.EXC: tagastab antud arvukomplekti määratud kvartiili 0–1 (välistava) protsentiili väärtuse alusel (uus Excel 2010-s)
• RANK: tagastab antud väärtuse statistilise järgu antud väärtuste massiivi piires (asendatud Excel 2010 funktsiooniga Rank.Eq)
• RANK.EQ: tagastab esitatud numbrite loendi režiimi (kõige sagedasema väärtuse) (kui rohkem kui ühel väärtusel on sama asetus, tagastatakse selle komplekti kõrgeim aste) (Uus Excel 2010-s: asendab funktsiooni Rank)
• RANK.AVG: tagastab antud väärtuse statistilise järgu antud väärtuste massiivi piires (kui mitmel väärtusel on sama asetus, tagastatakse keskmine asetus) (Uus Excel 2010-s)
• PERCENTRANK: tagastab andmekogumi väärtuse astme protsendina (0–1 kaasa arvatud) (asendatud Excel 2010 funktsiooniga Percentrank.Inc)
• PERCENTRANK.INC: tagastab andmekogumi väärtuse asetuse protsentides (0–1 kaasa arvatud) (Uus Excel 2010-s: asendab funktsiooni Percentrank)
• PERCENTRANK.EXC: tagastab väärtuse järjekoha andmestikus protsentides (v.a 0–1) (Uus Excel 2010-s)

Hälve ja dispersioon

• AVEDEV: tagastab andmepunktide absoluutsete kõrvalekallete keskmise väärtuse
• DEVSQ: tagastab andmepunktide kogumi valimi keskmisest kõrvalekallete ruutude summa
• STDEV: tagastab esitatud väärtuste komplekti standardhälbe (mis esindab üldkogumi valimit) (asendatakse Excel 2010 funktsiooniga St.Dev)
• STDEV.S: tagastab antud väärtuste komplekti standardhälbe (mis esindab üldkogumi valimit) (Uus Excel 2010-s: asendab funktsiooni STDEV)
• STDEVA: tagastab antud väärtuste komplekti standardhälbe (mis esindab üldkogumi valimit), loendades teksti ja loogilise väärtuse FALSE väärtuseks 0 ja loogilise väärtuse TRUE väärtuseks 1
• STDEVP: tagastab antud väärtuste komplekti standardhälbe (mis esindab kogu populatsiooni) (asendatakse Excel 2010 funktsiooniga StdPDev)
• STDEV.P: tagastab antud väärtuste komplekti standardhälbe (mis esindab kogu populatsiooni) (Uus Excel 2010-s: asendab funktsiooni STDEV)
• STDEVPA: tagastab antud väärtuste komplekti standardhälbe (mis esindab tervet populatsiooni), lugedes teksti ja loogilise väärtuse FALSE väärtuseks 0 ja loogilise väärtuse TRUE väärtuseks 1
• VAR: tagastab antud väärtuste komplekti dispersiooni (esindab populatsiooni valimit) (asendatakse Excel 2010 funktsiooniga SVar)
• VAR.S: tagastab antud väärtuste komplekti dispersiooni (esindab populatsiooni valimit) (Uus Excel 2010-s – asendab funktsiooni Var)
• VARA: tagastab antud väärtuste komplekti dispersiooni (esindab üldkogumi valimit), loendades teksti ja loogilise väärtuse FALSE väärtuseks 0 ja loogilise väärtuse TRUE väärtuseks 1
• VARP: tagastab antud väärtuste komplekti dispersiooni (mis esindab kogu populatsiooni) (asendatud funktsiooniga Var.P Excel 2010-s)
• VAR.P: tagastab antud väärtuste komplekti dispersiooni (mis esindab kogu populatsiooni) (Uus Excel 2010-s – asendab funktsiooni Varp)
• VARPA: tagastab antud väärtuste komplekti dispersiooni (mis esindab tervet populatsiooni), lugedes teksti ja loogilise väärtuse FALSE väärtuseks 0 ning loogilise väärtuse TRUE väärtuseks 1
• COVAR: tagastab populatsiooni kovariatsiooni (st iga paari hälvete korrutiste keskmise kahe antud andmekogumis) (asendatakse Excel 2010 funktsiooniga Covariance.P)
• COVARIANZA.P: tagastab populatsiooni kovariatsiooni (st kahe antud andmekogumi iga paari hälvete korrutiste keskmise) (Uus Excel 2010-s: asendab funktsiooni Covar)
• COVARIANZA.S: tagastab valimi kovariatsiooni (st iga paari hälvete korrutiste keskmise kahe antud andmekogumis) (uus Excel 2010-s)

Ennustavad funktsioonid

• FORECAST: ennustab tulevase punkti lineaarsel trendijoonel, mis on sobitatud antud x ja y väärtuste komplektiga (asendatud funktsiooniga FORECAST.LINEAR Excel 2016-s)
• FORECAST.ETS: kasutab eksponentsiaalset silumisalgoritmi, et ennustada tulevase väärtuse ajaskaalal olemasolevate väärtuste seeria põhjal (uus Excel 2016-s – pole saadaval Excel 2016 for Macis)
• FORECAST.ETS.CONFINT: tagastab prognoosiväärtuse usaldusvahemiku määratud sihtkuupäeval (Uus Excel 2016-s – pole saadaval Excel 2016 for Macis)
• FORECAST.ETS.SEASONALITY: tagastab Exceli tuvastatud korduva mustri pikkuse määratud aegrea kohta (uus Excel 2016-s – pole saadaval Excel 2016 for Macis)
• FORECAST.ETS.STAT: tagastab aegrea prognoosi statistilise väärtuse (uus Excel 2016-s – pole saadaval Excel 2016 for Maci jaoks)
• FORECAST.LINEAR: ennustab tulevase punkti lineaarsel trendijoonel, mis on sobitatud antud x- ja y-väärtuste komplektiga (uus Excel 2016-s (mitte Excel 2016-s Macile) – asendab funktsiooni Prognoos)
• INTERCEPT: arvutab x ja y väärtuste jada kaudu kõige sobivama regressioonijoone, tagastab väärtuse, mille juures see sirge lõikab y-telge
• LINEST: tagastab statistilise teabe, mis kirjeldab kõige paremini sobiva joone trendi x- ja y-väärtuste seeria kaudu.
• SLOPE: tagastab lineaarse regressioonijoone kalde läbi antud x ja y väärtuste komplekti
• TREND: Arvutab trendijoone läbi antud y väärtuste komplekti ja tagastab täiendavad y väärtused antud uute x väärtuste komplekti jaoks
• GROWTH: tagastab arvud eksponentsiaalses kasvutrendis, mis põhinevad esitatud x ja y väärtuste komplektil
• LOGEST: tagastab eksponentsiaalse trendi parameetrid antud x ja y väärtuste komplekti jaoks
• STEYX: tagastab prognoositud y väärtuse standardvea iga x jaoks regressioonijoonel antud x ja y väärtuste komplekti korral

Ercole Palmeri