Hàm lấy dữ liệu trong ô excel
|
Bây giờ, bạn đã biết lý do nên học hàm MATCH/INDEX, nên hãy đi vào phần thú vị nhất và xem xem bạn có thể áp dụng nguyên lý hay không. Show
CÁCH TÌM KIẾM GIÁ TRỊ SANG TRÁI VỚI HÀM INDEX VÀ HÀM MATCHNhư đã được nhắc đến trong bất kỳ bài hướng dẫn VLOOKUP nào, hàm Excel này không thể tìm kiếm bên trái của nó. Vì vậy, nếu cột cần tìm của bạn không phải là cột cận trái trong dải ô cần tìm, thì không bao giờ công thức VLOOKUP trả về kết quả mà bạn mong muốn. Hàm INDEX MATCH trong Excel thì linh hoạt hơn và thật sự không quan tâm việc cột trả về nằm ở đâu. Ví dụ, chúng ta sẽ dùng một bảng – bảng này liệt kê thủ đô và dân số. Lần này, hãy lập một công thức INDEX MATCH tìm xem thủ đô của Nga, Matxcơva, xếp thứ mấy dựa theo dân số. Như bạn có thể thấy từ ảnh chụp màn hình bên dưới, công thức sau đây hoạt động một cách trôi chảy: =INDEX($A$2:$A$10,MATCH(“Russia”,$B$2:$B$10,0)) Bây giờ, có lẽ bạn chẳng gặp chút khó khăn nào trong việc hiểu công thức hoạt động như thế nào: Đầu tiên, bạn lập công thức MATCH đơn giản tìm vị trí của Nga: =MATCH(“Russia”,$B$2:$B$10,0)) Rồi, bạn xác định thông số mảng cho hàm INDEX, trong trường hợp này là cột A (A2:A10). Cuối cùng, bạn kết hợp hai phần lại và có công thức: =INDEX($A$2:$A$10,MATCH(“Russia”,$B$2:$B$10,0)) Mẹo. Việc sử dụng tham chiếu ô tuyệt đối luôn là một ý tưởng hay trong công thức INDEX và MATCH để dải ô cần tìm của bạn không bị phá hỏng khi bạn sao chép công thức sang ô khác. Hàm VLOOKUP là một hàm đặc biệt hữu ích trong Excel giúp bạn dò và trả về dữ liệu tương ứng. Điện máy XANH sẽ hướng dẫn chi tiết bạn cách sử dụng hàm VLOOKUP trong bài viết sau nhé.Xem ngay chuột máy tính chính hãng, chất lượng, giá tốt bán chạy nhất tại Điện máy XANH:1.835.000₫ 2.450.000₫ -25% 1.320.000₫ 1.650.000₫ -20% Trả góp 0% 2.790.000₫ 3.990.000₫ -30% 805.000₫ 1.010.000₫ -20% 870.000₫ 1.090.000₫ -20% 400.000₫ 500.000₫ -20% 600.000₫ 750.000₫ -20% 390.000₫ 490.000₫ -20% 680.000₫ 850.000₫ -20% 440.000₫ 550.000₫ -20% 560.000₫ 700.000₫ -20% 280.000₫ 350.000₫ -20% Chuột Bluetooth Microsoft Ocean Plastic 700.000₫ 1.180.000₫ 1.390.000₫ -15% 755.000₫ 890.000₫ -15% 1.775.000₫ 2.090.000₫ -15% Xem thêm sản phẩm Chuột máy tínhVí dụ trong bài được thực hiện trên phiên bản Excel 2016. Bạn có thể áp dụng tương tự trên các phiên bản Excel khác như: 2007, 2010, 2013, 2017 và Microsoft Excel 365. 1Hàm VLOOKUP là gì?Hàm VLOOKUP được sử dụng khi bạn cần dò tìm dữ liệu trong một bảng, một phạm vi theo hàng dọc và trả về dữ liệu tương ứng theo hàng ngang tương ứng. Trong thực tế, hàm VLOOKUP cực kỳ thông 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í trên. Ngược lại, khi bạn cần dò tìm dữ liệu trong một bảng, một phạm vi theo hàng ngang và trả về dữ liệu tương ứng theo hàng dọc thì hãy sử dụng hàm HLOOKUP. LOOKUP là Look Up nghĩa là tìm kiếm trong tiếng Anh. V là viết viết tắt của Vertical - hàng dọc và H là viết tắt của Horizontal - hàng ngang. 2Công thức hàm VLOOKUPCông thức hàm VLOOKUP: =VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup) Trong đó:
Nếu Range_lookup = 1 (TRUE): dò tìm tương đối. Nếu Range_lookup = 0 (FALSE): dò tìm chính xác. Nếu bạn copy công thức cho các ô dữ liệu khác, bạn cần sử dụng dấu $ để cố định Table_array đề giới hạn dò tìm bằng cách thêm trực tiếp trước khai báo (VD: $H$6:$J$13), cột hoặc sử dụng nút F4 sau khi chọn bảng. Xem thêm: Hàm SUMIF 3Ví dụ hàm VLOOKUPBạn có thể truy cập vào đây để tải về các file ví dụ bên dưới Ví dụ 1: Tính phụ cấp theo chức vụ Do tình hình Covid-19, công ty quyết định phụ cấp cho nhân viên theo chức vụ tương ứng như bảng 2 (B16:C21). Lúc này, dựa vào danh sách nhân viên cùng với chức vụ có sẵn ở bảng 1, ta sẽ xác định mức phụ cấp tương ứng. Cách thực hiện là bạn sẽ dò tìm giá trị của chức vụ của nhân viên tại bảng 1, sau đó dò tìm tại cột 1 trong bảng 2 từ trên xuống dưới. Khi bạn tìm thấy giá trị, bạn sẽ lấy giá trị tương ứng tại cột 2 của bảng 2 để điền vào bảng 1. Với một danh sách nhân viên vài trăm, một ngàn người thì bạn không thể làm thủ công như vậy. Đó chính là cách hàm VLOOKUP phát huy tác dụng. Tại ô E4, bạn điền công thức: =VLOOKUP(D4,$B$16:$C$21,2,0)
Sau đó, bạn chỉ cần copy công thức cho các ô khác hoặc sử dụng Flash Fill và bạn đã hoàn thành việc tính phụ cấp theo chức vụ nhanh chóng. Ví dụ 2: Xếp loại học sinh theo điểm số Sau kỳ kiểm tra, ta có kết quả bài thi tương ứng với học sinh như bảng 1. Ta cần xếp loại theo điểm số dựa trên bảng 2 (B11:C15). Tại ô E4, bạn điền công thức: =VLOOKUP(D4,$B$11:$C$15,2,1)
Excel sẽ lấy điểm số ở cột D4 và dò trong bảng 2. Khi thấy giá trị gần nhất của D4 trong bảng (ở đây là 8.5), Excel sẽ trả về kết quả tương ứng ở cột 2 là Giỏi. Sau đó, bạn chỉ cần copy công thức cho các ô khác hoặc sử dụng Flash Fill và bạn đã hoàn thành việc xếp loại học sinh theo điểm số nhanh chóng. 4Các lỗi thường gặp khi dùng hàm VLOOKUPLỗi #N/AMột ràng buộc của hàm VLOOKUP là nó chỉ có thể tìm các giá trị trên cột ngoài cùng bên trái trong Table_array, nếu không sẽ xuất hiện lỗi #N/A. Lúc này bạn hãy cân nhắc sử dụng hàm INDEX kết hợp hàm MATCH. Như ví dụ bên dưới, Table_array là A2:C10, nên hàm VLOOKUP sẽ tìm kiếm trong cột A. Để sửa trường hợp này, bạn đổi Table_array thành B2:C10, hàm VLOOKUP sẽ tìm kiếm trong cột B. Ngoài ra, nếu không tìm thấy kết quả khớp chính xác sẽ trả về hàm sẽ trả về lỗi #N/A do dữ liệu không có trong Table_array. Lúc này bạn có thể sử dụng hàm IFERROR để đổi #N/A thành giá trị khác. Như ví dụ bên dưới, "Rau muống" không có trong bảng dò tìm nên hàm VLOOKUP sẽ không tìm thấy. Nếu bạn chắc chắn rằng dữ liệu có trong Table_array của mình và hàm VLOOKUP không tìm được được, hãy kiểm tra lại rằng các ô dữ liệu được tham chiếu không có khoảng trắng ẩn hoặc ký tự không in. Ngoài ra, hãy đảm bảo rằng các ô dữ liệu tuân theo đúng định dạng. Lỗi #REF!Nếu Col_index_num lớn hơn số cột trong Table_array, bạn sẽ nhận được giá trị lỗi #REF!. Lúc này, bạn hãy kiểm tra lại công thức để đảm bảo Col_index_num bằng hoặc nhỏ hơn số cột trong Table_array. Như ví dụ bên dưới, Col_index_num là 3, trong khi Table_array là B2:C10 chỉ có 2 cột. Lỗi #VALUE!Nếu Col_index_num nhỏ hơn 1 trong công thức, bạn sẽ nhận giá trị lỗi #VALUE!. Trong Table_array, cột 1 là cột tìm kiếm, cột 2 là cột đầu tiên ở bên phải của cột tìm kiếm, v.v... Vì vậy khi xuất hiện lỗi này, hãy kiểm tra lại giá trị Col_index_number trong công thức. Như ví dụ bên dưới, Col_index_num bằng 0 dẫn đến việc xuất hiện lỗi #VALUE!. Lỗi #NAME?Lỗi #NAME? xuất hiện khi Lookup_value thiếu dấu ngoặc kép ("). Để tìm kiếm giá trị định dạng văn bản (Text), bạn dùng dấu ngoặc kép để Excel có thể hiểu công thức. Như ví dụ bên dưới, Cải xoăn không bỏ vào dấu ngoặc kép (") sẽ làm xuất hiện lỗi #NAME?. Bạn sửa lỗi bằng cách thay Cải xoăn thành "Cải xoăn". 5Một số lưu ý khi dùng hàm VLOOKUPSử dụng tham chiếu tuyệt đốiTrong quá trình copy công thức, hãy biến Table_array hoặc Lookup_value thành tham chiếu tuyệt đối bằng cách đặt dấu đô la ($) trước các cột và hàng để công thức không bị thay đổi. Như ví dụ dưới, ta có công thức tại ô C13 là =VLOOKUP(B13,$B$2:$C$10,2,0). Khi copy công thức cho ô C4 Table_array sẽ giữ nguyên. Nếu không chuyển đổi thành tham chiếu tuyệt đối, Lookup_value hoặc Table_array sẽ bị thay đổi, làm kết quả tìm kiếm bị sai lệch. Như ví dụ dưới, ta có công thức tại ô C13 là =VLOOKUP(B13,B2:C10,2,0). Khi copy công thức cho ô C4 Table_array sẽ biến thành =VLOOKUP(B14,B3:C11,2,0). Không lưu trữ giá trị số dưới dạng văn bảnNếu trong Table_array, dữ liệu số đang để dưới dạng văn bản và Lookup_value lại là dạng số thì hàm VLOOKUP sẽ trả về lỗi #N/A. Như ví dụ dưới, ta có dữ liệu tại ô A2:A5 đang ở dạng văn bản nhưng Lookup_value tại ô A8 đang ở dạng số. Trong trường hợp này, hãy chuyển định dạng ô A2:A5 thành dạng số và hàm sẽ trả về kết quả bình thường. Bảng dò tìm chứa những giá trị bị trùngNếu bảng biểu của bạn chứa nhiều giá trị trùng nhau, hàm VLOOKUP sẽ trả về kết quả đầu tiên mà nó tìm thấy từ trên xuống dưới. Như ở ví dụ dưới, trong bảng ta có 2 giá trị ứng với Táo là 97 và 23. Hàm VLOOKUP sẽ trả về kết quả 97 vì đó là giá trị đầu tiên nó tìm thấy Giải pháp 1: Nếu bạn muốn loại bỏ giá trị trùng lặp, bạn bôi đen bảng dò tìm và chọn Data > Remove Duplicates Giải pháp 2: Sử dụng Pivot Table để lọc ra danh sách kết quả Trả góp 0% 4.990.000₫ 9.990.000₫ -50% Quà 100.000₫ Trả góp 0% 5.690.000₫ 9.990.000₫ -43% Quà 100.000₫ 5.590.000₫ 8.990.000₫ -37% Quà 100.000₫ Trả góp 0% 9.090.000₫ 12.990.000₫ -30% Quà 100.000₫ Trả góp 0% 8.990.000₫ 12.690.000₫ -29% Quà 100.000₫ Trả góp 0% 20.490.000₫ 28.490.000₫ -28% Quà 650.000₫ Trả góp 0% 10.090.000₫ 13.790.000₫ -26% Quà 100.000₫ Trả góp 0% 50.190.000₫ 66.990.000₫ -25% Trả góp 0% 50.190.000₫ 66.990.000₫ -25% 6.590.000₫ 8.790.000₫ -25% Quà 100.000₫ Trả góp 0% 9.690.000₫ 12.790.000₫ -24% Quà 100.000₫ Trả góp 0% 10.390.000₫ 12.990.000₫ -20% Quà 100.000₫ Surface Pro 8 i5 1135G7 30.890.000₫ Quà 100.000₫ Trả góp 0% 6.690.000₫ 8.590.000₫ -22% Quà 100.000₫ Trả góp 0% 22.490.000₫ 28.990.000₫ -22% Quà 2.050.000₫ 56.490.000₫ 71.990.000₫ -21% Xem thêm sản phẩm LaptopXem thêm:
Trên đây là bài viết chia sẻ cho bạn cách dùng hàm VLOOKUP trong Excel. Mong rằng những thông tin này đã giúp ích cho bạn trong việc hiểu và sử dụng hàm VLOOKUP cho công việc của mình. Bạn có làm được hướng dẫn này không? Cám ơn bạn đã phản hồi Xin lỗi bài viết chưa đáp ứng nhu cầu của bạn. Vấn đề bạn gặp phải là gì?Đang làm gặp lỗi, không có trong hướng dẫn Đã làm nhưng không thành công Bài viết không đúng nhu cầu Bài viết đã cũ, thiếu thông tin |
