Hàm vlookup kết hợp hàm if trong excel đơn giản, hiệu quả
Bài viết này, sẽ giới thiệu đến chúng ta hướng dẫn cách sử dụng hàm Vlookup kết phù hợp với hàm If, Left, Right trong Excel. Hàm Vlookup, được sử dụng khi mong muốn tìm tìm một quý giá trong cột ngoài cùng bên trái của bảng, cùng trả về một cực hiếm trong và một hàng từ một cột khác mà chúng ta chỉ định. Đôi khi, hàm Vlookup đề nghị kết hợp với các hàm khác ví như If, Left, Right để cách xử lý triệt nhằm bảng tính và đưa ra công dụng mà bạn hy vọng muốn. Hãy cùng công ty chúng tôi tìm phát âm nội dung nội dung bài viết sau đây, trải qua các ví dụ cố kỉnh thể.
Bạn đang xem: Hàm vlookup kết hợp hàm if trong excel đơn giản, hiệu quả
Nội dung chính
Hướng dẫn cách áp dụng hàm Vlookup phối hợp hàm IF, Left, RightI.Cấu trúc của hàm VlookupII.Hàm Vlookup kết hợp với các hàm khác
Hướng dẫn cách sử dụng hàm Vlookup phối kết hợp hàm IF, Left, Right
I.Cấu trúc của hàm Vlookup
1.Định nghĩaHàm VLOOKUP hay còn được gọi là hàm tra cứu giúp dọc, có công dụng tìm kiếm một giá trị trong cột không tính cùng phía bên trái của bảng tính, tiếp đến trả về một cực hiếm trong cùng một hàng xuất phát điểm từ một cột không giống mà chúng ta chỉ định.
2.Cú pháp:
=VLOOKUP(Lookup_value, Table, Col_index_num,
Trong đó:
Lookup_value – giá bán trị bắt buộc tìm vào cột trước tiên của bảng.Table – Bảng để truy xuất một giá bán trị.Col_index – Cột trong bảng nhằm truy xuất một giá trị.range_lookup –Xem lấy một ví dụ tại đây:Hàm VLOOKUP vào excel cách thực hiện và ví dụ cầm thể
II.Hàm Vlookup kết hợp với các hàm khác
1.Hàm VLOOKUP kết phù hợp với hàm Right
Hàm Right, là hàm lấy ký kết tự bên bắt buộc một chuỗi bất kỳ, khi phối hợp Hlookup với Right cũng tương tự như Hlookup phối kết hợp Left giúp cho việc dò tìm kết quả nhanh và bao gồm xác.
Cú pháp hàm Right: =Right(text,n)
text: là chuỗi văn bạn dạng các bạn có nhu cầu trích xuất những ký tự.num_chars: số lượng ký tự nhưng các bạn muốn trích xuất.Chúng ta có bài tập như hình, yêu ước của bài bác là tính ĐIỂM ƯU TIÊN như sau:
Biết rằng: ĐIỂM ƯU TIÊN: căn cứ vào ký kết tự cuối của MÃ SỐ NGÀNH-ƯU TIÊN, tra trong bảng ĐIỂM ƯU TIÊN.
Tại ô H3 bọn họ nhập phương pháp như sau:
=VLOOKUP(VALUE(RIGHT(C3,1)),$G$9:$H$12,2,0)
Trong đó:
C3 là mã số ngành ưu tiên trên cột C, mặt hàng 3.RIGHT(C3,1) là giá bán trị dùng để dò tìm, khớp ứng một chữ số bên nên của Mã ưu tiên, quý hiếm dò tìm nằm tại vị trí hàng 2, bảng $G$9:$H$12( chúng ta nhấn F4 nhằm khóa bảng tính).Do cột Điểm ưu tiên họ muốn mang là số, vị vậy cơ mà các bạn cần phải thêm VALUE vào trước hàm Right, tính năng của hàm VALUE là gửi từ dạng text quý phái dạng số.Sau đó, chúng ta kéo công thức cho các ô còn lại.
2.Hàm VLOOKUP kết phù hợp với hàm Left
Hàm Left, là hàm lấy cam kết tự bên trái một chuỗi bất kỳ, khi phối kết hợp Vlookup với Left giúp cho việc dò tìm hiệu quả nhanh và thiết yếu xác.
Cú pháp hàm Left: =Left(text,n)
Text: đoạn văn phiên bản cần bóc tách ký tựNum_chars: số ký tự muốn tách tích từ bên trái. Còn nếu không nhập thì vẫn tự nhận giá trị là 1Chúng ta gồm bảng báo cáo bán hàng như sau.
Yêu cầu: mang tên MẶT HÀNG địa thế căn cứ vào MÃ MH, tra cứu vãn ở bảng ĐƠN GIÁ.
Tại ô B3, bọn họ sẽ gồm công thức như sau:
=VLOOKUP(LEFT(A3,2), $A$15:$D$21,2,0)
Trong đó:
LEFT(A3,1) là giá trị dùng để dò tìm, tương xứng hai chữ đầu tiên trên trái của mã số MH, cực hiếm dò tìm nằm ở vị trí dọc, bảng $A$15:$D$21 (các chúng ta nhấn F4 nhằm khóa bảng). Kết quả trả về đang xếp đúng tên mặt hàng.2 là cột thứ 20 ở đấy là tùy chọn tương quan chính xác.Sau đó, các bạn kéo công thức cho những ô còn lại.
3.Hàm Vlookup kết hợp với hàm IF
Hàm IF là hàm logic, có thể chấp nhận được bạn nhận xét một điều kiện nhất định và trả về quý giá mà các bạn chỉ định nếu điều kiện là TRUE và trả về một giá trị khác nếu điều kiện là FALSE.
Cú pháp hàm IF: =IF(Điều gì đấy là True, thì có tác dụng gì, nếu không thì làm cái gi khác)
Tiếp tục với ví dụ như trên, chúng ta sẽ tính cột ĐƠN GIÁ cho bảng báo cáo bán hàng.
Yêu cầu: Tính ĐƠN GIÁ: căn cứ vào MÃ MH, tra cứu vớt ở bảng ĐƠN GIÁ.
Tại ô C3, chúng ta sẽ có công thức:
=VLOOKUP(LEFT(A3,2),$A$15:$D$21,IF(RIGHT(A3,1)=”1″,3,4),0)
Trong bí quyết này, họ kết hợp giữa những hàm Left, Right cùng If để lấy đơn giá mang lại từng khía cạnh hàng.
Trong đó:
LEFT(A3,1) là giá trị dùng để làm dò tìm, tương xứng hai chữ đầu tiên trên trái của mã số MH, cực hiếm dò tìm nằm tại hàng dọc, bảng $A$15:$D$21 (các các bạn nhấn F4 nhằm khóa bảng).IF(RIGHT(A3,1)=”1″,3,4),0): nếu như món đồ có ký tự thứ nhất bên cần = 1, thì nó vẫn trả về hiệu quả đơn giá bán ở cột số 3(cột C). Ngược lại, nếu như sản phẩm có ký tự đầu tiên bên cần = một trong những khác, mà không phải là 2 thì nó đã trả về hiệu quả ở cột 4,IF(RIGHT(A3,1)=”1″,3,4),0): 0 ở đấy là tùy chọn tương quan chính xác.4.Bài tập phối hợp Vlookup với If, Left cùng Right
Thêm 1 lấy ví dụ về hàm Vlookup kết hợp với các hàm Left, Right, If cho bài tập sau đây:
Chúng ta, có một bảng tính BÁO CÁO BÁN HÀNG THÉP XÂY DỰNG như hình bên dưới.
Xem thêm: Tổng Hợp Bài Tập Modal Verb Có Đáp Án, Bài Tập Modal Verb (Động Từ Khuyết Thiếu)
Yêu cầu:
1- tên hàng: dựa vào 4 ký tự đầu của Mã hàng, tra vào BẢNG 1.
2- Mã QG: phụ thuộc 4 ký tự đầu của Mã hàng, tra trong BẢNG 1.
3- Trị giá chỉ = con số * Đơn giá. Biết rằng: Đơn giá: nhờ vào 4 ký kết tự đầu của Mã hàng, tra vào BẢNG 1, kết phù hợp với Loại hàng để lấy giá trị hòa hợp lý.
4- giá thành vận gửi = con số * giá chỉ vận chuyển. Biết rằng: giá bán vận chuyển: nhờ vào Mã QG, tra trong BẢNG 2. 5- Thành chi phí = Trị giá chỉ + giá thành vận chuyển. Nếu xuất kho trong tháng 5 thì giảm 5% Thành tiền.
Chúng ta tiến hành như sau.
Câu 1: Để lấy Tên hàng, các bạn đặt phương pháp tại ô B4
=VLOOKUP(LEFT(A4,4),$A$12:$F$16,2,0)
Trong đó:
LEFT(A4,4) là giá chỉ trị dùng làm dò tìm, khớp ứng với tư chữ đầu trên trái của mã hàng, cực hiếm dò tìm nằm ở hàng dọc, bảng $A$12:$F$16(các các bạn nhấn F4 nhằm khóa bảng).2 nghĩa là cột thứ hai của bảng 10 ở đó là tùy chọn tương quan chính xác.Sau đó, chúng ta kéo công thức cho các ô còn lại.
Câu 2: Tính Mã QG
Tại ô C3, họ sẽ tất cả công thức:
=VLOOKUP(LEFT(A4,4),$A$12:$F$16,4,0)
Tương từ như cách phân tích và lý giải ở trên, tuy nhiên ở đây bọn họ lấy mã QG sinh sống cột số 4, nên chúng ta phải lựa chọn 4.
Sau đó, chúng ta kéo công thức cho các ô còn lại.
Câu 3: Tính cột Trị giá:
Trị giá = số lượng * Đơn giá. Biết rằng: Đơn giá: phụ thuộc vào 4 ký tự đầu của Mã hàng, tra trong BẢNG 1, kết phù hợp với Loại hàng để lấy giá trị vừa lòng lý.
Tại ô F4, bọn họ đặt công thức.
=D4*VLOOKUP(LEFT(A4,4),$A$12:$F$16, IF(RIGHT(A4,1)=”C”,5,6),0)
Trong đó:
D4 là số lượngLEFT(A4,4) là giá chỉ trị dùng để dò tìm, tương xứng bốn chữ đầu trên trái của mã hàng, quý hiếm dò tìm nằm ở vị trí hàng dọc, bảng $A$12:$F$16 (các các bạn nhấn F4 để khóa bảng).IF(RIGHT(A4,1)=”C”,5,6),0): nếu như như mặt hàng có ký tự đầu tiên bên yêu cầu là “C”, thì nó đang trả về kết quả đơn giá ở cột số 5(cột E). Ngược lại, trường hợp như sản phẩm có ký tự thứ nhất bên buộc phải = một vần âm khác, mà không phải là “C” thì nó đang trả về hiệu quả ở cột 6,IF(RIGHT(A3,1)=”1″,3,4),0): 0 ở đây là tùy chọn đối sánh tương quan chính xác.Sau đó, chúng ta kéo công thức cho các ô còn lại.
Câu 4: Tính phí vận chuyển
Phí vận chuyển = con số * giá bán vận chuyển. Biết rằng: Giá vận chuyển: nhờ vào Mã QG, tra vào BẢNG
Tại ô G4, chúng ta đặt công thức
=D4*VLOOKUP(C4,$H$13:$J$15,3,0)
Trong đó:
D4 là số lượngVLOOKUP(C4,$H$13:$J$15,3,0) là giá chỉ trị dùng để làm dò tra cứu mã QG, giá trị dò tìm nằm tại hàng dọc, bảng $H$13:$J$15 (các các bạn nhấn F4 để khóa bảng), 3 ở đây là giá trị đề nghị lấy nằm tại vị trí cột số 3.0 là tùy chọn đối sánh tương quan chính xác.Câu 5: Tính cột thành tiền, biết rằng : Thành tiền = Trị giá + tổn phí vận chuyển. Nếu xuất kho trong mon 5 thì bớt 5% Thành tiền.
Tại ô H4, bọn họ đặt công thức
=IF(MONTH(E4)=5,(F4+G4)*5%,F4+G4)
Trong đó:
IF(MONTH(E4)=5,(F4+G4)*5%,F4+G4): ví như E4 =5, thì (trị giá + mức giá vận chuyển)*5%, ngượi lại chưa hẳn tháng 5 thì trị giá + phí tổn vận chuyển.
Như vậy, kemhamysophie.com vẫn hướng dẫn chúng ta cách sử dụng hàm Vlookup phối kết hợp hàm If, Left, Right thông qua các ví dụ. Hy vọng, qua các ví dụ này, vẫn giúp các bạn hiểu rõ hơn khi nào chúng ta đang cần phối kết hợp hàm Vlookup với các hàm khác, để có được kết qảu như mong muốn muốn. Chúc các bạn thành công!.
