IF函数 - 有没有办法避免重复公式 [英] IF function - is there a way to avoid repeating formula
问题描述
无法相信我不知道这一点,但有没有办法避免在if语句中重复一个公式,如果逻辑测试依赖于它?
Can't believe I don't know this, but is there a way to avoid repeating a formula in an if statement if the logical test is dependent on it?
ie
= IF((SUMIFS公式)= 0,, SUMIFs公式)
=IF((SUMIFS formula)=0,"",SUMIFs formula)
我想在错误的场景中用短的替换那个SUMIFS函数,这将告诉它只是以编程方式重复它最初测试的公式。重复两次公式必须对处理速度产生不利影响。也许,可以忽略不计,但想要在这里寻求最佳实践。谢谢。
I want to replace that SUMIFS function in the false scenario with something short that will tell it to just programmatically repeat the formula it originally tested for. Repeating the formula twice has to have detrimental effects on processing speed. Negligible, maybe, but want to go for best-practices here. Thanks.
推荐答案
您可以为公式指定名称并使用名称 ..............参见:
You can assign a Name to a formula and use the Name..............See:
相关摘录 -
例如,我们假设我们经常使用如下公式:
= SUM(A1:A100)-SUM(B1:B100)
并且它位于A101
中,并被复制到第101行的许多列上。在这种情况下,更好的是
创建一个自定义公式在第101行的每个单元格中执行此操作。这里
是如何;
For example, let's suppose we frequently use a formula like:
=SUM(A1:A100)-SUM(B1:B100)
and this resides inA101
and is copied across many columns on row 101. It would be better in this case to create a custom formula that does this in each cell on row 101. Here is how;
1)选择单元格A101(这是至关重要的) 。
1) Select cell A101 (this is vital).
2)转到工作簿中的名称框中的插入>名称>定义和
类型:SalesLessCosts
2) Go to Insert>Name>Define and in the "Names in workbook" box type: SalesLessCosts
3)现在点击
参考框并输入: = SUM(A1:A100)-SUM(B1:B100)
然后单击
添加。
3) Now click in
the "Refers to" box and type: =SUM(A1:A100)-SUM(B1:B100)
then click
Add.
现在,您可以将单元格A101中的公式替换为: = SalesLessCosts
。
你也可以在第101行复制它,将改变其相对的
参考,就像公式 = SUM(A1:A100)-SUM( B1:B100)
会。
这样做的原因完全取决于我们在
之前选择A101进入Insert> Name> Define并在
中使用相对引用 = SUM(A1: A100)-SUM(B1:B100)
当我们将它添加到参考框时。
Now you can replace the formula in cell A101 with: =SalesLessCosts
.
You can also copy this across row 101 and it will change its relative
references just as the formula =SUM(A1:A100)-SUM(B1:B100)
would. The
reason it does this is all down to the fact we selected A101 before
going to Insert>Name>Define and used relative references in
=SUM(A1:A100)-SUM(B1:B100)
when we added it to the "Refers to" box.
这篇关于IF函数 - 有没有办法避免重复公式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!