Dawno temu pracowałem jako wiceprezes ds. Sprzedaży w firmie. Zawsze modelowałem jakiś nowy program bonusowy lub plan prowizyjny. Przyzwyczaiłem się do zlecania planów z różnymi warunkami. Ten pokazany w tej wskazówce jest dość oswojony.
Normalnym podejściem jest rozpoczęcie tworzenia zagnieżdżonej formuły JEŻELI. Zawsze zaczynasz od górnego lub dolnego końca zakresu. „Jeśli sprzedaż przekracza 500 000 USD, wówczas rabat wynosi 20%; Inaczej,… ." Trzeci argument funkcji JEŻELI to zupełnie nowa funkcja JEŻELI, która sprawdza drugi poziom: „Jeśli sprzedaż przekracza 250 000 USD, wówczas rabat wynosi 15%; w przeciwnym razie…”.
Te formuły stają się coraz dłuższe, ponieważ jest więcej poziomów. Najtrudniejszą częścią takiej formuły jest zapamiętanie, ile nawiasów zamykających należy umieścić na końcu formuły.
Jeśli używasz programu Excel 2003, Twoja formuła już zbliża się do limitu. W tej wersji nie można zagnieżdżać więcej niż 7 funkcji JEŻELI. To był brzydki dzień, kiedy uprawnienia zmieniły plan prowizji i potrzebowałeś sposobu na dodanie ósmej funkcji IF. Obecnie można zagnieżdżać 64 funkcje JEŻELI. Nigdy nie powinieneś zagnieżdżać tak wielu, ale dobrze jest wiedzieć, że nie ma problemu z zagnieżdżeniem 8 lub 9.
Zamiast używać zagnieżdżonej funkcji JEŻELI, spróbuj użyć funkcji WYSZUKAJ.PIONOWO. Gdy czwarty argument funkcji WYSZUKAJ.PIONOWO zmienia się z Fałsz na Prawda, funkcja nie szuka już dokładnego dopasowania. Cóż, najpierw WYSZUKAJ.PIONOWO próbuje znaleźć dokładne dopasowanie. Ale jeśli dokładne dopasowanie nie zostanie znalezione, Excel ustawia się w wierszu tylko na mniej niż to, czego szukasz.
Rozważ poniższą tabelę. W komórce C13 program Excel będzie szukał dopasowania za 28 355 USD w tabeli. Gdy nie może znaleźć 28355, program Excel zwróci rabat powiązany z wartością, która jest po prostu mniejsza - w tym przypadku 1% rabatu za poziom 10 000 USD.
Konwertując reguły do tabeli w E13: F18, musisz zacząć od najniższego poziomu i przejść do najwyższego poziomu. Chociaż nie było to określone w zasadach, jeśli ktoś ma sprzedaż poniżej 10 000 USD, rabat wyniesie 0%. Musisz dodać to jako pierwszy wiersz w tabeli.
Uwaga
Gdy używasz „prawdziwej” wersji WYSZUKAJ.PIONOWO, twoja tabela musi być posortowana rosnąco. Wiele osób uważa, że wszystkie tabele wyszukiwania muszą zostać posortowane. Ale tabelę należy posortować tylko w celu przybliżonego dopasowania.
A jeśli twój menedżer chce całkowicie samodzielnej formuły i nie chce widzieć tabeli premiowej po prawej stronie? Po utworzeniu formuły możesz osadzić tabelę bezpośrednio w formule. Umieść formułę w trybie edycji, klikając dwukrotnie komórkę lub zaznaczając komórkę i naciskając klawisz F2. Zaznacz kursorem cały drugi argument: $ E $ 13: $ F $ 18.
Naciśnij klawisz F9. Excel osadza tabelę przeglądową jako stałą tablicową. W stałej tablicowej średnik wskazuje nowy wiersz, a przecinek wskazuje nową kolumnę. Zobacz Understanding Array Constants.
Naciśnij enter. Skopiuj formułę do innych komórek.
Możesz teraz usunąć tabelę. Ostateczny wzór przedstawiono poniżej.
Dziękuję Mike'owi Girvinowi za nauczenie mnie pasujących nawiasów. Technikę WYSZUKAJ.PIONOWO zasugerowali Danny Mac, Boriana Petrova, Andreas Thehos i @mvmcos.
