Hàm vlookup kết hợp hàm sumif

     

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

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

Mục lục

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

Trong ví dụ như này, chúng ta sẽ đi tính tổng sản lượng của Cam hồi tháng 1, tháng 2, tháng 3 bằng cách sử dụng phương pháp 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 lưu ý vì đó là công thức mảng, vậy nên sau khi nhập công thức, bạn phải sử dụng tổng hợp phím CTRL + SHIFT + ENTER để rất có thể nhập được công thức và có tác dụng đúng.

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

=SUM(B2:D2)

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

Với công thức mảng phối hợp hàm SUM với VLOOKUP như sinh hoạt trên, bạn cũng có thể tạo ra 1 báo cáo về sản lượng của các sản phẩm một cách nhanh chóng như sau:

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

Tổng hợp dữ liệu không thêm cột phụ, phối kết hợp hàm LOOKUP cùng SUM:

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

Excel nâng cao, phối hợp hàm SUM cùng LOOKUP

Bình thường, bọn họ sẽ đề nghị dùng cột phụ như sau:

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

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

H2=F2*G2

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

Trong trường thích hợp không thêm được cột phụ, chúng ta có thể sử dụng 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))

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

Cột A cần được thu xếp theo lắp thêm tự tăng dần đều hoặc trường đoản cú A mang lại Z, để hàm LOOKUP cho bọn họ giá trị đúng.Công thức được nhập vào bằng tổ hợp phím CTRL + SHIFT + ENTER Nếu không sử dụng tổng hợp phím này, chúng ta cũng có thể thay hàm SUM bởi hàm SUMPRODUCT

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

Phần LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8) cho họ kết đúng như cột 1-1 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 các giá trị đúng và sai, khi lấy mảng này nhân với 1 số, thì quy tắc 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 sẽ tính tổng và mang lại ta hiệu quả cuối cùng

Tới đây, chúng ta cũng có thể download tệp tin excel nhằm theo dõi đến dễ tại đây

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

Ví dụ:

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

Trong lấy ví dụ này, ta gồm 2 bảng, 1 bảng là tên gọi Telesale với ID của họ, 1 bảng khác chỉ tất cả ID và doanh số. Trách nhiệm ở lấy một ví dụ này: yêu cầu đi tính tổng doanh số của bất kì Telesale nào phụ thuộc vào tên của họ.

Công thức chúng ta sẽ sử dụng tại chỗ này là

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

VLOOKUP sẽ có nhiệm vụ chuyển 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ư 1 ví dụ đơn giản dễ dàng bình thường.

Tính tổng nhiều đk ở thuộc 1 cột:

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

Để làm cho được điều này, chúng ta có 3 phương pháp tính, với bí quyết mà công thức tất cả dấu , nghĩa là công thức mảng, bạn phải nhấn tổ hợp phím tắt CTRL + SHIFT + ENTER sau khi 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 bác này, họ đã cùng nhau tò mò một số cách kết hợp công thức, hàm tính tổng với những hàm dò search để giải quyết nhu mong làm báo cáo mà không yêu cầu 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 công việc kế toán, lập report trong kho.

Ngoài ra để áp dụng Excel vào quá trình một cách tác dụng thì các bạn còn nên sử dụng xuất sắc các hàm, những công nuốm khác của Excel:

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

SUMIF, SUMIFS nhằm tính tổng theo một đ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 điều kiện với Conditional formattingThiết lập điều kiện nhập dữ liệu với Data ValidationCách đặt Name và áp dụng Name trong 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: phối hợp VLOOKUP, SUM với SUMIF

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

Để giúp chúng ta dễ hình dung được khi nào thì áp dụng hàm Sumif phối hợp hàm vlookup, hãy xem lấy ví dụ như 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, số lượng bán được của từng khía cạnh hàng.

Xem thêm: Để Cùng Hát Khúc Dân Ca Quê Mình, Nhạc Sĩ An Thuyên, Ca Dao Em Và Tôi

Tuy nhiên yêu ước của bọn họ là tính tổng số lượng bán theo Tên phương diện hàng được lựa chọn trong ô F11. Trong bảng tài liệu A1:C15 không tồn tại cột tên khía cạnh hàng. Mong mỏi biết Tên mặt hàng đó ứng cùng với Mã hàng nào, họ 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à phải tham chiếu tới 1 bảng khác => Sử dụng VLOOKUP xác định điều kiện

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

Viết hàm VLOOKUP

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

=VLOOKUP(giá trị kiếm tìm kiếm, vùng tham chiếu, cột cất kết quả, cách làm 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 đựng kết quả: cột Mã sản phẩm là cột thứ 2Phương thức tìm kiếm: tra cứu kiếm đúng đắn theo tên hàng => nhập số 0

Như vậy ta tất cả VLOOKUP(F11,E2:F8,2,0)

Khi phối kết hợp hai hàm với nhau ta có công thức tại ô F13 như sau:

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

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

Khi viết hàm SUMIF kết hợp với hàm VLOOKUP, chúng ta cần nên xác định: hàm VLOOKUP sẽ đặt tại vị trí tham số nào trong hàm SUMIF. Thường thì hay gặp mặt nhất chủ 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à cần tham chiếu cho tới 1 bảng tính nào đó.

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

Hàm SUMIFS là một trong những hàm rất 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 trong những trường hợp điều khiếu nại của báo cáo lại ko xác định được một biện pháp trực tiếp mà đề nghị tham chiếu tới 1 vùng tài liệu khác. Khi đó chúng ta cần phải phối 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 chúng ta biết sử dụng hàm Sumifs phối hợp hàm vlookup vào Excel.

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

*

Trong yêu mong trên, họ thấy gồm tới 3 điều kiện:

Vì vậy để 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, bọn họ sẽ bắt buộc dùng cho tới hàm SUMIFS.

Nhưng ở điều khiếu nại 3 là điều kiện liên 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à yêu cầu xác định thông qua bảng E1:F8. Trường đoản cú tên hàng (ở ô F13) tham chiếu ra mã sản phẩm tương ứng. Thực hiện mã hàng đó tham chiếu tiếp cho tới cột Mã hàng 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 khiếu nại bao gồm:

=SUMIFS(Vùng tính tổng, vùng điều kiện máy 1, điều khiếu nại 1, vùng điều kiện đồ vật 2, điều kiện 2, vùng điều kiện sản phẩm 3, điều kiện 3)

húng ta hy vọng tham chiếu Mã hàng phụ thuộc 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 đựng kết quả, thủ tục tìm kiếm)

Giá trị tìm kiế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 đựng kết quả: cột Mã hàng là cột trang bị 2Phương thức search kiếm: tìm kiếm kiếm đúng mực theo tên mặt hàng => nhập số 0

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

Khi phối kết hợp 2 hàm này, chúng ta có tác dụng tại ô F15 như sau:

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

*

Hãy demo 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 rất nhiều điều kiện. Khi đó bọn họ phải xác định rõ: Điều kiện đó rất có thể xác định trực tiếp được trong 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 ở lấy ví dụ trên)Nếu không: cần sử dụng những hàm phối kết hợp (như hàm VLOOKUP, hàm DATE, hàm EOMONTH…) để giúp từ các điều kiện đó loại gián tiếp tạo ra 1 điều kiện hoàn toàn có thể xác định trực tiếp được trong vùng tài liệu gốc.

Xem thêm: Bài Thơ Về Áo Dài Việt Nam Cực Hay, Chùm Thơ Về Áo Dài Việt Nam

Đây là giữa những kỹ thuật căn bạn dạng giúp lập báo cáo theo nhiều điều kiện một cách chính xác, nhanh chóng.

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

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