Lang geleden werkte ik voor de vice-president verkoop bij een bedrijf. Ik was altijd een nieuw bonusprogramma of commissieplan aan het modelleren. Ik raakte er behoorlijk aan gewend om plannen met allerlei voorwaarden te laten maken. Degene die in deze tip wordt getoond, is behoorlijk tam.
De normale benadering is om te beginnen met het bouwen van een geneste IF-formule. Je begint altijd aan de hoge of lage kant van het bereik. “Als de verkoop meer dan $ 500.000 bedraagt, is de korting 20%; anders…. " Het derde argument van de ALS-functie is een geheel nieuwe ALS-functie die test voor het tweede niveau: "Als de verkoop meer dan $ 250K bedraagt, is de korting 15%; anders …."
Deze formules worden langer en langer naarmate er meer niveaus zijn. Het moeilijkste deel van zo'n formule is onthouden hoeveel haakjes sluiten je aan het einde van de formule moet zetten.
Als u Excel 2003 gebruikt, nadert uw formule al de limiet. Met die versie kun je niet meer dan 7 IF-functies nesten. Het was een lelijke dag toen de machten het commissieplan veranderden en je een manier nodig had om een achtste ALS-functie toe te voegen. Tegenwoordig kunt u 64 IF-functies nesten. Je zou er nooit zoveel moeten nestelen, maar het is goed om te weten dat er geen probleem is om er 8 of 9 te nestelen.
Probeer in plaats van de geneste ALS-functie te gebruiken de functie VERT.ZOEKEN. Wanneer het vierde argument van VERT.ZOEKEN verandert van False in True, zoekt de functie niet langer naar een exacte match. Welnu, eerst probeert VERT.ZOEKEN een exacte overeenkomst te vinden. Maar als er geen exacte match wordt gevonden, nestelt Excel zich net minder in de rij dan waarnaar u zoekt.
Beschouw de onderstaande tabel. In cel C13 zoekt Excel naar een match voor $ 28.355 in de tabel. Als het 28355 niet kan vinden, retourneert Excel de korting die is gekoppeld aan de waarde die net minder is - in dit geval de korting van 1% voor het $ 10K-niveau.
Wanneer u de regels naar de tabel in E13: F18 converteert, moet u beginnen vanaf het kleinste niveau en doorgaan naar het hoogste niveau. Hoewel het niet in de regels werd vermeld, is de korting 0% als iemand minder dan $ 10.000 verkoopt. U moet dit toevoegen als de eerste rij in de tabel.
Voorzichtigheid
Als u de "True" -versie van VERT.ZOEKEN gebruikt, moet uw tabel oplopend worden gesorteerd. Veel mensen denken dat alle opzoektabellen gesorteerd moeten worden. Maar een tafel hoeft alleen gesorteerd te worden voor een match bij benadering.
Wat als uw manager een volledig op zichzelf staande formule wil en de bonustafel aan de rechterkant niet wil zien? Nadat u de formule heeft samengesteld, kunt u de tabel rechtstreeks in de formule insluiten. Zet de formule in de bewerkingsmodus door op de cel te dubbelklikken of door de cel te selecteren en op F2 te drukken. Gebruik de cursor om het volledige tweede argument te selecteren: $ E $ 13: $ F $ 18.
Druk op de F9-toets. Excel sluit de opzoektabel in als een matrixconstante. In de matrixconstante geeft een puntkomma een nieuwe rij aan en een komma een nieuwe kolom. Zie Arrayconstanten begrijpen.
Druk op Enter. Kopieer de formule naar de andere cellen.
U kunt de tabel nu verwijderen. De uiteindelijke formule wordt hieronder weergegeven.
Met dank aan Mike Girvin voor het leren van de bijpassende haakjes. De VLOOKUP-techniek werd voorgesteld door Danny Mac, Boriana Petrova, Andreas Thehos en @mvmcos.
