Hướng dẫn cách kết hợp hàm Vlookup và Sumif

Sử dụng vlookup và chức năng sumif giúp bạn tìm rất nhanh các thừa số đã chỉ định và tính tổng các giá trị tương ứng cùng một lúc. trong bài đăng này, biểu đồ sẽ giới thiệu với các bạn 2 công thức vlookup và tính tổng kết quả giá trị đầu tiên hoặc tất cả được theo hàng hoặc cột trong Excel.

Hàm Sumif và Vlookup được sử dụng nhiều trong Excel, việc kết hợp 2 hàm này trong Excel là rất phổ biến. Tuy nhiên để hiểu hàm cũng như tại sao phải tích hợp và sử dụng 2 hàm Sumif và Vlookup trong excel này, điểm mạnh và điểm yếu của chúng thì không phải ai cũng hiểu. Bạn đang xem bài viết: hHướng dẫn cách kết hợp vlookup và sumif. chức năng

Cách kết hợp hàm Sumif và Vlookup, vlookup tích hợp hàm sumif

Trước khi đi vào bài viết cụ thể về hàm Sumif và Vlookup, cũng như cách sử dụng hàm Sumif có sẵn Vlookup, chúng ta cần tìm hiểu qua về 2 hàm trên dành cho người đã học excel. Và nếu chúng ta mới làm quen với excel thì bài viết này cũng rất đáng để lưu lại và học Sumif, Vlookup trước.

Hàm sumif là gì?

Tổng . chức năng là một chức năng cho phép bạn tính tổng các kết quả trong cột. Tuy nhiên, trong một số trường hợp bạn lược bỏ một số kết quả trong dãy, lúc này hàm Sumif được sử dụng thay cho Sum. Với điều kiện được chèn vào dãy, hàm Sumif cho phép người dùng loại bỏ và bỏ qua các giá trị không thỏa mãn điều kiện và tính tổng các giá trị thỏa mãn còn lại.

Bí mật hàm sumif: SUMIF(phạm vi, Tiêu chí, tổng_phạm vi)

Phạm vi: Là vùng Selected chứa các ô điều kiện. Tiêu chuẩn: Là điều kiện để thực hiện chức năng này tổng_dải: Khu vực được tính tổng.

Để biết rõ hơn về hàm SUMIF cũng như các ví dụ rõ ràng, bạn đọc có thể tham khảo thêm qua nội dung giới thiệu và hàm SumIF mà Taimienphi.vn đã tổng hợp.

Hàm Vlookup là gì?

Khác với Tổng . chức năng, hàm Vlookup không tính toán bất kỳ dữ liệu nào mà chỉ tìm kiếm dữ liệu đó. Chức năng cho phép người dùng Tra cứu dữ liệu về một loại chuối cụ thể trong các điều kiện cụ thể để kết quả hiển thị đạt yêu cầu, thông thường hàm Vlookup trong Excel thường được dùng để tra cứu các mã như mã sinh viên, mã nhân sự thuận tiện cho việc truy xuất dữ liệu.

Công thức hàm vlookup: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Lookup_value: kết quả dùng để phát hiện Bảng_mảng: Bảng giá trị tra cứu, được đặt thành Địa chỉ tuyệt đối (có dấu $ phía trước bằng cách nhấn F4) Col_index_num: Thứ tự của cột lấy dữ liệu trên bảng kết quả. phạm vi_tra cứu: Phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).

Chi tiết hơn về hàm Vlookup cũng như ví dụ tham khảo về hàm này cho người mới làm quen với Excel, các bạn có thể đọc thêm các bài viết về hàm này. hàm vlookup mà Taimienphi.vn đã từng đề cập.

Kết hợp VLOOKUP và SUMIF. chức năng

tích hợp VLOOKUP và SUMIF . chức năng

Trong ví dụ này, chúng ta sẽ sử dụng hàm SUMIF và VLOOKUP để tính tổng sản lượng Cam trong tháng 1, tháng 2 và tháng 3 thông qua các công thức sau:

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

Một khi bạn nhập công thức này, bạn cần hết sức cẩn thận vì nó là một phương thức mảng. Vì vậy, sau khi nhập công thức, bạn phải trực tiếp sử dụng tổ hợp phím CTRL + SHIFT + ENTER để có thể nhập công thức và trả về kết quả chính xác.

Để hiểu rõ hơn về ví dụ này, chúng ta sẽ đo lường phương thức mảng này của hàm SUMIF trong Excel bằng cách đưa ra 3 công thức tương đương sau:

Phần số 2,3,4 trong công thức mảng trên có nghĩa là cột 2,3,4 trong mảng dữ liệu tương ứng A1:I8. Đọc đến đây, bạn có thể đặt ngay câu hỏi, tại sao con người không sử dụng thủ thuật sau để thực hiện phép tính tổng?

Mục tiêu của việc nói những bí mật phức tạp này, con người sẽ phục vụ để tạo báo cáo hoặc bảng điều khiển có khả năng. Nếu mọi người sử dụng phương pháp tính tổng dữ liệu đơn giản bằng hàm SUM, khi ta có một ô chứa dữ liệu về các loại hàng hóa, muốn thay đổi ô này để tính tổng sản lượng của một loại hàng hóa. , người ta phải sửa lại toàn bộ công thức SUM cho phù hợp.

làm báo cáo chính xác

Tóm tắt dữ liệu mà không cần thêm cột bổ sung, tích hợp LOOKUP và SUM . chức năng

Trong ví dụ bên dưới, người có 2 bảng dữ liệu, 1 bảng bao gồm thông tin sản phẩm và đơn giá; Bảng thứ hai bao gồm dữ liệu về khách hàng, sản phẩm, số lượng hàng hóa mà người mua hàng đã mua. Chúng ta sẽ có nhu cầu tính tổng giá trị của 1 người mua hàng

chúng ta sẽ cần sử dụng thêm cột

Các công thức cần nhập như sau:

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

H2=F2*G2

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

Trong trường hợp không thể thêm cột phụ, chúng ta có thể sử dụng công thức hàm SUM thay cho hàm SUMIF 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 kết hợp hàm SUM và VLOOKUP này:

• Cột A trong trang tính phải được sắp xếp theo thứ tự tăng dần hoặc từ A đến Z để hàm LOOKUP trong Excel cung cấp cho mọi người kết quả đúng.
• Công thức chính xác được nhập bằng tổ hợp phím CTRL + SHIFT + ENTER. Nếu không sử dụng tổ hợp phím này, chúng ta có thể thay thế hàm SUM trong Microsoft Excel bằng hàm SUMPRODUCT

Công thức tích phân này được hiểu như thế nào?

• Phần TÌM KIẾM($E$2:$E$8,$A$2:$A$8,$B$2:$B$8) là trường dành cho người tương ứng dưới dạng cột đơn giá trong ảnh chụp màn hình ở trên.
• Phần $F$2:$F$8 là mảng nội dung số lượng hàng hóa
• Phần ($D$2:$D$8=K1) khi nhập vào sẽ tạo ra một mảng giá trị true và false, khi lấy mảng này để nhân với 1 thì áp dụng quy tắc cụ thể như sau: ( TRUE được định nghĩa bởi Microsoft Excel là 1, FALSE được định nghĩa bởi Microsoft Excel là 0)

Và cuối cùng hàm SUM trong Excel sẽ tính tổng và cho ta kết quả cuối cùng

Sử dụng hàm SUMIF với nhiều OR . điều kiện

ví dụ 1. SUMIF + SUMIF

Tùy chọn đơn giản nhất là tính tổng các kết quả được trả về bởi một số hàm SUMIF. Ví dụ: thủ thuật phần mềm sau đây về cách sử dụng hàm SUMIF minh họa cách tính tổng số sản phẩm do Mike và John cung cấp:

=SUMIF(C2:C9, “Mike”, D2:D9) + SUMIF(C2:C9, “John”, D2:D9)

ví dụ 1

Như bạn có thể thấy, hàm SUMIF đầu tiên trong Excel hiển thị số tiền tương ứng với “Mike” và hàm SUMIF khác trả về số tiền ảnh hưởng đến “John” và sau đó bạn chỉ cần cộng hai kết quả này lại.

Ví dụ 2. SUM và SUMIF với đối số mảng

Các tùy chọn được đề cập ở trên rất dễ dàng và thực sự hiệu quả khi chỉ có một số điều kiện. tuy nhiên một phương pháp SUMIF + SUMIF sẽ được phát triển thành nhiều phần mềm khác Nếu chúng ta muốn tính tổng các giá trị với nhiều điều kiện OR. Trong trường hợp này, phơi sáng được cho là mượt mà hơn là sử dụng một đối số làm điều kiện mảng tương ứng trong hàm SUMIF, như sau:

Bạn có thể bắt đầu tính tổng bằng cách liệt kê tất cả các điều kiện có thể, được phân tách bằng dấu phẩy và sau đó chúng tôi đặt chúng trong dấu ngoặc nhọn, đây là điều mà Microsoft Excel gọi là mảng.

