Hãy cùng tìm hiểu cách sử dụng công thức tùy chỉnh để tô sáng các bản ghi trùng trong cùng một tháng trong Google Sheets nhé!
Trong cuộc sống hàng ngày, việc này sẽ giúp chúng ta nhanh chóng tìm thấy tất cả các mặt hàng mà chúng ta đã mua nhiều hơn một lần trong cùng một tháng.
Ví dụ, nếu chúng ta đã mua Xi măng vào ngày 15/08/2022, 26/08/2022 và 27/08/2022, quy tắc sẽ tô sáng hai bản ghi cuối cùng vì chúng là các bản sao.
Đối với các quy tắc có sẵn, ta có thể tô sáng các bản ghi dựa trên ngày tháng như “ngày là”, “ngày trước” và “ngày sau” trong điều kiện định dạng trong Google Sheets.
Nhưng không có quy tắc nào để tô sáng các bản ghi trùng trong cùng một tháng trong điều kiện định dạng trong Google Sheets.
Vậy hãy cùng tìm hiểu cách tạo một quy tắc nhé. Hãy nhớ rằng quy tắc có thể thay đổi một chút tùy thuộc vào số cột trong phạm vi của bạn.
Tìm và tô sáng các bản ghi trùng trong cùng một tháng – Hai cột
Dưới đây là một bộ dữ liệu hai cột.
Tôi đã sử dụng một công thức định dạng có điều kiện để tô sáng các mục trùng lặp (cột B) dựa trên tháng (cột A).
Vui lòng kiểm tra ghi chú của tôi trong cột C. Khi tô sáng, quy tắc định dạng loại trừ bản ghi đầu tiên.
Trong tháng Tám, chúng ta đã mua đá cuội 5-10 mm hai lần (dòng thứ 5 và 7) và Cát trắng cũng hai lần (dòng thứ 6 và 9).
Trong tháng Mười Một cùng năm đó, chúng ta đã mua đá cuội ba lần (dòng thứ 10, 11 và 12).
Vậy khi chúng ta tô sáng các bản ghi trùng trong cùng một tháng, quy tắc sẽ tô sáng dòng 7, 9, 11 và 12. Nghĩa là các lần xuất hiện lớn hơn 1.
Quy tắc định dạng và áp dụng chúng
Quy tắc định dạng tùy chỉnh (Hai cột):
=ArrayFormula(countifs($B$2:$B2,$B2,eomonth($A$2:$A2,0),eomonth($A2,0),row($B$2:$B2),"<="&row($B2)))>1
Cách áp dụng quy tắc trong Google Sheets:
Bạn có thể sử dụng quy tắc này cho A2:A, B2:B, A2:B hoặc A2:Z. Vậy làm thế nào để áp dụng nó trong Google Sheets?
Hãy làm theo các bước sau để áp dụng quy tắc tùy chỉnh và tô sáng các bản ghi trùng trong cùng một tháng trong Google Sheets:
- Chọn A2:B hoặc bất kỳ phạm vi nào đã đề cập ở trên.
- Truy cập vào Menu “Định dạng” > “Định dạng điều kiện”.
- Dưới Quy tắc định dạng, chọn “Công thức tùy chỉnh”.
- Sao chép mã quy tắc định dạng trên và dán vào ô trống.
- Sử dụng kiểu định dạng mặc định hoặc tùy chỉnh nó.
- Nhấp vào “Hoàn thành”.
Sự logic đằng sau việc tô sáng các bản ghi trùng trong cùng một tháng
Để hiểu rõ công thức COUNTIFS (Yep! mã trên dựa trên chức năng này), làm theo các bước sau.
Sao chép và dán mã trên vào ô C2 và sau đó sửa nó để xóa phần “>1” ở cuối mã.
Kéo xuống kích cỡ điếm (hộp màu xanh nhỏ ở góc dưới cùng bên phải của ô C2) cho đến khi bạn đến dòng số 12.
Nếu công thức trả về giá trị “>1” ở bất kỳ ô nào, các bản ghi đó rơi vào danh mục các bản ghi trùng trong cùng một tháng.
Nghĩa là để tô sáng các mục trùng trong cùng một tháng, chúng ta nên tô sáng các dòng mà công thức trả về giá trị “>1”.
Giá trị “>1” ở cuối công thức được sử dụng để kiểm tra logic. Nó sẽ trả về TRUE cho giá trị “>1” và FALSE cho giá trị 1.
Google Sheets Conditional Formatting sẽ tô sáng các dòng có giá trị TRUE.
Lưu ý rằng hãy xóa các phần “eomonth($A$2:$A2,0),eomonth($A2,0),” và “>1” khỏi công thức để có được đếm số lần chạy.
Quy tắc cho nhiều hơn hai cột
Giả sử chúng ta đã thêm một cột số lượng và muốn bao gồm nó vào quy tắc định dạng.
Vậy khi tô sáng các bản ghi trùng trong cùng một tháng, cũng cần xem xét các mặt hàng và số lượng của chúng.
Vui lòng xem ảnh bên dưới.
Dưới đây tôi sẽ nói về các ngoại lệ.
Như bạn có thể thấy, Cát trắng lặp lại hai lần vào tháng Tám (dòng thứ 6 và 9). Nhưng số lượng của chúng khác nhau, vì vậy bị loại trừ.
Trong trường hợp của Đá cuội, nó xuất hiện ba lần trong tháng Mười Một (dòng thứ 10, 11 và 12). Nhưng số lượng chỉ khớp trong dòng thứ 10 và 11. Vì vậy, quy tắc nên tô sáng dòng thứ 11.
Làm thế nào để bao gồm số lượng trong mã trên?
Quy tắc định dạng tùy chỉnh: (Ba cột)
=ArrayFormula(countifs($B$2:$B2,$B2,eomonth($A$2:$A2,0),eomonth($A2,0),$C$2:$C2,$C2,row($B$2:$B2),"<="&row($B2)))>1
Nếu bạn muốn bao gồm các cột bổ sung, hãy làm theo kỹ thuật trên.
Vậy là tất cả về cách tô sáng các mục trùng trong cùng một tháng trong Google Sheets.
Cảm ơn vì đã đọc bài viết. Chúc bạn vui vẻ!
