Ánh xạ dữ liệu là phần thiết yếu của Excel. Do đó, nắm vững một vài phương pháp tiện lợi để ánh xạ dữ liệu có thể tiết kiệm nhiều thời gian và cải thiện quy trình làm việc. Với suy nghĩ đó, bài viết này trình bày 4 cách hữu ích cách ánh xạ dữ liệu trong Excel VLOOKUP. Ngoài ra, chúng ta cũng thảo luận cách lấy lần xuất hiện thứ N của một giá trị và ẩn lỗi bằng hàm VLOOKUP.
Bạn có thể tải file thực hành từ liên kết bên dưới.
4 cách ánh xạ dữ liệu dùng VLOOKUP trong Excel
Trong bài viết, chúng ta kết hợp hàm VLOOKUP với MATCH, COUNTIF, INDIRECT và hàm IF để ánh xạ dữ liệu. Không chần chừ nữa, hãy bắt đầu ngay! Ở đây dùng phiên bản Microsoft Excel 365, bạn có thể dùng phiên bản khác tùy ý.
Phương pháp 1: Dùng hàm VLOOKUP để ánh xạ dữ liệu trong Excel
Bắt đầu với phương pháp rõ ràng nhất: dùng hàm VLOOKUP để ánh xạ dữ liệu. Xét tập dữ liệu trong ô B4:D14. Tập dữ liệu hiển thị danh sách ID nhân viên, Tên và Phòng ban nơi họ làm việc.
Các bước:
- Đầu tiên, đến ô G5 và nhập biểu thức dưới đây.
=VLOOKUP(G4,B5:D14,3,FALSE)
Trong công thức, ô G4 tham chiếu đến ID 1008 và phạm vi B5:D14 đại diện cho cột ID, Tên, và Phòng ban.
Phân tích công thức:
- VLOOKUP(G4,B5:D14,3,FALSE) → tìm giá trị ở cột ngoài cùng bên trái của bảng, trả về giá trị cùng hàng từ cột chỉ định. Ở đây, G4 (đối số lookup_value) được ánh xạ từ mảng B5:D14 (đối số table_array). Tiếp theo, 3 (đối số col_index_num) đại diện cho số cột của giá trị tra cứu. Cuối cùng, FALSE (đối số range_lookup) chỉ khớp chính xác giá trị tra cứu.
- Kết quả → Marketing
Kết quả sẽ như hình dưới đây.
Phương pháp 2: Ánh xạ dữ liệu với VLOOKUP và MATCH (VLOOKUP hai chiều)
Phương pháp 2 kết hợp VLOOKUP và MATCH để ánh xạ giá trị tại giao điểm của hàng và cột cụ thể. Còn gọi là VLOOKUP hai chiều. Xét tập dữ liệu Danh sách bán hàng ở ô B4:E12. Danh sách Mặt hàng và số Đơn vị bán trong Tháng 1, Tháng 2, Tháng 3.
Các bước:
- Chọn Mặt hàng và Tháng, ví dụ Television (Tivi) và March (Tháng 3).
- Đến ô H6 nhập công thức sau.
=VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE)
Ở đây, ô H4 và H5 tham chiếu đến Mặt hàng và Tháng tương ứng, B5:E5 đại diện cho tiêu đề cột.
Phân tích công thức:
- MATCH(H5, B5:E5, 0) → trả về vị trí tương đối của mục trong mảng khớp với giá trị cho trước. H5 là đối số lookup_value tham chiếu Tháng 3. B5:E5 là đối số lookup_array. 0 là đối số tùy chọn match_type chỉ khớp chính xác.
- Kết quả → 4
- VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE) → trở thành
- VLOOKUP(H4, B6:E10, 4, FALSE) → H4 (đối số lookup_value) ánh xạ từ mảng B6:E10 (đối số table_array). 4 (đối số col_index_num) là số cột. FALSE (đối số range_lookup) là khớp chính xác.
- Kết quả → 243
Kết quả như hình dưới.
Phương pháp 3: Dùng VLOOKUP và COUNTIF để ánh xạ dữ liệu
Cách khác ánh xạ dữ liệu trong Excel là kết hợp hàm COUNTIF trong VLOOKUP. Đơn giản, dễ làm, làm theo các bước.
Xét tập dữ liệu Sách bán chạy nhất ở ô B4:C11. Có tên Best Seller và Giá USD.
Các bước:
- Đến ô F5 nhập biểu thức dưới đây.
=IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0)
Trong biểu thức, phạm vi B5:C9 đại diện cho cột Sách bán chạy và Giá. Ô F4 tham chiếu Best Seller (ở đây là House of Wisdom).
Phân tích công thức:
- COUNTIF(B5:B9,F4) → đếm số ô trong phạm vi thỏa điều kiện. B5:B9 là đối số range tham chiếu Sách bán chạy. F4 là đối số criteria trả về số lần khớp.
- Kết quả → 1
- VLOOKUP(F4,B5:C9,2,TRUE) → F4 (đối số lookup_value) ánh xạ từ B5:C9 (đối số table_array). 2 (đối số col_index_num) là số cột. TRUE (đối số range_lookup) là khớp gần đúng.
- Kết quả → 25
- IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0) → trở thành
- IF(1,25,0) → kiểm tra điều kiện, trả về giá trị nếu TRUE và giá trị khác nếu FALSE. 1 là đối số logical_test khiến IF trả về 25 (đối số value_if_true) ngược lại trả 0 (đối số value_if_false).
- Kết quả → $25
Kết quả như ảnh chụp màn hình dưới.
Phương pháp 4: Ánh xạ dữ liệu dùng VLOOKUP và INDIRECT trong Excel
Cũng có thể ánh xạ dữ liệu bằng cách kết hợp INDIRECT và VLOOKUP. Xét tập dữ liệu Danh sách tạp hóa ở ô B4:I10. Hiển thị Giá của cùng Mặt hàng ở 3 thành phố trên Mỹ.
Các bước:
- Đến ô B5 > tab Data > nhấn thả xuống Data Validation.
- Hộp thoại Data Validation mở ra.
- Ở Allow chọn List > ở Source chọn B6:B10 > nhấn OK.
- Chọn B6:C10 > tab Formulas > nhấp đúp Define Name (Đặt tên).
- Cửa sổ Edit Name mở ra.
- Nhập tên phù hợp (ví dụ Boston) cho phạm vi dữ liệu > nhấn OK.
- Tương tự đặt Named Range (Phạm vi có tên) cho Atlanta.
- Tương tự đặt Named Range cho Denver.
- Đến ô C14 > tab Data > nhấn Data Validation.
- Chọn List > nhập Named Ranges như ảnh.
- Chọn Mặt hàng và Vị trí từ menu thả xuống. Ví dụ Tomato (Cà chua) và Atlanta.
- Ở ô D14 nhập biểu thức sau.
=VLOOKUP(B14,INDIRECT(C14),2,FALSE)
Ô B14 và C14 trỏ đến Mặt hàng và Vị trí.
Phân tích công thức:
- INDIRECT(C14) → trả về tham chiếu được chỉ định bởi chuỗi văn bản. C14 là đối số ref_text tham chiếu Named Range Boston.
- VLOOKUP(B14,INDIRECT(C14),2,FALSE) → B14 (đối số lookup_value) ánh xạ từ Named Range, INDIRECT(C14) là đối số table_array. 2 (đối số col_index_num) là số cột. FALSE (đối số range_lookup) là khớp chính xác.
- Kết quả → $1.2
Kết quả sau các bước như hình dưới.
Hơn nữa, phương pháp này cũng dùng được khi dữ liệu nằm trong nhiều trang tính và muốn ánh xạ từ trang cụ thể dựa trên giá trị tra cứu.
Áp dụng VLOOKUP để lấy lần xuất hiện thứ N
Giả sử có danh sách Bán hàng Văn phòng phẩm ở ô B4:D13. Muốn biết mặt hàng thứ 2 hoặc thứ 3 mà cùng một khách hàng mua. Để giải quyết, dùng hàm COUNTIF và VLOOKUP. Xem quy trình chi tiết.
Các bước:
- Chèn cột ở Cột B, đổi tiêu đề thành Helper Column (Cột hỗ trợ) > ở ô B5 nhập công thức.
=C5&COUNTIF($C$5:C5, C5)
📄 Lưu ý: Hãy chắc chắn chèn Cột hỗ trợ ở bên trái cùng của tập dữ liệu, vì mặc định VLOOKUP tra cứu từ trái sang phải.
Ô C5 tham chiếu Tên John, COUNTIF đếm số lần xuất hiện của John từ phạm vi $C$5:C5. Toán tử Ampersand (&) nối văn bản và số.
- Nhập Tên và Instance (Lần xuất hiện), ví dụ Julie và 3 > đến ô H6 nhập biểu thức.
=VLOOKUP(H4&H5, B5:E13, 3, FALSE)
Ô H4 và H5 chỉ Tên và Instance.
Kết quả như ảnh chụp màn hình dưới.
Dùng VLOOKUP và IF để ẩn lỗi #N/A
Có thể dùng hàm ISNA và VLOOKUP trong hàm IF để ẩn lỗi #N/A khi giá trị tra cứu không hợp lệ. Minh họa quy trình dưới đây.
Các bước:
- Đến ô G5 nhập biểu thức.
=IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE))
Ô G4 tham chiếu ID 1008, phạm vi B5:D14 đại diện cột ID, Tên, Phòng ban.
Phân tích công thức:
- ISNA(VLOOKUP(G4, B5:D14,3,FALSE)) → kiểm tra giá trị có phải #N/A, trả về TRUE hoặc FALSE. G4 (đối số lookup_value) ánh xạ từ B5:D14 (đối số table_array). 3 (đối số col_index_num) là số cột. FALSE (đối số range_lookup) là khớp chính xác.
- Kết quả → FALSE
- IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE)) → trở thành
- IF(FALSE, "",VLOOKUP(G4, B5:D14,3,FALSE)) → kiểm tra điều kiện. FALSE là đối số logical_test khiến IF trả về giá trị từ VLOOKUP (đối số value_if_true) ngược lại trả về rỗng "" (đối số value_if_false).
- Kết quả → HR
Kết quả như hình dưới.
Ngoài ra, nếu nhập ID không hợp lệ (ID 1012), công thức trả về giá trị rỗng thay vì lỗi #N/A như ảnh chụp dưới.
Phần thực hành
Chúng tôi cung cấp phần Practice (Thực hành) ở bên phải mỗi trang để bạn tự thực hành. Hãy tự làm nhé.
Kết luận
Hy vọng các phương pháp cách ánh xạ dữ liệu trong Excel dùng VLOOKUP sẽ giúp bạn áp dụng hiệu quả hơn trong bảng tính. Nếu có câu hỏi hoặc phản hồi, hãy để lại bình luận. Hoặc xem các bài viết khác về hàm Excel trên website này.