Hàm hlookup kết hợp hàm left

     

Cách cần sử dụng hàm Vlookup kết hợp vào Excel hơi đa dạng và phong phú, linch hoạt. Trên thực tế, Khi các yên cầu công việc phức tạp yên cầu bắt buộc tùy phát triển thành, phối hợp những hàm với nhau thì mới có thể giải quyết và xử lý được những hiểu biết. Vậy hôm nay cùng sucmanhngoibut.com.vn phát âm giải pháp phối hợp hàm Vlookup!!

1. CẤU TRÚC HÀM VLOOKUP:

Hàm VLOOKUP (Tra cứu vớt dọc) tra cứu tìm một quý hiếm vào cột ko kể cùng bên trái của bảng cùng sau đó trả về một giá trị vào cùng một mặt hàng từ một cột khác nhưng các bạn chỉ định.

Bạn đang xem: Hàm hlookup kết hợp hàm left

Cú pháp: 

2. HÀM VLOOKUP KẾT HỢPhường HÀM TRONG EXCEL

2.1 SỰ KẾT HỢPhường. CỦA HÀM VLOOKUP VỚI HÀM IF 

lấy ví dụ như 1: Dùng IF nhằm so sánh quý giá vào hàm VLOOKUP

Mô tả: Dựa vào cột tài liệu tất cả trong số ô B3: C9, hãy tò mò coi tên James được nói trong ô E3 tất cả phần ttận hưởng dựa vào doanh số lớn hơn 10000$ hay không?

Các bước thực hiện:

Bước 1: Kích loài chuột vào can dự ô F3Bước 2: Áp dụng vào hàm ta nhận thấy công thức tính: =IF(VLOOKUP(E3,B3:C9,2,FALSE)>10000,“Yes”,“No”)Bước 3: Nhấn Enter được công dụng trả về nlỗi hình phía bên dưới

*

Hình: Hàm VLOOKUPhường phối hợp với IF nhằm đối chiếu giá tri

lấy ví dụ như 2: Dùng IF để cách xử lý lỗi cực hiếm vào hàm VLOOKUP

Yêu cầu: Sử dụng hàm VLOOKUP kết hợp với hàm IF để đưa ra các lỗi

Mô tả: 

Bước 1: Gán quý giá Jam mang đến ô E3. Cách 2: Để kiếm tìm lợi nhuận, hãy gán công thức: =VLOOKUP(E3,B3:C9,2,FALSE) mang đến ô F3.

lúc triển khai dứt sẽ trả về lỗi #N/A. tức là thương hiệu Jam ko tồn tại trong các ô B3:C9.

Vì vậy, Để cách xử trí lỗi này, ta tiến hành bằng phương pháp lồng hàm VLOOKUP với ISNA vào bên trong hàm IF.

Cách 1: Kích con chuột vào ô F3Bước 2: Nhập công thức: =IF(ISNA(VLOOKUP(E3,B3:C9,2,FALSE)), “Name not found”, VLOOKUP(E3,B3:C9,2,FALSE)) vào ô F3.Cách 3: Nhấn Enter được hiệu quả trả về nlỗi hình dưới

*

Hình : Hàm VLOOKUPhường kết hợp với IF để giải pháp xử lý lỗi

Cách thức làm này vẫn trả lại tên không kiếm thấy. Sử dụng hàm ISNA để giúp soát sổ coi tác dụng của VLOOKUP bao gồm đề xuất bị lỗi #N/A hay không với thực hiện điều kiện IF khớp ứng. Quý Khách rất có thể đặt tin nhắn vnạp năng lượng phiên bản không giống hoặc thậm chí một 0 hoặc trống ( “” ) như đầu ra output.

lấy một ví dụ 3: Dùng IF để tra cứu giúp hai giá bán trị trong hàm VLOOKUP

Xét ví dụ, những ô B2:B7 đựng quý hiếm cho các thành phầm nghỉ ngơi hai sản phẩm khác nhau.

Để tra cứu quý giá của thành phầm trong ô F3, bạn triển khai nhỏng sau:

Bước 1: Kích con chuột vào ô H3Cách 2: Trong ô H3 nhập công thức sau: =IF(G3= “Shop1”,VLOOKUP(F3,B2:D7,2,FALSE),VLOOKUP(F3,B2:D7,3,FALSE))Bước 3: Ấn Enter được kết quả như hình dưới

*

Hình : Hàm VLOOKUP kết hợp với IF nhằm tra cứu giúp hai giá chỉ trị

lúc triển khai phương pháp này đã trả lại $ 2000. Hàm IF kiểm tra coi cực hiếm vào ô G3 là Cửa hàng 1 tốt 2. Theo ĐK này, VLOOKUP sau đó trả về giá tương xứng mang đến sản phẩm.

2.2 HÀM VLOOKUP KẾT HỢPhường. HÀM LEFT, MID, RIGHT

