Cu mult timp în urmă, am lucrat pentru vicepreședintele vânzări la o companie. Î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 prezentat în acest sfat 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%; în 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.
Dacă utilizați Excel 2003, formula dvs. se apropie deja de limită. Cu această versiune, nu puteți 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ă cuibăriți atât de mulți, dar este plăcut să știți că nu există nicio problemă cuibărirea 8 sau 9
În loc să utilizați funcția IF imbricată, încercați să utilizați 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ă, 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 pentru o potrivire aproximativă.
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. Folosiți cursorul pentru a selecta întregul al doilea argument: $ E $ 13: $ F $ 18.
Apăsați tasta F9. Excel încorporează 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ă. Consultați Înțelegerea constantelor de matrice.
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.
