Trong bài viết này, mình vẫn trả lời các bạn làm cho nạm như thế nào nhằm có thể phối kết hợp các hàm VLOOKUP., SUM và SUMIF nhằm dò kiếm tìm và tính tổng dựa vào một số ĐK.

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

Mục lục

Kết đúng theo hàm VLOOKUP và SUMIFTổng phù hợp tài liệu không thêm cột prúc, phối kết hợp hàm LOOKUP.. và SUMKết hợp hàm VLOOKUPhường. cùng SUMIF tra cứu giúp và tổng vừa lòng dữ liệu theo điều kiệnTính tổng nhiều ĐK ngơi nghỉ cùng 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 này, chắc hẳn các bạn đã thạo cùng với hàm VLOOKUPhường với hàm SUMIF rồi. Vậy nên bạn có thể bắt đầu vào ngay lập tức ví dụ thứ nhất.

Trong ví dụ này, bọn họ vẫn đi tính tổng sản lượng của Cam trong tháng 1, tháng 2, mon 3 bằng phương pháp áp dụng phương pháp sau đây:

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

Sau Lúc nhập công thức này, các bạn xem xét bởi vì đó là cách làm mảng, vậy đề nghị sau khi nhập bí quyết, bạn nên sử dụng tổng hợp phím CTRL + SHIFT + ENTER để rất có thể nhập được cách làm với tất cả kết quả đúng.

Để những chúng ta cũng có thể đọc rộng, bọn họ đang so sánh bí quyết mảng này bằng cách đưa ra 3 công thức 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 nghỉ ngơi trong công thức mảng bên trên tức là cột 2,3,4 vào mảng tài liệu A1:I8. Đọc cho đây, hoàn toàn có thể những bạn cũng có thể sẽ sở hữu được câu hỏi, vì sao họ không áp dụng công thức sau đây nhằm tính tổng

=SUM(B2:D2)

Mục đích chỉ dẫn phần lớn bí quyết phức tạp này, bọn họ đã Ship hàng mang lại câu hỏi tạo report hoặc dashboard. Nếu họ thực hiện một cách làm tính tổng đối chọi thuần bằng hàm SUM, khi bọn họ có 1 ô cất các nhiều loại sản phẩm hoá, mong muốn chuyển đổi ô này để tính tổng sản lượng của một mặt hàng, bọn họ nên biến hóa phương pháp SUM theo.

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

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

Tổng hòa hợp tài liệu ko thêm cột phú, kết hợp hàm LOOKUPhường cùng SUM:

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

Excel cải thiện, phối hợp hàm SUM với LOOKUP

Bình hay, họ vẫn yêu cầu sử dụng cột phú nhỏng sau:

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

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

H2=F2*G2

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

Trong trường vừa lòng ko thêm được cột prúc, chúng ta cũng có thể thực hiện phương pháp sau trên 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 ý Lúc thực hiện bí quyết phối kết hợp SUM và LOOKUPhường này:

Cột A buộc phải được thu xếp theo trang bị tự tăng ngày một nhiều hoặc từ bỏ A mang lại Z, để hàm LOOKUP mang lại họ giá trị đúng.Công thức được nhập lệ bởi tổ hợp phím CTRL + SHIFT + ENTER Nếu ko 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 gọi như thế nào?

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

Tới phía trên, bạn có thể tải về file excel để theo dõi và quan sát mang đến dễ tại đây

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

Ví dụ:

Excel nâng cao, phối kết hợp VLOOKUP.. với SUMIF

Trong ví dụ này, ta có 2 bảng, 1 bảng là tên gọi Telesale với ID của họ, 1 bảng khác chỉ gồm ID và doanh số. Nhiệm vụ ngơi nghỉ ví dụ này: buộc phải đi tính tổng lợi nhuận của bất cứ Telesale nào nhờ vào thương hiệu của họ.

Công thức bọn họ vẫn áp dụng ở chỗ này là

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

VLOOKUPhường. sẽ sở hữu được trách nhiệm gửi lại mã ID của Telesale cùng với tên tương ứng, nhờ vào ID này họ vận dụng SUMIF như một ví dụ dễ dàng và đơn giản thông thường.

Tính tổng nhiều ĐK sinh hoạt cùng 1 cột:

Chúng ta gồm ví dụ tính tổng sau đây, điều kiện tính tổng gần như năm ở một cột: Tính tổng của các thanh toán với đầu mã là 111 với 131

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

Trong khi nhằm vận dụng Excel vào các bước một cách tác dụng thì các bạn còn buộc phải sử dụng tốt những hàm, những lý lẽ khác của Excel:

Một số hàm cơ bản thường xuyên chạm mặt như:

SUMIF, SUMIFS nhằm tính tổng theo 1 ĐK, các điều kiệnCOUNTIF, COUNTIFS để những thống kê, đếm theo một ĐK, nhiều điều kiệnCác hàm cách xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…Các hàm dò tra cứu tsay đắm chiếu Index+Match, hàm SUMPRODUCT…

Một số qui định hay được sử dụng như:

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

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

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

Để góp chúng ta dễ hình dung được khi nào thì áp dụng hàm Sumif phối kết hợp hàm vlookup, hãy coi ví dụ sau đây:

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

