Nhiều người dùng không biết cách kiểm tra xem một dòng có được ẩn hay hiển thị trong Google Sheets. Câu hỏi tiếp theo là làm thế nào để làm nổi bật dòng phía trên dòng bị ẩn.
Mà không biết điều này (tìm dòng bị ẩn), không thể viết các công thức chỉ bao gồm các dòng hiển thị hoặc ẩn trong một phạm vi.
Trong bài viết này, có hai mẹo – một mẹo công thức và một mẹo định dạng có điều kiện. Hãy bắt đầu với mẹo đầu tiên.
Làm thế nào để Kiểm Tra Dòng Có Được Ẩn hay Hiển Thị trong Google Sheets
Để kiểm tra xem một dòng có được ẩn hay hiển thị, chúng ta có thể sử dụng công thức SUBTOTAL trong Google Sheets.
Lưu ý, theo kiến thức của tôi, không có công thức / hàm nào để tìm một cột bị ẩn trong Google Sheets.
Vui lòng không lãng phí thời gian của bạn bằng cách thử với các hàm khác như CELL để tìm chiều rộng của một ô trong một cột bị ẩn vì tôi thấy việc sử dụng như vậy không thành công. Nếu bạn quen với Google Apps Script thì có thể tìm được các giải pháp.
Làm thế nào để Kiểm Tra Một Dòng Bị Ẩn Trong Google Sheets
Để kiểm tra một dòng bị ẩn, bạn có thể xem xét bất kỳ ô không trống nào trong dòng đó.
Nếu một dòng hoàn toàn trống thì bạn không thể kiểm tra xem dòng đó có bị ẩn hay hiển thị trong Google Sheets. Ngoài ra, ô mà bạn xem xét cho kiểm tra dòng bị ẩn không được chứa bất kỳ công thức SUBTOTAL nào.
Ví dụ về Cách Kiểm Tra Một Dòng Bị Ẩn Trong Google Sheets:
Hãy cùng xem một ví dụ! Tôi sẽ giải thích về vấn đề / tình huống bằng một ví dụ dễ hiểu.
Giả sử, bạn muốn kiểm tra xem ô A5 (dòng số 5) có bị ẩn hay không trong một sheet. Sau đó, điều bạn có thể muốn là chèn công thức SUBTOTAL dưới đây vào bất kỳ dòng nào khác.
=subtotal(103,A5)Như tôi đã đề cập ở đầu, ô A5 phải chứa bất kỳ ký tự nào và không được chứa công thức SUBTOTAL nào khác.
Nếu dòng số 5 bị ẩn, kết quả của công thức trên sẽ là 0. Để hiển thị một thông báo tùy chỉnh như “ẩn” hoặc “không ẩn”, sử dụng công thức IF (nếu như vậy, thì) với SUBTOTAL.
=if(subtotal(103,A5)=0,"ẩn","không ẩn")Cách này cho phép bạn kiểm tra xem một dòng có bị ẩn hay hiển thị trong Google Sheets. Dưới đây là mẹo thứ hai.
Làm thế nào để Làm Nổi Bật Các Dòng phía trên Dòng Bị Ẩn trong Google Sheets
Việc này chỉ hoạt động nếu bạn có một cột mà không có ô trống trong phạm vi dữ liệu. Nếu có các ô trống, hãy chèn ký tự gạch ngang hoặc số 0 vào các ô trống đó.
Ví dụ, phạm vi dữ liệu của tôi là A1:A17. Tôi có thể bao gồm phạm vi A1:A1000 hoặc A1:Z1000 để áp dụng định dạng có điều kiện. Nhưng A1:A17 không được để trống. Hy vọng, hình ảnh dưới đây sẽ truyền tải tốt hơn.
Đối với những người muốn giữ các ô trống, còn một lựa chọn nữa!
Sử dụng một cột trống phụ (chúng ta có thể gọi là cột trợ giúp). Trong ô đầu tiên trong cột đó, nhập công thức SEQUENCE vào.
=sequence(rows(A:A),1)Sau đó, ẩn cột đó. Sử dụng cột đó trong các công thức thay vì phạm vi “thực tế”.
Có nghĩa là, vì không có ô trống, tôi đang sử dụng cột A trong các công thức của mình ở dưới. Giả sử có ô trống trong cột A.
Sau đó, nếu công thức chuỗi trên nằm trong ô D1, điều đó có nghĩa là cột D là cột trợ giúp, hãy sử dụng cột D trong các công thức của bạn thay vì cột A. Hãy bắt đầu.
Làm thế nào để biết, mà không sử dụng công thức, có các dòng bị ẩn trong một sheet?
Nhìn vào nút hiển thị (mũi tên lên đen ▲ và mũi tên xuống đen ▼) hoặc kiểm tra số dòng, đúng không?
Nếu bạn muốn, bạn có thể làm nổi bật dòng phía trên dòng bị ẩn để dễ dàng tìm / xác định các dòng bị ẩn.
Trước tiên, tôi sẽ cho bạn xem kết quả sẽ trông như thế nào. Nếu bạn có cảm thấy thú vị với định dạng có điều kiện như vậy, chỉ khi đó bạn tiếp tục.
Do các dòng 5, 9, 10 và 15 bị ẩn, các dòng ngay phía trên (tức là dòng 4, 8 và 14) được làm nổi bật.
Bằng cách làm nổi bật như vậy, mà không cần kiểm tra số dòng hoặc các mũi tên trỏ lên và xuống đen, bạn có thể xác định các dòng (vị trí) bị ẩn trong toàn bộ sheet của bạn.
Công Thức Tuỳ Chỉnh để Làm Nổi Bật
Để làm nổi bật các dòng phía trên dòng bị ẩn, chúng ta có thể sử dụng ba công thức tuỳ chỉnh trong Định dạng có điều kiện trong Google Sheets. Chúng là như sau.
Công Thức (Qui Tắc) # 1:
Màu nền là ‘None’.
=$A1=""Công Thức (Qui Tắc) # 2:
Màu nền là ‘None’.
=row($A1)=ArrayFormula(MATCH(2,1/($A:$A<>" "),1))Công Thức (Qui Tắc) # 3:
Sử dụng màu do bạn chọn khác với none/trắng.
=subtotal(103,$A2)=0Trong trình đơn Định dạng có điều kiện (Format > Conditional formatting > Format rules > Custom formula is), chèn các công thức trên theo thứ tự công thức giống như trình bày bên dưới.
Giải Thích Các Qui Tắc (Công Thức)
Qui Tắc # 1 dùng để loại bỏ bất kỳ sự làm nổi bật nào trong các dòng trống trong phạm vi.
Ví dụ, xem dữ liệu của tôi. Nó ở trong phạm vi A1:A17. Tôi đang áp dụng qui tắc của mình cho toàn bộ sheet (A1:Z1000). Vì vậy các dòng từ 18 trở xuống đều trống. Qui tắc này sẽ loại bỏ bất kỳ sự làm nổi bật nào phía dưới dòng thứ 17.
Mục đích của Qui tắc # 2 là tìm kiếm ô cuối cùng không trống trong cột A (nó là dòng số 17) và loại bỏ bất kỳ sự làm nổi bật nào trong dòng đó. Tại sao cần qui tắc này? Câu trả lời là qui tắc # 3.
Qui tắc # 3 kiểm tra xem một dòng có bị ẩn hay không và làm nổi bật dòng phía trên dòng bị ẩn.
Thông thường qui tắc định dạng có điều kiện sẽ áp dụng cho dòng được tham chiếu trong qui tắc công thức. Chúng tôi muốn bỏ qua việc làm nổi bật lên một dòng. Nghĩa là kiểm tra một ô và làm nổi bật ô phía trên nó.
Đây là mẹo. Tôi đã chọn phạm vi A1:Z1000 (áp dụng cho phạm vi) trong định dạng có điều kiện. Công thức qui tắc # 3 kiểm tra xem ô (dòng) có bị ẩn hay không từ dòng số 2 trở xuống.
Vì ‘áp dụng cho phạm vi’ (A1:Z1000) bắt đầu từ A1 (dòng số 1 trở xuống) và công thức qui tắc bắt đầu từ A2 (dòng số 2 trở xuống), thì việc làm nổi bật liên quan đến dòng số 2 sẽ áp dụng cho dòng số 1. Cách đó sẽ lặp lại cho các dòng tiếp theo.
Điều này gây ra vấn đề khi chúng ta đến dòng cuối cùng không trống, đó là dòng số 17. Vấn đề đó là gì?
Công thức qui tắc # 3 sẽ trả về 0 trong một ô không trống khác ngoài một hàng ẩn. A18 đang trống nên công thức sẽ trả về 0 và ‘sai một cách’ xem hàng # 18 là hàng trống và cuối cùng làm nổi bật hàng # 17.
Tôi đã sử dụng qui tắc thứ hai để loại bỏ sự làm nổi bật không mong muốn này trong hàng cuối cùng không trống.
Các Hàm Phổ Biến Của Google Sheets Trong Các Dòng Được Ẩn / Hiển Thị
- Google Sheets Query Hidden Row Handling with Virtual Helper Column
- SUMIF Excluding Hidden Rows in Google Sheets [Without Helper Column]
- How to Omit Hidden or Filtered out Values in Sum [Google Doc Spreadsheet]
- Vlookup Skips Hidden Rows in Google Sheets – Formula Example
- Find the Average of Visible Rows in Google Sheets
- Count Unique Values in Visible Rows in Google Sheets
- Highlight Visible Duplicates in Google Sheets
- IMPORTRANGE to Import Visible Rows in Google Sheets
Hy vọng bài viết này đã giúp bạn hiểu cách kiểm tra xem một dòng có bị ẩn hay không và làm nổi bật các dòng phía trên dòng bị ẩn trong Google Sheets. Đừng quên ghé thăm ‘Crawlan’ để tìm hiểu thêm về SEO và các mẹo về cải thiện thứ hạng trang web của bạn.
