10 parema Exceli valemi ja funktsiooni loetelu
# 1 - VLOOKUPi valem Excelis
See täiustatud Exceli funktsioon on üks Exceli enamkasutatavatest valemitest. See on peamiselt tingitud selle valemi lihtsusest ja selle rakendamisest teatud väärtuste otsimisel teistest tabelitest, millel on nende tabelite vahel üks ühine muutuja. Oletame, et teil on kaks tabelit üksikasjadega ettevõtte palga ja nime kohta, kusjuures töötaja veerg on töötaja ID. Palka soovite saada tabeli A tabelist B.
Võite kasutada funktsiooni VLOOKUP nagu allpool.
Selle tulemuseks on allolev tabel, kui rakendame seda täpsemat Exceli valemit veeru Töötaja palk teistes lahtrites.
Lohistage valem ülejäänud lahtritesse.
VLOOKUPil on kolm suurt piiritlust:
- Selle veeru paremas osas ei saa olla peamist veergu, mille väärtuse soovite teisest tabelist täita. Sellisel juhul ei tohi veerg Töötaja palk olla enne töötaja ID-d.
- Tabeli B peamise veeru dubleeritud väärtustes täidetakse lahtrisse esimene väärtus.
- Kui lisate andmebaasi uue veeru (Ex lisage uus veerg enne tabeli B töötaja palka), võib valemi väljund olla erinev valemis mainitud positsiooni põhjal (ülaltoodud juhul saadakse tulemus oleks tühi)
# 2 - INDEXi valem Excelis
Seda täiustatud Exceli valemit kasutatakse antud tabeli lahtri väärtuse saamiseks, määrates ridade, veergude või mõlema arvu. Nt Töötaja nime saamiseks viiendal vaatlusel on allpool toodud andmed.
Saame kasutada täiustatud Exceli valemit allpool:
Rea INDEX väärtuse saamiseks võib kasutada sama INDEX-valemit. Nii rea kui veeru numbri kasutamisel näib süntaks umbes selline:
Ülaltoodud valem tagastaks "Rajesh Ved".
Märkus. Kui lisate viiendasse ritta veel ühe rea , tagastatakse valemiga „Chandan Kale”. Seega sõltuks väljund kõigist andmetabelis aja jooksul toimuvatest muudatustest.
# 3 - vaste valem Excelis
See Exceli täpsem valem tagastab rea või veeru numbri, kui antud vahemikus on kindel string või number. Allpool toodud näites püüame veerust Töötaja nimi leida „Rajesh Ved”.
Valem oleks järgmine:
Funktsioon MATCH tagastaks väärtuseks 5.
3 rd argument kasutatakse täpse vaste. Võite kasutada ka +1 ja -1 vastavalt oma vajadustele.
Märkus . VLOOKUPi piirangute ületamiseks saab ühendada INDEXi ja MATCHi.
# 4 - KUI JA valem Excelis
On palju juhtumeid, kui lipud tuleb luua teatud piirangute alusel. IF-i põhisüntaks on meile kõigile tuttav. Seda funktsiooni excel IF kasutame uue välja loomiseks, mis põhineb olemasoleva välja mingil piirangul. Aga mis siis, kui peame lipu loomisel arvesse võtma mitut veergu. Nt Allpool toodud juhul soovime märkida kõik töötajad, kelle palk on suurem kui 50 000, kuid töötaja ID üle 3.
Sellistel juhtudel kasutaksime IF JA. Selle kohta leiate ekraanipildi alt.
See annaks tulemuseks 0.
AND-i abil mitmel veerul põhineva lipu loomiseks võib meil olla palju tingimusi või piiranguid.
# 5 - KUI VÕI valem Excelis
Samamoodi võime JA-i asemel kasutada ka funktsiooni OR Excelis, kui peame rahuldama paljude tingimusi.
Ülaltoodud juhtudel, kui mõni ühest tingimusest saab täidetud, on meil lahtrid täidetud kui 1 teine 0. Samuti võime asendada 1 või 0 mõne alamrubriigi topelt jutumärkidega (“”).
# 6 - SUMIFi valem Excelis
Mõnes analüüsis peate summa või loendusfunktsiooni rakendamisel filtreerima mõned tähelepanekud. Sellistel juhtudel on see täiustatud Exceli SUMIF-funktsioon Excelis meie päästmiseks. See filtreerib kõik tähelepanekud selles arenenud Exceli valemis toodud teatud tingimuste põhjal ja võtab need kokku. Nt Mis siis, kui tahame teada ainult nende töötajate palgasummat, kellel on töötaja ID üle 3.
Rakendades valemit SUMIFS:
Vormel tagastab tulemuseks 322000.
Kui loeme SUMIF, kasutame COUNTIF-i ka organisatsiooni töötajate arvu, kellel on töötaja ID üle 3.
# 7 - valemi liitmine Excelis
See Exceli täiustatud funktsioon on üks valemitest, mida saab kasutada mitme variandiga. See täiustatud Exceli valem aitab meil ühendada mitu tekstistringi ühte tekstistringi. Nt, kui soovime kuvada töötaja ID ja töötaja nimi ühes veerus.
Selleks saame kasutada seda CONCATENATE valemit.
Ülaltoodud valemi tulemuseks on “1Aman Gupta”.
Meil on veel üks variant, kui paneme ID ja NAME vahele ühe sidekriipsu. Nt CONCATENATE (B3, ”-“, C3) tulemuseks on “1-Aman Gupta”. Saame seda kasutada ka VLOOKUP-is, kui Exceli väärtuses LOOKUP on segu rohkem kui ühest muutujast.
# 8 - VASAK, MID ja PAREM valem Excelis
Selle täiustatud Exceli valemi abil saame antud stringist konkreetse alamstringi välja tõmmata. Seda saaks kasutada vastavalt meie nõuetele. Nt Kui tahame töötaja nimest välja tõmmata esimesed 5 tähemärki, võime Excelis kasutada VASAK valemit, kus veeru nimi ja teine parameeter on 5
Väljund on toodud allpool:
Valemi RIGHT rakendus excelis on samuti sama. Lihtsalt me vaataksime tegelast stringi paremalt. Exceli MID-funktsiooni korral peame aga andma vajaliku tekstistringi algpositsiooni ja stringi pikkuse.
# 9 - OFFSETi valem Excelis
See täiustatud Exceli funktsioon koos teiste funktsioonide, näiteks SUM või AVERAGE kombinatsiooniga võib anda arvutustele dünaamilise puudutuse. Seda on kõige parem kasutada juhtudel, kui sisestame pidevad read olemasolevasse andmebaasi. OFFSET Excel pakub meile vahemikku, kus peame mainima võrdlusrakku, ridade arvu ja veerge. Nt Kui tahame arvutada ettevõtte esimese viie töötaja keskmise, kus meil on töötajate palk töötajate ID järgi sorteeritud, saame teha järgmist. Allpool toodud arvutus annab meile alati palka.
- See annab meile esimese 5 töötaja palga summa.
# 10 - TRIMi valem Excelis
Seda täiustatud Exceli valemit kasutatakse tekstist ebaoluliste tühikute puhastamiseks. Nt Kui soovime mõne nime alguses tühikud eemaldada, saame seda kasutada Excelis funktsiooni TRIM abil, nagu allpool:
Tulemuseks oleks „Chandan Kale”, ilma et enne Chandanit oleks tühikut.