Trong ví dụ trên, nếu bạn muốn tính tổng số hàng hóa do John, Mike và Pete mang về, điều kiện dữ liệu mảng của bạn sẽ như sau “John”, “Mike”, “Pete”. Và hàm SUMIF đầy đủ của Excel là: =SUMIF(C2:C9, “John”, “Mike”, “Pete”, D2:D9).

Đối số mảng gồm 3 giá trị được chọn chắc chắn phải có trong công thức Hàm SUMIF trả về 3 kết quả độc lập tương ứng, nhưng do con người viết công thức trong 1 ô nên nó sẽ chỉ trả về kết quả khớp chính xác đầu tiên – tức là tổng số Sản phẩm được sản xuất bởi John. Để nó hoạt động, bạn phải sử dụng một vài thủ thuật trong đó – lồng công thức hàm SUMIF của chúng ta vào hàm SUM, cụ thể như sau:

=SUM(SUMIF(C2:C9, “John”, “Mike”, “Pete”, D2:D9))

VĐ 2

Như bạn có thể thấy, một điều kiện mảng cụ thể như ở trên làm cho công thức gọn hơn nhiều so với việc sử dụng các hàm SUMIF + SUMIF và chúng có khả năng cho phép bạn thêm bao nhiêu giá trị tồn tại trong mảng tùy ý. ở đó.

Công thức hữu ích này sẽ hoạt động với các số hoặc thậm chí là kết quả văn bản. Ví dụ: nếu thay vì bạn có tên nhà cung cấp ở cột C, bạn chỉ có dữ liệu ID nhà phân phối như 1, 2, 3, v.v… thì cách kết hợp hàm SUMIF với hàm SUM của bạn sẽ như sau: =SUM(SUMIF (C2:C9, 1,2,3, D2:D9))

Không giống như giá trị văn bản được đưa ra trong Microsoft Excel, các số không cần đưa vào phần được đưa ra trong dấu ngoặc kép trong đối số mảng.

Xem thêm bài viết: Sử dụng hàm Sumifs với điều kiện ngày tháng trong excel

Ví dụ 3. TÓM TẮT và SUMIF . chức năng

Trong trường hợp, bạn buộc phải liệt kê các điều kiện trong một số ô thay vì chỉ định trực tiếp trong công thức, lúc này bạn có thể sử dụng hàm SUMIF kết hợp với hàm SUMPRODUCT trong Excel để nhân nhiều thành phần trong công thức. mảng chụp rõ ràng của nó và sau đó trả về tổng số lượng của các sản phẩm đó.

=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))

Trong trường hợp ô G2:G4 là ô chứa điều kiện bắt buộc của chúng ta, thông tin về tên nhà phân phối trong tình huống này, được hiển thị trong ảnh chụp màn hình của ketnoiviec.net bên dưới.

Tuy nhiên, tất nhiên, sẽ không có điểm dừng nếu chúng ta muốn liệt kê các kết quả trong một điều kiện mảng của hàm SUMIF:

=SUMPRODUCT(SUMIF(C2:C9, “Mike”, “John”, “Pete”, D2:D9))

Kết quả trả về ngay lập tức bằng cả hai phương pháp trên sẽ giống như những gì bạn nhìn thấy trong hình:

Kết quả

Một số lỗi thường gặp khi kết hợp hàm SUMIF với hàm VLOOKUP

Lỗi #N/A

– nguyên nhân của lỗi #N/A là do đối số bị thiếu hoặc đối số phạm vi tổng không phải là một phạm vi.

– Khắc phục lỗi này bằng cách kiểm tra xem các đối số đã nhập có đầy đủ và chính xác hay không khi sử dụng hàm SUMIF.

Lỗi #N/A

0 . trả lại lỗi

– nguyên nhân lỗi là do các đối số khai báo sai kiểu dữ liệu.

– Khắc phục lỗi này bằng cách kiểm tra xem các đối số có được nhập chính xác hay không.

0 . trả lại lỗi

Lỗi #ERROR

– nguyên nhân của lỗi này là do cú pháp hàm sai.

– Khắc phục lỗi này bằng cách kiểm tra các đối số và biểu diễn có chính xác hay không.

Lỗi #ERROR

Xem thêm bài viết: Cách dùng hàm Vlookup giữa 2 sheet khác nhau

bản tóm tắt

Trên đây là toàn bộ bài hướng dẫn tích hợp hàm Sumif và hàm Vlookup trên Excel Bangxephang tổng hợp. Hai hàm Sumif và hàm Vlookup là những hàm tính toán, chọn lọc dữ liệu vô cùng cơ bản trong Excel. Và việc tích hợp 2 chức năng sẽ giúp người dùng tìm kiếm dữ liệu nhanh hơn, không cần tính toán thủ công dù dữ liệu đã chọn có bị thay đổi.