Як розрахувати комісійні в excel (з прикладом)

Часто вам може знадобитися розрахувати комісійні працівника на основі їхніх продажів.

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

Ось як інтерпретувати цю таблицю:

• Якщо працівник здійснює продажі від 0 до 5000 доларів США, він отримує 0% комісійних.
• Якщо працівник продає від 5000 до 10 000 доларів США, він отримує 2% комісійних.
• Якщо працівник продає від 10 000 до 20 000 доларів США, він отримує 5% комісійних.
• Якщо працівник продає від 20 000 до 40 000 доларів США, він отримує 8% комісійних.
• Якщо продаж працівника перевищує 40 000 доларів США, він отримує 12% комісійних.

У наведеному нижче прикладі показано, як використовувати функцію VLOOKUP в 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 доларів США.

Як працює ця формула?

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

Зауважте, що функція VLOOKUP використовує такий базовий синтаксис:

VLOOKUP (пошукове_значення, масив_таблиці, номер_індексу_стовпця, [пошуковий_діапазон])

золото:

• lookup_value : Значення, яке потрібно шукати
• table_array : діапазон клітинок для пошуку
• col_index_num : номер стовпця, який містить повернуте значення
• range_lookup : TRUE = шукає приблизну відповідність, FALSE = шукає точну відповідність

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

Наприклад, коли ми шукали значення 11 000 доларів США в таблиці «Комісійні», цього точного значення не існувало в стовпці «Продажі». Тому функція VLOOKUP шукала наступне найвище значення в стовпці «Продажі», яке було менше 11 000 доларів США.

Формула визначила вартість 10 000 доларів США та повернула ставку комісії для цього значення, яка становила 5% .

Зверніть увагу, що значення в стовпці «Продажі» вже відсортовано від найменшого до найбільшого, тому функція VLOOKUP змогла правильно визначити наступне найбільше значення, яке було меншим за значення пошуку.

Якщо перший стовпець у вашому діапазоні пошуку не відсортовано, функція VLOOKUP може повернути неочікувані результати.

Додаткові ресурси

У наступних посібниках пояснюється, як виконувати інші типові завдання в Excel:

Excel: використовуйте VLOOKUP, щоб повернути кілька значень по горизонталі
Excel: використовуйте VLOOKUP, щоб повернути порожнє значення замість нуля
Excel: як використовувати VLOOKUP за датою