a) HÀM LEFT : Hàm bóc ký tự trường đoản cú phía phía trái cùng với số lượng ký từ kéo ra bởi mình nguyên tắc.

Cú pháp: 

= LEFT(text, num_chars)Text: đoạn văn uống bạn dạng cần bóc ký kết tựNum_chars: số cam kết trường đoản cú ao ước tách bóc tích trường đoản cú phía trái. Nếu không nhập thì vẫn tự nhấn quý hiếm là 1VÍ DỤ HÀM VLOOKUPhường VỚI HÀM LEFT :Chúng ta tất cả một bảng tài liệu nlỗi hình dưới

*

Hình : ví dụ như Hàm VLOOKUPhường với Hàm LEFT

Chúng ta đang sử dụng hàm LEFT nhằm lấy Mã ngành tự cột Lớp.

Bước 1: Điền vào ô C4 công thức: =LEFT(B4,2). Ý nghĩa của công thức trên là cắt 2 ký từ bỏ sinh hoạt ô B4

Sau đó ấn Enter. Kết quả hiển thị là 2 ký kết tự CN đã có giảm ra trường đoản cú chuỗi ký tự CNTT1.

*

Hình :Hàm LEFT trong Excel

Cách 2: Các chúng ta điền vào ô D4 công thức: =VLOOKUP($C4,$G$4:$H$8,2,FALSE). $C4 là Giá trị dùng làm tra cứu kiếm, ,$G$4:$H$8 là Vùng dữ liệu tra cứu giúp, là vật dụng từ cột bắt buộc mang cực hiếm trong Vùng tài liệu tra cứu vãn, FALSE là phạm vi search tìm mang tính chất hoàn hảo và tuyệt vời nhất.

Sau kia ấn Enter. Kết quả hiển thị là Tên ngành khớp ứng với Mã Ngành.

*

Hình : Kết vừa lòng Hàm LEFT với Hàm VLOOKUP

Cách 3: Nhưng có tác dụng những điều đó sẽ rất mất thời gian, họ đã triển khai kết hợp 2 hàm là Vlookup với hàm Left cùng nhau. Tại ô E4 nhập cách làm =VLOOKUP(LEFT(B4,2),$G$4:$H$8,2,FALSE) và nhận Enter.

Nghĩa là bọn họ vẫn cố kỉnh quý hiếm dùng để search kiếm $C4 thành LEFT(B4,2).

Sau kia ấn Enter. Kết trái hiện ra là Tên ngành tương ứng với Mã Ngành.

Xem thêm: Giải Toán 10 Bài Tập Hệ Trục Tọa Độ Trong Mặt Phẳng, Bài Tập Hệ Trục Tọa Độ Lớp 10

*

Hình : Hàm VLOOKUPhường kết hợp Hàm LEFT

Bước 4: Các chúng ta kéo từ ô C4, D4, E4 xuống để các ô bên dưới tự động hóa điền công thức với tạo ra hiệu quả tương xứng.

*

Hình : Hàm VLOOKUPhường kết hợp Hàm LEFT

b) HÀM MID : Hàm cắt chuỗi lượng ký tự cụ thể từ một chuỗi văn uống bản, bắt nguồn từ vị trí vày quý khách hàng ra lệnh, dựa vào số lượng ký tự vị quý khách hàng sai bảo.

Cú pháp: 

= MID(text, start_num, num_chars)text: Chuỗi ký kết từ.start_num: Vị trí bước đầu phải cắt chuỗi ký trường đoản cú.num_chars: Số cam kết tự phải cắt ra tự chuỗi cam kết trường đoản cú.c) HÀM RIGHT : Nếu hàm LEFT nhằm bóc tách chuỗi ký kết tự phía bên trái thì hàm RIGHT trở lại, bóc chuỗi ký từ tự mặt phải vào một hàng cam kết từ nhưng người tiêu dùng lựa chọn

Cú pháp: 

= RIGHT(text, num_chars)Text: đoạn văn uống phiên bản đề xuất tách cam kết tựNum_chars: số ký kết từ mong muốn tách bóc tích tự bên đề nghị. Nếu không nhập thì vẫn từ bỏ thừa nhận cực hiếm là 1

2.3 SỬ DỤNG HÀM VLOOKUP VỚI HÀM SUMIF, SUM

Hàm SUMIF trong Excel thì tương tự như cùng với hàm SUM về khía cạnh tính tổng các quý giá. Điểm khác biệt chính là hàm SUMIF tính tổng chỉ các giá trị đáp ứng tiêu chuẩn chỉnh cơ mà các bạn đã định rõ.

Và hiện giờ, nhằm đọc bọn họ tốt mang đến với ví dụ ngay lập tức trên đây. Giả sử, bạn bao gồm Table 1: tên người chào bán cùng số ID (bảng yêu cầu tìm). Và Table 2 gồm thuộc số ID với những số lượng tương quan mang đến doanh số . Nhiệm vụ của người tiêu dùng là tìm tổng doanh số của một fan xác định ngay số ID của mình. Và, tất cả nhị nhân tố phức tạp:

