I have a table in Excel. In each row, I want to highlight the columns that correspond to the maximum value in that row.
我在Excel中有一个表。在每一行中，我想突出显示与该行中的最大值对应的列。
For example, if I have this table:
例如，如果我有这个表：
0 1 2 3
0 1 2 3
4 5 3 5
4 5 3 5
8 9 3 4
8 9 3 4
I want to highlight 3 in row 1, 5 in row 2 and 9 in row 3.
我想突出显示第1行中的3，第2行中的5和第3行中的9。
How can I do that automatically?
我怎么能自动完成？
THANKS!
谢谢！
3 个解决方案
#1
3
In Excel 2008, you can use Conditional Formatting in the Styles section of the Home Tab. Choose "Highlight Cells Rule", then "More Rules...". That opens the "New Formating Rule" dialog. Choose "Format only top or bottom ranked values". Then choose Format values that rank in the "Top" 1 and leave the % of selected range box UNchecked. Then click the Format button to pick the format that you want.
在Excel 2008中，您可以在“主页”选项卡的“样式”部分中使用“条件格式”。选择“突出显示单元格规则”，然后选择“更多规则...”。这将打开“新建格式规则”对话框。选择“仅格式化排名最高或排名最低的值”。然后选择格式化在“顶部”1中排名的值并保留所选范围的百分比框UNchecked。然后单击“格式”按钮以选择所需的格式。
If you want to be able to do this fully automatically, what you can do is record a macro that does the actions described above and use that to repeat for all rows of your table.
如果您希望能够完全自动执行此操作，您可以执行的操作是记录执行上述操作的宏，并使用该宏重复表的所有行。
#2
1
I think you could do this with conditional formatting, and you wouldn't require code. Just create a formula that looks to see if the cell value is equal to MAX(range_name) and highlight the cell in whatever way you choose.
我认为您可以使用条件格式执行此操作，并且您不需要代码。只需创建一个公式，查看单元格值是否等于MAX（range_name），并以您选择的任何方式突出显示单元格。
#3
1
This is somewhat roundabout, but works for me. Add an extra column (E) somewhere, so you end up with this for data:
这有点迂回，但适合我。在某处添加一个额外的列（E），所以你最终得到这个数据：
A B C D E
1 0 1 2 3 =MAXA(A1:D1)
2 4 5 3 5 =MAXA(A2:D2)
3 8 9 3 4 =MAXA(A3:D3)
Then in a cell, add a conditional format:
然后在单元格中添加条件格式：
For A1: =IF(A1 = $E$1, 1, 0)
Use the format painter to copy this to the other cells in the row. Then copy to the column, modify each column so the $E$1 reference is corrected, and copy to those rose as well
使用format painter将其复制到行中的其他单元格。然后复制到列，修改每一列，以便更正$ E $ 1引用，并复制到那些上升
Ugly, but gets the job done. Probably a quicker way to do it with VBA.
丑陋，但完成工作。使用VBA可能是一种更快捷的方法。
#1
3
In Excel 2008, you can use Conditional Formatting in the Styles section of the Home Tab. Choose "Highlight Cells Rule", then "More Rules...". That opens the "New Formating Rule" dialog. Choose "Format only top or bottom ranked values". Then choose Format values that rank in the "Top" 1 and leave the % of selected range box UNchecked. Then click the Format button to pick the format that you want.
在Excel 2008中，您可以在“主页”选项卡的“样式”部分中使用“条件格式”。选择“突出显示单元格规则”，然后选择“更多规则...”。这将打开“新建格式规则”对话框。选择“仅格式化排名最高或排名最低的值”。然后选择格式化在“顶部”1中排名的值并保留所选范围的百分比框UNchecked。然后单击“格式”按钮以选择所需的格式。
If you want to be able to do this fully automatically, what you can do is record a macro that does the actions described above and use that to repeat for all rows of your table.
如果您希望能够完全自动执行此操作，您可以执行的操作是记录执行上述操作的宏，并使用该宏重复表的所有行。
#2
1
I think you could do this with conditional formatting, and you wouldn't require code. Just create a formula that looks to see if the cell value is equal to MAX(range_name) and highlight the cell in whatever way you choose.
我认为您可以使用条件格式执行此操作，并且您不需要代码。只需创建一个公式，查看单元格值是否等于MAX（range_name），并以您选择的任何方式突出显示单元格。
#3
1
This is somewhat roundabout, but works for me. Add an extra column (E) somewhere, so you end up with this for data:
这有点迂回，但适合我。在某处添加一个额外的列（E），所以你最终得到这个数据：
A B C D E
1 0 1 2 3 =MAXA(A1:D1)
2 4 5 3 5 =MAXA(A2:D2)
3 8 9 3 4 =MAXA(A3:D3)
Then in a cell, add a conditional format:
然后在单元格中添加条件格式：
For A1: =IF(A1 = $E$1, 1, 0)
Use the format painter to copy this to the other cells in the row. Then copy to the column, modify each column so the $E$1 reference is corrected, and copy to those rose as well
使用format painter将其复制到行中的其他单元格。然后复制到列，修改每一列，以便更正$ E $ 1引用，并复制到那些上升
Ugly, but gets the job done. Probably a quicker way to do it with VBA.
丑陋，但完成工作。使用VBA可能是一种更快捷的方法。