HÀM TÍNH TỔNG CÓ NHIỀU ĐIỀU KIỆN – HÀM SUMIFS TRONG EXCEL  – TIN HỌC TÂY VIỆT

CHỨC NĂNG:

Hàm SUMIFS trong excel được sử dụng để tính tổng các giá trị số khi có 1 điều kiện.

CÚ PHÁP HÀM SUMIFS

= SUMIFS ( Vùng cần tính tổng, Vùng điều kiện 1, Điều kiện 1, [Vùng điều kiện 2, Điều kiện 2] )

+ Vùng cần tính tổng: là vùng chứa các giá trị tính tổng. Sử dụng F4 để chuyển sang địa chỉ tuyệt đối.

+ Vùng điều kiện 1: là vùng chứa điều kiện cần số 1 để tính tổng. Sử dụng F4 để chuyển sang địa chỉ tuyệt đối.

+ Điều kiện 1: là điều kiện số 1 để tính tổng, biểu thức điều kiện có thể áp dụng được cho các ký tự, ô trống, kiểu số, ngày tháng và có thể sử dụng các ký tự đại diện là “*’, “?”. Trong đó, ký tự “*” đại diện cho nhiều ký tự, ký tự “?” đại diện cho 1 ký tự.

+ Vùng điều kiện 2: là vùng chứa điều kiện cần số 2 để tính tổng. Sử dụng F4 để chuyển sang địa chỉ tuyệt đối. Tương tự như vùng điều kiện số 1.

+ Điều kiện 2: là điều kiện số 1 để tính tổng, tương tự như điều kiện số 1.

 CHÚ Ý

+ Hàm SUMIFS cho phép thực hiện tối đa tới 127 điều kiện. Do đó, nếu phát sinh thêm 1 điều kiện nào đó thì trong cú pháp phải thêm 1 cặp đối số là Vùng điều kiện và điều kiện.

ỨNG DỤNG CỦA HÀM TÍNH TỔNG CÓ NHIỀU ĐIỀU KIỆN SUMIFS

Hàm SUMIFS được ứng dụng rất nhiều trong thực tế để thực hiện việc thống kê dữ liệu trong công việc hằng ngày của các bạn. Do vậy, các bạn cần phải nắm rõ những dạng điều kiện và cách áp dụng công thức để có thể thống kê cơ sở dữ liệu một cách nhanh chóng.

Ví dụ: Cho bảng tính như hình dưới và thực hiện các yêu cầu theo các dạng

1.  HÀM SUMIFS TÍNH TỔNG CÓ NHIỀU ĐIỀU KIỆN CHO CỘT ĐIỀU KIỆN CHỨA KÝ TỰ

Dựa vào bảng tính trên hãy thực hiện yêu cầu: Tính tổng số lượng của tập Vĩnh Tiến sản xuất ở Đồng Nai

Chúng ta tiến hành phân tích các đối số của hàm SUMIFS cụ thể như sau:

+ Vùng cần tính tổng: đề bài yêu cầu tính tổng số lượng, do vậy vùng cần tính tổng chính là cột F, chúng ta quét từ ô F3 đến F11. Và nhớ bấm phím F4 trên bàn phím để chuyển sang địa chỉ tuyệt đối. Kết quả là: $F$3:$F$11

+ Vùng điều kiện 1: điều kiện số 1 được đề bài đưa ra là tập Vĩnh Tiến. Dựa vào bảng tính, chúng ta nhận thấy Vĩnh Tiến xuất hiện ở cột D. Do vậy, vùng điều kiện là vùng quét từ ô D3 đến D11. Và thực hiện bấm phím F4 để chuyển sang địa chỉ tuyệt đối. Kết quả là: D$3:D$11

+ Điều kiện 1: điều kiện số 1 theo đề bài yêu cầu là Vĩnh Tiến, tuy nhiên Vĩnh Tiến chỉ là những ký tự bên trái trong cột D, ngoài Vĩnh Tiến còn có rất nhiều ký tự khác trong cùng 1 ô. Do vậy, các bạn cần phải sử dụng những ký tự đại diện. Cụ thể là: “Vĩnh Tiến”&”*” 

