Trong hướng dẫn này, chúng ta sẽ tìm hiểu cách đánh dấu các giá trị trùng lặp dựa trên ngày xuất hiện trong Google Sheets. Nếu bạn đã kiểm tra “Tài nguyên” ở cuối bài viết này, bạn có thể tìm thấy một số hướng dẫn về định dạng điều kiện cơ bản liên quan đến các giá trị trùng lặp. Tuy nhiên, không có hướng dẫn nào giải quyết cách đánh dấu các giá trị trùng lặp dựa trên ngày xuất hiện của chúng.
Ví dụ, năm ngoái tôi đã bán một số sách trực tuyến. Tôi đã ghi lại thông tin đó trên Google Sheets trong hai cột – cột ngày và cột tiêu đề sách. Làm thế nào để đánh dấu các cuốn sách đã được bán trong vòng 30 ngày kể từ lần bán cuối cùng?
Tôi tiếp cận yêu cầu định dạng điều kiện này từ hai góc độ khác nhau.
Tiên quyết (Sắp xếp)
Trước tiên, chúng ta phải sắp xếp bảng.
Chúng ta có thể sắp xếp bảng theo hai cách khác nhau – 1) Sắp xếp theo “Ngày” và sau đó là “Tiêu đề” hoặc 2) Sắp xếp theo “Tiêu đề” và sau đó là “Ngày”.
Ở đây, tôi sẽ tuân thủ cách thứ hai. Để làm điều đó, vui lòng tuân theo các bước sau.
-
Chọn phạm vi A1:B trong đó A1:A chứa các ngày bán, và B1:B chứa các tiêu đề sách (hãy cuộn xuống và xem hình ảnh #2).
-
Đi đến Dữ liệu > Sắp xếp phạm vi > Tùy chọn sắp xếp phạm vi nâng cao.
-
Kiểm tra “Dữ liệu có hàng tiêu đề.”
-
Sắp xếp theo Tiêu đề – A -> Z.
-
Nhấp vào “Thêm cột sắp xếp khác.”
-
Sau đó là Ngày – A -> Z.
-
Nhấp vào Sắp xếp.
Bây giờ chúng ta hãy viết luật định dạng điều kiện để đánh dấu các giá trị trùng lặp dựa trên ngày xuất hiện trong Google Sheets.
Đánh dấu giá trị trùng lặp DUY NHẤT dựa trên ngày xuất hiện ít hơn 30
Giả sử tôi đã bán “Sách 1” vào ngày 01-01-2021, 05-02-2021, 25-02-2021, 06-04-2021 và 21-04-2021.
Số ngày giữa 01-01-2021 và 05-02-2021 là 36 ngày. Vượt quá giới hạn, tức là <= 30 ngày.
Vì vậy, chúng ta phải tiếp tục kiểm tra.
Lần này, hãy tìm sự khác biệt giữa 05-02-2021 và 25-02-2021 và chúng ta sẽ có 21 ngày, nằm trong giới hạn.
Vì vậy, công thức này phải làm nổi bật hàng chứa ngày 25-02-2021.
Chúng ta không cần kiểm tra tiếp vì chúng ta chỉ muốn đánh dấu các giá trị trùng lặp duy nhất dựa trên ngày xuất hiện của chúng.
Dưới đây là mã để làm nổi bật các bản sao duy nhất dựa trên ngày xuất hiện.
=and( len(A2), len(B2), A2=sortn( filter( {filter($A$2:$A,$B$2:$B=B2);"x"}, {filter($A$2:$A,$B$2:$B=B2);"x"}- {"x";filter($A$2:$A,$B$2:$B=B2)}<31 ) ) )
Tôi biết rằng có nhiều điều cần giải thích liên quan đến quy tắc trên. Hãy bắt đầu từng điều một.
Chúng ta có thể áp dụng các quy tắc trên cột A, cột B hoặc hàng hoàn chỉnh.
Trong các bước của tôi, tôi chỉ đang làm nổi bật cột B.
Dưới đây là các bước để áp dụng công thức tùy chỉnh để đánh dấu các giá trị trùng lặp dựa trên ngày xuất hiện trong Google Sheets.
-
Sao chép công thức trên.
-
Chọn B2:B.
-
Đi đến Đị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…”
-
Dán quy tắc trên.
-
Chọn một màu từ bảng màu dưới “Kiểu định dạng”.
-
Nhấp vào “Hoàn thành.”
Giải thích quy tắc đánh dấu
Chúng ta đã sử dụng kiểm tra logic AND để trả về TRUE (đánh dấu) nếu tất cả ba đối số được cung cấp là đúng logic.
Cú pháp: AND (biểu thức_logic1, biểu_thức_logic2, biểu_thức_logic3)
-
len(A2) – A2 không rỗng (biểu_thức_logic1)
-
len(B2) – B2 không rỗng (biểu_thức_logic2)
-
Kiểm tra xem giá trị được bán trong vòng 30 ngày kể từ lần bán cuối cùng. Điều này đòi hỏi một giải thích chi tiết, và đây là giải thích chi tiết (biểu_thức_logic3).
A2=sortn( filter( {filter($A$2:$A,$B$2:$B=B2);"x"}, {filter($A$2:$A,$B$2:$B=B2);"x"}- {"x";filter($A$2:$A,$B$2:$B=B2)}<31 ) )Có bốn bộ lọc và một chức năng sắp xếp.
Vai trò của BỐN công thức FILTER trong công thức trên là gì?
Hình vẽ dưới đây có thể giúp.
Công thức trên trả về các ngày 25-02-2021 và 21-04-2021 (được làm nổi bật màu vàng trong hình vẽ trên).
Vai trò của SORTN ở đây là giới hạn đầu ra thành 1 ô, tức là 25-02-2021.
Quy tắc định dạng tùy chỉnh thực sự kiểm tra xem A2 có bằng 25-02-2021 trong hàng đầu tiên.
Vì đây là một quy tắc định dạng điều kiện, việc kiểm tra logic sẽ diễn ra trên mỗi hàng.
Các tham chiếu tương đối trong công thức, tức A2 và B2 sẽ trở thành A3, B3, A4, B4 và cứ tiếp tục như vậy trên mỗi hàng.
Làm thay đổi phạm vi nhập bản sao từ trong vòng 30 ngày thành 7 ngày như thế nào?
Vui lòng kiểm tra dấu “<31” trong công thức. Thay đổi nó thành “<8”.
Điều đó sẽ làm nổi bật các giá trị trùng lặp dựa trên 7 ngày xuất hiện trong Google Sheets.
Đánh dấu TẤT CẢ các giá trị trùng lặp dựa trên ngày xuất hiện ít hơn 30 ngày
Ở đây, bạn cần thực hiện hai ba thay đổi.
Tôi sẽ đến đó sau. Dưới đây là quy tắc công thức và đầu ra.
=and( len(A2), len(B2), regexmatch( row(A2)&"", "^"&textjoin("$|^",true, filter( {filter(row($A$2:$A),$B$2:$B=B2);"x"}, {filter($A$2:$A,$B$2:$B=B2);"x"}- {"x";filter($A$2:$A,$B$2:$B=B2)}<31) ) &"$" ) )
Trong ví dụ trước, tôi đã giải thích tại sao công thức làm nổi bật “Sách 1” trong ô B4.
Ở đây, công thức cũng làm nổi bật “Sách 1” trong ô B6.
Điều đó là vì số ngày giữa các ngày trong B6 và B5 cũng nhỏ hơn 30 ngày.
Tôi đã thực hiện một số thay đổi ở đây.
-
Loại bỏ chức năng Sortn giới hạn đầu ra thành một giá trị vì chúng ta muốn làm nổi bật tất cả các bản sao dựa trên ngày xuất hiện.
-
Trong bộ lọc bên ngoài, thay vì lọc ngày, tôi đã sử dụng hàm ROW để lọc các hàng.
-
Vì vậy, đầu ra sẽ không phải là 25-02-2021 và 21-04-2021 (vui lòng tham khảo hình ảnh #3); thay vào đó, số hàng tương ứng.
-
REGEXMATCH phù hợp với các số hàng và làm nổi bật các ô tương ứng.
Lưu ý: – TEXTJOIN kết hợp các số hàng trả về để tạo thành một biểu thức chính quy trong Regexmatch.
Đó là tất cả về cách đánh dấu các giá trị trùng lặp dựa trên ngày xuất hiện trong Google Sheets.
Cảm ơn vì đã đọc. Chúc bạn có những trải nghiệm thú vị!
- Đánh dấu các giá trị trùng lặp trong một cột, nhiều cột, tất cả các ô trong Google Sheets.
- Đánh dấu các bản sao tương phần trong Google Sheets.
- Bỏ qua các bản sao trong việc làm nổi bật hàng theo hàng trong Google Sheets.
- Đánh dấu các bản sao hiển thị trong Google Sheets.
- Đánh dấu giá trị lớn nhất mà không có bản sao trong hàng theo hàng trong Google Sheets.
