Hàm SUMIFS mở rộng tính hữu ích của hàm SUMIF bằng cách cho phép bạn chỉ định từ 2 đến 127 tiêu chí chứ không phải chỉ một như trong SUMIF.
Thông thường, SUMIFS hoạt động với các hàng dữ liệu được gọi là các bản ghi. Trong một bản ghi , tất cả dữ liệu trong mỗi ô hoặc trường trong hàng có liên quan - chẳng hạn như tên, địa chỉ và số điện thoại của công ty.
Hàm SUMIFS tìm kiếm các tiêu chí cụ thể trong hai hoặc nhiều trường trong bản ghi và chỉ khi nó tìm thấy kết quả phù hợp cho từng trường được chỉ định là dữ liệu cho bản ghi đó được tổng hợp.
01 trên 10
Hàm SUMIFS hoạt động như thế nào
Trong hướng dẫn từng bước của SUMIF, chúng tôi đã so sánh tiêu chí duy nhất của các đại lý bán hàng đã bán được hơn 250 đơn đặt hàng trong một năm.
Trong hướng dẫn này, chúng tôi sẽ thiết lập hai điều kiện bằng cách sử dụng SUMIFS - các đại lý bán hàng ở khu vực bán hàng phía Đông có ít hơn 275 lần bán hàng trong năm qua.
Thiết lập nhiều hơn hai điều kiện có thể được thực hiện bằng cách chỉ định các đối số Criteria_range và Criteria bổ sung cho SUMIFS.
Làm theo các bước trong các chủ đề hướng dẫn dưới đây hướng dẫn bạn tạo và sử dụng hàm SUMIFS được thấy trong hình trên.
Chủ đề hướng dẫn
- Nhập dữ liệu hướng dẫn
- Cú pháp của hàm SUMIFS
- Bắt đầu hàm SUMIFS
- Đối số Sum_range
- Đối số Criteria_range1
- Đối số Criteria1
- Đối số Criteria_range2
- Đối số Criteria2
02 trên 10
Nhập dữ liệu hướng dẫn
Bước đầu tiên để sử dụng hàm SUMIFS trong Excel là nhập dữ liệu.
Nhập dữ liệu vào các ô từ D1 đến F11 của trang tính Excel như trong hình trên.
Hàm SUMIFS và tiêu chí tìm kiếm (dưới 275 đơn đặt hàng và đại lý bán hàng từ khu vực bán hàng phía Đông) sẽ được thêm vào hàng 12 bên dưới dữ liệu.
Các hướng dẫn hướng dẫn không bao gồm các bước định dạng cho trang tính.
Điều này sẽ không can thiệp vào việc hoàn thành hướng dẫn. Bảng tính của bạn sẽ trông khác với ví dụ được hiển thị, nhưng hàm SUMIFS sẽ cho bạn kết quả tương tự.
03 trên 10
Cú pháp của hàm SUMIFS
Trong Excel, cú pháp của hàm tham chiếu đến cách bố trí của hàm và bao gồm tên, khung và đối số của hàm .
Cú pháp cho hàm SUMIFS là:
= SUMIFS (Sum_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)
Lưu ý: Có thể chỉ định tối đa 127 cặp Criteria_range / Criteria trong hàm.
Các đối số của hàm SUMIFS
Các đối số của hàm cho biết hàm nào điều kiện đang được kiểm tra và phạm vi dữ liệu nào sẽ tổng hợp khi các điều kiện đó được đáp ứng.
Tất cả các đối số trong hàm này là bắt buộc.
Sum_range - dữ liệu trong phạm vi ô này được tổng kết khi một kết hợp được tìm thấy giữa tất cả các tiêu chí được chỉ định và đối số Criteria_range tương ứng của chúng.
Criteria_range - nhóm các ô mà hàm này tìm kiếm một đối sánh với đối số Criteria tương ứng.
Tiêu chí - giá trị này được so sánh với dữ liệu trong Criteria_range tương ứng. Dữ liệu thực tế hoặc tham chiếu ô đến dữ liệu có thể được nhập cho đối số này.
04 trên 10
Bắt đầu hàm SUMIFS
Mặc dù có thể chỉ cần gõ hàm SUMIFS vào một ô trong một trang tính , nhiều người thấy dễ dàng hơn khi sử dụng hộp thoại của hàm để nhập hàm.
Các bước hướng dẫn
- Bấm vào ô F12 để biến nó thành ô hiện hoạt . Đây là nơi chúng ta sẽ nhập hàm SUMIFS.
- Nhấp vào tab Công thức .
- Nhấp vào biểu tượng Math & Trig trên ribbon để mở danh sách thả xuống chức năng.
- Nhấp vào SUMIFS trong danh sách để hiển thị hộp thoại hàm SUMIFS.
Dữ liệu mà chúng ta nhập vào các dòng trống trong hộp thoại sẽ tạo thành các đối số của hàm SUMIFS.
Các đối số này cho biết hàm nào chúng ta đang thử nghiệm những điều kiện nào và phạm vi dữ liệu nào để tổng hợp khi các điều kiện đó được đáp ứng.
05 trên 10
Nhập đối số Sum_range
Đối số Sum_range chứa tham chiếu ô đến dữ liệu mà chúng tôi muốn thêm.
Khi hàm tìm thấy một kết hợp giữa tất cả các đối số Criteria và Criteria_range được chỉ định cho một bản ghi , trường Sum_range cho bản ghi đó được bao gồm trong tổng số.
Trong hướng dẫn này, dữ liệu cho đối số Sum_range được đặt trong cột Tổng doanh số .
Các bước hướng dẫn
- Nhấp vào dòng Sum_range trong hộp thoại .
- Đánh dấu các ô từ F3 đến F9 trong trang tính để thêm các tham chiếu ô này vào dòng Sum_range .
06 trên 10
Nhập đối số Criteria_range1
Trong hướng dẫn này, chúng tôi đang cố gắng kết hợp hai tiêu chí trong mỗi bản ghi dữ liệu:
- Đại lý bán hàng từ khu vực bán hàng Đông.
- Các đại lý bán hàng đã kiếm được ít hơn 275 doanh số trong năm nay.
Đối số Criteria_range1 cho biết phạm vi ô mà SUMIFS tìm kiếm khi cố gắng khớp với tiêu chí đầu tiên - vùng bán hàng phía Đông.
Các bước hướng dẫn
- Trong hộp thoại , nhấp vào dòng Criteria_range1 .
- Đánh dấu các ô D3 đến D9 trong trang tính để nhập các tham chiếu ô này làm phạm vi được hàm tìm kiếm tìm kiếm.
07 trên 10
Nhập đối số Criteria1
Trong hướng dẫn này, các tiêu chí đầu tiên mà chúng tôi đang tìm kiếm để phù hợp là nếu dữ liệu trong phạm vi D3: D9 bằng với Đông .
Mặc dù dữ liệu thực tế - chẳng hạn như từ Đông - có thể được nhập vào hộp thoại cho đối số này, cách tốt nhất là thêm dữ liệu vào một ô trong trang tính và sau đó nhập tham chiếu ô đó vào hộp thoại.
Các bước hướng dẫn
- Nhấp vào dòng Criteria1 trong hộp thoại .
- Nhấp vào ô D12 để nhập tham chiếu ô đó. Hàm sẽ tìm kiếm phạm vi được chọn trong bước trước đó cho dữ liệu khớp với tiêu chí này.
- Cụm từ tìm kiếm (Đông) sẽ được thêm vào ô D12 ở bước cuối cùng của hướng dẫn.
Cách tham chiếu ô tăng SUMIFS Tính linh hoạt
Nếu tham chiếu ô, chẳng hạn như D12, được nhập làm Đối số Tiêu chí , hàm SUMIFS sẽ tìm kiếm các kết quả khớp với bất kỳ dữ liệu nào đã được nhập vào ô đó trong trang tính .
Vì vậy, sau khi tìm số tiền bán hàng cho khu vực phía Đông , sẽ dễ dàng tìm thấy dữ liệu tương tự cho một khu vực bán hàng khác chỉ bằng cách thay đổi Đông sang Bắc hoặc Tây trong ô D12. Chức năng sẽ tự động cập nhật và hiển thị kết quả mới.
08 trên 10
Nhập đối số Criteria_range2
Như đã đề cập ở trên, trong hướng dẫn này, chúng tôi đang cố gắng kết hợp hai tiêu chí trong mỗi bản ghi dữ liệu:
- Đại lý bán hàng từ khu vực bán hàng Đông.
- Các đại lý bán hàng đã kiếm được ít hơn 275 doanh số trong năm nay.
Đối số Criteria_range2 cho biết phạm vi ô mà SUMIFS tìm kiếm khi cố gắng khớp với tiêu chí thứ hai - đại lý bán hàng đã bán ít hơn 275 đơn đặt hàng trong năm nay.
Các bước hướng dẫn
- Trong hộp thoại , nhấp vào dòng Criteria_range2 .
- Các tế bào highligt E3 đến E9 trong bảng tính để nhập các tham chiếu ô này làm phạm vi thứ hai được hàm tìm kiếm tìm kiếm.
09 trên 10
Nhập đối số Criteria2
Trong hướng dẫn này, tiêu chí thứ hai mà chúng tôi đang tìm kiếm phù hợp là nếu dữ liệu trong phạm vi E3: E9 nhỏ hơn 275 đơn đặt hàng.
Cũng như đối số Criteria1 , chúng ta sẽ nhập tham chiếu ô tới vị trí của Criteria2 vào hộp thoại chứ không phải chính dữ liệu.
Các bước hướng dẫn
- Bấm vào dòng Criteria2 trong hộp thoại .
- Nhấp vào ô E12 để nhập tham chiếu ô đó. Hàm sẽ tìm kiếm phạm vi được chọn trong bước trước đó cho dữ liệu khớp với tiêu chí này.
- Bấm OK để hoàn thành hàm SUMIFS và đóng hộp thoại.
- Một câu trả lời bằng không (0) sẽ xuất hiện trong ô F12 - ô mà chúng ta đã nhập hàm - vì chúng ta chưa thêm dữ liệu vào các trường Criteria1 và Criteria2 (C12 và D12). Cho đến khi chúng tôi làm, không có gì cho chức năng để thêm lên và do đó, tổng số ở lại bằng không.
- Các tiêu chí tìm kiếm sẽ được thêm vào trong bước tiếp theo của hướng dẫn.
10 trên 10
Thêm tiêu chí tìm kiếm và hoàn thành hướng dẫn
Bước cuối cùng trong hướng dẫn là thêm dữ liệu vào các ô trong trang tính được xác định là chứa các đối số Tiêu chí .
Các bước hướng dẫn
Để được trợ giúp với ví dụ này, hãy xem hình ảnh ở trên.
- Trong ô D12 loại Đông và nhấn phím Enter trên bàn phím.
- Trong ô E12 loại <275 và nhấn phím Enter trên bàn phím ("<" là ký hiệu ít hơn trong Excel).
- Câu trả lời $ 119,719.00 sẽ xuất hiện trong ô F12.
- Chỉ có hai bản ghi trong các hàng 3 và 4 phù hợp với cả hai tiêu chí và, do đó, chỉ tổng số bán hàng cho hai bản ghi đó được tổng kết bởi hàm.
- Tổng số tiền $ 49,017 và $ 70,702 là $ 119,719.
- Khi bạn bấm vào ô F12, hàm hoàn chỉnh
= SUMIFS (F3: F9, D3: D9, D12, E3: E9, E12) xuất hiện trong thanh công thức phía trên trang tính .