Налоговые ставки в 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 (ИСТИНА), первый столбец таблицы должен быть отсортирован в порядке возрастания.

28.07.2015 12:24
1415

Комментарии

Нет комментариев. Ваш будет первым!