Налоговые ставки в 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.

Налоговые ставки в Excel

Для автоматического расчета налога на доход выполните следующие действия:

  1. На листе Sheet 2, создайте диапазон, как на рисунке ниже, и назовите его «Rates».

    Налоговые ставки в Excel

  2. Мы уже знаем, что когда четвёртым аргументом функции VLOOKUP (ВПР) выступает FALSE (ЛОЖЬ), она может вернуть точное совпадение или ошибку #N/A (#Н/Д), если не сможет его найти. Если же аргументом будет TRUE (ИСТИНА), функция вернёт точное совпадение, а если не обнаружит его, то наибольшее значение, которое меньше искомого (A2). Это именно то, что нам нужно!

    =VLOOKUP(A2,Rates,2,TRUE)
    =ВПР(A2;Rates;2;ИСТИНА)

    Налоговые ставки в Excel

    Пояснение: Хоть Excel и не может найти 37000 в первом столбце диапазона «Rates», тем не менее он может найти 35000 (наибольшее значение, которое меньше 37000). В результате программа возвращает 4350.

  3. Теперь нам остаётся решить вторую часть уравнения: +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;ИСТИНА))

    Налоговые ставки в Excel

Примечание: Чтобы назначить четвертому аргументу функции VLOOKUP (ВПР) значение TRUE (ИСТИНА), первый столбец таблицы должен быть отсортирован в порядке возрастания.

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.excel-easy.com/examples/tax-rates.html
Перевела: Ольга Гелих
Правила перепечатки
Еще больше уроков по Microsoft Excel

Добавить комментарий

восемнадцать − шестнадцать =

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: