Tôi Giảm Thời Gian Xử Lý Dữ Liệu Excel Từ 2 Giờ Xuống 15 Phút Nhờ VLOOKUP

Mỗi ngày, tôi mất 2 giờ đồng hồ để tra cứu thông tin sản phẩm từ bảng giá. Sao chép mã hàng từ đơn nhập, tìm trong danh sách 500 dòng, gõ lại đơn giá và tên sản phẩm. Một cái gõ nhầm là toàn bộ báo cáo sai số liệu. Bảng lương nhân viên cũng vậy, tra cứu phụ cấp theo chức vụ mất thêm 45 phút.

Giờ đây, cùng khối lượng công việc, tôi chỉ cần 15 phút. Một công thức duy nhất tự động điền chính xác toàn bộ thông tin. Không còn gõ thủ công, không còn sai số. Hàm VLOOKUP xử lý mọi thứ trong nháy mắt.

Mục Lục

Bản chất của VLOOKUP là tra cứu dọc

VLOOKUP là viết tắt của Vertical Lookup, nghĩa là tra cứu theo chiều dọc. Hàm này tìm kiếm một giá trị trong cột đầu tiên của bảng dữ liệu, sau đó trả về thông tin tương ứng từ cột khác trong cùng hàng. Ví dụ, với mã sản phẩm SP001, hàm sẽ tìm SP001 trong cột đầu, rồi lấy giá bán từ cột thứ ba.

Cú pháp chuẩn:

=VLOOKUP(giá_trị_cần_tìm, bảng_dữ_liệu, số_thứ_tự_cột, chế_độ_tìm_kiếm)

Bốn thành phần này bắt buộc phải có đủ. Giá trị cần tìm là mã sản phẩm hoặc mã nhân viên. Bảng dữ liệu là phạm vi chứa toàn bộ thông tin tra cứu. Số thứ tự cột xác định lấy dữ liệu từ cột nào. Chế độ tìm kiếm quyết định tìm chính xác hay tương đối.

Trong thực tế, tôi dùng hàm này để tra đơn giá theo mã hàng, tra tên nhân viên theo mã số, tra xếp loại học sinh theo điểm số. Ba tình huống này chiếm 90% công việc hàng ngày. Mỗi lần áp dụng tiết kiệm từ 30 phút đến 1 giờ so với tra thủ công.

Dấu đô la cố định bảng dữ liệu khi sao chép

Sai lầm đầu tiên tôi mắc phải là không dùng dấu đô la trong bảng dữ liệu. Khi sao chép công thức từ ô D2 xuống D3, bảng tra cứu tự động dịch chuyển từ A2:C10 thành A3:C11. Kết quả là toàn bộ công thức bị sai, trả về lỗi #N/A hoặc giá trị không đúng.

Dấu đô la đóng vai trò khóa cố định vị trí. Cú pháp $A$2:$C$10 đảm bảo bảng này không thay đổi dù sao chép công thức đến bất kỳ đâu. Dấu đô la trước chữ cái khóa cột, dấu đô la trước số khóa hàng. Khi có cả hai, toàn bộ phạm vi bị cố định tuyệt đối.

Cách áp dụng đúng:

Giả sử bảng giá sản phẩm nằm trong phạm vi A2:C500. Mã sản phẩm ở cột A, tên sản phẩm ở cột B, đơn giá ở cột C. Tôi cần tra đơn giá cho danh sách 200 sản phẩm trong cột E.

XEM THÊM: Cách Dùng Hàm CHOOSEROWS Trong Excel 2024 Để Lọc Dữ Liệu Nhanh Gấp 20 Lần

Tại ô F2, nhập công thức:

=VLOOKUP(E2,$A$2:$C$500,3,0)

Ở đây, E2 là mã sản phẩm cần tra, không có dấu đô la vì cần thay đổi theo từng hàng. $A$2:$C$500 là bảng giá cố định, luôn giữ nguyên vị trí. Số 3 nghĩa là lấy dữ liệu từ cột thứ ba trong bảng, tức cột C chứa đơn giá. Số 0 yêu cầu tìm kiếm chính xác.

