Trong nội dung bài viết này, mình vẫn hướng dẫn các bạn làm chũm nào để có thể kết hợp các hàm VLOOKUP, SUM và SUMIF để dò tìm và tính tổng dựa trên một 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 thích hợp hàm VLOOKUP cùng SUMIFTổng hợp tài liệu không thêm cột phụ, phối kết hợp hàm LOOKUP với SUMKết thích hợp hàm VLOOKUP với SUMIF tra cứu và 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ộtKết hòa hợp hàm VLOOKUP cùng SUMIF
Trước khi bắt đầu vào bài xích này, kiên cố hẳn chúng ta đã thành thạo với hàm VLOOKUP và hàm SUMIF rồi. Vậy nên bạn cũng có thể bắt nguồn vào ngay lấy một ví dụ đầu tiên.
Trong lấy ví dụ này, họ sẽ đi tính tổng sản lượng của Cam vào thời điểm tháng 1, tháng 2, mon 3 bằng cách sử dụng bí quyết sau đây:
=SUM(VLOOKUP( A2 , A1:I8 , 2,3,4 , FALSE ))
Sau khi nhập công thức này, chúng ta lưu ý vì đó là công thức mảng, vậy nên sau khoản thời gian nhập công thức, bạn phải sử dụng tổ hợp phím CTRL + SHIFT + ENTER để có thể nhập được bí quyết và có công dụng đúng.
Để các chúng ta có thể hiểu hơn, chúng ta sẽ phân tích cách làm 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 bí quyết mảng bên trên nghĩa là cột 2,3,4 vào mảng dữ liệu A1:I8. Đọc mang đến đây, hoàn toàn có thể các chúng ta có thể sẽ tất cả câu hỏi, trên sao bọn họ không áp dụng công thức sau đây để tính tổng
=SUM(B2:D2)
Mục đích chuyển ra hầu hết công thức phức tạp này, chúng ta sẽ giao hàng cho vấn đề tạo report hoặc dashboard. Nếu họ sử dụng một cách làm tính tổng đơn thuần bằng hàm SUM, khi chúng ta có 1 ô chứa các loại hàng hoá, muốn biến hóa ô này để tính tổng sản lượng của một sản phẩm, họ phải đổi khác công thức SUM theo.
Với bí quyết mảng phối hợp hàm SUM với VLOOKUP như sinh sống trên, bạn cũng có thể tạo ra 1 báo cáo về sản lượng của những sản phẩm một cách nhanh nệm như sau:
Từ ví dụ phía trên, vớ nhiên, ta hoàn toàn có thể thay hàm SUM bằng một số hàm khác để phục vụ mục đích của chúng ta như hàm AVERAGE, hàm MIN, hàm MAX hoặc tiến hành các phép đo lường và tính toán khác … Và chú ý vì những công thức này phần đa 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 khi nhập bí quyết này trong Excel.
Tổng hợp tài liệu không thêm cột phụ, phối hợp hàm LOOKUP cùng SUM:
Chúng ta gồm ví dụ sau, trong lấy ví dụ như này, họ có 2 bảng dữ liệu, 1 bảng bao gồm có sản phẩm và 1-1 giá; bảng sản phẩm 2 bao hàm khách hàng, sản phẩm và con số sản phẩm quý khách đã mua. Bọn họ sẽ mong 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 LOOKUPBình thường, chúng ta sẽ đề xuất dùng cột phụ như sau:
Các phương pháp như sau:
G2=VLOOKUP(E2,bang_SP,2,FALSE)
H2=F2*G2
K2=SUMIF(D:D,K1,H:H)
Trong trường phù hợp không thêm được cột phụ, bạn cũng có thể sử dụng công thức 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 công thức phối kết hợp SUM cùng LOOKUP này:
Cột A đề nghị được thu xếp theo thứ tự tăng ngày một nhiều hoặc từ bỏ A mang lại Z, nhằm hàm LOOKUP cho 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 không sử dụng tổng hợp phím này, bạn cũng có thể thay hàm SUM bằng hàm SUMPRODUCTCô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 bọn họ kết đúng thật cột đối chọi 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 gồm những giá trị đúng với sai, khi đem mảng này nhân với cùng 1 số, thì quy tắc dưới đây được áp dụng: (TRUE được định nghĩa là 1, FALSE được tư tưởng là 0)Và cuối cùng, hàm SUM sẽ tính tổng và cho ta công dụng cuối cùngTới đây, bạn cũng có thể download file excel để theo dõi đến dễ tại đây
Kết thích hợp hàm VLOOKUP và SUMIF tra cứu với tổng hợp tài liệu theo điều kiện
Ví dụ:
Excel nâng cao, phối hợp VLOOKUP và SUMIFTrong ví dụ này, ta có 2 bảng, 1 bảng là tên Telesale với ID của họ, 1 bảng khác chỉ gồm ID và doanh số. Trọng trách ở lấy một ví dụ này: yêu cầu đi tính tổng doanh số của bất cứ Telesale nào phụ thuộc tên của họ.
Công thức bọn họ sẽ sử dụng tại đây là
=SUMIF(D:D,VLOOKUP(H1,A1:B8,2,FALSE),E:E)
VLOOKUP sẽ có được nhiệm vụ chuyển lại mã ID của Telesale với thương hiệu tương ứng, phụ thuộc vào ID này chúng ta áp dụng SUMIF như 1 ví dụ đơn giản và dễ dàng bình thường.
Tính tổng nhiều đk ở cùng 1 cột:
Chúng ta bao gồm ví dụ tính tổng sau đây, đk tính tổng phần nhiều năm tại 1 cột: Tính tổng của các giao dịch cùng với đầu mã là 111 với 131
Để làm được điều này, chúng ta có 3 biện pháp tính, với giải pháp mà công thức tất cả dấu , nghĩa là phương pháp mảng, bạn phải nhấn tổng hợp phím tắt CTRL + SHIFT + ENTER sau thời điểm 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 đã cùng nhau khám phá một số cách phối hợp công thức, hàm tính tổng với các hàm dò tìm kiếm để xử lý nhu ước làm report mà không đề nghị dùng thêm cột phụ. Hi vọng bài viết có ích cho quá trình của chúng ta đặc biệt là trong các bước 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 kết quả thì các bạn còn phải sử dụng giỏi các hàm, các công cố khác của Excel:
Một số hàm cơ bản thường chạm chán như:
SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiệnCOUNTIF, COUNTIFS để 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ò kiếm tìm tham chiếu Index+Match, hàm SUMPRODUCT…Một số công cụ hay được dùng như:
Định dạng theo điều kiện với Conditional formattingThiết lập đk nhập tài liệu với Data ValidationCách đặt Name và thực hiện Name vào công thứcLập báo cáo với Pivot Table…Các ví dụ như Excel nâng cao: phối hợp VLOOKUP, SUM và SUMIF
Cách sử dụng hàm Sumif phối hợp hàm vlookup
Để giúp chúng ta dễ hình dung được khi nào thì sử dụng hàm Sumif kết hợp hàm vlookup, hãy xem ví dụ sau đây:
Chúng ta có một bảng tài liệu từ A1:C15, gồm các 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: Ứng Dụng Theo Dõi Iphone Tốt Nhất Và Hiệu Quả 2020, Định Vị Dõi Theo Số Điện Thoại 4+
Tuy nhiên yêu cầu của chúng ta là tính tổng số lượng buôn bán theo Tên mặt hàng được chọn trong ô F11. Trong bảng dữ liệu A1:C15 không có cột tên khía cạnh hàng. Muốn biết Tên khía cạnh 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 ko xác định được 1 cách trực tiếp mà nên tham chiếu tới 1 bảng khác => Sử dụng VLOOKUP xác định điều kiệnCách làm 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ã sản phẩm là có tương quan tới điều khiếu nại => lựa chọn vùng A2:A15 (không chọn cái tiêu đề)Criteria: điều khiếu nại của đề bài là tên hàng. Dẫu vậy Range lại là Mã hàng. Do đó bắt buộc tham chiếu ra Mã hàng phụ thuộc vào Tên sản phẩm => áp dụng hàm VLOOKUP.Sum_range: công dụng cần tính là Số lượng. Do đó chọn vùng C2:C15Viết hàm VLOOKUP
Chúng ta ao ước tham chiếu Mã hàng phụ thuộc vào Tên hàng, do đó hàm VLOOKUP đang viết như sau:
=VLOOKUP(giá trị kiếm tìm kiếm, vùng tham chiếu, cột đựng kết quả, cách tiến hành 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ỏ lỡ dòng tiêu đề)Cột cất kết quả: cột Mã hàng là cột thứ 2Phương thức tìm kiếm kiếm: kiếm tìm kiếm đúng mực theo tên hàng => nhập số 0Như vậy ta bao gồm VLOOKUP(F11,E2:F8,2,0)
Khi phối kết hợp hai hàm với nhau ta tất cả công thức tại ô F13 như sau:
=SUMIF(A2:A15,VLOOKUP(F11,E2:F8,2,0),C2:C15)
Khi viết hàm SUMIF kết phù hợp với hàm VLOOKUP, chúng ta cần bắt buộc xác định: hàm VLOOKUP sẽ đặt tại vị trí tham số nào trong hàm SUMIF. Thông thường hay chạm mặt nhất bao gồm là: Điều kiện trong hàm SUMIF ko xác định được một cách trực tiếp, mà nên tham chiếu 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à 1 trong hàm hết sức hữu ích trong Excel giúp chúng ta có thể tính tổng theo nhiều điều kiện cùng lúc. Tuy nhiên trong một số trong những trường hợp điều kiện của báo cáo lại không xác định được một biện pháp trực tiếp mà nên tham chiếu cho tới 1 vùng tài liệu khác. Khi đó họ 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 đã hướng dẫn chúng ta biết sử dụng hàm Sumifs phối hợp hàm vlookup trong Excel.
Trước tiên hãy xét lấy một ví dụ sau đây:
Trong yêu ước trên, họ thấy tất cả tới 3 điều kiện:
Vì vậy để hoàn toàn có thể tính được số lượng bán thỏa mãn đồng thời cả 3 điều kiện trên, bọn họ sẽ buộc phải dùng tới hàm SUMIFS.
Nhưng ở điều kiện 3 là điều kiện liên quan tới thương hiệu hàng. Tên hàng không có sẵn vào bảng tài liệu A1:C15 mà buộc phải xác định thông qua bảng E1:F8. Từ tên sản phẩm (ở ô F13) tham chiếu ra mã hàng 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 cùng 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 1, điều khiếu nại 1, vùng điều kiện lắp thêm 2, điều kiện 2, vùng điều kiện sản phẩm 3, điều khiếu nại 3)
húng ta ao ước 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ị search 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 gọi hàng tại ô F13Vùng tham chiếu: Vùng bảng E2:F8 (có thể bỏ qua dòng tiêu đề)Cột cất kết quả: cột Mã hàng là cột thiết bị 2Phương thức kiếm tìm kiếm: kiếm tìm kiếm đúng chuẩn theo tên mặt hàng => nhập số 0Như vậy ta có VLOOKUP(F13,E2:F8,2,0)
Khi phối hợp 2 hàm này, bọn họ có hiệu quả tại ô F15 như sau:
=SUMIFS(C2:C15,B2:B15,">="&F11,B2:B15,"
Hãy test thay đổi tên hàng 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 đó bọn họ phải xác định rõ: Điều kiện đó hoàn toàn 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ư giá trị Từ ngày, đến ngày ở lấy ví dụ trên)Nếu không: đề nghị sử dụng các hàm phối hợp (như hàm VLOOKUP, hàm DATE, hàm EOMONTH…) để giúp từ các điều kiện đó gián tiếp tạo thành 1 điều kiện có thể xác định trực tiếp được trong vùng tài liệu gốc.Đây là một trong những kỹ thuật căn phiên bản giúp lập report theo nhiều điều khiếu nại một cách chủ yếu xác, cấp tốc chóng.
Chúc những bạn áp dụng tốt 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? toàn thể những kiến thức này các bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel từ cơ bạn dạng tới siêng gia của học tập Excel Online. Đây là khóa học khiến cho bạn hệ thống kiến thức và kỹ năng một biện pháp đầy đủ, chi tiết. Rộng nữa không còn có số lượng giới hạn về thời gian học tập nên chúng ta cũng có thể thoải mái học bất cứ lúc nào, tiện lợi tra cứu vớt lại kỹ năng và kiến thức khi cần. Hiện nay hệ thống đang sẵn có ưu đãi siêu lớn cho mình khi đk tham gia khóa học. Chi tiết xem tại: binhchanhhcm.edu.vn.Online
