Excel带加权总计的条件格式

I have sets of values in rows in Excel First row contains weights of how much each value in each column contributes to weighted total. Now, I want to mark in each following row the value that contributed to the weighted total the most, by changing its background. I played with conditional formatting, but my Excel skills are modest, to say the least. So, how do I do that?

我在Excel中的行中有一组值第一行包含每列中每个值对加权总计的贡献。现在,我希望通过更改其背景,在每个后续行中标记对加权总数贡献最大的值。我玩条件格式,但我的Excel技能是适度的,至少可以说。那么,我该怎么做?

2 个解决方案

#1

Assuming the weights are in A1:C1 and the values are in A2:C2, enter =MAX($A$1:$C$1*$A2:$C2)/A$1 as the 'equals' condition for A2

假设权重在A1:C1中且值在A2:C2中,则输入= MAX($ A $ 1:$ C $ 1 * $ A2:$ C2)/ A $ 1作为A2的'等于'条件

alt text http://www.vulomedia.com/images/12339ExcelConditionalFormatting.jpg

In words this formula is (maximum of (values x weights)) / this column's weight.

用这个词来表示(最大值(值x权重))/该列的权重。

Experienced Excel users may notice that if this formula was entered in a worksheet cell it would have to be entered as an array formula to give the correct value. The trick here is that when formulae are used in conditional formats they are always treated as array formulae; no Ctrl-Shift-Enter required.

有经验的Excel用户可能会注意到,如果在工作表单元格中输入此公式,则必须将其作为数组公式输入以提供正确的值。这里的技巧是当公式用于条件格式时,它们总是被视为数组公式;无需Ctrl-Shift-Enter。

#2

Here's what I think you are trying to do: You have a list of numbers (they happen to be weights, but that's not important). You want the largest number to be highlighted differently.

以下是我认为你要做的事情:你有一个数字列表(它们恰好是权重,但这并不重要)。您希望以不同方式突出显示最大数字。

Suppose the cells are in the range A1:A8.

假设单元格在A1:A8范围内。

Select the range A1:A8.

选择范围A1:A8。

Select Conditional Formatting (Format | Conditional Formatting)

选择条件格式(格式|条件格式)

Enter the condition Cell Value is/Equal To/=MAX(A1:A8)

输入条件单元格值是/等于/ = MAX(A1:A8)

Click the Format... button and set the Pattern to affect the background.

单击格式...按钮并设置模式以影响背景。

(Tested on Excel 2003 on Windows.)

(在Windows上的Excel 2003上测试过。)

#1