跳到主要內容

如何在Excel中過濾帶有註釋的單元格?

在工作表中插入註釋可以標記一些重要的信息或細節,這對我們很常見。 但是,在某些情況下,我們只想過濾包含註釋的行並將它們放在一起以便於查看。 Excel中的“篩選器”功能無法用於我們直接解決此工作。 在本文中,您將學習如何在Excel中過濾帶有註釋的單元格。

通過創建助手列來過濾帶有註釋的單元格


箭頭藍色右氣泡 通過創建助手列來過濾帶有註釋的單元格

要僅過濾註釋的行,可以使用以下命令標識註釋單元格 用戶自定義函數 首先,然後應用 篩選 的功能。

1。 按住 Alt + F11鍵 鍵,然後打開 Microsoft Visual Basic for Applications窗口.

2. 點擊 插入 > 模塊,然後將以下宏粘貼到 模塊窗口.

VBA代碼:確定包含註釋的單元格

Function HasComment(r As Range)
'Update 20140718
    Application.Volatile True
    HasComment = Not r.Comment Is Nothing
End Function

3。 然後保存代碼並關閉 模塊 窗口,然後返回工作表,輸入此公式 =有評論(B2) 放入評論單元格旁邊的空白單元格,(B2 包含您要使用的值),請參見屏幕截圖:

文檔過濾器註釋 1

4。 然後將填充手柄拖到要應用此公式的範圍單元格,您將獲得 TRUE or 在牢房裡 TRUE 代表具有評論和 表示沒有評論。

5。 確定註釋單元格後,選擇數據范圍,然後單擊 數據 > 篩選,請參見屏幕截圖:

文檔過濾器註釋 1

6。 然後單擊幫助器列旁邊的下拉箭頭,檢查 TRUE 僅選項,請參見屏幕截圖:

文檔過濾器註釋 1

7。 並點擊 OK 按鈕,然後註釋行被過濾掉,如下面的屏幕截圖所示:

文檔過濾器註釋 1

8。 最後,您可以根據需要刪除C列的內容。

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!

 

Comments (10)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
I am using Excel 365 and tried using Notes instead of Comments and I get #NAME?. When I try comments, I get all False. I confirmed that my cells have Comments and not Notes in the cells. What am I doing wrong?
This comment was minimized by the moderator on the site
Hello, Annie
In Excel 365, you can see the note is shown as below screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-comment-note-1.png

And the comment is shown as this:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-comment-note-2.png

So, if there are notes in your worksheet, you just use the code in this article and don't change any thing, then apply the formula you will get the correct results as below:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-comment-note-3.png

Please try, hope it can help you!
This comment was minimized by the moderator on the site
And how to do it with COMMENTS? Not NOTES.

Johnny
This comment was minimized by the moderator on the site
Me sale todo falso a que se debe
This comment was minimized by the moderator on the site
Hello Marcela,

Glad to help. May I know whether the Excel version you are using is Microsoft Excel 365, which can be different from other versions of Excel?

If it is Microsoft Excel 365, then I know what leads to the problem. In Microsoft Excel 365, Notes replaced the Comments in other versions of Excel. So change your comments to notes, then the returned values will be TRUEs. Please have a try. Looking forward to your reply.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Hi, so how to do it with COMMENTS? Without changing them to notes.

Thx,
johnny.
This comment was minimized by the moderator on the site
Maravilhoso!!!!!! Obrigado!!!!
This comment was minimized by the moderator on the site
Gracias por este aporte, muy recomendado esta página.
This comment was minimized by the moderator on the site
I would like to know how does the "HasComment = Not r.Comment Is Nothing" part works. Thanks!
This comment was minimized by the moderator on the site
Amazing solution to a problem. Loved it.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations