Exceli keerulised valemid (sisukord)
- Sissejuhatus Exceli keerukatesse valemitesse
- Valige funktsioon Teeninda otsingufunktsioonina
- VLOOKUP VALITUD
- KUI JA & VÕI Funktsioon
Sissejuhatus Exceli keerukatesse valemitesse
Excelis on palju kasulikke valemeid, meil on kategooria Finants, Loogika, Tekst, Kuupäev ja kellaaeg, Otsing ja viited ning Matemaatika ja Trig. Kõik üksikud valemid on vahetarbijate jaoks piisavalt head, kui nad seisavad silmitsi otseolukorraga, kuid järgmisele tasemele liikudes peab teie oskus tõusma, nii et sellega seoses mängib Excelis keerukamate valemite rakendamine üliolulist rolli keerukate olukordade lahendamisel. Seda artiklit silmas pidades tutvustame teile mõnda olulist ja vajalikku keerulist valemit. Loe edasi.
Valige funktsioon Teeninda otsingufunktsioonina
Mitte paljud teist pole funktsiooni VALITUD kasutanud, kuid see on väga kasulik ka otsingufunktsioonina.
Selle keerukate valemite Exceli malli saate alla laadida siit - keerukate valemite Exceli mall. Vaadake näiteks allpool toodud andmeid.
Selle tabeli all on klientide andmed igakuiselt, iga kliendi tabel on olemas. Selle tabeli lahtrisse B13 olen loonud kuu ripploendi, nii et kui see rippmenüü muutub, peaksime saama valitud kuu kokku.
Looge üks tabel nagu allpool.
Kandke lahtrile D13 VLOOKUP.
Nüüd avage lahtris B14 funktsioon VALITUD.
Valige VLOOKUP-lahtriks INDEX-number ja muutke see absoluutseks viiteks.
Valige nüüd iga argumendi jaoks iga kuu müüginumbrid.
Sulgege sulg ja vajutage sisestusklahvi.
Nüüd kopeerige ja kleepige valem ka lahtrite alla.
Nüüd kuvatakse VALITUD valem valitud kuu numbrid.
VLOOKUP funktsiooniga VALITUD
Oleme VLOOKUPi traditsiooni näinud, kuid võime seda kombineerida ka VALITUD funktsiooniga. Vaadake näiteks allpool toodud andmeid.
Lahtrites A1 kuni B11 on meil müügitabel ja lahtris D2 olen koostanud kuude ripploendi ja lahtris E2 peame rakendama VLOOKUPi ja saama valitud kuu müügiväärtuse. Kuid VLOOKUPi rakendamisel on probleemiks andmestruktuur, kus otsimisväärtus on tulemuste veerust paremal, seega ei saa VLOOKUP andmeid paremalt vasakule tuua, sel juhul saame kasutada funktsiooni VALI.
Tabelimassiivi valimiseks peame siin avama funktsiooni VALI.
Lilli lindi INDEX Number numbris sisestage sulud 1 ja 2. Nüüd valige veerus Kuu esimene vahemik.
Teise vahemikuna valige veerg Müük.
Mainige veeru indeksi numbrit 2 ja vahemiku otsimist kui 0.
Nii saime tabelist kuu aprilli tulemuse. Nii et kui teil pole täpset andmestruktuuri, võime valida funktsiooni VALIDA.
KUI JA & VÕI Funktsioon
Loogilised funktsioonid on kraami arvutamiseks täis loogikat. Vaadake allpool toodud näiteandmeid.
Ülaltoodud tabelist peame boonuse arvutama järgmiste tingimuste põhjal.
- Kui teenus on üle 1, 5 aasta ja kui osakond on kas müügi- või tugiteenus, on boonus 1000.
- Kui midagi muud, siis boonus on 3000.
Boonuse arvutamiseks peame rakendama IF, AND, & OR koos. Lahtri D2 avatud IF tingimus.
Peame katsetama mitut tingimust, avama ja funktsioneerima.
Järgmine loogiline on selle avatud VÕI tingimuse korral kas müük või tugi.
Katsetage, kas pealkiri on Müük või Tugi.
Seega, kui kõik esitatud tingimused on TÕESED, peaks boonus olema 10000.
Kui kaasasolevad loogilised testid on VÄÄRIS, on boonus 3000.
Sulgege sulg ja vajutage sisestusklahvi, et tulemus saadaks.
Kopeerige ja kleepige valem järgmistesse lahtritesse.
Nii et vastavalt tingimusele saime tulemuse. Kui teenuse kestus on> 1, 5 aastat ja pealkiri on kas müük või tugi, saame 10000 või muidu saame 3000.
Keerukas valem looduslike märkidega
VLOOKUP nõuab andmete hankimiseks täpset otsinguväärtust, mis tuleb sobitada. See on traditsiooniline tunnuslause, kuid siiski saame andmeid hankida, kasutades osakeste otsinguväärtust. Näiteks kui otsimisväärtus on „VIVO” ja põhitabelis, kui see on „VIVO Mobile”, võime ikkagi ühitada metamärke. Näeme ühte näidet nüüd, allpool on näiteandmed.
Veerus A on otsingulaud. Veerus C on meil otsinguväärtused, need otsinguväärtused ei ole täpselt samad, mis otsingustabeli väärtused. Nii et näeme, kuidas VLOOKUPi looduslike kaartide abil rakendada.
Esiteks avage lahtris D1 VLOOKUP-i funktsioon.
Esimene argument on otsimisväärtus . Üks otsimisväärtuse probleeme on see, et meil puudub täpne vaste, mistõttu peame selle otsinguväärtuse tärniga enne ja pärast otsinguväärtust lisama tärniga.
Siin oleme rakendanud kaks tärnitähte “*” & C2 & “*”. Tärn tähistab siin, et kõik metamärgi vahel olevad punktid peaksid sobima ja tagastama vastava tulemuse.
Isegi kui meil oli lihtsalt tärn “Infosys” tärn, vastas otsingutabeli väärtus ja tagasime täpse tulemuse kui “Infosys Ltd”.
Sarnaselt saime lahtris D6 vea väärtuse kui #VALUE! sest otsingulauas pole sõna “Mintra”.
Soovitatavad artiklid
See on Exceli keerukate valemite juhend. Siin käsitleme mõnda olulist ja väga vajalikku keerulist valemit koos näidete ja exceli malliga. Lisateavet leiate ka meie muudest soovitatud artiklitest -
- Otsetee valem Excelis (näpunäited)
- Juhendid funktsiooni Excel NORMSINV kohta
- Nimiboks ja selle kasutusvõimalused Excelis
- Keelte stringid Excelis