Để tô điểm các hàng SUMIFS (các hàng phù hợp với tiêu chí SUMIFS) trong Google Sheets, chúng ta có thể cần hai công thức. Một là SUMIFS và hai là REGEXMATCH. Tuy nhiên, cái đầu tiên là tùy chọn.
Để thêm một phạm vi điều kiện vào công thức SUMIFS, tức là tìm các hàng phù hợp với các điều kiện/tiêu chí SUMIFS, chúng ta có thể sử dụng công thức REGEXMATCH.
Tôi sẽ giải thích vì sao SUMIFS là tùy chọn trong việc định dạng có điều kiện sau.
Lưu ý nhỏ, nếu bạn chỉ có một cột điều kiện, bạn có thể sử dụng hàm SUMIF duy nhất. Không cần sử dụng hàm REGEXMATCH trong trường hợp này – Sumif trong Định dạng có điều kiện trong Google Sheets.
Mục đích của việc tô điểm các hàng SUMIFS trong Google Sheets
Trong một tập dữ liệu lớn, việc xác định thủ công các hàng mà SUMIFS tổng hợp sẽ là một nhiệm vụ khó khăn. Chúng ta có thể cần phải xem qua từng hàng để xác định xem nó có phù hợp với điều kiện đã cho hay không.
Trong ví dụ dưới đây, các điều kiện là Name = “A”, Area =”East”, và Month = “Jan”.
Công thức SUMIFS trong ô H2 (bạn sẽ nhận được công thức sau đó) sẽ không cho bạn bất kỳ gợi ý nào về các hàng phù hợp. Nó chỉ đơn giản cho bạn một tổng.
Theo các điều kiện trong H1:J1, tổng SUMIFS trong ô H2 là 13.
Tôi muốn tô điểm các hàng SUMIFS nếu tổng SUMIFS nhỏ hơn giá trị trong ô H3, tức là <20 (các hàng phù hợp đã được tô điểm màu vàng nhạt).
Bởi vì tôi muốn chỉnh sửa cột E trong các hàng phù hợp SUMIFS để tổng SUMIFS phù hợp với 20, tức là giá trị trong H3.
Nếu bạn tô điểm các hàng SUMIFS, bạn có thể dễ dàng chỉnh sửa các giá trị trong cột E, bằng cách thủ công hoặc sử dụng GOAL SEEK.
Lưu ý: Nếu bạn sử dụng GOAL SEEK trong một hàng không liên quan đến tính toán, bạn sẽ nhìn thấy lỗi sau đây.
“The goal could not be found. Subsequent iterations were not bringing the Add-on closer to a solution.”
Bước trên (tô điểm) giúp bạn sử dụng GOAL SEEK trong hàng đúng (các hàng tô điểm màu vàng).
Tóm lại, mục đích của việc tô điểm các hàng SUMIFS trong Google Sheets là dễ dàng chỉnh sửa các giá trị trong các hàng phù hợp với tiêu chí.
Cách tô điểm các hàng SUMIFS có điều kiện trong Google Sheets
Như tôi đã đề cập, bạn cần hai công thức. Một là SUMIFS và hai là REGEXMATCH.
Nếu bạn chỉ muốn tô điểm các hàng SUMIFS, thì hàm REGEXMATCH là đủ.
Để thêm một điều kiện vào việc tô điểm như là tổng SUMIFS nhỏ hơn, bằng hoặc lớn hơn một giá trị đã đặt (ô H3), chúng ta cần sử dụng SUMIFS.
Công thức REGEXMATCH để định dạng có điều kiện phạm vi phù hợp với SUMIFS
Công thức quy tắc:
=regexmatch($B$2:$B$15&$C$2:$C$15&$D$2:$D$15,$H$1&$I$1&$J$1)=true
Chỉ cần nhập công thức này vào quy tắc công thức tùy chỉnh trong định dạng có điều kiện để tô điểm các hàng phù hợp với tiêu chí SUMIFS.
Tôi hy vọng rằng bạn đã biết cách chèn công thức này trong Định dạng có điều kiện (Menu Định dạng> Định dạng có điều kiện). Nếu không, ảnh chụp màn hình dưới đây sẽ hữu ích.
REGEXMATCH khớp các giá trị kết hợp trong các cột B, C và D với các tiêu chí kết hợp trong ô H1, I1 và J1. Một mẹo vui nhộn, phải không?
Hãy nhớ rằng công thức này chỉ tô điểm các hàng phù hợp với SUMIFS. Nó không liên quan gì đến tổng SUMIFS.
Công thức SUMIFS để tổng hợp một phạm vi
=sumifs($E$2:$E,$B$2:$B,$H1,$C$2:$C,$I1,$D$2:$D,$J1)
Công thức trên trả về tổng của cột E trong ô H2 dựa trên các tiêu chí. Tương tự như công thức REGEXMATCH, nó cũng sử dụng các tiêu chí từ các ô H1, I1 và J1.
Tôi chỉ bỏ qua giải thích công thức, hy vọng bạn có thể đã biết cách sử dụng chức năng này. Nếu chưa, bạn có thể tìm hiểu điều đó từ Hướng dẫn các chức năng Google Sheets của tôi.
Quy tắc SUMIFS và REGEXMATCH để tô điểm các hàng SUMIFS trong Google Sheets
Để tô điểm các hàng SUMIFS dựa trên tổng của chúng, chúng ta phải kết nối công thức REGEXMATCH và SUMIFS một cách nào đó. Chúng ta có thể sử dụng lệnh IF để làm điều đó.
Công thức SUMIFS của chúng ta nằm trong ô H2. Tôi muốn tô điểm các hàng SUMIFS trong phạm vi B2:E dựa trên giá trị tổng trong ô H2.
Tôi muốn nói nếu giá trị trong H2 nhỏ hơn giá trị trong H3, tô điểm các hàng liên quan đến tổng SUMIFS.
Vì vậy, chỉ cần chỉnh sửa công thức REGEXMATCH trước đó trong định dạng có điều kiện như dưới đây. Xem việc sử dụng IF ở đầu công thức.
=if($H$2<$H$3,regexmatch($B$2:$B$15&$C$2:$C$15&$D$2:$D$15,$H$1&$I$1&$J$1)=true)
Thay đổi $H$2<$H$3 thành $H$2>$H$3 để tô điểm nếu tổng SUMIFS lớn hơn giá trị đã đặt trong H3.
Tài liệu tham khảo bổ sung
- So sánh Sumifs, Sumproduct, Dsum với Ví dụ trong Google Sheets
- Cách Bao Gồm Cùng Một Trường Hai Lần Trong Hàm SUMIFS trong Google Sheets
- Hàm REGEXMATCH trong SUMIFS và Nhiều Cột Tiêu Chí trong Google Sheets
- Sự khác biệt giữa SUMIFS và SUMPRODUCT trong Google Sheets
- Sự khác biệt giữa SUMIFS và DSUM trong Google Sheets
- Cách Sử Dụng Tiêu Chí Khác Nhau Trong Hàm SUMIFS Google Sheets
- Cách Bao Gồm Phạm Vi Ngày Trong SUMIFS trong Google Sheets
- Vấn Đề Mở Rộng Công Thức Mảng Sumifs Và Các Công Thức Thay Thế
- Cách Tính Tổng Cộng Sumif/Sumifs Loại Bỏ Nội Bộ Trong Google Sheets
- SUMIFS với Điều Kiện HOẶC trong Google Sheets
