Hàm vlookup trong excel – những điều cần nhớ!

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Hàm vlookup trong excel – những điều cần nhớ!

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Hàm VLOOKUP trong Excel không thể tìm ở bên trái. Nó luôn luôn tìm giá trị nằm trong cột cận trái của dải ô cần tìm (table_array).

Trong công thức VLOOKUP, tất cả giá trị đều không phân biệt dạng chữ, có nghĩa là ký tự viết hoa và viết thường đều được xử lý như nhau.

Nếu giá trị cần tìm nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của dải ô cần tìm, thì hàm VLOOKUP sẽ trả về lỗi #N/A.

Nếu câu lệnh thứ ba (col_index_num) nhỏ hơn 1, thì hàm VLOOKUP sẽ trả về lỗi #VALUE!. Trong trường hợp, nó lớn hơn số cột trong dải ô cần tìm (table_array), thì công thức sẽ trả về lỗi #REF!.

Hãy sử dụng tham chiếu ô tuyệt đối trong câu lệnh table_array của công thức VLOOKUP để có đúng dải ô cần tìm khi xử lý công thức. Hãy cân nhắc việc sử dụng tên cho dải ô hay cho bảng trong Excel như một phương án thay thế.

Khi tìm kiếm sự phù hợp tương đối (range_lookup được cải đặt thành TRUE hay loại bỏ), hãy luôn lọc dữ liệu ở cột đầu tiên trong dải ô cần tìm theo thứ tự tăng dần.

Trong bài viết này, mình sẽ hướng dẫn các bạn làm thế nào để có thể kết hợp các hàm VLOOKUP, SUM và SUMIF để dò tìm và tính tổng dựa trên 1 số điều kiện.

1. Kết hợp hàm Vlookup và Sumif

Trong ví dụ này, chúng ta sẽ đi tính tổng sản lượng của Cam trong tháng 1, tháng 2, tháng 3 bằng cách sử dụng công thức sau đây:

=SUM(VLOOKUP( A2 , A1:I8 , {2,3,4} , FALSE ))

Sau khi nhập công thức này, các bạn lưu ý vì đây là công thức mảng, vậy nên sau khi nhập công thức, bạn phải sử dụng tổ hợp phím CTRL + SHIFT + ENTER để có thể nhập được công thức và có kết quả đúng.

Để các bạn có thể hiểu hơn, chúng ta sẽ phân tích công thức mảng này bằng cách đưa ra 3 công thức tương đương sau đây:

=VLOOKUP( A2 , A1:I8 , 2 , FALSE ) + VLOOKUP( A2 , A1:I8 , 3 , FALSE ) + VLOOKUP( A2 , A1:I8 , 4 , FALSE )

Phần {2,3,4} ở trong công thức mảng phía trên nghĩa là cột 2,3,4 trong mảng dữ liệu A1:I8. Đọc đến đây, có thể các bạn có thể sẽ có câu hỏi, tại sao chúng ta không sử dụng công thức sau đây để tính tổng

=SUM(B2:D2)

Mục đích đưa ra những công thức phức tạp này, chúng ta sẽ phục vụ cho việc tạo báo cáo hoặc dashboard. Nếu chúng ta sử dụng một công thức tính tổng đơn thuần bằng hàm SUM, khi chúng ta có 1 ô chứa các loại hàng hoá, muốn thay đổi ô này để tính tổng sản lượng của 1 sản phẩm, chúng ta phải thay đổi công thức SUM theo.

Với công thức mảng kết hợp hàm SUM và VLOOKUP như ở trên, chúng ta có thể tạo ra 1 báo cáo về sản lượng của các sản phẩm 1 cách nhanh chóng như sau:

 

Từ ví dụ phía trên, tất nhiên, ta có thể thay hàm SUM bằng 1 số hàm khác để phục vụ mục đích của chúng ta như hàm AVERAGE, hàm MIN, hàm MAX hoặc thực hiện các phép tính toán khác … Và lưu ý vì các công thức này đều là công thức mảng, nên bạn cần sử dụng tổ hợp phím tắt CTRL + SHIFT + ENTER khi nhập công thức này trong Excel.