Sau khi nhập xong, kéo góc dưới phải của ô F2 xuống F201. Công thức tự động sao chép cho 200 hàng. E2 thay đổi thành E3, E4, E5 theo từng dòng, nhưng $A$2:$C$500 vẫn giữ nguyên. Kết quả là 200 đơn giá được điền chính xác trong 2 giây thay vì 30 phút gõ thủ công.

Phím tắt F4 giúp thêm dấu đô la nhanh chóng. Sau khi gõ A2:C500, nhấn F4 để chuyển thành $A$2:$C$500. Nhấn F4 nhiều lần để chuyển đổi giữa các chế độ: $A$2, A$2, hoặc không có dấu đô la.

Số 0 đảm bảo tìm kiếm chính xác

Tham số cuối cùng trong VLOOKUP quyết định cách thức tìm kiếm. Số 0 hoặc FALSE yêu cầu tìm kiếm chính xác. Số 1 hoặc TRUE cho phép tìm kiếm tương đối. Nếu bỏ trống, Excel mặc định dùng TRUE.

Tìm kiếm chính xác chỉ trả về kết quả khi giá trị khớp hoàn toàn. Mã sản phẩm SP001 chỉ khớp với SP001, không khớp với SP002 hoặc SP01. Đây là chế độ tôi dùng 95% thời gian vì hầu hết công việc yêu cầu tra cứu mã định danh cụ thể.

Tìm kiếm tương đối trả về giá trị gần nhất nhỏ hơn hoặc bằng giá trị cần tìm. Chế độ này yêu cầu bảng dữ liệu phải sắp xếp theo thứ tự tăng dần. Tôi chỉ dùng khi xếp loại học sinh theo điểm số hoặc tính thuế lũy tiến theo bậc thu nhập.

Ví dụ tra đơn giá chính xác:

Bảng giá trong A2:C10 gồm ba cột: mã hàng, tên hàng, đơn giá. Cột A chứa các mã: SP001, SP002, SP003 không theo thứ tự cụ thể. Để tra đơn giá cho mã SP002 nằm trong ô E2, dùng công thức:

=VLOOKUP(E2,$A$2:$C$10,3,0)

Số 0 ở cuối bảo đảm Excel chỉ trả về kết quả khi tìm thấy chính xác SP002. Nếu ô E2 chứa SP002 có khoảng trắng thừa hoặc SP02 thiếu số 0, hàm sẽ trả về lỗi #N/A thay vì trả kết quả sai. Đây là cơ chế bảo vệ quan trọng để phát hiện dữ liệu không khớp.

Ví dụ xếp loại học sinh tương đối:

Bảng điểm chuẩn trong B11:C15 có hai cột: điểm số và xếp loại. Điểm được sắp xếp từ thấp đến cao: 0 – Yếu, 3.5 – Trung bình, 5 – Khá, 6.5 – Khá, 8 – Giỏi, 9 – Xuất sắc. Để xếp loại cho điểm 7.8 trong ô D4, dùng công thức:

=VLOOKUP(D4,$B$11:$C$15,2,1)

Số 1 cho phép tìm kiếm tương đối. Excel tìm giá trị lớn nhất nhỏ hơn hoặc bằng 7.8, đó là 6.5, nên trả về xếp loại Khá. Điểm 8.5 sẽ trả về Giỏi vì 8 là giá trị gần nhất. Chế độ này giúp phân loại tự động mà không cần kiểm tra từng khoảng điểm.

Sai lầm thường gặp là quên sắp xếp bảng theo thứ tự tăng dần khi dùng TRUE. Nếu bảng điểm xếp lộn xộn, kết quả sẽ hoàn toàn sai. Tôi từng xếp loại cả lớp sai vì bảng chuẩn chưa được sắp xếp.

XEM THÊM: Cách Dùng Hàm UNIQUE Trong Excel 2021 Để Lọc Giá Trị Duy Nhất Tự Động

Lỗi #N/A xuất hiện vì bốn lý do chính

Trong 6 tháng đầu dùng VLOOKUP, tôi gặp lỗi #N/A hàng trăm lần. Lỗi này nghĩa là “not available”, tức Excel không tìm thấy giá trị cần tra. Có bốn nguyên nhân phổ biến nhất.