Chúng ta có 1 bảng dữ liệu từ bỏ A1:C15, có các cột Mã mặt hàng, Ngày cung cấp, Số lượng bán được của từng mặt hàng.

Xem thêm: Cách Cài Đặt Bình Luận Trên Facebook, Xếp Hạng Bình Luận Trên Facebook Là Gì

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

Nlỗi vậy:

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

Cách làm nlỗi sau:

Viết hàm SUMIF

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

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

Viết hàm VLOOKUP

Chúng ta ý muốn tmê say chiếu Mã sản phẩm phụ thuộc Tên sản phẩm, do kia hàm VLOOKUP sẽ viết nhỏng sau:

=VLOOKUP(giá trị tra cứu tìm, vùng tđắm đuối chiếu, cột chứa kết quả, thủ tục tra cứu kiếm)

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

Như vậy ta gồm VLOOKUP(F11,E2:F8,2,0)

Lúc kết hợp nhị hàm với nhau ta gồm cách làm tại ô F13 nhỏng sau:

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

*
Cách sử dụng hàm Vlookup trong Excel

Khi viết hàm SUMIF kết hợp với hàm VLOOKUP, chúng ta rất cần được xác định: hàm VLOOKUP.. sẽ đặt ở địa chỉ tđê mê số như thế nào vào hàm SUMIF. Đôi khi tốt gặp mặt duy nhất bao gồm là: Điều khiếu nại vào hàm SUMIF không xác định được 1 cách thẳng, mà nên tsi mê chiếu cho tới 1 bảng tính nào đó.

Hướng dẫn phương pháp thực hiện hàm Sumifs phối kết hợp hàm vlookup vào Excel

Hàm SUMIFS là một trong hàm hết sức hữu ích trong Excel giúp bạn có thể tính tổng theo nhiều điều kiện cùng lúc. Nhưng vào một vài trường hợp điều kiện của báo cáo lại ko xác định được một phương pháp thẳng mà lại đề xuất ttê mê chiếu tới 1 vùng dữ liệu khác. Khi đó chúng ta cần được kết hợp hàm SUMIFS với hàm VLOOKUP. Trong bài viết này Học Excel Online vẫn lí giải các bạn biết sử dụng hàm Sumifs kết hợp hàm vlookup vào Excel.

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

*

Trong kinh nghiệm bên trên, bọn họ thấy tất cả tới 3 điều kiện:

Vì vậy để rất có thể tính được số lượng bán thỏa mãn mặt khác cả 3 điều kiện trên, họ vẫn nên sử dụng cho tới hàm SUMIFS.

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

Cách viết hàm SUMIFS

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

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

húng ta mong tđam mê chiếu Mã mặt hàng nhờ vào Tên mặt hàng, do đó hàm VLOOKUPhường sẽ viết nhỏng sau:

=VLOOKUP(giá trị tra cứu kiếm, vùng tđắm say chiếu, cột chứa công dụng, cách làm tìm kiếm kiếm)

Giá trị tìm kiếm: là tên gọi sản phẩm tại ô F13Vùng ttê mê chiếu: Vùng bảng E2:F8 (hoàn toàn có thể bỏ qua mất mẫu tiêu đề)Cột đựng kết quả: cột Mã sản phẩm là cột thiết bị 2Pmùi hương thức tra cứu kiếm: search tìm đúng đắn theo thương hiệu mặt hàng => nhập số 0

Vậy nên ta gồm VLOOKUP(F13,E2:F8,2,0)

Khi kết hợp 2 hàm này, họ gồm hiệu quả 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 để thấy tác dụng của hàm SUMIFS tại ô F15 thay đổi đúng không nhỉ nhé.

Kết luận

Hàm SUMIFS rất có thể viết được cùng với khôn xiết nhiều ĐK. Khi đó bọn họ yêu cầu xác định rõ: Điều kiện kia rất có thể xác định trực tiếp được trong bảng tài liệu ko.

Nếu có: Ttê mê chiếu trực tiếp điều kiện đó (như quý hiếm Từ ngày, đến ngày sống ví dụ trên)Nếu không: Phải áp dụng những hàm phối hợp (nlỗi hàm VLOOKUP.., hàm DATE, hàm EOMONTH…) để giúp từ các điều kiện kia gián tiếp tạo nên 1 điều kiện rất có thể xác định trực tiếp được trong vùng tài liệu gốc.

Đây là một Một trong những kỹ thuật căn bạn dạng giúp lập report theo nhiều điều kiện một phương pháp chính xác, hối hả.

Chúc những bạn áp dụng tốt kỹ năng này vào quá trình nhé!

Rất các kiến thức phải ko nào? Toàn cỗ đông đảo kỹ năng và kiến thức này các bạn rất nhiều hoàn toàn có thể học được trong khóa học EX101 – Excel tự cơ bản cho 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 một phương pháp tương đối đầy đủ, cụ thể. mà còn không hề có số lượng giới hạn về thời gian học hành phải chúng ta cũng có thể thoải mái học bất kể dịp làm sao, dễ dãi tra cứu vớt lại kỹ năng và kiến thức khi yêu cầu. Hiện nay hệ thống đang sẵn có khuyến mãi không hề nhỏ cho chính mình Lúc ĐK tsi mê gia khóa đào tạo. Chi tiết xem tại: thuychien.vn.Online

Bài viết liên quan

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *