Trang thông tin điện tử Công an Bắc Kạn
Trang thông tin điện tử Công an Bắc Kạn
Hàm SUMIF trong Excel là gì? Cách dùng hàm SUMIF như thế nào? Hãy cùng Quantrimang.com tìm hiểu nhé!
Excel là một chương trình bảng tính nổi tiếng của Microsoft mà sắp xếp các số và dữ liệu bằng cách dùng hàm và công thức. Một trong số các hàm hữu ích nhất mà bạn nên biết khi dùng Excel là hàm SUMIF. Bạn có thể dùng hàm SUMIF trong nhiều tình huống khác nhau bởi đây là hàm Excel khá linh hoạt. Tuy nhiên, nó có thể hơi khó với người mới bắt đầu. Tuy nhiên, những điều cần biết về cách dùng SUMIF trong Excel dưới đây sẽ giúp bạn có khởi đầu dễ dàng.
SUMIF là hàm dùng để tính tổng các giá trị thỏa mãn điều kiện nhất định, được giới thiệu lần đầu trong Excel 2007. Hàm SUMIF có thể sử dụng cho việc tính tổng các ô dựa vào ngày, số liệu và văn bản khớp với điều kiện đã cung cấp. Hàm này hỗ trợ các phép tính logic (>, <, <>, =) và ký hiệu (*,?) cho phù hợp với từng phần.
Bạn hẳn sẽ thắc mắc, hàm SUMIF khác gì hàm SUM? Hàm SUMIF mở rộng khả năng của hàm SUM, thay vì tính tổng trong một phạm vi nhất định, để được tính tổng trong SUMIF các ô buộc phải thỏa mãn điều kiện mà người dùng truyền vào tham số criteria. Hàm SUMIF tiết kiệm rất nhiều công sức nếu bạn muốn tính tổng doanh thu của một đơn vị, doanh số của một nhóm nhân viên, hoặc doanh thu trong khoảng thời gian nhất định, tổng lương theo điều kiện nào đó...
Mục đích của hàm SUMIF là để tính tổng các số trong phạm vi đáp ứng được các tiêu chí đã cung cấp. Kết quả trả về là tổng các giá trị đã cung cấp.
Bạn có thể dùng hàm SUMIF để tính toán tài chính trong nhiều bối cảnh khác nhau. Dưới đây là một số cách hay để dùng hàm SUMIF trong Excel.
=SUMIF (range, criteria, [sum_range])
Tham số của hàm SUMIF:
Tiêu chí được áp dụng cho các ô trong phạm vi. Khi các ô trong phạm vi đáp ứng tiêu chí, các ô tương ứng trong sum_range sẽ được cộng lại.
*Lưu ý khi sử dụng hàm SUMIF
Hàm SUMIF trả về tổng các ô trong một phạm vi đáp ứng một điều kiện duy nhất. Đối số đầu tiên là phạm vi áp dụng tiêu chí, đối số thứ hai là tiêu chí và đối số cuối cùng là phạm vi chứa các giá trị để tính tổng. SUMIF hỗ trợ các toán tử logic (>, <, <>, =) và các ký tự đại diện (*,?) để đối sánh từng phần. Tiêu chí có thể sử dụng giá trị trong một ô khác, như được giải thích bên dưới.
SUMIF nằm trong nhóm 8 hàm Excel chia tiêu chí logic thành hai phần (phạm vi + tiêu chí). Do đó, cú pháp được sử dụng để xây dựng tiêu chí là khác nhau và SUMIF yêu cầu một phạm vi ô cho đối số phạm vi, bạn không thể sử dụng một mảng.
SUMIF chỉ hỗ trợ một điều kiện duy nhất. Nếu bạn cần áp dụng nhiều tiêu chí, hãy sử dụng hàm SUMIFS. Nếu bạn cần thao tác các giá trị xuất hiện trong đối số phạm vi (tức là trích xuất năm từ ngày tháng để sử dụng trong tiêu chí), hãy xem hàm SUMPRODUCT và/hoặc FILTER.
Hãy cùng Quantrimang.com học cách sử dụng hàm SUMIF nhé. Giả sử chúng ta có bảng tổng kết thu nhập và thuế thu nhập cá nhân như sau:
| Tổng thu nhập | Số người phụ thuộc | Thu nhập chịu thuế | Thuế TNCN | Thu nhập thực nhận | Phòng ban |
| 80,000,000 | 2 | 63,348,500 | 13,154,550 | 66,393,950 | Kế toán |
| 30,000,000 | 1 | 16,948,500 | 1,792,275 | 27,756,225 | Bán hàng |
| 20,000,000 | 1 | 6,875,000 | 437,500 | 19,037,500 | Bán hàng |
| 10,000,000 | 1 | -3,650,000 | 0 | 8,950,000 | Bán hàng |
| 100,000,000 | 2 | 82,750,000 | 19,112,500 | 79,837,500 | Kế toán |
| 1,000,000,000 | 1 | 976,900,000 | 332,065,000 | 657,435,000 | Giám đốc |
Trong Excel bảng được trình bày như sau:
Ví dụ 1: Sử dụng hàm SUMIF để tính tổng thuế thu nhập cá nhân của những người có thu nhập dưới 50 triệu đồng.
Để giải quyết ví dụ này, trước tiên ta cần xác định được 3 tham số của hàm SUMIF, cụ thể:
Khi đó, ta sẽ có công thức: =SUMIF(B4:B9,"<50.000.000",H4:H9), bạn nhập công thức này vào ô chứa kết quả, trong ví dụ của chúng tôi là ô H19.
Nhấn Enter và sẽ nhận được số tiền thuế thu nhập cá nhân cần đóng cho những người có thu nhập dưới 50.000.0000 là 2.229.775.
Ví dụ 2: Vẫn với bảng tính trên, hãy tính tổng thuế thu nhập cá nhân của người có 2 người phụ thuộc. Lúc này:
Như vậy, công thức sẽ là: =SUMIF(D4:D9,2,H4:H9), bạn nhập công thức vào ô chứa kết quả, trong ví dụ là H21:
Nhấn Enter để nhận được tổng thuế trả về là 32.267.050 đồng.
Ví dụ 3: Vẫn trong bảng trên, tính tổng thu nhập của những người có thu nhập trên 80 triệu đồng. Lúc này:
Như vậy công thức sẽ là: =SUMIF(B4:B9,">80000000"), bạn nhập vào ô chứa kết quả, ở đây là H22:
Nhấn Enter để nhận kết quả tổng thu nhập của những người có thu nhập trên 80 triệu là 1.100.000.000:
Ví dụ 4: Vẫn dùng bảng số liệu ban đầu, ta đi tính tổng thu nhập của phòng bán hàng nhé. Khi đó:
Như vậy công thức sẽ là: =SUMIF(J4:J9,"Bán hàng",B4:B9), bạn nhập công thức vào ô chứa kết quả, như hình dưới là ô H23:
Nhấn Enter và nhận về tổng thu nhập của phòng bán hàng là 60.000.000:
? và * đều có thể được sử dụng trong Criteria. Một dấu chấm hỏi khớp với bất kỳ ký tự đơn nào; một dấu sao phù hợ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ự.Bạn cũng có thể sử dụng một Named Range (vùng được đặt tên) trong hàm SUMIF. Named Range là tên mô tả cho một tập hợp các ô hoặc dải ô trong một trang tính. Nếu bạn không chắc chắn về cách thiết lập Named Range trong bảng tính của mình, hãy đọc hướng dẫn của Quantrimang.com về: Cách đặt tên cho ô hoặc vùng dữ liệu Excel.
Ví dụ, nếu bạn đã tạo một Named Range gọi là years trong bảng tính của mình tham chiếu đến các ô A2:A6 trong Sheet1 (Lưu ý rằng Named Range là tham chiếu tuyệt đối đến =Sheet1!$A$2:$A$6 trong hình ảnh bên dưới):
Bài viết có thể sử dụng Named Range này trong ví dụ hiện tại.
Điều này sẽ cho phép thay thế A2:A6 làm tham số đầu tiên với Named Range years, như sau:
=SUMIF(A2:A6, D2, C2:C6) 'First parameter uses a standard range
Result: 218.6
=SUMIF(years, D2, C2:C6) 'First parameter uses a named range called years
Result: 218.6
Câu hỏi: Tôi có một vài ô trong trang tính, nhưng tôi chỉ cần tổng của tất cả các ô âm. Vì vậy, nếu có 8 giá trị, A1 đến A8 và chỉ A1, A4 và A6 là â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 để chỉ tính tổng các giá trị âm như bạn đã mô tả ở trên.
Ví dụ:
=SUMIF(A1:A8,"<0")
Công thức này sẽ chỉ tính tổng các giá trị trong các ô A1:A8 - những ô có gia trị âm (tức là: <0).
Câu hỏi: Trong Microsoft Excel, tôi đang cố gắng đạt được những điều sau với hàm IF:
Nếu một giá trị trong bất kỳ ô nào trong cột F là "food" thì hãy thêm giá trị của ô tương ứng của nó trong cột G (ví dụ: ô tương ứng cho F3 là G3). Hàm IF được thực hiện hoàn toàn trong một ô khác. Tôi có thể làm điều đó cho một cặp ô nhưng không biết làm thế nào để thực hiện điều đó cho toàn bộ cột.
Hiện tại, tôi có như sau:
=IF(F3="food"; G3; 0)
Trả lời: Công thức này có thể được tạo bằng hàm SUMIF thay vì sử dụng hàm IF:
=SUMIF(F1:F10,"=food",G1:G10)
Công thức này sẽ đánh giá 10 hàng dữ liệu đầu tiên trong bảng tính. Bạn có thể cần phải điều chỉnh các phạm vi cho phù hợp.
Nếu bạn phân tách các tham số của mình bằng dấu chấm phẩy, thì bạn có thể cần thay thế dấu phẩy trong công thức ở trên bằng dấu chấm phẩy.
Có một số nguyên nhân khiến hàm SUMIF trong Excel không hoạt động. Đôi khi, công thức của bạn không trả về kết quả như mong đợi chỉ bởi vì kiểu dữ liệu trong ô hoặc ở một vài đối số không phù hợp với hàm SUMIF. Bên dưới là những điều quan trọng mà bạn cần phải ghi nhớ.
SUMIF chỉ hỗ trợ một điều kiện
Công thức của hàm SUMIF chỉ có chỗ trống cho một điều kiện. Để tính tổng với nhiều tiêu chí, hãy dùng hàm SUMIF (thêm ô đáp ứng tất cả điều kiện) hoặc xây dựng một công thức SUMIF với nhiều tiêu chí OR (tính tổng ô đáp ứng điều kiện bất kỳ).
Range và sum_range nên cùng kích thước
Để công thức hàm SUMIF hoạt động chính xác, đối số range và sum_range nên có cùng kích thước, nếu không, bạn có thể nhận kết quả sai lệch. Vấn đề là Microsoft Excel không dựa vào khả năng cung cấp phạm vi phù hợp của người dùng và để tránh các sự cố không nhất quán có thể xảy ra, nó sẽ tự động xác định phạm vi tổng theo cách này:
Sum_range chỉ xác định ô bên trái ở trên của phạm vi được tính tổng. Phần còn lại được quyết định bởi kích thước và hình dạng của đối số phạm vi.
Trên đây là cách sử dụng hàm SUMIF cơ bản trong Excel, thực ra thì nó rất dễ phải không nào? Bạn chỉ cần xác định đúng phạm vi cột cần thỏa mãn tiêu chí, tiêu chí và phạm vi cột cần tính tổng là xong. Nếu cần tính tổng theo nhiều điều kiện, khi đó bạn nhớ sử dụng hàm SUMIFS nhé, dễ và nhanh hơn nhiều lắm.
Bạn có thắc mắc gì trong quá trình thao tác với hàm SUMIF hãy bình luận ở phía dưới để được trợ giúp nhé.
Ý kiến bạn đọc
Những tin mới hơn
Những tin cũ hơn
