Hàm sumif và vlookup

Trong bài viết này, bản thân sẽ khuyên bảo các bạn làm cho rứa như thế nào để rất có thể kết hợp những hàm VLOOKUPhường, SUM với SUMIF để dò kiếm tìm cùng tính tổng dựa trên một số ít ĐK.

Bạn đang xem: Hàm sumif và vlookup

Mục lục

Kết vừa lòng hàm VLOOKUPhường với SUMIFTổng hợp tài liệu ko thêm cột phụ, kết hợp hàm LOOKUPhường cùng SUMKết thích hợp hàm VLOOKUPhường cùng SUMIF tra cứu vớt với tổng phù hợp tài liệu theo điều kiệnTính tổng nhiều điều kiện ngơi nghỉ thuộc 1 cột

Kết vừa lòng hàm VLOOKUP cùng SUMIF

Trước Khi bước đầu vào bài xích này, chắc rằng các bạn sẽ thạo cùng với hàm VLOOKUP.. và hàm SUMIF rồi. Vậy nên bạn cũng có thể ban đầu vào tức thì ví dụ thứ nhất.

Trong ví dụ này, bọn họ sẽ đi tính tổng sản lượng của Cam trong thời điểm tháng 1, tháng 2, mon 3 bằng cách áp dụng bí quyết sau đây:

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

Sau Khi nhập cách làm này, các bạn chú ý do đây là bí quyết mảng, vậy đề nghị sau khoản thời gian nhập bí quyết, các bạn nên thực hiện tổng hợp phím CTRL + SHIFT + ENTER nhằm rất có thể nhập được bí quyết với tất cả tác dụng đúng.

Để những bạn cũng có thể hiểu hơn, chúng ta đang so với bí quyết mảng này bằng cách đưa ra 3 bí quyết tương tự 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 sinh sống trong bí quyết mảng bên trên tức thị cột 2,3,4 trong mảng tài liệu A1:I8. Đọc mang đến phía trên, hoàn toàn có thể các chúng ta có thể sẽ sở hữu thắc mắc, tại sao bọn họ không thực hiện phương pháp tiếp sau đây nhằm tính tổng

=SUM(B2:D2)

Mục đích đưa ra phần lớn công thức tinh vi này, họ vẫn phục vụ mang đến bài toán chế tạo báo cáo hoặc dashboard. Nếu họ thực hiện một phương pháp tính tổng solo thuần bởi hàm SUM, khi bọn họ có 1 ô cất những loại mặt hàng hoá, hy vọng thay đổi ô này nhằm tính tổng sản lượng của 1 sản phẩm, bọn họ cần biến hóa phương pháp SUM theo.

Với bí quyết mảng phối kết hợp hàm SUM cùng VLOOKUPhường nhỏng nghỉ ngơi bên trên, chúng ta có thể tạo thành 1 báo cáo về sản lượng của các sản phẩm một cách mau lẹ như sau:

Từ ví dụ phía trên, tất yếu, ta hoàn toàn có thể thế hàm SUM bằng một số ít hàm khác nhằm Giao hàng mục tiêu của họ như hàm AVERAGE, hàm MIN, hàm MAX hoặc thực hiện các phép tính toán thù không giống … Và để ý bởi vì những cách làm này phần nhiều là bí quyết mảng, đề xuất bạn cần sử dụng tổng hợp phím tắt CTRL + SHIFT + ENTER lúc nhập cách làm này vào Excel.

Tổng vừa lòng tài liệu ko thêm cột prúc, kết hợp hàm LOOKUP và SUM:

Chúng ta có ví dụ sau, trong ví dụ này, bọn họ bao gồm 2 bảng tài liệu, 1 bảng có tất cả sản phẩm cùng solo giá; bảng thứ hai bao gồm người tiêu dùng, thành phầm cùng số số lượng sản phẩm khách hàng vẫn sở hữu. Chúng ta vẫn mong mỏi đi tính tổng mức của một khách hàng

Excel nâng cao, phối kết hợp hàm SUM và LOOKUP

Bình thường xuyên, chúng ta đã đề xuất sử dụng cột prúc nhỏng sau:

Các cách làm như sau:

G2=VLOOKUP(E2,bang_SPhường,2,FALSE)

H2=F2*G2

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

Trong ngôi trường vừa lòng không thêm được cột prúc, bạn cũng có thể sử dụng phương pháp 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))

Crúc ý khi sử dụng cách làm kết hợp SUM với LOOKUP. này:

Cột A bắt buộc được bố trí theo lắp thêm trường đoản cú tăng mạnh hoặc từ bỏ A cho Z, nhằm hàm LOOKUP đến họ quý hiếm đúng.Công thức được nhập vào bởi tổng hợp phím CTRL + SHIFT + ENTER Nếu ko sử dụng tổ hợp phím này, chúng ta có thể cố gắng hàm SUM bởi hàm SUMPRODUCT

Công thức này được gọi như thế nào?

Phần LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8) đến chúng ta công dụng nlỗi cột đối kháng giá trong hình chụp phía trênPhần $F$2:$F$8 là mảng số lượng những sản phẩmPhần ($D$2:$D$8=K1)  tạo ra 1 mảng bao gồm những giá trị đúng với sai, Lúc mang mảng này nhân với 1 số, thì phép tắc tiếp sau đây được áp dụng: (TRUE được quan niệm là một, FALSE được khái niệm là 0)Và cuối cùng, hàm SUM công thêm tổng và cho ta tác dụng cuối cùng

Tới đây, bạn có thể tải về tệp tin excel để theo dõi đến dễ tại đây

Kết hòa hợp hàm VLOOKUP. và SUMIF tra cứu vớt cùng tổng hòa hợp dữ liệu theo điều kiện

Ví dụ:

Excel nâng cấp, kết hợp VLOOKUP.. với SUMIF

Trong ví dụ này, ta có 2 bảng, 1 bảng là tên Telesale cùng ID của họ, 1 bảng khác chỉ gồm ID và doanh thu. Nhiệm vụ sinh hoạt ví dụ này: đề nghị đi tính tổng doanh thu của bất kì Telesale làm sao phụ thuộc thương hiệu của mình.

Công thức chúng ta đang áp dụng tại đây là

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

VLOOKUP sẽ có được trọng trách gửi lại mã ID của Telesale cùng với thương hiệu tương xứng, phụ thuộc ID này bọn họ áp dụng SUMIF nlỗi một ví dụ đơn giản và dễ dàng bình thường.

Tính tổng nhiều điều kiện sống thuộc 1 cột:

Chúng ta gồm ví dụ tính tổng tiếp sau đây, ĐK tính tổng đa số năm ở một cột: Tính tổng của các giao dịch với đầu mã là 111 với 131

Để có tác dụng được điều đó, chúng ta gồm 3 phương pháp tính, cùng với phương pháp mà cách làm gồm lốt , tức là phương pháp mảng, bạn cần dấn tổ hợp phím tắt CTRL + SHIFT + ENTER sau khoản thời gian nhập phương pháp để đượ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 đã bên nhau tò mò một số trong những phương pháp phối hợp công thức, hàm tính tổng với những hàm dò kiếm tìm để giải quyết và xử lý nhu yếu làm cho report mà lại ko đề nghị dùng thêm cột prúc. Hi vọng bài viết bổ ích mang lại quá trình của chúng ta nhất là trong quá trình kế toán thù, lập report vào kho.

Hình như nhằm ứng dụng Excel vào quá trình một bí quyết hiệu quả thì các bạn còn đề xuất thực hiện giỏi các hàm, những dụng cụ không giống của Excel:

Một số hàm cơ bạn dạng hay chạm mặt như:

SUMIF, SUMIFS nhằm tính tổng theo 1 ĐK, nhiều điều kiệnCOUNTIF, COUNTIFS nhằm thống kê, đếm theo một điều kiện, các điều kiệnCác hàm giải pháp xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò tìm tmê man chiếu Index+Match, hàm SUMPRODUCT…

Một số chính sách thường dùng như:

Định dạng theo điều kiện với Conditional formattingThiết lập ĐK nhập dữ liệu cùng với Data ValidationCách đặt Name cùng thực hiện Name vào công thứcLập report cùng với Pivot Table…

Các ví dụ Excel nâng cao: phối kết hợp VLOOKUP.., SUM cùng SUMIF

Cách thực hiện hàm Sumif phối kết hợp hàm vlookup

Để giúp chúng ta dễ dàng hình dung được khi nào thì sử dụng hàm Sumif phối hợp hàm vlookup, hãy xem ví dụ sau đây:

Cách sử dụng hàm Sumif phối hợp hàm vlookup

Chúng ta có một bảng tài liệu từ bỏ A1:C15, có những cột Mã hàng, Ngày buôn bán, Số lượng bán được của từng món đồ.

Xem thêm: Những Nhân Vật Trong Free Fire 2021, Tổng Hợp Các Nhân Vật Trong Free Fire 2020

Tuy nhiên đề nghị của chúng ta là tính tổng số lượng buôn bán theo Tên phương diện hàng được chọn trong ô F11. Trong bảng dữ liệu A1:C15 không tồn tại cột tên mặt hàng. Muốn biết Tên phương diện hàng đó ứng với Mã sản phẩm nào, họ phải tsi mê chiếu trong bảng E1:F8.

Nlỗi vậy:

Điều khiếu nại không xác định được một cách thẳng nhưng phải tđam mê chiếu tới 1 bảng khác => Sử dụng VLOOKUP xác định điều kiện

Các bước làm nlỗi sau:

Viết hàm SUMIF

Cấu trúc của hàm SUMIF gồm những: =SUMIF(Range, Criteria, Sum_range)

Range: vùng chứa điều kiện. Ở vào bảng A1:C15 chỉ bao gồm cột Mã sản phẩm là có liên quan tới ĐK => Chọn vùng A2:A15 (không lựa chọn loại tiêu đề)Criteria: ĐK của đề bài là Tên hàng. Nhưng Range lại là Mã mặt hàng. Do đó yêu cầu tđắm đuối chiếu ra Mã hàng nhờ vào Tên mặt hàng => Sử dụng hàm VLOOKUPhường.Sum_range: tác dụng nên tính là Số lượng. Do kia chọn vùng C2:C15

Viết hàm VLOOKUP

Chúng ta ao ước tmê man chiếu Mã hàng dựa vào Tên mặt hàng, do đó hàm VLOOKUP sẽ viết nlỗi sau:

=VLOOKUP(quý hiếm tìm kiếm, vùng tham mê chiếu, cột chứa tác dụng, cách thức search kiếm)

Giá trị tra cứu kiếm: là tên sản phẩm tại ô F11Vùng tđê mê chiếu: Vùng bảng E2:F8 (rất có thể bỏ lỡ loại tiêu đề)Cột đựng kết quả: cột Mã mặt hàng là cột sản phẩm 2Pmùi hương thức tra cứu kiếm: tra cứu tìm chính xác theo tên hàng => nhập số 0

do đó ta bao gồm VLOOKUP(F11,E2:F8,2,0)

Lúc phối kết hợp nhị hàm với nhau ta gồm công thức tại ô F13 nhỏng sau:

=SUMIF(A2:A15,VLOOKUP(F11,E2:F8,2,0),C2:C15)

Cách cần sử dụng hàm Vlookup trong Excel

Lúc viết hàm SUMIF kết hợp với hàm VLOOKUP, bọn họ cần được xác định: hàm VLOOKUP.. sẽ đặt tại vị trí tđắm đuối số như thế nào vào hàm SUMIF. Đôi khi tuyệt chạm mặt tốt nhất thiết yếu là: Điều kiện trong hàm SUMIF ko xác định được một cách thẳng, nhưng mà bắt buộc tham chiếu cho tới 1 bảng tính nào đó.

Hướng dẫn cách thực hiện hàm Sumifs kết hợp hàm vlookup trong Excel

Hàm SUMIFS là một trong những hàm rất hữu ích trong Excel góp chúng ta cũng có thể tính tổng theo nhiều điều kiện đồng thời. Nhưng trong một số trong những trường hợp ĐK của report lại không xác định được một cách trực tiếp nhưng bắt buộc tđam mê chiếu tới 1 vùng tài liệu không giống. Khi kia bọn họ rất cần được kết hợp hàm SUMIFS với hàm VLOOKUP. Trong nội dung bài viết này Học Excel Online đã khuyên bảo chúng ta biết thực hiện hàm Sumifs phối hợp hàm vlookup vào Excel.

Trước hết hãy xét ví dụ sau đây:

Trong đòi hỏi trên, chúng ta thấy tất cả tới 3 điều kiện:

Vì vậy nhằm rất có thể tính được con số phân phối thỏa mãn đồng thời cả 3 ĐK bên trên, họ sẽ đề xuất cần sử dụng cho tới hàm SUMIFS.

Nhưng ở điều kiện 3 là ĐK liên quan cho tới tên hàng. Tên sản phẩm không có sẵn trong bảng tài liệu A1:C15 nhưng mà buộc phải xác định trải qua bảng E1:F8. Từ thương hiệu sản phẩm (sinh sống ô F13) tsay đắm chiếu ra mã mặt hàng tương ứng. Sử dụng mã hàng đó tsi chiếu tiếp tới cột Mã hàng trong bảng A1:C15 để ra tác dụng đến hàm SUMIFS.

Cách viết hàm SUMIFS

Cấu trúc hàm SUMIFS cùng với 3 ĐK bao gồm:

=SUMIFS(Vùng tính tổng, vùng ĐK thứ nhất, điều kiện 1, vùng điều kiện thứ hai, điều kiện 2, vùng điều kiện vật dụng 3, điều kiện 3)

húng ta mong mỏi tyêu thích chiếu Mã mặt hàng phụ thuộc vào Tên mặt hàng, do kia hàm VLOOKUP. sẽ viết nlỗi sau:

=VLOOKUP(cực hiếm tìm kiếm kiếm, vùng tmê mệt chiếu, cột đựng kết quả, cách làm tìm kiếm kiếm)

Giá trị tìm kiếm kiếm: là tên gọi sản phẩm tại ô F13Vùng tsi chiếu: Vùng bảng E2:F8 (có thể bỏ lỡ dòng tiêu đề)Cột cất kết quả: cột Mã hàng là cột trang bị 2Phương thức tìm kiếm: tra cứu kiếm chính xác theo thương hiệu sản phẩm => nhập số 0

bởi vậy ta tất cả VLOOKUP(F13,E2:F8,2,0)

khi phối kết hợp 2 hàm này, bọn họ có tác dụng tại ô F15 nhỏng sau:

=SUMIFS(C2:C15,B2:B15,">="&F11,B2:B15,"

Hãy test thay đổi tên sản phẩm tại ô F13 để xem tác dụng của hàm SUMIFS tại ô F15 thay đổi đúng không ạ nhé.

Kết luận

Hàm SUMIFS hoàn toàn có thể viết được với rất nhiều điều kiện. Khi đó chúng ta đề xuất xác định rõ: Điều kiện đó hoàn toàn có thể xác định trực tiếp được trong bảng dữ liệu ko.

Nếu có: Tmê mệt chiếu trực tiếp điều kiện đó (nhỏng cực hiếm Từ ngày, đến ngày ở ví dụ trên)Nếu không: Phải sử dụng các hàm kết hợp (nhỏng hàm VLOOKUP, hàm DATE, hàm EOMONTH…) sẽ giúp đỡ tự các điều kiện kia gián tiếp tạo ra 1 điều kiện có thể xác định trực tiếp được vào vùng tài liệu gốc.

Đây là một trong những chuyên môn căn uống bạn dạng góp lập báo cáo theo nhiều điều kiện một biện pháp đúng đắn, nhanh chóng.

Chúc các bạn áp dụng xuất sắc kiến thức này vào các bước nhé!

Rất nhiều kỹ năng và kiến thức cần không nào? Toàn cỗ hồ hết kiến thức và kỹ năng này các bạn gần như rất có thể học được trong khóa học EX101 – Excel từ bỏ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp cho bạn khối hệ thống kỹ năng và kiến thức một biện pháp đầy đủ, chi tiết. hơn nữa không hề gồm giới hạn về thời gian học tập buộc phải bạn có thể dễ chịu và thoải mái học bất kể dịp như thế nào, dễ dàng tra cứu vớt lại kỹ năng và kiến thức Lúc yêu cầu. Trong thời điểm này khối hệ thống đang sẵn có ưu đãi không hề nhỏ cho chính mình lúc đăng ký tđắm say gia khóa học. Chi ngày tiết xem tại: indotech.vn.Online