+ Vùng điều kiện 2: điều kiện số 2 của đề bài là Đồng Nai, dựa vào bảng tính, các bạn nhận thấy Đồng Nai nằm ở cột E, do vậy vùng quét của các bạn là từ ô E3 đến ô E11. Sử dụng F4 để chuyển sang địa chỉ tuyệt đối. Kết quả là: $E$3:$E$11

+ Điều kiện 2: đề bài yêu cầu điều kiện là Đồng Nai, các bạn có thể nhận thấy Đồng Nai nằm đầy đủ trong cột E và hoàn toàn không có ký tự nào khác. Do vậy các bạn cứ yên tâm và nhập đầy đủ vào công thức như sau: “Đồng Nai”

Và cuối cùng, kết quả chúng ta thu được là:

= SUMIFS ($F$3:$F$11, D$3:D$11, “Vĩnh Tiến”&”*”, $E$3:$E$11, “Đồng Nai”)     => 400

2. HÀM SUMIFS TÍNH TỔNG CÓ NHIỀU ĐIỀU KIỆN CHO CỘT CHỨA ĐIỀU KIỆN LÀ SỐ

Dựa vào bảng tính trên, Hãy tính tổng thành tiền của các mặt hàng bán với số lượng từ 150 đến 300. 

Đối với các điều kiện dạng số được phép sử dụng các biểu thức so sánh (“<“, “<=”, “>”, “>=”,”=”, “<>”) ở trước các giá trị điều kiện.

Chúng ta phân tích các thành phần như sau:

+ Vùng cần tính tổng: đề bài yêu cầu tính tổng thành tiền, do đó, chúng ta quét từ ô H3 đến H11. Và bấm phím F4 trên bàn phím để chuyển sang địa chỉ tuyệt đối. Kết quả là: $H$3:$H$11

+ Vùng điều kiện 1: điều kiện số 1 được đề bài đưa ra là số lượng >=150. Do vậy, vùng điều kiện là vùng quét từ ô F3 đến F11. Và thực hiện bấm phím F4 để chuyển sang địa chỉ tuyệt đối. Kết quả là: F$3:F$11

+ Điều kiện 1: điều kiện số 1 theo đề bài yêu cầu là số lượng >=150, nên khi đưa giá trị vào công thức ta được biểu thức như sau: “>=150” 

+ Vùng điều kiện 2: điều kiện số 2 của đề bài là số lượng <=300, do vậy vùng điều kiện cũng là cột số lượng và kết quả tương tự điều kiện 1 là: $F$3:$F$11

+ Điều kiện 2: tương tự như điều kiện 1, số lượng yêu cầu là <=300, nên biểu thức được viết là: “<=300”

Và cuối cùng, kết quả chúng ta thu được là:

= SUMIFS ($H$3:$H$11, $F$3:$F$11, “>=150”, $F$3:$F$11, “<=300”)     => 2,510,000

3. HÀM SUMIFS TÍNH TỔNG CÓ NHIỀU ĐIỀU KIỆN CHO CỘT CHỨA NGÀY THÁNG

Dựa vào bảng tính trên, Hãy tính tổng thành tiền của tất cả các mặt hàng bán được trong tháng 4. 

Tương tự như đối với các điều kiện cho ô chứa số. Dạng điều kiện cho các ô chứa ngày tháng cũng được phép sử dụng các biểu thức so sánh (“<“, “<=”, “>”, “>=”,”=”, “<>”) ở trước các giá trị điều kiện.

Với yêu tính tổng thành tiền của các mặt hàng bán trong tháng 4. Như vậy điều kiện của đề bài là ngày bán phải bắt đầu từ ngày 01/04/2017 đến ngày 30/04/2017. Ngoài khoảng thời gian này sẽ không được tính tổng. Do đó, Chúng ta phân tích các thành phần của công thức được viết như sau:

+ Vùng cần tính tổng: đề bài yêu cầu tính tổng thành tiền, do đó, vùng quét của chúng ta vẫn là: $H$3:$H$11.

