Bến Hà Trương
Bến Hà Trương
Thảo luận 0 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 3152 lượt xem

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

Dec 09 2020

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).

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

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.

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

Đầ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.

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

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

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ảng trong Excel để tạo các phạm vi động trong công thức.
Cách tính tiền thưởng và hoa hồng đơn giản với VLOOKUP () trong Excel

Bạn có ngạc nhiên vì tính đơn giản của giải pháp này không? Đừng quen theo dõi Gitiho ngay hôm nay nhé, chúng tôi còn rất nhiều thủ thuật tin học văn phòng thú vị khác muốn chia sẻ với bạn.

Cùng tham gia cộng đồng hỏi đáp về chủ đề Tin học văn phòng

Thảo luận 0 câu trả lời
Lượt xem 3152 lượt xem
Vỗ tay vỗ tay

0 Bình luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội