Ali imate Excelovo formulo, ki ugnezdi več funkcij IF? Ko pridete do točke s preveč ugnezdenih stavkov IF, morate preveriti, ali bi vse skupaj postalo preprostejše s preprosto funkcijo VLOOKUP. Videl sem, da so formule s 1000 znaki poenostavljene na 30-mestno formulo VLOOKUP. To je enostavno vzdrževati, ko morate pozneje dodati nove vrednosti.
Že dolgo nazaj sem delal za podpredsednika prodaje v službi. Vedno sem oblikoval kak nov bonusni program ali načrt provizije. Precej sem se navadila naročiti načrte v najrazličnejših pogojih. Spodnji je precej ukroten.
Običajni pristop je začetek gradnje ugnezdene formule IF. Vedno začnete na zgornjem ali spodnjem koncu razpona. »Če prodaja presega 500.000 USD, je popust 20%; drugače … " Tretji argument funkcije IF je povsem nova funkcija IF, ki preizkuša drugo raven: če je prodaja več kot 250 tisoč USD, je popust 15%; v nasprotnem primeru … «
Te formule so vedno daljše, saj je ravni več. Najtežji del takšne formule je spomin, koliko zaključnih oklepajev naj bo na koncu formule.
Mini bonus nasvet
Ujemanje oklepajev
Excel kroži skozi različne barve za vsako novo raven oklepajev. Medtem ko Excel znova uporablja barve, črno barvo uporablja samo za začetno oklepaje in za ujemajoče se zaključne oklepaje. Ko končujete spodnjo formulo, nadaljujte s tipkanjem zaključnih oklepajev, dokler ne vtipkate črne oklepaje.
Nazaj na nasvet ugnezdene formule
Če uporabljate Excel 2003, je vaša formula že blizu meje. Takrat niste mogli ugnezditi več kot 7 funkcij IF. Bil je grd dan, ko so pooblastila, ki bodo spremenila načrt provizije, in potrebovali način, kako dodati osmo funkcijo IF. Danes lahko ugnezdiš 64 funkcij IF. Nikoli tega ne smete storiti, vendar je lepo vedeti, da pri gnezdenju 8 ali 9 ni težav.
Namesto da uporabite ugnezdeno funkcijo IF, poskusite uporabiti nenavadno uporabo za funkcijo VLOOKUP. Ko se četrti argument VLOOKUP spremeni iz False v True, funkcija ne išče več natančnega ujemanja.
No, najprej VLOOKUP poskuša najti natančno ujemanje. Če pa natančnega ujemanja ne najdemo, se Excel poravna v vrstico le manj kot tisto, kar iščete.
Upoštevajte spodnjo tabelo. V celici C13 bo Excel v tabeli iskal ujemanje za 28.355 USD. Ko 28355 ne najde, bo Excel vrnil popust, povezan z vrednostjo, ki je le manjša. V tem primeru 1% popust za raven 10.000 USD.
Ko pretvorite pravila v tabelo v E13: F18, morate začeti z najmanjše ravni in nadaljevati na najvišjo raven. Čeprav v pravilih ni bilo navedenega, bo popust 0%, če nekdo proda manj kot 10.000 USD. To morate dodati kot prvo vrstico v tabeli.
Previdno
Ko uporabljate "True" različico VLOOKUP, mora biti tabela razvrščena naraščajoče. Mnogi ljudje verjamejo, da je treba vse iskalne tabele razvrstiti. Toda tabelo je treba razvrstiti samo v primeru približne ujemanja.
Kaj pa, če vaš upravitelj želi popolnoma samostojno formulo in ne želi videti deske z bonus mizo? Po izdelavi formule lahko tabelo vdelate naravnost v formulo.
Formulo postavite v način urejanja tako, da dvokliknete celico ali tako, da izberete celico in pritisnete F2.
S kazalko izberite celoten drugi argument: $ E $ 13: $ F $ 18.
Pritisnite tipko F9. Excel bo vdelano iskalno tabelo vstavil kot konstanto matrike. V konstanti polja podpičje označuje novo vrstico, vejica pa nov stolpec.
Pritisnite Enter. Kopirajte formulo v druge celice.
Zdaj lahko tabelo izbrišete. Končna formula je prikazana spodaj.
Hvala Miku Girvinu, ker me je poučil o ujemajočih se oklepajih. Tehniko VLOOKUP so predlagali Danny Mac, Boriana Petrova, Andreas Thehos in @mvmcos.
Oglejte si video
- Z večstopenjskim programom provizij, bonusov ali popustov morate pogosto ugnezditi svoje funkcije IF
- Omejitev programa Excel 2003 je bilo 7 ugnezdenih stavkov IF.
- Zdaj lahko gnezdiš 32, vendar mislim, da ne bi smel kdaj gnezditi 32
- Kdaj bi kdaj uporabil približno primerljivo različico VLOOKUP-a? To je čas.
- Prevedite program popustov v iskalno tabelo
- VLOOKUP v večini primerov ne bo našel odgovora.
- Če na koncu poveste True, bo VLOOKUP povedal, da bo vrednost našla le manj.
- Takrat je treba razvrstiti tabelo VLOOKUP.
- Ne želite, da se miza VLOOKUP odstrani? Vdelajte ga v formulo.
- F2 za urejanje formule. Izberite iskalno tabelo. Pritisnite F9. Enter.
Video zapis
Naučite se Excel iz podcasta, epizoda 2030 - Nadomeščeni ugnezdeni IF z VLOOKUP!
Celotno knjigo bom objavil v podcastu, kliknite »i« v zgornjem desnem kotu, da pridete do seznama predvajanja!
Hej, dobrodošli nazaj v oddaji, jaz sem Bill Jelen. V redu, to je resnično pogosto pri programu provizij ali programu popustov ali bonus programu, kjer imamo stopnje, različne stopnje, kajne? Če ste nad 10000 USD, prejmete 1% popust, 50000 USD 5% popust. Bodite previdni, ko gradite ta ugnezdeni stavek IF, zaženete ga na zgornjem koncu, če iščete več kot, ali na spodnjem koncu, če iščete manj kot. Torej B10> 50000, 20%, sicer pa še IF, B10> 250000, 25% itd. In tako naprej. To je le dolga in zapletena formula in če je vaša tabela večja, v Excelu 2003 ne morete ugnezditi več kot 7 stavkov IF, smo tu skoraj blizu meje. Zdaj lahko greš na 32, mislim, da ne bi smel nikoli na 32, in tudi če kričiš: "Hej, počakaj,kaj pa nova funkcija, ki je pravkar izšla v Excelu 2016, februarja 2016, s funkcijo IFS? " Ja, seveda, tukaj je manj oklepajev in 87 znakov namesto 97 znakov, še vedno je zmeda, rešimo se tega in naredimo z VLOOKUPOM!
V redu, tukaj so koraki, upoštevaš ta pravila in jih obrneš na glavo. Prva stvar, ki jo moramo povedati, je, da če ste imeli 0 USD prodaje, dobite 0% popusta, če ste prodali 10000 USD, dobite 1% popust, če imate 50000 USD prodaje, dobite 5% popusta . 100000 USD, 10% popust, 250000 USD, 15% popust, nato pa vse, kar je> 500000 USD, prejme 20% popust. Zdaj se velikokrat vsakič, ko govorim o VLOOKUP-u, rečem, da se bo vsak VLOOKUP, ki ga kdajkoli ustvarite, končal NALOŽNO, ljudje pa bodo rekli "No, počakajte malo, za kaj sploh obstaja TRUE različica?" Prav v tem primeru iščemo obseg, zato iščemo to vrednost, običajni VLOOKUP, seveda, 2, FALSE ne bo našel 550000, bo vrnil # N / A!Torej, v tem enem zelo posebnem primeru bomo to FALSE spremenili v TRUE in to bo Excelu reklo: "Poiščite 550000, če ga ne najdete, nam dajte vrednost, ki je le manjša." Torej nam daje 20%, to tudi počne, v redu? In to je edini čas, da morate razvrstiti tabelo VLOOKUP, vsi drugi časi z FALSE, lahko je, kakor želite. In ja, resnico lahko pustite izključeno, vendar jo vedno dam tam, da se spomnim, da je to eden tistih čudnih neverjetno nevarnih VLOOKUP-ov, in te formule ne želim kopirati drugam. V redu, zato bomo kopirali in prilepili posebne formule.vredu? In to je edini čas, da morate razvrstiti tabelo VLOOKUP, vsi drugi časi z FALSE, lahko je, kakor želite. In ja, resnico lahko pustite izključeno, vendar jo vedno dam tam, da se spomnim, da je to eden tistih čudnih neverjetno nevarnih VLOOKUP-ov, in te formule ne želim kopirati drugam. V redu, torej bomo kopirali in prilepili posebne formule, v redu.vredu? In to je edini čas, da morate razvrstiti tabelo VLOOKUP, vsi drugi časi z FALSE, lahko je, kakor želite. In ja, resnico lahko pustite izključeno, vendar jo vedno dam tam, da se spomnim, da je to eden tistih čudnih neverjetno nevarnih VLOOKUP-ov, in te formule ne želim kopirati drugam. V redu, torej bomo kopirali in prilepili posebne formule, v redu.
Naslednja pritožba: vaš upravitelj ne želi videti iskalne tabele, ampak jo želi »Samo znebite se te iskalne tabele.« V redu, to lahko storimo z vdelavo iskalne tabele, preverite ta odličen trik, ki sem ga dobil od Mikea Girvina pri programu ExcelIsFun. F2, da formulo prestavite v način urejanja, nato pa zelo previdno izberite le obseg za iskalno tabelo. Pritisnite F9, ta miza vzame to tabelo in jo postavi v nomenklaturo nizov, nato pa samo pritisnem Enter. Kopirajte to, Prilepite posebne formule, in potem se lahko znebim iskalne tabele, ki vse še naprej deluje po vrsti. To je velika težava, če se moraš vrniti in urediti to, moraš resnično zreti vanjo. Vejica pomeni, da gremo v naslednji stolpec, podpičje pomeni, da gremo v naslednjo vrstico, v redu,vendar bi se teoretično lahko vrnili tja in spremenili to formulo, če se spremeni eno od številk verige.
Torej, uporaba VLOOKUP-a namesto ugnezdenega stavka IF je nasvet št. 32 na poti do 40, "40 največjih nasvetov za Excel vseh časov", lahko imate celo to knjigo. V zgornjem desnem kotu kliknite "i", 10 USD za e-knjigo, 25 USD za tiskano knjigo. Torej danes poskušamo rešiti stopenjski provizijski bonus ali popustni program. Vgnezdeni IF so res pogosti, pazite, 7 je vse, kar lahko imate v Excelu 2003, danes jih lahko imate 32, vendar jih nikoli ne bi smeli imeti 32. Kdaj torej uporabiti približno MATCH različico VLOOKUP, je to to. Vzemite ta popustni program, ga obrnite na glavo, naredite ga v iskalni tabeli, začenši z najmanjšim številom in nato največjim. VLOOKUP seveda ne bo našel odgovora, toda s spreminjanjem VLOOKUPA na TRUE na koncu mu bo sporočil, naj poišče vrednost le manj,tokrat je treba razvrstiti tabelo. Če ne želite videti te tabele tam zunaj, jo lahko vstavite v formulo z uporabo trika Mike Girvin, F2, da uredite formulo, izberite iskalno tabelo, pritisnite F9 in nato Enter.
V redu, hej, rad bi se vam zahvalil, da ste se ustavili, se vidimo naslednjič za še eno oddajo!
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2030.xlsx