+ Vùng điều kiện 1: điều kiện số 1 được phân tích là bắt đầu từ ngày 01/04/2017. Do vậy, vùng điều kiện là vùng của cột ngày tháng, quét từ ô C3 đến C11, thực hiện bấm phím F4 để chuyển sang địa chỉ tuyệt đối. Kết quả là: C$3:C$11.

+ Điều kiện 1: điều kiện số 1 là bắt đầu từ ngày 01/04/2017. Do vậy, biểu thức được ghi trong công thức là: “>=01/04/2017” .

+ Vùng điều kiện 2: điều kiện số 2 được phân tích là ngày kết thúc của tháng 4 (nghĩa là: 30/04/2017). Do đó, vùng điều kiện cũng là vùng chứa cột ngày tháng và kết quả tương tự như  điều kiện 1 là: $C$3:$C$11.

+ Điều kiện 2: tương tự như điều kiện 1, biểu thức được viết là: “<=30/04/2017”.

Và cuối cùng, kết quả chúng ta thu được là:

= SUMIFS ($H$3:$H$11, $C$3:$C$11, “>=1/4/2017”, $C$3:$C$11, “<1/5/2017”)     => 3,300,000

4. HÀM SUMIFS TÍNH TỔNG CÓ NHIỀU ĐIỀU KIỆN KẾT HỢP

Dựa vào bảng tính đã cho, Hãy tính tổng thành tiền của mặt hàng Vĩnh Tiến và Tân Mai loại 100 trang. 

Đây là một yêu cầu kết hợp, các bạn phân tích các điều kiện với nhau. Các điều kiện nào xảy ra đồng thời và các điều kiện nào xảy ra không đồng thời. Đối với những điều kiện xảy ra đồng thời thì chúng ta sẽ kết hợp chung trong hàm SUMIFS. Còn nếu điều kiện xảy ra không đồng thời thì phải thực hiện trên một SUMIFS khác.

Chúng ta hãy bắt đầu phân tích điều kiện trong trường hợp này. Đề bài yêu cầu tính tổng thành tiền của mặt hàng Vĩnh Tiến và Tân Mai loại 100 trang, như vậy có thể hiểu chỉ tính tổng thành tiền của loại tập “Vĩnh Tiến – Loại 100 trang” với tập “Tân Mai – Loại 100 trang” mà thôi. Và trong trường hợp này thì chỉ có tập Vĩnh Tiến xảy ra đồng thời với 100 trang, nên được viết chung trong 1 hàm SUMIFS. Còn tập Tân Mai xảy ra đồng thời vơí 100 trang sẽ được viết trong một hàm SUMIFS khác. Và sau khi viết 2 hàm SUMIFS khác nhau, chúng ta sẽ tiến hành cộng hai hàm SUMIFS đó lại mới đúng yêu cầu của đề bài. Như vậy chúng ta sẽ có hai hàm SUMIFS được viết như sau:

Hàm SUMIFS để tính cho tập Vĩnh Tiến 100 trang:

= SUMIFS( $H$3:$H$11, $D$3:$D$11, “Vĩnh Tiến”&”*”, $D$3:$D$11, “*”&”100″&”*”)

Hàm SUMIFS để tính cho tập Tân Mai 100 trang:

= SUMIFS( $H$3:$H$11, $D$3:$D$11, “Tân Mai”&”*”, $D$3:$D$11, “*”&”100″&”*”)

Và để tính tổng chung thì chúng ta cộng 2 hàm SUMIFS trên lại với nhau, kết quả cuối cùng:

= SUMIFS( $H$3:$H$11, $D$3:$D$11, “Vĩnh Tiến”&”*”, $D$3:$D$11, “*”&”100″&”*”) + SUMIFS( $H$3:$H$11, $D$3:$D$11, “Tân Mai”&”*”, $D$3:$D$11, “*”&”100″&”*”)     => 1,440,000

 

Tóm lại: 

Với hàm SUMIFS các bạn có thể thoải mái thực hiện tính tổng với nhiều điều kiện khác nhau, tuy nhiên các điều kiện nằm trong đối số của hàm tính tổng có nhiều điều kiện SUMIFS là phải xảy ra đồng thời. Nếu các điều kiện xảy ra không đồng thời thì phải tính ở các hàm SUMIFS khác nhau rồi cộng lại.