Cập nhật nội dung chi tiết về Hỏi Đáp: Cách Sử Dụng Hiệu Quả Hàm Vlookup Ngược mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
Bạn gặp phải khó khăn trong việc tìm kiếm các dữ liệu ở cột phía bên phải khi áp dụng hàm Vlookup. sẽ hướng dẫn cho các bạn cách dùng hàm Vlookup ngược.
Vlookup là một trong những hàm thường được sử dụng để truy xuất dữ liệu ở trong Excel. Khi áp ung hàm vào giả quyết các bài toán thì chúng ta sẽ được trả lại kết quả nhanh chóng và hoàn toàn tương ứng với những giá trị ở cột phía bên trái của bảng.
Mục đích khi sử dụng hàm Vlookup là gì?
Khi muốn đi sâu vào vấn đề nâng cao của một hàm nào đó chúng ta cần trả lời cho mình chính xác câu hỏi ” mục đích khi sử dung hàm Vlookup là gì?
Vlookup là một hàm được dùng với mục đích hỗ trợ tra cứu cũng như tìm kiếm các thông tin trong một bản dữ liệu hoặc danh sách dựa vào những cột mã số đã được định danh có sẵn.
Ví dụ, nếu như các bạn chèn hàm Vlookup kèm theo với mã nhân viên vào một bảng tính mới, khi đó nó sẽ hiển thị tất cả toàn bộ thông tin tương ứng của nhân viên với mã đó.
Những thông tin trong đó có thể là họ tên, năm sinh, giới tinh, cấp bậc, mức lương, đánh giá năng lực… tùy theo công thức và điều kiện mà các bạn đã viết.
Khi số lượng thông tin cần tìm kiếm càng nhỏ bao nhiêu thì viết hàm Vlookup sẽ càng khó hơn. Thông thường các bạn sẽ áp dụng Vlookup này vào một bảng tính phụ. Mỗi lần nhập thêm cho bảng dữ liệu mã nhân viên thích hợp, hệ thống sẽ trả kết quả về tất cả các thông tin cần thiết về nhân viên đó.
=VLOOKUP(Giá trị cần tìm, Bảng dữ liệu, Cột tham chiếu, cách để tìm)
– Giá trị cần tìm: Chính là giá trị cụ thể hay địa chỉ của một ô nào đó.
– Bảng dữ liệu: Địa chỉ của vùng
– Cột tham chiếu: Cột cần lấy giá trị
– Cách tìm: Tìm gần chính xác trả về là True hoặc 1, còn tìm chính xác là False hoặc 0, thông dụng nhất giá trị 0.
Một số điểm các bạn cần lưu ý khi sử dụng hàm Vlookup
– Vlookuplaf hàm chỉ tìm trong cột thứ nhất của bảng dữ liệu.
– Bảng dữ liệu thông thường được mặc định ở địa chỉ tuyệt đối của $Ô số 1:$ Ô số 2.
– Đặc biết, đối với cột tham chiếu sẽ không được vượt quá số cột của bảng, nếu như bảng dữ liệu có 3 cột thì chắc chắc rằng giá trị của cột tham chiếu chỉ trả về các cột 1,2,3 chứ không trả về cột 4,5,…
Sử dụng Vlookup ngược để lấy dữ liệu kết hợp hàm If
Để lấy được tất cả các dữ liệu của bảng từ cột phải sang cột trái thì chỉ có một cách là kết hợp hàm Vlookup bảng với hàm If.
Lấy ví dụ, mình có bảng có các cột tương ứng như sau mã, tên, tuổi, giới tính.
Bài toán yêu cầu như sau: Khi các bạn đã có được mã, mà muốn lấy dữ liệu vào cột tên sẽ tương ứng với từng mã.
Khi đó bạn chọn ô đầu tiên của cột tên và nhập công thức =VLOOKUP(Ô cần truy xuất tên,IF({1,0},$C$7:$C$11,$D$7:$D$11),2,0) vào thanh fx nhấn Enter thì dữ liệu sẽ được nhập tương ứng với Mã đó.
$C$7: $C$11 – Ô cột mã đầu tiên cho đến cuối cùng của bảng
$D$7:$D$11 – Ô cột tên đầu tiên cho tới cuối cùng
Bạn hãy kéo các ô vừa hoàn thành ở cột tên để hệ thống tự thực hiện tiếp những ô còn lại.
Với cách dùng hàm Vlookup ngược để truy xuất các dữ liệu mà bạn mong muốn. Chúng tôi hi vọng rằng bài viết này các bạn sẽ nắm được rõ ràng cách thức sử dụng và không còn gặp bất kỳ khó khăn trong việc sử dụng chúng nữa.
Hàm Vlookup Ngược, Cách Dùng Vlookup Ngược Ít Người Biết.
1. Chức năng của hàm Vlookup trong Excel.
Trong Excel chúng ta sử dụng hàm Vlookup để tìm kiếm dữ liệu trong bảng hoặc một phạm vi theo cột trong một bảng dò tìm đã định nghĩa trước. Như vậy, chức năng chính của hàm là dùng để tìm kiếm giá trị trong một bảng giá trị cho trước.
2. Cú pháp hàm Vlookup trong excel.
Trong đó:
Lookup_value(bắt buộc): Giá trị cần tìm, có thể là ô tham chiếu, một giá trị hoặc chuỗi văn bản.
Table_array(bắt buộc): Bảng tìm kiếm giá trị gồm hai cột dữ liệu trở lên. Có thể là mảng thường, được đặt tên hoặc bảng Excel. Cột chứa giá trị tìm kiếm phải được đặt đầu tiên của Table_array.
Row_index_num(bắt buộc): Số thứ tự của cột chứa kết quả trả về trong Table_array.
Range_lookup(tuỳ chọn): Một giá trị logic (Boolean) cho biếtcần phải tìm kết quả chính xác hay tương đối.
Nếu TRUE hoặc bỏ qua, kết quả khớp tương đối được trả về. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy thì sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.
Nếu FALSE, chỉ kết quả khớp chính xác được trả về. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếmsẽ trả về lỗi #N/A.
3. Hàm Vlookup ngược, tham chiếu ngược là gì?
Tham chiếu “xuôi” là dạng tham chiếu từ phải qua trái. Hàm Vlookup sẽ đối chiếu sang bên phải để lấy giá trị trả tương ứng và cột tìm kiếm phải dược đặt đầu tiên. Đây là dạng thông thường chúng ta hay gặp phải.
Nhưng trong một số trường hợp chùng ta cần tìm kiếm, đối chiếu ngược lại để lấy giá trị tương ừng thì hàm Vlookup thông thường không thể dùng được.
Hình 1: Hàm Vlookup ngược trong Excel.
Sử dụng hàm Vlookup ta có: F6 =VLOOKUP( F4,$B$4:$C$9,2,0 )
Cột địa chỉ nằm ở phía bên phải của cột mã vùng. Do đó sử dụng hàm Vlookup rất dễ dàng.
Tuy nhiên nếu yêu cầu cho 1 địa chỉ tìm mã vùng tương ứng mà vẫn giữ nguyên cấu trúc bảng ban đầu thì sao?
Hình 2: Hàm Vlookup ngược trong Excel.
Ở đây chiều tham chiếu là từ phải qua trái. Điều này ngược với cách tham chiếu thông thường của hàm vlookup, do đó chúng ta hay hiểu thành tham chiếu ngược. ( Hàm VLOOKUP ngược).
4. Cách sử dụng hàm Vlookup ngược.
Ở bài viết này, chúng ra sẽ giải quyết bài toán tham chiếu ngược của hàm Vlookup thông qua hàm Lookup.
Trong các hàm cơ bản Excel, hàm Lookup là 1 hàm tham chiếu rất hữu hiệu thường nhưng hay bị bỏ quên. Hàm Lookup là hàm tham chiếu, tìm kiếm trong Excel. Hàm Lookup cho phép chúng ta tham chiếu một cách đa dạng và hiệu quả. Hàm này sẽ dùng khi bạn cần xem 1 hàng hoặc 1 cột, tìm kiếm giá trị trong cùng một vị trí hay cột.
Hàm LOOKUP có 2 cách sử dụng theo 2 dạng như sau:
Dạng Vector: Dùng để tìm 1 giá trị trong phạm vi gồm 1 hàng hoặc 1 cột (gọi là vector) và trả về 1 giá trị từ cùng vị trí đó trong phạm vi thứ 2 gồm 1 hàng hoặc 1 cột. Nên sử dụng dạng vectơ khi bạn muốn xác định phạm vi chứa các giá trị mà bạn muốn so khớp và khi phạm vi cần tìm gồm nhiều giá trị hoặc các giá trị có thể thay đổi.
Dạng Mảng: Dùng để tìm kiếm giá trị đã chỉ định trong cột hoặc hàng thứ nhất của mảng và trả về giá trị từ cùng vị trí đó trong cột hoặc hàng cuối cùng của mảng. Nên sử dụng dạng Mảng khi phạm vi tìm kiếm gồm ít giá trị, giá trị không thay đổi và phải được sắp xếp.
Nhưng để giải quyết hàm VLOOKUP ngược thì ta chỉ sử dụng đến dạng Vecter của hàm LOOKUP.
Cú pháp: =LOOKUP( Giá trị cần tìm,Vùng chứa giá trị cần tìm,Vùng chứa giá trị kết quả )
Trong đó:
Giá trị cần tìm: Có thể là số, văn bản, giá trị logic, tên hoặc tham chiếu tới 1 giá trị.
Vùng chứa giá trị cần tìm: Có thể là văn bản, số hoặc giá trị logic. Phạm vi chỉ có thể là 1 hàng hoặc 1 cột. Như đã nói ở trên, các giá trị trong vùng cần tìm phải sắp xếp tăng dần: 0, 1, 2… hoặc theo A, B, C…để hàm trả về giá trị chính xác. Văn bản không phân biệt chữ hoa hay chữ thường.
Vùng chứa giá trị kết quả: Phạm vi chỉ có thể là 1 hàng hoặc 1 cột và phải có cùng kích cỡ với vùng chứa giá trị cần tìm.
Ví dụ 1: Tìm tên sản phẩm có số lượng hàng đã bán cho trước.
Dễ thấy cột số lượng đã bán ở bên tay phải cột tên sản phẩm, trường hợp này ta không thể dùng hàm Vlookup thông thường để dò tìm từ phải qua trái được, nên tại ô D14 ta nhập công thức: =LOOKUP( D13,E4:E11,C4:C11 )
E4:E11: Vùng chứa giá trị cần tìm là cột Số lượng đã bán.
C4:C11: Vùng chứa giá trị kết quả trả về là cột Tên sản phẩm.
Hình 3: Hàm Vlookup ngược trong Excel.
Ví dụ 2: Chẳng hạn bạn muốn mua điện thoại khoảng 7,5 triệu thì tìm loại nào. Và muốn tìm điện thoại khoảng 13 triệu thì tìm loại điện thoại nào?
Hình 4: Hàm Vlookup ngược trong Excel.
Để biết với giá tiền 7,500,000 có thể mua được loại điện thoại nào, tại ô G6 ta nhập công thức: =LOOKUP( G5,D4:D8,C4:C8 )
Trong đó:
Sau khi nhập xong công thức ta sẽ được kết quả như hình dưới.
Hình 5: Hàm Vlookup ngược trong Excel.
Để biết với giá tiền 13,000,000 có thể mua được loại điện thoại nào, tại ô G5 ta đổi 7,500,000 thành 13,000,000, và tại ô G6 ta giữ nguyên công thức như trên là: =LOOKUP( G5,D4:D8,C4:C8 )
Sau khi nhập công thức, ta sẽ được kết quả như hình dưới.
Hình 6: Hàm Lookup trong Excel.
Giải thích: Với giá tiền là 13,000,000 không có giá trị nào khớp thì nó sẽ lấy giá trị nhỏ hơn gần nhất trong Vùng chứa giá trị cần tìm là 12,000,000
6. Lưu ý khi sử dụng hàm Vlookup ngược trong Excel.
– Hàm Lookup trả về giá trị đầu tiên tìm được.
Nếu cột dò tìm chứa các giá trị trùng lặp nhau thì kết quả trả về sẽ lấy giá trị đầu tiên tìm được.
Hình 7: Hàm Vlookup ngược trong Excel.
– Hàm Lookup thực hiện tra cứu không phân biệt chữ hoa chữ thường.
Ví dụ, ở bên dưới tra cứu NGUYỄN HUY (ô G4) ở cột ngoài cùng bên trái của bảng. Hàm LOOKUP không phân biệt chữ hoa chữ thường nên nó sẽ tra cứu NGUYỄN HUY hoặc Nguyễn Huy hoặc nguyễn huy, v.v. Kết quả là, hàm LOOKUP trả về quê của Nguyễn Huy Tưởng (trường hợp đầu tiên)
Hình 8: Hàm Vlookup ngược trong Excel.
– Nếu giá trị cần tìm không có thì nó sẽ lấy giá trị nhỏ hơn gần nhất trong vùng chứa giá trị cần tìm.
Hình 9: Hàm Vlookup ngược trong Excel.
– Nếu Giá trị cần tìm nhỏ hơn giá trị nhỏ nhất trong Vùng chứa giá trị cần tìm thì hàm LOOKUP sẽ trả về giá trị lỗi #N/A.
Gợi ý học tập mở rộng.
Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản
Hàm Vlookup Trong Excel, Cách Sử Dụng Hàm Vlookup Trong Excel.
Hàm Vlookup trong Excel là hàm tìm kiếm giá trị theo cột và trả về phương thức hàng dọc (theo cột), nó giúp chúng ta thống kê, dò tìm dữ liệu theo cột một cách nhanh chóng và tiện lợi. Đây là một trong những hàm phổ biến và hữu ích nhất trong Excel, nhưng lại ít người hiểu về nó. Trong bài viết này, chúng tôi sẽ giúp bạn hiểu và sử dụng hàm Vlookup một cách thành thạo qua các ví dụ thực tế nhất.
1. Chức năng của hàm Vlookup trong Excel.
Chúng ta sử dụng hàm Vlookup để tìm kiếm dữ liệu trong bảng hoặc một phạm vi theo cột trong một bảng dò tìm đã định nghĩa trước. Như vậy, chức năng chính của hàm Vlookup là dùng để tìm kiếm giá trị trong một bảng giá trị cho trước.
2. Cú pháp hàm vlookup trong excel.
Lookup_value (bắt buộc): Giá trị cần tìm, có thể là ô tham chiếu, một giá trị hoặc chuỗi văn bản.
Table_array (bắt buộc): Bảng tìm kiếm giá trị gồm hai cột dữ liệu trở lên. Có thể là mảng thường, được đặt tên hoặc bảng Excel. Cột chứa giá trị tìm kiếm phải được đặt đầu tiên của Table_array.
Row_index_num (bắt buộc): Số thứ tự của cột chứa kết quả trả về trong Table_array.
Range_lookup (tuỳ chọn): Một giá trị logic (Boolean) cho biết hàm VLOOKUP cần phải tìm kết quả chính xác hay tương đối.
Nếu TRUE hoặc bỏ qua, kết quả khớp tương đối được trả về. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.
Nếu FALSE, chỉ kết quả khớp chính xác được trả về. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ trả về lỗi #N/A.
3. Ví dụ về hàm VLOOKUP trong Excel.
3.1. Các trường hợp sử dụng hàm Vlookup trong Excel.
Hàm Vlookup trong Excel có 2 cách sử dụng. Khi khi lập công thức ta có thể lựa chọn 1 trong 2 bằng cách gán giá trị cho Range_lookup là TRUE (1) hoặc FALSE (0).
Hàm Vlookup để tìm kiếm chính xác (Range_lookup = FALSE). Cách tìm kiếm này được áp dụng trong những trường hợp cần tìm kiếm kết quả khớp chính xác. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ báo lỗi. VD: Tìm kiếm tên, tuổi, quê quán, … với mã nhân viên khớp với mã nhân viên cho trước.
Hàm Vlookup để tìm kiếm tương đối (Range_lookup = TRUE). Cách tìm kiếm này được áp dụng trong những trường hợp tìm kiếm theo khoảng giá trị kết quả khớp tương đối. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value. VD: Xếp loại học lực của học sinh theo điểm tổng kết.
3.2. Ví dụ về hàm Vlookup để tìm kiếm chính xác.
VD: Giả sử, bạn có một bảng dữ liệu nhân viên, lưu trữ mã nhân viên, họ tên, chức vụ. Một bảng khác lưu trữ mã nhân viên, quê quán, trình độ học vấn. Giờ bạn muốn điền thông tin quê quán, trình độ học vấn cho từng nhân viên thì phải làm như thế nào?
Để điển thông tin quê quán cho nhân viên, tại ô E4, ta nhập công thức dò tìm chính xác như sau: =VLOOKUP(B4,$B$17:$D$26,2,0).
Trong đó:
B4: Là giá trị cần đối chiếu.
$B$17:$D$26: Là bảng dò tìm, địa chỉ của bảng dò tìm phải là địa chỉ tuyệt đối.
2: Số thứ tự cột dữ liệu trên bảng dò tìm.
0: Kiểu tìm kiếm chính xác.
Sau khi điền xong công thức cho ô E4, tiếp tục kéo xuống copy công thức cho những nhân viên còn lại. Quan sát hình để hiểu rõ về công thức hơn.
Ảnh mình hoạ sử dụng hàm Vlookup để tìm kiếm chính xác trong Excel.
Chúng ta cũng thực hiện tương tự như vậy vậy để điền trình độ nhân viên, với công thức ở ô F4 là: =VLOOKUP(B4,$B$17:$D$26,3,0)
Trong đó:
Quan sát hình bên dưới để hiểu cách tìm kiếm và lấy giá trị của hàm Vlookup khi tìm kiếm chính xác.
Tìm kiếm tương đối chỉ có thể áp dụng khi giá trị cần dò tìm trong table_array đã được sắp xếp theo thứ tự (tăng dần hoặc giảm dần hay theo bảng chữ cái). Với những bảng như vậy bạn có thể dùng dò tìm tương đối, khi đó nó tương tự như dùng hàm IF vô hạn vậy.
VD: Căn cứ vào bảng quy định xếp loại tương ứng với điểm đã cho, tiến hành xếp loại học lực cho các sinh viên có tên trong danh sách:
Giờ ta sẽ sử dụng hàm VLOOKUP để nhập xếp loại cho các học sinh. Bạn để ý thấy rằng bảng Quy định xếp loại đã được sắp xếp theo thứ tự từ thấp đến cao (từ yếu đến giỏi) nên trong trường hợp này ta có thể dùng dò tìm tương đối.
Tại ô E4, ta nhập vào công thức là: =VLOOKUP(D4,$B$17:$C$20,2,1)
Trong đó:
Sau khi điền xong công thức cho ô E4, tiếp tục kéo xuống copy công thức cho những học sinh còn lại. Quan sát hình để hiểu rõ hơn về công thức và cách dò tìm tương đối.
Dễ thấy, khi không do tìm được kết quả nào trùng khớp với giá trị dò tìm thì hàm Vlookup sẽ lấy giá trị lớn nhất kế tiếp nhỏ hơn nó.
4. Những điều cần biết khi sử dùng hàm Vlookup trong Excel.
4.1. Một số lưu ý khi sử dụng hàm Vlookup trong Excel.
Khi tạo công thức hàm bạn cần cố định Table_array (vùng cần tìm kiếm) bằng cách thêm kí tự $ vào địa chỉ của vùng ( VD: $A$18:$B$21) để khi ta copy công thức xuống các hàng khác thì vùng cần tìm kiếm không bị thay đổi theo.
Hàm Vlookup trong Excel chỉ có thể tìm ở cột ngoài cùng bên trái của vùng ta chọn. Nếu bạn cần tìm ở những vị trí khác, hãy sử dụng công thức Index kết hợp Match.
Hàm Vlookup trong Excel không phân biệt được chữ hoa và thường.
Nếu range_lookup được đặt là TRUE hoặc bỏ trống (kết quả khớp tương đối), các giá trị ở hàng đầu của table_array phải được xếp theo thứ tự tăng dần (A-Z) từ trái sang phải.
Khi dùng hàm Vlookup để tìm kiếm tuyệt đối nhưng vùng cần tìm kiếm lại không chứa giá trị đó thì kết quả trả về sẽ là #N/A!.
4.2. Hướng dẫn sử dụng dấu chấm dấu phẩy.
Bạn có đang gặp vấn đề trong việc dùng dấu chấm “.” dấu dấu chấm phẩy “;” trong việc ngăn cách cách thành phần trong hàm, hay dấu chấm “.” dấu phẩy “,” trong việc phân cách chữ số thập phân?
Trong Excel người ta thường dấu phẩy “,” trong việc ngăn cách thành phần trong hàm và phân cách các hàng nghìn, triệu, tỷ trong số. Dấu chấm “.” để phân cách chữ số thập nhân. Nhưng trong ở một số bản phần mềm Excel khác lại dùng dấu phẩy “,” để phân cách hàng thập phân và dấu chấm “.” để phân cách các nhóm 3 chữ số (các hàng nghìn, triệu, tỷ), còn dấu “;” để phân cách các thành phần trong hàm. Điều này khiến người dùng dễ nhầm lẫn khi dùng dấu chấm và dấu phẩy trong Excel.
Trong Excel có 2 kiểu quy ước:
Quy ước kiểu phương Tây: Dấu chấm “.” là dấu để phân cách chữ số thập phân, còn phân cách các hàng nghìn, triệu, tỷ và các thành phần khi viết hàm là dấy phẩy “,”.
Quy ước của Việt Nam: Dấu phẩy “,” để phân cách hàng thập phân và dấu chấm “.” để phân cách các nhóm 3 chữ số (các hàng nghìn, triệu, tỷ), còn dấu “;” để phân cách các thành phần trong hàm.
Vậy làm thế nào để biết Excel của mình dùng tuần theo quy ước nào, bạn có thể thử cách sau:
Nhập thử một số thập phân tại ô nào đó, nếu Excel không dịch số đó về bên phải thì dấu ngăn cách thập phân là dấu “.” (chấm).
Nếu dấu ngăn cách thập phân là dấu “,” thì dấu ngăn cách phần ngàn là dấu “.” và dấu ngăn cách trong công thức là dấu “;” (chấm phẩy).
Khi Excel của bạn tuần theo quy ước của Việt Nam bạn cần đề ý hơn khi nhập dữ liệu, lập công thức, tra cứu công thức để phù hợp.
Nhưng để tiện sử dụng và tra cứu bạn nên đổi sang quy ước của phương Tây bằng cách:
4.3. Hướng dẫn sử dụng địa chỉ tuyệt đối khi dùng hàm Vlookup trong Excel.
Trong excel có 3 loại địa chỉ:
Địa chỉ tương đối: Là địa chỉ bị thay đổi tương ứng với mỗi dòng và cột khi chúng ta thực hiện sao chép công thức. (VD: B5 là địa chỉ của hàng 5 cột B).
Địa chỉ tuyệt đối: Là địa chỉ được cố định lại, không thay đổi khi ta copy công thức. (VD: $A$1- địa chỉ tuyệt đối của 1 ô, $B$17:$C$20 – địa chỉ tuyệt đối của 1 vùng)
Để tạo địa chỉ tuyệt đối, thì bạn nhấn phím F4, lúc này sẽ có dấu đô la ($) ở trước chỉ số cột và dòng.
Tóm lại nếu là địa chỉ tuyệt đối thì bạn thấy có dấu đô la ($) trước chỉ số cột và dòng.
Địa chỉ hỗn hợp: Địa chỉ hỗn hợp là địa chỉ chỉ cố định dòng hoặc cột mà thôi.
Cố định cột: Ví dụ: $A1, thì bạn thấy chỉ số cột được cố định, còn chỉ số dòng không được cố định.
Cố định dòng: Ví dụ: A$1 thì bạn thấy chỉ số cột không được cố định, còn chỉ số dòng cố định.
Khi sử dùng hàm Vlookup trong Excel bạn thường phải tìm kiếm cho cả cột nên việc copy công thức là không tránh khỏi. Lúc này bạn cần lưu ý để địa chỉ của vùng tìm kiếm là địa chỉ tuyệt đối để khi ta copy công thức cho những hàng khác thì vùng tìm kiếm của ta không bị thay đổi.
4.4. Đặt tên cho bảng trong Excel.
Địa chỉ các ô, các bảng, các vùng trong Excel được lưu bằng những kí tự và số rất khó nhớ, điều này gây khó khăn và tồn thời gian cho chúng ta khi phải gọi ra các bảng, các vùng dữ liệu từ nhiều nơi. Để đơn giản hoá việc này chúng ra có thể đặt tên cho các vùng dữ liệu. Khi cần sử dụng đến chúng ta chỉ cần gọi tới vùng đó thông qua tên đã đặt.
Đặt tên cho vùng dữ liệu dữ liệu bằng cách:
B1: Chọn vùng muốn đặt tên, vùng này có thể là 1 ô hoặc 1 vùng tuỳ theo mục đích sử dụng của bạn.
B2: Tại ô địa chỉ của vùng dữ liệu (góc bên trên phía bên tay trái, ngay trên cột A) ta nhập tên cho vùng dữ liệu và Enter.
Sau khi đặt tên cho vùng dữ liệu ta chỉ cần gọi vùng đó ra thông qua tên mà không lo địa chỉ bị sai khi copy công thức từ dòng này sang dòng khác.
Khi sử dụng hàm Vlookup ta có thể đặt tên cho cùng cần tìm kiếm mà không cần quan tâm tới địa chỉ tuyệt đối của nó.
VD: Ở ví dụ bên trên sau khi đã đặt tên cho bảng quy định xếp loại.
Từ công thức: =VLOOKUP(D4,$B$17:$C$20,2,1).
Cả 2 công thức này đều tương đương nhau nhưng với công thức mà vùng tìm kiếm được đặt tên giúp ta dễ dàng copy cho những hàng khác và đơn giản, dễ hình dung.
Video hướng dẫn:
Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản
Hàm VLOOKUP nâng cao trong ExcelHàm VLOOKUP 2 điều kiện trong ExcelHàm VLOOKUP kết hợp hàm IF trong ExcelHàm VLOOKUP ngược và cách dùng hàm VLOOKUP ngược trong ExcelCách Sử Dụng Hàm Vlookup Trong Excel
Trong ví dụ này, tham số thứ tư là FALSE. Một tham số của FALSE có nghĩa là đang tìm kiếm một đối sánh CHÍNH XÁC cho giá trị của 10251. Một tham số TRUE có nghĩa là một kết quả phù hợp sẽ được trả về. Vì VLOOKUP có thể tìm giá trị 10251 trong phạm vi A1: A6, nó trả về giá trị tương ứng từ B1: B6 là Pears.
Kết hợp chính xác và so khớp gần đúng
Để tìm kết quả khớp chính xác, hãy sử dụng FALSE làm thông số cuối cùng. Để tìm đối sánh gần đúng, hãy sử dụng TRUE làm thông số cuối cùng.
Hãy tìm kiếm một giá trị không tồn tại trong dữ liệu của chúng tôi để chứng minh tầm quan trọng của tham số này!
Sử dụng FALSE để tìm đối sánh chính xác:
=VLOOKUP(10248, A1:B6, 2, FALSE)
Nếu không tìm thấy kết quả khớp chính xác, # N / A được trả về.
Sử dụng TRUE để tìm đối sánh gần đúng:
=VLOOKUP(10248, A1:B6, 2, TRUE)
Nếu không tìm thấy kết quả phù hợp, nó trả về giá trị nhỏ hơn tiếp theo trong trường hợp này là “Táo”.
VLOOKUP từ một trang tính khác
Bạn có thể sử dụng VLOOKUP để tra cứu một giá trị khi bảng nằm trên một trang tính khác. Hãy sửa đổi ví dụ trên của chúng ta và giả sử rằng bảng nằm trong một Trang tính khác có tên Sheet2 trong phạm vi A1: B6.
Chúng tôi có thể viết lại ví dụ ban đầu của chúng tôi, nơi chúng tôi tra cứu giá trị 10251 như sau:
=VLOOKUP(10251, Sheet2!A1:B6, 2, FALSE)
Bởi trước phạm vi bảng với tên trang tính và dấu chấm than, chúng tôi có thể cập nhật VLOOKUP của chúng tôi để tham chiếu bảng trên một trang tính khác.
VLOOKUP từ một trang tính khác với dấu cách trong tên trang tính
Hãy ném thêm một biến chứng nữa, điều gì sẽ xảy ra nếu tên Trang tính của bạn chứa khoảng trắng, sau đó bạn sẽ cần thay đổi công thức hơn nữa.
Giả sử rằng bảng nằm trên một Sheet được gọi là “Test Sheet” trong phạm vi A1: B6, bây giờ chúng ta cần bọc tên Sheet trong các dấu nháy đơn như sau:
=VLOOKUP(10251, ‘Test Sheet’!A1:B6, 2, FALSE)
Bằng cách đặt tên trang tính trong dấu nháy đơn, chúng ta có thể xử lý tên trang tính với dấu cách trong hàm VLOOKUP.
VLOOKUP từ một Workbook khác
Bạn có thể sử dụng VLOOKUP để tra cứu một giá trị trong một sổ làm việc khác. Ví dụ: nếu bạn muốn có phần bảng của công thức VLOOKUP từ một sổ làm việc bên ngoài, chúng tôi có thể thử công thức sau:
=VLOOKUP(10251, ‘C:[data.xlsx]Sheet1’!$A$1:$B$6, 2, FALSE)
Điều này sẽ tìm giá trị 10251 trong tệp C: chúng tôi trong Bảng 1 nơi dữ liệu bảng được tìm thấy trong phạm vi $ A $ 1: $ B $ 6.
Tại sao nên sử dụng tham chiếu tuyệt đối?
Vì vậy, nếu bạn có công thức sau trong ô G1:
=VLOOKUP(10251, A1:B6, 2, FALSE)
Và sau đó bạn sao chép công thức này từ ô G1 sang ô H2, nó sẽ sửa đổi công thức VLOOKUP thành công thức này:
=VLOOKUP(10251, B2:C7, 2, FALSE)
Vì bảng của bạn được tìm thấy trong phạm vi A1: B6 chứ không phải B2: C7, công thức của bạn sẽ trả lại kết quả sai trong ô H2. Để đảm bảo rằng phạm vi của bạn không thay đổi, hãy thử tham khảo phạm vi bảng của bạn bằng cách sử dụng tham chiếu tuyệt đối như sau:
=VLOOKUP(10251, $A$1:$B$6, 2, FALSE)
Bây giờ nếu bạn sao chép công thức này sang một ô khác, phạm vi bảng của bạn sẽ vẫn là $ A $ 1: $ B $ 6.
Tiếp theo, hãy xem xét cách xử lý các cá thể trong đó hàm VLOOKUP không tìm thấy kết quả phù hợp và trả về lỗi # N / A. Trong hầu hết các trường hợp, bạn không muốn thấy # N / A nhưng muốn hiển thị kết quả thân thiện với người dùng hơn.
=VLOOKUP(10248, $A$1:$B$6, 2, FALSE)
Thay vì hiển thị lỗi # N / A nếu bạn không tìm thấy kết quả phù hợp, bạn có thể trả lại giá trị “Không tìm thấy”. Để làm điều này, bạn có thể sửa đổi công thức VLOOKUP của bạn như sau:
=IF(ISNA(VLOOKUP(10248, $A$1:$B$6, 2, FALSE)), “Not Found”, VLOOKUP(10248, $A$1:$B$6, 2, FALSE))
=IFERROR(VLOOKUP(10248, $A$1:$B$6, 2, FALSE), “Not Found”)
=IFNA(VLOOKUP(10248, $A$1:$B$6, 2, FALSE), “Not Found”)
Các công thức này sử dụng các hàm ISNA , IFERROR và IFNA để trả về “Không tìm thấy” nếu một hàm không tìm thấy hàm VLOOKUP.
Đây là một cách tuyệt vời để tạo bảng tính của bạn để bạn không thấy các lỗi Excel truyền thống.
Bạn đang đọc nội dung bài viết Hỏi Đáp: Cách Sử Dụng Hiệu Quả Hàm Vlookup Ngược trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!