Phần mềm

Hướng dẫn về cách sử dụng SUMIFS (Hàm SUMIFS) trong Excel

Bạn đang muốn tính tổng trong Excel với SUMIFS (Hàm SUMIFS) là dạng tính tổng cho nhiều điều kiện. Nhưng bạn chưa biết cú pháp và cách sử dụng của chúng như thế nào? Bạn có thể tìm hiểu chi tiết thông tin được chia sẻ về hàm SUMIFS ở dưới đây nhé!

SUMIFS (Hàm SUMIFS) là gì? – Cú pháp và cách sử dụng

Để giúp bạn dễ dàng sử dụng hàm SUMIFS để tính toán trong Excel chuẩn chỉnh nhất. Bạn nên đọc kỹ về hàm SUMIFS với cú pháp và cách sử dụng của chúng như sau:

Định nghĩa về hàm SUMIFS:

SUMIFS (Hàm SUMIFS) là một trong những hàm toám học và lượng giác, cộng được tất cả các đối số của chúng, đáp ứng được nhiều tiêu chí. Cụ thể: Bạn sử dụng hàm SUMIFS để tính tổng về số lượng của nhà bán lẻ trong nước đang cư trú tại một mã zip và mang lại lợi nhuận vượt quá một giá trị cụ thể được tính bằng tiền đô la.

Bang-tinh-tong-trong-Excel-SUMIFS
Bảng tính tổng trong Excel sử dụng hàm SUMIFS

Vậy là bạn đã đa hiểu được SUMIFS (Hàm SUMIFS) là như thế nào rồi dúng không nào? Giờ đây bạn cần biết về cấu trúc và cách sử dụng của chúng trong Excel.

Cú pháp và cách sử dụng SUMIFS (Hàm SUMIFS) trong Excel:

Bạn đã biết được SUMIFS (Hàm SUMIFS) trong Excel được sử dụng để tính tổng có giá trị. Các giá trị đó đều được dựa trên nhiều điều kiện. Đối với Excel 2007 đã sử dụng được hàm SUMIFS, vì thế bạn có thể sử dụng chúng trong các bản: Excel 2010, Excel 2013, Excel 2016

Đối với hàm SUMIFS có cấu trúc phức tạp hơn chút so với hàm SUMIF như sau:

=SUMIFS (sum_range, criteria_range1, criteria1, [criter_range2, criteria2],…)

Trong đó:

Với 3 đối số đầu tiên buộc phải có, những thành phần range được bổ sung và các điều kiện có liên quan tới chúng thì sẽ được tùy chọn.

– sum_range: Đây là một hoặc nhiều ô tính tổng, mang tính chất bắt buộc. Chúng là một ô duy nhất, một dải ô hay dải có tên. Chỉ những ô có chứa số thì mới được tính tổng, còn với giá trị ô trống và giá trị văn bản sẽ được bỏ qua.

– criteria_range1: Phần ranger đầu tiên được đánh giá với điều kiện có liên quan, mang tính chất là bắt buộc.

– criteria1: Đây là điều kiện đầu tiên cần phải đáp ứng được, chính là đối số bắt buộc phải có. Bạn cũng có thể cung cấp tất cả các điều kiện dưới dạng một số, biểu thức logic, văn bản, tham chiếu ô, hoặc một hàm Excel nào khác. Cụ thể: bạn cũng có thể sử dụng tất cả những điều kiện như: 10, “> = 10”, A1, “cherries” hoặc TODAY().

– criteria_range2, criteria2,…: Đây chính là những dải được thêm vào và điều kiện có liên quan tới dải này, do chính bạn tùy chọn. Bạn cũng có thể sử dụng tối đa 127 dải / điều kiện có trong công thức SUMIFS.

Lưu ý: Đối với hàm SUMIFS hoạt động trong biểu thức logic với AND. Tức là với mỗi ô trong đối số sum_range chỉ được tính tổng nếu tất cả những điều kiện được chỉ định đúng cho ô ở trong đó.

