Davno sam radio za potpredsjednika prodaje u nekoj tvrtki. Uvijek sam modelirao neki novi bonus program ili plan provizije. Prilično sam se naviknuo na naručivanje planova pod svakakvim uvjetima. Ovaj prikazan u ovom savjetu prilično je 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%; u suprotnom, … " 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%; u suprotnom, …"
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.
Ako koristite Excel 2003, vaša se formula već približila ograničenju. S tom verzijom ne možete 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 ne biste smjeli gnijezditi toliko, ali lijepo je znati da nema problema gnijezditi se 8 ili 9.
Umjesto da koristite ugniježđenu funkciju IF, pokušajte koristiti funkciju 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, Excel se smješta 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 uspije 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. Ali tablicu je potrebno sortirati samo za 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 ugrađuje tablicu pretraživanja kao konstantu niza. U konstanti niza točka-zarez označava novi redak, a zarez novi stupac. Pogledajte Razumijevanje konstanti niza.
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.