Bảng Table 2 chứa được nhiều mục bao gồm cùng số ID theo thứ từ bất chợt.Bạn thiết yếu thêm cột “Tên người bán” vào bảng Table 2.

*

Hình: Vlookup kết hợp SUMIF vào Excel

Trong trường hòa hợp này, giả dụ sử dụng hàm Sumif tất yêu xuất ra hiệu quả được bởi quý giá lợi nhuận nằm tại vị trí cột Mã ID không nằm trong bảng Table 2 với ko tương quan gì cho bảng Table 1. Do đó đề nghị thực hiện hàm tìm kiếm tìm Vlookup để dò search mã số ID tương xứng tiếp đến kết phù hợp với hàm SUMIF để tính tổng cùng với ĐK là mã ID vừa lòng.

CÔNG THỨC:

=SUMIF(E:E,VLOOKUP(I2,B4:C10,2,FALSE),F:F)SumiF với Vlookup là tên hàm tính tổng cùng hàm tìm tìm theo ĐK.E:E là vùng được sàng lọc để cất những ô điều kiệnI2 là quý hiếm so sánh cùng với cột doanh thu, là quý giá dò kiếm tìm. Tại trên đây khi chúng ta đổi khác thương hiệu thì cột doanh thu cũng biến hóa theo.B2:C10 là thiết bị từ bỏ cột nên rước dự liệu nhằm dò gì mang đến quý hiếm I2 sống bên trên.Số 2 là vật dụng từ bỏ xuất cực hiếm, hiển thị lên màn hình tùy thuộc vào cột đề nghị lấy tài liệu tất cả mấy cột, bởi vì cột Mã ID ở trong phần thứ hai nên họ đặt là 2.False là phạm vi tra cứu tìm hoàn hảo và tuyệt vời nhất đến công dụng đúng đắn nỗ lực vị áp dụng True mang lại hiệu quả tương đối.

*

Hình: Hàm VLOOKUPhường phối kết hợp hàm SUMIF

2.4 HÀM VLOOKUP LỒNG HLOOKUP DÒ TÌM DỮ LIỆU

Chúng ta gồm ví như sau:

Tính ThanhTien ( biết công thức ThanhTien = SoLuong * Gia ), với SoLuong vào bảng 1 cùng Gia vào bảng 3

Ý tưởng: Để xử lý hưởng thụ đó: Ta sẽ bắt buộc căn cứ vào MaHang vào bảng 1, thực hiện dò vào bảng 2 để lấy TenHang (sử dụng hàm HLOOKUP), kế tiếp lại cần sử dụng TenHang trường đoản cú công dụng trả về của hàm HLOOKUP.. bên trên dò tra cứu vào bảng 3 nhằm lấy Gia (hàm VLOOKUP) với sau cùng là nhân cùng với SoLuong để ra ThanhTien.

*

Hình: Sử dụng Hàm VLOOKUP. và HLOOKUP

Quý khách hàng rất có thể có tác dụng điều này dễ dãi rộng với câu hỏi tạo nên cột prúc. Tuy nhiên, trong ngôi trường hòa hợp này ta đang sử dụng phối hợp hàm VLOOKUPhường và HLOOKUP. Cụ thể ta có phương pháp tại ô E5 như sau:

=D5*VLOOKUP(HLOOKUP(B5,$C$11:$F$12,2,FALSE),$B$15:$D$18,3,FALSE)Bạn có thể đọc phương pháp trên như sau: Giá trị trả về của hàm HLOOKUP. là giá trị tìm kiếm của hàm VLOOKUP..

*

Hình: Hàm VLOOKUPhường phối hợp hàm HLOOKUP

KẾT LUẬN

Trên đấy là các ví dụ thường xuyên gặp mặt nhất vào câu hỏi phối hợp các hàm với hàm VLOOKUP.. Các bạn hãy ghi lưu giữ để áp dụng cân xứng vào từng hoàn cảnh.

Xem thêm: Unit 3 Lớp 11: Listening - Listening Unit 3: A Party Bữa Tiệc

Để có thể sử dụng giỏi việc kết hợp các hàm cùng nhau, họ phải cầm cố rõ được xúc tích của vấn đề trước, sau kia mới xác định sử dụng hàm nào, đặt thứ từ bỏ các hàm tại vị trí nào.

Để hoàn toàn có thể áp dụng xuất sắc Excel vào trong công việc, bọn họ không chỉ có nắm rõ được những hàm ngoài ra bắt buộc thực hiện giỏi cả những nguyên lý của Excel. Trong khi còn không ít hàm cải thiện khác ví như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Công nắm như Data validation, Pivot table, Power nguồn Query…

GÓC HỌC TẬP

THAM KHẢO THÊM


Chuyên mục: