Cách sử dụng hàm SUMIFS trong Excel

Danh sách hàm của Excel là một trong những yếu tố năng động nhất trong phân tích dữ liệu. Từ các phép toán số học ưa thích đến logic có điều kiện nâng cao, có một chút gì đó dành cho mọi loại người dùng ngoài kia.

Hàm SUMIFS là một trong nhiều hàm có sẵn cho người dùng muốn thúc đẩy phân tích chi tiết dựa trên logic điều kiện. Nếu bạn đã quen thuộc với hàm SUMIF trong Excel, bạn sẽ thấy hàm SUMIFS khá giống với đối tác của nó. Đây là những điều bạn cần biết về cách sử dụng hàm SUMIFS trong Excel.

Cú pháp của hàm SUMIFS

Hàm SUMIFS trong Excel là đỉnh cao của hàm SUM và IF. Nó tính tổng các giá trị trong một ô dựa trên các tiêu chí cụ thể. Bạn có thể tạo các điều kiện liên quan đến ngày, số và văn bản. Ngoài ra, vui lòng sử dụng các toán tử logic để điều khiển các điều kiện của bạn và làm cho chúng mở rộng hơn.

Để thành thạo các công thức logic này, bạn cần biết cách sử dụng hàm IFS, trước khi kết hợp nó với bất kỳ hàm tổng hợp nào.

Excel có các hàm logic khác, chẳng hạn như COUNTIF, COUNTIFS, SUMIF, MAXIFS, MINIFS và nhiều hàm khác. Khi làm việc với bất kỳ hàm nào trong số này, bạn phải đảm bảo rằng bạn truyền đúng cú pháp trong các công thức.

Đây là cú pháp của Hàm SUMIFS:

 =SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...) 

Ở đâu:

• tổng_dải: Phạm vi/cột để tính tổng dựa trên tiêu chí của bạn
• phạm vi1: Phạm vi 1 xác định cột trong đó công thức của bạn sẽ tìm thấy bộ tiêu chí đầu tiên
• tiêu chí1: Tiêu chí 1 đề cập đến điều kiện đầu tiên
• phạm vi2: Phạm vi 2 xác định cột trong đó công thức của bạn sẽ tìm thấy bộ tiêu chí thứ hai
• tiêu chí2: Tiêu chí 2 đề cập đến điều kiện thứ hai

Cách sử dụng hàm SUMIFS trong Excel

Giờ thì cú pháp đã hết, điều cần thiết là phải hiểu các thành phần khác nhau của hàm và cách bạn có thể sử dụng nó một cách tốt nhất. Hàm SUMIF có rất nhiều công dụng trong thế giới dữ liệu, nhưng điều đó không có nghĩa là hàm SUMIFS xa vời. Bạn có thể chuyển nhiều điều kiện trong công thức SUMIFS và nâng cấp hàm SUMIF.

Chuẩn bị dữ liệu

Giả sử bạn có một bộ dữ liệu chứa thông tin về các sản phẩm được bán trong một nhà máy văn phòng. Một số cột dữ liệu có liên quan bao gồm ngày đặt hàng, danh mục, màu sắc, giá bán, số lượng đã bán và tổng doanh số. Tất cả các công thức sẽ dựa trên tham chiếu dữ liệu có trong dữ liệu mẫu này.

1. Làm việc với điều kiện văn bản

Nếu bạn muốn tính tổng chi phí của chất kết dính màu xanh lá cây từ danh sách này, bạn có thể sử dụng SUMIFS công thức như sau:

 =SUMIFS(F2:F18, B2:B18, "Binders",C2:C18, "Green") 

Trước tiên, bạn phải xác định cột mà bạn muốn lấy tổng số từ đó; trong trường hợp này, nó là Tổng doanh thu cột. Xác định phạm vi cột có giá trị Chất kết dính (cột A). Phần tiếp theo của công thức cần tiêu chí, Binders.

Tiêu chí đầu tiên được đặt ra; để xác định phân đoạn tiếp theo, đó là màu sắc, bạn phải tuân theo quy tắc tương tự. Bạn cần bao gồm cột chứa chi tiết màu, theo sau là màu (Xanh lục) mà bạn muốn tìm.

