Phải làm gì để tìm nạp dữ liệu bằng hàm VLOOKUP, khi có nhiều phạm vi tra cứu?
Ví dụ: Để tính lương công nhân, bạn cần có một bảng thông tin, ghi rõ ứng với mỗi giờ làm việc, công nhân đó sẽ nhận được bao nhiêu tiền lương. Tuy nhiên, trong trường hợp công nhân đó làm ít hơn lượng công việc tối thiểu hoặc làm tăng ca thì sao nhỉ? Chắc hẳn cần có một bảng quy định tiền lương riêng cho những trường hợp như thế đúng không?
Trong bài viết này, UniTrain sẽ chỉ cách bạn có thể tra cứu, chuyển đổi giữa nhiều phạm vi tra cứu như trên ví dụ đã nêu!
Cách dùng VLOOKUP để chuyển đổi giữa nhiều phạm vi tra cứu
Giải pháp khá đơn giản. Chúng ta chỉ cần đặt điều kiện “Nếu giờ làm việc cao hơn giờ tiêu chuẩn, thì sẽ sử dụng bảng quy định tiền lương thứ 2. Nếu không, sẽ sử dụng bảng quy định như ban đầu”. Để làm được điều này trong Excel, chúng ta sẽ sử dụng hàm IF – hàm khá nổi tiếng trong Excel.
Đặt tên các dải ô
Names Manager cho phép bạn đặt tên cho phạm vi ô cụ thể, hãy thực hiện điều này trước khi viết công thức nhé. Chúng ta sẽ tham khảo dựa trên ví dụ bên dưới.
Tải tệp ví dụ ngay tại đây: Tệp ví dụ
Bước 1: Chúng ta có thể thấy 2 phạm vi “Upto standard hours” và “Above standard hours”. Chọn ô B14 và kéo vùng chọn đến ô C21, sau đó nhấn Ctrl + F3 để chọn Names Manager
Bước 2: Nhập range1 vào trường Name
Bước 3: Lặp lại các bước tương tự cho phạm vi còn lại với range2
Lồng hàm IF vào hàm VLOOKUP
Chọn ô C4 và nhập công thức sau:
=VLOOKUP (B4, IF (B4>B1, range2, range1), 2)
Giải nghĩa cho công thức trên:
Ô B1 chứa thông tin giờ làm việc tiêu chuẩn. Trước tiên, Excel so sánh số giờ trong ô B4 (số giờ làm việc thực tế) với số giờ trong ô B1 (giờ tiêu chuẩn). Điều này sẽ giúp xác định xem range1 (phạm vi chứa tỷ lệ nếu số giờ thực tế làm việc tối đa cho phép theo giờ tiêu chuẩn) được sử dụng hoặc range2 (phạm vi chứa tỷ lệ nếu số giờ thực tế làm việc vượt quá giờ tiêu chuẩn) được sử dụng.
Nói một cách đơn giản hơn, nếu số giờ trong ô B4 nhiều hơn số giờ được đề cập trong ô B1, hãy sử dụng range2. Nếu không, hãy sử dụng range1 để đối chiếu.
Khi phạm vi đối chiếu đã được quyết định, hàm VLOOKUP bắt đầu tìm kiếm trong cột B.
Đối với công nhân đã làm việc 50 giờ, tức là đã vượt quá số giờ tiêu chuẩn, do đó Excel sẽ tự động chuyển sang range2 tức là B25:C32.
Nó sẽ bắt đầu tìm kiếm và sẽ tìm thấy kết quả phù hợp nhất tại B30. Và như công thức đã đề cập đến “2”, điều đó có nghĩa là giá trị cần tìm nạp nằm ở cột thứ 2 trong phạm vi đã chọn, do đó nó sẽ chuyển đến ô C30 và tìm nạp với tỉ lệ đó. Vậy là gần như đã hoàn tất các bước.
Cuối cùng là, nhấn đúp vào ô đã viết công thức để áp dụng tương tự cho các ô khác là hoàn thành rồi!
Xem thêm
Cách tham chiếu tuyệt đối trong Excel
Excel Data Cleaning – 5 kỹ thuật làm sạch dữ liệu trong Excel
Combo Khóa học: EXCEL FOR PROFESSIONALS