Cách tính tiền thưởng và hoa hồng đơn giản với VLOOKUP () trong Excel

Có rất nhiều công thức khác nhau để tính phụ cấp và hoa hồng trong Excel. Với hướng dẫn này, chúng ta sẽ áp dụng một công thức vô cùng đơn giản và ngắn gọn, giúp bạn tính hoa hồng, tiền thưởng dựa trên các mốc cho trước.

Chúng ta sẽ xây dựng một hệ thống phân cấp đơn giản trong Microsoft Excel để thay đổi tỷ lệ tiền thưởng dựa trên tổng số. Đây là một ví dụ đơn giản chỉ với 3 mức thưởng, nhưng bạn có thể thay đổi cho phù hợp với mục đích sử dụng của mình.

Tạo Bảng phân cấp

Giả sử, mức chi trả tiền thưởng của bạn như sau:

• Tổng doanh số hàng tháng dưới 50.000 đô la được nhân với 0,02
• Tổng doanh số hàng tháng dưới 80.000 đô la được nhân với 0,025
• Tổng doanh số hàng tháng trên 80.000 đô la được nhân với 0,03

Mẹo ở đây là không nhập các giá trị cột mốc và tỷ lệ phần trăm vào biểu thức của bạn. Thay vào đó, chúng ta sẽ tạo các ô đầu vào và tham chiếu các ô đó. Bằng cách này, bạn có thể dễ dàng điều chỉnh trang tính khi các giá trị mốc và tỷ lệ phần trăm tăng lên (hoặc giảm xuống).

Như bạn có thể thấy trong Hình trên, tôi đã tạo một bảng cấp đơn giản phản ánh tỷ lệ từ phần trước. Áp dụng định dạng kế toán cho F1: F3 và định dạng số cho G1: G3.

Bạn có thể muốn xây dựng bảng hoa hồng trên một trang tính khác, nhưng chúng ta sẽ hiển thị tất cả trên một trang tính để bạn có thể xem giải pháp và dữ liệu ban đầu cùng một lúc.

Bước tiếp theo là thêm các biểu thức kết hợp dữ liệu và bảng phân cấp để trả lại hoa hồng hàng tháng cho tất cả nhân sự.

Xem thêm khóa học tin học văn phòng online Thủ thuật Excel cập nhật hàng tuần cho dân văn phòng để bạn có thể giải quyết công việc theo một cách thông minh và nhanh chóng để giúp bạn toả sáng nơi công sở.

Tạo công thức tính tiền thưởng với hàm VLOOKUP () trong Excel

Tạo một công thức tính tiền thưởng theo nhiều cấp bậc thường khiến bạn mệt mõi. Nhưng công thức trong ví dụ này rất đơn giảm, không có hàm IF lồng nhau cũng không có quá nhiều dấu () để bạn sai sót.

Nói một cách đơn giản, chúng ta cần một biểu thức so sánh tổng số hàng tháng với giá trị mốc gần nhất trong cột F của bảng phân cấp và trả về tỷ lệ tương ứng trong cột G. Một hàm VLOOKUP () đơn giản có thể làm điều đó. Sau đó, chúng ta cchỉ đơn giản nhân tỷ lệ đó với tổng hàng tháng.

Đầu tiên, chúng ta cần một bảng mới. Hình bên trên cho thấy một đối tượng Bảng không có dữ liệu hoặc biểu thức. Chỉ cần sao chép Bảng Sales và xóa dữ liệu. Nhập biểu thức sau vào G7 (bảng Commission) và sao chép để điền vào dải dữ liệu còn lại (G7: H15).

= VLOOKUP (C7, $ F $ 1: $ G $ 3,2) * C7

Và rồi, một cách thần kỳ, tiền hoa hồng đã được tính toán xong.

Hãy tách biểu thức ra để kiểm tra thủ công lại nhé.

Đầu tiên, hàm VLOOKUP () so sánh tổng hàng tháng trong bảng doanh số, $ 52.008 (trong C7). Bản chất của hàm này giúp ích cho chúng tôi vì nó không phải tìm một kết quả phù hợp chính xác và nó tiếp tục đánh giá danh sách cho đến khi gặp một giá trị lớn hơn giá trị ban đầu.Trong trường hợp này, đó là $ 80.000—80000> 52008.

Đối số 2, yêu cầu hàm đi qua 1 cột từ các mốc trong cột F và trả về giá trị đó, là 0,025. Phần cuối cùng của biểu thức nhân 0,025 với 52.008 đô la để trả về 1.300,20 đô la.

Bây giờ, hãy chia nhỏ nó thành một biểu thức:

= VLOOKUP (52008, $ F $ 1: $ G $ 3,2) * C7 =

= VLOOKUP (52008, {0,0.02; 50000,0.025; 80000,0.03}, 2) * C7 =

= 0,025 * 52008 =

= 1300,20

Cách thay đổi mức chi hoa hồng trong bảng Excel