Imate li Excel formulu koja ugniježđuje više IF funkcija? Kad dođete do točke s previše ugniježđenih IF izraza, morate vidjeti hoće li cijela stvar postati jednostavnija pomoću jednostavne funkcije VLOOKUP. Vidio sam da se formule od 1000 znakova pojednostavljuju do formule od 30 znakova VLOOKUP. Jednostavno ga je održavati kad kasnije trebate dodati nove vrijednosti.
Davno sam radio za potpredsjednika prodaje na poslu. Uvijek sam modelirao neki novi bonus program ili plan provizije. Prilično sam se naviknuo na naručivanje planova pod svakakvim uvjetima. Ovaj dolje je prilično pitom.
Uobičajeni pristup je započeti izgradnju ugniježđene IF formule. Uvijek započinjete s gornjeg ili donjeg kraja raspona. „Ako je prodaja veća od 500.000 USD, tada je popust 20%; inače … " Treći argument funkcije IF potpuno je nova funkcija funkcije IF koja testira drugu razinu: Ako je prodaja veća od 250.000 USD, popust je 15%; inače, … "
Te formule postaju sve dulje i dulje kako postoji više razina. Najteži dio takve formule je pamćenje koliko zatvarajućih zagrada treba staviti na kraj formule.
Mini bonus savjet
Podudaranje zagrada
Excel kruži kroz razne boje za svaku novu razinu zagrada. Dok Excel ponovno koristi boje, crnu koristi samo za otvaranje zagrada i za odgovarajuće zagrade za zatvaranje. Dok završavate donju formulu, samo nastavite tipkati zagrade dok ne upišete crnu zagradu.
Povratak na ugniježđenu formulu
Ako koristite Excel 2003, vaša se formula već približila ograničenju. Tada niste mogli ugnijezditi više od 7 IF funkcija. Bio je to ružan dan kada su ovlasti koje će se mijenjati planirale proviziju i trebao vam je način da dodate osmu funkciju IF. Danas možete ugnijezditi 64 IF funkcije. Nikada to ne biste trebali raditi, ali lijepo je znati da nema problema s gniježđenjem 8 ili 9.
Umjesto da koristite ugniježđenu funkciju IF, pokušajte upotrijebiti neobičnu upotrebu funkcije VLOOKUP. Kad se četvrti argument VLOOKUP-a promijeni iz False u True, funkcija više ne traži točno podudaranje.
Pa, prvo VLOOKUP pokušava pronaći točno podudaranje. Ali ako se ne pronađe točno podudaranje, tada će se Excel smjestiti u red samo manje od onoga što tražite.
Razmotrite donju tablicu. U ćeliji C13 Excel će u tablici tražiti podudaranje za 28.355 američkih dolara. Kad ne može pronaći 28355, Excel će vratiti popust povezan s vrijednosti koja je samo manja. U ovom slučaju, popust od 1% za razinu od 10.000 USD.
Kada pretvorite pravila u tablicu u E13: F18, morate početi od najmanje razine i prijeći na najvišu razinu. Iako to nije navedeno u pravilima, ako netko proda manje od 10.000 USD, popust će biti 0%. To morate dodati kao prvi redak u tablici.
Oprez
Kada koristite verziju VLOOKUP-a "True", tablica mora biti sortirana uzlazno. Mnogi ljudi vjeruju da se sve tablice pretraživanja moraju sortirati. No, tablicu je potrebno sortirati samo u slučaju da se izvrši približno podudaranje.
Što ako vaš upravitelj želi potpuno samostalnu formulu i ne želi vidjeti tablicu bonusa s desne strane? Nakon izgradnje formule možete tablicu ugraditi izravno u formulu.
Stavite formulu u način uređivanja dvostrukim klikom na ćeliju ili odabirom ćelije i pritiskom na F2.
Pomoću kursora odaberite cijeli drugi argument: $ E $ 13: $ F $ 18.
Pritisnite tipku F9. Excel će ugrađivati tablicu pretraživanja kao konstantu niza. U konstanti niza točka-zarez označava novi redak, a zarez novi stupac.
Pritisni enter. Kopirajte formulu u ostale stanice.
Sada možete izbrisati tablicu. Konačna formula prikazana je u nastavku.
Hvala Mikeu Girvinu što me naučio o odgovarajućim zagradama. Tehniku VLOOKUP predložili su Danny Mac, Boriana Petrova, Andreas Thehos i @mvmcos.
Gledaj video
- Uz raznovrsni program provizije, bonusa ili popusta, često morate ugnijezditi svoje IF funkcije
- Ograničenje programa Excel 2003 bilo je 7 ugniježđenih IF izraza.
- Sada možete ugnijezditi 32, ali mislim da nikada ne biste smjeli gnijezditi 32
- Kada biste ikada koristili približnu verziju VLOOKUP-a za podudaranje? Ovo je vrijeme.
- Prevedite program popusta u potražnu tablicu
- VLOOKUP u većini slučajeva neće pronaći odgovor.
- Stavljajući, True na kraju reći će VLOOKUP-u da pronađe vrijednost samo manje.
- Ovo je jedini put da se tablica VLOOKUP mora sortirati.
- Ne želite da VLOOKUP stol skrene sa strane? Ugradite ga u formulu.
- F2 za uređivanje formule. Odaberite tablicu pretraživanja. Pritisnite F9. Unesi.
Prijepis videozapisa
Naučite Excel iz podcasta, epizoda 2030 - Zamijenjeni ugniježđeni AKO VLOOKUP!
Podcast ću cijelu ovu knjigu, kliknite "i" u gornjem desnom kutu da biste došli do popisa za reprodukciju!
Hej, dobrodošao natrag u prijenos uživo, ja sam Bill Jelen. U redu, ovo je stvarno uobičajeno ili za program provizije, ili program popusta ili bonus program, gdje imamo razine, različite razine, zar ne? Ako ste viši od 10000 USD, ostvarujete 1% popusta, 50000 $ 5% popusta. Morate biti oprezni kada gradite ovu ugniježđenu izjavu IF, započinjete je na gornjem kraju ako tražite veće od ili na donjem kraju ako tražite manje od. Dakle B10> 50000, 20%, inače još jedan IF, B10> 250000, 25%, i tako dalje i tako dalje. Ovo je samo duga i komplicirana formula, a ako je vaša tablica veća, u programu Excel 2003, niste mogli ugnijezditi više od 7 IF izjava, ovdje smo gotovo blizu granice. Možete ići na 32 sada, mislim da nikada ne biste trebali ići na 32, pa čak i ako vrištite "Hej, čekaj,što je s novom funkcijom koja je upravo izašla u programu Excel 2016, izdanje u veljači 2016., s funkcijom IFS? " Da, naravno, ovdje je manje zagrada, i 87 znakova umjesto 97 znakova, još uvijek je nered, riješimo se ovoga i napravimo to s VLOOKUP-om!
Dobro, evo koraka, vi se pridržavate tih pravila i okrećete im glavu. Prvo što moramo reći je da ako ste imali 0 dolara prodaje, ostvarujete popust od 0%, ako ste prodali 10000 dolara, dobivate popust od 1%, ako imate 50000 dolara prodaje, dobivate popust od 5% . $ 100000, popust od 10%, $ 250000, popust od 15%, i konačno, sve> 500000 $ dobiva popust od 20%, u redu. Sad puno puta, svaki put kad govorim o VLOOKUP-u, kažem da će svaki VLOOKUP koji ikada napravite završiti LAŽNO, a ljudi će reći "Pa pričekajte malo, za što uopće postoji PRAVA verzija?" To je upravo za ovaj slučaj kada tražimo raspon, pa tražimo ovu vrijednost, redoviti VLOOKUP, naravno, 2, FALSE neće pronaći 550000, vratit će # N / A!Dakle, u ovom jednom vrlo posebnom slučaju promijenit ćemo FALSE u TRUE, a to će reći Excelu "Idite potražite 550000, ako ga ne možete pronaći, dajte nam vrijednost koja je samo manja." Dakle, daje nam 20%, to i radi, u redu? I ovo je jedini put da se vaša VLOOKUP tablica mora sortirati, a sva ostala vremena s FALSE, može biti kako god želite. I da, mogli biste isključiti, TAČNO, ali uvijek ga stavljam samo kako bih se podsjetio da je ovo jedan od onih čudnih nevjerojatno opasnih VLOOKUP-ova i ne želim kopirati ovu formulu negdje drugdje. U redu, kopirat ćemo i zalijepiti posebne formule, u redu.u redu? I ovo je jedini put da se vaša VLOOKUP tablica mora sortirati, a sva ostala vremena s FALSE, može biti kako god želite. I da, mogli biste isključiti, TAČNO, ali uvijek ga stavljam samo kako bih se podsjetio da je ovo jedan od onih čudnih nevjerojatno opasnih VLOOKUP-ova i ne želim kopirati ovu formulu negdje drugdje. U redu, kopirat ćemo i zalijepiti posebne formule, u redu.u redu? I ovo je jedini put da se vaša VLOOKUP tablica mora sortirati, a sva ostala vremena s FALSE, može biti kako god želite. I da, mogli biste isključiti, TAČNO, ali uvijek ga stavljam samo kako bih se podsjetio da je ovo jedan od onih čudnih nevjerojatno opasnih VLOOKUP-ova i ne želim kopirati ovu formulu negdje drugdje. U redu, kopirat ćemo i zalijepiti posebne formule, u redu.
Sljedeća žalba: vaš upravitelj ne želi vidjeti tablicu pretraživanja, on je želi "Samo se riješite te tablice pretraživanja." U redu, to možemo učiniti ugrađivanjem tablice pretraživanja, pogledajte ovaj, sjajni trik koji sam dobio od Mikea Girvina u programu ExcelIsFun. F2 za prebacivanje formule u način uređivanja, a zatim vrlo pažljivo odaberite samo raspon za tablicu pretraživanja. Pritisnite F9, a ona uzima ovu tablicu i stavlja je u nomenklaturu niza, a zatim jednostavno pritisnem Enter. Kopirajte to, zalijepite posebne formule, i tada se mogu riješiti tablice pretraživanja koja sve nastavlja raditi niz redove. Ovo je velika gnjavaža ako se morate vratiti i urediti ovo, morate stvarno buljiti u to s puno jasnoće. Zarez znači da idemo u sljedeći stupac, točka sa zarezom znači da idemo u sljedeći redak, u redu,ali u teoriji biste se mogli vratiti tamo i promijeniti tu formulu ako se promijeni jedan od brojeva lanaca.
Dobro, tako da je korištenje VLOOKUP-a umjesto ugniježđenog IF izraza savjet br. 32 na našem putu do 40, „40 najvećih Excel savjeta svih vremena“, možete dobiti cijelu ovu knjigu. Kliknite ono "i" u gornjem desnom kutu, 10 USD za e-knjigu, 25 USD za tiskanu knjigu, u redu. Dakle, danas pokušavamo riješiti bonus u visini provizije ili program popusta. Ugnježđeni IF-ovi su stvarno uobičajeni, pazite, 7 je sve što možete imati u programu Excel 2003, danas ih možete imati 32, ali nikada ne biste trebali imati 32. Dakle, kada upotrijebiti približnu MATCH verziju VLOOKUP-a, to je to. Uzmite taj program popusta, okrenite ga naopako, pretvorite u tablicu za pretraživanje koja započinje s najmanjim brojem i ide do najvećeg broja. VLOOKUP, naravno, neće pronaći odgovor, ali promjenom VLOOKUP-a u, TRUE na kraju, reći će mu da pronađe vrijednost samo manje,ovo je jedini put da se tablica mora sortirati. Ako tu tablicu ne želite vidjeti, možete je ugraditi u formulu pomoću trika Mike Girvin, F2 za uređivanje formule, odaberite tablicu pretraživanja, pritisnite F9, a zatim Enter.
U redu, želim vam zahvaliti što ste navratili, vidimo se sljedeći put za još jedan prijenos od!
Preuzmi datoteku
Preuzmite uzorak datoteke ovdje: Podcast2030.xlsx
