Cú pháp của hàm SUMIFS
Hàm SUMIFS trong Excel là tổng hợp của hai hàm SUM và IF. Nó tính tổng các giá trị trong một ô dựa trên một số tiêu chí cụ thể. Bạn có thể tạo các tiêu chí liên quan đến ngày, số và văn bản.
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 sử dụng đú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], ...)Trong đó:
- sum_range: Phạm vi/cột để tính tổng dựa trên tiêu chí của bạn
- range1: 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
- criteria1: tiêu chí đầu tiên
- range2: 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
- range2: điều kiện thứ hai
Cách sử dụng hàm SUMIFS trong Excel
Hàm SUMIF có rất nhiều công dụng trong thế giới dữ liệu. Bạn có thể chỉ định nhiều điều kiện trong công thức SUMIFS và nâng hàm SUMIF lên một cấp độ mới.
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 sản xuất vật dụng 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.
|
Ngày đặt hàng |
Loại |
Màu sắc |
Giá bán |
Số lượng |
Tổng doanh thu |
|
08-11-2016 |
Điện thoại |
Đen |
907.152 |
6 |
5442.912 |
|
12-06-2016 |
Kẹp tài liệu |
Màu xanh lá |
18.504 |
3 |
55.512 |
|
11-10-2015 |
Thiết bị gia dụng |
Màu vàng |
114,9 |
5 |
574,5 |
|
11-10-2015 |
Bàn |
Màu nâu |
1706.184 |
9 |
15355.656 |
|
09-06-2014 |
Điện thoại |
Màu đỏ |
911.424 |
4 |
3645.696 |
|
09-06-2014 |
Giấy |
Trắng |
15.552 |
3 |
46.656 |
|
09-06-2014 |
Kẹp tài liệu |
Đen |
407.976 |
3 |
1223.928 |
|
09-06-2014 |
Thiết bị gia dụng |
Màu vàng |
68,81 |
5 |
344.05 |
|
09-06-2014 |
Kẹp tài liệu |
Màu xanh lá |
2.544 |
3 |
7.632 |
|
09-06-2014 |
Tủ |
Màu cam |
665.88 |
6 |
3995.28 |
|
09-06-2014 |
Tủ |
Màu cam |
55,5 |
2 |
111 |
|
15-04-2017 |
Điện thoại |
Đen |
213,48 |
3 |
640.44 |
|
05-12-2016 |
Kẹp tài liệu |
Màu xanh lá |
22,72 |
4 |
90,88 |
|
22-11-2015 |
Thiết bị gia dụng |
Màu xanh lá |
60,34 |
7 |
422.38 |
|
22-11-2015 |
Ghế |
Màu nâu tối |
71.372 |
2 |
142.744 |
|
11-11-2014 |
Công nghệ |
Không áp dụng |
1097.544 |
7 |
7682.808 |
|
13-05-2014 |
Nội thất |
Màu cam |
190,92 |
5 |
954,6 |
1. Làm việc với điều kiện chữ
Nếu bạn muốn tính tổng chi phí của Green Binders (kẹp tài liệu màu xanh) từ danh sách này, bạn có thể sử dụng công thức SUMIFS 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, đó là cột Total Sales (Tổng doanh thu). Xác định phạm vi cột có chứa giá trị Binders (kẹp tài liệu), ở đây là 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 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 giá trị màu, theo sau là màu (Green) 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 các điều kiện chữ: Color = Green và Category = Binders (Màu = xanh, Category = kẹp tài liệu). 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 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ô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 Total Sales (Tổng doanh thu). 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 tháng. 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 thời gian, 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 chỉ đến cột Tổng doanh thu. Trong phần tiếp theo, bạn cần xác định phạm vi thời gian. 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, bạn có thể kết hợp chức năng tìm kiếm ký tự đại diện với hàm SUMIFS để 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ị chứa các chữ cái ind trong cột Danh mục (Category) và các chữ cái la trong cột Màu sắc (Color).
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 được cộng lại 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 * hoặc dấu ? trong công thức.
Đâ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ả
Dù 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, có rất nhiều cách để bạn tận dụng tối đa hàm SUMIFS trong Excel.
Tìm hiểu các khóa học lập trình của FUNiX tại đây:
Vân Nguyễn
Dịch từ: https://www.makeuseof.com/use-sumifs-function-excel/
Bình luận (0
)