Onko sinulla Excel-kaava, joka kätkee useita IF-toimintoja? Kun pääset pisteeseen, jossa on liian monta sisäkkäistä IF-käskyä, sinun täytyy nähdä, yksinkertaistuuko koko asia yksinkertaisella VLOOKUP-toiminnolla. Olen nähnyt 1000 merkin kaavojen yksinkertaistuvan 30 merkin VLOOKUP-kaavaksi. Se on helppo ylläpitää, kun joudut lisäämään uusia arvoja myöhemmin.
Työskentelin kauan sitten myyntijohtajana. Mallin aina jotain uutta bonusohjelmaa tai palkkiojärjestelmää. Tunsin melko tottelemaan suunnitelmia kaikenlaisilla ehdoilla. Alla oleva on melko kesy.
Normaali lähestymistapa on aloittaa sisäkkäisen IF-kaavan rakentaminen. Aloitat aina joko alueen ylä- tai alaosasta. "Jos myynti on yli 500 000 dollaria, alennus on 20%; muuten… ” IF-funktion kolmas argumentti on täysin uusi IF-funktio, joka testaa toista tasoa: Jos myynti on yli 250 000 dollaria, alennus on 15%; muuten… ”
Nämä kaavat pidentyvät ja pitenevät, kun tasoja on enemmän. Tällaisen kaavan vaikein osa on muistaa, kuinka monta sulkeita laitetaan kaavan loppuun.
Mini Bonus Vinkki
Sulujen vastaavuus
Excel selaa eri värejä kullekin uudelle sulkujen tasolle. Vaikka Excel käyttää värejä uudelleen, se käyttää mustaa vain alkusulkeissa ja vastaavissa sulkeissa. Kun olet viimeistelemässä alla olevaa kaavaa, jatka kirjoittamalla sulkeita, kunnes kirjoitat mustan sulun.
Takaisin sisäkkäisiin kaavavinkkeihin
Jos käytät Excel 2003: ta, kaava on jo lähestymässä rajaa. Tuolloin et voinut sijoittaa yli 7 IF-toimintoa. Se oli ruma päivä, jolloin valtuudet, joita muutettiin, palkkasuunnitelmaan ja tarvitsit tapaa lisätä kahdeksas IF-toiminto. Tänään voit sijoittaa 64 IF-toimintoa. Sinun ei pitäisi koskaan tehdä tätä, mutta on mukava tietää, ettei 8: n tai 9: n pesimisessä ole mitään ongelmaa.
Sen sijaan, että käyttäisit sisäkkäistä IF-toimintoa, yritä käyttää VLOOKUP-toiminnon epätavallista käyttöä. Kun VLOOKUPin neljäs argumentti vaihtuu False-arvosta True-arvoksi, funktio ei enää etsi tarkkaa vastaavuutta.
No, ensin VLOOKUP yrittää löytää tarkan vastaavuuden. Mutta jos tarkkaa vastaavuutta ei löydy, Excel sijoittuu riville vain vähemmän kuin etsimäsi.
Harkitse alla olevaa taulukkoa. Solussa C13 Excel etsii taulukosta 28 355 dollaria. Kun se ei löydä 28355: tä, Excel palauttaa alennuksen, joka liittyy vain pienempään arvoon. Tässä tapauksessa 1%: n alennus $ 10K -tasosta.
Kun muunnat säännöt taulukoksi taulukossa E13: F18, sinun on aloitettava pienimmältä tasolta ja siirryttävä korkeimmalle tasolle. Vaikka sitä ei ilmoitettu säännöissä, jos joku on alle 10 000 dollaria myynnissä, alennus on 0%. Sinun on lisättävä tämä taulukon ensimmäiseksi riviksi.
Varoitus
Kun käytät VLOOKUP-version True-versiota, taulukko on lajiteltava nousevasti. Monet ihmiset uskovat, että kaikki hakutaulukot on lajiteltava. Mutta taulukko on lajiteltava vain, jos tehdään likimääräinen ottelu.
Entä jos esimiehesi haluaa täysin itsenäisen kaavan eikä halua nähdä bonustaulukkoa oikealla puolella? Kaavan rakentamisen jälkeen voit upottaa taulukon suoraan kaavaan.
Aseta kaava muokkaustilaan kaksoisnapsauttamalla solua tai valitsemalla solu ja painamalla F2.
Valitse kohdistimen avulla koko toinen argumentti: $ E $ 13: $ F $ 18.
Paina F9-näppäintä. Excel upottaa hakutaulukon matriisivakiona. Matriisivakiossa puolipiste osoittaa uuden rivin ja pilkku osoittaa uuden sarakkeen.
Paina Enter. Kopioi kaava alas muihin soluihin.
Voit nyt poistaa taulukon. Lopullinen kaava on esitetty alla.
Kiitos Mike Girvinille siitä, että hän opetti minulle vastaavista suluista. VLOOKUP-tekniikkaa ehdottivat Danny Mac, Boriana Petrova, Andreas Thehos ja @mvmcos.
Katso video
- Porrastetulla provisio-, bonus- tai alennusohjelmalla joudut usein sijoittamaan IF-toiminnot
- Excel 2003 -raja oli 7 sisäkkäistä IF-käskyä.
- Voit nyt pestä 32, mutta en usko, että sinun pitäisi koskaan pestä 32
- Milloin käytät koskaan likimääräistä VLOOKUP-peliversiota? Tämä on aika.
- Käännä alennusohjelma hakutaulukkoon
- VLOOKUP ei löydä vastausta useimmissa tapauksissa.
- Kun lopuksi asetetaan True, käskyn VLOOKUP löytää arvon vain vähemmän.
- Tämä on ainoa kerta, kun VLOOKUP-taulukko on lajiteltava.
- Etkö halua VLOOKUP-pöydän sivuun? Upota se kaavaan.
- F2 kaavan muokkaamiseksi. Valitse hakutaulukko. Paina F9. Tulla sisään.
Videon transkriptio
Opi Excel podcastista, jakso 2030 - Korvattu sisäkkäiset IF: t VLOOKUPilla!
Lähetän podcasting koko tämän kirjan, pääset soittolistaan napsauttamalla oikeassa yläkulmassa olevaa "i" -kuvaketta!
Hei, tervetuloa takaisin netcastiin, olen Bill Jelen. Selvä, tämä on todella yleistä joko provisio-ohjelman, alennusohjelman tai bonusohjelman kanssa, jossa meillä on tasot, eri tasot, eikö? Jos olet yli 10000 dollaria, saat 1% alennuksen, 50000 dollaria 5% alennuksen. Sinun on oltava varovainen, kun rakennat tämän sisäkkäisen IF-käskyn, aloitat sen yläpäässä, jos etsit suurempaa kuin, tai alaosassa, jos etsit vähemmän kuin. Joten B10> 50000, 20%, muuten toinen IF, B10> 250000, 25% ja niin edelleen ja niin edelleen. Tämä on vain pitkä ja monimutkainen kaava, ja jos taulukosi on suurempi, Excel 2003: ssa, et voi sijoittaa yli 7 IF-käskyä, olemme melkein lähellä rajaa. Voit mennä nyt 32: een, en usko, että sinun pitäisi koskaan mennä 32: een, ja vaikka huutaisitkin "Hei odota,entä uusi toiminto, joka juuri ilmestyi Excel 2016: ssa, helmikuun 2016 julkaisu, IFS-toiminnolla? " Joo varmasti, täällä on vähemmän sulkeita, ja 87 merkkiä 97 merkin sijaan, se on silti sotku, päästään eroon tästä ja tehdään se VLOOKUPilla!
Selvä, tässä ovat vaiheet, otat nuo säännöt ja käännät ne päähänsä. Ensinnäkin meidän on sanottava, että jos sinulla oli 0 dollaria myynnissä, saat 0% alennuksen, jos sinulla oli 10000 dollaria myynnissä, saat 1% alennuksen, jos sinulla on 50000 dollaria myynnissä, saat 5% alennuksen . 100000 dollaria, 10%: n alennus, 250000 dollaria, 15%: n alennus ja lopuksi kaikki> 500 000 dollaria saa 20%: n alennuksen. Nyt monta kertaa, aina kun puhun VLOOKUPista, sanon, että jokainen luomasi VLOOKUP päättyy EPÄTOSIIN, ja ihmiset sanovat "No, odota hetki, mikä on TOSI versio siellä?" Juuri tässä tapauksessa etsimme aluetta, joten etsimme tätä arvoa, tavallinen VLOOKUP, tietenkin, 2, FALSE ei löydä 550000, palauttaa # N / A!Joten tässä yhdessä erityistapauksessa aiomme muuttaa sen EPÄTOSIN TOSIIN, ja se kertoo Excelille: "Mene etsimään 550000: ta, jos et löydä sitä, anna meille arvo, joka on vain vähemmän." Joten se antaa meille 20%, niin se tekee, okei? Ja tämä on ainoa kerta, kun VLOOKUP-pöytäsi on lajiteltava, muutoin FALSE-näppäimellä, se voi olla mitä haluat. Ja kyllä, voit jättää TOSIN pois päältä, mutta laitoin sen aina muistuttamaan itseäni siitä, että tämä on yksi niistä outoista uskomattoman vaarallisista VLOOKUP-tiedostoista, enkä halua kopioida tätä kaavaa muualle. Selvä, joten kopioimme ja liitämme erikoiskaavat.hyvä on? Ja tämä on ainoa kerta, kun VLOOKUP-pöytäsi on lajiteltava, muutoin FALSE-näppäimellä, se voi olla mitä haluat. Ja kyllä, voit jättää TOSIN pois päältä, mutta laitoin sen aina muistuttamaan itseäni siitä, että tämä on yksi niistä outoista uskomattoman vaarallisista VLOOKUP-tiedostoista, enkä halua kopioida tätä kaavaa muualle. Selvä, joten kopioimme ja liitämme erikoiskaavat.hyvä on? Ja tämä on ainoa kerta, kun VLOOKUP-pöytäsi on lajiteltava, muutoin FALSE-näppäimellä, se voi olla mitä haluat. Ja kyllä, voit jättää TOSIN pois päältä, mutta laitoin sen aina muistuttamaan itseäni siitä, että tämä on yksi niistä outoista uskomattoman vaarallisista VLOOKUP-tiedostoista, enkä halua kopioida tätä kaavaa muualle. Selvä, joten kopioimme ja liitämme erikoiskaavat.
Seuraava valitus: esimiehesi ei halua nähdä hakutaulukkoa, vaan sen, että "vain päästä eroon hakutaulukosta". Selvä, voimme tehdä sen upottamalla hakutaulukon, tarkista tämä, hieno temppu, jonka sain Mike Girviniltä ExcelIsFunissa. F2 kaavan siirtämiseksi muokkaustilaan ja valitse sitten hyvin tarkasti vain hakutaulukon alue. Paina F9, ja se vie tämän taulukon ja laittaa sen taulukon nimikkeistöön, ja sitten minä vain painan Enter niin. Kopioi se alas, Liitä erikoiskaavat, ja sitten pääsen eroon hakutaulukosta, joka kaikki vain jatkaa riviä. Tämä on valtava hässäkkä, jos joudut palaamaan sisään ja muokkaamaan tätä, sinun täytyy todella tuijottaa sitä paljon selkeästi. Pilkku tarkoittaa, että siirrymme seuraavaan sarakkeeseen, puolipiste tarkoittaa seuraavaa riviä, ok,mutta voit teoriassa palata sinne ja muuttaa kaavaa, jos joku ketjun numero muuttuu.
Selvä, joten VLOOKUPin käyttäminen sisäkkäisen IF-käskyn sijasta on vihje # 32 matkalla 40, "40 kaikkien aikojen suurimpia Excel-vinkkejä", sinulla voi olla koko tämä kirja. Napsauta sitä “i” oikeassa yläkulmassa, 10 dollaria e-kirjasta, 25 dollaria painokirjasta, ok. Joten yritämme tänään ratkaista porrastetun palkkiobonuksen tai alennusohjelman. Sisäkkäiset IF: t ovat todella yleisiä, varo, 7 on kaikki mitä sinulla voi olla Excel 2003: ssa, tänään sinulla voi olla 32, mutta sinun ei pitäisi koskaan olla 32. Joten kun haluat käyttää VLOOKUPin likimääräistä MATCH-versiota, tämä on se. Ota tämä alennusohjelma, käännä se ylösalaisin, tee siitä hakutaulukko, joka alkaa pienimmällä numerolla ja menee suurimpaan numeroon. VLOOKUP ei tietenkään löydä vastausta, mutta vaihtamalla VLOOKUP-asetukseksi TOSI lopussa, se kertoo sen löytävän arvon vain vähemmän,tämä on ainoa kerta, kun taulukko on lajiteltava. Jos et halua nähdä taulukkoa siellä, voit upottaa sen kaavaan käyttämällä Mike Girvin-temppua, F2 muokataksesi kaavaa, valitse hakutaulukko, paina F9 ja sitten Enter.
Selvä Hei, haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla uudesta netcastista!
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2030.xlsx