Nguyên nhân 1: Giá trị không tồn tại trong bảng

Mã sản phẩm SP999 không có trong danh sách từ SP001 đến SP500. Hoặc mã được gõ sai, SP002 thành SP02. Hoặc có khoảng trắng thừa ở cuối, “SP001 ” khác với “SP001”. Để phát hiện, dùng hàm TRIM loại bỏ khoảng trắng: =VLOOKUP(TRIM(E2),$A$2:$C$500,3,0).

Nguyên nhân 2: Định dạng không khớp

Cột tra cứu chứa số nhưng lưu dưới dạng văn bản. Số 100 khác với văn bản “100” trong Excel. Hoặc ngược lại, văn bản “SP001” khác với số 1. Để sửa, chọn toàn bộ cột, bấm biểu tượng lỗi xuất hiện, chọn “Chuyển đổi sang Số” hoặc “Chuyển đổi sang Văn bản” tùy trường hợp.

Nguyên nhân 3: Giá trị tra cứu không ở cột đầu tiên

VLOOKUP chỉ tìm kiếm trong cột đầu tiên của bảng dữ liệu. Nếu bảng là B2:E10 nhưng mã sản phẩm nằm ở cột C, hàm sẽ luôn trả về #N/A. Giải pháp là điều chỉnh bảng để cột tra cứu nằm bên trái nhất, hoặc dùng hàm INDEX kết hợp MATCH để tra cứu ngược.

Nguyên nhân 4: Bảng dữ liệu bị dịch chuyển khi sao chép

Quên không dùng dấu đô la khiến $A$2:$C$500 thành A3:C501, A4:C502 khi kéo xuống. Bảng dịch chuyển dần khỏi phạm vi đúng. Từ hàng 500 trở đi, công thức tìm trong vùng không có dữ liệu nên trả về #N/A. Luôn kiểm tra dấu đô la trước khi sao chép.

Để xử lý lỗi #N/A trong báo cáo, dùng hàm IFERROR bọc ngoài VLOOKUP:

=IFERROR(VLOOKUP(E2,$A$2:$C$500,3,0),"Không tìm thấy")

Khi gặp lỗi, thay vì hiển thị #N/A, ô sẽ hiển thị văn bản “Không tìm thấy” hoặc số 0 tùy nhu cầu. Điều này giúp báo cáo trông chuyên nghiệp hơn, nhưng cần cẩn thận vì có thể che giấu lỗi thực sự trong dữ liệu.

Ba kỹ thuật nâng cao giúp tiết kiệm thêm 30 phút

Kỹ thuật 1: Tra cứu trên nhiều sheet

Bảng giá nằm ở Sheet2, đơn hàng ở Sheet1. Để tra giá từ sheet khác, thêm tên sheet vào công thức:

=VLOOKUP(E2,Sheet2!$A$2:$C$500,3,0)

Dấu chấm than phân tách tên sheet và phạm vi ô. Nếu tên sheet có khoảng trắng như “Bảng Giá”, dùng dấu ngoặc đơn: ‘Bảng Giá’!$A$2:$C$500. Tôi tổ chức workbook với một sheet chính cho dữ liệu, các sheet khác dùng VLOOKUP tham chiếu ngược. Khi cập nhật giá, chỉ sửa một chỗ, toàn bộ đơn hàng tự động thay đổi.

Kỹ thuật 2: Kết hợp nhiều điều kiện

VLOOKUP chỉ tra theo một cột, nhưng thực tế thường cần tra theo hai điều kiện. Ví dụ tra đơn giá sản phẩm theo mã hàng và đơn vị tính (cái, hộp, thùng). Giải pháp là tạo cột phụ ghép hai điều kiện thành một chuỗi duy nhất.

XEM THÊM: Tôi Thay Pivot Table Bằng Hàm GROUPBY Và Tiết Kiệm 3 Giờ Mỗi Tuần

Trong cột phụ D2, nhập công thức: =A2&”-“&B2 để ghép mã hàng và đơn vị thành “SP001-Cái”, “SP001-Hộp”. Sau đó VLOOKUP tra theo cột ghép này:

=VLOOKUP(E2&"-"&F2,$D$2:$G$500,4,0)

E2 chứa mã hàng cần tra, F2 chứa đơn vị. E2&”-“&F2 tạo khóa ghép “SP001-Cái” để tra trong cột D. Cột thứ 4 trong bảng D:G là cột G chứa đơn giá. Phương pháp này cho phép tra cứu phức tạp mà không cần hàm SUMIFS hay PivotTable.

Kỹ thuật 3: Dùng Named Range thay vì phạm vi ô

Thay vì gõ $A$2:$C$500 trong mọi công thức, tạo một tên gọi cho bảng này. Chọn phạm vi A2:C500, vào ô Name Box bên trái thanh công thức, gõ tên “BangGia”, nhấn Enter. Giờ công thức chỉ cần:

=VLOOKUP(E2,BangGia,3,0)

Named Range tự động cố định tuyệt đối, không cần dấu đô la. Thêm hoặc xóa hàng trong bảng, tên vẫn cập nhật đúng phạm vi. Công thức dễ đọc, dễ kiểm tra lỗi. Tôi đặt tên cho tất cả bảng tra cứu quan trọng: BangGia, DanhSachNhanVien, BangLuong, DiemChuan. Mỗi lần gõ VLOOKUP chỉ mất 5 giây thay vì 15 giây gõ phạm vi dài.

Hiệu quả thực tế sau 6 tháng áp dụng

Trước khi biết VLOOKUP, tôi mất 2 giờ mỗi ngày cho công việc tra cứu. Đơn nhập kho 200 sản phẩm mất 90 phút tra giá và gõ tên hàng. Bảng lương 150 nhân viên mất 45 phút tra phụ cấp theo chức vụ. Báo cáo doanh thu 300 đơn hàng mất thêm 30 phút tra tên khách hàng.

Hiện tại, cùng khối lượng công việc chỉ còn 15 phút. Công thức VLOOKUP tự động điền toàn bộ thông tin trong 3 giây. 5 phút để kiểm tra kết quả, 5 phút để xử lý các trường hợp đặc biệt hoặc lỗi #N/A. 5 phút để định dạng báo cáo. Tiết kiệm 105 phút mỗi ngày, tương đương 437 giờ mỗi năm.

Tỷ lệ sai sót giảm từ 5 lỗi mỗi tuần xuống 0. Gõ thủ công luôn có khả năng gõ nhầm số, gõ thiếu chữ, hoặc tra sai hàng. VLOOKUP loại bỏ hoàn toàn yếu tố con người. Công thức chạy đúng 100% miễn là dữ liệu nguồn chính xác.

Khả năng mở rộng tăng đáng kể. Trước đây xử lý tối đa 200 dòng mỗi lần vì giới hạn thời gian. Giờ có thể xử lý 5000 dòng mà không tăng thời gian làm việc. Kéo công thức từ ô đầu xuống ô cuối, Excel tính toán tự động cho toàn bộ dữ liệu trong vài giây.

Tương thích và phiên bản cần thiết

Hàm VLOOKUP hoạt động trên mọi phiên bản Excel từ 2007 trở về sau, bao gồm Excel 2010, 2013, 2016, 2019, 2021 và Microsoft 365. Cú pháp hoàn toàn giống nhau giữa các phiên bản. Tính năng này cũng có trong Google Sheets với công thức tương tự.

Excel 365 và Excel 2021 có hàm XLOOKUP mới, mạnh hơn VLOOKUP vì không yêu cầu giá trị tra cứu ở cột đầu tiên và có nhiều tính năng nâng cao. Tuy nhiên, VLOOKUP vẫn cần thiết khi làm việc với các phiên bản Excel cũ hơn hoặc khi chia sẻ file cho người khác. Đa số công ty vẫn dùng Excel 2016 hoặc 2019, chưa chuyển sang 365, nên VLOOKUP vẫn là lựa chọn an toàn nhất.

Với công việc đơn giản như tra đơn giá, tra tên sản phẩm, hoặc tra phụ cấp, VLOOKUP đủ mạnh và nhanh hơn XLOOKUP về tốc độ gõ công thức. Chỉ chuyển sang XLOOKUP khi cần tra ngược từ phải sang trái hoặc cần nhiều tính năng phức tạp khác.