Cách đây khá lâu, tôi đã làm việc cho phó giám đốc kinh doanh của một công ty. Tôi luôn mô hình hóa một số chương trình tiền thưởng hoặc kế hoạch hoa hồng mới. Tôi đã trở nên khá quen thuộc với các kế hoạch hoa hồng với đủ loại điều kiện. Một trong những hiển thị trong mẹo này là khá thuần phục.
Cách tiếp cận thông thường là bắt đầu xây dựng công thức IF lồng nhau. Bạn luôn bắt đầu ở đầu cao hoặc cuối của dải. “Nếu doanh số trên $ 500K, thì chiết khấu là 20%; nếu không thì,… ." Đối số thứ ba của hàm IF là một hàm IF hoàn toàn mới kiểm tra cấp độ thứ hai: "Nếu doanh số bán hàng trên 250 nghìn đô la, thì chiết khấu là 15%; ngược lại,….”
Các công thức này ngày càng dài hơn khi có nhiều cấp độ hơn. Phần khó nhất của một công thức như vậy là nhớ có bao nhiêu dấu ngoặc đóng để đặt ở cuối công thức.
Nếu bạn đang sử dụng Excel 2003, công thức của bạn đã gần đến giới hạn. Với phiên bản đó, bạn không thể lồng nhiều hơn 7 hàm IF. Đó là một ngày tồi tệ khi quyền hạn bị thay đổi kế hoạch hoa hồng và bạn cần một cách để thêm hàm IF thứ tám. Ngày nay, bạn có thể lồng 64 hàm IF. Bạn đừng bao giờ lồng nhiều con như vậy, nhưng thật vui khi biết rằng không có vấn đề gì khi lồng 8 hoặc 9.
Thay vì sử dụng hàm IF lồng nhau, hãy thử sử dụng hàm VLOOKUP. Khi đối số thứ tư của hàm VLOOKUP thay đổi từ Sai thành Đúng, hàm không còn tìm kiếm đối sánh chính xác nữa. Vâng, trước tiên hàm VLOOKUP cố gắng tìm một kết quả phù hợp chính xác. Nhưng nếu không tìm thấy kết quả phù hợp chính xác, thì Excel sẽ xếp vào hàng ít hơn những gì bạn đang tìm kiếm.
Hãy xem xét bảng dưới đây. Trong ô C13, Excel sẽ tìm kiếm một kết quả phù hợp với $ 28.355 trong bảng. Khi không thể tìm thấy 28355, Excel sẽ trả lại chiết khấu được liên kết với giá trị nhỏ hơn trong trường hợp này, chiết khấu 1% cho mức $ 10K.
Khi bạn chuyển đổi các quy tắc sang bảng trong E13: F18, bạn cần bắt đầu từ cấp độ nhỏ nhất và tiến tới cấp độ cao nhất. Mặc dù nó không được đánh dấu trong các quy tắc, nhưng nếu ai đó có doanh số bán hàng dưới $ 10.000, chiết khấu sẽ là 0%. Bạn cần thêm hàng này làm hàng đầu tiên trong bảng.
Thận trọng
Khi bạn đang sử dụng phiên bản “True” của hàm VLOOKUP, bảng của bạn phải được sắp xếp tăng dần. Nhiều người tin rằng tất cả các bảng tra cứu phải được sắp xếp. Nhưng một bảng chỉ cần được sắp xếp cho một kết quả phù hợp gần đúng.
Điều gì sẽ xảy ra nếu người quản lý của bạn muốn một công thức hoàn toàn khép kín và không muốn thấy bảng tiền thưởng lệch sang bên phải? Sau khi xây dựng công thức, bạn có thể nhúng bảng ngay vào công thức. Đặt công thức ở chế độ Chỉnh sửa bằng cách bấm đúp vào ô hoặc bằng cách chọn ô và nhấn F2. Sử dụng con trỏ để chọn toàn bộ đối số thứ hai: $ E $ 13: $ F $ 18.
Nhấn phím F9. Excel nhúng bảng tra cứu dưới dạng hằng số mảng. Trong hằng số mảng, dấu chấm phẩy cho biết hàng mới và dấu phẩy cho biết cột mới. Xem phần Hiểu Hằng số Mảng.
Bấm phím Enter. Sao chép công thức xuống các ô khác.
Bây giờ bạn có thể xóa bảng. Công thức cuối cùng được hiển thị bên dưới.
Cảm ơn Mike Girvin đã dạy tôi về dấu ngoặc đơn phù hợp. Kỹ thuật VLOOKUP được đề xuất bởi Danny Mac, Boriana Petrova, Andreas Thehos và @mvmcos.
