·
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?
如何根據Google表格中的單元格顏色過濾行？
通常情況下，我們可以使用Filter功能在Excel中快速篩選特定顏色的行，但在Google工作表中，Filter功能不支持此操作。 本文將討論如何在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天免費試用。
Office選項卡為Office提供選項卡式界面，使您的工作更輕鬆
- 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，Publisher，Access，Visio和Project。
- 在同一窗口的新選項卡中打開並創建多個文檔，而不是在新窗口中。
- 通過50％提高您的工作效率，每天為您減少數百次鼠標點擊！
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.
-
To post as a guest, your comment is unpublished.
· 19 days agoIt 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.
· 22 days agoty it works
-
To post as a guest, your comment is unpublished.
· 2 months agoThanks a lot! It helped.
-
To post as a guest, your comment is unpublished.
· 3 months agoSuperb! very useful! Thanks a million! Cheers!
-
To post as a guest, your comment is unpublished.
· 3 months agoThank you. Awesome -
To post as a guest, your comment is unpublished.
· 6 months agothere's a question that it kept showing "Range not found",how can i fix it
-
To post as a guest, your comment is unpublished.
· 7 months agoAwesome job, it works exactly like the explanation.Thank you
-
To post as a guest, your comment is unpublished.
· 7 months agoGreat 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.
· 8 months agoI 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.
· 9 months agoThis 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.
· 9 months agoThis 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.
· 9 months agoi want use it, but dont working :-/
-
To post as a guest, your comment is unpublished.
· 10 months agoJust 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.
· 10 months agoHi, 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.
· 1 years agoTop, Danke!
English
العربية
简体中文
繁體中文
Nederlands
Français
Deutsch
Italiano
日本語
한국어
Português
Русский
Español