Trong nội dung bài viết này, mình vẫn hướng dẫn các bạn làm cụ nào để rất có thể kết hợp những hàm VLOOKUP, SUM cùng SUMIF để dò tìm với 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 dữ liệu không thêm cột phụ, kết hợp hàm LOOKUP với SUMKết vừa lòng hàm VLOOKUP cùng SUMIF tra cứu và tổng hợp dữ liệu theo điều kiệnTính tổng nhiều đk ở cùng 1 cộtKế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 hẳn các bạn đã thuần thục với hàm VLOOKUP và hàm SUMIF rồi. Vậy nên bạn cũng có thể bắt đầu vào ngay ví dụ đầu tiên.
Trong lấy ví dụ này, bọn họ sẽ đi tính tổng sản lượng của Cam trong tháng 1, tháng 2, mon 3 bằng cách 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ì đó 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ổng hợp phím CTRL + SHIFT + ENTER để có thể nhập được công thức và có hiệu quả đúng.
Để các chúng ta có thể hiểu hơn, bọn họ 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 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ẽ gồm câu hỏi, trên sao chúng ta không thực hiện công thức dưới đây để tính tổng
=SUM(B2:D2)
Mục đích gửi ra hầu hết công thức tinh vi này, chúng ta sẽ giao hàng cho câu hỏi tạo report hoặc dashboard. Nếu bọn họ sử dụng một công thức tính tổng đối kháng thuần bởi hàm SUM, khi bọn họ có 1 ô chứa các loại sản phẩm hoá, muốn biến đổi ô này để tính tổng sản lượng của 1 sản phẩm, họ phải chuyển đổi công thức SUM theo.
Với cách làm mảng phối kết hợp hàm SUM và VLOOKUP như sinh sống trên, chúng ta cũng có thể tạo ra 1 report về sản lượng của những sản phẩm 1 cách nhanh giường như sau:
Từ lấy một ví dụ phía trên, vớ nhiên, ta có thể thay hàm SUM bằng một số hàm không giống để giao hàng mục đích của họ như hàm AVERAGE, hàm MIN, hàm MAX hoặc thực hiện các phép đo lường khác … Và lưu ý vì những công thức này đầy đủ 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 lúc nhập công thức này vào Excel.
Tổng hợp dữ liệu không thêm cột phụ, phối kết hợp hàm LOOKUP với SUM:
Chúng ta có ví dụ sau, trong lấy 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à đối chọi giá; bảng sản phẩm công nghệ 2 bao hàm khách hàng, thành phầm và con số sản phẩm người sử dụng đã mua. Bọn họ sẽ hy vọng đ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 và LOOKUPBình thường, bọn họ sẽ buộc phải 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ó 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))
Chú ý khi sử dụng công thức kết hợp SUM và LOOKUP này:
Cột A phải được bố trí theo sản phẩm công nghệ tự tăng nhiều hoặc từ bỏ A mang lại Z, để 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, chúng ta cũng có thể thay hàm SUM bởi 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 quả thật cột solo giá vào 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 thành 1 mảng gồm các giá trị đúng cùng 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 tư tưởng là 1, FALSE được quan niệm là 0)Và cuối cùng, hàm SUM công thêm tổng và mang đến ta hiệu quả cuối cùngTới đây, chúng ta có thể download file excel để theo dõi mang đến dễ tại đây
Kết hợp 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, kết hợp VLOOKUP với SUMIFTrong lấy ví dụ như 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ỉ bao gồm ID với doanh số. Nhiệm vụ ở ví dụ như này: buộc phải đi tính tổng doanh thu của bất kể 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ó nhiệm vụ chuyển lại mã ID của Telesale với thương hiệu tương ứng, dựa vào ID này bọn họ áp dụng SUMIF như một ví dụ dễ dàng 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 rất nhiều năm ở 1 cột: Tính tổng của những giao dịch với đầu mã là 111 và 131
Để có tác dụng được điều này, họ có 3 biện pháp tính, với cách mà công thức tất cả dấu , nghĩa là cách làm mảng, bạn phải nhấn tổ hợp phím tắt CTRL + SHIFT + ENTER sau khoản thời gian nhập công thức để đượ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 xích này, chúng ta đã cùng nhau tìm hiểu một số cách phối kết hợp công thức, hàm tính tổng với những hàm dò search để xử lý nhu ước 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 quá trình của chúng ta đặc biệt là trong các bước kế toán, lập report trong kho.
Ngoài ra để ứng dụng Excel vào quá trình một cách kết quả thì bạn còn bắt buộc sử dụng tốt các hàm, các công cố gắng khác của Excel:
Một số hàm cơ phiên bản thường gặp mặt như:
SUMIF, SUMIFS để tính tổng theo 1 điều kiện, những điều kiệnCOUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiệnCác hàm xử lý tài 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ụ thường dùng như:
Định dạng theo đk với Conditional formattingThiết lập đk nhập dữ liệu với Data ValidationCách đặt Name và sử dụng Name trong 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ới SUMIF
Cách áp dụng hàm Sumif kết hợp hàm vlookup
Để giúp họ dễ hình dung được lúc nào thì thực hiện hàm Sumif phối hợp hàm vlookup, hãy xem ví dụ sau đây:
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.
Tuy nhiên yêu ước của bọn họ là tính tổng số lượng chào 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 có cột tên khía cạnh hàng. Mong mỏi biết Tên khía cạnh hàng đó ứng với Mã hàng nào, họ phải tham chiếu vào 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ông giống => 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ỉ có 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 loạ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 đó đề nghị tham chiếu ra Mã hàng dựa vào Tên mặt hàng => thực hiện 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 ước ao tham chiếu Mã hàng nhờ vào Tên hàng, do đó hàm VLOOKUP đã viết như sau:
=VLOOKUP(giá trị tra cứu 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ị search kiếm: là tên hàng tại ô F11Vù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ã mặt hàng là cột đồ vật 2Phương thức tra cứu kiếm: search kiếm đúng chuẩn theo tên sản phẩm => nhập số 0Như vậy ta bao gồm VLOOKUP(F11,E2:F8,2,0)
Khi phối hợp hai hàm cùng nhau ta bao gồm 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 hợp với hàm VLOOKUP, họ cần đề nghị 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 thiết yếu là: Điều khiếu nại trong hàm SUMIF ko xác định được một cách trực tiếp, mà đề xuất 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 kết hợp hàm vlookup trong Excel
Hàm SUMIFS là 1 trong những hàm vô cùng 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. Cơ mà trong một số trường hợp điều khiếu nại của report lại ko xác định được một giải pháp trực tiếp mà bắt buộc tham chiếu tới 1 vùng tài liệu khác. Khi đó họ 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 vẫn 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 một ví dụ sau đây:
Trong yêu cầu trên, họ thấy có tới 3 điều kiện:
Vì vậy để 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, họ sẽ đề nghị 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 có sẵn vào bảng dữ liệu A1:C15 mà bắt buộc xác định trải qua bảng E1:F8. Từ bỏ tên hàng (ở ô F13) tham chiếu ra mã hàng tương ứng. Sử 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 hiệu quả 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 thiết bị 1, điều kiện 1, vùng điều kiện đồ vật 2, điều kiện 2, vùng điều kiện lắp thêm 3, điều khiếu nại 3)
húng ta ao ước tham chiếu Mã hàng phụ thuộc Tên hàng, do đó hàm VLOOKUP đã 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 thứ 2Phương thức tìm kiếm kiếm: tìm kiếm kiếm đúng mực theo tên sản phẩm => nhập số 0Như vậy ta bao gồm VLOOKUP(F13,E2:F8,2,0)
Khi kết hợp 2 hàm này, chúng ta có kết quả tại ô F15 như sau:
=SUMIFS(C2:C15,B2:B15,">="&F11,B2:B15,"
Hãy test thay đổi tên mặt hàng tại ô F13 để xem kết quả của hàm SUMIFS tại ô F15 thay đổi đúng ko nhé.
Kết luận
Hàm SUMIFS hoàn toàn có thể viết được với siêu nhiều điều kiện. Khi đó bọn họ phải xác định rõ: Điều kiện đó 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 một ví dụ trên)Nếu không: buộc phải sử dụng những 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 đó loại gián tiếp tạo nên 1 điều kiện có thể xác định trực tiếp được trong vùng dữ liệu gốc.Xem thêm: Mua Mã Thẻ Bằng Sms Vinaphone Đơn Giản, Hướng Dẫn Cách Mua Thẻ Game Bằng Sms Vinaphone
Đây là 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 cách bao gồm 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 kiến thức và kỹ năng phải không nào? tổng thể những kỹ năng và kiến thức này các bạn đều hoàn toàn có thể học được vào khóa học EX101 – Excel tự cơ phiên bản tới chăm 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 giải pháp đầy đủ, đưa ra 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ũng có thể thoải mái học bất cứ lúc nào, dễ dãi tra cứu vớt lại kỹ năng khi cần. Bây chừ hệ thống đang sẵn có ưu đãi siêu lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: heckorea.com.Online