I have a Table with three columns:
我有一个包含三列的表:
Name Task HoursSheena Task1 5Sheena Task2 3Chris Task1 5Chris Task1 2.5
名称任务时间Sheena Task1 5 Sheena Task2 3 Chris Task1 5 Chris Task1 2.5
I am trying to conditionally format the Hours column. I want to format the cells there if the total hours for the person are over 7.5 . In the example, the cells containing the hours for Sheena should be formatted because her totals are over 7.5 but Chris's equal exactly 7.5 so should not be formatted.
我试图有条件地格式化时数列。如果这个人的总小时数超过7.5,我想格式化那里的细胞。在示例中,包含Sheena小时数的单元格应该格式化,因为她的总数超过7.5,但Chris等于7.5,所以不应该格式化。
I assume the formula is something along these lines:
我假设公式是这样的:
=SUMIF([Name],INDIRECT("RC[-2]",0),[Hours])>7.5
but this does not work. The following does:
但这不起作用。以下是:
=SUMIF($A$2:$A$5,$A2,$C$2:$E$5)>7.5
but is messy because it doesn't use column references.
但是它很乱,因为它不使用列引用。
2 个解决方案
#1
0
This works:
=SUMIF(Name, INDIRECT("RC[-2]", FALSE), Hours)>7.5
#2
0
The formula "something along these lines" looks very good but unfortunately Any structured reference throws an error in CF.
公式“沿着这些方向的东西”看起来非常好但不幸的是任何结构化引用都会在CF中引发错误。
This goes on to mention wrapping in INDIRECT. Something like this does work:
这继续提到INDIRECT的包装。像这样的东西确实有效:
=SUMIF(INDIRECT("Table2[Name]"),INDIRECT("RC[-2]",FALSE),INDIRECT("Table2[Hours]"))>7.5
but in terms of messiness I think even worse than your messy:
但就混乱而言,我认为比你的凌乱更糟糕:
=SUMIF($A$2:$A$5,$A2,$C$2:$E$5)>7.5
I think @smozgur has offered an excellent "compromise" though this does involve some "behind the scenes" work. He has named the data ranges within your table as Hours and Name (hence the lack of square brackets - these are not structured references). Makes the formula look almost as you would like it but might be confusing because the range for [Hours] for example will automatically expand if new rows are added to the Table - but the named range Hours will not.
我认为@smozgur提供了一个很好的“妥协”,虽然这确实涉及一些“幕后”的工作。他已将表中的数据范围命名为小时和名称(因此缺少方括号 - 这些不是结构化引用)。使公式看起来几乎与您希望的一样,但可能会令人困惑,因为如果将新行添加到表中,[小时]的范围将自动扩展 - 但命名范围小时不会。
Hence my preference, assuming no conflict with data elsewhere in the columns, would be:
因此,假设与列中其他地方的数据没有冲突,我的偏好是:
=SUMIF(A:A,A1,C:C)>7.5
_ at least it is short! (But I admit I do not like structured references.)
_至少它很短! (但我承认我不喜欢结构化的引用。)
#1
0
This works:
=SUMIF(Name, INDIRECT("RC[-2]", FALSE), Hours)>7.5
#2
0
The formula "something along these lines" looks very good but unfortunately Any structured reference throws an error in CF.
公式“沿着这些方向的东西”看起来非常好但不幸的是任何结构化引用都会在CF中引发错误。
This goes on to mention wrapping in INDIRECT. Something like this does work:
这继续提到INDIRECT的包装。像这样的东西确实有效:
=SUMIF(INDIRECT("Table2[Name]"),INDIRECT("RC[-2]",FALSE),INDIRECT("Table2[Hours]"))>7.5
but in terms of messiness I think even worse than your messy:
但就混乱而言,我认为比你的凌乱更糟糕:
=SUMIF($A$2:$A$5,$A2,$C$2:$E$5)>7.5
I think @smozgur has offered an excellent "compromise" though this does involve some "behind the scenes" work. He has named the data ranges within your table as Hours and Name (hence the lack of square brackets - these are not structured references). Makes the formula look almost as you would like it but might be confusing because the range for [Hours] for example will automatically expand if new rows are added to the Table - but the named range Hours will not.
我认为@smozgur提供了一个很好的“妥协”,虽然这确实涉及一些“幕后”的工作。他已将表中的数据范围命名为小时和名称(因此缺少方括号 - 这些不是结构化引用)。使公式看起来几乎与您希望的一样,但可能会令人困惑,因为如果将新行添加到表中,[小时]的范围将自动扩展 - 但命名范围小时不会。
Hence my preference, assuming no conflict with data elsewhere in the columns, would be:
因此,假设与列中其他地方的数据没有冲突,我的偏好是:
=SUMIF(A:A,A1,C:C)>7.5
_ at least it is short! (But I admit I do not like structured references.)
_至少它很短! (但我承认我不喜欢结构化的引用。)