Bạn đang xem: Tính đơn giá dựa vào mã hàng trong excel
Hàm VLOOKUP là hàm được sử dụng để đối chiếu, tra cứu tin tức trong bảng dữ liệu hoặc vùng dữ liệu. Trong bài viết này emcanbaove.edu.vn.vn đã giúp chúng ta cách sử dụng hàm VLOOKUP bằng những lấy ví dụ thực thay và phương pháp khắc phục giả dụ như sử dụng hàm bị lỗi nhé.
NOTE: Tải tức thì 70+ bài bác tập excel cơ phiên bản và nâng cao
Cách thực hiện này giúp bạn:- Áp dụng hàm Vlookup vào công việc- kết hợp hàm Vlookup với những hàm Excel khác- Sửa lỗi hàm VLOOKUP khi gặp
Hàm VLOOKUP vào Excel là hàm tìm kiếm kiếm quý giá theo cột với trả về cách làm hàng dọc, đây nói theo cách khác là hàm excel hơi thông dụng và thuận lợi với những người thường dùng Excel nhằm thống kê, dò tìm tài liệu theo cột thiết yếu xác. Trong cỡ thủ thuật học excel, emcanbaove.edu.vn.vn sẽ trình bày tới bạn đọc 2 giải pháp dò tìm cơ phiên bản của hàm này.
Kết quả khi sử dụng hàm vlookup
2. Phương pháp dò tìm xuất xắc đối
Với phương pháp dò tìm tuyệt đối thì các bạn sẽ tìm được cụ thể hơn bí quyết dò kiếm 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 cấp dưới đã cho khớp ứng phía dưới.
Ví dụ dò tìm hoàn hảo và tuyệt vời nhất khi cần sử dụng hàm vlookup
Để điền tin tức quê cửa hàng của nhân viên cấp dưới ta dùng phương pháp Vlookup cho ô E6 như sau: =VLOOKUP(A6,$D$12:$F$17,2,0)A6 là cực hiếm đem dò tìm$D$12:$F$17 là bảng dò tìm2 : số sản phẩm công nghệ tự cột tài liệu trên bảng dò0 : thứ hạng dò tìm bao gồm xác
Tương tự để điền vào ô chuyên môn của nhân viên ta làm cho như sau:
Với ô F6 gồm công thức : =VLOOKUP(A6,$D$12:$F$17,3,0)
Kết quả sử dụng hàm Vlookup dò giá trị tuyệt đối
3. Giá chỉ trị tương đối và quý giá tuyệt đối
Với quý giá tuyệt đối các bạn sẽ tìm tìm kiếm được kết quả đúng đắn nhất, những giá trị tuyệt đối sẽ không biến thành thay đổi địa chỉ dù xuất hiện ở những cột, ô mới. Vị đó bọn họ sử dụng giá trị hoàn hảo nhất trong ngôi trường hợp buộc phải tìm đúng đắn nhất thông tin và áp dụng giá trị tương đối trong trường hợp tìm tìm các hiệu quả chính xác, gần đúng chuẩn nhất. Để mày mò thêm, độc giả 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
Ở câu chữ trên emcanbaove.edu.vn.vn đã lý giải bạn khám phá cơ phiên bản về hàm Vlookup, nhằm giúp chúng ta hiểu hơn và áp dụng thực tiễn hơn, bây giờ chúng tôi sẽ đưa ra một trường hợp bài tập cụ thể như sau. Việc này là sự phối kết hợp giữa hàm Vlookup và những hàm trong Excel như hàm Hlookup hay hàm Left, hàm Right cùng hàm Match.
Cho dữ liệu như bảng sau đây với quy mong 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 bắt buộc giải quyết:
Câu 1: địa thế căn cứ vào 2 ký kết tự phía bên trái và 2 ký tự bên nên của Mã SP trong Bảng 1, hãy tra vào Bảng 2 đề điền giá chỉ trị cho cột Tên sản phẩm - Tên hãng sản xuất (Tính tài liệu cho cột C5:C10).VD: KD-NĐ tức là gạo Khang Dân - phái mạnh Định.Câu 2: Hãy điền Đơn Giá cho từng mặt mặt hàng đựa vào Mã SP ở Bảng 1 cùng tra làm việc Bảng 2. (Tính tài liệu cho cột D5:D10).Câu 3: Tính thành chi phí = con số * Đơn giá
Đáp án:
Câu 1: - Giải thích: chúng ta cần đưa ra được cách làm lấy dữ liệu Tên Hàng cùng Tên tỉnh Sản xuất, tiếp nối ghép 2 công thức giám sát này lại thì vẫn ra được đáp án Câu 1.
+ Lấy tên hàng: mang 2 ký tự phía trái trong cột Mã SP sinh sống Bảng 1 (A5:A10) so với 2 ký kết tự trong cột Mã sản phẩm ở Bảng 2, nhưng vùng dữ liệu ta mang sang cả cột Tên sản phẩm ở Bảng 2 (A15:B20) hoặc (A15:E20). >> Ta sử dụng Hàm Vlookup: trên một ô nào đó chưa xuất hiện dữ liệu, chúng ta nhập thử bí quyết sau: =VLOOKUP(LEFT(A5,2),$A$15:$B$20,2,FALSE)Sau lúc nhập xong, bấm Enter, nếu ra trường đoản cú "Khang Dân", tức là bạn đã hoàn thành được 40% câu trả lời Câu 1 này rồi đó, đơn giản dễ dàng phải ko nào, liên tục nhé.
+ Lấy tên Tỉnh Sản xuất: rước 2 ký kết tự bên đề nghị trong cột Mã SP(A5:A10) nghỉ ngơi Bảng 1 đối với 2 ký tự vào "Hàng" Mã gạo - tên tỉnh chế tạo ở Bảng 2, vùng dữ liệu lấy sẽ (A16:E20). >> Lấy dữ liệu so theo hàng buộc phải ta cần sử dụng hàm Hlookup. Cũng ở 1 ô chưa có dữ liệu, các bạn nhập thử cách làm sau:
= HLOOKUP(RIGHT(A5,2),$C$16:$E$20,2,FALSE)Sau khi nhập xong, các bạn cũng thường xuyên bấm Enter, giả dụ ra từ "Nam Định", có nghĩa là bạn đã hoàn thành xong được thêm 40% đáp án Câu 1 rồi. Hôm nay bạn đã tưởng tượng ra đáp án rồi đúng không nhỉ nào. Quá trình tiếp theo ghép 2 hàm đó lại để đo lường và thống kê được dữ liệu trong cột Tên hàng - thương hiệu Tỉnh Sản xuất
+ Ghép 2 công thức: có tương đối nhiều công thức nhằm ghép hay nối chuỗi lại với nhau, ở vấn đề này emcanbaove.edu.vn.vn hướng dẫn các bạn cách nối chuỗi sử dụng hàm &. Trong câu hỏi này, chúng tôi sẽ áp dụng dấu gạch ngang (có dấu biện pháp trống) để phân làn 2 phương pháp đã giám sát ra hiệu quả ở trước, tức là Tên Hàng và Tên tỉnh giấc Sản Xuất, ví dụ là: " - " Tổng quan liêu lại thì hàm sẽ như sau =+vlookup&" - "&hlookup (Không còn vệt = làm việc đầu hàm Hlookup nữa nhé) Lúc đó, trên 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 giúp xem kết quả, nếu như ra chuẩn chỉnh đáp án thì bắt buộc là "Khang Dân - phái mạnh Định" Kết quả đang ok, ở ô tiếp theo là C6:C10, bạn chỉ việc trỏ chuột vào cạnh công thức ở ô C5 và kéo xuống C10, là xong Tương trường đoản cú thế,
C6=+VLOOKUP(LEFT(A6,2),$A$15:$B$20,2,FALSE)&" - "&HLOOKUP(RIGHT(A6,2),$C$16:$E$20,2,FALSE)Thế là đã chấm dứt Câu 1 của yêu cầu việc đưa ra rồi, tiếp tục họ giải đáp Câu 2 chúng ta nhé
Câu 2: Tính Đơn giá
Công thức sẽ như sau: trên ô 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 hay xuất hiện trong những bài toán đo lường và tính toán dữ liệu như bảng lương, đưa ra phí, tổng thành tiền, ... Khá là dễ dàng rồi buộc phải không các bạn.Công thức tính thành tiền đang như sau, tại ô F5 chúng ta nhập E5=+D5*E5
V. Các lỗi thường gặp gỡ khi dùng hàm Vlookup
1. Lỗi #N/A
Lỗi #NA là gì? Lỗi #NA được trả về trong bí quyết excel khi không tìm thấy quý giá thích hợp. Khi sử dụng hàm Vlookup, chúng ta gặp lỗi #NA khi không kiếm thấy đk tìm tìm trong vùng điều khiển, đúng mực ở đây là cột thứ nhất trong vùng đk của hàm Vlookup.
Tham khảo: phương pháp sửa lỗi #N/A vào Excel2. Lỗi #REF!
Lỗi #REF! xẩy ra khi cột được chỉ không khẳng định ví dụ như trong bài bác dưới đây Col_index_num là 3, trong khi Table_array là B2:C10 chỉ có 2 cột. Vì chưng đó hệ thống sẽ báo lỗi #REF!
Tham khảo: phương pháp sửa lỗi #REF! vào Excel3. Lỗi #VALUE!
Lỗi #VALUE! xẩy ra khi cột Col_index_num nhỏ tuổi hơn 1 vào công thức. Ví dụ trong bài xích dưới đây Col_index_num bởi 0 dẫn đến việc lộ diện lỗi #VALUE!.
Tham khảo: phương pháp sửa lỗi #VALUE! vào Excel4. Lỗi #NAME?
Lỗi #NAME? xuất hiện khi Lookup_value thiếu vết ngoặc kép ("") (dấu "" để định dạng văn bạn dạng giúp Excel nắm rõ công thức). Ví dụ dưới đây Cải xoăn thiếu thốn ngoặc ké ("") làm cho Excel không hiểu nhiều được công thức. Sửa lỗi bằng phương pháp thay Cải xoăn thành "Cải xoăn".
Tham khảo: phương pháp sửa lỗi #NAME? trong ExcelVI. Một số lưu ý khi sử dụng hàm VLOOKUP
1. Thực hiện tham chiếu tốt đối
Sử dụng tham chiếu tuyệt đối giúp cho bạn copy phương pháp từ cột này sang cột không giống sẽ không xẩy ra thay đổi.
Như lấy ví dụ như dưới, ta tất cả công thức trên ô C13 là =VLOOKUP(B13,$B$2:$C$10,2,0). Khi copy bí quyết cho ô C14 Table_array sẽ lưu lại nguyên.
2. Ko lưu giá trị số bên dưới dạng văn bản.
Nếu tài liệu số trong bảng dữ liệu đang nhằm dạng văn phiên bản như vào ví dụ sau đây của cột A. Khi chúng ta nhập phương pháp =VLOOKUP(A8$A$2:$B$5,2,0) vào cột doanh thu dữ liệu vẫn trả về lỗi #N/A. Để chuyển tài liệu text về số bạn chỉ việc chọn Home => chọn Wrap Text => chọn Number.
Xem thêm: Mầm Đậu Nành Super Lecithin 1200Mg Chính Hãng Của Úc, Healthy Care Lecithin 1200Mg 100 Capsules
3. Bảng dò search chứa gần như giá trị bị trùng
Nếu bảng biểu của bạn chứa được nhiều giá trị trùng nhau, hàm VLOOKUP vẫn trả về công dụng đầu tiên mà nó tìm kiếm thấy từ bên trên xuống dưới. Lấy ví dụ trong bảng trả về tác dụng Táo bằng 97 thay vày 23 ngơi nghỉ dưới.
- Giải pháp 1: Nếu bạn muốn loại quăng quật giá trị trùng lặp, các bạn bôi black bảng dò search và chọn Data => Chọn Remove Duplicates