Excel - 根据值的变化对整行进行着色 [英] Excel - Shading entire row based on change of value

问题描述

我想根据一个单元格的值遮盖Excel中的整行。例如说我有以下行：

  **文件号** 
 1122 
 1122 
 1144 
 1155 
 1155 
 1155 
 1166 
  

我想要前两行（文件＃的值为1122）以颜色1进行着色，下一行（文件＃的值为1144）要以颜色2进行着色，接下来3行（其中文件＃的值为1155）要被阴影颜色1，下一行（文件＃的值为1166）被阴影颜色2

解决方案

您可以做的是在电子表格的右侧创建一个新列，用于计算您可以将阴影基础的值计算。假设您的新列是列D，并且您要查看的值位于第2行中的列A中。

在单元格D2中，将：= MOD（IF（ROW（）= 2,0，IF（A2 = A1，D1，D1 + 1）），2）

根据需要填充下来（然后如果需要，请隐藏列）。

现在突出显示您的整个数据集 - 此选择的单元格将在下一步中得到阴影。

从首页标签中，点击条件格式设置然后新规则。

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

在格式化此公式为真的值put = $ D2 = 1

单击格式按钮， 填写标签，然后选择要遮蔽的颜色。

这里的示例：

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

**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

解决方案

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.

In cell D2 put: =MOD(IF(ROW()=2,0,IF(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.

Select Use a formula to determine which cells to format.

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

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

Examples here:

这篇关于Excel - 根据值的变化对整行进行着色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！