Ar turite „Excel“ formulę, kurioje yra kelios IF funkcijos? Kai pasieksite per daug įterptų IF sakinių, turite pamatyti, ar viskas taps paprasčiau naudojant paprastą VLOOKUP funkciją. Mačiau, kad 1000 simbolių formulės yra supaprastintos iki 30 simbolių VLOOKUP formulės. Tai lengva prižiūrėti, kai vėliau turite pridėti naujų verčių.
Labai seniai dirbau pardavimų viceprezidente darbe. Aš visada modeliuodavau kokią nors naują premijų programą ar komisinių planą. Aš gana įpratau užsakyti planus su visomis sąlygomis. Žemiau esantis yra gana prisijaukintas.
Įprastas požiūris yra pradėti kurti įdėtą IF formulę. Jūs visada pradedate nuo aukščiausio ar žemiausio diapazono pabaigos. „Jei pardavimai viršija 500 000 USD, tada nuolaida yra 20%; kitaip…. “ Trečiasis IF funkcijos argumentas yra visiškai nauja IF funkcija, išbandanti antrą lygį: jei pardavimai viršija 250 000 USD, tada nuolaida yra 15%; kitaip… “
Šios formulės tampa vis ilgesnės, nes yra daugiau lygių. Sunkiausia tokios formulės dalis yra prisiminti, kiek uždarančių skliaustų reikia įdėti formulės pabaigoje.
Mini premijos patarimas
Skliaustų derinimas
„Excel“ pereina įvairias spalvas kiekvienam naujam skliaustų lygiui. Nors „Excel“ pakartotinai naudoja spalvas, juoda spalva naudojama tik pradiniuose skliaustuose ir atitinkančiose uždaromose skliaustuose. Baigdami toliau pateiktą formulę, tiesiog įveskite uždaromus skliaustus, kol įvesite juodą skliaustą.
Grįžti į „Lested Formula“ patarimą
Jei naudojate „Excel 2003“, jūsų formulė jau artėja prie ribos. Tada negalėjote įdėti daugiau nei 7 IF funkcijų. Tai buvo negraži diena, kai galios, kurios buvo pakeistos, planuoja komisinius ir jums reikėjo, kaip pridėti aštuntąją IF funkciją. Šiandien galite įdėti 64 IF funkcijas. Niekada neturėtumėte to daryti, bet malonu žinoti, kad nėra problemų įdėti 8 ar 9 lizdus.
Užuot naudoję įdėtą IF funkciją, pabandykite naudoti neįprastą funkcijos VLOOKUP naudojimą. Kai ketvirtasis „VLOOKUP“ argumentas pasikeičia iš „False“ į „True“, funkcija nebeieško tikslaus atitikimo.
Na, pirmiausia „VLOOKUP“ bando rasti tikslią atitiktį. Bet jei tiksli atitiktis nerandama, „Excel“ įsitaiso eilutėje tik mažiau nei ieškote.
Apsvarstykite toliau pateiktą lentelę. C13 langelyje „Excel“ lentelėje ieškos atitikties už 28 355 USD. Neradusi „28355“, „Excel“ grąžins su mažesne verte susietą nuolaidą. Šiuo atveju 1% nuolaida 10 000 USD lygiui.
Konvertuodami taisykles į lentelę E13: F18, turite pradėti nuo mažiausio lygio ir pereiti į aukščiausią lygį. Nors taisyklėse tai nebuvo nurodyta, jei kas nors parduoda mažiau nei 10 000 USD, nuolaida bus 0%. Tai turite pridėti kaip pirmąją lentelės eilutę.
Atsargiai
Kai naudojate „True“ VLOOKUP versiją, lentelė turi būti rūšiuojama didėjančia tvarka. Daugelis žmonių mano, kad visos paieškos lentelės turi būti rūšiuojamos. Bet lentelę reikia išrūšiuoti tik tuo atveju, jei atliekamas apytikslis mačas.
Ką daryti, jei jūsų vadybininkas nori visiškai savarankiškos formulės ir nenori, kad premijų lentelė būtų dešinėje? Sukūrę formulę, galite įterpti lentelę tiesiai į formulę.
Įdėkite formulę į redagavimo režimą dukart spustelėdami langelį arba pasirinkdami langelį ir paspausdami F2.
Naudodami žymeklį pasirinkite visą antrąjį argumentą: $ E $ 13: $ F $ 18.
Paspauskite klavišą F9. „Excel“ įterps paieškos lentelę kaip masyvo konstantą. Masyvo konstantoje kabliataškis rodo naują eilutę, o kablelis rodo naują stulpelį.
Paspausk Enter. Nukopijuokite formulę žemyn į kitas langelius.
Dabar galite ištrinti lentelę. Galutinė formulė parodyta žemiau.
Ačiū Mike'ui Girvinui, kad jis išmokė mane apie atitinkamus skliaustus. VLOOKUP techniką pasiūlė Danny Macas, Boriana Petrova, Andreasas Thehosas ir @mvmcos.
Žiūrėti video
- Naudodamiesi pakopine komisinių, premijų ar nuolaidų programa, dažnai turite įdėti savo IF funkcijas
- „Excel 2003“ riba buvo 7 įdėti IF teiginiai.
- Dabar galite lizdą 32, bet nemanau, kad kada nors turėtumėte lizdą 32
- Kada kada naudotumėte apytikslę „VLOOKUP“ atitikties versiją? Tai laikas.
- Išverskite nuolaidų programą į paieškos lentelę
- Daugeliu atvejų „VLOOKUP“ neras atsakymo.
- Pabaigoje pateikdamas „True“, „VLOOKUP“ nurodys, kad vertė bus nustatyta mažiau.
- Tai yra vienintelis kartas, kai VLOOKUP lentelė turi būti rūšiuojama.
- Nenorite, kad VLOOKUP lentelė būtų šone? Įterpkite jį į formulę.
- F2 redaguoti formulę. Pasirinkite paieškos lentelę. Paspauskite F9. Įveskite.
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš tinklalaidės, 2030 m. Serijos - „Nested IF“ pakeitė „VLOOKUP“!
Aš transliuosiu visą šią knygą, viršutiniame dešiniajame kampe spustelėkite „i“, kad patektumėte į grojaraštį!
Ei, sveiki sugrįžę į internetinę transliaciją, aš Billas Jelenas. Gerai, tai tikrai būdinga komisinių, nuolaidų ar premijų programoms, kur mes turime pakopas, skirtingas pakopas, tiesa? Jei viršijate 10000 USD, gausite 1% nuolaidą, 50000 USD 5% nuolaidą. Turite būti atsargūs kurdami šį įdėtą IF teiginį, pradėdami jį viršutiniame gale, jei ieškote didesnio nei, arba apačioje, jei ieškote mažiau nei. Taigi B10> 50000, 20%, kitaip kitas IF, B10> 250000, 25% ir t.t. Tai tik ilga ir sudėtinga formulė, ir jei jūsų lentelė yra didesnė, dar „Excel 2003“, negalėjote įdėti daugiau nei 7 IF teiginių, mes čia beveik arti ribos. Dabar galite eiti į 32 metus, nemanau, kad kada nors turėtumėte eiti į 32 metus, ir net jei jūs šaukiate „Ei, palauk,Ką apie naują funkciją, kuri ką tik pasirodė „Excel 2016“, 2016 m. vasario leidimas, su IFS funkcija? “ Taip, tikrai, čia yra mažiau skliaustų ir 87 simboliai, o ne 97 simboliai, vis tiek yra netvarka, atsikratykime to ir padarykime tai su VLOOKUP!
Gerai, čia yra žingsniai, jūs laikotės tų taisyklių ir pasukate jas ant galvos. Pirmas dalykas, kurį turime pasakyti, yra tai, kad jei jūs pardavėte 0 USD, jūs gaunate 0% nuolaidą, jei jūs pardavėte 10000 USD, jūs gaunate 1% nuolaidą, jei turite 50000 USD pardavimų, jūs gaunate 5% nuolaidą . 100000 USD, 10% nuolaida, 250000 USD, 15% nuolaida ir galiausiai viskas, kas yra> 500000 USD, gauna 20% nuolaidą. Dabar daug kartų, kiekvieną kartą, kai kalbu apie „VLOOKUP“, sakau, kad kiekvienas jūsų sukurtas „VLOOKUP“ baigsis NETIESA, o žmonės sakys „Na, palaukite sekundę, kam vis dėlto TIKRA versija?“ Tai yra šis atvejis, kai mes ieškome diapazono, todėl ieškome šios vertės, žinoma, įprastas VLOOKUP, 2, FALSE neras 550000, grąžinsi # N / A!Taigi šiuo vienu ypatingu atveju tą NETIESĄ pakeisime į TIESĄ, ir tai „Excel“ pasakys: „Eikite ieškoti 550000, jei nerandate, suteikite mums tik mažesnę vertę“. Taigi mums duoda 20%, tai ir daro, gerai? Tai yra vienintelis kartas, kai jūsų VLOOKUP lentelė turi būti rūšiuojama, visus kitus kartus naudodama „FALSE“, ji gali būti tokia, kokios norite. Taip, jūs galite palikti „TRUE“ išjungtą, bet aš visada jį įdėjau, kad tik norėčiau priminti sau, jog tai yra vienas iš tų keistų, nepaprastai pavojingų VLOOKUP, ir aš nenoriu kopijuoti šios formulės kur nors kitur. Gerai, todėl nukopijuosime ir įklijuosime specialias formules.gerai? Tai yra vienintelis kartas, kai jūsų VLOOKUP lentelė turi būti rūšiuojama, visus kitus kartus naudodama „FALSE“, ji gali būti tokia, kokios norite. Taip, jūs galite palikti „TRUE“ išjungtą, bet aš visada jį įdėjau, kad tik norėčiau priminti sau, jog tai yra vienas iš tų keistų, nepaprastai pavojingų VLOOKUP, ir aš nenoriu kopijuoti šios formulės kur nors kitur. Gerai, todėl nukopijuosime ir įklijuosime specialias formules.gerai? Tai yra vienintelis kartas, kai jūsų VLOOKUP lentelė turi būti rūšiuojama, visus kitus kartus naudodama „FALSE“, ji gali būti tokia, kokios norite. Taip, jūs galite palikti „TRUE“ išjungtą, bet aš visada jį įdėjau, kad tik norėčiau priminti sau, jog tai yra vienas iš tų keistų, nepaprastai pavojingų VLOOKUP, ir aš nenoriu kopijuoti šios formulės kur nors kitur. Gerai, todėl nukopijuosime ir įklijuosime specialias formules.
Kitas skundas: jūsų vadybininkas nenori matyti paieškos lentelės, jis nori „Tiesiog atsikratykite tos paieškos lentelės“. Gerai, mes galime tai padaryti įterpdami paieškos lentelę, patikrinkite tai, puikus triukas, kurį gavau iš Mike'o Girvino „ExcelIsFun“. F2, kad formulė būtų įjungta į Redagavimo režimą, tada labai atsargiai pasirinkite tik paieškos lentelės diapazoną. Paspauskite F9, ji paims šią lentelę ir įdės ją į masyvų nomenklatūrą, o aš tiesiog taip paspaudžiu Enter. Nukopijuokite tai žemyn, Įklijuokite specialias formules, tada galiu laisvai atsikratyti paieškos lentelės, kad viskas toliau eina žemyn. Tai yra didžiulis vargas, jei jūs turite sugrįžti ir tai redaguoti, jūs turite į tai žiūrėti labai aiškiai. Kablelis reiškia, kad einame į kitą stulpelį, kabliataškis reiškia, kad eisime į kitą eilutę, gerai,bet teoriškai galėtum grįžti ten ir pakeisti šią formulę, jei pasikeistų vienas iš grandinės skaičių.
Gerai, todėl naudodami VLOOKUP vietoj įdėto IF teiginio, patarimas Nr. 32, einant į 40, „40 geriausių visų laikų„ Excel “patarimų“, galite turėti visą šią knygą. Spustelėkite tą „i“ viršutiniame dešiniajame kampe, 10 USD už elektroninę knygą, 25 USD už spausdintą knygą, gerai. Taigi šiandien mes bandome išspręsti pakopinį komisinį priedą arba nuolaidų programą. Įterptieji IF yra tikrai įprasti, saugokitės, 7 yra viskas, ką galite turėti „Excel 2003“, šiandien galite turėti 32, bet niekada neturėtumėte turėti 32. Taigi, kai naudoti apytikslę VLOOKUP MATCH versiją, viskas. Paimkite tą nuolaidų programą, apverskite ją aukštyn kojomis, padarykite ją paieškos lentele, pradedant mažiausiu skaičiumi ir einant prie didžiausio skaičiaus. VLOOKUP, žinoma, neras atsakymo, bet pakeisdamas VLOOKUP į, TRUE pabaigoje, jis liepia rasti vertę tik mažiau,tai yra vienintelis kartas, kai lentelė turi būti rūšiuojama. Jei nenorite matyti tos lentelės, galite ją įterpti į formulę naudodami Mike'o Girvino triuką, F2, kad redaguotumėte formulę, pasirinkite paieškos lentelę, paspauskite F9 ir tada Enter.
Gerai labas, noriu padėkoti už tai, kad užsukote, pamatysime kitą kartą kitam netcast'ui iš!
Atsisiųsti failą
Atsisiųskite failo pavyzdį čia: Podcast2030.xlsx
