用好Excel的“颜色”，获得不一样的体验|剪贴板|窗体

大家好，我是永不止步的老牛。

Excel使用中，如果能用好颜色，会让你的数据增色不少，便捷许多。

以下是我认为经常会碰到的场景，分享出来，希望能给大家帮助。

我们用下图数据作为演示数据，大家不要纠结数据逻辑的合理性，主要是看具体的操作步骤。

以下为我们模拟的规则：

如果库存大于预警上限，在预警值中显示超出预警上限的值，并标为黄色
如果库存小于预警下限，在预警值中显示低于预警下限的值，并标为红色
如果库存在预警下限和预警上限之间，在预警值中0，不变色

一、如何设置条件格式

我们先建立需标黄色的条件格式，选中G4:G15，点击顶部菜单“开始”-“条件格式”-“新建规则”。

在弹出窗体中，“选择规则类型”选择第二项“只为包含以下内容的单元格设置格式”，在规则编辑处，选择“大于”，输入框内输入0，再点击“格式”-“填充”，选择黄色，确定，再确定。

此时数据中库存大于预警上限的数据背景就成黄色了，同样的操作设置库存小于预警下限的，只是选择“小于”0，颜色选成红色。

二、如何设置条件整行格式

我们需要根据条件，将整行数据变色，以黄色为例，选中需变色的区域A4:G5，点击顶部菜单“开始”-“条件格式”-“新建规则”，在弹出窗体中，“选择规则类型”选择最后项“使用公式确定要设置格式的单元格”，在公式输入框中，输入“=$G4>0”，再点击“格式”-“填充”，选择黄色，确定，在确定。

此时数据中库存大于预警上限的数据整行背景就成黄色了。

三、如何根据颜色筛选数据

先说如何设置筛选，有以下几个方法：

办法1：选中A3:G15，点击顶部菜单“数据”-“筛选”。

办法2：选中准备筛选区域内任一单元格，按“Ctrl+A”(等同选中A3:G15)，点击顶部菜单“数据”-“开始”。

办法3：选中准备筛选区域的第一行，按“Ctrl+Shift+L”。

“Ctrl+Shift+L”是个开关键，再按一次就取消筛选。

筛选设置好后，我们只要点击G3列的下三角，在下来菜单中选择“按颜色筛选”，右侧就会弹出你数据包含的颜色，想看哪个就点哪个颜色。

四、如何对根据颜色筛选的数据进行求和

我们先用SUM函数对G4:G15进行汇总，再用SUBTOTAL函数对G4:G15进行汇总，然后通过颜色进行筛选，我们会发现，SUM统计的结果不是我们想要的，而SUBTOTAL统计值可以根据筛选结果变动。

使用SUBTOTAL函数的公式是：=SUBTOTAL(109,G4:G15)

有关SUBTOTAL函数的详细用法，请参考我的视频“Excel的汇总之王Subtotal实操”。

五、如何根据条件设置颜色，在不筛选下汇总数据

先按上述方式设置好条件格式，此时是无法根据颜色获取到数据的，因为单元格真实的背景色是白色，只是条件格式让它显示成红黄色了，这种情况下，我们需要执行以下操作：

1、选中数据区域A4:G15，按“Ctrl+C”，鼠标点击顶部菜单“开始”的“剪贴板”项右下角的小箭头，如下图位置，在左侧出现的“剪贴板”中，点击你刚才按“Ctrl+C”复制到剪贴板中的数据，此时会将剪贴板的数据反填到A4:G15。

2、按“Ctrl+F”，在弹出的查找窗体中，点击“选项”，点击“格式”，选择“红色”，点击“查找全部”，在查找结果列表中点击任一行，按“Ctrl+A”，然后关闭窗体。

在左上角的“名称框”处输入“红色”，相当于把选中的数据定义成名称“红色”。

3、参照2，把颜色是黄色的数据定义成名称“黄色”

4、选中H4，输入公式：=SUM(红色)

5、选中J4，输入公式：=SUM(黄色)

六、如何根据颜色隐藏行

按“Ctrl+F”，在弹出的查找窗体中，点击“选项”，点击“格式”，选择“红色”，点击“查找全部”，在查找结果列表中点击任一行，按“Ctrl+A”，然后关闭窗体，再按“Ctrl+9”，就把刚查到的数据隐藏了。

“Ctrl+Shift+9”是取消隐藏。

七、如何根据颜色复制行

我们利用第二点的办法，设置条件格式整行变色，然后按“Ctrl+F”，在弹出的查找窗体中，点击“选项”，点击“格式”，选择“红色”，点击“查找全部”，在查找结果列表中点击任一行，按“Ctrl+A”，然后关闭窗体，再按“Ctrl+C”，就把刚查到的数据复制到剪贴板了。

八、复制时如何不包含隐藏行

我们复制数据时，有些数据不想给别人看到，我们可以利用第六点把不想给别人看的数据隐藏起来，然后选中数据区域，此时要是按Ctrl+C复制，会把隐藏行也复制进去，我们先不要按“Ctrl+C”，先按F5，在弹出窗体中点“定位条件”，选择“可见单元格”，确定后，再按“Ctrl+C”，此时复制的数据步包含隐藏行。