Exceli täpsemad valemid (sisukord)
- Sissejuhatus täpsematesse valemitesse Excelis
- Exceli täpsema valemi näited
Sissejuhatus täpsematesse valemitesse Excelis
Kui olete jõudnud Exceli kesktasemele, peate kõrgele tasemele jõudmiseks kõvasti tööd tegema. Kõrgetasemelisele tasemele liikumiseks peate olema teadlik mõnest sagedamini kasutatavast täpsemast vormelist. Selles artiklis käsitlen kümmet edasijõudnute valemit, mida kõik exceli õppijad peavad teadma. Järgige seda artiklit, et õppida ja uurida.
Exceli täpsema valemi näited
Mõistame, kuidas Excelis täpsemaid vormeleid kasutada koos mõne näitega.
Selle täpsemate valemite Exceli malli saate alla laadida siit - täpsemate valemite Exceli mallNäide nr 1 - osaline VLOOKUP-funktsioon
Peate olema kokku puutunud olukorraga, kus VLOOKUP viskab välja tõrke isegi siis, kui otsinguväärtuses oli kerge puudus. Näiteks kui leiate palga eest nime Abhishek Sinha ja kui teil on ainult Abhishek, ei saa VLOOKUP teile andmeid tuua.
Kasutades tärnimärke otsinguväärtuse mõlemas otsas, saame aga andmeid osalise otsinguväärtuse jaoks.
Tabelis 1 on mul täielik nimi ja palk, kuid tabelis 2 on mul ainult osalised nimed ja mul on vaja leida iga töötaja töötasu.
1. samm: avage lahtris E3 VLOOKUP-i valem. Enne otsinguväärtuse valimist pange tärn (*) otsimisväärtuse mõlemale küljele.
2. samm: Nagu tavaliselt, saate nüüd VLOOKUPi valemi täita ja meil on tulemused.
Pärast ülaltoodud valemi kasutamist on väljund näidatud allpool.
Sama valemit kasutatakse ka teistes lahtrites.
Näide nr 2 - COUNTIFID koos operaatori sümbolitega
Loendis olevate asjade loendamiseks peate olema kasutanud funktsiooni COUNTIF & IFS. Samuti võime arvestada selliste operatsioonisümbolite põhjal nagu suurem kui (>) väiksem kui (<) ja võrdusmärk (=).
Vaadake nüüd näiteks allpool toodud andmeid. Kuidas soovite arvestada, kui soovite arvestada arvelduste koguarvu lõunapoolses piirkonnas pärast kuupäeva 10. jaanuar 2018?
Näide 3 - kui seisund on JA-VÕI tingimustega
Loogilised funktsioonid on osa meie igapäevasest tegevusest. Järgmisele tasemele liikumiseks peate need valdama. Kui arvutate boonust mitmel tingimusel, peate töö lõpule viima JA või VÕI tingimuse IF-tingimusega, et töö saaks tehtud.
Oletame, et peate arvutama iga osakonna boonussumma, lähtudes osakonnast ja töötatud aastatest, mida neid funktsioone vajate. Allpool toodud kriteeriumide põhjal peame boonuse arvutama.
Kui teenistus on üle 4 aasta ja osakond on kas müügi- või tugisummaks, on 50000 või muul juhul on boonus 25000.
Boonussumma saamiseks rakendage valemit allpool.
Pärast ülaltoodud valemi kasutamist on allpool näidatud väljund.
Sama valem kehtib lahtris E3 kuni E9.
Näide nr 4 - TEKST Funktsioon, et muuta teid dünaamiliseks
Oletame, et peate igapäevast müügitabelit ja peate seda tabelit iga päev värskendama. Tabeli alguses on üks pealkiri, mille pealkiri on „Konsolideeritud müügiandmed alates PP-KK-AAAA kuni PP-KK-AAAA”. Kui ja millal tabel uuendatakse, peate muutma pealkirja kuupäeva. Kas pole sama pettumust valmistav ülesanne ikka ja jälle sama asja teha? Selle pealkirja saab muuta dünaamiliseks, kasutades funktsioone TETX, MIN ja Max koos liitunud operaatori sümboliga ampersand (&).
Näide nr 5 - INDEX + MATCH + MAX kõrgeima müügiinimese leidmiseks
Teil on nimekiri müügipersonalist ja müügist, mida nad tegid nende nime vastu. Kuidas öelda, kes on parim või kõrgeim müüja nimekirjas? Muidugi on meil tulemuse teatamiseks veel mitu tehnikat, kuid see funktsioon võib partiist tagasi tuua kõrgeima müügipersonali.
Kõrgeima müüja nime saamiseks rakendage funktsiooni allpool.
Pärast ülaltoodud valemi kasutamist on allpool näidatud väljund.
Näide nr 6 - hankige loendist unikaalsete väärtuste arv
Kui teil on palju dubleerivaid väärtusi ja kui teil on vaja öelda, mitu ainulaadset väärtust on, siis kuidas öelda? Võite öelda, eemaldades duplikaadi väärtuse, kuid see ei ole dünaamiline viis kordumatu väärtuse määramiseks.
Seda massiivifunktsiooni kasutades saame partiist teada ainulaadseid väärtusi.
Kordumatu väärtuste loendi saamiseks rakendage valemit allpool.
Näide # 7 - kasutage rippmenüü dünaamiliseks nimega Range
Kui töötate sageli ripploendiga ja muudate ripploendit ajakohaseks, peate väärtuste kustutamiseks või naasmiseks naasma allikate loendi vahemikku. Pärast seda peate tagasi rippmenüü lahtritesse jõudma ja uuesti ripploendi vahemikku värskendama.
Kui saate aga oma ripploendi jaoks nimega vahemiku luua, saate ripploendi dünaamiliseks ja ajakohaseks muuta.
Looge nimevahemik, nagu on näidatud alloleval pildil.
Nüüd minge rippmenüüle ja avage rippmenüü dialoogiboks.
Vajutades allikat F3, kuvatakse kõik määratletud nimed, valige ripploendi nimi.
Ok, ripploend on valmis ja see uuendab väärtusi automaatselt, kui ripploendivahemik muutub.
Näide # 8 - Vabastage veaväärtustest, kasutades funktsiooni IFERROR
Olen kindel, et olete VLOOKUP-i, Divisioni arvutustega töötades kogenud tõrkeväärtusi. Nende veaväärtuste käsitlemine on tüütu ülesanne. Kuid nendest tõrkeväärtustest saame vabaneda, kasutades valemis funktsiooni IFERROR.
Pärast ülaltoodud valemi kasutamist on allpool näidatud väljund.
Sama valem, mida kasutatakse teistes lahtrites.
Näide nr 9 - kasutage PMT funktsiooni oma EMI diagrammi loomiseks
Tänapäeval pole EMI võimalus meile kõigile võõras asi. Suurepäraselt saame PMT funktsiooni abil hinnata omaenda EMI diagrammi. Oma diagrammi loomiseks toimige järgmiselt.
EMI summa saamiseks rakendage lahtris B4 valemit.
Näide # 10 - INDEX + MATCH kui VLOOKUP-funktsiooni alternatiiv
Loodan, et olete VLOOKUP-i funktsiooni piirangutest teadlik. Üks peamisi piiranguid on see, et VLOOKUP saab andmeid tuua vasakult paremale, mitte paremalt vasakule. Kogu aeg pole andmed hästi korraldatud ja kasutamiseks valmis. Sageli asub otsitav väärtuste veerg otsimisväärtuse vasakus servas, nii et VLOOKUP neist ei aita.
Funktsiooni INDEX + MATCH kombinatsioon toimib VLOOKUP-i alternatiivina.
Pärast ülaltoodud valemi kasutamist on väljund näidatud allpool.
Sama valemit kasutatakse ka teistes lahtrites.
Toote ID põhjal peame müügiväärtused välja võtma. Põhitabelis on toote ID müügisamba paremas servas. Seega ei saa VLOOKUP andmeid tuua. Andmete toomiseks kasutage järgmist valemit.
Soovitatavad artiklid
See on juhend Exceli täpsemate valemite jaoks. Siin arutleme, kuidas kasutada Excelis täpsemaid vormeleid koos praktiliste näidete ja allalaaditava excelimalliga. Võite vaadata ka meie teisi soovitatud artikleid -
- Kuidas kasutada funktsiooni Excel INDEX?
- Advanced Excel | Andmebaasi funktsioon
- Näited Exceli TRIM-vormelist
- Juhend Exceli OFFSETi vormelile