Vlookup là 1 trong những hàm dò tìm dữ liệu được sử dụng nhiều nhất trong Excel. Tuy nhiên vẫn còn nhiều bạn chưa hiểu rõ cách sử dụng hàm vlookup này, vì vậy trong bài viết này sẽ hướng dẫn các bạn chi tiết cùng bài tập áp dụng vlookup.
Bạn đang xem: Cách tính đơn giá dựa vào bảng phụ
Hàm Vlookup là hàm dùng để tìm kiếm giá trị và trả về kết quả theo phương thức hàng dọc [theo cột]. Hàm này còn dùng để thống kê, dò tìm dữ liệu theo cột một cách nhanh và chuẩn xác nhất.
Trong bài viết này chúng ta cùng tìm hiểu chi tiết cách sử dụng hàm Vlookup, Vlookup 2 điều kiện, vlookup nhiều điều kiện, kết hợp vlookup và If, Vlookup và left, right.
1. Hàm Vlookup
Cú pháp lệnh [syntax]: VLOOKUP[Lookup_value, Table_array, Col_index_ num, Range_lookup]
Trong đó:
– Lookup_value: Giá trị cần dò tìm.
– Table_array: Bảng chứa dữ liệu cần dò tìm, bạn nhấn F4 để khoá địa chỉ tuyệt đối cho mục đích copy công thức tự động.
– Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm.
– Range_lookup: Kiểu dò tìm [Là giá trị Logic: TRUE=1, FALSE=0 quyết định dò tìm chính xác hay tương đổi với bảng dò].
+ Nếu Range_lookup = 0: dò tìm chính xác.
+ Nếu Range_lookup = 1: dò tìm tương đối
_ Khi đó những giá trị trong cột đầu tiên của table_array phải được sắp xếp theo thứ tự tăng dần.
+ Nếu bỏ qua đối số này thì Excel hiểu là Range_lookup = 1
* Lưu ý: Vlookup được sử dụng khi bảng dữ liệu dò có chứa giá trị dò tìm sắp xếp theo cột [theo chiều dọc] nếu bảng dữ liệu có giá trị dò tìm sắp xếp theo chiều ngang chúng ta phải sử dụng hàm Hlookup.
Ví dụ 1 bài tập sử dụng Vlookup
- Bạn hãy điền vào cột Đơn giá theo Loại vật tư từ thông tin Bảng phụ theo ví dụ dưới đây:
Trong ví dụ trên, tại ô D5 ta gõ công thức: =VLOOKUP[B5;$F$6:$H$9;3;0]
Trong đó:
Vlookup: là hàm dùng để tìm kiếm ra LOẠI VẬT TƯ tại BẢNG PHỤ có Đơn giá.
B5: Là Giá trị cần dò tìm; ở đây là các Giá trị trong cột Loại Vật tư [Xi Măng, Bột màu, Sơn gỗ]
$F$6:$H$9: Bảng dữ liệu dò tìm, chính là F6:H9 nhưng khoá địa chỉ tuyệt đối [nhấn F4] để Copy công thức xuống các ô D6→D12.
3: Thứ tự cột giá trị cần lấy, trong trường hợp này chính là cột Đơn giá;
0: là kiểu dò tìm chính xác.
Xem thêm: Tổng Công Ty Đầu Tư Phát Triển Nhà Và Đô Thị Bộ Quốc Phòng, Tổng Công Ty Đầu Tư Phát Triển Nhà Và Đô Thị
- Ở ví dụ tiếp theo chúng ta sẽ thực hiện dò tìm tương đối [kiểu dò trong hàm Vlookup là:1] Xếp loại học lực dựa vào Điểm trung bình [ĐTB] trong Bảng Xếp Loại. Sau khi nhập công thức và copy xuống các ô từ D5→D10 ta được bảng sau:
Trong ví dụ này thì ở ô D4 nhập công thức: =VLOOKUP[C4,$F$6:$G$10,2,1]
Trong đó:
C4: là giá trị dò tìm
$F$6:$G$10: Là bảng dữ liệu cần dò giá trị
2: Thứ tự cột giá trị cần lấy, trong trường hợp này chính là cột Đơn giá;
1: Kiểu dò tìm tương đối [gần đúng]
2. Hàm Vlookup và Left
- Như các bạn đã biết, hàm Left là hàm lấy ký tự bên trái một chuỗi bất kỳ, khi kết hợp Vlookup và left giúp cho việc dò tìm kết quả nhanh và chính xác trong nhiều bài toán cụ thể.
* Cú pháp hàm left:=Left[text,n]
Ví dụ: Điền vào cột Tên hàng biết rằng ký tự đầu của Phiếu xuất kho là Mã VT
- Ý nghĩa công thức: Đầu tiên hàm left lấy 1 ký tự bên trái ô B5, dò tìm ký tự này trong bảng E5:F7, khi gặp giá trị dò tìm sẽ trả về giá trị tương ứng trong cột thứ 2 của bảng E5:F7.
3. Hàm Vlookup và Right
- Hàm Right là hàm lấy ký tự bên phải một chuỗi bất kỳ, khi kết hợp Vlookup và Right cũng tương tự như Vlookup kết hợp left giúp cho việc dò tìm kết quả nhanh và chính xác trong nhiều bài toán cụ thể.
* Cú pháp hàm Right: =Right[text,n]
Ví dụ: Điền vào cột Tên hàng biết rằng ký tự cuối của Phiếu xuất kho là Mã VT
- Ý nghĩa công thức: Đầu tiên, hàm Right sẽ lấy 1 ký tự bên phải ô B5, dò tìm giá trị này trong bảng E4:F7, khi gặp giá trị dò tìm sẽ trả về giá trị tương ứng trong cột thứ 2 của bảng E4:F7.
4. Hàm Vlookup kết hợp IF
- Hàm IF là một trong những hàm điều kiện được dùng phổ biết nhất trong Excel, khi kết hợp IF và Vlookup giúp cho nhiều bài toán được giải quyết nhanh chóng.
* Cú pháp hàm IF:=if[logical_test, value if true, value if false]
Ví dụ: Điền vào cột mức Giá của Mã VT biết rằng, ký tự đầu phiếu xuất kho là Mã VT, ký tự cuối Phiếu xuất kho là Giá.
+ Ý nghĩa công thức:
- Đầu tiên, Hàm Left lấy 1 ký tự bên trái ô B5, dò tìm giá trị này trong bảng E4:G7, khi thấy giá trị dò tìm sẽ trả về giá trị tương ứng trong dòng do hàm IF trả về
- Đối với hàm IF, đầu tiên lấy 1 ký tự bên phải ô B5 kiểm tra giá trị này có ="1" hay không, nếu bằng thì trả về 2, nếu không trả về 3. Giá trị do if trả về chính là dòng trả về kết quả của Hlookup.
5. Hàm Vlookup 2 điều kiện
- Để hiểu rõ cách sử dụng Vlookup 2 điều kiện chúng ta cùng xem một ví dụ cụ thể như hình dưới đây:
- Yêu cầu: Làm sao biết sản lượng của mỗi sản phẩm trong từng ca
+ Ở bài toán này chúng ta sẽ dùng Vlookup 2 điều kiện như sau:
* Mời các bạn xem video hướng dẫn chi tiết cách sử dụng hàm Vlookup - How to use Vlookup function in Excel
Hi vọng qua phần hướng dẫn cách sử dụng Vlookup ở trên sẽ phần nào giúp các bạn hiểu rõ hơn về hàm vlookup để áp dụng cho bảng tính thực tế.
Ngoài Vlookup, còn có các hàm dò tìm rất hữu ích và phổ biến khác như Hlookup, Index và Match, . . . tất cả các hướng dẫn này sẽ được giới thiệu trên webmuanha.com, các bạn hãy tìm đọc và tham khảo nhé.
Hàm VLOOKUP là hàm được sử dụng để đối chiếu, tra cứu thông tin trong bảng dữ liệu hoặc vùng dữ liệu. Trong bài viết này thienluc.vn.vn sẽ giúp các bạn cách sử dụng hàm VLOOKUP bằng những ví dụ thực thế và cách khắc phục nếu như sử dụng hàm bị lỗi nhé. Bạn đang xem: Tính đơn giá dựa vào mã hàng trong excel
NOTE: Tải Ngay 70+ bài tập excel cơ bản và nâng cao
Cách thực hiện này giúp bạn:- Áp dụng hàm Vlookup trong công việc- Kết hợp hàm Vlookup với các hàm Excel khác- Sửa lỗi hàm VLOOKUP khi gặp
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, đây có thể nói là hàm excel khá thông dụng và tiện lợi với những người hay sử dụng Excel để thống kê, dò tìm dữ liệu theo cột chính xác. Trong khuôn khổ thủ thuật học excel, thienluc.vn.vn sẽ trình bày tới bạn đọc 2 cách dò tìm cơ bản của hàm này.
Kết quả khi dùng hàm vlookup
2. Cách dò tìm tuyệt đối
Với cách dò tìm tuyệt đối thì bạn sẽ tìm được chi tiết hơn cách dò tìm tương đối.VD: Điền thông tin Quê quán và trình độ của các nhân viên vào bảng căn cứ vào mã nhân viên đã cho tương ứng phía dưới.
Ví dụ dò tìm tuyệt đối khi dùng hàm vlookup
Để điền thông tin quê quán của nhân viên ta dùng công thức Vlookup cho ô E6 như sau: =VLOOKUP[A6,$D$12:$F$17,2,0]A6 là giá trị đem dò tìm$D$12:$F$17 là bảng dò tìm2 : số thứ tự cột dữ liệu trên bảng dò0 : Kiểu dò tìm chính xác
Tương tự để điền vào ô trình độ của nhân viên ta làm như sau:
Với ô F6 có công thức : =VLOOKUP[A6,$D$12:$F$17,3,0]
Kết quả dùng hàm Vlookup dò giá trị tuyệt đối
3. Giá trị tương đối và giá trị tuyệt đối
Với giá trị tuyệt đối bạn sẽ tìm kiếm được kết quả chính xác nhất, các giá trị tuyệt đối sẽ không bị thay đổi địa chỉ dù xuất hiện ở các cột, ô mới. Do đó chúng ta sử dụng giá trị tuyệt đối trong trường hợp cần tìm chính xác nhất thông tin và sử dụng giá trị tương đối trong trường hợp tìm kiếm các kết quả chính xác, gần chính xác nhất. Để tìm hiểu thêm, bạn đọc có thể tham khảo thêm bài viết về tham chiếu các hàm trong Excel nhé.
IV. Hàm Vlookup kết hợp hàm Hlookup, Left, Right, Match
Ở nội dung trên thienluc.vn.vn đã hướng dẫn bạn tìm hiểu cơ bản về hàm Vlookup, để giúp các bạn hiểu hơn và áp dụng thực tế hơn, bây giờ chúng tôi sẽ đưa ra một tình huống bài tập cụ thể như sau. Bài toán này là sự kết hợp giữa hàm Vlookup và các hàm trong Excel như hàm Hlookup, hàm Left, hàm Right và hàm Match.
Cho dữ liệu như bảng dưới đây với quy ước như sau:- Bảng 1: BẢNG THỐNG KÊ TIÊU THỤ GẠO- Bảng 2: BẢNG TRA TÊN HÀNG, TÊN HÃNG SẢN XUẤT VÀ ĐƠN GIÁ
Yêu cầu cần giải quyết:
Câu 1: Căn cứ vào 2 ký tự bên trái và 2 ký tự bên phải của Mã SP trong Bảng 1, hãy tra trong Bảng 2 đề điền giá trị cho cột Tên Hàng - Tên Hãng Sản xuất [Tính dữ liệu cho cột C5:C10].VD: KD-NĐ Tức là gạo Khang Dân - Nam Định.Câu 2: Hãy điền Đơn Giá cho mỗi mặt hàng đựa vào Mã SP ở Bảng 1 và tra ở Bảng 2. [Tính dữ liệu cho cột D5:D10].Câu 3: Tính thành tiền = Số lượng * Đơn giá
Đáp án:
Câu 1: - Giải thích: Chúng ta cần đưa ra được công thức lấy dữ liệu Tên Hàng và Tên Tỉnh Sản xuất, sau đó ghép 2 công thức tính toán này lại thì sẽ ra được đáp án Câu 1.
+ Lấy Tên hàng: Lấy 2 ký tự bên trái trong cột Mã SP ở Bảng 1 [A5:A10] so với 2 ký tự trong cột Mã Hàng ở Bảng 2, nhưng vùng dữ liệu ta lấy sang cả cột Tên Hàng ở Bảng 2 [A15:B20] hoặc [A15:E20]. >> Ta dùng Hàm Vlookup: Tại một ô nào đó chưa có dữ liệu, bạn nhập thử công thức sau: =VLOOKUP[LEFT[A5,2],$A$15:$B$20,2,FALSE]Sau khi nhập xong, bấm Enter, nếu ra từ "Khang Dân", tức là bạn đã hoàn thiện được 40% đáp án Câu 1 này rồi đó, đơn giản phải không nào, tiếp tục nhé.
Xem thêm: 【1️⃣】 Cách Tạo Avatar Facebook Độc Đáo Với Chữ Cái Tên Mình Trên Facebook
+ Lấy Tên Tỉnh Sản xuất: Lấy 2 ký tự bên phải trong cột Mã SP[A5:A10] ở Bảng 1 so với 2 ký tự trong "Hàng" Mã gạo - Tên tỉnh sản xuất ở Bảng 2, vùng dữ liệu lấy sẽ [A16:E20]. >> Lấy dữ liệu so theo hàng nên ta dùng hàm Hlookup. Cũng tại 1 ô chưa có dữ liệu, bạn nhập thử công thức sau:
= HLOOKUP[RIGHT[A5,2],$C$16:$E$20,2,FALSE]Sau khi nhập xong, bạn cũng tiếp tục bấm Enter, nếu ra từ "Nam Định", tức là bạn đã hoàn thiện được thêm 40% đáp án Câu 1 rồi. Lúc này bạn đã hình dung ra đáp án rồi đúng không nào. Công việc tiếp theo ghép 2 hàm này lại để tính toán được dữ liệu trong cột Tên Hàng - Tên Tỉnh Sản xuất
+ Ghép 2 công thức: Có nhiều công thức để ghép hay nối chuỗi lại với nhau, ở bài toán này thienluc.vn.vn hướng dẫn các bạn cách nối chuỗi sử dụng hàm &. Trong bài toán này, chúng tôi sẽ sử dụng dấu gạch ngang [có dấu cách trống] để ngăn cách 2 công thức đã tính toán ra kết quả ở trước, tức là Tên Hàng và Tên Tỉnh Sản Xuất, cụ thể là: " - " Tổng quan lại thì hàm sẽ như sau =+vlookup&" - "&hlookup [Không còn dấu = ở đầu hàm Hlookup nữa nhé] Lúc đó, tại C5, bạn nhập công thức: C5=+VLOOKUP[LEFT[A5,2],$A$15:$B$20,2,FALSE]&" - "&HLOOKUP[RIGHT[A5,2],$C$16:$E$20,2,FALSE] Bấm Enter để xem kết quả, Nếu ra chuẩn đáp án thì phải là "Khang Dân - Nam Định" Kết quả đã ok, ở ô tiếp theo là C6:C10, bạn chỉ cần trỏ chuột vào cạnh công thức ở ô C5 và kéo xuống C10, là xong Tương tự thế,
C6=+VLOOKUP[LEFT[A6,2],$A$15:$B$20,2,FALSE]&" - "&HLOOKUP[RIGHT[A6,2],$C$16:$E$20,2,FALSE]Thế là đã xong Câu 1 của yêu cầu bài toán đưa ra rồi, tiếp tục chúng ta giải đáp Câu 2 các bạn nhé
Câu 2: Tính Đơn giáCông thức sẽ như sau: Tại ô D5 các bạn nhập công thứcD5=+VLOOKUP[LEFT[A5,2],$A$16:$E$20,MATCH[RIGHT[A5,2],$A$16:$E$16,0],FALSE]
Câu 3: Tính thành tiềnỒ, câu hỏi này thường xuất hiện trong những bài toán tính toán dữ liệu như bảng lương, chi phí, tổng thành tiền, ... Khá là dễ rồi phải không các bạn.Công thức tính thành tiền sẽ như sau, Tại ô F5 các bạn nhập E5=+D5*E5
V. Các lỗi thường gặp khi dùng hàm Vlookup
1. Lỗi #N/A
Lỗi #NA là gì? Lỗi #NA được trả về trong công thức excel khi không tìm thấy giá trị thích hợp. Khi sử dụng hàm Vlookup, chúng ta gặp lỗi #NA khi không tìm thấy điều kiện tìm kiếm trong vùng điều khiển, chính xác ở đây là cột đầu tiên trong vùng điều kiện của hàm Vlookup.
Tham khảo: Cách sửa lỗi #N/A trong Excel2. Lỗi #REF!
Lỗi #REF! xảy ra khi cột được chỉ không xác định ví dụ như trong bài dưới đây Col_index_num là 3, trong khi Table_array là B2:C10 chỉ có 2 cột. Do đó hệ thống sẽ báo lỗi #REF!
Tham khảo: Cách sửa lỗi #REF! trong Excel3. Lỗi #VALUE!
Lỗi #VALUE! xảy ra khi cột Col_index_num nhỏ hơn 1 trong công thức. Ví dụ trong bài dưới đây Col_index_num bằng 0 dẫn đến việc xuất hiện lỗi #VALUE!.
Tham khảo: Cách sửa lỗi #VALUE! trong Excel4. Lỗi #NAME?
Lỗi #NAME? xuất hiện khi Lookup_value thiếu dấu ngoặc kép [""] [dấu "" để định dạng văn bản giúp Excel hiểu rõ công thức]. Ví dụ bên dưới đây Cải xoăn thiếu ngoặc ké [""] làm Excel không hiểu được công thức. Sửa lỗi bằng cách thay Cải xoăn thành "Cải xoăn".
Tham khảo: Cách sửa lỗi #NAME? trong ExcelVI. Một số lưu ý khi dùng hàm VLOOKUP
1. Sử dụng tham chiếu tuyệt đối
Sử dụng tham chiếu tuyệt đối giúp bạn copy công thức từ cột này sang cột khác sẽ 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 ô C14 Table_array sẽ giữ nguyên.
2. Không lưu giá trị số dưới dạng văn bản.
Nếu dữ liệu số trong bảng dữ liệu đang để dạng văn bản như trong ví dụ dưới đây của cột A. Khi bạn nhập công thức =VLOOKUP[A8$A$2:$B$5,2,0] trong cột doanh thu dữ liệu sẽ trả về lỗi #N/A. Để chuyển dữ liệu text về số bạn chỉ cần chọn Home => Chọn Wrap Text => Chọn Number.
3. Bảng dò tìm chứa những giá trị bị trùng
Nế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. Ví dụ trong bảng trả về kết quả Táo bằng 97 thay vì 23 ở dưới.
- 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 => Chọn Remove Duplicates