2. Tổng hợp dữ liệu không thêm cột phụ, kết hợp hàm Lookup và Sum

Chúng ta có ví dụ sau, trong ví dụ này, chúng ta có 2 bảng dữ liệu, 1 bảng gồm có sản phẩm và đơn giá; bảng thứ 2 bao gồm khách hàng, sản phẩm và số lượng sản phẩm khách hàng đã mua. Chúng ta sẽ muốn đi tính tổng giá trị của 1 khách hàng

Bình thường, chúng ta sẽ cần dùng cột phụ như sau:

Các công thức như sau:

G2=VLOOKUP(E2,bang_SP,2,FALSE)

H2=F2*G2

K2=SUMIF(D:D,K1,H:H)

Trong trường hợp không thêm được cột phụ, chúng ta có thể sử dụng công thức sau tại K2:

K2=SUM(LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8)*$F$2:$F$8*($D$2:$D$8=K1))

Chú ý khi sử dụng công thức kết hợp SUM và LOOKUP này:

  1. Cột A phải được sắp xếp theo thứ tự tăng dần hoặc từ A đến Z, để hàm LOOKUP cho chúng ta giá trị đúng.
  2. Công thức được nhập vào bằng tổ hợp phím CTRL + SHIFT + ENTER. Nếu không sử dụng tổ hợp phím này, bạn có thể thay hàm SUM bằng hàm SUMPRODUCT

Công thức này được hiểu như thế nào?

  1. Phần LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8) cho chúng ta kết quả như cột đơn giá trong hình chụp phía trên
  2. Phần $F$2:$F$8 là mảng số lượng các sản phẩm
  3. Phần ($D$2:$D$8=K1) tạo ra 1 mảng gồm các giá trị đúng và sai, khi lấy mảng này nhân với 1 số, thì quy tắc sau đây được áp dụng: (TRUE được định nghĩa là 1, FALSE được định nghĩa là 0)
  4. Và cuối cùng, hàm SUM sẽ tính tổng và cho ta kết quả cuối cùng

3. Kết hợp hàm Vlookup và Sumif tra cứu và tổng hợp dữ liệu theo điều kiện

Chúng ta có ví dụ sau đây

Trong ví dụ này, ta có 2 bảng, 1 bảng là tên Telesale và ID của họ, 1 bảng khác chỉ có ID và doanh số. Nhiệm vụ ở ví dụ này: cần đi tính tổng doanh số của bất kì Telesale nào dựa vào tên của họ.

Công thức chúng ta sẽ sử dụng ở đây là

=SUMIF(D:D,VLOOKUP(H1,A1:B8,2,FALSE),E:E)

VLOOKUP sẽ có nhiệm vụ đưa lại mã ID của Telesale với tên tương ứng, dựa vào ID này chúng ta áp dụng SUMIF như một ví dụ đơn giản bình thường. Bây giờ, các bạn có thể download file excel kèm theo để tìm hiểu kĩ hơn

4. Tính tổng nhiều điều kiện ở cùng 1 cột

Chúng ta có ví dụ tính tổng sau đây, điều kiện tính tổng đều năm ở 1 cột: Tính tổng của các giao dịch với đầu mã là 111 và 131

Để làm được điều này, chúng ta có 3 cách tính, với cách mà công thức có dấu {}, nghĩa là công thức mảng, bạn cần nhấn tổ hợp phím tắt CTRL + SHIFT + ENTER sau khi nhập công thức để được kết quả.

=SUMIF(A:A,”111*”,C:C)+SUMIF(A:A,”131*”,C:C)

{=SUM(SUMIF(A:A,{“111*”,”131*”},C:C))}

{=SUM(C2:C14*(–(LEFT(A2:A14,3)={“111″,”131”})))}

Vậy là trong bài này, chúng ta đã cùng nhau tìm hiểu một số cách kết hợp công thức, hàm tính tổng với các hàm dò tìm để giải quyết nhu cầu làm báo cáo mà không cần dùng thêm cột phụ.

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