Ví dụ cụ thể: Hàm SUMIFS hoạt động với cả 2 điều kiện. Bạn có một bảng liên kết với tất cả các lô hàng hóa trái cây từ nhà cung cấp khác nhau. Trong đó, bạn có tên của tất cả các loại quả ở cột A, tên của nhà cung cấp ở cột B và số lượng nằm trong cột C. Bạn đang muốn tìm ra số tiền có liên quan tới quả và nhà cung cấp. Chẳng hạn như: tất cả các quả Táo (Apples) được cung cấp bởi Pete.

Khi bạn bắt đầu với một cái gì đó hoàn toàn mới lạ, bạn nên bắt đầu từ những thứ đơn giản trước. Bởi vậy, để bắt đầu thì bạn cần xác định tất cả những đối số cho công thức SUMIFS như sau:

– sum_range dữ liệu được lấy từ ô: C2:C9

– criteria_range1 dữ liệu được lấy từ: A2:A9

– criteria1 dữ liệu điền thông tin: “apples”

– criteria_range2 dữ liệu được lấy từ: B2:B9

– criteria2 dữ liệu điền thông tin: Pete.

Giờ đây, bạn tập hợp tất cả các thông số trên, và bạn sẽ cho ra được công thức SUMIFS như dưới đây:

= SUMIFS (C2: C9, A2:A9, “táo”, B2:B9, “Pete”)

Để việc chỉnh sửa công thức đơn giản, bạn có thể thay các tiêu chuẩn trong văn bản “apples” và “Pete” bằng các ô tham chiếu. Trong những trường hợp này, bạn sẽ không phải thay đổi công thức để tính toán về lượng trái cây khác từ các nhà cung cấp khác nhau như sau:

= SUMIFS (C2: C9, A2:A9, F1, B2:B9, F2)

Bang-tinh-tong-trong-Excel-SUMIFS-01
Bảng tính tổng dùng hàm SUMIFS cho hàng quả Appler của hãng Pete

Qua phần này, bạn lại hiểu thêm về cách dùng hàm SUMIFS trong Excel với cú pháp sử dụng như nào cho chuẩn rồi nhé.

Ví dụ cụ thể về công thức tính hàm SUMIFS trong Excel

Ở trên bạn đã hiểu rõ về hàm SUMIFS sử dụng trong Excel như thế nào và có cấu trúc ra làm sao? Giờ đây bạn áp dụng chi tiết vào bài tính của mình, thông qua những ví dụ cụ thể ở dưới đây:

Ví dụ 1: Cách dùng công thức SUMIFS đối với toán tử so sánh.

Với bảng tính cung cấp trái cây ở dưới đây, ví dụ, bạn đang muốn tính tổng của tất cả các lượng hàng đã được Mike cung cấp với số lượng 200 trở lên. Với điều này, bạn sử dụng phương pháp toán tử so sánh “lớn hơn hoặc bằng” (> =). Trong tất cả các điều kiện 2 và được nhận với công thức SUMIFS sau:

= SUMIFS (C2:C9, B2:B9, “Mike”, C2:C9, “> = 200”)

Chú ý: Với tất cả các công thức hàm SUMIFS, thì các biểu thức logic với tất cả các toán tử so sánh thì luôn luôn được đặt ở trong dấu nháy kép (“”).

Bang-tinh-tong-trong-Excel-SUMIFS-01
Bảng tính tổng dùng hàm SUMIFS cho hàng quả Appler của hãng Pete
Bang-tinh-tong-trong-Excel-SUMIFS-02
Bảng tính hàm SUMIFS với toán tử so sánh

Chúng tôi đã đề cập chi tiết cụ thể về tất cả các toán từ so sánh để có thể thảo luận về hàm SUMIFS. Tất cả các chúng cũng có thể sử dụng ở trong điều kiện SUMIFS. Ví dụ cụ thể: Trả về với giá trị tổng của tất cả các giá trị ở tổng các ô C2:C9 mà lớn hơn hoặc có thể bằng 200 và nhỏ hơn hoặc bằng 300.

= SUMIFS (C2:C9, C2:C9, “> = 200”, C2:C9, “<= 300”)

Ví dụ 2: Cách dùng công thức SUMIFS cho ngày

Trong những trường hợp bạn muốn tính tổng của các giá trị với điều kiện dựa vào ngày hiện tại. Bạn sử dụng hàm TODAY () với điều kiện của hàm SUMIFS của bạn, như đã được trình bày ở dưới đây. Công thức sau đây sẽ tính tổng về 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, và bao gồm cả hôm nay:

