Aveți o formulă Excel care cuibărește mai multe funcții IF? Când ajungeți la punctul cu prea multe instrucțiuni IF imbricate, trebuie să vedeți dacă totul ar deveni mai simplu cu o funcție simplă VLOOKUP. Am văzut formule de 1000 de caractere simplificate la o formulă VLOOKUP de 30 de caractere. Este ușor de întreținut atunci când trebuie să adăugați valori noi mai târziu.
Cu mult timp în urmă, am lucrat pentru vicepreședintele vânzări la locul de muncă. Întotdeauna modelam un nou program bonus sau un plan de comisioane. M-am obișnuit destul să comand comenzi cu tot felul de condiții. Cel de mai jos este destul de blând.
Abordarea normală este de a începe construirea unei formule IF imbricate. Începeți întotdeauna fie la capătul superior, fie la capătul inferior al gamei. „Dacă vânzările depășesc 500.000 de dolari, atunci reducerea este de 20%; in caz contrar… ." Al treilea argument al funcției IF este o funcție IF cu totul nouă care testează pentru al doilea nivel: Dacă vânzările depășesc 250K USD, atunci reducerea este de 15%; in caz contrar,… "
Aceste formule devin din ce în ce mai lungi, deoarece există mai multe niveluri. Cea mai grea parte a unei astfel de formule este să ne amintim câte paranteze de închidere să punem la sfârșitul formulei.
Sfat Mini Bonus
Potrivirea parantezelor
Excel parcurge o varietate de culori pentru fiecare nivel nou de paranteze. În timp ce Excel reutilizează culorile, folosește negru numai pentru parantezele de deschidere și pentru parantezele de închidere potrivite. Pe măsură ce terminați formula de mai jos, continuați să tastați paranteze de închidere până când tastați o paranteză neagră.
Înapoi la Sfatul cu formula imbricată
Dacă utilizați Excel 2003, formula dvs. se apropie deja de limită. Pe atunci, nu puteai cuibări mai mult de 7 funcții IF. A fost o zi urâtă când puterile au schimbat planul de comision și ai nevoie de o modalitate de a adăuga o a opta funcție IF. Astăzi, puteți adăuga 64 de funcții IF. Nu ar trebui să faceți acest lucru niciodată, dar este plăcut să știți că nu există nicio problemă de cuibărit 8 sau 9.
În loc să utilizați funcția IF imbricată, încercați să utilizați utilizarea neobișnuită pentru funcția VLOOKUP. Când al patrulea argument al VLOOKUP se schimbă din False în True, funcția nu mai caută o potrivire exactă.
Ei bine, mai întâi VLOOKUP încearcă să găsească o potrivire exactă. Dar dacă nu se găsește o potrivire exactă, atunci Excel se instalează pe rând cu puțin mai puțin decât ceea ce căutați.
Luați în considerare tabelul de mai jos. În celula C13, Excel va căuta o potrivire pentru 28.355 USD în tabel. Când nu găsește 28355, Excel va returna reducerea asociată cu valoarea care este doar mai mică. În acest caz, reducerea de 1% pentru nivelul de 10.000 USD.
Când convertiți regulile în tabel în E13: F18, trebuie să începeți de la cel mai mic nivel și să treceți la cel mai înalt nivel. Deși nu era precizat în reguli, dacă cineva are vânzări sub 10.000 USD, reducerea va fi de 0%. Trebuie să adăugați acest lucru ca primul rând din tabel.
Prudență
Când utilizați versiunea „True” a VLOOKUP, tabelul dvs. trebuie să fie sortat crescător. Mulți oameni cred că toate tabelele de căutare trebuie să fie sortate. Dar un tabel trebuie să fie sortat numai în cazul unei potriviri aproximative.
Ce se întâmplă dacă managerul dvs. dorește o formulă complet autonomă și nu vrea să vadă tabelul de bonusuri în dreapta? După construirea formulei, puteți încorpora tabelul chiar în formulă.
Puneți formula în modul Editare făcând dublu clic pe celulă sau selectând celula și apăsând F2.
Folosind cursorul, selectați întregul al doilea argument: $ E $ 13: $ F $ 18.
Apăsați tasta F9. Excel va încorpora tabelul de căutare ca o constantă de matrice. În constanta matricei, un punct și virgulă indică un rând nou, iar o virgulă indică o nouă coloană.
Apasa Enter. Copiați formula în celelalte celule.
Acum puteți șterge tabelul. Formula finală este prezentată mai jos.
Mulțumesc lui Mike Girvin pentru că m-a învățat despre parantezele potrivite. Tehnica VLOOKUP a fost sugerată de Danny Mac, Boriana Petrova, Andreas Thehos și @mvmcos.
Urmăriți videoclipul
- Cu un comision pe niveluri, bonus sau program de reduceri, trebuie adesea să vă cuibăriți funcțiile IF
- Limita Excel 2003 era de 7 instrucțiuni IF imbricate.
- Acum poți cuibări 32, dar nu cred că ar trebui să cuibărești vreodată 32
- Când ați folosi vreodată versiunea de potrivire aproximativă a VLOOKUP? Acesta este timpul.
- Traduceți programul de reduceri într-un tabel de căutare
- VLOOKUP nu va găsi răspunsul în majoritatea cazurilor.
- Punând, True la final îi va spune VLOOKUP să găsească valoarea doar mai puțin.
- Aceasta este singura dată când trebuie sortată tabela VLOOKUP.
- Nu doriți ca masa VLOOKUP să fie laterală? Încorporați-l în formulă.
- F2 pentru a edita formula. Selectați tabelul de căutare. Apăsați F9. Introduce.
Transcriere video
Aflați Excel din podcast, episodul 2030 - Înlocuit Nested IF cu VLOOKUP!
Voi transmite în podcast această carte întreagă, dau clic pe „i” din colțul din dreapta sus pentru a ajunge la lista de redare!
Hei, bine ai venit din nou pe netcast, eu sunt Bill Jelen. Bine, acest lucru este foarte obișnuit fie cu un program de comision, fie cu un program de reduceri, fie cu un program bonus, unde avem nivelurile, nivelurile diferite, nu? Dacă sunteți peste 10000 USD, primiți 1% reducere, 50000 USD 5% reducere. Trebuie să aveți grijă atunci când creați această instrucțiune IF imbricată, o începeți la capătul superior dacă căutați mai mult de sau la capătul inferior dacă căutați mai puțin decât. Deci B10> 50000, 20%, altfel un alt IF, B10> 250000, 25% și așa mai departe și așa mai departe. Aceasta este doar o formulă lungă și complicată și, dacă masa dvs. este mai mare, în Excel 2003, nu ați putea cuibări mai mult de 7 instrucțiuni IF, suntem aproape de limită aici. Poți să mergi la 32 acum, nu cred că ar trebui să mergi vreodată la 32 și chiar dacă țipi „Hei stai,Ce zici de noua funcție care tocmai a apărut în Excel 2016, lansarea din februarie 2016, cu funcția IFS? ” Da, sigur, există mai puține paranteze aici și 87 de caractere în loc de 97 de caractere, este încă o mizerie, să scăpăm de asta și să o facem cu o VLOOKUP!
Bine, iată pașii, luați acele reguli și le întoarceți pe cap. Primul lucru pe care trebuie să-l spunem este că, dacă ați avut 0 USD în vânzări, veți obține o reducere de 0%, dacă ați avut 10000 USD în vânzări, veți obține o reducere de 1%, dacă aveți 50000 USD în vânzări, veți obține o reducere de 5% . 100000 USD, 10% reducere, 250000 USD, 15% reducere și, în cele din urmă, orice> 500000 USD primește 20% reducere, bine. Acum, de multe ori, de fiecare dată când vorbesc despre VLOOKUP, spun că fiecare VLOOKUP pe care l-ați creat vreodată se va termina în FALS, iar oamenii vor spune „Ei bine, așteptați o secundă, pentru ce este adevărată versiunea acolo?” Este tocmai în acest caz în care căutăm o gamă, deci căutăm această valoare, un VLOOKUP obișnuit, desigur, 2, FALSE nu va găsi 550000, va returna # N / A!Deci, în acest caz foarte special, vom schimba acel FALS într-un ADEVĂRAT și asta îi va spune Excel „Du-te și caută 550000, dacă nu îl găsești, dă-ne valoarea care este doar mai mică”. Deci ne oferă 20%, asta face, bine? Și acesta este singurul moment în care tabelul dvs. VLOOKUP trebuie să fie sortat, toate celelalte ori cu, FALS, poate fi oricum doriți. Și da, ai putea lăsa, ADEVĂRAT, dar îl pun mereu acolo doar pentru a-mi reaminti că acesta este unul dintre acele VLOOKUP-uri ciudat de incredibil de periculoase și nu vreau să copiez această formulă în altă parte. Bine, așa că vom copia și lipi formulele speciale, bine.bine? Și acesta este singurul moment în care tabelul dvs. VLOOKUP trebuie să fie sortat, toate celelalte ori cu, FALS, poate fi oricum doriți. Și da, ai putea lăsa, ADEVĂRAT, dar îl pun mereu acolo doar pentru a-mi reaminti că acesta este unul dintre acele VLOOKUP-uri ciudat de incredibil de periculoase și nu vreau să copiez această formulă în altă parte. Bine, așa că vom copia și lipi formulele speciale, bine.bine? Și acesta este singurul moment în care tabelul dvs. VLOOKUP trebuie să fie sortat, toate celelalte ori cu, FALS, poate fi oricum doriți. Și da, ai putea lăsa, ADEVĂRAT, dar îl pun mereu acolo doar pentru a-mi reaminti că acesta este unul dintre acele VLOOKUP-uri ciudat de incredibil de periculoase și nu vreau să copiez această formulă în altă parte. Bine, așa că vom copia și lipi formulele speciale, bine.
Următoarea reclamație: managerul dvs. nu dorește să vadă tabelul de căutare, ci îl dorește „Scăpați doar de tabelul de căutare”. În regulă, putem face acest lucru încorporând tabelul de căutare, verificați acest lucru, un truc grozav pe care l-am primit de la Mike Girvin la ExcelIsFun. F2 pentru a pune formula în modul Editare, apoi selectați foarte atent doar intervalul pentru tabelul de căutare. Apăsați F9 și ia acest tabel și îl pune în nomenclatura matricei, apoi apăs pe Enter așa. Copiați-l, lipiți formule speciale și apoi sunt liber să scap de tabelul de căutare, că totul continuă să funcționeze pe rând. Este o problemă uriașă dacă trebuie să revii și să editezi acest lucru, trebuie să te uiți cu adevărat la el cu multă claritate. Virgula înseamnă că mergem la coloana următoare, punctul și virgula înseamnă că mergem la rândul următor, bine,dar teoretic ai putea să te întorci acolo și să schimbi acea formulă dacă se schimbă unul dintre numerele lanțului.
Bine, deci utilizarea unui VLOOKUP în loc de o instrucțiune IF imbricată este sfatul nr. 32 pe drumul nostru către 40, „40 de cele mai bune sfaturi Excel din toate timpurile”, puteți avea această carte întreagă. Faceți clic pe „i” din colțul din dreapta sus, bine 10 USD pentru o carte electronică, 25 USD pentru cartea tipărită. Așadar, astăzi încercăm să rezolvăm un bonus de comision pe niveluri sau un program de reduceri. IF-urile imbricate sunt cu adevărat obișnuite, atenție, 7 este tot ce puteți avea în Excel 2003, astăzi puteți avea 32, dar nu ar trebui să aveți niciodată 32. Deci, când utilizați versiunea MATCH aproximativă a VLOOKUP, acesta este. Luați acel program de reduceri, întoarceți-l cu susul în jos, transformați-l într-un tabel de căutare începând cu cel mai mic număr și mergând la cel mai mare număr. VLOOKUP, desigur, nu va găsi răspunsul, dar schimbând VLOOKUP în, ADEVĂRAT la sfârșit, îi va spune să găsească valoarea doar mai puțin,aceasta este singura dată când tabelul trebuie să fie sortat. Dacă nu doriți să vedeți acel tabel acolo, îl puteți încorpora în formulă folosind trucul Mike Girvin, F2 pentru a edita formula, selectați tabelul de căutare, apăsați F9 și apoi Enter.
Bine hei, vreau să-ți mulțumesc că ai trecut pe aici, ne vedem data viitoare pentru un alt netcast de la!
Descărcare fișier
Descărcați exemplul de fișier aici: Podcast2030.xlsx
