Dùng vlookup và hàm sumif giúp bạn rất nhanh tìm ra các yếu tố được chỉ định và tính tổng các giá trị tương ứng cùng một lúc. trong bài viết này, Bảng xếp hạng sẽ giới thiệu cho bạn hai công thức để vlookup và tính tổng các thành quả trước tiên hoặc tất cả các giá trị ổn trong các hàng hoặc cột trong Excel.
Hàm Sumif và Vlookup được sử dụng rất nhiều trong Excel, sự kết hợp giữa hai hàm này trong Excel là rất phổ biến. tuy vậy để hiểu rõ được chức năng cũng giống như việc vì sao phải tích hợp và dùng 2 hàm Sumif và hàm 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: Hướng dẫn cách kết hợp hàm vlookup và sumif
Cách kết hợp hàm Sumif và Vlookup, hàm sumif tích hợp vlookup
Trước khi đi vào bài viết cụ thể về hàm Sumif và Vlookup cũng giống như sử dụng hàm Sumif tích hợp Vlookup chúng ta cần điểm qua một tí về 2 hàm trên cho những người đã từng học excel. Còn nếu chúng ta là người mới bắt đầu học excel thì bài viết này cũng đáng để lưu lại và học Sumif, Vlookup trước nhé.
Hàm sumif là gì?
Hàm Sum là hàm cho phép bạn tính tổng các thành quả trong cột. mặc dù vậy trong một vài trường hợp bạn bỏ qua một số thành quả có trong dãy, lúc này hàm Sumif được dùng thay thế 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ỏ, bỏ qua các giá trị không thảo mãn điều kiện và tính tổng các giá trị thỏa mãn còn lại.
bí quyết hàm Sumif: SUMIF(range, Criteria, sum_range)
Range: Là vùng được Lựa chọn chứa các ô điều kiện. Criteria: Là điều kiện để thực hiện hàm này Sum_range: Vùng cần tính tổng.
Để biết rõ hơn về hàm SUMIF cũng như các VD rõ ràng bạn đọc có thể đọc thêm qua bài content giới thiệu và hàm SumIF mà Taimienphi.vn đã biên soạn.
Hàm Vlookup là gì?
Khác với Hàm Sum, hàm Vlookup không tính toán bất cứ một dữ liệu nào mà chỉ tìm kiếm dữ liệu đấy. Hàm cho phép người sử dụng tra cứu dữ liệu trên một chuối cụ thể theo các điều kiện xác định sao cho kết quả hiển thị thỏa mãn, thông thường hàm Vlookup trong Excel thường được sử dụng để tra cứu những loại mã số như mã học sinh, 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: thành quả sử dụng để dò tìm Table_array: Bảng giá trị dò, để ở dạng địa chỉ Tuyệt đối (có dấu $ phía trước bằng việc nhấn F4) Col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng thành quả dò. Range_lookup: Phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE đồng nghĩa với 0 (dò tìm tuyệt đối).
Chi tiết hơn về hàm Vlookup cũng giống như ví dụ để tham khảo thêm về hàm này cho người mới học Excel bạn đọc xem thêm bài content đề cập về hàm Vlookup mà Taimienphi.vn từng đề cập đến.
Kết hợp hàm VLOOKUP và SUMIF
tích hợp hàm VLOOKUP và SUMIF
Trong VD này, chúng ta sẽ sử dụng cách sử dụng hàm SUMIF và VLOOKUP đi tính tổng sản lượng của Cam trong tháng 1, tháng 2, tháng 3 qua các công thức sau đây:
=SUM(VLOOKUP( A2 , A1:I8 , 2,3,4 , FALSE ))
một khi nhập công thức này, các bạn cần hết sức lưu ý vì Nó là bí quyết mảng. Vậy nên sau khi nhập công thức, bạn phải trực tiếp dùng tổ hợp phím CTRL + SHIFT + ENTER để có khả năng nhập được bí quyết và được trả về kết quả đúng.
Để các chúng ta có thể hiểu về VD này hơn, chúng ta sẽ đo đạt bí quyết mảng này của hàm SUMIF trong Excel bằng việc đư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 số 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 tương ứng A1:I8. Đọc đến đây, có thể các bạn có thể lập tức có câu hỏi, tại sao con người không sử dụng bí quyết sau đây để thực thi tính tổng
=SUM(B2:D2)
mục tiêu của việc nói ra những bí quyết phức tạp này, con người sẽ phục vụ cho việc tạo báo cáo hoặc có khả năng là dashboard. nếu con người sử dụng một bí quyết tính tổng dữ liệu đơn thuần bằng hàm SUM, khi chúng ta có 1 ô chứa dữ liệu về các kiểu hàng hoá, mong muốn thay đổi ô này để thực hiện tính tổng sản lượng của 1 hàng hóa, con người phải chỉnh sửa toàn bộ bí quyết SUM theo.
Với công thức mảng tích hợp hợp lý giữa hàm SUM và VLOOKUP như ở trên, chúng ta có thể tạo ra 1 báo cáo chuẩn xác về sản lượng của các sản phẩm 1 cách nhanh chóng như sau:
làm ra 1 báo cáo chuẩn xác
Xem thêm bài viết : Hướng dẫn sử dụng hàm if kết hợp vlookup và left
Tổng hợp dữ liệu không thêm cột phụ, tích hợp hàm LOOKUP và hàm SUM
Trong ví dụ phía dưới, con người có 2 bảng dữ liệu, 1 bảng bao gồm thông tin hàng hóa và đơn giá; bảng thứ hai bao gồm dữ liệu về khách hàng, sản phẩm và số lượng hàng hóa người mua hàng đã mua. chúng ta sẽ có nhu cầu đi tính tổng giá trị của 1 người mua hàng
Tổng hợp dữ liệu không thêm cột phụ, tích hợp hàm LOOKUP và hàm SUM
thông thường, chúng ta sẽ cần sử dụng cột phụ như sau:
chúng ta sẽ cần dùng cột phụ
Các công thức cần nhập vào 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ực hiện thêm được cột phụ, chúng ta có thể 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 bí quyết kết hợp hàm SUM và hàm 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 cho con người thành quả đúng.
- công thức chuẩn xác được nhập vào bằng tổ hợp phím CTRL + SHIFT + ENTER. nếu không thực hiện dùng tổ hợp phím này, chúng ta có thể thay hàm SUM trong Microsoft Excel bằng hàm SUMPRODUCT
công thức tích hợp này được hiểu như thế nào?
- Phần LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8) là trường cho con người kết quả tương ứng như cột đơn giá trong hình chụp phía trên
- Phần $F$2:$F$8 là mảng nội dung số lượng các hàng hóa
- Phần ($D$2:$D$8=K1) khi được nhập sẽ tạo ra 1 mảng gồm các giá trị đúng và sai, khi lấy mảng này đem nhân với 1 số, thì quy tắc cụ thể sau đây được áp dụng: (TRUE được Microsoft Excel khái niệm là 1, FALSE được Microsoft Excel khái niệm 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
Dùng hàm SUMIF có nhiều điều kiện OR
ví dụ 1. SUMIF + SUMIF
phương án được đánh giá là đơn giản nhất chính là tính tổng các kết quả trả về bởi một vài hàm SUMIF. Ví dụ: bí quyết phần mềm cách dùng hàm SUMIF sau đây sẽ 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 thấy, hàm SUMIF trong Excel đầu tiên cho biết số lượng tương ứng với “Mike”, còn cách dùng hàm SUMIF kia thì trả lại số tiền ảnh hưởng đến “John” và sau đó thì bạn chỉ việc cộng hai kết quả này lại.
Ví dụ 2. SUM và SUMIF với đối số mảng
Các phương án được nêu trên trên rất dễ dàng và thực sự hiệu quả cao khi chỉ có một vài điều kiện. tuy nhiên một bí quyết SUMIF + SUMIF sẽ được phát triển ra thành rất 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, cách tiếp xúc được cho là mượt hơn chính là sử dụng một đối số như điều kiện mảng tương ứng trong hàm SUMIF, như sau:
Bạn có thể bắt tay vào làm tính tổng bằng cách liệt kê toàn bộ các điều kiện nên có, được ngăn cách bởi dấu phẩy và sau đó chúng ta đặt chúng trong dấu ngoặc nhọn, điều này được Microsoft Excel gọi là mảng.
Trong ví dụ được nêu ở trước, Nếu bạn muốn thực hiện tính tổng các hàng hóa do John, Mike và Pete mang lại, điều kiện dữ liệu mảng của bạn sẽ như thế này “John”, “Mike”, “Pete”. Và hàm SUMIF trong Excel đầy đủ là: =SUMIF (C2: C9, “John”, “Mike”, “Pete”, D2: D9).
Đối số mảng bao 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ề tương ứng ba kết quả độc lập, tuy nhiên vì con người đã viết công thức trong một ô duy nhất, nó sẽ chỉ trả lại kết quả tương ứng chính xác đầu tiên – tức là tổng số sản phẩm do John mang lại. Để nó hoạt động, bạn phải dùng trong đó một vài mẹo – lồng công thức hàm SUMIF của chúng ta trong một hàm SUM, cụ thể như sau:
= SUM (SUMIF (C2: C9, “John”, “Mike”, “Pete”, D2: D9))
VD 2
Như bạn thấy, một điều kiện mảng cụ thể như trên đã làm cho công thức nhỏ gọn hơn nhiều so với việc sử dụng hàm SUMIF + SUMIF, và chúng có khả năng cho phép bạn thêm nhiều giá trị như bạn muốn hiện hữu trong mảng đấy.
công thức có ích này sẽ chạy với các con số hoặc thậm chí là các thành quả văn bản. Ví dụ: nếu như thay vì bạn có thông tin về tên các nhà cung cấp trong cột C, mà bạn chỉ có phần dữ liệu ID nhà phân phối như 1, 2, 3, v.v … thì bí quyết hàm SUMIF Kết hợp với SUM của bạn sẽ như sau: = SUM (SUMIF (C2: C9, 1,2,3, D2: D9))
Không giống như các giá trị văn bản được cho trong Microsoft Excel, các con số không cần phải được gồm có phần được cho trong dấu nháy kép trong đối số mảng.
Xem thêm bài viết: Sử dụng hàm Sumifs điều kiện ngày tháng trong excel
Ví dụ 3. Hàm SUMPRODUCT và hàm SUMIF
Trong trường hợp, bạn buộc phải liệt kê các điều kiện trong vài ô thay vì chỉ định chúng trực tiếp trong công thức, thì 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ác mảng nắm rõ ràng của nó rồi sau đó trả lại tổng số lượng của các sản phẩm đó.
= SUMPRODUCT (SUMIF (C2: C9, G2: G4, D2: D9))
Trong hoàn cảnh ở ô G2: G4 là các ô chứa điều kiện cần có của chúng ta, thì thông tin về tên nhà phân phối trong hoàn cảnh này, được minh họa như trong ảnh chụp màn hình của ketnoiviec.net phía dưới.
tuy nhiên tất nhiên, không có gì ngăn cản Nếu chúng ta muốn thực hiện liệt kê các thành 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 sau đó bởi cả hai bí quyết trên sẽ giống như những gì bạn thấy trong hình:
Kết quả
Một vài 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 xuất hiện lỗi #N/A là vì nhập thiếu đối số hoặc đối số sum_range không phải dải số.
– Khắc phục lỗi này bằng việc kiếm tra lại các đối số coi đã nhập đủ và đúng chưa khi dùng hàm SUMIF.
Lỗi #N/A
Lỗi trả về 0
– lý do xảy ra 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 duyệt các đối số coi đã nhập đúng hay chưa.
Lỗi trả về 0
Lỗi #ERROR
– lý do của lỗi này là do nhập sai cú pháp hàm.
– Khắc phục lỗi này bằng việc kiểm tra các đối số và cách trình bày coi đã nhập đúng hay chưa.
Lỗi #ERROR
Xem thêm bài viết: Cách dùng hàm Vlookup giữa 2 sheet khác nhau
Tổng kết
Trên đây là bài hướng dẫn đầy đủ cách tích hợp hàm Sumif và hàm Vlookup trên Excel được Bangxephang tổng hợp. Hai hàm Sumif và hàm Vlookup là hàm tính toán, hàm chọn lựa dữ liệu vô cùng căn bản trên Excel. Và việc tích hợp 2 hàm lại sẽ giúp người dùng có thể search dữ liệu nhanh hơn, mà không cần tính toán thủ công mặc dù đổi dữ liệu chọn lựa đi chăng nữa.
