Cách sử dụng vlookup hlookup
(ĐSPL) - Hàm ngày tháng là hàm thường được ứng dụng nhiều trong công việc thường ngày. Tuy nhiên, bạn có chắc mình đã nắm kĩ cách ứng dụng hàm ngày tháng trong Excel.
Bên cạnh VLOOKUP, HLOOKUP là một hàm dò tìm và trả về kết quả dễ sử dụng và cũng rất phổ biến trongExcel. Điện máy XANH sẽ hướng dẫn chi tiết bạn cách sử dụng hàm HLOOKUP Excel trong bài viết sau nhé. Show
1Hàm HLOOKUP là gì?Hàm HLOOKUPđược sử dụngkhi bạn cần dò tìm dữ liệu trong một bảng, một phạm vitheo hàng ngang (từ trái qua phải)và trả về dữ liệu tương ứng theo hàng dọc(từ trên xuống dưới) tương ứng. Hàm HLOOKUP được sử dụng khi tìm tên sản phẩm, đơn giá, số lượng,.. dựa trên mã vạch, mã sản phẩm,... hoặc tìm tên nhân viên, xếp loại nhân viên dựa trên các tiêu chí. Tên hàm HLOOKUP được cấu tạo từ viết tắt của H -Horizontal - hàng ngangvà LOOKUP - Look Up - Dò tìmtrong tiếng Anh. 2Công thức hàm HLOOKUPNếu Range_lookup = 1 (TRUE): dò tìm tương đối. Nếu Range_lookup = 0 (FALSE): dò tìm chính xác. Trong khi sử dụng công thức, dấu$sẽ giúp bạn cố địnhTable_array khi copy công thức cho các ô dữ liệu khá. Bạn có thể trực tiếp thêm dấu $ (VD:$H$6:$J$13) trong công thức hoặc sử dụng nút F4 sau khi chọn phạm vi. 3Ví dụ hàm HLOOKUPVí dụ 1: Xếp loại học sinh theo điểmỞ ví dụ ở dưới, ta cần xếp loại học sinh trong Bảng 1 (B3:D8) với dữ kiện ở Bảng 2 (B11:F12). Tại ô D4, ta sử dụng hàm HLOOKUP như sau: Hàm HLOOKUP sẽ dò tìm điểm số ở ô C4 trong Bảng 2 từ trái qua phải. Khi tìm thấy giá trị gần bằng, nó sẽ trả về kết quả xếp loại tương ứng nằm ở hàng 2. Ví dụ 2: Tính phụ cấp theo chức vụỞ ví dụ ở dưới, ta cần tính mức phụ cấp trong Bảng 1 (B3:D8) với dữ kiện ở Bảng 2 (B11:E12). Tại ô D4, ta sử dụng hàm HLOOKUP như sau: Hàm HLOOKUP sẽ dò tìm giá trị ở ô C4 trong Bảng 2 từ trái qua phải. Khi tìm thấy giá trị đúng, nó sẽ trả về kết quả mức phụ cấp tương ứng nằm ở hàng 2. 4Hàm HLOOKUP kết hợp với hàm IFỞ ví dụ dưới, ta có Bảng 1 (B3:E11) gồm tên nhân viên, nhóm của nhân viên, doanh số của nhân viên. Dựa vào Bảng 2 (B14:F15), nếu nhân viên thuộc nhóm A, B, C, D có doanh số lớn hơn 18, 20, 17, 19, nhân viên được đánh giá là "Đạt", nếu không là "Không Đạt". Tại ô E4, ta sử dụng hàm IF kết hợp hàm HLOOKUP như sau: Hàm HLOOKUP lúc này sẽ dò tìm và trả về chỉ tiêu tương ứng của nhóm A (ô C4) từ Bảng 2. Lúc này, ta sử dụng hàm IF để so sánh doanh số thực tế (ô D4) với chỉ tiêu và trả về kết quả "Đạt" hoặc "Không Đạt" Mời bạn tham khảo một số mẫu laptop đang kinh doanh tại Điện máy XANH Trên đây là bài viết chia sẻ cho bạn cách dùng hàm HLOOKUP trong Excel. Mong rằng những thông tin này đã giúp ích cho bạn trong việc sử dụng hàm HLOOKUP.
Công việc của bạn thường xuyên phải làm việc trên Excel thì việc sử lí dữ liệu cần thiết phải hiểu rõ các hàm trong Excel đặc biệt là hàm dò tìm. Hôm nay mình xin chia sẻ các bạn cách sử dụng hàm Hlooup và Vlookup chi tiết nhất, mời các bạn cùng theo dõi. Hướng dẫn dùng hàm Hlookup1. Chức năng chínhChức năng: Là hàm tìm kiếm giá trị của đối tượng và trả về kết quả theo hàng ngang. Cú pháp: =HLOOKUP (lookup_value, table_array,row_index_num,[rang_lookup]) Các tham số: • Lookup_value: Giá trị dùng để dò tìm • Table_array: Bảng giá trị dò, nên để ở dạng địa chỉ tuyệt đối (có dấu $ phía trước bằng cách nhấn F4). • Row_index_num: Thứ tự của hàng cần lấy dữ liệu trên bảng giá trị dò. • Range_lookup: Phạm vi tìm kiếm, TRUE (1) là dò tìm gần chính xác, FALSE (0) dò tìm chính xác. 2. Ví dụCho bảng điểm sau: Yêu cầu: Căn cứ vào bảng xếp loại, điền thông tin vào cột xếp loại. Thực hiện yêu cầu: Nhìn vào bảng thông tin ta thấy nếu điểm là 0 thì điền ở cột xếp loại là “Yếu” và sẽ dò tìm bên bảng tra thông tin, dựa vào cú pháp của hàm Hlookup (dò tìm giá trị theo hàng) ta có công thức dò tìm như sau: =HLOOKUP(C6,$C$19:$G$20,2,0). Trong đó: - Lookup_value= C6= “4”. - Table_value = $C$19:$G$20= Dò tìm toàn bộ bảng tra thông tin (nhấn F4 để cố định bảng tra thông tin lại). - Row_index_num= 2= tức là trả về cột nhóm ở bảng tra thông tin. - Range_lookup= 0 (dò tìm tuyệt đối) Sẽ có 2 phương pháp dò tìm: 0: dò tìm tuyệt đối (chính xác) 1: dò tìm tương đối (gần đúng) Ta có kết quả cuối cùng như hình dưới: Hướng dẫn dùng hàm Vlookup1. Chức năng chínhChức năng: Là hàm tìm kiếm giá trị của đối tượng và trả về kết quả theo hàng dọc. Cú pháp: =VLOOKUP (lookup_value, table_array, col_index_num,[range_lookup]) Các tham số: • Lookup_value: Giá trị dùng để dò tìm • Table_array: Bảng giá trị dò, nên để ở dạng địa chỉ tuyệt đối (có dấu $ phía trước bằng cách nhấn F4). • Col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò. • Range_lookup: Phạm vi tìm kiếm, TRUE (1) là dò tìm gần chính xác, FALSE (0) dò tìm chính xác. 2. Ví dụCho bảng thông tin dưới đây: Yêu cầu: Căn cứ vào bảng tra cứu thông tin, điền nội dung vào cột “Nhóm”. Thực hiện yêu cầu: Nhìn vào bảng thông tin ta thấy nếu bộ phận là tiếp thị thì ta sẽ điền là A và sẽ dò tìm bên bảng tra thông tin, dựa vào cú pháp của hàm Vlookup ta có công thức dò tìm như sau: =VLOOKUP(D6,$G$6:$H$10,2,0). Trong đó: - Lookup_value= D6= “Tiếp thị” - Table_value= $G$6:$H$10= Dò tìm toàn bộ bảng tra thông tin (nhấn F4 để cố định bảng tra thông tin lại). - Col_index_num= 2= tức là trả về cột nhóm ở bảng tra thông tin. - Range_lookup= 0 (dò tìm tuyệt đối) Sẽ có 2 phương pháp dò tìm: 0: dò tìm tuyệt đối (chính xác) 1: dò tìm tương đối (gần đúng) Ta có kết quả như dưới hình. Cảm ơn các bạn đã dành thời gian theo dõi bài viết Cách sử dụng hàm Hlookup và Vlookup chi tiết nhất và đừng quên để lại bình luận bên dưới để đóng góp ý kiến cho bài viết nhé.
=VLOOKUP(Lookup_value, Table_array, Col_index_ num, [Range_lookup]) Giải thích: – Lookup_value: Giá trị cần dò tìm. Bạn có thể điền giá trị trực tiếp vào công thức hoặc tham chiếu tới một ô trên bảng tính Excel. – Table_array: Bảng giới hạn để dò tìm, bạn cần F4 để Fix cố định giá trị cho mục đích copy công thức tự động, nếu bạn sử dụng laptop, có thể bạn sẽ cần bấm phím FN+F4 để cố định tham chiếu cho vùng bảng tính này. – Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm, tính từ trái qua phải.
– Range_lookup: Là giá trị Logic (TRUE=1, FALSE=0) quyết định so sánh, tìm kiếm chính xác hay so sánh, tìm kiếm tương đối với bảng giới hạn. + Nếu Range_lookup = 1 (TRUE): So sánh tương đối. Vậy khi nào thì sử dụng hàm Vlookup và khi nào dùng hàm Hlookup? Đơn giản thôi, nếu bảng giới hạn dò tìm xếp dọc như ví dụ dưới đây thì ta dùng hàm Vlookup, còn bảng giới hạn dò tìm là ngang thì ta dùng hàm Hlookup, vậy bạn chỉ cần nhớ V – dọc, H – ngang (Trong tiếng Anh, V – viết tắt của từ Vertical, còn H – viết tắt của từ Horizontal) Ví dụ cách sử dụng hàm Vlookup trong ExcelCách tính thuế nhập khẩu theo đối tượngVí dụ. Bạn hãy tính thuế nhập khấu theo Đối tượng của các mặt hàng dưới đây: Ví dụ tra cứu với hàm vlookupTrong ví dụ trên, tại ô G5 ta gõ công thức: =VLOOKUP(D5,$D$17:$F$20,2,0)*E5 Trong đó:
Với công thức trên, kết quả ta được là: Kết quả khi kéo công thức vlookup cho các ô còn lạiCopy công thức xuống các ô G6->G12 ta được kết quả như ảnh trên. Cách dùng hàm VLOOKUP để tìm kiếm gần đúngSẽ có bạn hỏi mình, làm thế nào xác định được Range_lookup=1 hay Range_lookup=0? Theo đúng định nghĩa của Microsoft Office Range_lookup=1 khi chúng ta cần tìm giá trị tương đối nghĩa là gần đúng hay giá trị hợp lý nhất khi không thể tìm thấy một giá trị chính xác. Ví dụ: Để xếp loại học sinh dựa trên điểm trung bình trong trường hợp dưới đây thì Range_lookup=1, vì để xếp loại theo bảng tham chiếu thì ta bắt buộc phải lấy giá trị tương đối nghĩa là gần đúng, 9.1 gần với 9, 5.3 gần với 5 … hay diễn đạt theo một cách khác, chúng ta có thể đưa ra tiêu chí xếp loại như sau:
Để điền vào cột Xếp loại (cột D), chúng ta có thể dùng hàm VLOOKUP như sau trong ô D4: hoặc =VLOOKUP(C4,$B$10:$C$12,2,1) Lưu ý: Khi sử dụng hàm VLOOKUP trong Excel để thực hiện tra cứu gần đúng, bảng tra cứu cần được sắp xếp theo thứ tự từ nhỏ tới lớn. Nếu bạn không sắp xếp bảng tra cứu theo thứ tự này, kết quả tra cứu dùng hàm VLOOKUP sẽ bị sai. Ví dụ sử dụng hàm vlookup tra cứu gần đúngCũng với ví dụ tra cứu xếp loại dựa trên điểm số trên sử dụng hàm VLOOKUP, khi bảng điểm có một bạn được 4 điểm, thì chúng ta sẽ nhận được lỗi #N/A khi sử dụng hàm VLOOKUP. Để khắc phục lỗi này của hàm VLOOKUP, bạn có thể thêm vào bảng tra cứu một dòng dữ liệu như hình bên dưới Ví dụ khắc phục lỗi #N/A trong công thức VLOOKUPCách sử dụng hàm VLOOKUP kết hợp với Data Validation trong ExcelTrong ví dụ ứng dụng hàm VLOOKUP này, chúng ta sẽ tham chiếu tới ô có Data Validation, để khi thay đổi giá trị trong danh sách dropdown validation này, thì kết quả trả về từ hàm VLOOKUP cũng được thay đổi một cách tự động. Bước đầu tiên, chúng ta sẽ đi chuẩn bị Data Validation bằng cách chọn ô C10, sau đó bấm vào thẻ Data / Data Validation: Tạo Data Validation để sử dụng cùng hàm VLOOKUPTrong hộp thoại Data Validation, chúng ta sẽ chọn thẻ Settings (1), sau đó trong mục Allow, chọn List (2) và cuối cùng chọn Source (3) là vùng B4:B7 Thiết lập vùng dữ liệu cho Data ValidationSau đó, chúng ta sẽ hoàn thành hàm VLOOKUP tham chiếu đến ô chứa Data Validation như hình
Hoàn thành hàm VLOOKUP tham chiếu tới ô chứa Data validation Tìm mã ngành học sử dụng hàm VLOOKUPYêu cầu của bài lần này của chúng ta là dựa vào bảng mã ngành để có thể tìm ra tên ngành học của sinh viên dựa trên ký tự đầu tiên của số báo danh Tra cứu mã ngành học với hàm VLOOKUPTrong trường hợp này, chúng ta có thể kết hợp hàm VLOOKUP với hàm LEFT như sau
Trong đó, LEFT(C10) sẽ lấy ra ký tự đầu tiên trong số báo danh được lưu trong ô C10, và hàm VLOOKUP sẽ sử dụng ký tự này cho việc tra cứu trong bảng mã ngành. Kết hợp hàm VLOOKUP với hàm LEFTSử dụng hàm VLOOKUP tra cứu ở 1 file Excel khácTra cứu dữ liệu từ file Excel khácĐể sử dụng hàm VLOOKUP tra cứu dữ liệu từ một file Excel khác, chúng ta lại bắt đầu bằng việc viết hàm VLOOKUP ở file/Workbook chúng ta muốn lấy dữ liệu, trong ảnh minh hoạ là sheet Ví dụ 03 trong file Book1.xlsx. Sau khi viết giá trị muốn tìm kiếm là SP002 được lưu trong ô A2, lưu ý bạn cần bấm dấu phẩy, sau đó chọn vùng dữ liệu cần tra cứu trong file wb khác.xlsx. Excel sẽ viết công thức đúng cho chúng ta, và bạn chỉ cần hoàn thành công thức như sau: =VLOOKUP(A2,'[wb khác.xlsx]Ví dụ 02'!$A$2:$C$7,2,FALSE) Trong trường hợp wb khác.xlsx đóng, thì công thức VLOOKUP của chúng ta vẫn sẽ hoạt động, nhưng Excel sẽ tự động “viết lại” công thức VLOOKUP với đường dẫn tới file dữ liệu như sau: =VLOOKUP(A2,'C:\Users\thanhnguyen\Desktop\[wb khác.xlsx]Ví dụ 02'!$A$2:$C$7,2,FALSE) Tra cứu vlookup từ một workbook khácXem thêm: CÁCH DÙNG HÀM VLOOKUP GIỮA 2 SHEET TRONG EXCEL Sử dụng hàm VLOOKUP trong excel và ký tự đại diện *Giống như nhiều hàm Excel khác, hàm VLOOKUP hỗ trợ bạn sử dụng ký tự thay thế như
Ví dụ tìm kiếm mã sản phẩm bắt đầu hoặc kết thúc bởi một chuỗiTra cứu với dấu * trong VLOOKUP=VLOOKUP("*AX",A2:B7,2,FALSE) Trong ví dụ này, dấu * thay thế cho cả đoạn “SP001-ABC-” trong dữ liệu của chúng ta. Nếu bạn muốn tra cứu chuỗi có chứa chữ “GFC” ở khoảng giữa, thì công thức VLOOKUP chúng ta có thể viết công thức VLOOKUP như sau: =VLOOKUP("*GFC*",A2:B7,2,FALSE) Để công thức của chúng ta linh hoạt hơn, bạn có thể tách phần “GFC” ra 1 ô riêng – ô G1 chẳng hạn, thì công thức VLOOKUP của chúng ta sẽ như sau: =VLOOKUP("*" & G1 & "*",A2:B7,2,FALSE) Tra cứu VLOOKUP với dấu *=VLOOKUP("ZZ*",A2:B7,2,FALSE) Trong ví dụ này, chúng ta sử dụng “ZZ*” để tra cứu và tìm kiếm ô dữ liệu bắt đầu bằng “ZZ” Download file ví dụ (33KB) 5 chú ý khi sử dụng hàm VLOOKUP trong Excel
Nếu những ví dụ trên chưa đáp ứng hết những thắc mắc của bạn về hàm Vlookup thì đừng bỏ qua: VÍ DỤ HÀM VLOOKUP NÂNG CAO Xử lý lỗi khi hàm VLOOKUP không tìm thấy giá trị cần tra cứu, lỗi #N/ATrong trường hợp giá trị dùng để tra cứu không được tìm thấy trong bảng tra cứu, hàm VLOOKUP sẽ trả về lỗi #N/A. Hàm VLOOKUP và lỗi #N/ANếu bạn muốn đưa ra một thông báo rõ ràng hơn, có ý nghĩa hơn, ví dụ “Không tìm thấy số báo danh”, thì bạn có thể sử dụng hàm VLOOKUP kết hợp với hàm IFERROR trong ô C12 như sau:
Hàm IFERROR sẽ phát hiện lỗi #N/A trong trường hợp này và trả về giá trị chúng ta đưa ra là “Không tìm thấy SBĐ”. Đây chỉ là một trường hợp lỗi phổ biến khi bạn sử dụng Hàm Vlookup ứng dụng trong công việc. Để tìm hiểu về cách sửa những lỗi khác khi sử dụng hàm Vlookup như lỗi Lỗi #REF!, Lỗi #VALUE!, Lỗi #NAME? thì đừng bỏ qua bài viết này bạn nhé! Các lỗi hàm Vlookup thường gặp nhất định bạn phải biết để tránh 5 điểm hạn chế của hàm VlookupChỉ hỗ trợ tham chiếu từ trái sang phảiPhần nhiều người dùng cho rằng Vlookup chỉ hoạt động theo chiều từ trái sang phải, chứ không có chiều ngược lại. Một số khác cho rằng để có thể dùng Vlookup tham chiếu từ phải sang thì bắt buộc phải kết hợp nhiều công thức lại với nhau. Thực tế mà nói, việc kết hợp công thức là một thao tác khá là rắc rối và không hề dễ dàng gì. Vì thế đây vẫn được coi là một hạn chế lớn của Vlookup Giải pháp: Sử dụng công thức Match & Index Chỉ hoạt động hiệu quả với các giá trị riêng biệtVlookup có xu hướng chỉ quan tâm đến giá trị tham chiếu đầu và bỏ quên các dòng khác chứa giá trị tương tự. Nếu như dữ liệu của bạn không có quá nhiều sự phân hóa thì bạn bắt buộc phải lách luật một chút để Vlookup có thể tham chiếu được giá trị bạn cần. Giải pháp: Tạo một bảng Pivot chứa các giá trị riêng biệt và khi đó bạn có thể dùng hàm Vlookup một cách bình thường Số thứ tự cột tham chiếu luôn cố địnhViệc số thứ tự của cột tham chiếu được điền thủ công vào nội dung của công thức khiến cho thao tác sao chép sang ô tính khác gặp nhiều khó khăn và bất tiện. Giải pháp: Bạn có thể sử dụng số thứ tự biến đổi liên tục thông qua hàm Match Mặc định thiết lập “approximate match”Nếu để ý kỹ, bạn có thể thấy rằng điều kiện kiểm duyệt tham chiếu ở trong nội dung hàm Vlookup là yếu tố tùy chọn (được ký hiệu bằng hai dấu ngoặc vuông []) nên nếu như bạn không điền nội dung gì vào đó thì công thức sẽ tự mặc định là “approximate match” Bạn thật sự cần phải chú ý nếu như không muốn kết quả tham chiếu trở nên sai lệch Giải pháp: Luôn chú ý phải thiết lập là “exact match” để có kết quả tham chiếu chính xác nhất, tuy nhiên nếu bạn vẫn muốn chọn thiết lập mặc định “approximate match” thì phải thật sự cẩn thận Làm tốc độ phản hồi của bảng tính bị chậm điMột vài ý kiến cho rằng việc sử dụng quá nhiều hàm Vlookup để tham chiếu sẽ làm giảm hiệu năng của chương trình, thậm chí gây ra hiện tượng crash. Giải pháp: Bạn không bị giới hạn số lượng hàm Vlookup cần dùng, tuy nhiên cố gắng thay thế chúng bằng lệnh Paste Special để thay thế các hàm bằng giá trị cố định, điều đó sẽ giúp chương trình hoạt động hiệu quả hơn. Để tìm hiểu kĩ hơn về cách tra cứu gần đúng, tra cứu với kí tự đại diện dấu sao * trong hàm VLOOKUP, mời các bạn theo dõi bài viết cập nhật: Tất cả về hàm VLOOKUP trong excel cơ bản cho người mới bắt đầu Ngoài ra, nếu bạn thích xem video hướng dẫn về hàm VLOOKUP cũng như những hàm tra cứu khác như Index và Match, thì Thanh có chuẩn bị sẵn video sau đây để các bạn có thể tham khảo Hi vọng với bài viết chia sẻ này các bạn sẽ nắm được chi tiết về hàm Vlookup trong excel và cách sử dụng. Trong thời gian tới Học Excel Online sẽ chia sẻ thêm về hàm Hlookup và các hàm kết hợp với hàm Vlookup và Hlookup hay được sử dụng trong công việc. Bên cạnh đó, để giúp các bạn sử dụng Excel thành thạo, tiết kiệm thời gian tra cứu, ứng dụng linh hoạt, phát triển kỹ năng sử dụng hàm và lồng ghép hàm, Học Excel Online đã thiết kế ra chương trình Excel từ cơ bản tới nâng cao, đây là chương trình học trực tuyến đầy đủ có tính ứng dụng thực tế, có sự hỗ trợ giải đáp của giảng viên và chuyên gia Nguyễn Đức Thanh trong suốt quá trình học, học viên có thể học đi học lại nhiều lần, học linh hoạt bất kỳ thời điểm nào. Chi tiết về khoá học, bạn vui lòng tham khảo: EX101 – Excel từ cơ bản tới chuyên gia Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table… Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học trên. |