2020 年,Google为(Google)Google 表格(Google Sheets)添加了最有用的功能之一;按颜色排序或过滤的能力。
这是用户在Microsoft Excel中能够做到的事情已经有一段时间了。在Google 表格中提供它意味着将(Sheets)Excel表格迁移到在线电子表格更加容易。
如果您想知道为什么要按颜色排序或过滤,请考虑设置基于条件的格式的场景。通过这种方式排序,您可以按类别或优先级而不是基于单个数字对警报或排名进行排序。
在 Google 表格中按颜色排序
让我们看一个电子表格,其中包含涵盖各种范围的学生成绩列表。在此电子表格中,我们配置了基于条件的格式,以便为每个等级范围分配适当的颜色代码。
使用以这种方式格式化(formatted in this way)的工作表,如果您按 B 列排序,您将看到从 A 到 B 排序的成绩,但您不会清楚地看到哪些成绩块构成每个字母成绩组。
最好的方法是按颜色排序。
去做这个:
1.从菜单中选择数据,选择(Data)过滤视图(Filter views),然后选择创建新过滤视图(Create new filter view)。
2. 要对所有“A”等级在顶部的列表进行排序,您可以按绿色(Green)排序。选择列顶部的过滤器图标,选择按颜色排序(Sort by color),选择填充颜色(Fill Color),然后选择要作为排序依据的颜色。在这种情况下,请选择浅绿色。
3.您选择的颜色将在列表顶部进行分组和排序。
您可能会注意到您不能按所有颜色排序,这是有道理的,因为颜色没有排序依据。
但是,如果您按数字对列进行排序,颜色也将根据其数字范围进行组织。Google Sheets中按颜色排序的要点是根据单元格格式(通常基于条件格式(conditional formatting))快速识别大列表中的单个项目组。
在 Google 表格中按颜色过滤
另一种按颜色对项目进行分组的方法是使用过滤功能。Google 表格(Sheets)现在可让您过滤掉除您感兴趣的颜色之外的所有其他颜色。
在较大的列表中,这对于将您感兴趣的所有项目分组同时消除所有其他数据的混乱非常有用。
为此,请选择列顶部的过滤器图标,选择按颜色过滤(Filter by color),选择填充颜色(Fill Color),然后选择要作为过滤依据的颜色。在这种情况下为浅绿色。
按一种颜色过滤后,您将看到您过滤的列是您选择显示的颜色的所有行。具有其他颜色的所有其他行都将从视图中过滤掉。
这是一种专注于数据组的有用方法,可以消除可能妨碍分析的所有其他内容。
在Google 表格(Google Sheets)中按颜色排序或过滤比单独按数字排序的粒度更小,但有时像这样在类别或组中可视化数据更有价值。
在 Google 表格中按文本颜色排序
就像您可以在Google 表格(Google Sheets)中对单元格颜色进行排序或过滤一样,您也可以根据文本颜色(text color)进行相同的操作。这对上述所有原因都很有用,但您已经为文本颜色而不是单元格颜色创建了条件格式。
按文本颜色排序:
1.从菜单中选择数据,选择(Data)过滤视图(Filter views),然后选择创建新过滤视图(Create new filter view)。
2. 要对所有“A”等级在顶部的列表进行排序,您可以按绿色文本排序。选择列顶部的过滤器图标,选择按颜色排序(Sort by color),选择文本颜色(Text Color),然后选择要作为排序依据的颜色。在这种情况下是深绿色。
3.您选择的颜色将在列表顶部进行分组和排序。
就像填充排序一样,您选择的文本颜色会将具有该文本颜色的所有项目分组在列表顶部。该组下的所有项目均未排序。
同样,这是专注于特定数据组或数据类别的好方法。但是,如果您真的想按数字顺序排序,则需要像往常一样按单元格内容(数字)进行排序。
在Google表格中按文本颜色(Text Color)过滤
您还可以过滤掉那些其他颜色的记录,只留下您想要的颜色的组。同样(Again),这对于非常长的列表很有用,您希望在其中过滤掉不在您正在寻找的范围或类别中的所有其他值。
按文本颜色过滤:
1.从菜单中选择数据,选择(Data)过滤视图(Filter views),然后选择创建新过滤视图(Create new filter view)。
2. 要过滤列表以便仅在顶部列出“A”等级,您可以按绿色文本进行过滤。选择列顶部的过滤器图标,选择按颜色过滤(Filter by color),选择文本颜色(Text Color),然后选择要作为排序依据的颜色。在这种情况下,请选择深绿色。
3.您选择的颜色将被分组并列在顶部,而不会显示任何其他文本颜色。
这对于需要过滤掉“噪音”(所有其他数据)的非常长的列表特别有用,因此您可以只关注您关心的数据。
在Google表格中按颜色(Color)排序或过滤
在Google 表格(Google Sheets)中使用基于颜色的排序或过滤功能,您可以对数据进行分组和组织。它不像通常的排序或过滤,因为它不是按每行组织数据,而是按行块组织数据。这是对块中的信息进行分类和组织的好方法。
只要您有条件格式化的数据,如果您正确使用它,它就有它的用途。
How to Sort or Filter by Color in Google Sheets
In 2020, Google added one of the most useful featureѕ to Google Sheets; the ability to sort or filtеr by color.
This is something users have been able to do in Microsoft Excel for some time. Having it available in Google Sheets means it’s even easier to migrate Excel sheets to an online spreadsheet.
If you’re wondering why you may ever want to sort or filter by color, consider the scenario where you’ve set up condition-based formatting. Sorting in this way lets you do things like sort alarms or rankings by category or priority rather than based on individual numbers.
Sort by Color in Google Sheets
Let’s look at a spreadsheet with a list of student grades that cover various ranges. In this spreadsheet we’ve configured condition-based formatting to assign the appropriate color code for each grade range.
With a sheet that’s formatted in this way, if you sort by column B you’ll see the grades sorted from A to B, but you won’t clearly see which blocks of grades make up each letter grade group.
The best way to do this is sorting by color.
To do this:
1. Select Data from the menu, select Filter views, then select Create new filter view.
2. To sort the list with all of the “A” grades at the top, you can sort by Green. Select the filter icon at the top of the column, select Sort by color, select Fill Color, and then select the color you want to sort by. In this case, choose light green.
3. The color you select will be grouped and sorted at the top of the list.
You might notice that you can’t sort by all colors, and this makes sense, because colors have no order to sort by.
However, if you sort the column by numbers, the colors will also be organized according to their numeric ranges. The main point of sorting by color in Google Sheets is to quickly identify a single group of items in a large list according to cell formatting (which is usually based on conditional formatting).
Filter by Color in Google Sheets
Another approach to grouping items by their color is using the filtering feature. Google Sheets now lets you filter out all other colors except the one you’re interested in.
In larger lists, this is very useful for grouping all of the items you’re interested in while removing the clutter of all of the other data.
To do this, select the filter icon at the top of the column, select Filter by color, select Fill Color, and then select the color you want to filter by. In this case light green.
Once you’ve filtered by one color, you’ll see all rows where the column you’ve filtered is the color you selected displayed. All the other rows with other colors will be filtered out of the view.
This is a useful way to focus on groups of data, removing everything else that may get in the way of your analysis.
Sorting or filtering by color in Google Sheets is a less granular approach than sorting by numbers alone, but sometimes visualizing data in categories or groups like this is more valuable.
Sort by Text Color in Google Sheets
In the same way you can sort or filter cell colors in Google Sheets, you can do the same based on text color. This is useful for all of the reasons above, but you’ve created conditional formatting for text color rather than cell color.
To sort by text color:
1. Select Data from the menu, select Filter views, then select Create new filter view.
2. To sort the list with all of the “A” grades at the top, you can sort by Green text. Select the filter icon at the top of the column, select Sort by color, select Text Color, and then select the color you want to sort by. In this case dark green.
3. The color you select will be grouped and sorted at the top of the list.
Just like will the fill sort, the text color you selected will group all items with that text color at the top of the list. All items underneath that group remain unsorted.
Again, it’s a good way to focus on specific groups or categories of data. But if you actually want to sort in numerical order you’ll need to sort by cell content (numbers) as you normally would.
Filter by Text Color in Google Sheets
You can also filter out those records that are other colors, leaving only the group that’s the color you want. Again, this is useful with very long lists where you want to filter out all of the other values that aren’t in the range or category you’re looking for.
To filter by text color:
1. Select Data from the menu, select Filter views, then select Create new filter view.
2. To filter the list so only the “A” grades are listed at the top, you can filter by Green text. Select the filter icon at the top of the column, select Filter by color, select Text Color, and then select the color you want to sort by. In this case, choose dark green.
3. The color you select will be grouped and listed at the top without any other text colors displayed.
This is especially useful for very long lists where you need to filter out the “noise” (all the other data), so you can focus on only the data you care about.
Sort or Filter by Color in Google Sheets
Using the sort or filter function in Google Sheets based on color lets you group and organize data. It’s not like normally sorting or filtering because it doesn’t organize data by each row, but instead by blocks of rows. It’s a great way to categorize and organize information in blocks.
It has its uses if you use it properly whenever you have conditionally formatted data.