提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何根據Google表格中的單元格顏色過濾行?

通常情況下,我們可以使用Filter功能在Excel中快速篩選特定顏色的行,但在Google工作表中,Filter功能不支持此操作。 本文將討論如何在Google工作表中按顏色過濾行。

根據Google表格中的顏色過濾行


根據Google表格中的顏色過濾行


請應用以下步驟根據顏色過濾數據。

1。 點擊 工具 > 腳本編輯器,看截圖:

2。 在打開的項目窗口中,請複制並粘貼以下腳本代碼以替換代碼模塊中的原始代碼,請參見截圖:

function getHex(input) {
  return SpreadsheetApp.getActiveSpreadsheet().getRange(input).getBackgrounds();
}

3。 然後保存代碼窗口,並彈出一個提示框提醒您為該項目命名,請參見截圖:

4。 保存腳本代碼後,返回表單並輸入以下公式: = getHex(“A2:A16”) 放到數據范圍旁邊的空白單元格中,然後按 輸入 鍵,每行的顏色索引已顯示在助手列中,請參見截圖:

5。 然後,您只需根據此幫助程序列篩選行,請單擊幫助程序列標題,然後單擊 數據 > 過濾,看截圖:

6。 然後單擊助手列中的下拉按鈕,在展開窗格中選擇並檢查要從列錶框中過濾的顏色索引,請參閱截圖:

7。 點擊 OK 按鈕,填充特定單元格顏色的行將按照以下屏幕截圖進行過濾:


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Tony · 7 days ago
    What if only certain cells in a row were colored? How can I use this method to notate which ROWS contain a particular color?
  • To post as a guest, your comment is unpublished.
    Kerstin · 19 days ago
    It worked for me, but not for alle colors. I don't know why.
    But anyway: Thank a lot!
  • To post as a guest, your comment is unpublished.
    emr · 22 days ago
    ty it works
  • To post as a guest, your comment is unpublished.
    Ankit · 2 months ago
    Thanks a lot! It helped.
  • To post as a guest, your comment is unpublished.
    David Cardoso · 3 months ago
    Superb! very useful! Thanks a million! Cheers!
  • To post as a guest, your comment is unpublished.
    Volkan Yılmaz · 3 months ago
    Thank you. Awesome
  • To post as a guest, your comment is unpublished.
    ian · 6 months ago
    there's a question that it kept showing "Range not found",how can i fix it
  • To post as a guest, your comment is unpublished.
    Henrique · 7 months ago
    Awesome job, it works exactly like the explanation.Thank you
  • To post as a guest, your comment is unpublished.
    Bob Thomas · 7 months ago
    Great workaround! I tried repeatedly to highlight duplicates in an Excel spreadsheet then sort by cell color but since the file was so large (373K + rows) the sort would "hang", and never complete. I decided to try Google Sheets and although I could only format about 100K rows at a time (had to do a format 4x) your workaround worked perfectly. Plus, scrolling down the page with Google Sheets was infinitely faster than with Excel. Thanks for the great hack!
  • To post as a guest, your comment is unpublished.
    Ramón · 8 months ago
    I understand that the topic is about a script for such a need. But a simpler option can be to handle conventions by color. Example: Green color indicates CLIENT, Color Orange. INTERESTED, Color Yellow indicates NOT INTERESTED .. Then add a column status and then filter for the desired state.
  • To post as a guest, your comment is unpublished.
    Tarek Allam · 9 months ago
    This is a great script it really helped me solve a problem, however I modified it a bit because my range is not fixed, I keep adding rows to the sheet and I don't want to modify the formula everytime I add a new row, so I used the following formula instead:

    =ARRAYFORMULA(IF(NOT(ISBLANK(A2:A)),getHex("A2:A"),""))

    The problem is when I change the color of a column the hex value does not change even if I refresh the sheet, the only solution I found is to delete the formula then paste it again so it loads again, anyone could help with a solution to this?
  • To post as a guest, your comment is unpublished.
    Tarek · 9 months ago
    This is a great script it really helped me solve a problem, however I modified it a bit because my range is not fixed, I keep adding rows to the sheet and I don't want to modify the formula everytime I add a new row, so I used the following formula instead:

    =ARRAYFORMULA(IF(NOT(ISBLANK(A2:A)),getHex("A2:A"),""))

    The problem is when I change the color of a column the hex value does not change even if I refresh the sheet, the only solution I found is to delete the formula then paste it again so it loads again, anyone could help with a solution to this?
  • To post as a guest, your comment is unpublished.
    Filip · 9 months ago
    i want use it, but dont working :-/
  • To post as a guest, your comment is unpublished.
    Polynon · 10 months ago
    Just a note if this isn't working for you, the quotation marks used in the example above don't parse a formula correctly and give you a "Formula Parse Error".

    The correct one is:

    =getHex("A2:A16")

    rather than:

    =getHex(“A2:A16”)
    • To post as a guest, your comment is unpublished.
      skyyang · 10 months ago
      Hi, Polynon,
      Thank you for your comment, the double quote around the cell reference should be English punctuation.
      I have updated it.
  • To post as a guest, your comment is unpublished.
    Lydia · 1 years ago
    Top, Danke!