= SUMIFS (D2:D10, C2:C10, “> =” & TODAY () – 7, C2:C10, “<=” & TODAY ())

Lưu ý: Khi bạn đang sử dụng với một hàm Excel khác cùng với toán tử logo thuộc các điều kiện, bạn cần sử dụng ký hiệu dấu và (&) để nối với 1 chuỗi, ví dụ “<=’ & TODAY ().

Cúng tương tự như thế, bạn có thể dùng hàm SUMIFS để tính tổng của tất cả các giá trị ở trong dải có dữ liệu là ngày xác định. Ví dụ cụ thể: Công thức SUMIFS sau sẽ được thêm với tất cả các giá trị ở trong ô C2:C9 nếu ngày nằm trong cột B được rơi vào khoảng giữa ngày 1 của tháng 10 năm 2014 và vào ngày 31 của tháng 10 năm 2014, thì có công thức như sau:

= SUMIFS (C2:C9, B2:B9, “>= 10/1/2014”, B2:B9, “<= 10/31/2014”)

Bang-tinh-tong-trong-Excel-SUMIFS-03
Bảng tính sử dụng SUMIFS với ngày

Trả về kết quả tương tự có thể đạt được bằng việc tính toán sự khác biệt với hàm SUMIF, như trong phần ví dụ. Với cách sử dụng hàm SUMIFS để tính tổng về các giá trị có trong một dải xác định. Nhưng bạn cũng thấy được công thức hàm SUMIFS là khá dễ dàng và dễ hiểu hơn rất nhiều đúng không?

Ví dụ 3: Cách dùng công thức SUMIFS với ô trống và không trống:

Theo sự phân tích báo cáo và dữ liệu khác biệt nhau, bạn cần phải tính tổng của các giá trị tương ứng với ô trống hoặc ô không trống.

+ Đối với những ô trống, cho ra điều kiện bằng “=”: Sử dụng để tính tổng các giá trị với những ô trống tương ứng (chúng hoàn toàn không chứa dữ liệu – không có công thức, và chuỗi có 0 ký tự). Cho ra công thức tính toán như sau:

=SUMIFS(C2:C10, A2:A10, “=”, B2:B10)

Tính tổng giá trị ở trong các ô C2:C10, nếu tất cả các ô tương ứng với chúng có trong cột A và D là những ô hoàn toàn trống.

+ Đối với những ô trống, có điều kiện trắng “”: Được dùng để tính các giá trị tương ứng với những ô trắng “nhận định về trực quan”. Trong đó gồm có các giá trị chứa chuỗi rỗng được trả về bởi một số hàm Excel khác. Cho ra công thức như sau:

=SUMIFS(C2:C10, A2:A10, “”, B2:B10)

Tính tổng về các giá trị có trong tất cả các ô C2:C10 (đều có cùng các điều kiện) như công thức ở trên, nhưng chúng gồm có các chuỗi trống.

+ Đối với những ô không trống có điều kiện “<>”: Được dùng tính tổng với tất cả các giá trị mà có các giá trị tương ứng là những ô không trống, và bao gồm có chiều dài bằng 0. Cho ra công thức tính như sau:

=SUMIFS(C2:C10, A2:A10, “<>”, B2:B10, ‘<>’)

Tính tổng giá trị của các ô trong C2:C10, nếu tất cả các ô tương ứng với chúng ở cột A và B không phải là ô trống, gồm có các ô với chuỗi trống.

+ Đối với những ô không trống, có điều kiện SUM-SUMIF hoặc SUM/LEN: được sử dụng để tính tổng của tất cả các giá trị mà có tất cả các giá trị tương ứng chính là những ô không trống, và không gồm chuỗi có chiều dài bằng 0. Cho ra công thức tính như sau:

= SUMIFS(c2:C10) – SUMIFS(C2:C10, A2:A10, “’, B2:B10, “”)

