Đôi khi, trong một bộ dữ liệu lớn trong Google Sheets, bạn có thể muốn làm nổi bật các giá trị mới nhất để tập trung vào những bản ghi cụ thể đó.
Điều này có thể trở thành một yêu cầu bắt buộc nếu bạn thường xắp xếp dữ liệu của mình dựa trên bất kỳ cột nào khác ngoài cột ngày trong phạm vi.
Việc sắp xếp như vậy làm cho tập dữ liệu bị xáo lộn theo cột ngày và khó khăn cho bạn trong việc xác định các hàng mới nhất.
Dù bạn sắp xếp dữ liệu hay không, làm nổi bật N giá trị mới nhất là cách lý tưởng để tập trung vào chúng.
Trong Google Sheets, để giải quyết các vấn đề cụ thể, có thể có một số kết hợp công thức hoạt động. Điều đó cũng áp dụng ở đây.
Ở đây, tôi sử dụng một kết hợp của các hàm Filter, Sort và Index trong trường công thức tùy chỉnh trong định dạng có điều kiện. Hãy xem cách làm ở dưới đây.
Công thức để tô sáng các giá trị mới nhất trong Google Sheets
Chúng ta có dữ liệu trong A1:B, trong đó cột A chứa ngày và cột B chứa số.
Chúng ta cần làm nổi bật N giá trị mới nhất trong cột B dựa trên các ngày tương ứng trong cột A.
Hãy bắt đầu với công thức (quy tắc tô sáng) và cách sử dụng nó trong định dạng có điều kiện.
Công thức:
=and(B2>0,A2>=index(sort(filter($A$2:$A,$B$2:$B>0),1,0),N))
Lưu ý: Thay thế N trong công thức trên bằng 5, 10, 20, 50, 100 hoặc số bản ghi gần đây bạn muốn làm nổi bật.
Ví dụ, nếu N = 10, công thức sẽ làm nổi bật mười giá trị mới nhất trong Google Sheets.
Thêm quy tắc:
Để sử dụng quy tắc trên để làm nổi bật, làm như sau.
Chọn B2:B, phạm vi mà chúng ta muốn áp dụng màu nền thông qua định dạng có điều kiện.
Sau đó, vào menu Format > Conditional formatting và sửa “Apply to range” nếu không phải là B2:B1000.
Sau đó, chọn “Custom formula is” và nhập quy tắc trên (công thức).
Nhấp vào “Done”, và xong!
Giải thích công thức
Như bạn có thể biết, để hiểu một công thức, chúng ta phải tập trung vào phần trung tâm của nó. Ở đây đó là hàm FILTER.
Bước 1:
=filter($A$2:$A,$B$2:$B>0)
Nó lọc ra các hàng không có giá trị trong cột B.
Cách này, chúng ta có thể đảm bảo rằng phạm vi các giá trị n mới nhất không có các ô trống.
Bước 2:
=sort(step_1,1,0)
Sắp xếp dữ liệu để di chuyển các bản ghi mới nhất lên đầu trang.
Bước 3:
=index(step_2,N)
Hàm INDEX ở đây tiến lùi N hàng và trả về ngày từ ô tương ứng.
Giả sử N = 10. Hãy nhớ rằng các bản ghi mới nhất hiện đã nằm trên đầu (step_2).
Trong bước 3, chúng ta muốn công thức trả về ngày trong hàng thứ 10 từ đầu.
Nếu đầu ra bước 3 là 19/06/2022, tất cả các ngày lớn hơn hoặc bằng nó sẽ thuộc phạm vi n bản ghi mới nhất.
Bước 4:
=and(B2>0,A2>=step_3)
Quan trọng:
Nếu bạn có các bản sao của ngày thứ n, công thức sẽ tô sáng chúng cũng (điều này cũng áp dụng cho Truy vấn dưới tiêu đề tiếp theo). Vì vậy, đôi khi bạn sẽ nhìn thấy n+ bản ghi có màu nền được áp dụng.
Nếu bạn muốn, bạn có thể sử dụng công thức SORTN dưới đây để trích xuất các bản ghi đó.
=sortn(filter(A2:B,B2:B<>""),n,1,1,0)
Truy vấn trong việc làm nổi bật N giá trị mới nhất – Một số gợi ý bổ sung
Thực tế, trong quy tắc trên, chúng ta có thể thay thế combo Filter, Sort, và Index (bước 3) bằng một công thức TRUY VẤN.
Bởi vì chúng ta có thể giải quyết cốt lõi của vấn đề làm nổi bật giá trị mới nhất đơn giản chỉ bằng cách sử dụng truy vấn vì nó có tất cả các mệnh đề cần thiết để lọc, sắp xếp và lùi bản ghi trong tập dữ liệu.
Chúng ta đã sử dụng combo (bước 3) trên để lọc ra các ô trống, sắp xếp dữ liệu dựa trên ngày theo thứ tự Z-A và cuối cùng lùi N hàng.
Truy vấn dưới đây sẽ giải quyết tất cả!
=query($A$2:$B,"Select A where B is not null order by A desc limit 1 offset N-1")
Quan trọng: Nếu N = 10, thay thế N-1 bằng 9.
Hãy xem công thức dưới đây.
Để làm nổi bật N giá trị mới nhất trong Google Sheets, ngoài công thức trước đó, chúng ta cũng có thể sử dụng công thức dựa trên TRUY VẤN dưới đây trong định dạng có điều kiện.
=and(B2>0,A2>=query($A$2:$B,"Select A where B is not null order by A desc limit 1 offset N-1"))
Đó là tất cả. Cảm ơn vì đã đọc bài viết này. Chúc bạn có những trải nghiệm thú vị!
