Как рассчитать комиссию в excel (с примером)

Часто вам может потребоваться рассчитать комиссионные сотрудников на основе их продаж.

Например, вы можете использовать следующую таблицу в Excel, чтобы определить комиссию, выплачиваемую сотруднику, исходя из общего объема продаж за год:

Вот как интерпретировать эту таблицу:

• Если сотрудник совершает продажи на сумму от 0 до 5000 долларов США, он получает комиссию 0% .
• Если сотрудник совершает продажи на сумму от 5000 до 10 000 долларов США, он получает комиссию в размере 2% .
• Если сотрудник совершает продажи на сумму от 10 000 до 20 000 долларов, он получает комиссию в размере 5% .
• Если сотрудник совершает продажи на сумму от 20 000 до 40 000 долларов, он получает комиссию в размере 8% .
• Если сотрудник совершает продажи на сумму более 40 000 долларов США, он получает комиссию в размере 12% .

В следующем примере показано, как использовать функцию ВПР в Excel для определения комиссии с продаж, подлежащей выплате сотруднику, на основе его общего объема продаж.

Пример: Как рассчитать комиссию в Excel

Допустим, общий объем продаж сотрудника за определенный год составляет 11 000 долларов США.

Мы можем ввести эту сумму продаж в ячейку E1 , а затем ввести следующую формулу в ячейку E2 , чтобы определить ставку комиссии:

 =VLOOKUP( E1 , $A$2:$B$6 , 2, TRUE)

На следующем снимке экрана показано, как использовать эту формулу на практике:

Формула возвращает ставку комиссии в размере 5% , поскольку стоимость продажи в размере 11 000 долларов США находится в диапазоне от 10 000 до 20 000 долларов США.

Предположим, мы изменим стоимость продажи на 27 000 долларов США:

Теперь формула возвращает ставку комиссии в размере 8% , поскольку стоимость продажи в размере 27 000 долларов США находилась в диапазоне от 20 000 до 40 000 долларов США.

Как работает эта формула?

Вы можете использовать функцию ВПР в Excel с TRUE в качестве последнего аргумента, чтобы найти значение, находящееся в одном диапазоне, и вернуть соответствующее значение в другом диапазоне.

Обратите внимание, что функция ВПР использует следующий базовый синтаксис:

ВПР (искомое_значение, table_array, col_index_num, [range_lookup])

Золото:

• искомое_значение : значение для поиска.
• table_array : диапазон ячеек для поиска.
• col_index_num : номер столбца, который содержит возвращаемое значение.
• range_lookup : TRUE = ищет приблизительное совпадение, FALSE = ищет точное совпадение

Используя TRUE для последнего аргумента, вы можете искать значение в диапазоне.

Например, когда мы искали значение 11 000 долларов США в таблице «Комиссионные», этого точного значения не существовало в столбце «Продажи». Поэтому функция ВПР искала следующее по величине значение в столбце «Продажи», которое составляло менее 11 000 долларов США.

Формула определила стоимость в 10 000 долларов США и вернула ставку комиссии для этой суммы, которая составила 5% .

Обратите внимание, что значения в столбце «Продажи» уже отсортированы от меньшего к большему, поэтому функция ВПР смогла правильно определить следующее по величине значение, которое было меньше искомого значения.

Если первый столбец в диапазоне поиска не отсортирован, функция ВПР может вернуть неожиданные результаты.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные задачи в Excel:

Excel: используйте ВПР для возврата нескольких значений по горизонтали
Excel: используйте ВПР, чтобы вернуть пустое значение вместо нуля.
Excel: как использовать ВПР по дате