= SUM((C2:C10)*(LEN(A2:A10)>0)*(LEN(BE2:B10)>)

Tính tổng về giá trị đó trong các ô C2:C10 nếu tất cả các ô đó tương ứng với giá trị trong cột A, B là ô trống, và không bao gồm các ô với chuỗi trống.

Bạn xem cách sử dụng với công thức SUMIFS có giá trị “trống” và “không trống” nằm trên dữ liệu thực như thế nào nhé:

Ví dụ: Bạn có ngày đặt hàng ở cột B, ngày giao hàng cột C, và số lượng ở cột D. Vậy làm thế nào để tính được tổng về số sản phẩm đã chứa được giao? Tức là bạn đang muốn biết tổng giá trị tương ứng với ô không rỗng ở trong cột B và những ô rỗng ở trong cột C.

Cách giải quyết chính là sử dụng công thức SUMIFS với cả 2 điều kiện:

=SUMIFS (D2:D10, B2:B10, “<>”, C2:C10, “=”)

Bang-tinh-tong-trong-Excel-SUMIFS-04
Bảng tính giá trị cụ thể của hàng SUMIFS lấy tổng số hàng chưa được giao

Qua ví dụ này, bạn lại có thêm kiến thức về cách tính tổng số lượng hàng hóa dựa vào hàm SUMIFS trong excel rồi nhé.

Những vấn đề chung khi sử dụng hàm SUMIFS tRong Excel

Mục đích chính của hướng dẫn này sẽ bao gồm tất cả các cách có thể có, để tính tổng giá trị thỏa mãn điều kiện, với các vấn đề chung như sau:

– 0 (Không) được hiển thị thay bởi kết quả mong đợi: Nhằm đảm bảo Criteria1,2 nằm ở trong dấu ngoặc kép “”. Nếu bạn đang kiểm tra về giá trị văn bản, ví dụ như tên người.

– Trả về kết quả sai khi Sum_range với giá trị TRUE hoặc FALSE: Trong đó với giá trị TRUE và FALSE cho Sum_range đã được định trị theo các cách khác nhau. Điều này có thể dẫn tới kết quả không như mong muốn khi cộng chúng lại. Tất cả những ô trong Sum_range có chứa TRUE được mặc định là 1. Những ô có chứa FALSE được mặc định là 0 (Không).

Những cách thực hành hàm SUMIFS tốt nhất

Để có phương án thực hành hàm SUMIFS mang lại hiệu quả nhất cho người dùng. Bạn cần nắm chắc một số cách ở dưới đây:

+ Sử dụng ký tự đại diện:

Dùng những ký tự đại diện như dấu chấm hỏi (?), dấu sao (*) có trong criteria1,2. Với cách này có thể giúp bạn tìm được kết quả khớp tương tự nhưng không chính xác.

Với một dấu chấm hỏi khớp với một ký tự đơn lử bất kỳ. Một dấu sao khớp với chuỗi ký tự bất kỳ. Nếu bạn đang muốn tìm kiếm dấu chấm hỏi hoặc dấu sao thực sự, hãy nhập một đấu ngã (~) trước dấu chấm hỏi.

+ Hiểu rõ về sự khác biệt giữa SUMIF VÀ SUMIFS: 

Trước tiên với tất cả các đối số của hàm SUMIF và hàm SUMIFS khác nhau. Cụ thể, với đối số sum_range là đối số thứ nhất ở trong hàm SUMIFS, nhưng chúng là đối số thứ ba ở trong hàm SUMIF. Đây chính là nguyên nhân phổ biến nhất gây ra vấn đề khi sử dụng của tất cả hàm này.

Nếu bạn vẫn đang sao chép và sửa chữa những hàm tương tự như nhau này, bạn hãy bảo bạn đặt tất cả đối số theo đúng thứ tự.

+ Sử dụng về một số hàng và cột cho ra đối số phạm vi: 

Đối số Criteria Range cần phải chứa về số hàng và số cột bằng vối đối số Sum_range.

Qua những thông tin chia sẻ trên đây, chắc chắn bạn đã bỏ túi cho mình về kinh nghiệm sử dụng SUMIFS (hàm SUMIFS) trong Excel rồi nhé. Mong rằng với những chia sẻ này sẽ giúp ích cho bạn phần nào trong suốt quá trình học và làm việc trên bảng tính Excel.

>>> Xem thêm: Hàm match trong excel

>>> Xem thêm: Hàm countif trong excel

Related Articles

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

Back to top button