Chúng ta có thể áp dụng ba loại quy tắc đánh dấu cho các bản sao. Nếu không hiểu được điều đó, thì việc tiếp tục với cách đánh dấu những bản sao hiển thị trong Google Sheets không có ý nghĩa.
Những cái ba loại quy tắc định dạng đó là gì?
Bản sao có nghĩa là giá trị xuất hiện nhiều lần. Vì vậy sẽ có một lần xuất hiện đầu tiên, một lần xuất hiện thứ hai, một lần xuất hiện thứ ba và tương tự.
Khi bạn tìm thấy một bản sao lặp lại ba lần, điều bạn thường muốn làm với định dạng có điều kiện?
Bạn thích tô đậm ba lần xuất hiện, hai lần xuất hiện cuối cùng hoặc hai lần xuất hiện đầu tiên?
Xem hình ảnh dưới đây, trong đó tôi đã áp dụng các quy tắc định dạng có điều kiện khác nhau.
Tôi đã giải thích những loại định dạng có điều kiện này trong hướng dẫn của tôi – Đánh dấu bản sao trong một cột, nhiều cột và tất cả các ô trong Google Sheets.
Tôi hy vọng bạn đã đọc qua nó. Nếu chưa, hãy đọc hướng dẫn đó trước, dù không bắt buộc ở đây.
Bản sao hiển thị trong Google Docs Sheets – Điều gì đó?
Giả sử ô A1 và ô A2 chứa số 100, vì vậy hai giá trị trùng lặp. Tôi ẩn hoặc lọc bỏ hàng số 1 (ô A1).
Sau đó, chỉ còn một lần xuất hiện của giá trị 100 (trong ô A2). Vì vậy nó không phải là một bản sao xem xét những bản sao hiển thị.
Trong các ví dụ sau, bạn có thể tìm hiểu cách loại bỏ các giá trị trong các hàng bị ẩn/lọc ra khỏi việc tìm kiếm các bản sao và đánh dấu chúng.
Cách đánh dấu những bản sao hiển thị trong Docs Sheets
Chúng tôi phải sử dụng một cột trợ giúp trong Docs Sheets để loại trừ các hàng bị ẩn trong các công thức có điều kiện hoặc tiêu chuẩn. Vì vậy, hãy để tôi bắt đầu từ đó.
Trong ví dụ đầu tiên, chúng tôi sẽ sử dụng đánh dấu bản sao loại 3, tức là đánh dấu tất cả các lần xuất hiện của bản sao.
Đánh dấu tất cả các lần xuất hiện của bản sao hiển thị trong Google Sheets
Dưới đây là ảnh chụp màn hình. Trong đó, hãy xem công thức trong ô B1, mà tôi đã kéo xuống cho đến ô B9.
=subtotal(103,A1)
Hoặc, làm trống B1:B9 và chèn công thức sau vào ô B1, trong đó MAP mở rộng SUBTOTAL.
=map(A1:A9,lambda(row,subtotal(103,row)))
Công thức Subtotal này làm gì?
Khi một hàng bị ẩn, giá trị trong ô đó, có nghĩa là ô được áp dụng công thức Subtotal, trở thành 0 (không)!
Để kiểm tra tính năng Subtotal này, hãy sử dụng công thức dưới đây trong ô D5.
=B3
Công thức Subtotal trả về 1 trong ô D5. Xem giá trị trong ô D5 thay đổi thành 0 khi tôi ẩn hàng số 3.
Tôi sẽ tận dụng tính năng này của công thức Subtotal để chỉ đánh dấu các bản sao hiển thị trong Google Sheets.
Dưới đây là công thức tùy chỉnh để định dạng có điều kiện những bản sao hiển thị trong bảng tính Google.
=countifs($A$1:$A$25,$A1,$B$1:$B$25,$B1)>1
Tôi áp dụng quy tắc này cho đến hàng # 25. Vì vậy, sao chép công thức Subtotal trong ô B1 lên đến hàng này.
Nếu bạn đang sử dụng công thức Map, hãy thay thế A1:A9 bằng A1:A25.
Để áp dụng công thức này trong định dạng có điều kiện, thực hiện theo các bước sau.
- Đến menu Format > Conditional Formatting.
- Đặt “Apply to range” thành A1:A25.
- Chọn “Custom Formula” dưới “Format rules” và nhập công thức trên.
Nếu bạn tham khảo hình ảnh trên (hình ảnh này chứa ba hình ảnh, tôi đang nói về hình ảnh nằm ở bên trái nhất), bạn có thể thấy ba lần xuất hiện của giá trị Mận (Orange).
Khi tôi ẩn hàng số 7, nó xuất hiện hai lần. Vì vậy lại là một bản sao.
Nhưng khi tôi ẩn các hàng 6 và 7, chỉ còn một Mận được hiển thị.
Mặc dù nó có bản sao trong các hàng bị ẩn, nhưng đánh dấu không được áp dụng.
Lý do là không có bản sao hiển thị của giá trị này.
Đánh dấu tất cả những bản sao hiển thị, loại bỏ lần xuất hiện đầu tiên
Ban đầu, tôi đã đề cập đến ba loại định dạng.
Đây là loại thứ hai, trong đó, tôi đánh dấu tất cả các bản sao, loại bỏ lần xuất hiện đầu tiên.
Mọi thiết lập đều giống như ví dụ trên, như sử dụng công thức Subtotal/Map và áp dụng quy tắc định dạng có điều kiện.
Nhưng công thức định dạng có điều kiện tùy chỉnh khác nhau.
Dưới đây là công thức để sử dụng trong trường hợp này.
=countifs($A$1:$A1,A1,$B$1:$B1,B1)>1
Trên đây là công thức loại 2 để đánh dấu những bản sao hiển thị trong Google Sheets.
Hãy xem ảnh chụp màn hình sau để hiểu cách điều này khác với quy tắc trước đó.
Đánh dấu tất cả những bản sao hiển thị, loại bỏ lần xuất hiện cuối cùng
Nó tương tự như quy tắc đánh dấu ở trên ngay.
Ở đây cũng như trên, chúng ta loại bỏ các giá trị trong các hàng bị ẩn bằng cách sử dụng cột trợ giúp và Công thức Subtotal.
Ở đây, sự khác biệt duy nhất so với quy tắc định dạng trên, đó là tôi đánh dấu tất cả các bản sao, loại bỏ lần xuất hiện cuối cùng (loại 1).
=countifs(A1:A25,A1,B1:B25,B1)>1
Một lần nữa, tôi sẽ sử dụng các ảnh chụp màn hình để giúp bạn hiểu điều này.
Đó là tất cả. Bạn có thể làm theo ba loại trên để đánh dấu những bản sao hiển thị trong Google Sheets. Thưởng thức nào!
Thêm mẹo định dạng có điều kiện:
- Luật định dạng có điều kiện liên quan đến ngày trong Google Sheets.
- Tìm tất cả các ô có Định dạng có điều kiện trong Google Sheets.
- AND, OR hoặc NOT trong Định dạng có điều kiện trong Google Sheets.
- Định dạng có điều kiện dựa trên nhóm dữ liệu trong Google Sheets.
