Налоговые ставки в Excel — Информационные технологии — Разбираем подробно
Иногда при использовании функции 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