Иногда при использовании функции VLOOKUP (ВПР) точные соответствия не нужны. Например, когда требуется вычислить налог на доход. Налоговые ставки, представленные в таблице ниже, применяются к жителям Австралии:
| Доход | Налог на этот доход |
|---|---|
| 0 — $6000 | Ноль |
| $6001 — $35000 | 15 центов с каждого $1 от суммы большей $6000 |
| $35001 — $80000 | $4350 плюс 30 центов с каждого $1 от суммы большей $35000 |
| $80001 — $180000 | $17850 плюс 38 центов с каждого $1 от суммы большей $80000 |
| $180001 и более | $55850 плюс 45 центов с каждого $1 от суммы большей $180000 |
Пример: Если доход $37000, то налог будет равен: 4350+0,3*(37000-35000) = 4350+600 = $4950.
Для автоматического расчета налога на доход выполните следующие действия:
- На листе Sheet 2, создайте диапазон, как на рисунке ниже, и назовите его «Rates».
- Мы уже знаем, что когда четвёртым аргументом функции VLOOKUP (ВПР) выступает FALSE (ЛОЖЬ), она может вернуть точное совпадение или ошибку #N/A (#Н/Д), если не сможет его найти. Если же аргументом будет TRUE (ИСТИНА), функция вернёт точное совпадение, а если не обнаружит его, то наибольшее значение, которое меньше искомого (A2). Это именно то, что нам нужно!
=VLOOKUP(A2,Rates,2,TRUE)
=ВПР(A2;Rates;2;ИСТИНА)
Пояснение: Хоть Excel и не может найти 37000 в первом столбце диапазона «Rates», тем не менее он может найти 35000 (наибольшее значение, которое меньше 37000). В результате программа возвращает 4350.
- Теперь нам остаётся решить вторую часть уравнения: +0,3*(37000-35000). Это сделать очень легко.
Чтобы узнать сумму налога, которая начисляется на каждый доллар (т.е 0,3), нужно присвоить третьему аргументу функции VLOOKUP (ВПР) значение 3. А чтобы вернуть 35000, нужно присвоить третьему аргументу функции значение 1. Формула ниже выполняет этот фокус:
=VLOOKUP(A2,Rates,2,TRUE)+VLOOKUP(A2,Rates,3,TRUE)*(A2-VLOOKUP(A2,Rates,1,TRUE))
=ВПР(A2;Rates;2;ИСТИНА)+ВПР(A2;Rates;3;ИСТИНА)*(A2-ВПР(A2;Rates;1;ИСТИНА))
Примечание: Чтобы назначить четвертому аргументу функции VLOOKUP (ВПР) значение TRUE (ИСТИНА), первый столбец таблицы должен быть отсортирован в порядке возрастания.
Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.excel-easy.com/examples/tax-rates.html
Перевела: Ольга Гелих
Правила перепечатки
Еще больше уроков по Microsoft Excel
