Чи є у вас формула Excel, яка містить кілька функцій IF? Коли ви дійдете до суті із занадто великою кількістю вкладених операторів IF, вам потрібно побачити, чи все це стане простішим за допомогою простої функції VLOOKUP. Я бачив, як формули з 1000 символів спрощуються до 30-символьної формули VLOOKUP. Це легко підтримувати, коли пізніше доведеться додавати нові значення.
Давним-давно я працював у віце-президента з продажу на роботі. Я завжди моделював якусь нову бонусну програму або план комісій. Я вже досить звик вводити плани в будь-які умови. Той, що нижче, досить ручний.
Звичайним підходом є початок побудови вкладеної формули IF. Ви завжди починаєте з верхнього або нижнього кінця діапазону. “Якщо продажі перевищують 500 тис. Доларів, то знижка становить 20%; інакше … " Третій аргумент функції IF - це цілком нова функція IF, яка перевіряє другий рівень: якщо обсяг продажів перевищує 250 тис. Доларів, то знижка становить 15%; інакше, … "
Ці формули стають довшими і довшими, оскільки рівнів більше. Найскладнішою частиною такої формули є запам'ятовування кількості заключних дужок у кінці формули.
Міні-бонусна порада
Відповідність дужок
Excel перемикає різні кольори для кожного нового рівня дужок. Хоча Excel повторно використовує кольори, він використовує чорний лише для відкриваючих дужок та для відповідних закриваючих дужок. Закінчуючи формулу нижче, просто продовжуйте вводити закриваючі дужки, поки не введете чорну дужку.
Повернімося до підказки вкладеної формули
Якщо ви використовуєте Excel 2003, ваша формула вже наближається до межі. Тоді ви не могли вкласти більше 7 функцій IF. Це був потворний день, коли повноваження, які повинні були змінити план комісії, і вам потрібен був спосіб додати восьму функцію IF. Сьогодні ви можете вкласти 64 функції IF. Ви ніколи не повинні цього робити, але приємно знати, що немає проблем вкласти 8 або 9.
Замість того, щоб використовувати вкладену функцію IF, спробуйте використовувати незвичне використання для функції VLOOKUP. Коли четвертий аргумент VLOOKUP змінюється з False на True, функція більше не шукає точного збігу.
Ну, спочатку VLOOKUP намагається знайти точну відповідність. Але якщо точного збігу не знайдено, тоді Excel осідає в рядку трохи менше того, що ви шукаєте.
Розглянемо таблицю нижче. У клітинці C13 Excel шукатиме в таблиці відповідності на суму 28 355 доларів. Коли не вдається знайти 28355, Excel поверне знижку, пов’язану зі значенням, яке просто менше. У цьому випадку знижка 1% для рівня 10 тис. Доларів.
Коли ви перетворюєте правила в таблицю в E13: F18, вам потрібно починати з найменшого рівня і переходити до найвищого рівня. Хоча це не було зазначено в правилах, якщо хтось продає менше 10 000 доларів, знижка становитиме 0%. Вам потрібно додати це як перший рядок у таблиці.
Обережно
Коли ви використовуєте «Справжню» версію VLOOKUP, вашу таблицю потрібно сортувати за зростанням. Багато людей вважають, що всі таблиці пошуку потрібно сортувати. Але таблицю потрібно сортувати лише у випадку, коли виконується приблизний збіг.
Що робити, якщо ваш менеджер хоче повністю автономну формулу і не хоче бачити таблицю бонусів праворуч? Побудувавши формулу, ви можете вбудувати таблицю прямо у формулу.
Переведіть формулу в режим редагування, двічі клацнувши клітинку або виділивши комірку та натиснувши F2.
За допомогою курсору виберіть весь другий аргумент: $ E $ 13: $ F $ 18.
Натисніть клавішу F9. Excel вбудовує таблицю пошуку як константу масиву. У константі масиву крапка з комою позначає новий рядок, а кома - новий стовпець.
Натисніть клавішу Enter. Скопіюйте формулу в інші комірки.
Тепер ви можете видалити таблицю. Остаточна формула наведена нижче.
Дякую Майку Гірвіну за те, що він навчив мене відповідних дужок. Техніка VLOOKUP була запропонована Денні Маком, Боряною Петровою, Андреасом Техосом та @mvmcos.
Переглянути відео
- За допомогою багаторівневої комісійної, бонусної або дисконтної програми вам часто доводиться вкладати свої функції ПФ
- Обмеженням Excel 2003 було 7 вкладених операторів IF.
- Тепер ви можете вкласти 32, але я не думаю, що вам слід коли-небудь вкладати 32
- Коли б ви коли-небудь використовували приблизну відповідну версію VLOOKUP? Настав цей час.
- Перекладіть програму знижок у таблицю пошуку
- VLOOKUP не знаходить відповіді у більшості випадків.
- Поставивши, True в кінці скаже VLOOKUP знайти значення просто менше.
- Це єдиний раз, коли таблицю VLOOKUP потрібно сортувати.
- Не хочете, щоб таблиця VLOOKUP відходила вбік? Вбудуйте його у формулу.
- F2 для редагування формули. Виберіть таблицю пошуку. Натисніть F9. Введіть.
Стенограма відео
Дізнайтеся Excel з подкасту, серія 2030 - Замінено вкладене ПІБ на VLOOKUP!
Я буду підкастувати всю цю книгу, натисніть "i" у верхньому правому куті, щоб перейти до списку відтворення!
Привіт, ласкаво просимо назад до трансляції, я Білл Джелен. Добре, це справді спільно або з комісійною програмою, або з дисконтною програмою, або з бонусною програмою, коли ми маємо рівні, різні рівні, так? Якщо ви перевищуєте 10000 доларів, ви отримуєте знижку 1%, 50000 доларів - знижку 5%. Потрібно бути обережним, будуючи цей вкладений оператор IF, ви починаєте його у верхньому кінці, якщо ви шукаєте більше ніж, або в нижньому кінці, якщо ви шукаєте менше ніж. Отже B10> 50000, 20%, інакше інший IF, B10> 250000, 25% тощо, тощо. Це просто довга і складна формула, і якщо ваша таблиця більша, ще в Excel 2003, ви не могли вкласти більше 7 тверджень IF, тут ми майже наближаємось до межі. Ви можете перейти на 32 зараз, я не думаю, що вам слід коли-небудь переходити на 32, і навіть якщо ви кричите: "Гей, почекай,а як щодо нової функції, яка щойно вийшла в Excel 2016, випуск у лютому 2016 року, з функцією IFS? " Так, звичайно, тут менше дужок, і 87 символів замість 97 символів, це все ще безлад, давайте позбудемося цього і зробимо це за допомогою VLOOKUP!
Гаразд, ось кроки, ви приймаєте ці правила і повертаєте їх собі на голову. Перше, що ми повинні сказати, це те, що якщо у вас було $ 0 продажів, ви отримуєте знижку 0%, якщо у вас $ 10000 продажів, ви отримуєте знижку 1%, якщо у вас є $ 50000 продажів, ви отримуєте знижку 5% . $ 100000, знижка 10%, $ 250000, знижка 15%, і, нарешті, все, що перевищує $ 500000, отримує знижку 20%, добре. Зараз багато разів, кожного разу, коли я говорю про VLOOKUP, я кажу, що кожен VLOOKUP, який ви коли-небудь створювали, закінчуватиметься НЕВИЩИМ, і люди будуть говорити: "Ну, почекайте секунду, для чого там ВІРНА версія?" Це саме для цього випадку, коли ми шукаємо діапазон, тому ми шукаємо це значення, звичайний VLOOKUP, звичайно, 2, FALSE не знайде 550000, поверне # N / A!Отже, в цьому одному особливому випадку ми збираємося змінити ФАЛЬШЕ на ІСТИНУ, і це скаже Excel: "Ідіть шукайте 550000, якщо ви не можете його знайти, дайте нам значення, яке просто менше". Отже, це дає нам 20%, ось що це робить, добре? І це єдиний раз, коли вашу таблицю VLOOKUP потрібно сортувати, а всі інші випадки з FALSE, це може бути як завгодно. І так, ви можете залишити, TRUE, але я завжди поміщаю його там, лише щоб нагадати собі, що це один із тих дивних неймовірно небезпечних VLOOKUP, і я не хочу копіювати цю формулу в іншому місці. Добре, отже, ми скопіюємо та вставимо спеціальні формули, добре.гаразд? І це єдиний раз, коли вашу таблицю VLOOKUP потрібно сортувати, а всі інші випадки з FALSE, це може бути як завгодно. І так, ви можете залишити, TRUE, але я завжди поміщаю його там, щоб лише нагадати собі, що це один із тих дивних неймовірно небезпечних VLOOKUP, і я не хочу копіювати цю формулу в іншому місці. Добре, отже, ми скопіюємо та вставимо спеціальні формули, добре.гаразд? І це єдиний раз, коли вашу таблицю VLOOKUP потрібно сортувати, а всі інші випадки з FALSE, це може бути як завгодно. І так, ви можете залишити, TRUE, але я завжди поміщаю його там, лише щоб нагадати собі, що це один із тих дивних неймовірно небезпечних VLOOKUP, і я не хочу копіювати цю формулу в іншому місці. Добре, ми скопіюємо та вставимо спеціальні формули, добре.
Наступна скарга: ваш менеджер не хоче бачити таблицю пошуку, він хоче її "Просто позбудьтеся цієї таблиці пошуку". Добре, ми можемо це зробити, вбудувавши таблицю пошуку, перевірте це, чудовий фокус, який я отримав від Майка Гірвіна з ExcelIsFun. F2, щоб перевести формулу в режим редагування, а потім дуже ретельно вибрати лише діапазон для таблиці пошуку. Натискаємо F9, і вона бере цю таблицю та поміщає її в номенклатуру масивів, а потім я просто натискаю Enter так. Скопіюйте це, Вставте спеціальні формули, і тоді я можу позбутися таблиці пошуку, яка все продовжує працювати внизу. Це величезна суєта, якщо вам доведеться повернутися і відредагувати це, ви повинні по-справжньому дивитись на це з великою ясністю. Кома означає, що ми переходимо до наступного стовпця, крапка з комою означає, що ми йдемо до наступного рядка, добре,але теоретично ви могли б повернутися туди і змінити цю формулу, якщо зміниться одне з ланцюгових чисел.
Добре, отже, використання VLOOKUP замість вкладеного оператора IF - це підказка №32 на шляху до 40, “40 найбільших підказок Excel усіх часів”, ви можете отримати всю цю книгу. Клацніть на "i" у верхньому правому куті, 10 доларів за електронну книгу, 25 доларів за друковану книгу, добре. Отже, сьогодні ми намагаємось вирішити багаторівневий комісійний бонус або програму знижок. Вкладені ІФС справді поширені, будьте уважні, 7 - це все, що ви можете мати в Excel 2003, сьогодні у вас може бути 32, але у вас ніколи не повинно бути 32. Тож коли використовувати приблизну версію MATCH VLOOKUP, це все. Візьміть цю програму знижок, переверніть її догори дном, зробіть її таблицею пошуку, починаючи з найменшого числа і переходячи до найбільшого числа. VLOOKUP, звичайно, не знайде відповіді, але, змінивши VLOOKUP на TRUE в кінці, він скаже йому знайти значення просто менше,це єдиний раз, коли таблицю доводиться сортувати. Якщо ви не хочете бачити ту таблицю там, ви можете вбудувати її у формулу, використовуючи фокус Майка Гірвіна, F2, щоб відредагувати формулу, виберіть таблицю пошуку, натисніть F9, а потім Enter.
Гаразд, привіт, я хочу подякувати вам за заїзд, ми побачимось наступного разу для чергової трансляції від!
Завантажити файл
Завантажте зразок файлу тут: Podcast2030.xlsx
