Excel:我可以根据单元格的颜色创建条件公式吗? [英] Excel: Can I create a Conditional Formula based on the Color of a Cell?

问题描述

我是初学者，正在尝试创建一个公式，根据 B2 中单元格的颜色修改单元格 A1 的内容；

I'm a beginner and trying to create a formula that modifies the contents of Cell A1 based on the color of the cell in B2;

如果单元格 B2 = [红色]，则显示 FQS.

If Cell B2 = [the color red] then display FQS.

如果单元格 B2 = [黄色]，则显示 SM.

If Cell B2 = [the color yellow] then display SM.

这是基于单元格填充颜色的条件.

This is conditional based on the cell fill color.

推荐答案

不幸的是，没有一种直接的方法可以使用单个公式来做到这一点.但是，存在一种相当简单的解决方法.

Unfortunately, there is not a direct way to do this with a single formula. However, there is a fairly simple workaround that exists.

在 Excel 功能区上，转到公式"并单击名称管理器".选择新建"，然后输入CellColor"作为名称".跳到引用"部分并输入以下内容:

On the Excel Ribbon, go to "Formulas" and click on "Name Manager". Select "New" and then enter "CellColor" as the "Name". Jump down to the "Refers to" part and enter the following:

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),1,1))

点击确定"，然后关闭名称管理器"窗口.

Hit OK then close the "Name Manager" window.

现在，在单元格 A1 中输入以下内容:

Now, in cell A1 enter the following:

=IF(CellColor=3,"FQS",IF(CellColor=6,"SM",""))

这将为红色返回 FQS，为黄色返回 SM.对于任何其他颜色，单元格将保持空白.

This will return FQS for red and SM for yellow. For any other color the cell will remain blank.

***如果 A1 中的值没有更新，请按键盘上的F9"强制 Excel 在任何时候更新计算(或者如果 B2 中的颜色发生变化).

***If the value in A1 doesn't update, hit 'F9' on your keyboard to force Excel to update the calculations at any point (or if the color in B2 ever changes).

如果您想扩展内容，以下是单元格填充颜色列表的参考(有 56 种可用):http://www.smixe.com/excel-color-pallette.html

Below is a reference for a list of cell fill colors (there are 56 available) if you ever want to expand things: http://www.smixe.com/excel-color-pallette.html

干杯.

::编辑::

名称管理器中使用的公式可以进一步简化，如果它有助于您理解它的工作原理(我上面包含的版本更加灵活，并且在使用时复制时更容易用于检查多个单元格引用它自己的单元格地址作为参考点，而不是专门针对单元格 B2).

The formula used in Name Manager can be further simplified if it helps your understanding of how it works (the version that I included above is a lot more flexible and is easier to use in checking multiple cell references when copied around as it uses its own cell address as a reference point instead of specifically targeting cell B2).

无论哪种方式，如果您想简化事情，都可以在名称管理器中使用此公式:

Either way, if you'd like to simplify things, you can use this formula in Name Manager instead:

=GET.CELL(63,Sheet1!B2)

这篇关于Excel:我可以根据单元格的颜色创建条件公式吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！