VLOOKUP trong Excel có thể giúp bạn trích xuất thông tin từ database trong một ô ở bảng tính cục bộ. Bài viết sẽ hướng dẫn bạn cách dùng VLOOKUP mà không cần database.
Khi làm việc với database, VLOOKUP được thông qua một mã định danh duy nhất để xác định bản ghi dữ liệu muốn tìm trong database (ví dụ: mã sản phẩm hoặc ID khách hàng). Mã định danh này phải hiện trong cơ sở dữ liệu, nếu không VLOOKUP sẽ trả về lỗi.
Ở bài viết này, chúng ta sẽ kiểm tra mã định danh không cần tồn tại trong database. Nói cách khác, VLOOKUP đủ khả năng cung cấp dữ liệu bạn đang tìm kiếm.
Bài viết sẽ lấy một ví dụ thực tế - Đó là tính tiền hoa hồng dựa trên doanh số bán hàng. Chúng ta sẽ bắt đầu bằng một tình huống đơn giản, sau đó, dần phức tạp hơn cho tới khi chỉ còn giải pháp khắc phục vấn đề duy nhất là dùng VLOOKUP.
Tình huống cụ thể như sau: Một nhân viên bán hàng có doanh số hơn 30.000 USD trong năm, tương ứng hoa hồng là 30%. Nếu không, hoa hồng của họ chỉ có 20%. Lúc này, bạn có một bảng tính khá đơn giản:
Để dùng bảng tính này, nhân viên bán hàng phải nhập số liệu tại ô B1. Công thức trong ô B2 tính tỷ lệ hoa hồng chính xác họ nhận được. Con số này được dùng trong ô B3 để tính tổng hoa hồng mà nhân viên bán hàng sở hữu (phép nhân đơn giản B1 với B2).
Ô B2 chỉ chứa phần thú vị của bảng tính này - công thức quyết định tỷ lệ hoa hồng sử dụng: dưới hoặc trên mức 30.000 USD. Công thức này dùng hàm IF trong Excel:
IF(condition,value if true,value if false)
Condition là biểu thức đánh giá đúng (true) hoặc false (sai). Trong ví dụ trên, condition là biểu thức B1<B5 (có thể đọc là B1 nhỏ hơn B5 phải không? hay tổng doanh số bán hàng có thấp hơn mức quy định không?). Nếu câu trả lời là có (true), chúng ta sẽ dùng tham số value if true của hàm này, cụ thể ở đây là B6 - tỷ lệ hoa hoa hồng nếu tổng doanh số nằm dưới ngưỡng đó. Nếu câu trả lời cho câu hỏi này là “no” (false), chúng ta sẽ dùng tham số if false của hàm, cụ thể là ô B7 - tỷ lệ hoa hồng nếu tổng doanh số dưới mức quy định.
Như bạn thấy, tổng doanh số 20.000USD cho chúng ta tỷ lệ hoa hồng 20% trong ô B2. Nếu nhập giá trị 40.000USD, chúng ta sẽ có con số khác:
Giờ chúng ta sẽ làm nó phức tạp hơn. Nếu nhân viên bán hàng kiếm được hơn 40.000 USD, tỷ lệ hoa hồng của họ sẽ tăng lên 40%.
Ví dụ này đủ dễ hiểu trong thế giới thực, nhưng ở ô B2 trong công thức này sẽ phức tạp hơn. Nếu quan sát kỹ công thức, bạn sẽ thấy tham số thứ ba của hàm IF gốc (value if false) giờ hoàn toàn là hàm IF theo đúng nghĩa. Đây được gọi là hàm lồng nhau (hàm trong hàm). Nó hoàn toàn hợp lệ trong Excel nhưng khó đọc hiểu hơn.
Chúng ta sẽ không đi sâu vào chi tiết cách thức và lí dó tại sao dùng hàm này bởi bài viết tập trung hướng dẫn vào VLOOKUP, không phải Excel nói chung.
Quay trở lại ví dụ. Giả sử nhân viên bán hàng kiếm được hơn 50.000USD, họ được hưởng 50% hoa hồng và nếu kiếm được hơn 60.000USD, họ có được 60% hoa hồng không?
Giờ công thức trong ô B2 dù chính xác nhưng hầu như không thể đọc được. Không ai cần phải viết công thức tại vị trí các hàm lồng nhau tới 4 cấp. Chắc chắn có một cách đơn giản hơn. Đó là sử dụng VLOOKUP.
Bảng tính giờ sẽ được thiết kế lại một chút. Không có gì thay đổi trong số liệu, ngoài việc sắp xếp chúng theo phong cách bảng nhiều hơn:
Hãy dành một chút thời gian quan sát bảng để chắc chắn rằng Rate Table mới này chứa chính xác các mức hoa hồng kể trên.
Về mặt lí thuyết, chúng ta dùng VLOOKUP để tra tổng doanh số bán hàng của một nhân viên (từ B1) trong Rate Table và kết quả trả về là phần trăm hoa hồng tương ứng. Lưu ý rằng người bán thực sự có thể đạt được doanh số không nằm trong 5 mức trên. Ví dụ: Họ có thể kiếm được 34.988USD. Hãy xem VLOOKUP giải quyết tình huống này như thế nào nhé.
Chọn ô B2 (vị trí chúng ta muốn đặt công thức) và chèn hàm VLOOKUP từ tab Formulas:
Box Function Arguments dành cho VLOOKUP. Điền lần lượt từng đối số, bắt đầu với Lo
Tiếp theo, chỉ định bảng VLOOKUP tra cứu dữ liệu bên trong. Ở đây tất nhiên là Rate Table. Đặt con trỏ vào trường Table_array, sau đó “highlight” toàn bộ bảng tỷ lệ hoa hồng - ngoại trừ các tiêu đề:
Tiếp theo, chúng ta phải xác định cột trong bảng chứa thông tin muốn công thức trả về dữ liệu tỷ lệ hoa hồng, nằm ở cột thứ hai. Vì thế, chúng ta nhập 2 vào trường Col_index_num:
Cuối cùng, chúng ta nhập giá trị vào trường Range_lo
Lưu ý: Để dùng VLOOKUP với database, tham số cuối cùng: Range_lo
Để rõ ràng, bài viết nhập giá trị true trong trường Range_lo
Sau khi điền đầy đủ toàn bộ tham số. Click nút OK. Excel tự động xây dựng công thức cho chúng ta:
