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?

即

=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 in A101 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去插入>名称>定义和使用的相对引用=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屋！