Bạn đang xem bài viết Hàm Sumif Là Gì ? Cách Sử Dụng Hàm Sumif Trong Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Hàm SUMIF là gì ?
Trong Microsoft Excel , SUMIF là một công thức được sử dụng để cộng các giá trị lại với nhau trên một phạm vi ô. Hàm SUM cơ bản thêm các giá trị trong một phạm vi ô, bất kể giá trị là gì. Với SUMIF, người dùng có thể xác định các điều kiện cụ thể phải được đáp ứng cho các giá trị trong một phạm vi ô được đưa vào quy trình bổ sung.
Công thức và cú pháp hàm SUMIF
SUMIF( phạm vị, điều kiện, [sum_range] )phạm vi Phạm vi của các ô mà bạn muốn áp dụng các tiêu chí chống lại. tiêu chí Các tiêu chí được sử dụng để xác định các ô cần thêm. sum_range Không bắt buộc. Đây là phạm vi của các ô để tổng hợp lại với nhau. Nếu tham số này bị bỏ qua, nó sử dụng phạm vi là sum_range .
Hàm SUMIF trả về một giá trị số.
Áp dụng đối với
Excel cho Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 cho Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Cách sử dụng hàm SUMIF
Hãy nhìn vào hình ảnh bên phải. Nếu bạn chỉ muốn thêm các giá trị lớn hơn năm, bạn có thể viết công thức SUMIF như sau:
Vì các ô A2 và A5 chứa một giá trị lớn hơn năm, chúng sẽ là các ô duy nhất được cộng với nhau bởi công thức, dẫn đến giá trị là 17.
Ví dụ về hàm SUMIF
Sử dụng hàm sumif với con số
Ví dụ 1: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi A1: A5 nhỏ hơn hoặc bằng 10.
SUMIF(A1:A5,"<=10")Ví dụ 2: Hàm SUMIF sau đây cho kết quả chính xác như nhau. Nó sử dụng toán tử & để nối biểu tượng ‘nhỏ hơn hoặc bằng’ và giá trị trong ô C1.
SUMIF(A1:A5,"<="&C2)Ví dụ 3: Hàm SUMIF bên dưới (ba đối số, đối số cuối cùng là phạm vi cần tính tổng) tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa giá trị 25.
Ví dụ 4: Hàm SUMIF sau đây cho kết quả chính xác tương tự (đối số thứ hai đề cập đến ô D1).
Sử dụng hàm sumif với chữ văn bản
Ví dụ 5: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1:B5 nếu các ô tương ứng trong phạm vi A1:A5 chứa chính xác “circle”.
Ví dụ 6: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 không chứa chính xác “triangle”.
Ví dụ 7: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa chính xác “circle” + 1 ký tự. Một dấu hỏi (?) Khớp với chính xác một ký tự.
Ví dụ 8: Hàm SUMIF bên dưới tính tổng các giá trị trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa một chuỗi gồm 0 hoặc nhiều ký tự + le. Dấu hoa thị (*) khớp với một loạt các ký tự bằng 0 hoặc nhiều hơn.
Ví dụ 9: Các hàm SUMIF bên dưới các giá trị tổng trong phạm vi B1: B5 nếu các ô tương ứng trong phạm vi A1: A5 chứa chính xác “triangle” hoặc “circle8”.
Sử dụng hàm SUMIF thứ ngày tháng
Lưu ý: hàm DATE trong Excel chấp nhận ba đối số: năm, tháng và ngày.
Ví dụ 12: Hàm SUMIFS bên dưới (có chữ S ở cuối) tính tổng doanh số giữa hai ngày (bao gồm cả ngày bắt đầu và ngày kết thúc).
Lưu ý: hàm SUMIFS trong Excel tính tổng các giá trị dựa trên hai hoặc nhiều tiêu chí (đối số đầu tiên là phạm vi cần tính tổng, theo sau là hai hoặc nhiều cặp phạm vi / tiêu chí). Điều chỉnh ngày để tính tổng doanh số trong một tháng, năm cụ thể, v.v.
Hàm Sumif Trong Excel Là Gì? Cách Sử Dụng Hàm Sumifs
Hàm SUMIF là gì? Sự khác nhau giữa hàm SUMIFS trong Excel với hàm SUMIF là như thế nào? Bài viết này sẽ hướng dẫn cho các bạn cú pháp, ý nghĩa, cách sử dụng và một số cách kết hợp giữa hàm SUMIF, SUMIFS với một số hàm thông dụng khác.
#1. Tổng quan về hàm SUMIFS
Trong công việc hàng ngày có sử dụng đến phần mềm Excel, để cộng các giá trị cùng một điều kiện cho trước với nhau thì chúng ta thường nghĩ ngay tới hàm SUMIF. Vậy Hàm SUMIF là gì?
Hàm SUMIF là gì?
Hàm SUMIFS trong Excel là một trong những hàm Excel cơ bản, hàm tính toán thường được dùng trong Excel. Để tính tổng trong Excel chúng ta sẽ dùng đến hàm SUM, nếu muốn thêm 1 điều kiện nhất định cho hàm tính tổng đó sẽ dùng hàm SUMIF.
#2. Cách sử dụng hàm SUMIFS
#2.1. Cú pháp và cách sử dụng hàm SUMIF
Cú pháp hàm này như sau:
=SUMIF(range,criteria,[sum_range])
Cú pháp hàm SUMIF có các đối số sau đây:
– Range (Bắt buộc): Phạm vi ô bạn muốn được đánh giá theo tiêu chí. Các ô trong mỗi phạm vi phải là số hoặc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua. Phạm vi được chọn có thể chứa các ngày ở định dạng Excel tiêu chuẩn (ví dụ bên dưới).
– criteria (Bắt buộc): Tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng các ô nào. Ký tự đại diện có thể được bao gồm-dấu chấm hỏi (?) để khớp với bất kỳ ký tự đơn nào, dấu hoa thị (*) để khớp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.
– sum_range Tùy chọn. Các ô thực tế để cộng nếu bạn muốn cộng các ô không phải là các ô đã xác định trong đối số range. Nếu đối số sum_range bị bỏ qua, Excel cộng các ô được xác định trong đối số range (chính các ô đã được áp dụng tiêu chí).
Sum_range phải có cùng kích cỡ và hình dạng theo phạm vi. Nếu không, hiệu suất có thể bị, và công thức sẽ tính tổng phạm vi ô bắt đầu với ô đầu tiên trong sum_range nhưng có cùng kích thước như phạm vi.
Bước 1: Tại ô G8, chúng ta viết công thức sau: =SUMIF(E8:E22;”nam”;F8:F22).
Trong công thức này, vùng phạm vi đánh giá tiêu chí là từ ô E8 đến ô E22, tiêu chí là giới tính nam, vùng tính tổng là từ ô F8 đến F22.
Bước 2: Bấm phím Enter, kết quả sẽ là 87.860.000 đồng. Tức tổng số tiền lương của các nhân viên nam là 87,86 triệu đồng.
#2.2. Cú pháp và cách sử dụng hàm SUMIFS
Hàm này có cú pháp tổng quát như sau:
=SUMIFS(sum_range,criteria_range1,criteria1,[criter_range2, criteria2], …)
Bước 1: Tại ô G7, chúng ta viết công thức sau:
=SUMIFS(F7:F21;E7:E21;”Nam”;D7:D21;”Văn phòng”)
Trong công thức này, vùng tính tổng là từ ô F7 đến ô F21, vùng tiêu chí đánh giá thứ nhất là từ ô E7 đến ô F21, tiêu chí thứ nhất là giới tính nam, cùng tiêu chí đánh giá thứ 2 là từ ô D7 đến ô D21, tiêu chí thứ 2 là văn phòng.
Bước 2: Bấm phím Enter, kết quả của phép tính sẽ là: 15.910.000.
Vậy, tổng tiền lương của các nhân viên nam ở bộ phận văn phòng là 15.910.000 đồng.
#2.3. Hàm SUMIFS nhiều điều kiện
Ví dụ #3, Tính tổng tiền lương của nhân viên thỏa mãn các điều kiện sau:
– Giới tính nam;
– Thuộc bộ phận văn phòng;
– Sinh năm 1992
Để giải quyết bài toàn này, chúng ta thực hiện như sau:
Bước 1: Tại ô H7, chúng ta viết công thức:
=SUMIFS(G7:G21;F7:F21;”nam”;E7:E21;”văn phòng”;D7:D21;1992)
Bước 2: Bấm phím Enter, chúng ta sẽ ra kết quả là: 8.110.000 đồng.
Hỏi: Sự khác nhau giữa hàm SUMIF và SUMIFS như thế nào?
Trả lời: Hàm SUMIF dùng để tính tổng các phần tử theo 1 điều kiện cho trước, còn SUMIFS dùng để tính tổng các phần tử thỏa mãn từ hai điều kiện trở lên.
Hỏi: Tối đa có thể dùng bao nhiêu điều kiện trong hàm SUMIFS?
Hỏi: Cách sử dụng các phím tắt trong Excel?
Hỏi: Tải file hướng dẫn hàm SUMIFS tại đâu?
Cách Sử Dụng Hàm Sumif Trong Excel
Trong ví dụ này, tham số thứ ba là C2: C6. Đối với mọi giá trị trong A2: A6 khớp với D2, giá trị tương ứng trong C2: C6 sẽ được tổng hợp.
Bạn cũng có thể sử dụng dải ô được đặt tên trong hàm SUMIF. Phạm vi được đặt tên là tên mô tả cho tập hợp các ô hoặc dải ô trong trang tính. Nếu bạn không chắc chắn về cách thiết lập phạm vi được đặt tên trong bảng tính của mình, hãy đọc hướng dẫn của chúng tôi về Thêm phạm vi được đặt tên .
Điều này sẽ cho phép chúng tôi thay thế A2: A6 làm tham số đầu tiên với phạm vi được đặt tên là năm , như sau:
=SUMIF(A2:A6, D2, C2:C6) ‘First parameter uses a standard range
=SUMIF(years, D2, C2:C6) ‘First parameter uses a named range called years
Câu hỏi: Tôi có câu hỏi về cách viết công thức sau trong Excel.
Tôi có một vài tế bào, nhưng tôi chỉ cần tổng của tất cả các tế bào âm tính. Vì vậy, nếu tôi có 8 giá trị, A1 đến A8 và chỉ A1, A4 và A6 là số âm thì tôi muốn B1 là tổng (A1, A4, A6).
Trả lời: Bạn có thể sử dụng hàm SUMIF để tính tổng các giá trị âm như bạn đã mô tả ở trên. Ví dụ:
Công thức này sẽ chỉ tổng hợp các giá trị trong các ô A1: A8 trong đó giá trị là âm (ví dụ: <0).
Câu hỏi: Trong Microsoft Excel, tôi đang cố gắng đạt được những điều sau đây với hàm IF:
Nếu một giá trị trong bất kỳ ô nào trong cột F là “thực phẩm” thì hãy cộng giá trị của ô tương ứng trong cột G (ví dụ ô tương ứng cho F3 là G3). Hàm IF được thực hiện trong một ô khác hoàn toàn. Tôi có thể làm điều đó cho một cặp tế bào nhưng tôi không biết làm thế nào để làm điều đó cho toàn bộ một cột. Bạn có thể giúp không?
Tại thời điểm này, tôi đã có điều này:
Trả lời: Công thức này có thể được tạo bằng cách sử dụng công thức SUMIF thay vì sử dụng hàm IF :
=SUMIF(F1:F10,”=food”,G1:G10)
Điều này sẽ đánh giá 10 hàng dữ liệu đầu tiên trong bảng tính của bạn. Bạn có thể cần phải điều chỉnh phạm vi cho phù hợp.
Tôi nhận thấy rằng bạn tách các tham số của mình bằng dấu chấm phẩy, vì vậy bạn có thể cần phải thay thế dấu phẩy trong công thức trên bằng dấu chấm phẩy.
Cách Sử Dụng Hàm Sumifs Và Sumif Với Nhiều Điều Kiện
Trong bài viết này, Học Excel Online sẽ giải thích sự khác biệt giữa các hàm SUMIF và SUMIFS theo cú pháp và cách sử dụng của chúng, bên cạnh đó, cũng cung cấp một số ví dụ về công thức để tính tổng các giá trị có nhiều điều kiện đồng thời xảy ra () hoặc tất cả không đồng thời xảy ra ( OR) trong phiên bản Excel 2013, 2010, 2007, 2003 và trước nữa.
range – dải của các ô được đánh giá theo điều kiện mà bạn đưa ra, mang tính bắt buộc.
criteria – điều kiện cần phải đáp ứng, mang tính bắt buộc
sum_range – các ô tính tổng nếu thỏa điều kiện, mang tính tùy chọn.
Như bạn thấy, cú pháp của hàm Excel SUMIF chỉ cho phép một điều kiện. Tuy nhiên, bên trên, chúng tôi có nói rằng Excel SUMIF có thể được sử dụng để tính tổng các giá trị với nhiều điều kiện. Làm thế nào mà có thể được? Thực tế, bạn cần thêm các kết quả của vài hàm SUMIF và sử dụng các công thức SUMIF với các điều kiện mảng, như trong ví dụ tiếp theo.
Hàm SUMIFS – cú pháp và cách sử dụng:
Bạn sử dụng SUMIFS trong Excel để tìm một tính tổng có giá trị các giá trị dựa trên nhiều điều kiện. Hàm SUMIFS đã được giới thiệu trong Excel 2007, vì vậy bạn có thể sử dụng nó trong tất cả các phiên bản của Excel 2013, 2010 và 2007.
So với SUMIF, thì cú pháp SUMIFS phức tạp hơn một chút:
sum_range – một hoặc nhiều ô tính tổng, mang tính bắt buộc. Đây có thể là một ô duy nhất, một dải ô hoặc dải có tên. Chỉ có các ô chỉ chứa số mới được tính tổng; Còn giá trị ô trống và giá trị văn bản thì sẽ bị bỏ qua.
Chú ý: Hàm SUMIFS hoạt động với biểu thức logic với AND, nghĩa là mỗi ô trong đối số sum_range chỉ được tính tổng nếu tất cả các điều kiện được chỉ định là đúng cho ô đó.
Bây giờ tập hợp các thông số trên, và bạn sẽ nhận được công thức SUMIFS sau:
= SUMIFS (C2: C9, A2: A9, F1, B2: B9, F2)
Sử dụng SUMIFS và SUMIF trong Excel – một số điều cần nhớ:
Mặc dù nét chung thì khá rõ ràng – tương đồng về điểm đến cho kết quả cuối cùng và các tham số – trong khi sự khác biệt dù không rõ rang nhưng vẫn rất cần thiết.
1. Trình tự của các đối số
Trong các hàm Excel SUMIF và SUMIFS, thứ tự các đối số là khác nhau. Cụ thể, sum_range là tham số thứ nhất trong SUMIFS, nhưng lại đứng thứ 3 trong công thức SUMIF.
Khi bắt đầu học, bạn sẽ cảm thấy có vẻ như Microsoft đã cố ý làm phức tạp hóa cho người học và người dùng nó. Tuy nhiên, khi xem xét kỹ hơn, bạn sẽ thấy lý do thực chất đằng sau nó. Vấn đề là sum_range là tùy chọn trong SUMIF. Nếu bạn bỏ qua nó, không có vấn đề, SUMIF công thức của bạn sẽ tính tổng các giá trị trong range (tham số đầu tiên).
Trong SUMIFS, sum_range là rất quan trọng và là bắt buộc, và đó là lý do tại sao nó đến trước. Có thể những người của Microsoft nghĩ rằng sau khi thêm các dải/điều kiện xem xét thứ 10 hoặc 100, thì có ai đó có thể quên xác định dải để tính tổng:)
Tóm lại, nếu bạn đang sao chép và chỉnh sửa các hàm này, hãy đảm bảo bạn đặt các thông số theo thứ tự đúng.
2. Kích thước của đối số sum_range và criteria_range
Trong hàm SUMIF, đối số sum_range không nhất thiết phải có cùng kích thước với đối số range, miễn là bạn có ô phía trên bên trái. Trong hàm SUMIFS, mỗi criteria_range phải chứa cùng một số hàng và cột như tham số sum_range.
Ví dụ, công thức = SUMIF (A2: A9, F1, C2: C18) sẽ trả lại kết quả đúng vì Excel chỉ xem ô phía trên bên trái trong đối số sum_range (C2 trong ví dụ này là đúng) và sau đó bao gồm số cột và hàng giống như kích thước của đối số range.
Công thức SUMIFS: = SUMIFS (C2: C9, A2: A9, “apples”, B2: B10, “Pete”) sẽ báo vì criter_range2 (B2: B10) không khớp với criteria_range1 (A2: A9) và sum_range (C2: C9).
Có vẻ như chúng ta đã tiếp cận tạm xong lý thuyết rồi, nên mục sau chúng ta sẽ chuyển sang thao tác thực hành (chính là các ví dụ công thức:)
Đăng ký ngay: Học Excel dành cho người đi làm
Làm thế nào để sử dụng SUMIFS trong Excel – ví dụ công thức
Ví dụ 1. Công thức SUMIFS với toán tử so sánh
Trong trường hợp bạn muốn tính tổng các giá trị với nhiều điều kiện dựa trên ngày hiện tại, hãy sử dụng hàm TODAY () trong điều kiện của hàm SUMIFS của bạn, như được trình bày bên dưới. Công thức sau đây tính tổng giá trị trong cột D nếu ngày tương ứng trong cột C rơi vào khoảng thời gian 7 ngày vừa qua, có bao gồm ngày hôm nay:
Tương tự như vậy, bạn có thể sử dụng hàm SUMIF để tính tổng các giá trị trong một dải có dữ liệu là ngày xác định. Ví dụ: công thức SUMIFS sau sẽ thêm các giá trị trong các ô C2: C9 nếu ngày trong cột B rơi trong khoảng giữa ngày 1 tháng 10 năm 2014 và ngày 31 tháng 10 năm 2014, như sau:
Kết quả tương tự có thể đạt được bằng cách tính toán sự khác biệt của hai hàm SUMIF, như được minh họa trong ví dụ này – Cách sử dụng SUMIF để tính tổng các giá trị trong một dải ngày xác định. Tuy nhiên, bạn cũng thấy rằng công thức SUMIFS là dễ dàng hơn và dễ hiểu hơn nhiều, phải không nào?
Hướng dẫn học Excel cơ bản
Ví dụ 3. Hàm SUMIFS với ô trống và không trống
Khi phân tích báo cáo và dữ liệu khác, bạn thường cần phải tính tổng các giá trị tương ứng với ô trống hoặc không trống.
Những ô trống
“=”
Tính tổng các giá trị mà có ô trống tương ứng (hoàn toàn không chứa dũ liệu – không công thức, và chuỗi có 0 kí tự)
=SUMIFS(C2:C10, A2:A10, “=”, B2:B10, “=”)
Tính tổng giá trị trong các ô C2:C10 nếu các ô tương ứng với nó trong cột A và B là ô hoàn toàn trống.
“”
Tính tổng các giá trị tương ứng với các ô trắng “nhận định trực quan”, bao gồm các giá trị chứa các chuỗi rỗng được trả về bởi một số hàm Excel khác (ví dụ: ô có công thức như = “”).
=SUMIFS(C2:C10, A2:A10, “”, B2:B10, “”)
Tính tổng các giá trị trong các ô C2:C10 (có cùng các điều kiện) như công thức ở trên, nhưng có bao gồm các chuỗi trống.
Những ô không trống
Tính tổng các giá trị mà có các giá trị tương ứng là các ô không trống, và không bao gồm chuỗi có chiều dài bằng 0
Tính tổng giá trị trong các ô C2:C10 nếu các ô tương ứng với nó trong cột A và B không là ô trống, và không bao gồm các ô với chuỗi trống.
Giả sử bạn có ngày đặt hàng trong cột B, ngày giao hàng trong cột C và số lượng trong cột D. Làm thế nào để bạn tính được tổng số sản phẩm chưa được giao? Nghĩa là bạn muốn biết tổng các giá trị tương ứng với các ô không rỗng trong cột B và các ô rỗng trong cột C.
Giải pháp là sử dụng công thức SUMIFS với 2 điều kiện:
Như đã lưu ý ở phần đầu của bài này, thì hàm SUMIFS được thiết kế với AND. Nhưng nếu bạn cần tính tổng các giá trị có nhiều điều kiện OR, nghĩa là khi có ít nhất một trong các điều kiện sẽ được đáp ứng?
Ví dụ 1. SUMIF + SUMIF
Giải pháp đơn giản nhất là tính tổng các kết quả trả về bởi một số hàm SUMIF. Ví dụ: công thức sau đây sẽ minh họa cách tính tổng số sản phẩm do Mike và John cung cấp:
= SUMIF (C2: C9, “Mike”, D2: D9) + SUMIF (C2: C9, “John”, D2: D9)
Ví dụ 2. SUM & SUMIF với đối số mảng
Các giải pháp trên rất đơn giản và thực sự hiệu quả cao khi chỉ có một vài điều kiện. Nhưng một công thức SUMIF + SUMIF có thể được phát triển rất nhiều nếu bạn muốn tính tổng các giá trị với nhiều điều kiện OR. Trong trường hợp này, cách tiếp cận tốt hơn là sử dụng một đối số như điều kiện mảng trong hàm SUMIF, như sau:
Bạn có thể bắt đầu bằng cách liệt kê tất cả các điều kiện của bạn, được ngăn cách bởi dấu phẩy và sau đó đặt chúng trong {dấu ngoặc nhọn}, điều này được gọi là mảng.
Trong ví dụ trước, nếu bạn muốn tính tổng các sản phẩm do John, Mike và Pete cung cấp, điều kiện mảng của bạn sẽ như thế này {“John”, “Mike”, “Pete”}. Và hàm SUMIF hoàn chỉnh là: =SUMIF (C2: C9, {“John”, “Mike”, “Pete”}, D2: D9).
Đối số mảng bao gồm 3 giá trị bắt buộc phải có trong công thức SUMIF để trả về ba kết quả độc lập, nhưng vì chúng ta viết công thức trong một ô duy nhất, nó sẽ chỉ trả lại kết quả đầu tiên – tức là tổng số sản phẩm do John cung cấp. Để nó hoạt động, bạn phải sử dụng vài mẹo – lồng công thức SUMIF của bạn trong một hàm SUM, như sau:
= SUM (SUMIF (C2: C9, {“John”, “Mike”, “Pete”}, D2: D9))
Phương pháp này sẽ chạy với các con số hoặc các giá trị văn bản. Ví dụ: nếu thay vì bạn có tên các nhà cung cấp trong cột C, mà bạn chỉ có ID nhà cung cấp như 1, 2, 3, v.v … thì công thức SUMIF của bạn sẽ như sau:
= SUM (SUMIF (C2: C9, {1,2,3}, D2: D9))
Không giống như các giá trị văn bản, các con số không cần phải được bao gồm trong dấu nháy kép trong đối số mảng.
Trong trường hợp, Bạn thích liệt kê các điều kiện trong vài ô thay vì chỉ định chúng trực tiếp trong công thức, thì bạn có thể sử dụng SUMIF kết hợp với hàm SUMPRODUCT để nhân nhiều thành phần trong các mảng xác định rồi trả lại tổng số lượng của các sản phẩm đó.
Nhưng tất nhiên, không có gì ngăn cản nếu bạn muốn liệt kê các giá trị trong một điều kiện mảng của hàm SUMIF”
= SUMPRODUCT (SUMIF (C2: C9, {“Mike”, “John”, “Pete”}, D2: D9))
Kết quả trả về bởi cả hai công thức sẽ giống như những gì bạn thấy trong hình:
Như thường lệ, một ví dụ có thể giúp minh họa tốt hơn điều trên. Trong bảng các nhà cung cấp trái cây của chúng tôi, hãy thêm Delivery Date (Ngày giao hàng) (cột E) và tìm tổng số lượng được Mike, John và Pete giao vào tháng 10.
Công thức được tạo ra bởi cách phương pháp này bao gồm nhiều lần lặp đi lặp lại và trông có vẻ rườm rà, tuy vậy nhưng nó rất dễ hiểu, và điểm quan trọng nhất là nó hoạt động tốt 🙂
Tôi đã cố gắng giải thích bản chất của cách tiếp cận này trong ví dụ với SUMIF, vì vậy bây giờ chúng ta chỉ có thể sao chép công thức đó, thay đổi thứ tự các đối số (có sự khác nhau ở SUMIF và SUMIFS – như trên đã đề cập) và thêm các điều kiện bổ sung. Công thức kết quả sau sẽ nhỏ gọn hơn SUMIFS + SUMIFS:
Kết quả trả về bằng công thức này giống như những gì bạn thấy trong ảnh chụp màn hình ở trên.
Ví dụ 3. SUMPRODUCT & SUMIFS
Nếu như bạn nhớ, thì cách tiếp cận SUMPRODUCT khác với hai cách trước đó theo cách bạn nhập mỗi điều kiện của bạn vào một ô riêng biệt thay vì nhập chúng trực tiếp trong công thức. Trong trường hợp một số điều kiện, hàm SUMPRODUCT sẽ không đủ và bạn sẽ phải sử dụng hàm ISNUMBER và hàm MATCH.
Vì vậy, giả sử rằng các tên người cung cấp nằm trong các ô H1: H3, Ngày bắt đầu (Start date) nằm trong ô H4 và ngày kết thúc (End date) trong ô H5, công thức SUMPRODUCT của chúng tôi có dạng sau:
Tôi hy vọng lời giải thích trên có ý nghĩa với bạn. Và thậm chí nếu không, thì thực sự là bạn chỉ cần nhớ nguyên tắc này – sử dụng toán tử đôi (-) khi bạn sử dụng các toán tử so sánh trong các công thức SUMPRODUCT.
Sử dụng hàm SUM trong các công thức mảng
Như bạn đã biết, thì Microsoft đã thực hiện hàm SUMIFS trong Excel 2007. Nếu ai đó vẫn sử dụng Excel 2003, 2000 hoặc phiên bản trước đó, thì bạn sẽ phải sử dụng công thức mảng SUM để thêm các giá trị có nhiều điều kiện AND. Đương nhiên, phương pháp tiếp cận này cũng hoạt động trong các phiên bản hiện đại của Excel 2013 – 2007, và có thể được coi là sự lỗi thời của hàm SUMIFS.
Ví dụ 1. Sum với các điều kiện AND trong Excel 2003 và phiên bản trước đó:
Chúng ta hãy quay lại ví dụ đầu tiên mà chúng ta tính tổng số lượng ứng với một loại trái cây và nhà cung cấp nhất định:
= SUMIFS (C2: C9, A2: A9, “apples”, B2: B9, “Pete”)
Điều kiện 1: A2: A9 = “apples”
Điều kiện 2: B2: B9 = “Pete”
Sau đó, bạn viết một công thức SUM có nhân tất cả trong trường hợp: tương ứng với các cặp dải / điều kiện của bạn, mỗi cặp được đặt trong ngoặc đơn. Hệ số nhân cuối cùng là dải để tính tổng, là C2: C9 trong trường hợp này:
= SUM ((A2: A9 = “apples”) * (B2: B9 = “Pete”) * (C2: C9))
Như được minh họa trong hình bên dưới, thì công thức này hoạt động rất tốt trong phiên bản Excel 2013 mới nhất.
Ví dụ 2. Các công thức mảng SUM trong các phiên bản Excel hiện đại
Ngay cả trong các phiên bản hiện đại của Excel 2013, 2010 hoặc 2007, sức mạnh của hàm SUM không bao giờ bị đánh giá thấp. Công thức mảng SUM không chỉ đơn giản là luyện tập hướng suy nghĩ mà còn có giá trị thực tế, như trong ví dụ sau đây:
Giả sử bạn có hai cột, B và C, và bạn cần phải biết được cột C lớn hơn gấp bao nhiêu lần so với cột B, khi một giá trị trong cột C lớn hơn hoặc bằng 10. Một giải pháp mà bạn có thể nghĩ ra đo là sử dụng công thức mảng SUM:
Điều kiện 1: Giá trị trong cột B (Ordered items) lớn hơn 0
Điều kiện 2: Một giá trị trong cột C (Delivered) ít hơn trong cột B
Điều kiện 3: Ngày trong cột D (Due date) ít hơn 11/1/2014.
Đặt ba cặp range / điều kiện lại với nhau, bạn sẽ nhận được công thức sau:
Ngoài hàm SumIf, SumIfS giúp tính tổng theo nhiều điều kiện, chúng ta còn có thể sử dụng các hàm khác như SUMPRODUCT để làm việc này. Trong thực tế thì yêu cầu tính toán và đặc điểm dữ liệu sẽ quyết định tới việc chúng ta sẽ cần dùng hàm nào, dùng như thế nào. Do đó bạn cần bổ sung thêm cho mình càng nhiều kiến thức về hàm, về tổ chức dữ liệu thì càng thuận lợi khi làm việc.
Tiếp đó là việc cần phải kết hợp các công cụ của Excel để tăng hiệu quả công việc như: định dạng dữ liệu, định dạng theo điều kiện, trích lọc, sắp xếp, báo cáo bằng Pivot Table, vẽ biểu đồ…
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học Excel từ cơ bản đến chuyên gia dành cho người đi làm
Hướng Dẫn Sử Dụng Hàm Sumif Trong Excel
Hàm SUMIF trong Excel có thể được hiểu là sự kết hợp giữa hàm SUM với hàm IF, nghĩa là cộng có điều kiện. SUMIF sẽ được dùng trong không ít các công việc, ví dụ như tổng hợp thống kê.
Excel có hàm SUM để cộng giá trị của một dãy hàng hay một dãy cột, cấu trúc dạng SUM(A20:F20) hoặc SUM(A1:A6). Tuy nhiên phép cộng sẽ có chọn lọc hơn nếu áp theo một tiêu chí nào đó, và lúc đó chúng ta sẽ dùng hàm SUMIF hoặc SUMIFS.
Hướng dẫn cách sử dụng hàm SUMIF trong Excel
Khi dùng hàm SUMIF chúng ta có cấu trúc như sau:
=SUMIF(khoảng xét tiêu chí, tiêu chí, khoảng tính)
Như vậy, hàm SUMIF sẽ xét trong một dãy ô hoặc dãy cột, kiểu như A20:F20 (từ ô cột A hàng 20 đến ô cột F hàng 20) hay A1:A6. Bất kỳ ô nào có giá trị đạt tiêu chí sẽ được dùng để tham chiếu sang ô tương ứng trong khoảng tính, kiểu như A21:F21 hay B1:B6.
Trong khi đó với tiêu chí để so sánh, ngoại trừ tiêu chí dùng giá trị chữ số, bất kỳ tiêu chí nào dùng ký tự text hoặc dùng biểu thức toán học đều cần đặt trong ngoặc kép.
Ví dụ, chúng ta có một bảng thu nhập của các thành viên trong công ty, với phần kê thu nhập ở cột D và phần thông tin chức vụ ở cột C. Lúc đó để có thống kê tự động tổng thu nhập của những ai là nhân viên, chúng ta sẽ có cấu trúc kiểu như sau:
=SUMIF(C2:C10,”Nhân viên”,D2:D10)
Thực ra trong 3 tham số của hàm SUMIF nêu trên chúng ta có thể bỏ trống khoảng tính, và lúc đó khoảng tính sẽ chính là khoảng xét tiêu chí.
Với ví dụ trên, nếu muốn thống kê tự động thu nhập của những ai có thu nhập từ 10 triệu trở lên thì chúng ta dùng cấu trúc kiểu như sau:
Hướng dẫn cách sử dụng hàm SUMIFS trong Excel
So với SUMIF, chữ “S” trong hàm SUMIFS chính là thể hiện của việc có nhiều tiêu chí cần thỏa mãn hơn, từ 2 tiêu chí trở lên. Hàm SUMIFS 2 tiêu chí có cấu trúc như sau:
=SUMIFS(khoảng tính, khoảng xét tiêu chí 1, tiêu chí 1, khoảng xét tiêu chí 2, tiêu chí 2)
Vẫn với ví dụ nêu trên, nếu muốn thống kê tự động thu nhập của những ai là nhân viên đồng thời có thu nhập từ 10 triệu đồng trở lên, chúng ta sẽ phải dùng hàm SUMIFS 2 tiêu chí như sau:
S
Với những hàm IF, AND hay OR trong Excel thì những ai đã từng học lập trình sẽ dễ dàng nắm bắt được hơn, tuy nhiên những ai chưa từng học thì hoàn toàn có thể nắm được sau một vài ví dụ.
Cập nhật thông tin chi tiết về Hàm Sumif Là Gì ? Cách Sử Dụng Hàm Sumif Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!