Quay trở lại danh sách công thức Excel Tải xuống ví dụ sổ làm việc Tải xuống sổ làm việc ví dụ Hướng dẫn này sẽ trình bày cách sử dụng chức năng SUMIFS để tổng hợp dữ liệu với các ngày cụ thể trong Excel và Google Sheets.
Giá trị tổng nếu ngày bằng nhau
Hàm SUMIFS tổng dữ liệu đáp ứng các tiêu chí nhất định. Cú pháp của nó là:
Ví dụ này sẽ tổng hợp số lượng giao hàng theo kế hoạch vào một ngày cụ thể bằng cách sử dụng các chức năng SUMIFS và ngày.Planned Deliveries on a specific Date by using the SUMIFS and DATE Functions.
=SUMIFS[D3:D7,B3:B7,DATE[2021,1,2]]Trong ví dụ này, chúng tôi muốn tổng hợp các giao hàng được lên kế hoạch cho 1/2/2021. Để nhập ngày này làm tiêu chí trong công thức của chúng tôi, chúng tôi sử dụng hàm ngày:Deliveries planned for 1/2/2021. To enter this Date as a criteria in our formula, we use the DATE Function:
DATE[2021,1,2]Trong trường hợp này, biểu thức hàm ngày ở trên là tất cả những gì chúng ta cần kiểm tra xem ngày có bằng 1/2/2021 hay không. Tuy nhiên, một thử nghiệm hợp lý trong hàm SUMIFS thường yêu cầu một toán tử trong các trích dẫn kép [Hồi], do đó, các tiêu chí tổng cũng có thể được viết là:
"="&DATE[2021,1,2]Giá trị tổng nếu ngày bằng nhau - tham chiếu ô
Thông thường, đó là thực tế xấu đối với các giá trị ngày cứng thành các công thức. Thay vào đó, việc sử dụng một ô riêng biệt sẽ linh hoạt hơn để xác định ngày được sử dụng làm tiêu chí tổng.
=SUMIFS[D3:D7,B3:B7,F3]Điều này cho phép người dùng thay đổi ngày được sử dụng trong công thức theo cách nhanh hơn và trực quan hơn bằng cách chỉnh sửa ô F3.Date used in the formula in a quicker and more visual way by editing the cell F3.
Lưu ý rằng các toán tử logic khác cũng có thể được sử dụng trong SUMIFS, chẳng hạn như:
- Lớn hơn [Hồi> và & F3]
- Lớn hơn hoặc bằng [“> = '& f3]
- Ít hơn ["
- Ít hơn hoặc bằng ["
- Không bằng [[“& f3]
Khóa tài liệu tham khảo tế bào
Để làm cho các công thức của chúng tôi dễ đọc hơn, chúng tôi đã hiển thị các công thức mà không bị khóa tham chiếu ô:
=SUMIFS[D3:D7,B3:B7,F3]Nhưng các công thức này sẽ không hoạt động đúng khi sao chép và dán ở nơi khác trong tệp của bạn. Thay vào đó, bạn nên sử dụng các tài liệu tham khảo ô bị khóa như thế này:
=SUMIFS[$D$3:$D$7,$B$3:$B$7,F3]Đọc bài viết của chúng tôi về khóa tham chiếu tế bào để tìm hiểu thêm.
Giá trị tổng nếu ngày bằng nhau trong Google Sheets
Các công thức này hoạt động chính xác giống nhau trong Google Sheets như trong Excel.
Hướng dẫn cho thấy cách sử dụng chức năng SUMIF trong bảng tính Google để tổng hợp các ô. Bạn sẽ tìm thấy các ví dụ công thức cho văn bản, số và ngày và tìm hiểu cách tổng hợp với nhiều tiêu chí.
Một số chức năng tốt nhất trong Google Sheets là các chức năng giúp bạn tóm tắt và phân loại dữ liệu. Ngày nay, chúng ta sẽ xem xét kỹ hơn một trong những chức năng như vậy - SUMIF - một công cụ mạnh mẽ để tổng hợp các ô. Trước khi nghiên cứu các ví dụ về cú pháp và công thức, hãy để tôi bắt đầu với một vài nhận xét quan trọng.
Google Sheets có hai chức năng để thêm các số dựa trên các điều kiện: SUMIF và SUMIF. Cái trước đánh giá chỉ một điều kiện trong khi trường hợp sau có thể kiểm tra nhiều điều kiện tại một thời điểm. Trong hướng dẫn này, chúng tôi sẽ chỉ tập trung vào hàm SUMIF, việc sử dụng SUMIF sẽ được đề cập trong bài viết tiếp theo.SUMIF and SUMIFS. The former evaluates just one condition while the latter can test multiple conditions at a time. In this tutorial, we will focus solely on the SUMIF function, the use of SUMIFS will be covered in the next article.
Nếu bạn biết cách sử dụng SUMIF trong máy tính để bàn Excel hoặc Excel trực tuyến, SUMIF trong Google Sheets sẽ là một miếng bánh cho bạn vì cả hai về cơ bản đều giống nhau. Nhưng đừng vội vã đóng trang này - bạn có thể tìm thấy một vài công thức tổng hợp không đáng tin cậy nhưng rất hữu ích mà bạn không biết!
SUMIF trong Google Sheets - Cú pháp và mục đích sử dụng cơ bản
Hàm SUMIF là Google Sheets được thiết kế để tổng hợp dữ liệu số dựa trên một điều kiện. Cú pháp của nó như sau:
SUMIF [phạm vi, tiêu chí, [sum_range]]
Where:
- Phạm vi [bắt buộc] - Phạm vi của các ô nên được đánh giá theo tiêu chí. [required] - the range of cells that should be evaluated by criterion.
- Tiêu chí [bắt buộc] - Điều kiện cần đáp ứng. [required] - the condition to be met.
- Sum_range [tùy chọn] - Phạm vi để tổng số. Nếu bị bỏ qua, thì phạm vi được tổng hợp. [optional] - the range in which to sum numbers. If omitted, then range is summed.
Ví dụ, hãy tạo một công thức đơn giản sẽ tổng số trong cột B nếu cột A chứa một mục bằng "mục mẫu".
Đối với điều này, chúng tôi xác định các đối số sau:
- Phạm vi - Danh sách các mục - A5: A13.
- Tiêu chí - Một ô chứa mục quan tâm - B1.
- SUM_RANGE - Số tiền sẽ được tổng hợp - B5: B13.
Đặt tất cả các đối số lại với nhau, chúng tôi có được công thức sau:
=SUMIF[A5:A13,B1,B5:B13]
Và nó hoạt động chính xác như nó nên:
Google Sheets Sumif ví dụ
Từ ví dụ trên, bạn có thể có ấn tượng rằng việc sử dụng các công thức SUMIF trong bảng tính của Google dễ dàng đến mức bạn có thể làm điều đó với đôi mắt nhắm. Trong hầu hết các trường hợp, nó thực sự là như vậy :] nhưng vẫn có một số thủ thuật và cách sử dụng không tầm thường có thể làm cho công thức của bạn hiệu quả hơn. Các ví dụ dưới đây cho thấy một vài trường hợp sử dụng điển hình. Để làm cho các ví dụ dễ theo dõi hơn, tôi mời bạn mở Sumif Sumif của chúng tôi.
Công thức SUMIF với tiêu chí văn bản [khớp chính xác]
Để thêm các số có một văn bản cụ thể trong một cột khác trong cùng một hàng, bạn chỉ cần cung cấp văn bản quan tâm trong đối số tiêu chí của công thức SUMIF của bạn. Như thường lệ, bất kỳ văn bản nào trong bất kỳ đối số nào của bất kỳ công thức nào cũng nên được đặt trong "trích dẫn kép".
Ví dụ: để có được tổng số chuối, bạn sử dụng công thức này:
=SUMIF[A5:A13,"bananas",B5:B13]
Hoặc, bạn có thể đặt tiêu chí vào một số ô và tham khảo ô đó:
=SUMIF[A5:A13,B1,B5:B13]
Công thức này rõ ràng, phải không? Bây giờ, làm thế nào để bạn có được tổng cộng tất cả các mặt hàng ngoại trừ chuối? Đối với điều này, hãy sử dụng không bằng người vận hành:except bananas? For this, use the not equal to operator:
=SUMIF[A5:A13,"bananas",B5:B13]
Nếu một "mục loại trừ" là đầu vào trong một ô, thì bạn sẽ gửi lại không bằng toán tử trong trích dẫn đôi [""] và kết hợp toán tử và tham chiếu ô bằng cách sử dụng ampersand [&]. Ví dụ:
DATE[2021,1,2]Ảnh chụp màn hình sau đây thể hiện cả "tổng nếu bằng" và "tổng nếu không bằng" các công thức trong hành động:
Xin lưu ý rằng tổng số trong Google Sheets tìm kiếm chính xác văn bản được chỉ định. Trong ví dụ này, chỉ có số lượng chuối được tổng hợp, chuối xanh và chuối Goldfinger không được bao gồm. Để tổng hợp với khớp một phần, hãy sử dụng các ký tự ký tự đại diện như trong ví dụ tiếp theo.exactly. In this example, only Bananas amounts are summed, Green bananas and Goldfinger bananas are not included. To sum with partial match, use wildcard characters as shown in the next example.
Công thức SUMIF với ký tự đại diện [kết hợp một phần]
Trong các tình huống khi bạn muốn tổng hợp các ô trong một cột nếu một ô trong một cột khác chứa một văn bản hoặc ký tự cụ thể là một phần của nội dung ô, bao gồm một trong các ký tự đại diện sau trong tiêu chí của bạn:part of the cell contents, include one of the following wildcards in your criteria:
- Dấu câu hỏi [?] Để phù hợp với bất kỳ ký tự đơn.
- Asterisk [*] để phù hợp với bất kỳ chuỗi ký tự nào.
Ví dụ, để tổng hợp số lượng của tất cả các loại chuối, hãy sử dụng công thức này:
DATE[2021,1,2]Bạn cũng có thể sử dụng ký tự đại diện cùng với các tài liệu tham khảo di động. Đối với điều này, kèm theo ký tự đại diện trong các dấu ngoặc kép và nối nó với một tham chiếu ô:
DATE[2021,1,2]Dù bằng cách nào, công thức SUMIF của chúng tôi cộng lại số lượng của tất cả các chuối:
Để phù hợp với một dấu hỏi thực tế hoặc dấu hoa thị, tiền tố nó với nhân vật Tilde [~] như "~?" hoặc "~*".
Ví dụ: để tổng số trong cột B có dấu hoa thị trong cột A trong cùng một hàng, hãy sử dụng công thức này:
DATE[2021,1,2]Bạn thậm chí có thể nhập dấu hoa thị trong một số tế bào, nói B1 và nối tế bào đó với Tilde Char:
DATE[2021,1,2]Tổng hợp nhạy cảm trường hợp trong google sheets
Theo mặc định, SUMIF trong Google Sheets không thấy sự khác biệt giữa các chữ cái nhỏ và viết hoa. Để buộc nó phải teat chữ hoa và các ký tự thường khác nhau, hãy sử dụng SUMIF kết hợp với các hàm tìm và ArrayFormula:
SUMIF [ArrayFormula [tìm ["văn bản", phạm vi]], 1, sum_range]
Giả sử bạn có một danh sách các số đơn hàng trong A5: A13 và số lượng tương ứng trong C5: C13, trong đó cùng một số đơn hàng xuất hiện trong một số hàng. Bạn nhập ID thứ tự đích trong một số ô, giả sử B1 và sử dụng công thức sau để trả về tổng số đơn hàng:
DATE[2021,1,2]Cách thức hoạt động của công thức này
Để hiểu rõ hơn về logic của công thức, hãy chia nó thành các phần có ý nghĩa:
Phần khó nhất là đối số phạm vi: ArrayFormula [Tìm [B1, A5: A13]]]
Bạn sử dụng chức năng tìm thấy trường hợp nhạy cảm với trường hợp để tìm ID thứ tự chính xác. Vấn đề là một công thức tìm thấy thông thường chỉ có thể tìm kiếm trong một ô. Để tìm kiếm trong một phạm vi, cần có một công thức mảng, vì vậy bạn sẽ tìm thấy bên trong ArrayFormula.
Khi kết hợp ở trên tìm thấy một kết hợp chính xác, nó sẽ trả về 1 [vị trí của ký tự được tìm thấy đầu tiên], nếu không thì lỗi #Value. Vì vậy, điều duy nhất còn lại để bạn làm là tổng hợp các số tiền tương ứng với 1. Đối với điều này, bạn đặt 1 trong đối số tiêu chí và C5: C13 trong đối số SUM_RANGE. Xong!
Công thức Sumif cho các số
Để tổng số đáp ứng một điều kiện nhất định, hãy sử dụng một trong các toán tử so sánh trong công thức SUMIF của bạn. Trong hầu hết các trường hợp, việc chọn một toán tử thích hợp không phải là vấn đề. Nhúng nó vào tiêu chí đúng cách có thể là một thách thức.
Tổng nếu lớn hơn hoặc ít hơn
Để so sánh các số nguồn với một số cụ thể, hãy sử dụng một trong các toán tử logic sau:
- lớn hơn [>]
- ít hơn [
- lớn hơn hoặc bằng [> =]
- ít hơn hoặc bằng [
Ví dụ: để thêm các số trong B5: B13 lớn hơn 200, hãy sử dụng công thức này:
DATE[2021,1,2]Xin lưu ý cú pháp chính xác của tiêu chí: Một số tiền tố với toán tử so sánh và toàn bộ cấu trúc được đặt trong các dấu ngoặc kép.
Hoặc, bạn có thể nhập số vào một số ô và nối toán tử so sánh với tham chiếu ô:
DATE[2021,1,2]Bạn thậm chí có thể nhập cả toán tử so sánh và số vào các ô riêng biệt và nối các tế bào đó:
Theo cách tương tự, bạn có thể sử dụng các toán tử logic khác như:
Tổng nếu lớn hơn hoặc bằng 200:
DATE[2021,1,2]Tổng nếu nhỏ hơn 200:
DATE[2021,1,2]Tổng nếu nhỏ hơn hoặc bằng 200:
"="&DATE[2021,1,2]Tổng nếu bằng
Để tổng các số bằng một số cụ thể, bạn có thể sử dụng dấu bình đẳng [=] cùng với số hoặc bỏ qua dấu bình đẳng và chỉ bao gồm số trong đối số tiêu chí.
Ví dụ: để thêm số lượng trong cột B có số lượng trong cột C bằng 10, sử dụng bất kỳ công thức nào dưới đây:
"="&DATE[2021,1,2]hoặc
"="&DATE[2021,1,2]hoặc
"="&DATE[2021,1,2]Trong đó B1 là tế bào với số lượng cần thiết.
Tổng nếu không bằng
Để tổng số khác với số được chỉ định, sử dụng không bằng toán tử [].not equal to operator [].
Trong ví dụ của chúng tôi, để thêm số lượng trong cột B có bất kỳ số lượng nào ngoại trừ 10 trong cột C, đi với một trong những công thức này:
"="&DATE[2021,1,2]"="&DATE[2021,1,2]Ảnh chụp màn hình dưới đây cho thấy kết quả:
Google Sheets Sumif Công thức cho ngày
Để tổng hợp các giá trị dựa trên các tiêu chí ngày, bạn cũng sử dụng các toán tử so sánh như được hiển thị trong các ví dụ trên. Điểm mấu chốt là một ngày nên được cung cấp theo định dạng mà Google Sheets có thể hiểu.
Chẳng hạn, để tổng số tiền trong B5: B13 cho ngày giao hàng trước 11 tháng 9 năm 2018, hãy xây dựng tiêu chí theo một trong những cách này:
"="&DATE[2021,1,2]"="&DATE[2021,1,2]"="&DATE[2021,1,2]Trong đó B1 là ngày đích:
Trong trường hợp bạn muốn tổng hợp các ô dựa trên có điều kiện dựa trên ngày hôm nay, hãy bao gồm hàm TODAY [] trong đối số tiêu chí.today's date, include the TODAY[] function in the criterion argument.
Ví dụ, chúng ta hãy tạo một công thức bổ sung số tiền cho việc giao hàng ngày nay:
"="&DATE[2021,1,2]Lấy ví dụ xa hơn, chúng ta có thể tìm thấy tổng số giao hàng trong quá khứ và tương lai:
Trước hôm nay:
=SUMIFS[D3:D7,B3:B7,F3]Sau ngày hôm nay:
=SUMIFS[D3:D7,B3:B7,F3]Tổng dựa trên các ô trống hoặc không trống
Trong nhiều tình huống, bạn có thể cần phải tổng hợp các giá trị trong một cột nhất định nếu một ô tương ứng trong một cột khác là hoặc không trống.
Đối với điều này, hãy sử dụng một trong các tiêu chí sau trong các công thức tổng hợp Google Sheets của bạn:
Tổng nếu trống:
- "=" Để tổng các ô hoàn toàn trống. to sum cells that are completely blank.
- "" Để tổng hợp các ô trống bao gồm các tế bào có chứa chuỗi độ dài bằng không. to sum blank cells including those that contain zero length strings.
Tổng nếu không trống:
- "" Để thêm các ô có chứa bất kỳ giá trị nào, bao gồm cả chuỗi độ dài bằng không.
Ví dụ: để tổng hợp các số tiền mà ngày giao hàng được đặt [một ô trong cột C không trống], hãy sử dụng công thức này:not empty], use this formula:
=SUMIFS[D3:D7,B3:B7,F3]Để có được tổng số lượng không có ngày giao hàng [một ô trong cột C trống], hãy sử dụng số tiền này:empty], use this one:
=SUMIFS[D3:D7,B3:B7,F3]Google Sheets Sumif với nhiều tiêu chí [hoặc logic]
Hàm SUMIF trong Google Sheets được thiết kế để thêm các giá trị chỉ dựa trên một tiêu chí. Để tổng hợp với nhiều tiêu chí, bạn có thể thêm hai hoặc nhiều hàm tổng hợp với nhau.
Ví dụ: tổng hợp táo và cam, sử dụng công thức này:
=SUMIFS[D3:D7,B3:B7,F3]Hoặc, đặt tên vật phẩm trong hai ô riêng biệt, nói B1 và B2 và sử dụng từng ô đó làm tiêu chí:
=SUMIFS[D3:D7,B3:B7,F3]Xin lưu ý rằng công thức này hoạt động như SUMIF với hoặc logic - nó tổng hợp các giá trị nếu ít nhất một trong các tiêu chí được chỉ định được đáp ứng.OR logical - it sums values if at least one of the specified criteria is met.
Trong ví dụ này, chúng tôi thêm các giá trị trong cột B nếu cột A bằng "Táo" hoặc "Orange". Nói cách khác, SUMIF [] + SUMIF [] hoạt động giống như định dạng giả sau [không phải là thật, nó chỉ thể hiện logic!]: Sumif [A: A, "Táo" hoặc "Orange", B: B] .
Nếu bạn đang tìm cách tổng hợp có điều kiện với và logic, tức là thêm các giá trị khi tất cả các tiêu chí được chỉ định được đáp ứng, hãy sử dụng hàm SUMFS của Google Sheets.AND logical, i.e. add up values when all of the specified criteria are met, use the Google Sheets SUMIFS function.
Google Sheets Sumif - Những điều cần nhớ
Bây giờ bạn đã biết các loại hạt và bu lông của hàm SUMIF trong Google Sheets, có thể là một ý tưởng tốt để tạo ra một bản tóm tắt ngắn về những gì bạn đã học.
1. Sumif chỉ có thể đánh giá một điều kiện
Cú pháp của hàm SUMIF chỉ cho phép một phạm vi, một tiêu chí và một sum_range. Để tổng hợp với nhiều tiêu chí, thêm một số hàm tổng hợp với nhau [hoặc logic] hoặc sử dụng các công thức SUMIFS [và logic].sum with multiple criteria, either add several SUMIF functions together [OR logic] or use SUMIFS formulas [AND logic].
2. Hàm SUMIF không nhạy cảm trường hợp
Nếu bạn đang tìm kiếm một công thức SUMIF nhạy cảm trường hợp có thể phân biệt giữa các ký tự chữ hoa và chữ thường, hãy sử dụng SUMIF kết hợp với ArrayFormula và tìm như trong ví dụ này.
3. Cung cấp phạm vi có kích thước bằng nhau và sum_range
Trên thực tế, đối số SUM_RANGE chỉ xác định ô trên cùng bên trái của phạm vi thành tổng, khu vực còn lại được xác định bởi các kích thước của đối số phạm vi.
Để đặt nó khác nhau, SUMIF [A1: A10, "Táo", B1: B10] và SUMIF [A1: A10, "Táo", B1: B100] sẽ cả hai giá trị tổng trong phạm vi B1: B10 vì nó có cùng kích thước như phạm vi [A1: A10].
Vì vậy, ngay cả khi bạn cung cấp nhầm một phạm vi số tiền sai, Google Sheets vẫn sẽ tính toán công thức của bạn, với điều kiện ô trên cùng bên trái của SUM_RANGE là chính xác.
Điều đó nói rằng, vẫn nên cung cấp phạm vi có kích thước như nhau và sum_range để tránh sai lầm và ngăn ngừa các vấn đề không nhất quán.
4. Tận hưởng cú pháp của tiêu chí SUMIF
Đối với công thức tổng hợp Google Sheets của bạn hoạt động chính xác, hãy thể hiện các tiêu chí đúng cách:
- Nếu tiêu chí bao gồm văn bản, ký tự đại diện hoặc toán tử logic theo sau là một số, văn bản hoặc ngày, hãy gửi tiêu chí trong các dấu ngoặc kép. Ví dụ:text, wildcard character or logical operator followed by a number, text or date, enclose the criterion in quotation marks. For example:
6=SUMIFS[D3:D7,B3:B7,F3]
7=SUMIFS[D3:D7,B3:B7,F3]
8=SUMIFS[D3:D7,B3:B7,F3]
9=SUMIFS[D3:D7,B3:B7,F3] - Nếu tiêu chí bao gồm toán tử logic và tham chiếu ô hoặc chức năng khác, hãy sử dụng dấu ngoặc kép để bắt đầu chuỗi văn bản và ampersand [&] để kết hợp và hoàn thành chuỗi tắt. Ví dụ:logical operator and a cell reference or another function, use the quotation marks to start a text string
and ampersand [&] to concatenate and finish the string off. For example:
0=SUMIFS[D3:D7,B3:B7,F3]
1=SUMIFS[D3:D7,B3:B7,F3]
5. Khóa phạm vi với các tham chiếu ô tuyệt đối nếu cần
Nếu bạn có kế hoạch sao chép hoặc di chuyển công thức SUMIF của mình vào một điểm sau, hãy sửa các phạm vi bằng cách sử dụng các tham chiếu ô tuyệt đối [với dấu $] như SUMIF [$ a $ 2: $ A $ 10, "Táo", $ B $ 2: $ $ B $ 10].
Đây là cách bạn sử dụng chức năng SUMIF trong Google Sheets. Để có cái nhìn kỹ hơn về các công thức được thảo luận trong hướng dẫn này, bạn có thể mở Sumif Sumif của chúng tôi. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!