Täpsemad Exceli valemid | 10 parema Exceli lisafunktsiooni loetelu
10 parema Exceli valemi ja funktsiooni loetelu
Selle Advanced Exceli valemite malli saate alla laadida siit - Exceli valemite täpsem mall# 1 - VLOOKUPi valem Excelis
See täiustatud Exceli funktsioon on üks Exceli kõige enam kasutatavatest valemitest. See on peamiselt tingitud selle valemi lihtsusest ja selle rakendamisest teatud tabelitelt teatud väärtuse otsimisel, millel on nende tabelite seas üks ühine muutuja. Oletame, et teil on kaks tabelit, kus on üksikasjad ettevõtte palga ja nime kohta, kusjuures esmane 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.
- Kui tabeli B esmases veerus on dubleeritud väärtused, täidetakse lahtrisse esimene väärtus.
- Kui lisate andmebaasi uue veeru (Ex lisage uus veerg enne tabelisse B enne töötajate palka), võib valemi väljund olla erinev valemis mainitud positsiooni põhjal (ülaltoodud juhul väljund oleks tühi)
# 2 - INDEXi valem Excelis
Seda täiustatud Exceli valemit kasutatakse antud tabeli lahtri väärtuse saamiseks, määrates ridade arvu, veeru või mõlemad. Nt Töötaja nime saamiseks 5. 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 sisestate 5. rea andmetesse veel ühe rea, tagastab valem „Chandan Kale”. Seega sõltuks väljund kõigist andmetabelis aja jooksul toimuvatest muudatustest.
# 3 - vaste valemiga Excelis
See Exceli täpsem valem tagastab rea või veeru numbri, kui antud vahemikus on teatud string või arv. Allpool toodud näites püüame veerus Töötaja nimi leida „Rajesh Ved” positsiooni.
Valem oleks järgmine:
Funktsioon MATCH tagastaks väärtuseks 5.
Täpse vaste jaoks kasutatakse 3. argumenti. 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 juba olemasoleva välja mingil piirangul. Aga mis siis, kui peame lipu loomisel arvesse võtma mitut veergu. Nt Allpool toodud juhul tahame märkida kõik töötajad, kelle palk on suurem kui 50 000, kuid töötaja ID üle 3.
Sellistel juhtudel kasutaksime IF-i JA. Selle kohta leiate ekraanipildi alt.
See annaks tulemuseks 0.
AND-i abil mitme veeru põhjal 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 lihtsalt vastama paljudele paljudest tingimustest.
Ülaltoodud juhtudel, kui mõni ühest tingimusest saab täidetud, on meil lahtrid täidetud kui 1 muu 0. Samuti võime asendada 1 või 0 mõne alamstringiga 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.
Samuti võime loendada töötajate arvu organisatsioonis, kellel on töötaja ID üle 3, kui kasutame SUMIFi asemel COUNTIF-i.
# 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. Näiteks, kui tahame kuvada töötaja ID ja töötaja nimi ühes veerus.
Saame seda CONCATENATE valemit kasutada ainult selleks.
Ü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
Me võime kasutada seda täiustatud Exceli valemit, kui tahame antud stringist eraldada teatud alamstringi. Mainitud valemeid võiks kasutada meie nõuetest lähtuvalt. 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:
RIGHT-valemi rakendamine excelis on samuti sama, lihtsalt me vaataksime tähemärki stringi paremal. Exceli MID-funktsiooni korral peame siiski andma nõutava 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 olla kasulik arvutuste dünaamilise puudutamise andmiseks. Seda on kõige parem kasutada juhtudel, kui sisestame pidevad read olemasolevasse andmebaasi. OFFSET Excel annab meile vahemiku, kus peame mainima võrdlusrakke, ridade arvu ja veerge. Nt Kui tahame arvutada ettevõtte esimese 5 töötaja keskmise, kus meil on töötajate palk sorditud töötajate ID järgi, 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.
