Excel -根据值的变化对整行进行着色

I would like to shade entire rows in Excel based on the value of one cell. For example say I have the rows below:

我希望基于一个单元格的值在Excel中对整个行进行阴影处理。例如，我有下面的行:

**File No**
1122
1122
1144
1155
1155
1155
1166

I would like the first two rows (where the value of the file # is 1122) to be shaded in color 1, the next row (where the value of the file # is 1144) to be shaded in color 2, the next 3 rows (where the value of the file # is 1155) to be shaded color 1, the next row (where the value of the file # is 1166) to be shaded color 2

我想前两行(文件的值# 1122)阴影颜色1,下一行的值(文件# 1144)阴影颜色2,接下来的3行(文件的值# 1155)阴影颜色1,下一行的值(文件# 1166)阴影颜色2

10 个解决方案

#1

What you can do is create a new column over on the right side of your spreadsheet that you'll use to compute a value you can base your shading on.

你可以做的是在你的电子表格的右边创建一个新的列，你可以用它来计算你的阴影的值。

Let's say your new column is column D, and the value you want to look at is in column A starting in row 2.

假设你的新列是D列，你想看的值在A列，从第2行开始。

In cell D2 put: =MOD(IF(ROW()=2,0,IF(A2=A1,D1, D1+1)), 2)

在细胞D2说:=国防部(如果(行()= 2,0,如果(A2 = A1,D1,D1 + 1)),2)

Fill that down as far as you need, (then hide the column if you want).

根据需要将其填满(如果需要，则隐藏列)。

Now highlight your entire data set - this selection of cells will be the ones that get shaded in the next step.

现在突出显示整个数据集——这些单元格的选择将在下一步中被着色。

From the Home tab, click Conditional Formatting, then New Rule.

在Home选项卡中，单击条件格式，然后单击New Rule。

Select Use a formula to determine which cells to format.

选择使用公式确定要格式化哪些单元格。

In "Format values where this formula is true" put =$D2=1

在“公式为真的格式值”中，代入=$D2=1

Click the Format button, click the Fill tab, then choose the color you want to shade with.

单击“格式”按钮，单击“填充”选项卡，然后选择要着色的颜色。

Examples here:

例子:

#2

If you are using MS Excel 2007, you could use the conditional formatting on the Home tab as shown in the screenshot below. You could either use the color scales default option as I have done here or you can go ahead and create a new rule based on your data set.

如果您使用MS Excel 2007，您可以在Home选项卡上使用条件格式，如下面的屏幕截图所示。您可以像我在这里所做的那样使用颜色比例默认选项，或者您可以继续创建一个基于您的数据集的新规则。

#3

Use Conditional Formatting.

使用条件格式。

In it's simplest form, you are saying "for this cell, if it's value is X, then apply format foo". However, if you use the "formula" method, you can select the whole row, enter the formula and associated format, then use copy and paste (formats only) for the rest of the table.

在最简单的形式中，你说“对于这个单元格，如果它的值是X，那么应用format foo”。但是，如果您使用“公式”方法，您可以选择整个行，输入公式和相关的格式，然后对表的其余部分使用复制和粘贴(仅使用格式)。

You're limited to only 3 rules in Excel 2003 or older so you might want to define a pattern for the colours rather than using raw values. Something like this should work though:

在excel2003或更老版本中，您只能使用3条规则，因此您可能需要为颜色定义一个模式，而不是使用原始值。但是，类似这样的东西应该是有用的:

#4

This one has puzzled me for ages. Don't like the idea of creating an extra (irrelevant) row/column just to calculate formatting. Finally came up with the following rule:

这个问题困扰了我很久。不要仅仅为了计算格式而创建额外的(不相关的)行/列。最后得出以下规则:

=INDIRECT("A"&ROW())<>INDIRECT("A"&(ROW()-1))

This creates the reference A2<>A1 for row 2, A3<>A2 for row 3 etc. Adjust the letter "A" to be the column you wish to compare

这将为第2行创建引用A2<>A1，为第3行创建A3<>A2等。将字母“A”调整为您希望比较的列

#5

I have found a simple solution to banding by content at Pearson Software Consulting: Let's say the header is from A1 to B1, table data is from A2 to B5, the controling cell is in the A column

我在皮尔逊软件咨询公司找到了一个简单的通过内容进行分组的解决方案:假设头从A1到B1，表数据从A2到B5，控制单元在a列中

1. Make a new column, C
2. 新建一列，C
3. At first the first row to color make the formula =true in the C2 cell
4. 首先，第一行的颜色使公式=true在C2单元格中。
5. In the second row make the formula =IF(A3=A2,C2,NOT(C2))
6. 在第二行中，令公式=IF(A3=A2,C2,NOT(C2))
7. Fill the column down to the last row
8. 把这列填到最后一行
9. Select the data range
10. 选择数据范围
11. Select conditional formatting, choose Use a formula... and put =$C2 as the formula
12. 选择条件格式，选择使用公式……代入=$C2

#6

In MS Excel, first save your workbook as a Macro Enabled file then go to the Developper Tab and click on Visual Basic. Copy and paste this code in the "ThisWorkbook" Excel Objects. Replace the 2 values of G = and C= by the number of the column containing the values being referenced.

在MS Excel中，首先将工作簿保存为一个宏启用的文件，然后转到Developper选项卡并单击Visual Basic。复制并粘贴此代码到“ThisWorkbook”Excel对象中。将G =和C=的两个值替换为包含所引用值的列的数量。

In your case, if the number of the column named "File No" is the first column (namely column 1), replace G=6 by G=1 and C=6 by C-1. Finally click on Macro, Select and Run it. Voila! Works like a charm.

在您的示例中，如果名为“File No”的列的编号是第一列(即第一列)，则将G=6替换为G=1, C=6替换为C-1。最后单击宏，选择并运行它。瞧!就像一个魅力。

Sub color()
    Dim g As Long
    Dim c As Integer
    Dim colorIt As Boolean

    g = 6
    c = 6
    colorIt = True

    Do While Cells(g, c) <> ""
        test_value = Cells(g, c)
        Do While Cells(g, c) = test_value
            If colorIt Then
                Cells(g, c).EntireRow.Select
                Selection.Interior.ColorIndex = 15
            Else
                Cells(g, c).EntireRow.Select
                Selection.Interior.ColorIndex = x1None
            End If
            g = g + 1
        Loop
        colorIt = Not (colorIt)
    Loop
End Sub

#7

Like at least 1 other contributor here, I also have never liked having to add a extra "helper" column, which can create some hassles in various situations. I finally found a solution. There are a couple different formulas that you can use depending on needs and what is in the column, whether there are blank values, etc. For most of my needs, I have landed on using the following simple formula for the CF formula:

和这里的至少一个其他贡献者一样，我也不喜欢添加额外的“助手”列，这可能会在各种情况下造成一些麻烦。我终于找到了解决办法。有几个不同的公式，你可以根据需要和列中的内容，是否有空白的值，等等。对于我的大部分需求，我已经使用了以下简单的公式，用于CF公式:

=MOD(Fixed(SUMPRODUCT(1/COUNTIF(CurrentRange,CurrentRange))),2)=0

=国防部(固定(SUMPRODUCT(1 /条件统计(CurrentRange CurrentRange))),2)= 0

I create a Named Range called "CurrentRange" using the following formula where [Sheet] is the sheet on which your data results, [DC] is the column with the values on which you want to band your data and [FR] is the first row that the data is in:

我使用以下公式创建了一个名为“CurrentRange”的命名范围，其中[Sheet]是您的数据结果所在的表，[DC]是您想要对数据进行绑定的列，[FR]是数据所在的第一行:

=[Sheet]!$[DC]$[FR]:INDIRECT("$[DC]$" & ROW())

=(表)!(直流)美元[FR]:间接(“(DC)美元”和行())

The sheet reference and column reference will be based on the column that has the values you are evaluating. NOTE: You have to use a named range in the formula because will throw an error if you try to use range references directly in the CF rule formula.

表引用和列引用将基于具有您正在计算的值的列。注意:您必须在公式中使用一个指定的范围，因为如果您尝试在CF规则公式中直接使用范围引用，将会抛出一个错误。

Basically, the formula works by evaluating for each row the count of all of the unique values for that row and above to the top of your range. That value for each row essentially provides an ascending Unique ID for each new unique value. Then it uses that value in the place of the Row() function within the standard CF MOD function formula for simple alternating row colors (i.e. =Mod(Row(),2)=0).

基本上，这个公式是通过对每一行的计算来计算所有的唯一值的计数，并在你的范围的上面。每个行的值实质上为每个新的唯一值提供一个升序唯一ID。然后，它将这个值用在标准的CF MOD函数公式中，用于简单的交替行颜色(例如，= MOD (Row()，2)=0)。

See the following example that breaks down the formula to show the resulting components in columns to show what it is doing behind the scenes.

请参见下面的示例，该示例分解了公式，以在列中显示结果组件，以显示它在幕后所做的工作。

Example Data

示例数据

In this example, the CurrentRange named range is defined as:

在本例中，命名为range的CurrentRange被定义为:

=Sheet1$A$2:INDIRECT("$A$" & ROW())

= Sheet1 $ $ 2:间接(“$ $”&行())

The Unique ID column contains the following portion of the formula of the CF formula:

唯一ID列包含CF公式公式的以下部分:

=Fixed(SUMPRODUCT(1/COUNTIF(CurrentRange,CurrentRange)))

=固定(SUMPRODUCT(1 /条件统计(CurrentRange CurrentRange)))

You can see that, as of row 3, the count of unique values from that row and above in the "Color" column is 2 and it remains 2 in each subsequent row until row 6 when the formula finally encounters a 3rd unique value.

您可以看到，在第3行中，“Color”列中这一行和上面的惟一值的计数是2，并且在后面的每一行中它仍然是2，直到第6行，当该公式最终遇到第三个惟一值时。

The Band column uses the remainder of the formula referring to the result in column B =MOD(B2,2) to show how it gets you to the 1s and 0s that can then be used for CF.

带列使用公式的余数表示列B =MOD(B2,2)中的结果，来展示它是如何得到可以用于CF的1和0的。

In the end, the point is, you don't need the extra columns. The entire formula can be used in the CF rule + a named range. For me, that means I can stick the basic formula in a template that I use to drop data in and not have to worry about messing with an extra column once the data is dropped in. It just works by default. Additionally, if you need to account for blanks or other complexities or large sets of data you can use other more complex formulas using frequency and match functions.

最后，重点是，不需要额外的列。整个公式可以在CF规则+一个命名范围中使用。对我来说，这意味着我可以将基本公式插入到一个用于删除数据的模板中，而不必担心一旦数据被删除后会造成额外的列混乱。它只是默认工作。此外，如果您需要考虑空白或其他复杂性或大量数据，您可以使用频率和匹配函数来使用其他更复杂的公式。

Hope this helps someone else avoid the frustration I have had for years!

希望这能帮助别人避免多年来的挫折!

#8

I hate using these in-cell formulas and having to fill in a new column, and I finally learned enough to make by own VBA macro to accomplish this effect.

我讨厌使用这些细胞内公式，并且必须填写一个新的列，最后我学到了足够的知识，可以使用自己的VBA宏来实现这个效果。

This might not be all that logically different from another answer, but I think the code looks a hell of a lot better:

这可能与另一个答案在逻辑上没有什么不同，但我认为代码看起来要好得多:

Dim Switch As Boolean
For Each Cell In Range("B2:B" & ActiveSheet.UsedRange.Rows.Count)
    If Not Cell.Value = Cell.Offset(-1, 0).Value Then Switch = Not (Switch)
    If Switch Then Range("A" & Cell.Row & ":" & Chr(ActiveSheet.UsedRange.Columns.Count + 64) & Cell.Row).Interior.Pattern = xlNone
    If Not Switch Then Range("A" & Cell.Row & ":" & Chr(ActiveSheet.UsedRange.Columns.Count + 64) & Cell.Row).Interior.Color = 14869218
Next

My code here is going by column B, it assumes a header row so it starts at 2, and I use the Chr(x+64) method to get column letters (which won't work past column Z; I haven't yet found a simple-enough method for getting past this).

我这里的代码是按B列写的，它假设有一个头行所以它从2开始，我使用Chr(x+64)方法来获取列字母(它不会在Z列之后工作;我还没有找到一个足够简单的方法来解决这个问题)。

First, the boolean variable will alternate whenever the value changes to a new one (uses Offset to check cell above), and for each pass the row is checked for either True or False and colors it accordingly.

首先，当值更改为一个新的值时，布尔变量将进行替换(使用偏移量检查上面的单元格)，对于每一次传递，将检查行是否为真或假，并相应地将其着色。

#9

I had to do something similar for my users, with a small variant that they want to have a running number grouping the similar items. Thought I'd share it here.

我必须为我的用户做一些类似的事情，使用一个小的变体，他们希望有一个可运行的编号来分组相似的项目。我想在这里分享。

• Make a new column A
• 新建一列a
• Assuming the first row of data is in row 2 (row 1 being header), put 1 in A2
• 假设第一行数据在第2行(第1行是header)，将1代入A2
• Assuming your File No is in column B, in the second row (in this case A3) make the formula =IF(B3=B2,A2,A2+1)
• 假设文件No在B列中，在第二行(在本例中为A3)中，令公式=IF(B3=B2,A2,A2+1)
• Fill/copy-paste cell A3 down the column to the last row (be careful not to copy A2 by accident; that will populate all cells with 1)
• 将/复制-粘贴单元格A3填充到最后一行(注意不要意外地复制A2;用1)填充所有单元格
• Select the data range
• 选择数据范围
• In the Home ribbon select Conditional Formatting -> New Rule
• 在主色带中选择条件格式->新规则
• Choose Use a formula to determine which cells to format
• 选择使用公式来确定要格式化哪些单元格
• In the formula cell, put =MOD($A1, 2)=1 as the formula
• 在公式单元格中，put =MOD($A1, 2)=1作为公式
• Click Format, select the Fill tab
• 单击“格式”，选择“填充”选项卡。
• Select the Background Color you want, then click OK
• 选择所需的背景色，然后单击OK
• Click OK
• 单击OK

#10

you could use this formular to do the job -> get the CellValue for the specific row by typing: = indirect("$A&Cell()) depending on which column you have to check, you have to change the $A

您可以使用这个格式来完成这项工作——>通过输入:=间接(“$A&Cell())”来获取特定行的CellValue，这取决于您要检查的列，您必须更改$A

For Example -> You could use a customized VBA Function in the Background:

例如，>可以在后台使用定制的VBA函数:

Public Function IstDatum(Zelle) As Boolean IstDatum = False If IsDate(Zelle) Then IstDatum = True End Function

公共函数IstDatum(Zelle)作为布尔IstDatum = False，如果IsDate(Zelle)，则IstDatum = True End函数

I need it to check for a date-entry in column A:

我需要它检查a栏的日期输入:

=IstDatum(INDIREKT("$A"&ZEILE()))

= IstDatum(INDIREKT(" $ " &ZEILE()))

have a look at the picture:

请看这张照片: