Hàm Vlookup Kết Hợp Hàm Sumif

     

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

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

Mục lục

Kết vừa lòng hàm VLOOKUP cùng SUMIFTổng hợp dữ liệu không thêm cột phụ, phối hợp hàm LOOKUP cùng SUMKết hợp hàm VLOOKUP với SUMIF tra cứu với tổng hợp tài liệu theo điều kiệnTính tổng nhiều điều kiện ở cùng 1 cột

Kết đúng theo hàm VLOOKUP và SUMIF

Trước khi bắt đầu vào bài này, chắc chắn hẳn chúng ta đã thành thạo với hàm VLOOKUP hàm SUMIF rồi. Vậy nên chúng ta có thể bắt đầu vào ngay lấy ví dụ đầu tiên.

Trong ví dụ này, chúng ta 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 phương pháp sử dụng cách làm sau đây:

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

Sau lúc nhập cách làm 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, chúng ta phải sử dụng tổ hợp phím CTRL + SHIFT + ENTER để rất có thể nhập được cách làm và có hiệu quả đúng.

Để các bạn có thể hiểu hơn, họ sẽ phân tích phương pháp mảng này bằng phương pháp đư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ách làm mảng phía trên nghĩa là cột 2,3,4 trong mảng tài liệu A1:I8. Đọc mang lại đây, hoàn toàn có thể các bạn cũng có thể sẽ bao gồm câu hỏi, trên sao chúng ta không áp dụng công thức dưới đây để tính tổng

=SUM(B2:D2)

Mục đích gửi ra đều công thức tinh vi này, bọn họ sẽ ship hàng cho bài toán tạo báo cáo hoặc dashboard. Nếu bọn họ sử dụng một công thức tính tổng solo thuần bởi hàm SUM, khi bọn họ có 1 ô chứa những loại hàng hoá, muốn đổi khác ô này để tính tổng sản lượng của 1 sản phẩm, họ phải đổi khác công thức SUM theo.

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

Từ lấy ví dụ phía trên, vớ nhiên, ta rất có thể thay hàm SUM bằng 1 số hàm không giống để ship hàng mục đích của bọn họ như hàm AVERAGE, hàm MIN, hàm MAX hoặc tiến hành các phép đo lường và thống kê khác … Và xem xét vì các công thức này số đông là cách làm mảng, nên bạn phải 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 hợp dữ liệu không thêm cột phụ, phối hợp hàm LOOKUP và SUM:

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

Excel nâng cao, kết hợp hàm SUM cùng LOOKUP

Bình thường, họ sẽ yêu cầu dùng cột phụ như sau:

Các bí quyết như sau:

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

H2=F2*G2

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

Trong trường đúng theo không thêm được cột phụ, bạn cũng có thể sử dụng cách làm 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))

Chú ý khi thực hiện công thức phối hợp SUM cùng LOOKUP này:

Cột A đề nghị được sắp xếp theo máy tự tăng nhiều hoặc từ bỏ A mang đến Z, nhằm hàm LOOKUP cho bọn họ giá trị đúng.Công thức được nhập vào bằng 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ể thay hàm SUM bằng hàm SUMPRODUCT

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

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

Tới đây, chúng ta có thể download file excel nhằm theo dõi mang lại dễ tại đây

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

Ví dụ:

Excel nâng cao, kết hợp VLOOKUP cùng SUMIF

Trong lấy ví dụ này, ta tất cả 2 bảng, 1 bảng là tên Telesale với ID của họ, 1 bảng khác chỉ tất cả ID và doanh số. Trọng trách ở ví dụ này: đề nghị đi tính tổng doanh thu của bất kỳ Telesale nào nhờ vào tên của họ.

Công thức bọn họ sẽ sử dụng ở chỗ này là

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

VLOOKUP sẽ sở hữu nhiệm vụ đưa lại mã ID của Telesale với thương hiệu tương ứng, nhờ vào ID này họ áp dụng SUMIF như một ví dụ đơn giản bình thường.

Tính tổng nhiều đk ở 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 đều năm ở 1 cột: Tính tổng của những giao dịch cùng với đầu mã là 111 và 131

Để làm cho được điều này, chúng ta có 3 cách tính, với phương pháp mà công thức gồm dấu , nghĩa là công thức mảng, bạn phải nhấn tổng hợp phím tắt CTRL + SHIFT + ENTER sau khoản thời gian nhập cách làm để đượ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 bác này, chúng ta đã thuộc 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 mong làm report mà không cần dùng thêm cột phụ. Hi vọng nội dung bài viết có ích cho các bước của các bạn đặc biệt là trong quá trình kế toán, lập báo cáo trong kho.

Ngoài ra để áp dụng Excel vào các bước một cách công dụng thì các bạn còn bắt buộc sử dụng tốt các hàm, các công chũm khác của Excel:

Một số hàm cơ bạn dạng thường gặp mặt như:

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

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

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

Các lấy một ví dụ Excel nâng cao: kết hợp VLOOKUP, SUM cùng SUMIF

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

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

*
Cách sử dụng hàm Sumif kết hợp hàm vlookup

Chúng ta có 1 bảng dữ liệu từ A1:C15, gồm những cột Mã hàng, Ngày bán, con số bán được của từng mặt hàng.

Xem thêm: Cách Xác Định Góc Giữa 2 Đường Thẳng Trong Không Gian Lớp 12

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

Như vậy:

Điều kiện không xác định được một cách trực tiếp mà bắt buộc tham chiếu cho tới 1 bảng không giống => Sử dụng VLOOKUP xác định điều kiện

Cách có tác dụng như 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 điều kiện. Ở trong bảng A1:C15 chỉ có cột Mã sản phẩm là có liên quan tới điều kiện => lựa chọn vùng A2:A15 (không chọn loại tiêu đề)Criteria: điều kiện của đề bài là thương hiệu hàng. Nhưng mà Range lại là Mã hàng. Do đó đề xuất tham chiếu ra Mã hàng nhờ vào Tên mặt hàng => áp dụng hàm VLOOKUP.Sum_range: công dụng cần tính là Số lượng. Do đó lựa chọn vùng C2:C15

Viết hàm VLOOKUP

Chúng ta mong muốn tham chiếu Mã hàng dựa vào Tên hàng, do đó hàm VLOOKUP sẽ viết như sau:

=VLOOKUP(giá trị tìm kiếm kiếm, vùng tham chiếu, cột cất kết quả, thủ tục tìm kiếm)

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

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

Khi phối hợp hai hàm cùng nhau ta gồm công thức tại ô F13 như sau:

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

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

Khi viết hàm SUMIF kết phù hợp với hàm VLOOKUP, bọn họ cần đề nghị xác định: hàm VLOOKUP sẽ đặt tại địa điểm tham số nào vào hàm SUMIF. Thường thì hay chạm mặt nhất thiết yếu là: Điều khiếu nại trong hàm SUMIF ko xác định được 1 cách trực tiếp, mà nên tham chiếu cho tới 1 bảng tính nào đó.

Hướng dẫn cách sử dụng hàm Sumifs phối kết hợp hàm vlookup vào Excel

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

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

*

Trong yêu ước trên, chúng ta thấy có tới 3 điều kiện:

Vì vậy để rất có thể tính được con số bán thỏa mãn đồng thời cả 3 điều khiếu nại trên, chúng ta sẽ đề xuất dùng cho tới hàm SUMIFS.

Nhưng ở điều kiện 3 là điều kiện tương quan tới thương hiệu hàng. Tên hàng không tồn tại sẵn trong bảng tài liệu A1:C15 mà cần xác định trải qua bảng E1:F8. Tự tên sản phẩm (ở ô F13) tham chiếu ra mã hàng tương ứng. áp dụng mã hàng đó tham chiếu tiếp cho tới cột Mã sản phẩm trong bảng A1:C15 để ra tác dụng cho hàm SUMIFS.

Cách viết hàm SUMIFS

Cấu trúc hàm SUMIFS với 3 điều kiện bao gồm:

=SUMIFS(Vùng tính tổng, vùng điều kiện vật dụng 1, điều kiện 1, vùng điều kiện trang bị 2, điều khiếu nại 2, vùng điều kiện trang bị 3, điều kiện 3)

húng ta mong mỏi tham chiếu Mã hàng phụ thuộc Tên hàng, do đó hàm VLOOKUP đã viết như sau:

=VLOOKUP(giá trị tìm kiếm kiếm, vùng tham chiếu, cột cất kết quả, cách thức tìm kiếm)

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

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

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

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

*

Hãy thử 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 có thể viết được với khôn xiết nhiều điều kiện. Khi đó họ phải xác định rõ: Điều kiện đó rất có thể xác định trực tiếp được vào bảng dữ liệu không.

Nếu có: Tham chiếu trực tiếp điều kiện đó (như 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ư hàm VLOOKUP, hàm DATE, hàm EOMONTH…) để góp từ các điều kiện đó gián tiếp tạo nên 1 điều kiện có thể xác định trực tiếp được vào vùng dữ liệu gốc.

Xem thêm: Bài 75 Trang 106 Sgk Toán 8 Tập 1, Giải Bài 75 Trang 106

Đây là trong những kỹ thuật căn bạn dạng giúp lập báo cáo theo nhiều điều khiếu nại một cách thiết yếu xác, cấp tốc chóng.

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

Rất nhiều kỹ năng và kiến thức phải ko nào? cục bộ những kiến thức và kỹ năng này chúng ta đều hoàn toàn có thể học được vào khóa học EX101 – Excel từ bỏ cơ phiên bản tới siêng gia của học Excel Online. Đây là khóa học giúp cho bạn hệ thống kiến thức một phương pháp đầy đủ, chi tiết. Rộng nữa không hề có giới hạn về thời hạn học tập nên chúng ta có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu vớt lại kỹ năng khi cần. Hiện nay hệ thống đang có ưu đãi siêu lớn cho bạn khi đăng ký tham gia khóa học. Cụ thể xem tại: gift4u.vn.Online