☰

Как создать формулу в Excel для этой структуры оплаты?

tag-icon • tag-icon microsoft-excel worksheet-function
Как создать формулу в Excel для этой структуры оплаты?

У меня есть ячейка, в которой содержится значение A1 = 3,500,000.

Я хочу рассчитать комиссию за эти 3,5 миллиона долларов по следующей формуле:

7% на первый миллион, плюс. 5% на второй миллион, плюс. 3% на третий миллион, плюс. 2% после этого.

В идеале я бы хотел, чтобы все это происходило в одной ячейке, то есть одна формула в одной ячейке, а не целая таблица или лист, в котором это делается.

Спасибо!

решение1

Конечно, можно решить эту задачу с помощью одной формулы в одной ячейке, но если вы хотите, чтобы ваши формулы было легко читать и изменять, таблица будет более наглядным решением.

--

Решение для одной ячейки

Для решения с одной ячейкой, если значение находится в A1, эта формула рассчитает ваши многоуровневые сборы:

= A1 * 0,07 + МАКС(A1-1000000,0) * -0,02 + МАКС(A1-2000000,0) * -0,02 + МАКС(A1-3000000,0) * -0,01

Вы умножаете общую стоимость на 0,07, а затем для каждого уровня вычитаете разницу в комиссии между новым уровнем и предыдущим уровнем (есть много способов сделать это — я привел только один пример).

--

Простое решение для таблицы

Для простого табличного решения можно сделать следующее:

введите описание изображения здесь

Формулы для расчета стоимости на каждом уровне:

E2: =МИН(C2;$A$1)

E3: =МИН(C3;$A$1)-СУММА($E$2:E2)

E4: =МИН(C4;$A$1)-СУММА($E$2:E3)

E5: =МАКС($A$1-C4 ,0)

Формула расчета общей суммы гонорара:

D6: =СУММПРОИЗВ(D2:D5,E2:E5)

Эта настройка является динамической: вы можете изменять пороговые значения или комиссии, и формулы все равно будут работать.

Если вам могут понадобиться дополнительные уровни в будущем, рекомендуется использовать таблицу XML. Если вы будете использовать только 4 уровня, но пороги и сборы для этих уровней могут измениться, то простой таблицы выше будет достаточно

решение2

Для этого (а также для решения очень похожих задач по расчету налогов) можно использовать надлежащим образом структурированную таблицу соответствия.

Из вашей таблицы комиссионных

  • Столбец 1 = базовая сумма продаж
  • Столбец 2 = процент на каждом уровне
  • Столбец 3 = сумма комиссии, заработанной до этого уровня

В таблице ниже:
G1: 0
G2: =(E3-E2)*F2+G2
и заполните по мере необходимости

Затем с данными вA1

=(A1-VLOOKUP(A1,commissionTbl,1))*VLOOKUP(A1,commissionTbl,2)+VLOOKUP(A1,commissionTbl,3)

введите описание изображения здесь

Обратите внимание: если структура ваших комиссионных изменится, таблицу можно будет легко отредактировать — это гораздо проще, чем редактировать формулы.

решение3

Я хотел бы показать три возможности.

введите описание изображения здесь

  1. Рассчитайте отдельно значение уровня 1, затем уровня 2, уровня 3 и затем уровня 4, и, наконец, получите общую сумму.

Для этого проверьте формулу в ячейках:

Э2: =IF(D2<=1000000, D2*0.07, 1000000*0.07)

Ф2: =IF(D2>1000000, IF(D2<=2000000, (D21-1000000)*0.05, 1000000*0.05), 0)

Г2: =IF(D2>2000000, IF(D2<=3000000, (D2-2000000)*0.03, 1000000*0.03), 0)

Н2: =IF(D2>3000000, (D2-3000000)*0.02, 0)

И последний находится вИ2: =SUM(E2:H2)

2. Другое решение — это одноформула в I5.

Ячейка I5:

=IF(D2<=1000000, D2*0.07, IF(D2<=2000000, 1000000*0.07 + (D2-1000000)*0.05, IF(D2<=3000000, 1000000*0.07 + 1000000*0.05 + (D2-2000000)*0.03, 1000000*0.07 + 1000000*0.05 + 1000000*0.03 + (D2-3000000)*0.02)))

3. И последнее — формула I7для более поздней версии Excel, в которой используются функции LET.

Ячейка I7:

=LET( amount, D2,
        tier1, MIN(amount, 1000000),
        tier2, MAX(0, MIN(amount, 2000000) - 1000000),
        tier3, MAX(0, MIN(amount, 3000000) - 2000000),
        tier4, MAX(0, amount - 3000000),
        tier1*0.07 + tier2*0.05 + tier3*0.03 + tier4*0.02)

Примечание. При необходимости отрегулируйте ссылки на ячейки.

Связанный контент