Để tô đậ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ần sử dụng hai công thức. Một là SUMIFS và một là REGEXMATCH. Tuy nhiên, công thức trước là tùy chọn.
Để thêm điều kiện cho việc tô đậm như là tổng của SUMIFS nhỏ hơn, bằng hoặc lớn hơn một giá trị đặt sẵn (cell H3), chúng ta cần sử dụng SUMIFS.
Mục đích của việc tô đậ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 là một công việc khó khăn. Chúng ta có thể phải kiểm tra từng hàng để xem liệu nó 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à Tên = “A”, Khu vực =”Đông”, và Tháng = “Tháng 1”.
Theo công thức SUMIFS trong ô H2 (bạn sẽ nhận được công thức sau đây), bạn sẽ không có bất kỳ gợi ý nào về các hàng phù hợp. Nó chỉ đơn giản là đưa ra một tổng kết.
Theo các điều kiện trong H1:J1, tổng SUMIFS trong ô H2 là 13.
Tôi muốn tô đậm các hàng SUMIFS nếu tổng SUMIFS nhỏ hơn giá trị trong ô H3, tức <20 (các hàng phù hợp đã được tô đậ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 SUMIFS phù hợp để phù hợp với tổng SUMIFS thành 20, tức là giá trị trong H3.
Nếu bạn tô đậ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 việc tính toán, bạn sẽ 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ô đậm) giúp bạn sử dụng GOAL SEEK trong hàng đúng (các hàng được tô đậm màu vàng).
Nói ngắn gọn, mục đích của việc tô đậ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ô đậm các hàng SUMIFS theo điều kiện trong Google Sheets
Như tôi đã đề cập trước đó, bạn cần hai công thức. Một là SUMIFS và một là REGEXMATCH.
Nếu bạn chỉ muốn tô đậm các hàng SUMIFS, thì chỉ cần sử dụng hàm REGEXMATCH là đủ.
Để thêm điều kiện cho việc tô đậm như là tổng của SUMIFS nhỏ hơn, bằng hoặc lớn hơn một giá trị đặt sẵn (cell H3), chúng ta cần sử dụng SUMIFS.
Công thức REGEXMATCH để định dạng điều kiện trên phạm vi phù hợp của SUMIFS
Công thứ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 điều kiện để tô đậm các hàng phù hợp với tiêu chí SUMIFS.
Tôi hy vọng, bạn đã biết cách chèn công thức này vào định dạng điều kiện (Menu Format > Conditional formatting). Nếu chưa, hình ảnh bên dưới sẽ hữu ích.
REGEXMATCH phù hợ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 vô cùng thông minh, phải không?
Hãy nhớ rằng công thức này chỉ tô đậm các hàng SUMIFS phù hợp. Nó không liên quan đến tổng của SUMIFS.
Công thức SUMIFS để tổng hợp một phạm vi
Công thức: =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 rằng bạn có thể biết cách sử dụng hàm này. Nếu chưa, bạn có thể tìm hiểu trong Hướng dẫn các chức năng của Google Sheets.
Quy tắc SUMIFS và REGEXMATCH để tô đậm các hàng SUMIFS trong Google Sheets
Để tô đậm các hàng SUMIFS dựa trên tổng của nó, chúng ta phải kết nối công thức REGEXMATCH và SUMIFS. Chúng ta có thể sử dụng hàm IF để làm điều đó.
SUMIFS của chúng ta ở ô H2. Tôi muốn tô đậ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 rằng nếu giá trị trong ô H2 nhỏ hơn giá trị trong ô H3, hãy tô đậm các hàng tham gia vào tổng của SUMIFS.
Để làm điều đó, chỉ cần sửa đổi công thức REGEXMATCH trước đó trong định dạng điều kiện như dưới đây. Xem 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)
Sửa $H$2<$H$3 thành $H$2>$H$3 để tô đậm nếu tổng của SUMIFS lớn hơn giá trị đã đặt trong H3.
Tài nguyên bổ sung
- So sánh Sumifs, Sumproduct, Dsum với Ví dụ trong Google Sheets.
- Làm thế nào để bao gồm cùng một trường hai lần trong hàm SUMIFS trong Google Sheets.
- REGEXMATCH trong SUMIFS và các cột tiêu chí nhiều 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.
- Làm thế nào để sử dụng tiêu chí khác nhau trong hàm SUMIFS của Google Sheets.
- Làm thế nào để bao gồm một 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ế.
- Làm thế nào để Sumif/Sumifs Loại Trừ Các Giá Trị Trùng Lặp trong Google Sheets.
- SUMIFS với Điều Kiện HOẶC trong Google Sheets.
