Cách dùng hàm SUMIFS kết hợp VLOOKUP trong Excel

Hàm SUMIFS và hàm VLOOKUP đều là những lệnh được sử dụng phổ biến trong Excel. Thế nhưng liệu sự kết hợp 2 hàm này có tác dụng gì? Cách sử dụng hàm kết hợp này như thế nào? Trong bài viết này, Gitiho sẽ đem đến cho bạn đọc cách dùng hàm SUMIF kết hợp VLOOKUP trong Excel để giải quyết những tình huống cụ thể.

Xem thêm: Hướng dẫn cách viết các hàm trong Excel chi tiết dễ hiểu nhất

Đăng ký ngay khoá học Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ

Ví dụ minh họa

Dưới đây là bảng ghi chép lại số lượng đã bán ra của các mặt hàng khác nhau. Hãy tính tổng số lượng bán của mặt hàng theo các điều kiện sau:

Trong đó, 

• Điều kiện 1: Từ ngày 01/06/2018
• Điều kiện 2: Đến ngày 30/06/2018
• Điều kiện 3: Tên hàng là ''Mận Hà Giang loại 2'' (tương ứng với mã là M102 trong bảng E1:F8)

Từ những yêu cầu đặc trưng của đề bài này, có thể kết luận được rằng: Hàm cần được sử dụng trong ví dụ này là hàm SUMIFS.

Lưu ý: Tuy nhiên, điều kiện 3 là điều kiện liên quan đến tên hàng. ''Tên hàng'' không có sẵn trong bảng dữ liệu A1:C15 mà phải xác định thông qua bảng E1:F8. Từ ''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 tới cột ''Mã hàng'' trong bảng A1:C15 để ra kết quả cho hàm SUMIFS.

Hàm SUMIFS

Công thức hàm SUMIFS như sau:

=SUMIFS(Vùng tính tổng, vùng điều kiện thứ 1, điều kiện 1, vùng điều kiện thứ 2, điều kiện 2, vùng điều kiện thứ 3, điều kiện 3)

Trong đó, 

• Vùng tính tổng: Do yêu cầu tính tổng số lượng, nên công thức hàm sẽ lấy theo vùng C2:C15
• Vùng điều kiện thứ 1: Là cột ''Ngày'' (vì chứa điều kiện 1 là ngày), chọn vùng B2:B15
• Điều kiện thứ 1: Từ ngày 01/06/2018 là giá trị tại ô F11. Tuy nhiên từ ngày được hiểu với ký hiệu là >=, do đó cách viết trong công thức hàm sẽ là “>=”&F11
• Vùng điều kiện thứ 2: Là cột ''Ngày'' (vì chứa điều kiện 2 là ngày), chọn vùng B2:B15
• Điều kiện thứ 2: Đến ngày 30/06/2018 là giá trị tại ô F12. Tuy nhiên đến ngày được hiểu là <=, do đó cách viết trong công thức hàm sẽ là “<=”&F12
• Vùng điều kiện thứ 3: Là cột ''Mã hàng'', chọn vùng A2:A15
• Điều kiện thứ 3: Mã hàng, được xác định bởi hàm VLOOKUP tham chiếu theo ''Tên hàng'' được chọn ở ô F13

Cụ thể hơn, công thức hàm SUMIFS cần được áp dụng là:

=SUMIFS(C2:C15,B2:B15,”>=”&F11,B2:B15,”<=”&F12,A2:A15)

Hàm VLOOKUP

Do yêu cầu của đề bài, để giải quyết các điều kiện, cần tham chiếu Mã hàng dựa vào Tên hàng, cho nên công thức hàm VLOOKUP được sử dụng trong ví dụ này là:

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

• Giá trị tìm kiếm: Là Tên hàng tại ô F13
• Vùng tham chiếu: Là vùng bảng E2:F8 (có thể bỏ qua dòng tiêu đề)
• Cột chứa kết quả: Là cột ''Mã hàng'' - cột thứ 2
• Phương thức tìm kiếm: Tìm kiếm chính xác theo tên hàng. Do đó, bạn đọc cần nhập số 0.

Cụ thể hơn, công thức hàm VLOOKUP cần được áp dụng là:

=VLOOKUP(F13,E2:F8,2,0)

Hàm SUMIFS kết hợp VLOOKUP

Như đã phân tích trên đề bài của ví dụ. Do cần tham chiếu để ra kết quả cho hàm SUMIFS nên bạn đọc cần kết hợp hàm SUMIFS với hàm VLOOKUP để được đáp án chính xác nhất. Sau khi viết rời từng công thức hàm SUMIFS và VLOOKUP ở trên, hàm kết hợp lúc này sẽ được viết như sau:

=SUMIFS(C2:C15,B2:B15,”>=”&F11,B2:B15,”<=”&F12,A2:A15,VLOOKUP(F13,E2:F8,2,0))

Kiểm tra lại: Hãy thử thay đổi tên hàng tại ô F13 để xem kết quả của hàm SUMIFS tại ô F15 thay đổi có còn cho kết quả đúng hay không nhé.

Kết luận

Bài viết trên chỉ là một trong những cách sử dụng khác của hàm SUMIFS, hàm này có thể được viết với khá nhiều điều kiện đi kèm, nhưng người dùng cần phải xác định được rõ xem điều kiện đó có thể xác định trực tiếp được trong bảng dữ liệu hay không. Câu trả lời có thể như sau:

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

Những ghi nhớ này có thể coi là một kỹ thuật căn bản hỗ trợ lập báo cáo theo nhiều điều kiện một cách dễ dàng, chính xác và tốc độ hơn.

Hy vọng qua bài viết này, bạn đọc có thể biết đến không chỉ các sử dụng hàm SUMIFS kết hợp VLOOKUP mà còn là công cụ lập báo cáo tối ưu từ hàm SUMIFS. Để thành thạo các kỹ năng hữu ích như vậy, hãy luyện tập với các bài tập Excel có đáp án hay đăng ký ngay khóa học Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ để làm chủ các kỹ năng tin học văn phòng chuyên nghiệp nhất và mới nhất nhé. Chi tiết xem tại: Gitiho.com

Chúc các bạn sử dụng thành công!

Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất

Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel

Hướng dẫn cách sắp xếp ngày trong Excel

Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao

HƯỚNG DẪN CÁCH SỬ DỤNG HÀM IF VỚI NHIỀU ĐIỀU KIỆN: AND, OR, HÀM IF LỒNG NHAU VÀ HƠN THẾ