- Tham gia
- 4/6/06
- Bài viết
- 901
- Được thích
- 2,715
5 lỗi phổ biến khi dùng VLOOKUP và cách khắc phục
VLOOKUP là một trong những hàm phổ biến nhất hiện nay. Thậm chí độ phổ biến của nó có thể so sánh với những hàm cơ bản nhất như SUM, IF,… Nói cách khác, không biết về VLOOKUP nghĩa là không biết về Excel. Bởi vì, đối với những người dùng Excel mà nói, tìm kiếm và lấy giá trị là một công việc thường ngày của họ.
Như một thực tế, càng sử dụng nhiều bao nhiêu, bạn lại càng dễ mắc phải một số lỗi phổ biến mà đôi khi chính bạn lại không nhận ra điều đó. Và thật đáng tiếc là những lỗi không nhận thấy này lại vô cùng nguy hiểm và có thể phá hủy bảng tính của bạn.
1/ Lỗi tham chiếu bảng dò tìm
Khi bạn sử dụng VLOOKUP và quét bảng dữ liệu dò tìm, thông thường Excel sẽ tự động tạo địa chỉ tham chiếu tương đối.
Và khi bạn không để ý, bạn sẽ tiếp tục hoàn thiên công thức với địa chỉ tham chiếu tương đối. Sau đó, bạn chọn fill công thức xuống một cách vô thức, và rồi, lỗi sẽ xảy ra.
Lỗi này xảy ra là vì bạn không chuyển địa chỉ tương đối thành tuyệt đối, dẫn đến khi bạn kéo công thức xuống, địa chỉ bảng của bạn sẽ tự động dịch xuống một dòng, gây ra lỗi sai về bảng dò tìm và khiến cho giá trị cần dò tìm không còn có mặt trong bảng nữa.
Lỗi này nhìn có vẻ không nguy hiểm đối với tình huống VLOOKUP đơn thuần và giá trị là chữ. Tuy nhiên, mức độ nguy hiểm của nó sẽ được tăng lên một bậc khi bạn sử dụng số và hàm IFFERROR. Thật vậy, trong quá trình làm việc, chúng ta có xu hướng sử dụng luôn IFFERROR kết hợp với VLOOKUP bởi vì chúng ta biết trước việc VLOOKUP sẽ có lỗi NA khi không tìm thấy giá trị. Tuy nhiên, việc này kéo theo nguy hiểm ở chỗ, chính từ chỗ này, bạn sẽ nghiễm nhiên nhận được kết quả bằng 0 và không hề kiểm tra lại. Nhưng sự thật là do lỗi tham chiếu của bạn dẫn đến kết quả bằng 0 chứ không phải là giá trị đó không có. Bạn có thể xem thêm chi tiết về sự nguy hiểm này trong bài bàn về sự nguy hiểm của IFERROR và cách khắc phục.
Như vậy, trong tình huống lỗi này, bạn hãy nhớ đến phím F4 để chuyển thành tham chiếu tuyệt đối trước khi đi tiếp với các tham số kế tiếp của VLOOKUP nhé.
2/ Lỗi cột lấy giá trị không đúng
Một trong những thông số của VLOOKUP chính là đòi hỏi bạn phải đưa số cột lấy giá trị thật đúng. Chẳng hạn như hình trên, tôi đã đưa giá trị cột col_index_num là 2 theo bảng tham chiếu, để lấy giá trị tương ứng ở cột số 2. Tuy nhiên, trong trường hợp bạn có một "rừng" cột, có nghĩa là số lượng cột của bạn phải có từ chục cột trở lên, quả là một cơn ác mộng nếu sau khi quét bảng là phải ngồi đếm cột. Và đôi khi lại còn sai nữa. Như trong hình dưới đây, sau khi lỡ tay thì tôi không thể biết cột AM là cột số bao nhiêu.
Có một cách nhỏ để tránh sai, đó chính là trong quá trình bạn quét khung, hãy để ý đến con số nho nhỏ gần đó.
Bạn có thể thấy rất dễ dàng cột số 39, và chỉ việc gõ số 39 vào thôi. Rất đơn giản, không bị sai mà cũng không cần phải đếm lại nếu quên.
3/ Lỗi giá trị gần đúng
Theo mặc định, VLOOKUP sẽ trả về kết quả tương đối. Đây là một mặc định nguy hiểm vì VLOOKUP sẽ lặng lẽ trả về kết quả không chính xác khi nó không tìm thấy giá trị tra cứu của bạn.
Như hình dưới đây, nếu không tìm thấy kết quả, VLOOKUP sẽ tự trả kết quả về thay vì hiện #N/A cho bạn bởi vì bạn không thiết lập thông số cho VLOOKUP.
Bên cạnh đó, nếu bạn cố ý sử dụng tìm kiếm tương đối, bạn cũng có thể gặp một số vấn đề đến từ việc bố trí dữ liệu dẫn đến kết quả sai lệch. Bạn có thể tham khảo thêm tại bài viết "Cẩn thận với mặc định của VLOOKUP†tại đây.
Để tránh vấn đề này, lời khuyên cho bạn là luôn luôn đặt kiểu tìm kiếm cho VLOOKUP dù bạn tìm kiếm tương đối hay tìm kiếm chính xác. Ngoài ra, khi bạn muốn sử dụng tìm kiếm tương đối, hãy chắc rằng bảng của bạn đã được sắp xếp theo thứ tự.
4/ Lỗi bố trí dữ liệu sai khiến giá trị cần lấy nằm ở bên trái
Đôi khi thực tế oái oăm là, nhiều khi bạn không cố ý nhưng dữ liệu của bạn lại ngược với yêu cầu của VLOOKUP. Điều này có nghĩa là, giá trị bạn cần lấy nằm ở bên trái so với cột so sánh.
Bạn nên biết là VLOOKUP sẽ tính từ cột so sánh của bạn là cột số 1 và những cột về sau nhất quyết phải nằm bên phải và mang giá trị từ 1 trở lên. Và như thế, việc nằm bên trái khiến cho col_index_num của bạn phải mang số âm, và số âm thì chắc chắn là không được chấp nhận bởi VLOOKUP rồi.
Để giải quyết vấn đề này, bạn hãy dùng cặp hàm kết hợp INDEX + MATCH hoặc VLOOKUP + CHOOSE trong bài "Một cách VLOOKUP từ phải sang tráiâ€.
5/ VLOOKUP chỉ lấy giá trị đầu tiên
Lỗi này không hẳn là lỗi của VLOOKUP, nhưng là lỗi đến từ thói quen sử dụng VLOOKUP. Chúng ta sử dụng VLOOKUP quen đến nỗi gần như quên mất, quên không để ý những hàm khác mà trường hợp dưới đây là một trường hợp điển hình dở khóc dở cười thường gặp.
Giả sử bạn có một danh sách như sau:
Bây giờ bạn cần tìm số lượt xem của sách E001 và B001. Thế là bạn dùng ngay hàm VLOOKUP. Nhưng đợi đã, hình như kết quả có chút không ổn.
Bởi vì ngay từ lúc này, bạn cần phải dùng hàm SUMIF thì mới đúng chứ không phải dùng VLOOKUP. Vì VLOOKUP chỉ lấy giá trị đầu tiên, nên nó lấy đúng số 1000 và kết thúc, và không quan tâm đến các kết quả sau này.
Đây chính là một trong những lỗi cơ bản mà tôi biết được từ câu trả lời của rất nhiều người tôi từng hỏi qua. Và đây chính là tình huống bạn nên cẩn thận, đặc biệt với lượng dữ liệu lớn. Bạn không biết rõ dữ liệu của bạn đã được "làm sạch" hay chưa? Đã lọc hết các dòng trùng? Do đó, ví dụ trong tình huống kể trên, bạn nên, hoặc là dùng PivotTable để lọc ra giá trị duy nhất với số tổng (thường hay được các nhà phân tích sử dụng) hay chỉ đơn thuần là dùng hàm SUMIF.
Hy vọng bài viết này phần nào giúp bạn trong công việc. Chúc bạn thành công.
Một số bài viết có liên quan:
1/ VLOOKUP với Cột động
2/ Sử dụng hàm Subtotal
3/ Cách viết hàm hiệu quả
4/ Dùng hàm "Đọc số thành chữ VND và USD†với thủ thuật Name và hàm Macro4
5/ 23 điều bổ ích về VLOOKUP có thể bạn muốn biết (phần 1)
6/ 6 thói quen cá nhân khi làm việc với dữ liệu và Excel
7/ Chiêu số 23: Bật, tắt chức năng Conditional Formatting bằng 1 checkbox
8/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 1)
9/ Sử dụng hàm Subtotal
10/ Cách viết hàm hiệu quả
VLOOKUP là một trong những hàm phổ biến nhất hiện nay. Thậm chí độ phổ biến của nó có thể so sánh với những hàm cơ bản nhất như SUM, IF,… Nói cách khác, không biết về VLOOKUP nghĩa là không biết về Excel. Bởi vì, đối với những người dùng Excel mà nói, tìm kiếm và lấy giá trị là một công việc thường ngày của họ.
Như một thực tế, càng sử dụng nhiều bao nhiêu, bạn lại càng dễ mắc phải một số lỗi phổ biến mà đôi khi chính bạn lại không nhận ra điều đó. Và thật đáng tiếc là những lỗi không nhận thấy này lại vô cùng nguy hiểm và có thể phá hủy bảng tính của bạn.
1/ Lỗi tham chiếu bảng dò tìm
Khi bạn sử dụng VLOOKUP và quét bảng dữ liệu dò tìm, thông thường Excel sẽ tự động tạo địa chỉ tham chiếu tương đối.
Và khi bạn không để ý, bạn sẽ tiếp tục hoàn thiên công thức với địa chỉ tham chiếu tương đối. Sau đó, bạn chọn fill công thức xuống một cách vô thức, và rồi, lỗi sẽ xảy ra.
Lỗi này xảy ra là vì bạn không chuyển địa chỉ tương đối thành tuyệt đối, dẫn đến khi bạn kéo công thức xuống, địa chỉ bảng của bạn sẽ tự động dịch xuống một dòng, gây ra lỗi sai về bảng dò tìm và khiến cho giá trị cần dò tìm không còn có mặt trong bảng nữa.
Lỗi này nhìn có vẻ không nguy hiểm đối với tình huống VLOOKUP đơn thuần và giá trị là chữ. Tuy nhiên, mức độ nguy hiểm của nó sẽ được tăng lên một bậc khi bạn sử dụng số và hàm IFFERROR. Thật vậy, trong quá trình làm việc, chúng ta có xu hướng sử dụng luôn IFFERROR kết hợp với VLOOKUP bởi vì chúng ta biết trước việc VLOOKUP sẽ có lỗi NA khi không tìm thấy giá trị. Tuy nhiên, việc này kéo theo nguy hiểm ở chỗ, chính từ chỗ này, bạn sẽ nghiễm nhiên nhận được kết quả bằng 0 và không hề kiểm tra lại. Nhưng sự thật là do lỗi tham chiếu của bạn dẫn đến kết quả bằng 0 chứ không phải là giá trị đó không có. Bạn có thể xem thêm chi tiết về sự nguy hiểm này trong bài bàn về sự nguy hiểm của IFERROR và cách khắc phục.
Như vậy, trong tình huống lỗi này, bạn hãy nhớ đến phím F4 để chuyển thành tham chiếu tuyệt đối trước khi đi tiếp với các tham số kế tiếp của VLOOKUP nhé.
2/ Lỗi cột lấy giá trị không đúng
Một trong những thông số của VLOOKUP chính là đòi hỏi bạn phải đưa số cột lấy giá trị thật đúng. Chẳng hạn như hình trên, tôi đã đưa giá trị cột col_index_num là 2 theo bảng tham chiếu, để lấy giá trị tương ứng ở cột số 2. Tuy nhiên, trong trường hợp bạn có một "rừng" cột, có nghĩa là số lượng cột của bạn phải có từ chục cột trở lên, quả là một cơn ác mộng nếu sau khi quét bảng là phải ngồi đếm cột. Và đôi khi lại còn sai nữa. Như trong hình dưới đây, sau khi lỡ tay thì tôi không thể biết cột AM là cột số bao nhiêu.
Có một cách nhỏ để tránh sai, đó chính là trong quá trình bạn quét khung, hãy để ý đến con số nho nhỏ gần đó.
Bạn có thể thấy rất dễ dàng cột số 39, và chỉ việc gõ số 39 vào thôi. Rất đơn giản, không bị sai mà cũng không cần phải đếm lại nếu quên.
3/ Lỗi giá trị gần đúng
Theo mặc định, VLOOKUP sẽ trả về kết quả tương đối. Đây là một mặc định nguy hiểm vì VLOOKUP sẽ lặng lẽ trả về kết quả không chính xác khi nó không tìm thấy giá trị tra cứu của bạn.
Như hình dưới đây, nếu không tìm thấy kết quả, VLOOKUP sẽ tự trả kết quả về thay vì hiện #N/A cho bạn bởi vì bạn không thiết lập thông số cho VLOOKUP.
Bên cạnh đó, nếu bạn cố ý sử dụng tìm kiếm tương đối, bạn cũng có thể gặp một số vấn đề đến từ việc bố trí dữ liệu dẫn đến kết quả sai lệch. Bạn có thể tham khảo thêm tại bài viết "Cẩn thận với mặc định của VLOOKUP†tại đây.
Để tránh vấn đề này, lời khuyên cho bạn là luôn luôn đặt kiểu tìm kiếm cho VLOOKUP dù bạn tìm kiếm tương đối hay tìm kiếm chính xác. Ngoài ra, khi bạn muốn sử dụng tìm kiếm tương đối, hãy chắc rằng bảng của bạn đã được sắp xếp theo thứ tự.
4/ Lỗi bố trí dữ liệu sai khiến giá trị cần lấy nằm ở bên trái
Đôi khi thực tế oái oăm là, nhiều khi bạn không cố ý nhưng dữ liệu của bạn lại ngược với yêu cầu của VLOOKUP. Điều này có nghĩa là, giá trị bạn cần lấy nằm ở bên trái so với cột so sánh.
Bạn nên biết là VLOOKUP sẽ tính từ cột so sánh của bạn là cột số 1 và những cột về sau nhất quyết phải nằm bên phải và mang giá trị từ 1 trở lên. Và như thế, việc nằm bên trái khiến cho col_index_num của bạn phải mang số âm, và số âm thì chắc chắn là không được chấp nhận bởi VLOOKUP rồi.
Để giải quyết vấn đề này, bạn hãy dùng cặp hàm kết hợp INDEX + MATCH hoặc VLOOKUP + CHOOSE trong bài "Một cách VLOOKUP từ phải sang tráiâ€.
5/ VLOOKUP chỉ lấy giá trị đầu tiên
Lỗi này không hẳn là lỗi của VLOOKUP, nhưng là lỗi đến từ thói quen sử dụng VLOOKUP. Chúng ta sử dụng VLOOKUP quen đến nỗi gần như quên mất, quên không để ý những hàm khác mà trường hợp dưới đây là một trường hợp điển hình dở khóc dở cười thường gặp.
Giả sử bạn có một danh sách như sau:
Bây giờ bạn cần tìm số lượt xem của sách E001 và B001. Thế là bạn dùng ngay hàm VLOOKUP. Nhưng đợi đã, hình như kết quả có chút không ổn.
Bởi vì ngay từ lúc này, bạn cần phải dùng hàm SUMIF thì mới đúng chứ không phải dùng VLOOKUP. Vì VLOOKUP chỉ lấy giá trị đầu tiên, nên nó lấy đúng số 1000 và kết thúc, và không quan tâm đến các kết quả sau này.
Đây chính là một trong những lỗi cơ bản mà tôi biết được từ câu trả lời của rất nhiều người tôi từng hỏi qua. Và đây chính là tình huống bạn nên cẩn thận, đặc biệt với lượng dữ liệu lớn. Bạn không biết rõ dữ liệu của bạn đã được "làm sạch" hay chưa? Đã lọc hết các dòng trùng? Do đó, ví dụ trong tình huống kể trên, bạn nên, hoặc là dùng PivotTable để lọc ra giá trị duy nhất với số tổng (thường hay được các nhà phân tích sử dụng) hay chỉ đơn thuần là dùng hàm SUMIF.
Hy vọng bài viết này phần nào giúp bạn trong công việc. Chúc bạn thành công.
Một số bài viết có liên quan:
1/ VLOOKUP với Cột động
2/ Sử dụng hàm Subtotal
3/ Cách viết hàm hiệu quả
4/ Dùng hàm "Đọc số thành chữ VND và USD†với thủ thuật Name và hàm Macro4
5/ 23 điều bổ ích về VLOOKUP có thể bạn muốn biết (phần 1)
6/ 6 thói quen cá nhân khi làm việc với dữ liệu và Excel
7/ Chiêu số 23: Bật, tắt chức năng Conditional Formatting bằng 1 checkbox
8/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 1)
9/ Sử dụng hàm Subtotal
10/ Cách viết hàm hiệu quả
Lần chỉnh sửa cuối:
Upvote
0
