Hãy tưởng tượng rằng bạn đang cần tính tổng các giá trị trong bảng tính của mình dựa trên các điều kiện cụ thể. Đây là nơi Chức năng TỔNG.ĐIỀU.KIỆN trong Google Sheets trở nên hữu ích.
Chức năng TỔNG.ĐIỀU.KIỆN trong Google Sheets là gì?
Chức năng TỔNG.ĐIỀU.KIỆN trong Google Sheets cho phép bạn tính tổng các giá trị trong bảng tính dựa trên các điều kiện đã được định sẵn.
Khác với chức năng TỔNG, chỉ tính tổng các giá trị dựa trên một điều kiện duy nhất, chức năng TỔNG.ĐIỀU.KIỆN có thể áp dụng hai điều kiện trở lên. Chức năng TỔNG.ĐIỀU.KIỆN trong Google Sheets chỉ tính tổng giá trị khi tất cả các điều kiện đều được thỏa mãn.
Hướng dẫn cuối cùng về TỔNG.ĐIỀU.KIỆN trong Google Sheets này sẽ chỉ cho bạn cách thao tác với chức năng này thông qua một loạt các công thức mẫu, ví dụ sử dụng và hướng dẫn từng bước. Bạn cũng có thể xem video hướng dẫn đầy đủ về chức năng TỔNG.ĐIỀU.KIỆN trong Google Sheets dưới đây.
Video hướng dẫn: Cách sử dụng TỔNG.ĐIỀU.KIỆN trong Google Sheets
Tại sao nên sử dụng chức năng TỔNG.ĐIỀU.KIỆN trong Google Sheets?
Trong Google Sheets, cách đơn giản nhất để tính tổng các giá trị trong các ô là tạo một công thức cộng dồn.
=B1+B2+B3+B4+B5
Tuy nhiên, phương pháp cơ bản này không thực tế khi bạn chỉ muốn tính tổng một số giá trị cụ thể trong một phạm vi lớn hơn, chẳng hạn như toàn bộ hàng hoặc cột. Đó là lúc chức năng TỔNG trở nên hữu ích. Nhưng khi bạn chỉ muốn tính tổng một số giá trị cụ thể trong một phạm vi, chức năng TỔNG.ĐIỀU.KIỆN là một lựa chọn tốt hơn. Hãy nghĩ về TỔNG.ĐIỀU.KIỆN như một phiên bản TỔNG với sức mạnh siêu việt. Với TỔNG.ĐIỀU.KIỆN, bạn có thể dễ dàng tính tổng các phạm vi và đặt các tiêu chí để loại bỏ một số giá trị khỏi tổng.
Cú pháp của chức năng TỔNG.ĐIỀU.KIỆN trong Google Sheets
Cú pháp cơ bản của chức năng TỔNG.ĐIỀU.KIỆN trong Google Sheets như sau:
TỔNG.ĐIỀU.KIỆN(phạm_vi_tổng, phạm_vi_điều_kiện1, điều_kiện1, [phạm_vi_điều_kiện2, điều_kiện2], ...)
Cú pháp này bao gồm các đối số sau:
phạm_vi_tổnglà phạm vi các ô bạn muốn tính tổng.phạm_vi_điều_kiện1là phạm vi các ô bạn muốn kiểm tra với điều kiện.điều_kiện1xác định các điều kiện cho các ô trongphạm_vi_điều_kiện1. Các ô thỏa mãn các điều kiện này sẽ được cộng vào tổng.phạm_vi_điều_kiện2,điều_kiện2,… đại diện cho các phạm vi và điều kiện bổ sung trong chức năng TỔNG.ĐIỀU.KIỆN. Bạn có thể bao gồm tối đa 127 cặp phạm vi/điều kiện trong một công thức.
Mỗi công thức TỔNG.ĐIỀU.KIỆN phải xác định ít nhất một cặp phạm vi/điều kiện, mặc dù trong trường hợp này một công thức TỔNG.ĐIỀU.KIỆN có thể thực hiện cùng một nhiệm vụ.
Một số gợi ý quan trọng để sử dụng chức năng TỔNG.ĐIỀU.KIỆN trong Google Sheets
Trong quá trình triển khai công thức TỔNG.ĐIỀU.KIỆN trong Google Sheets, nó tương đối đơn giản, nhưng hãy cân nhắc những gợi ý quan trọng sau để đảm bảo quá trình diễn ra một cách trơn tru:
- TỔNG.ĐIỀU.KIỆN chỉ cộng các giá trị của ô khi tất cả các điều kiện được xác định đều đúng, tương tự như toán tử logic VÀ trong các ngôn ngữ lập trình.
- Bạn phải đặt các ngày, văn bản và ký tự chung trong dấu ngoặc kép.
- Phạm vi của các điều kiện không được vượt qua phạm vi của tổng, nếu không công thức TỔNG.ĐIỀU.KIỆN sẽ trả về lỗi #VALUE.
- Bạn phải đặt các toán tử logic trong dấu ngoặc kép trong văn bản, số và ngày.
Những gợi ý này sẽ giúp bạn tránh các lỗi phổ biến mà chúng tôi đã quan sát thấy ở người dùng. Bây giờ chúng ta hãy đi vào các ví dụ cụ thể.
Cách sử dụng chức năng TỔNG.ĐIỀU.KIỆN trong Google Sheets
Trước khi sử dụng các công thức TỔNG.ĐIỀU.KIỆN, hãy tạo một bộ dữ liệu mẫu trước. Trong ví dụ này, chúng ta sẽ nhập dữ liệu bán hàng từ HubSpot vào Google Sheets bằng ứng dụng Coefficient. Ứng dụng Coefficient cho phép bạn tự động đồng bộ dữ liệu từ hệ thống doanh nghiệp với Sheets.
Bây giờ, hãy sử dụng bộ dữ liệu thực tế này để tìm hiểu cách sử dụng chức năng TỔNG.ĐIỀU.KIỆN. Giả sử bạn muốn tính tổng số tiền cho một mục cụ thể với một trạng thái đăng ký nhất định, ví dụ như kế hoạch cơ bản đang hoạt động.
Về lý thuyết, bạn có thể sử dụng một công thức ARRAYFORMULA như sau:
=ARRAYFORMULA(SUMPRODUCT((A6:A14="Basic")*(C6:C14="Active")*(B6:B14)))
Mặc dù công thức ARRAYFORMULA trên cho kết quả mong muốn, nhưng nó có thể phức tạp không cần thiết. Một phương pháp đơn giản hơn là sử dụng chức năng TỔNG.ĐIỀU.KIỆN của Google Sheets.
Hãy xác định các đối số trong công thức TỔNG.ĐIỀU.KIỆN. Chúng ta chỉ muốn cộng các giá trị liên quan đến kế hoạch cơ bản đang hoạt động:
phạm_vi_điều_kiện1: A6:A14, vàđiều_kiện1: “Basic”.phạm_vi_điều_kiện2: C6:C14, vàđiều_kiện2: “Active”.phạm_vi_tổng: B6:B14, vì các giá trị chúng tôi muốn cộng nằm trong cột B.
Công thức TỔNG.ĐIỀU.KIỆN hoàn chỉnh sẽ có dạng như sau:
=TỔNG.ĐIỀU.KIỆN(B6:B14, A6:A14, "basic", C6:C14, "active")
Kết quả cuối cùng sẽ tương ứng với mỗi đối số trong công thức.
Sử dụng Trợ lý công thức GPT để tự động tạo công thức TỔNG.ĐIỀU.KIỆN
Bạn có thể sử dụng Trợ lý công thức miễn phí từ Coefficient để tự động tạo công thức TỔNG.ĐIỀU.KIỆN cho bạn. Để sử dụng Trợ lý công thức, bạn phải cài đặt Coefficient bằng cách làm theo các hướng dẫn. Việc cài đặt chỉ mất không quá một phút.
Khi hoàn thành việc cài đặt, quay lại Phần mở rộng trong menu Google Sheets. Coefficient sẽ hiện là một tiện ích.
Bây giờ, hãy khởi chạy ứng dụng. Coefficient sẽ chạy trong thanh bên của bảng tính Google của bạn. Hãy chọn Trợ lý GPT trong thanh bên của Coefficient.
Sau đó, nhấp vào Trợ lý công thức.
Nhập mô tả của một công thức vào ô văn bản. Ví dụ, nhập “Thêm giá trị từ B6:B14 nếu giá trị từ A6:A14 là ‘cơ bản’ và giá trị từ C6:C14 là ‘hoạt động'”.
Tiếp theo, nhấp vào “Xây dựng”. Trợ lý công thức sẽ tự động tạo công thức từ ví dụ đầu tiên.
Bạn chỉ cần sao chép và dán công thức TỔNG.ĐIỀU.KIỆN vào ô mong muốn.
Bây giờ chúng ta đã biết cơ bản, hãy xem qua một số ví dụ khác về việc sử dụng công thức TỔNG.ĐIỀU.KIỆN với các loại điều kiện khác nhau.
Sử dụng chức năng TỔNG.ĐIỀU.KIỆN với các toán tử logic
Khi các điều kiện trong công thức TỔNG.ĐIỀU.KIỆN liên quan đến ngày hoặc số, bạn cần sử dụng các toán tử so sánh. Dưới đây là một lời nhắc về cách sử dụng các toán tử so sánh trong Google Sheets:
- Lớn hơn (>)
- Nhỏ hơn (<)
- Lớn hơn hoặc bằng (>=)
- Nhỏ hơn hoặc bằng (<=)
- Bằng (= hoặc bỏ qua)
- Khác (<>)
Công thức TỔNG.ĐIỀU.KIỆN dưới đây xác định các đăng ký hoạt động trước ngày 1 tháng 4 năm 2022 và tổng các giá trị lớn hơn hoặc bằng 200.
=TỔNG.ĐIỀU.KIỆN(B6:B14, B6:B14, ">=200", C6:C14, "<April 1, 2022")
Nếu bạn muốn thay thế các giá trị bằng các tham chiếu tới ô, đặt các toán tử so sánh trong dấu ngoặc kép. Cộng thêm dấu và phạm vi tham chiếu ô.
Công thức của bạn sẽ trông như sau:
=TỔNG.ĐIỀU.KIỆN(B6:B14, B6:B14, ">= "&B1, C6:C14, "< "&B2)
Sử dụng TỔNG.ĐIỀU.KIỆN với các ô trống và không trống
Bạn cũng có thể tính tổng các số trong một cột nếu một ô trong cột khác là trống. Đây là cách làm:
- “=” để tính tổng các ô trống (rỗng)
- “” để tính tổng các ô trống (bao gồm chuỗi rỗng)
- “<>” để cộng các ô không trống (bao gồm chuỗi rỗng)
Giả sử cột “Ngày kích hoạt” chứa các ô trống. Bạn có thể viết công thức TỔNG.ĐIỀU.KIỆN để cộng các kế hoạch cơ bản với ngày kích hoạt đã được xác định, dựa trên các ô rỗng:
=TỔNG.ĐIỀU.KIỆN(B6:B14, A6:A14, "basic", C6:C14, "<>")
Sử dụng TỔNG.ĐIỀU.KIỆN với các chức năng khác
Các điều kiện trong công thức TỔNG.ĐIỀU.KIỆN có thể sử dụng các chức năng khác như phụ thuộc. Nghĩa là bạn có thể tích hợp các chức năng khác vào các điều kiện của công thức TỔNG.ĐIỀU.KIỆN.
Hãy nói rằng chúng ta muốn bao gồm các ngày kích hoạt từ hôm nay trở đi. Trong trường hợp này, chúng ta có thể nối toán tử nhỏ hơn hoặc bằng (<=) với hàm TODAY() bên trong công thức TỔNG.ĐIỀU.KIỆN:
=TỔNG.ĐIỀU.KIỆN(B6:B14, A6:A14, B2, C6:C14, "<="&TODAY())
Sử dụng TỔNG.ĐIỀU.KIỆN với nhiều điều kiện OR trong cùng một cột
Bạn cũng có thể cộng hai chức năng TỔNG.ĐIỀU.KIỆN với nhau. Ví dụ, nếu bạn muốn cộng các giá trị từ kế hoạch “Cơ bản” hoặc “Quản trị”, bạn có thể sử dụng một kết hợp của SUMIF() + SUMIF():
=SUMIF(B:B, A:A, "basic") + SUMIF(A:A, "management", B:B)
Nếu có ba điều kiện trở lên, bạn nên sử dụng một công thức nhỏ gọn hơn. Đặt các mục trong một mảng hàng. Sử dụng ArrayFormula để tạo tổng phần tử con và bao toàn bộ trong một chức năng SUM() để cộng các tổng con.
Công thức sẽ trông như sau:
=SUM(ArrayFormula(SUMIF(A6:A14, {"basic", "business", "enterprise"}, B6:B14)))
Bây giờ, hãy nhập các mục vào các ô riêng lẻ. Bao gồm các tham chiếu ô cho bảng của bạn (cho các ô không liền kề) hoặc cung cấp một phạm vi (cho các ô liền kề). Chúng tôi sẽ hạn chế danh sách này thành hai mục:
=SUM(ArrayFormula(SUMIF(A6:A14, {B1, B2}, B6:B14)))
Hoặc
=SUM(ArrayFormula(SUMIF(A6:A14, B1:B2, B6:B14)))
Nếu bạn muốn, bạn có thể thêm các tổng con bằng cách sử dụng hàm SUMPRODUCT thay vì ArrayFormula:
=SUMPRODUCT((A6:A14="basic") + (A6:A14="business"), B6:B14)
Sử dụng cú pháp này cho nhiều điều kiện OR (với ba mục trở lên):
=SUMPRODUCT((A6:A14={"basic", "business"}) * B6:B14)
Thay thế các mục trong mảng bằng tham chiếu ô và bạn sẽ có một công thức nhỏ gọn hơn.
=SUMPRODUCT((A6:A14={B1, B2}) * B6:B14)
Sử dụng TỔNG.ĐIỀU.KIỆN với nhiều điều kiện OR và kết quả trong các ô khác
Như đã đề cập ở trên, bạn có thể tạo tổng con cho mỗi mục trong một ô khác với một công thức mảng. Từ đó, bạn có thể điều chỉnh các tham chiếu ô và loại bỏ phần SUM(). Kết quả sẽ dưới dạng sau:
=ARRAYFORMULA(SUMIF(A4:A13, {"basic", "business", "enterprise"}, B4:B13))
Trong ví dụ này, công thức sẽ cho kết quả sau:
Chắc chắn rằng bạn có các ô trống bên phải ô chứa công thức. Google Sheets sẽ tự động đặt kết quả vào các ô tương ứng với số lượng mục trong hằng số mảng của bạn.
Nếu bạn muốn hiển thị các tổng con trong một cột, hãy tạo một mảng dọc bằng cách phân tách các mục bằng dấu chấm phẩy:
=ARRAYFORMULA(SUMIF(A3:A11, {"basic"; "business"; "enterprise"}, B3:B11))
Hơn nữa, bạn có thể sử dụng một tham chiếu phạm vi để làm cho công thức của bạn linh hoạt hơn. Khi đó, người dùng khác trong nhóm của bạn có thể nhập các mục vào các ô được xác định trước và bạn sẽ không cần phải cập nhật công thức của mình.
Ví dụ, công thức ở trên có thể trông như thế này:
=ARRAYFORMULA(SUMIF(A3:A11, D3:D5, B3:B11))
Sử dụng TỔNG.ĐIỀU.KIỆN với nhiều điều kiện OR trong các cột khác nhau
Bạn cũng có thể sử dụng chức năng TỔNG.ĐIỀU.KIỆN để cộng các số với nhiều tập hợp điều kiện dựa trên quy tắc logic sau:
- Tất cả các điều kiện phải đúng (quy tắc logic AND) trong mỗi tập hợp.
- Một ô sẽ được cộng nếu bất kỳ tập hợp nào thoả mãn (quy tắc logic OR).
Dưới đây là một ví dụ. Hãy cộng số trong cột B nếu cột A chứa “Basic” hoặc “Enterprise” và ngày kích hoạt trong cột C là ngày 16 tháng 3 năm 2022.
Cách thông thường là lấy hai công thức TỔNG.ĐIỀU.KIỆN riêng biệt để cộng “Cơ bản” và “Quản trị”, sau đó cộng các kết quả:
=SUMIF(B:B, A:A, "basic") + SUMIF(A:A, "management", B:B)
Tuy nhiên, Google Sheets không cho phép hằng số mảng với nhiều điều kiện OR. Thay vào đó, bạn có thể sử dụng một công thức nhỏ gọn hơn với chức năng SUMPRODUCT.
Sử dụng công thức sau để cộng các số trong B6:B14 dựa trên các điều kiện ở trên:
=SUMPRODUCT(-(C6:C14=B3), (-(ISNUMBER(MATCH(A6:A14, B2:C2, 0)))), B6:B14)
Dưới đây là cách các phần của công thức hoạt động.
- Xác định xem một mục có thuộc về cơ bản hoặc doanh nghiệp không: ISNUMBER(MATCH(A6:A14, B2:C2,0)). Bạn sẽ nhận được giá trị TRUE nếu một trong các điều kiện đã được xác định được thỏa mãn và giá trị FALSE nếu không có điều kiện nào được thỏa mãn.
- So sánh các ngày danh sách với ngày cụ thể: C6:C14=B3. Bạn cũng có thể nhập ngày trực tiếp vào công thức thay vì một tham chiếu ô bằng cách sử dụng hàm DATE hoặc DATEVALUE. Ví dụ: sử dụng C6:C14=DATEVALUE(“March 16, 2022”) hoặc C6:C14=DATE(March 16, 2022).
- Chuyển đổi cả mảng TRUE và FALSE thành 1 và 0 bằng cách sử dụng toán tử phủ định hai lần (-).
- Cung cấp phạm vi cần cộng: B6:B14
Chỉ các giá trị phù hợp với các điều kiện đăng ký đã được xác định và ngày kích hoạt mới được cộng.
Chức năng TỔNG.ĐIỀU.KIỆN: Một phương pháp đa năng để cộng các giá trị dựa trên điều kiện
Chức năng TỔNG.ĐIỀU.KIỆN thực sự hữu ích khi bạn muốn cộng các giá trị dựa trên những điều kiện đã được định sẵn trong một phạm vi ô. Bằng cách nắm vững chức năng TỔNG.ĐIỀU.KIỆN và các biến thể của nó, bạn có thể nâng cao khả năng tính toán của mình trong bảng tính và làm cho các công thức của mình hiệu quả và thông minh hơn.
Khách hàng của chúng tôi thường sử dụng chức năng TỔNG.ĐIỀU.KIỆN để thực hiện các phân tích thông tin hữu ích, đồng thời tối ưu hiệu suất của bảng tính. Họ triển khai chức năng TỔNG.ĐIỀU.KIỆN với dữ liệu trực tiếp trong Google Sheets thông qua Coefficient.
Hãy thử Coefficient miễn phí ngay bây giờ!
Crawlan.com là một nguồn tài nguyên tuyệt vời để tìm hiểu về các tính năng nâng cao của Google Sheets và khám phá những mẹo hữu ích khác.
