- Tham gia
- 4/6/06
- Bài viết
- 901
- Được thích
- 2,717
23 điều bổ ích về VLOOKUP có thể bạn muốn biết (phần 2)
12. Với tham chiếu tuyệt đối, VLOOKUP có thể di chuyển
Bạn cần lấy thông tin từ nhiều hơn một cột trong một bảng, hoặc bạn cần phải Copy - Paste VLOOKUP. Để tiết kiệm thời gian, hãy sử dụng tham chiếu tuyệt đối cho các giá trị tra cứu (lookup_value) và bảng dò tìm (table_array). Điều này cho phép bạn sao chép công thức, và sau đó chỉ cần thay đổi chỉ số cột để tra cứu tương tự, bạn sẽ có được một giá trị từ một cột khác.
Ví dụ, bởi vì giá trị tra cứu và bảng dò tìm là tuyệt đối, chúng ta có thể sao chép công thức trên các cột, sau đó trở lại và thay đổi các chỉ số cột khi cần thiết.
13. Name vùng làm cho hàm VLOOKUP trở nên dễ đọc và dễ di chuyển hơn
Địa chỉ của vùng tuyệt đối trông khá xấu xí và làm cho công thức trở nên khó đọc. Do đó, để làm cho công thức VLOOKUP sạch và dễ dàng đọc hơn, hãy thay thế các tham chiếu tuyệt đối bằng các Name vùng.
Trong ví dụ dữ liệu nhân viên ở trên, bạn có thể đặt tên cho ô nhập liệu B3 là "MaNV" và sau đó đặt tên cho bảng dò tìm là "data", công thức của bạn sẽ được viết như sau:
Không chỉ dễ đọc hơn, mà công thức này còn dễ di chuyển hơn nữa, vì Name vùng là tuyệt đối
14. Chèn 1 cột có thể phá vỡ công thức VLOOKUP hiện có
Nếu bạn có công thức VLOOKUP đang có trong một bảng tính, công thức có thể sẽ bị phá vỡ nếu bạn chèn thêm 1 cột trong bảng. Đó là bởi vì giá trị Chỉ số cột (column_index) không thể thay đổi tự động khi các cột được chèn hoặc xóa.
Ví dụ dưới đây, tra cứu Xếp hạng và Lượng bán hàng đã bị phá vỡ vì có cột mới được chèn vào giữa Năm và Xếp hạng. Năm vẫn được tìm ra cột Năm nằm bên trái của cột chèn vào:
Để tránh vấn đề này, bạn có thể tính toán Chỉ số cột (column_index) được nhắc tới trong 2 lời khuyên tiếp theo.
15. Sử dụng ROW hoặc COLUMN để tính toán Chỉ số cột
Nếu bạn đang bị làm phiền bởi sự thay đổi sau khi sao chép một công thức, bạn có thể sử dụng một trong hai, hoặc hàm ROW, hoặc hàm COLUMN để tạo ra các chỉ số cột năng động. Chỉ cần có dữ liệu từ các cột liên tiếp, thủ thuật này cho phép bạn thiết lập một công thức VLOOKUP, sau đó sao chép qua mà không có bất kì sự thay đổi nào
Ví dụ, với dữ liệu nhân viên bên dưới, chúng ta có thể sử dụng hàm COLUMN để tạo ra Chỉ số cột năng động. Đối với công thức đầu tiên ở ô C3, COLUMN tự chính nó sẽ trả kết quả là 3 (bởi vì cột C là cột thứ ba trong bảng tính) vì vậy chỉ cần trừ đi 1, và sao chép công thức qua các ô còn lại :
Tất cả các công thức giống hệt nhau và không yêu cầu chỉnh sửa.
Công thức được sử dụng là: =VLOOKUP(MaNV,data,COLUMN()-1,0)
16. Sử dụng VLOOKUP + MATCH cho Chỉ số cột năng động đầy đủ
Thêm 1 bước nữa bổ sung cho mẹo 15 ở trên, sử dụng MATCH để tìm kiếm vị trí của một cột trong một bảng và trả kết quả là Chỉ số cột năng động đầy đủ. Điều này đôi khi được gọi là một tra cứu hai chiều kể từ khi bạn đang tìm kiếm cả dòng và cột. Bạn có thế áp dụng thủ thuật này để tìm kiếm lượng bán hàng của 1 nhân viên bán hàng trong 1 tháng cụ thể, hoặc tìm kiếm giá cho 1 sản phẩm nào đó từ 1 nhà cung cấp cụ thể. Xem ví dụ dưới đây, giả sử bạn có doanh số bán hàng mỗi tháng, chia ra bởi nhân viên bán hàng:
VLOOKUP có thể dễ dàng tìm thấy tên người bán hàng, nhưng lại không có cách nào để xử lý Tên tháng cách tự động. Bí quyết chính là sử dụng hàm MATCH ở vị trí Chỉ số cột (column_index).
Hàm MATCH sẽ cho kết quả là số thứ tự của cột cần lấy giá trị tra cứu, gồm tất cả các cột trong bảng để "đồng bộ" với Chỉ số cột được sử dụng trong hàm VLOOKUP
=VLOOKUP(H2,data,MATCH(H3,thang,0),0)
Lưu ý : bạn nên thường xuyên sử dụng 2 cách tra cứu với hàm INDEX và hàm MATCH. 2 cách đều linh động và hiệu suất tốt hơn nếu làm việc trên các tập dữ liệu lớn.
17. VLOOKUP cho phép tìm kiếm trên ký tự đại diện
Bất cứ lúc nào bạn đang sử dụng VLOOKUP với kiểu tìm kiếm chính xác, bạn được tùy chọn sử dụng các ký tự đại diện trong các giá trị tra cứu. Những ký tự đại diện cho phép bạn làm một cuộc tìm kiếm chính xác, dù chỉ khớp 1 phần.
Excel cung cấp hai ký tự đại diện: dấu sao (*) nếu muốn khớp với 1 hoặc nhiều ký tự, và dấu hỏi (?) nếu muốn khớp với 1 ký tự. Bạn có thể gõ dấu trực tiếp vào 1 ô và xem nó như là một giá trị tra cứu với VLOOKUP.
Ví dụ, trong hình dưới đây, nhập "Kha*" vào H3, Name cho H3 là "GiaTri". VLOOKUP sẽ chạy ra kết quả là "Khanh".
Công thức trong trường hợp trên rất đơn giản: =VLOOKUP(GiaTri,data,1,0)
Nếu bạn thích, bạn có thể điều chỉnh công thức VLOOKUP để sử dụng ký tự đại diện cho thật bài bản, giống như ví dụ dưới đây, chỉ đơn giản là ghép các giá trị trong H3 với một dấu sao.
Trong trường hợp này, công thức VLOOKUP để nổi dấu sao (*) với giá trị tra cứu là: =VLOOKUP(GiaTri&"*",data,1,0)
Lưu ý: Hãy cẩn thận với các ký tự đại diện và VLOOKUP. Bạn sẽ trở nên lười biếng hơn với thủ thuật tiện lợi này cũng như cách làm này dễ dàng cung cấp cho bạn một kết quả sai, không như mong đợi.
18. Bạn có thể bắt lỗi #N/A và hiển thị một thông điệp thân thiện
Một khi bạn sử dụng VLOOKUP trong chế độ tìm kiếm chính xác, bạn bị ràng buộc để chạy lỗi #N/A mỗi khi VLOOKUP không thể tìm thấy kết quả phù hợp. Đây là 1 lỗi rất hữu ích vì VLOOKUP cho bạn biết rõ ràng rằng không có kết quả tra cứu trong bảng dò tìm. Tuy nhiên, lỗi #N/A không hề thú vị khi cần xem xét. Bạn có thể bắt lỗi này và hiển thị cái gì đó khác để thay thế.
Trong ví dụ này, "Cà phê" không tồn tại như một thức uống trong bảng, do VLOOKUP đưa ra lỗi #N/A
Công thức chuẩn để tìm kết quả chính xác trong trường hợp trên: =VLOOKUP(E6,data,2,0)
Tuy nhiên, lỗi #N/A trông thật là phát ghét, vì vậy bạn muốn hiển thị một thông điệp thân thiện hơn.
Cách dễ nhất là bọc hàm VLOOKUP trong hàm IFERROR. Hàm IFERROR cho phép bạn "bắt" bất kỳ lỗi nào và trả về một kết quả mà bạn muốn.
Để bắt lỗi #N/A trong ví dụ trên và hiển thị thông điệp "Không xác định", bạn bọc công thức ban đầu trong hàm IFERROR và thiết lập kết quả mà bạn muốn:
Nếu giá trị tra cứu được tìm thấy, không có lỗi xảy ra, hàm VLOOKUP sẽ trả về kết quả bình thường. Dưới đây là công thức:
=IFERROR(VLOOKUP(E6,data,2,0),"Không xác định")
19. Số ở dạng Text có thể gây ra lỗi tìm kiếm
Đôi khi, bảng dò tìm bạn dùng để tra cứu với hàm VLOOKUP có thể chứa các con số mà vô tình bạn nhập vào dưới dạng Text. Nếu bạn chỉ đơn giản là trích con số dưới dạng Text từ một cột trong một bảng, điều này không quan trọng. Nhưng nếu cột đầu tiên trong bảng dò tìm chứa con số được nhập dưới dạng Text, bạn sẽ nhận được kết quả là lỗi #N/A nếu giá trị tra cứu (lookup_value)không phải là dạng Text.
Trong ví dụ sau đây, số ID trong bảng Hành Tinh được nhập dưới dạng Text, đó là nguyên nhân dẫn đến VLOOKUP trả về lỗi #N/A khi giá trị tra cứu (lookup_value) ở ô H3 được gõ vào là số 3:
Để giải quyết vấn đề này, bạn cần phải chắc chắn rằng giá trị tra cứu (lookup_value) và cột đầu tiên trong bảng dò tìm cùng kiểu dữ liệu (hoặc cả hai là Number, hoặc cả hai là Text). Một cách để làm điều này là chuyển đổi các giá trị trong cột tìm kiếm thành dạng Number bằng cách thêm số 0 khi sử dụng tính năng Paste Special.
Nếu không kiểm soát được bảng dò tìm, bạn cũng có thể điều chỉnh công thức VLOOKUP để chuyển đổi các giá trị tra cứu (lookup_value) thành dạng Text, bằng cách ghép "" vào công thức như sau : =VLOOKUP(id&"",hanh_tinh,2,0)
Còn nếu bạn không chắc chắn dữ liệu trong bảng dò tìm đang ở dạng Number hay Text , vẫn còn cách để bạn đáp ứng cho cả hai lựa chọn. Hãy bọc hàm VLOOKUP trong hàm IFERROR và viết một công thức để xử lý cả hai trường hợp:
=IFERROR(VLOOKUP(id,hanh_tinh,3,0),VLOOKUP(id&"",hanh_tinh,3,0))
20. Sử dụng VLOOKUP để thay thế các hàm IF lồng nhau
Một trong những ứng dụng khá thú vị của VLOOKUP là có thể thay thế các hàm IF lồng nhau. Nếu bạn đã từng làm việc với công thức bao gồm các hàm IF lồng nhau, bạn sẽ biết chúng làm việc tốt thế nào. Tuy nhiên, 1 bất lợi của các hàm IF lồng nhau, đó là việc yêu cầu quá nhiều dấu ngoặc đơn. Bạn cũng phải cẩn thận về thứ tự của dấu ngoặc đơn khi bạn đang xây dựng công thức, sao cho không xuất hiện 1 lỗi logic nào.
Một ứng dụng cần dùng đến các hàm IF lồng nhau đó là tính điểm chữ dựa trên số điểm của các em học sinh. Trong ví dụ dưới đây, bạn sẽ thấy một công thức đã được xây dựng với các hàm IF lồng nhau để tính điểm chữ dựa trên QUY TẮC TÍNH ĐIỂM CHỮ ở bên phải bảng dữ liệu.
Công thức các hàm IF lồng nhau trong ví dụ trên như sau:
=IF(C5<64,"F",IF(C5<73,"D",IF(C5<85,"C",IF(C5<95,"B","A"))))
Công thức này hoạt động tốt, nhưng lưu ý rằng cả logic và giá trị điểm số được đưa ngay vào công thức. Nếu vì lý do nào đó, cần phải thay đổi QUY TẮC TÍNH ĐIỂM CHỮ, bạn sẽ phải cập nhật công thức cẩn thận, sau đó sao chép nó xuống toàn bộ bảng.
Ngược lại VLOOKUP có thể ghép các lớp với nhau chỉ với một công thức đơn giản. Tất cả bạn cần làm là đảm bảo bảng QUY TẮC TÍNH ĐIỂM CHỮ được thiết lập để dùng cho VLOOKUP (tức là nó phải được sắp xếp từ nhỏ đến lớn theo điểm số, và được chứa trong khung để xử lý tất cả các điểm số).
Sau khi đặt Name vùng cho bảng QUY TẮC TÍNH ĐIỂM CHỮ, công thức VLOOKUP rất đơn giản và có thể tính điểm chữ giống như khi bạn dùng các hàm IF lồng nhau:
Với Name vùng cho bảng QUY TẮC TÍNH ĐIỂM CHỮ là quy_tac, chúng ta có công thức VLOOKUP đơn giản: =VLOOKUP(C5,quy_tac,2,TRUE)
Một tính năng tốt đẹp nữa của phương pháp này là cả logic và giá trị điểm số không được đưa ngay vào công thức, mà qua các ô và bảng. Vì thế, nếu có bất cứ điều gì thay đổi, bạn chỉ cần cập nhật trực tiếp trong bảng, và các công thức VLOOKUP sẽ tự động cập nhật.
21. VLOOKUP chỉ có thể xử lý 1 điều kiện
Theo thiết kế, VLOOKUP chỉ có thể tìm thấy kết quả dựa trên 1 điều kiện duy nhất, được cung cấp như 1 giá trị tra cứu (lookup_value) để tìm trong cột đầu tiên của bảng (cột tra cứu). Điều này có nghĩa là bạn không thể dễ dàng làm những việc như tìm kiếm 1 nhân viên tên "Khanh" trong Bộ phận "kế toán", hoặc tìm kiếm 1 nhân viên dựa trên tên và họ trong 2 cột riêng biệt.
Tuy nhiên, vẫn có cách để khắc phục hạn chế này. 1 trong những cách giải quyết đó là tạo ra 1 cột phụ để móc nối các giá trị từ các cột khác với nhau và tạo ra các giá trị tra cứu rồi xử lý giống như nhiều điều kiện. Ví dụ, ở đây muốn tìm Bộ phận và Nhóm cho một nhân viên, nhưng tên và họ lại xuất hiện trong 2 cột riêng biệt. Làm thế nào để tra cứu cả 2 cùng một lúc?
Đầu tiên, thêm một cột phụ với chức năng đơn giản là móc nối tên và họ của nhân viên:
Sau đó, xây dựng công thức VLOOKUP và sử dụng bảng dò tìm mới bao gồm cột phụ này, và nhập Giá trị tra cứu là tên và họ của nhân viên:
Công thức VLOOKUP để tìm kiếm tên và họ với nhau có sử dụng cột phụ như sau: =VLOOKUP(D3&C3,data,4,0)
22. 2 VLOOKUP thì nhanh hơn 1
Nghe thì có vẻ điên rồ, nhưng khi bạn có 1 tập hợp lớn các dữ liệu và cần phải có 1 kết quả chính xác, bạn nên nghĩ đến điều này. VLOOKUP sẽ được tăng tốc độ khi bạn thêm 1 VLOOKUP nữa vào công thức.
Hãy tưởng tượng, bạn có rất nhiều dữ liệu theo đơn đặt hàng, hơn 10.000 đơn và bạn đang sử dụng VLOOKUP để tra cứu tổng số đơn đặt hàng dựa trên Mã đơn hàng. Giả sử, công thức của bạn như thế này: =VLOOKUP(ma_donhang, data, 5, FALSE)
Chữ FALSE ở cuối công thức để buộc VLOOKUP tìm kiếm chính xác. Tại sao phải tìm kiếm chính xác? Bởi vì đây là dịp để một vài số đơn hàng sẽ không được tìm thấy. Trong trường hợp này, kiểu tìm kiếm chính xác sẽ làm cho VLOOKUP trả kết quả là lỗi #N/A
Vấn đề là kiểu tìm kiếm chính xác sẽ làm VLOOKUP chạy rất chậm, vì Excel phải chạy tuyến tính qua tất cả các giá trị cho đến khi nó tìm thấy kết quả tương ứng. Ngược lại, kiểu tìm kiếm tương đối thì sẽ nhanh như chớp vì Excel có thể làm những gì gọi là tìm kiếm nhị phân. Vấn đề xảy ra đối với kiểu tìm kiếm tương đối là VLOOKUP có thể trả lại kết quả sai khi một giá trị không được tìm thấy. Tệ hơn nữa, kết quả có thể trông hoàn toàn bình thường, do đó, rất khó để phát hiện sai sót.
Giải pháp ở đây là sử dụng VLOOKUP 2 lần, cả 2 lần tìm kiếm tương đối. VLOOKUP đầu tiên chỉ đơn giản là kiểm tra rằng giá trị thực sự tồn tại. VLOOKUP khác đang chạy (cũng trong kiểu tìm kiếm tương đối) để lấy dữ liệu mà bạn muốn. Nếu không, bạn có thể trả lại bất kỳ giá trị mà bạn muốn, ví dụ như "Không tìm thấy".
Công thức cuối cùng sẽ như thế này : =IF(VLOOKUP(ma_donhang, data,1,TRUE)=ma_donhang, VLOOKUP(ma_donhang, data,5,TRUE), "Không tìm thấy")
23. Đồng INDEX, đồng MATCH, vượt VLOOKUP và hơn thế nữa
Nếu tham gia các diễn đàn Excel, bạn sẽ bị cuốn vào cuộc tranh luận giữa hàm VLOOKUP với hàm INDEX + MATCH. Đề tài này chưa bao giờ hết hot. Đại ý là: INDEX + MATCH có thể làm tất cả mọi thứ mà VLOOKUP (và cả HLOOKUP) có thể làm, với nhiều sức mạnh hơn, tốc độ nhanh hơn và xử lý linh hoạt hơn, hơi phức tạp hơn chút xíu. Vì vậy, những người ủng hộ INDEX + MATCH sẽ luôn luôn tranh luận vì điều này. Bạn cũng có thể bắt đầu học INDEX + MATCH từ bây giờ, vì nó cung cấp cho bạn 1 bộ công cụ tốt hơn. Các lập luận chống lại INDEX + MATCH cho rằng, nó đòi hỏi sử dụng kết hợp 2 hàm thay vì 1, do đó, sự phức tạp tăng lên cho người dùng (nhất là những người mới) trong việc tìm hiểu.
Nếu bạn sử dụng Excel thường xuyên, bạn sẽ muốn tìm hiểu làm thế nào để sử dụng INDEX + MATCH. Đó là 1 sự kết hợp rất mạnh. Điều đó không có nghĩa là bạn hãy bỏ qua VLOOKUP. Bạn sẽ cần đến VLOOKUP ở khắp nơi, nhất là những lúc bạn phải nhận lại dữ liệu từ người khác. Trong những tình huống đơn giản, VLOOKUP sẽ hoàn thành công việc tốt hơn mà không có gì phải bàn cãi.
Chúc bạn vui với bài viết này.
Nguyễn Bảo Khanh.
Một số bài viết có liên quan:
1/ 23 điều bổ ích về VLOOKUP có thể bạn muốn biết (phần 1)
2/ 6 thói quen cá nhân khi làm việc với dữ liệu và Excel
3/ Chiêu số 23: Bật, tắt chức năng Conditional Formatting bằng 1 checkbox
4/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 1)
5/ Sử dụng hàm Subtotal
6/ Cách viết hàm hiệu quả
7/ 5 phương pháp học Excel cơ bản hiệu quả nhất
8/ Ebook: Dữ liệu & Báo cáo trong Excel 2013
9/ Ebook: Công thức và hàm Excel 97-2013
10/ Ebook: "Một số chuyên đề Excel mừng sinh nhật GPE lần 7"
12. Với tham chiếu tuyệt đối, VLOOKUP có thể di chuyển
Bạn cần lấy thông tin từ nhiều hơn một cột trong một bảng, hoặc bạn cần phải Copy - Paste VLOOKUP. Để tiết kiệm thời gian, hãy sử dụng tham chiếu tuyệt đối cho các giá trị tra cứu (lookup_value) và bảng dò tìm (table_array). Điều này cho phép bạn sao chép công thức, và sau đó chỉ cần thay đổi chỉ số cột để tra cứu tương tự, bạn sẽ có được một giá trị từ một cột khác.
Ví dụ, bởi vì giá trị tra cứu và bảng dò tìm là tuyệt đối, chúng ta có thể sao chép công thức trên các cột, sau đó trở lại và thay đổi các chỉ số cột khi cần thiết.
13. Name vùng làm cho hàm VLOOKUP trở nên dễ đọc và dễ di chuyển hơn
Địa chỉ của vùng tuyệt đối trông khá xấu xí và làm cho công thức trở nên khó đọc. Do đó, để làm cho công thức VLOOKUP sạch và dễ dàng đọc hơn, hãy thay thế các tham chiếu tuyệt đối bằng các Name vùng.
Trong ví dụ dữ liệu nhân viên ở trên, bạn có thể đặt tên cho ô nhập liệu B3 là "MaNV" và sau đó đặt tên cho bảng dò tìm là "data", công thức của bạn sẽ được viết như sau:
Không chỉ dễ đọc hơn, mà công thức này còn dễ di chuyển hơn nữa, vì Name vùng là tuyệt đối
14. Chèn 1 cột có thể phá vỡ công thức VLOOKUP hiện có
Nếu bạn có công thức VLOOKUP đang có trong một bảng tính, công thức có thể sẽ bị phá vỡ nếu bạn chèn thêm 1 cột trong bảng. Đó là bởi vì giá trị Chỉ số cột (column_index) không thể thay đổi tự động khi các cột được chèn hoặc xóa.
Ví dụ dưới đây, tra cứu Xếp hạng và Lượng bán hàng đã bị phá vỡ vì có cột mới được chèn vào giữa Năm và Xếp hạng. Năm vẫn được tìm ra cột Năm nằm bên trái của cột chèn vào:
Để tránh vấn đề này, bạn có thể tính toán Chỉ số cột (column_index) được nhắc tới trong 2 lời khuyên tiếp theo.
15. Sử dụng ROW hoặc COLUMN để tính toán Chỉ số cột
Nếu bạn đang bị làm phiền bởi sự thay đổi sau khi sao chép một công thức, bạn có thể sử dụng một trong hai, hoặc hàm ROW, hoặc hàm COLUMN để tạo ra các chỉ số cột năng động. Chỉ cần có dữ liệu từ các cột liên tiếp, thủ thuật này cho phép bạn thiết lập một công thức VLOOKUP, sau đó sao chép qua mà không có bất kì sự thay đổi nào
Ví dụ, với dữ liệu nhân viên bên dưới, chúng ta có thể sử dụng hàm COLUMN để tạo ra Chỉ số cột năng động. Đối với công thức đầu tiên ở ô C3, COLUMN tự chính nó sẽ trả kết quả là 3 (bởi vì cột C là cột thứ ba trong bảng tính) vì vậy chỉ cần trừ đi 1, và sao chép công thức qua các ô còn lại :
Tất cả các công thức giống hệt nhau và không yêu cầu chỉnh sửa.
Công thức được sử dụng là: =VLOOKUP(MaNV,data,COLUMN()-1,0)
16. Sử dụng VLOOKUP + MATCH cho Chỉ số cột năng động đầy đủ
Thêm 1 bước nữa bổ sung cho mẹo 15 ở trên, sử dụng MATCH để tìm kiếm vị trí của một cột trong một bảng và trả kết quả là Chỉ số cột năng động đầy đủ. Điều này đôi khi được gọi là một tra cứu hai chiều kể từ khi bạn đang tìm kiếm cả dòng và cột. Bạn có thế áp dụng thủ thuật này để tìm kiếm lượng bán hàng của 1 nhân viên bán hàng trong 1 tháng cụ thể, hoặc tìm kiếm giá cho 1 sản phẩm nào đó từ 1 nhà cung cấp cụ thể. Xem ví dụ dưới đây, giả sử bạn có doanh số bán hàng mỗi tháng, chia ra bởi nhân viên bán hàng:
VLOOKUP có thể dễ dàng tìm thấy tên người bán hàng, nhưng lại không có cách nào để xử lý Tên tháng cách tự động. Bí quyết chính là sử dụng hàm MATCH ở vị trí Chỉ số cột (column_index).
Hàm MATCH sẽ cho kết quả là số thứ tự của cột cần lấy giá trị tra cứu, gồm tất cả các cột trong bảng để "đồng bộ" với Chỉ số cột được sử dụng trong hàm VLOOKUP
=VLOOKUP(H2,data,MATCH(H3,thang,0),0)
Lưu ý : bạn nên thường xuyên sử dụng 2 cách tra cứu với hàm INDEX và hàm MATCH. 2 cách đều linh động và hiệu suất tốt hơn nếu làm việc trên các tập dữ liệu lớn.
17. VLOOKUP cho phép tìm kiếm trên ký tự đại diện
Bất cứ lúc nào bạn đang sử dụng VLOOKUP với kiểu tìm kiếm chính xác, bạn được tùy chọn sử dụng các ký tự đại diện trong các giá trị tra cứu. Những ký tự đại diện cho phép bạn làm một cuộc tìm kiếm chính xác, dù chỉ khớp 1 phần.
Excel cung cấp hai ký tự đại diện: dấu sao (*) nếu muốn khớp với 1 hoặc nhiều ký tự, và dấu hỏi (?) nếu muốn khớp với 1 ký tự. Bạn có thể gõ dấu trực tiếp vào 1 ô và xem nó như là một giá trị tra cứu với VLOOKUP.
Ví dụ, trong hình dưới đây, nhập "Kha*" vào H3, Name cho H3 là "GiaTri". VLOOKUP sẽ chạy ra kết quả là "Khanh".
Công thức trong trường hợp trên rất đơn giản: =VLOOKUP(GiaTri,data,1,0)
Nếu bạn thích, bạn có thể điều chỉnh công thức VLOOKUP để sử dụng ký tự đại diện cho thật bài bản, giống như ví dụ dưới đây, chỉ đơn giản là ghép các giá trị trong H3 với một dấu sao.
Trong trường hợp này, công thức VLOOKUP để nổi dấu sao (*) với giá trị tra cứu là: =VLOOKUP(GiaTri&"*",data,1,0)
Lưu ý: Hãy cẩn thận với các ký tự đại diện và VLOOKUP. Bạn sẽ trở nên lười biếng hơn với thủ thuật tiện lợi này cũng như cách làm này dễ dàng cung cấp cho bạn một kết quả sai, không như mong đợi.
18. Bạn có thể bắt lỗi #N/A và hiển thị một thông điệp thân thiện
Một khi bạn sử dụng VLOOKUP trong chế độ tìm kiếm chính xác, bạn bị ràng buộc để chạy lỗi #N/A mỗi khi VLOOKUP không thể tìm thấy kết quả phù hợp. Đây là 1 lỗi rất hữu ích vì VLOOKUP cho bạn biết rõ ràng rằng không có kết quả tra cứu trong bảng dò tìm. Tuy nhiên, lỗi #N/A không hề thú vị khi cần xem xét. Bạn có thể bắt lỗi này và hiển thị cái gì đó khác để thay thế.
Trong ví dụ này, "Cà phê" không tồn tại như một thức uống trong bảng, do VLOOKUP đưa ra lỗi #N/A
Công thức chuẩn để tìm kết quả chính xác trong trường hợp trên: =VLOOKUP(E6,data,2,0)
Tuy nhiên, lỗi #N/A trông thật là phát ghét, vì vậy bạn muốn hiển thị một thông điệp thân thiện hơn.
Cách dễ nhất là bọc hàm VLOOKUP trong hàm IFERROR. Hàm IFERROR cho phép bạn "bắt" bất kỳ lỗi nào và trả về một kết quả mà bạn muốn.
Để bắt lỗi #N/A trong ví dụ trên và hiển thị thông điệp "Không xác định", bạn bọc công thức ban đầu trong hàm IFERROR và thiết lập kết quả mà bạn muốn:
Nếu giá trị tra cứu được tìm thấy, không có lỗi xảy ra, hàm VLOOKUP sẽ trả về kết quả bình thường. Dưới đây là công thức:
=IFERROR(VLOOKUP(E6,data,2,0),"Không xác định")
19. Số ở dạng Text có thể gây ra lỗi tìm kiếm
Đôi khi, bảng dò tìm bạn dùng để tra cứu với hàm VLOOKUP có thể chứa các con số mà vô tình bạn nhập vào dưới dạng Text. Nếu bạn chỉ đơn giản là trích con số dưới dạng Text từ một cột trong một bảng, điều này không quan trọng. Nhưng nếu cột đầu tiên trong bảng dò tìm chứa con số được nhập dưới dạng Text, bạn sẽ nhận được kết quả là lỗi #N/A nếu giá trị tra cứu (lookup_value)không phải là dạng Text.
Trong ví dụ sau đây, số ID trong bảng Hành Tinh được nhập dưới dạng Text, đó là nguyên nhân dẫn đến VLOOKUP trả về lỗi #N/A khi giá trị tra cứu (lookup_value) ở ô H3 được gõ vào là số 3:
Để giải quyết vấn đề này, bạn cần phải chắc chắn rằng giá trị tra cứu (lookup_value) và cột đầu tiên trong bảng dò tìm cùng kiểu dữ liệu (hoặc cả hai là Number, hoặc cả hai là Text). Một cách để làm điều này là chuyển đổi các giá trị trong cột tìm kiếm thành dạng Number bằng cách thêm số 0 khi sử dụng tính năng Paste Special.
Nếu không kiểm soát được bảng dò tìm, bạn cũng có thể điều chỉnh công thức VLOOKUP để chuyển đổi các giá trị tra cứu (lookup_value) thành dạng Text, bằng cách ghép "" vào công thức như sau : =VLOOKUP(id&"",hanh_tinh,2,0)
Còn nếu bạn không chắc chắn dữ liệu trong bảng dò tìm đang ở dạng Number hay Text , vẫn còn cách để bạn đáp ứng cho cả hai lựa chọn. Hãy bọc hàm VLOOKUP trong hàm IFERROR và viết một công thức để xử lý cả hai trường hợp:
=IFERROR(VLOOKUP(id,hanh_tinh,3,0),VLOOKUP(id&"",hanh_tinh,3,0))
20. Sử dụng VLOOKUP để thay thế các hàm IF lồng nhau
Một trong những ứng dụng khá thú vị của VLOOKUP là có thể thay thế các hàm IF lồng nhau. Nếu bạn đã từng làm việc với công thức bao gồm các hàm IF lồng nhau, bạn sẽ biết chúng làm việc tốt thế nào. Tuy nhiên, 1 bất lợi của các hàm IF lồng nhau, đó là việc yêu cầu quá nhiều dấu ngoặc đơn. Bạn cũng phải cẩn thận về thứ tự của dấu ngoặc đơn khi bạn đang xây dựng công thức, sao cho không xuất hiện 1 lỗi logic nào.
Một ứng dụng cần dùng đến các hàm IF lồng nhau đó là tính điểm chữ dựa trên số điểm của các em học sinh. Trong ví dụ dưới đây, bạn sẽ thấy một công thức đã được xây dựng với các hàm IF lồng nhau để tính điểm chữ dựa trên QUY TẮC TÍNH ĐIỂM CHỮ ở bên phải bảng dữ liệu.
Công thức các hàm IF lồng nhau trong ví dụ trên như sau:
=IF(C5<64,"F",IF(C5<73,"D",IF(C5<85,"C",IF(C5<95,"B","A"))))
Công thức này hoạt động tốt, nhưng lưu ý rằng cả logic và giá trị điểm số được đưa ngay vào công thức. Nếu vì lý do nào đó, cần phải thay đổi QUY TẮC TÍNH ĐIỂM CHỮ, bạn sẽ phải cập nhật công thức cẩn thận, sau đó sao chép nó xuống toàn bộ bảng.
Ngược lại VLOOKUP có thể ghép các lớp với nhau chỉ với một công thức đơn giản. Tất cả bạn cần làm là đảm bảo bảng QUY TẮC TÍNH ĐIỂM CHỮ được thiết lập để dùng cho VLOOKUP (tức là nó phải được sắp xếp từ nhỏ đến lớn theo điểm số, và được chứa trong khung để xử lý tất cả các điểm số).
Sau khi đặt Name vùng cho bảng QUY TẮC TÍNH ĐIỂM CHỮ, công thức VLOOKUP rất đơn giản và có thể tính điểm chữ giống như khi bạn dùng các hàm IF lồng nhau:
Với Name vùng cho bảng QUY TẮC TÍNH ĐIỂM CHỮ là quy_tac, chúng ta có công thức VLOOKUP đơn giản: =VLOOKUP(C5,quy_tac,2,TRUE)
Một tính năng tốt đẹp nữa của phương pháp này là cả logic và giá trị điểm số không được đưa ngay vào công thức, mà qua các ô và bảng. Vì thế, nếu có bất cứ điều gì thay đổi, bạn chỉ cần cập nhật trực tiếp trong bảng, và các công thức VLOOKUP sẽ tự động cập nhật.
21. VLOOKUP chỉ có thể xử lý 1 điều kiện
Theo thiết kế, VLOOKUP chỉ có thể tìm thấy kết quả dựa trên 1 điều kiện duy nhất, được cung cấp như 1 giá trị tra cứu (lookup_value) để tìm trong cột đầu tiên của bảng (cột tra cứu). Điều này có nghĩa là bạn không thể dễ dàng làm những việc như tìm kiếm 1 nhân viên tên "Khanh" trong Bộ phận "kế toán", hoặc tìm kiếm 1 nhân viên dựa trên tên và họ trong 2 cột riêng biệt.
Tuy nhiên, vẫn có cách để khắc phục hạn chế này. 1 trong những cách giải quyết đó là tạo ra 1 cột phụ để móc nối các giá trị từ các cột khác với nhau và tạo ra các giá trị tra cứu rồi xử lý giống như nhiều điều kiện. Ví dụ, ở đây muốn tìm Bộ phận và Nhóm cho một nhân viên, nhưng tên và họ lại xuất hiện trong 2 cột riêng biệt. Làm thế nào để tra cứu cả 2 cùng một lúc?
Đầu tiên, thêm một cột phụ với chức năng đơn giản là móc nối tên và họ của nhân viên:
Sau đó, xây dựng công thức VLOOKUP và sử dụng bảng dò tìm mới bao gồm cột phụ này, và nhập Giá trị tra cứu là tên và họ của nhân viên:
Công thức VLOOKUP để tìm kiếm tên và họ với nhau có sử dụng cột phụ như sau: =VLOOKUP(D3&C3,data,4,0)
22. 2 VLOOKUP thì nhanh hơn 1
Nghe thì có vẻ điên rồ, nhưng khi bạn có 1 tập hợp lớn các dữ liệu và cần phải có 1 kết quả chính xác, bạn nên nghĩ đến điều này. VLOOKUP sẽ được tăng tốc độ khi bạn thêm 1 VLOOKUP nữa vào công thức.
Hãy tưởng tượng, bạn có rất nhiều dữ liệu theo đơn đặt hàng, hơn 10.000 đơn và bạn đang sử dụng VLOOKUP để tra cứu tổng số đơn đặt hàng dựa trên Mã đơn hàng. Giả sử, công thức của bạn như thế này: =VLOOKUP(ma_donhang, data, 5, FALSE)
Chữ FALSE ở cuối công thức để buộc VLOOKUP tìm kiếm chính xác. Tại sao phải tìm kiếm chính xác? Bởi vì đây là dịp để một vài số đơn hàng sẽ không được tìm thấy. Trong trường hợp này, kiểu tìm kiếm chính xác sẽ làm cho VLOOKUP trả kết quả là lỗi #N/A
Vấn đề là kiểu tìm kiếm chính xác sẽ làm VLOOKUP chạy rất chậm, vì Excel phải chạy tuyến tính qua tất cả các giá trị cho đến khi nó tìm thấy kết quả tương ứng. Ngược lại, kiểu tìm kiếm tương đối thì sẽ nhanh như chớp vì Excel có thể làm những gì gọi là tìm kiếm nhị phân. Vấn đề xảy ra đối với kiểu tìm kiếm tương đối là VLOOKUP có thể trả lại kết quả sai khi một giá trị không được tìm thấy. Tệ hơn nữa, kết quả có thể trông hoàn toàn bình thường, do đó, rất khó để phát hiện sai sót.
Giải pháp ở đây là sử dụng VLOOKUP 2 lần, cả 2 lần tìm kiếm tương đối. VLOOKUP đầu tiên chỉ đơn giản là kiểm tra rằng giá trị thực sự tồn tại. VLOOKUP khác đang chạy (cũng trong kiểu tìm kiếm tương đối) để lấy dữ liệu mà bạn muốn. Nếu không, bạn có thể trả lại bất kỳ giá trị mà bạn muốn, ví dụ như "Không tìm thấy".
Công thức cuối cùng sẽ như thế này : =IF(VLOOKUP(ma_donhang, data,1,TRUE)=ma_donhang, VLOOKUP(ma_donhang, data,5,TRUE), "Không tìm thấy")
23. Đồng INDEX, đồng MATCH, vượt VLOOKUP và hơn thế nữa
Nếu tham gia các diễn đàn Excel, bạn sẽ bị cuốn vào cuộc tranh luận giữa hàm VLOOKUP với hàm INDEX + MATCH. Đề tài này chưa bao giờ hết hot. Đại ý là: INDEX + MATCH có thể làm tất cả mọi thứ mà VLOOKUP (và cả HLOOKUP) có thể làm, với nhiều sức mạnh hơn, tốc độ nhanh hơn và xử lý linh hoạt hơn, hơi phức tạp hơn chút xíu. Vì vậy, những người ủng hộ INDEX + MATCH sẽ luôn luôn tranh luận vì điều này. Bạn cũng có thể bắt đầu học INDEX + MATCH từ bây giờ, vì nó cung cấp cho bạn 1 bộ công cụ tốt hơn. Các lập luận chống lại INDEX + MATCH cho rằng, nó đòi hỏi sử dụng kết hợp 2 hàm thay vì 1, do đó, sự phức tạp tăng lên cho người dùng (nhất là những người mới) trong việc tìm hiểu.
Nếu bạn sử dụng Excel thường xuyên, bạn sẽ muốn tìm hiểu làm thế nào để sử dụng INDEX + MATCH. Đó là 1 sự kết hợp rất mạnh. Điều đó không có nghĩa là bạn hãy bỏ qua VLOOKUP. Bạn sẽ cần đến VLOOKUP ở khắp nơi, nhất là những lúc bạn phải nhận lại dữ liệu từ người khác. Trong những tình huống đơn giản, VLOOKUP sẽ hoàn thành công việc tốt hơn mà không có gì phải bàn cãi.
Chúc bạn vui với bài viết này.
Nguyễn Bảo Khanh.
Một số bài viết có liên quan:
1/ 23 điều bổ ích về VLOOKUP có thể bạn muốn biết (phần 1)
2/ 6 thói quen cá nhân khi làm việc với dữ liệu và Excel
3/ Chiêu số 23: Bật, tắt chức năng Conditional Formatting bằng 1 checkbox
4/ 29 cách tiết kiệm thời gian với các công thức Excel (phần 1)
5/ Sử dụng hàm Subtotal
6/ Cách viết hàm hiệu quả
7/ 5 phương pháp học Excel cơ bản hiệu quả nhất
8/ Ebook: Dữ liệu & Báo cáo trong Excel 2013
9/ Ebook: Công thức và hàm Excel 97-2013
10/ Ebook: "Một số chuyên đề Excel mừng sinh nhật GPE lần 7"
Lần chỉnh sửa cuối:
Upvote
0
