根据A列的值设置行颜色的格式 [英] format row colour based on column A values

问题描述

我在Excel工作表中大约有2000行.第一栏是我感兴趣的.

I have about 2000 rows in an excel sheet. The first column is what I am interested in.

数据示例为

|123|
|123|
|124|
|125|
|126|
|126|

如果数字匹配，我想突出显示行背景.例如

I want to get the rows background highlighted if they numbers match. For example

123在两行中，因此这两行应为shade1，存在124次则应为shade2.一次出现125，因此应该仅在一行中显示shade1.126行应高亮显示阴影2

123 in in two rows so these two rows should be shade1, 124 is present once so should be shade2. 125 is presnt once so should be shade1 in just one line. Line with 126 should be highllighted shade2

我试图通过条件格式来解决这个问题，但似乎不可能.你能帮忙吗?

Ive tried to figure this out by conditional formatting but it doesnt seem to be possible. Can you help?

谢谢

推荐答案

以前的答案显然是错误的，我误解了这个问题.

The previous answer was obviously wrong, I misunderstood the question.

如果您要将替代颜色应用于第一列中具有相同值的行组:

If you want to apply alternate colors to group of rows with the same value in first column:

• 选择表中的所有单元格，在 A2 中开始选择，然后将其上下移动
• 条件格式>新规则
• 使用公式确定要格式化的单元格

• 输入以下公式:

• select all cells in the table, start the selection in A2 and move it down and right
• Conditional Formatting > New Rule
• Use a formula to determine which cells to format

• Enter the following formula:

=MOD(SUMPRODUCT(1*($A$1:$A1<>$A$2:$A2)),2) = 1

将格式设置为 shade1

确认

使用公式重复

set format to shade1

confirm

reapeat with formula

=MOD(SUMPRODUCT(1*($A$1:$A1<>$A$2:$A2)),2) = 0

将格式设置为 shade2

无论第一列是否排序，这都将起作用.

This will work no matter if the first column is sorted.

这篇关于根据A列的值设置行颜色的格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！