Excel tính toán các giá trị dựa trên các điều kiện đã chỉ định và đưa ra kết quả 154.024.

2. Làm việc với điều kiện số

Trong ví dụ trên, bạn đã xác định điều kiện Văn bản (Màu = Xanh lục và Danh mục = Chất kết dính). Bây giờ, bước tiếp theo là làm việc với các điều kiện số. Hãy minh họa điều này bằng một ví dụ:

Nếu bạn muốn tính tổng doanh số cho các sản phẩm có Giá ưu đãi từ 500 đến 1000. Dưới đây là cách bạn có thể xây dựng công thức:

 =SUMIFS(E2:E18, D2:D18, ">500", D2:D18, "<1000") 

Phạm vi đầu tiên của công thức trên là tham chiếu cột Tổng doanh thu. Trong phân đoạn tiếp theo, bạn phải xác định cột cho tiêu chí đầu tiên, bao gồm các giá trị trên 500. Trong phần cuối cùng, bạn phải xác định tiêu chí cuối cùng, bao gồm các giá trị dưới 1000.

kết quả cuối cùng là 13083.888.

3. Làm việc với điều kiện ngày tháng

Bạn có thể sử dụng hàm SUMIFS để tính tổng các giá trị theo điều kiện ngày cơ sở. Ví dụ: nếu bạn có một tập hợp các cột có ngày và muốn tìm tổng doanh số bán hàng trong một phạm vi ngày, bạn có thể sử dụng công thức để nhanh chóng lấy được kết quả.

 =SUMIFS(F2:F18, A2:A18, ">1/1/2015", A2:A18, "<1/1/2016") 

Giống như các công thức khác ở trên, bạn có thể sử dụng ngày để thao tác dữ liệu của mình và tính tổng giá trị bán hàng. Phần đầu tiên của công thức đề cập đến cột Tổng doanh thu. Trong phân đoạn tiếp theo, bạn cần xác định phạm vi ngày. Bạn cần sử dụng các toán tử logic để thao tác ngày trong công thức SUMIFS.

Vì tham chiếu bao gồm các ngày từ 01-01-2015 đến 01-01-2016, nên kết quả là 16495.28.

4. Sử dụng tìm kiếm ký tự đại diện

Cuối cùng, có một phương pháp cuối cùng để sử dụng với hàm SUMIFS. Bạn có thể kết hợp khéo léo chức năng tìm kiếm ký tự đại diện với chức năng SUMIFS đa chức năng để tìm kết quả phù hợp dựa trên các yếu tố nhất định. Ví dụ: giả sử bạn muốn tìm tổng các giá trị có chứa các chữ cái ind trong cột Danh mục và các chữ cái la trong cột Màu.

Hàm SUMIFS của Excel lọc qua từng cột được chỉ định và tìm kiếm các chữ cái được chỉ định. Mọi giá trị phù hợp đều được tổng hợp và kết quả được hiển thị. Để sử dụng tìm kiếm ký tự đại diện, bạn có thể sử dụng * (dấu hoa thị) dấu hiệu hoặc ? (dấu chấm hỏi) biểu tượng trong công thức của bạn.

Đây là cách bạn có thể viết công thức:

 =SUMIFS(F2:F18, B2:B18, "*ind*", C2:C18, "*la*") 

Kết quả là 1223.928.

Sử dụng hàm SUMIFS trong Excel hiệu quả

Mặc dù việc sử dụng rộng rãi hàm SUMIFS trong Excel, nhiều nhà phân tích vẫn tiếp tục dựa vào tiềm năng thực sự của hàm mạnh mẽ này. Có rất nhiều cách bạn có thể sử dụng chức năng đa diện này trong báo cáo và phân tích thường xuyên của mình.

Bất kể bạn là người làm việc tự do, chủ cửa hàng hay nhà phân tích dữ liệu, rất có thể sẽ luôn có cách để bạn tận dụng tối đa chức năng này.