File Excel Tính Hoa Hồng – Bảng Tính Hoa Hồng Bán Hàng 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.

Đang xem: File excel tính hoa hồng

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,02Tổng doanh số hàng tháng dưới 80.000 đô la được nhân với 0,025Tổ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ự.

Tạo công thức tính tiền thưởng với hàmVLOOKUP () 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.

Xem thêm: Diện Tích Lưu Vực Sông Nin, Sông Nile Có Khởi Nguồn Thật Sự Ở Nơi Nào

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.

Xem thêm: Tự Học Excel 2003 Trong 10 Tiếng : Phần 1, Ebook Tự Học Excel 2003 Trong 10 Tiếng: Phần 1

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

Bởi vì các sự kiện quan trọng và tỷ lệ phần trăm đều là những ô đầu vào của VLOOKUP (), bạn có thể nhanh chóng cập nhật tất cả hoa hồng, bằng cách đơn giản thay đổi các giá trị trong bảng tầng, như thể hiện trong hình bên dưới. Tỷ lệ chia trả hoa hồng đã tăng lên một chút!
Bằng cách thay đổi một giá trị hoặc tất cả chúng, bạn có thể cập nhật số tiền hoa hồng ngay lập tức. Khi thay đổi các giá trị này, hãy nhớ rằng các giá trị mốc trong cột F phải theo thứ tự tăng dần và chúng ta đang sử dụngđối tượng bảngtrong Excel để tạo các phạm vi động trong công thức.