Kas teil on Exceli valem, mis pesitseb mitu IF-funktsiooni? Kui jõuate liiga paljude pesastatud IF-lausetega punkti, peate nägema, kas kogu VLOOKUP-funktsiooni abil muutuks kogu asi lihtsamaks. Olen näinud, kuidas 1000-kohalised valemid on lihtsustatud 30-täheliseks VLOOKUP-vormeliks. Seda on lihtne hooldada, kui peate hiljem lisama uusi väärtusi.
Pikka aega tagasi töötasin tööl müügiesinduse asepresidendi juures. Modelleerisin alati mõnda uut boonusprogrammi või komisjonitasu plaani. Sain üsna harjunud igasuguste tingimustega plaanide tellimisega. Allolev on üsna taltsas.
Tavaline lähenemisviis on alustada sisestatud IF-valemi koostamist. Alustate alati vahemiku kõrgeimast või madalamast otsast. „Kui müük ületab 500 000 dollarit, on allahindlus 20%; muidu … ” Funktsiooni IF kolmas argument on täiesti uus IF-funktsioon, mis testib teist taset: kui müük on üle 250 000 USD, on allahindlus 15%; muidu … ”
Need valemid muutuvad järjest pikemaks, kuna tasemeid on rohkem. Sellise valemi kõige raskem osa on meeles pidada, kui palju sulgusid valemi lõppu panna.
Mini boonuse näpunäide
Sulgude sobitamine
Excel sirvib iga uue sulgude taseme jaoks erinevaid värve. Ehkki Excel taaskasutab värve, kasutab ta musta ainult sulgude sulgemiseks ja sobivate sulgude jaoks. Kui lõpetate allpool toodud valemi, jätkake lihtsalt sulgude sisestamist, kuni sisestate musta sulgud.
Tagasi pesastatud valemi näpunäite juurde
Kui kasutate Exceli 2003, on teie valem juba piiril. Siis ei saanud te pesitseda rohkem kui 7 IF-funktsiooni. See oli kole päev, kui volitused, mida muudetakse, plaanivad komisjonitasu ja teil on vaja võimalust lisada kaheksas IF-funktsioon. Täna saate pesa 64 IF funktsiooni. Seda ei tohiks kunagi teha, kuid on tore teada, et 8. või 9. pesastamisel pole probleeme.
Pesastatud IF-funktsiooni asemel proovige kasutada funktsiooni VLOOKUP ebatavalist kasutamist. Kui VLOOKUPi neljas argument muutub valest väärtuseks True, ei otsi funktsioon enam täpset vastet.
Noh, kõigepealt proovib VLOOKUP leida täpse vaste. Kuid kui täpset vastet ei leita, asetub Excel reale veidi vähem kui see, mida otsite.
Mõelge allolevale tabelile. Lahtris C13 otsib Excel tabelist vastet hinnaga 28 355 dollarit. Kui 28355 ei leia, tagastab Excel allahindluse, mis on seotud väärtusega, mis on lihtsalt väiksem. Sel juhul 1% allahindlust 10 000 dollari tasemele.
Reeglite teisendamisel tabelisse E13: F18 peate alustama väikseimast tasemest ja jätkama kõrgeimale tasemele. Kuigi see oli reeglites märkimata, on alla 10 000 dollari müügikäibe korral allahindlus 0%. Peate selle lisama tabeli esimese reana.
Ettevaatust
Kui kasutate VLOOKUPi tõelist versiooni, tuleb teie tabel järjestada kasvavas järjestuses. Paljud inimesed usuvad, et kõik otsingu tabelid tuleb sortida. Kuid tabelit tuleb sortida ainult ligikaudse matši tegemise korral.
Mis saab siis, kui teie juht soovib täiesti iseseisvat valemit ja ei taha, et boonuste tabel oleks paremal? Pärast valemi koostamist saate tabeli otse valemisse kinnistada.
Pange valem režiimi Redigeerimine, topeltklõpsates lahtrit või valides lahtri ja vajutades klahvi F2.
Valige kursori abil kogu teine argument: $ E $ 13: $ F $ 18.
Vajutage klahvi F9. Excel kinnistab otsingu tabeli massiivikonstandina. Massiivikonstandis tähistab semikoolon uut rida ja koma uut veergu.
Vajutage sisestusklahvi. Kopeerige valem teistele lahtritele.
Nüüd saate tabeli kustutada. Lõplik valem on toodud allpool.
Aitäh Mike Girvinile, kes õpetas mind sobivate sulgude kohta. VLOOKUPi tehnikat soovitasid Danny Mac, Boriana Petrova, Andreas Thehos ja @mvmcos.
Vaata videot
- Mitmetasandilise vahendustasu, boonuse või allahindlusprogrammi abil peate sageli oma IF-funktsioonid pesitsema
- Exceli 2003 limiit oli 7 pesastatud IF-lauset.
- Nüüd saate pesa 32, kuid ma ei arva, et peaksite kunagi 32 pesa pesema
- Millal te kunagi kasutaksite VLOOKUPi ligikaudset vaste versiooni? See on aeg.
- Tõlgi allahindlusprogramm otsingu tabelisse
- VLOOKUP ei leia enamikul juhtudel vastust.
- Lõpus paneb väärtus True, et VLOOKUP leiaks väärtuse veidi vähem.
- See on ainus kord, kui VLOOKUP tabelit tuleb sortida.
- Kas te ei soovi, et VLOOKUP-laud oleks külje pealt ära? Manusta see valemisse.
- F2 valemi redigeerimiseks. Valige otsingutabel. Vajutage klahvi F9. Sisenema.
Video ärakiri
Õppige Exceli podcastist, osa 2030 - Pesatud IF asendati VLOOKUPiga!
Podcastin kogu seda raamatut, esitusloendisse pääsemiseks klõpsake paremas ülanurgas oleval nupul „i”!
Tere, tere tulemast tagasi netisaate juurde, ma olen Bill Jelen. Hea küll, see on tõesti tavaline kas komisjonitasu programmi, allahindlusprogrammi või boonusprogrammi puhul, kus meil on tasemed, erinevad tasemed, eks? Kui olete üle 10000 dollari, saate 1% allahindlust, 50000 dollarit 5% allahindlust. Selle sisestatud IF-lause koostamisel peate olema ettevaatlik. Kui otsite suuremat kui, siis alustate seda ülemisest otsast või kui alla. Nii et B10> 50000, 20%, muidu teine IF, B10> 250000, 25% jne jne. See on lihtsalt pikk ja keeruline valem ning kui teie tabel on suurem, siis veel Excelis 2003, ei saaks te pesastada rohkem kui 7 IF-lauset, oleme siin peaaegu piiri lähedal. Võite minna nüüd 32-aastaseks, ma ei arva, et peaksite kunagi minema 32-le ja isegi kui te karjute: "Hei, oota,kuidas on uue funktsiooniga, mis just tuli välja Excel 2016-s, 2016. aasta veebruari väljaandes koos funktsiooniga IFS? " Jah, siin on vähem sulge ja 97 tähemärgi asemel 87 tähemärki, see on ikka jama, vabaneme sellest ja teeme VLOOKUPiga!
Olgu, siin on sammud, võtate need reeglid vastu ja pöörate need neile pähe. Esimene asi, mida peame ütlema, on see, et kui teil oli 0 dollarit müügis, saate 0% allahindlust, kui teil oli 10000 dollarit müügis, saate 1% allahindlust, kui teil oli 50000 dollarit müügis, siis 5% allahindlust . 100000 dollarit, 10% allahindlust, 250000 dollarit, 15% allahindlust ja siis kõik, mis on> 500000 dollarit, saab 20% allahindlust. Nüüd, mitu korda, kui räägin VLOOKUP-ist, ütlen, et iga teie loodud VLOOKUP lõpeb VÄÄRIGA ja inimesed ütlevad: "Noh, oota üks hetk, milleks see TÕELINE versioon ikkagi on?" See on just sel juhul, kui otsime vahemikku, nii et otsime seda väärtust, muidugi tavaline VLOOKUP, 2, FALSE ei leia 550000, tagastab # N / A!Nii et selle ühe väga erilise juhtumi korral muudame selle VÄÄRI TÕELISEKS ja see ütleb Excelile: "Minge otsige 550000, kui te ei leia seda, andke meile väärtus, mis on lihtsalt väiksem." Nii et see annab meile 20%, seda ta teeb, eks? Ja see on ainus kord, kui teie tabel VLOOKUP tuleb sorteerida, kõik muud korrad, valiku FALSE abil, võib see olla nii, nagu soovite. Ja jah, võite jätta, TRUE välja, aga ma panin selle alati sinna lihtsalt selleks, et endale meelde tuletada, et see on üks neist imelikest, eriti ohtlikest VLOOKUPidest, ja ma ei taha seda valemit kuhugi mujale kopeerida. Hästi, nii et kopeerime ja kleepime erivalemid, olgu.olgu? Ja see on ainus kord, kui teie tabel VLOOKUP tuleb sorteerida, kõik muud korrad, valiku FALSE abil, võib see olla nii, nagu soovite. Ja jah, võite jätta, TRUE välja, aga ma panin selle alati sinna lihtsalt selleks, et endale meelde tuletada, et see on üks neist imelikest, eriti ohtlikest VLOOKUPidest, ja ma ei taha seda valemit kuhugi mujale kopeerida. Hästi, nii et kopeerime ja kleepime erivalemid, olgu.olgu? Ja see on ainus kord, kui teie tabel VLOOKUP tuleb sorteerida, kõik muud korrad, valiku FALSE abil, võib see olla nii, nagu soovite. Ja jah, võite jätta tõelise, TRUE välja, kuid ma panin selle alati sinna lihtsalt selleks, et endale meelde tuletada, et see on üks neist imelikest, eriti ohtlikest VLOOKUPidest, ja ma ei taha seda valemit kuhugi mujale kopeerida. Hästi, nii et kopeerime ja kleepime erivalemid, olgu.
Järgmine kaebus: teie haldur ei soovi otsingutabelit näha, vaid seda, et see lihtsalt "vabaneks sellest otsingu tabelist". Hästi, me saame seda teha otsingulaua kinnistamisega, vaadake seda, suurepärane trikk, mille sain Mike Girvinilt ExcelIsFunis. F2 valemi redigeerimisrežiimi viimiseks ja seejärel valige väga ettevaatlikult tabeli vahemik. Vajutage klahvi F9 ja see võtab selle tabeli ja paneb selle massiivi nomenklatuuri ja siis ma lihtsalt vajutan Enter niimoodi. Kopeerige see alla, Kleebi erivalemid, ja siis saan vabaneda otsingu tabelist, see kõik jätkab rida. See on tohutu vaev, kui peate tagasi tulema ja seda redigeerima, peate seda tõesti väga selgelt vahtima. Koma tähendab, et läheme järgmisele veerule, semikoolon tähendab järgmist rida, olgu,kuid teoreetiliselt võiksite sinna tagasi tulla ja seda valemit muuta, kui üks ahelarvudest muutub.
Hea küll, seega on pesastatud IF-lause asemel VLOOKUP-i kasutamine näpunäide nr 32, mis on meie teel 40-le, "40 kõigi aegade suurimat Exceli näpunäidet", teil võib olla kogu see raamat. Klõpsake paremas ülanurgas seda tähte „i”, e-raamatu eest 10 dollarit, trükiraamatu eest 25 dollarit, olgu. Nii proovime täna lahendada astmelise komisjonitasu või allahindlusprogrammi. Pesastatud IF-id on tõesti levinud, olge ettevaatlik, 7 on kõik, mis teil Excelis 2003 võib olla, täna võib teil olla 32, kuid teil ei tohiks kunagi olla 32. Nii et kui kasutada VLOOKUPi ligikaudset MATCH-versiooni, siis see on kõik. Võtke see allahindlusprogramm, keerake see tagurpidi, tehke sellest otsingutabel, mis algab väikseima arvuga ja läheb kõige suurema arvuni. Muidugi ei leia VLOOKUP vastust, kuid muutes lõpus VLOOKUP väärtuseks TÕENE, ütleb ta, et ta leiaks väärtuse veidi vähem,see on ainus kord, kui tabelit tuleb sortida. Kui te ei soovi seda tabelit seal näha, saate selle valemisse kinnistada, kasutades valemi redigeerimiseks Mike Girvini trikki F2, valige otsingutabel, vajutage F9 ja seejärel sisestusklahvi.
Hei hei, ma tahan tänada teid, et peatusite, näeme järgmine kord järgmise netisaate jaoks!
Laadige fail alla
Laadige näidisfail alla siit: Podcast2030.xlsx
