logo标签列表

IF函数 - 有没有避免重复公式的方法

excelif-statementexcel-formula
40

难以置信我不知道这个，但如果逻辑测试依赖于它，有没有办法避免在if语句中重复使用公式？

也就是说,

=IF((SUMIFS 公式)=0,"",SUMIFs 公式)

我想用一些简短的东西来替换false场景中的SUMIFS函数，告诉它只需以编程方式重复最初测试的公式。 重复两次公式肯定会对处理速度产生不利影响。 可能可以忽略不计，但希望采用最佳实践。 谢谢。

最好将公式放在一个（隐藏的？）单元格中，并在方程式中引用该单元格，例如 IF(cell=0,"",cell)。这是一个非常好的问题，但据我所知，没有一个真正好的答案。 - Floris
根据您的需求而定，但另一个选项是应用自定义格式，将空字符串 "" 显示为 0。 - Dmitry Pavliv
1
我认为我们都可以同意，明智的选择显然是 =SUBSTITUTE(SUBSTITUTE("Z" & SUMIFS(...) & "Z", "Z0Z", ""), "Z", "")。 - Alex K.
8个回答
48
您可以强制出现错误，例如#DIV/0!，然后使用IFERROR函数进行处理，如下所示：=IFERROR(1/(1/SUMIFS_formula),"")。
4
哇，这是一个非常有趣的解决方案。谢谢。对于所提供的特定公式，它是所有答案中最简洁的，但我只投了一票，而不是选择它作为答案，因为我实际上想要某种变量。在Excel中，重复的公式在各种公式场景中都会出现，我需要一种方法来避免这样做，以缩减我的公式。不过还是谢谢！我已经在我的特定情况下使用了它。 - Lorenzo
这是一个非常棒的技巧。这里有两个聪明的点：第一，使用ISERROR函数；第二，重写公式，使其在特定条件为真时抛出错误，否则返回“正确”的值。向你致敬！ - Floris
很好的数字数据解决方案，但对于文本字符串无法使用。 - geotheory
这是邪恶而极其聪明的。 - Justin Makeig
1
正如@GSerg为我回答的那样，在365中，您可以使用LET公式。=LET( x, SUMIFS(formula), IF(x=0,"",x)) - Greg_D
13
您可以为公式分配一个名称并使用该名称......请参见：

为公式指定名称

相关摘录 -
例如，假设我们经常使用像=SUM（A1：A100）-SUM（B1：B100）这样的公式，它位于A101中，并且在第101行的许多列中进行复制。在这种情况下，最好创建一个自定义公式，在第101行的每个单元格中执行此操作。如下：
1）选择单元格A101（这非常重要）。
2）转到“插入”>“名称”>“定义”，在“工作簿中的名称”框中键入：SalesLessCosts
3）现在单击“引用”的框，并键入：=SUM（A1：A100）-SUM（B1：B100），然后单击添加。
现在，您可以将单元格A101中的公式替换为：=SalesLessCosts。您还可以将其复制到第101行，并且它将更改其相对引用，就像公式=SUM（A1：A100）-SUM（B1：B100）一样。它能够这样做的原因是我们在转到“插入”>“名称”>“定义”之前选择了A101，并在将其添加到“引用”框中时使用了相对引用。
这很有趣。出于某种原因，我不知道您可以在VB编辑器之外声明唯一的公式。对我来说，仍然感觉很奇怪，在公式内部没有办法做到这一点，就像在Javascript中的“this”关键字一样。也许将来会有这样的功能。谢谢。 - Lorenzo
我认为这并没有解决函数被评估两次的问题，即使你节省了一些打字时间。我更喜欢barry houdini的解决方案。 - Floris
坦白地说，这个技巧非常棒。虽然不能避免重复操作，但它是一种难以置信的帮助！ - Mark Lazarides
@LazzMaTazz 这可以节省很多打字和输错的时间。 - Gary's Student
8
LET公式可用于此确切场景。您可以将公式定义为变量，然后在同一单元格中引用该变量以在公式中使用。 LET公式格式如下：
=LET(name,name_value,calculation)

SUMIFS示例

以下是如何使用SUMIF示例使您无需重复公式：

LET示例的屏幕截图

在此屏幕截图中，我们有一个数组A1:B7。 如果ColA中的名称为“apple”，则我们要对值（Col B）求和。

为此，我们有一个标准的SUMIFS公式：

=SUMIFS(B1:B7,A1:A7,"apple")

E2单元格中显示公式。E3单元格中显示结果。

为了在不重复公式的情况下将其放入IF语句中，我们可以使用截图中显示的LET。

我们创建一个变量，将SUMIFS公式作为该变量的值。然后，我们编写IF语句，使用变量名称而不是多次重写公式。

=LET(name,name_value,calculation)

变量名: sumapples

变量值: SUMIFS(B1:B7,A1:A7,"apple")

计算: IF(sumapples=0,"",sumapples)

将它们放在 LET 函数中，它看起来像这样:

=LET(sumapples,SUMIFS(B1:B7,A1:B7,"apple"),IF(sumapples=0,"",sumapples))

这个LET函数可以在任何Excel公式中使用，非常有用，可以缩短具有重复的长公式。

可选：额外复杂性

如果您愿意，可以通过命名多个变量来增加复杂度。

=LET(name,name_value,name2,name_value2,calculation)
6
如果您只想隐藏零，则有一个简单的方法：
  • 选择要隐藏零的所有单元格
  • 进入自定义数字格式
  • 将格式设置为“General; General;”
自定义格式具有[正数]; [负数]; [零]的结构 通过使最后一部分为空，您有效地隐藏了零，但显示其他所有内容。
与条件格式不同的是，您可以在任何背景上使用此选项。
我有时使用的一个巧妙的技巧是使用“;;;”的自定义格式完全隐藏单元格值。这样，您可以像使用条件格式一样在单元格中放置图像，而根本看不到该值。
3
尝试使用SUBSTITUTE函数，如下所示：
=SUBSTITUTE( VLOOKUP( H4; $D$5:$E$8; 2; 0 ); $H$1; $I$1 )

这里有一个示例：

Example

我不想重复两次的公式是VLOOKUP函数。 VLOOKUP的结果是在另一张表格中找到的字符串（例如：“绿色”）。

我想检查该字符串是否与$H$1中特定的字符串值匹配（这里是“黄色”）。

  • 如果匹配，SUBSTITUTE将其替换为$I$1（您想要的错误字符串。这里是“FORBIDDEN”）。
  • 如果不匹配，则显示VLOOKUP结果字符串（正常授权输出，如“绿色”）。

这对我很有用，因为我的实际公式非常长，所以我不想重复写它。 我也不想使用两个不同的单元格，因为我已经在10列上应用了这个公式，这意味着我应该添加额外的10列才能使其工作。

这是一个很好的解决方案，可以从VLOOKUP中删除0或NA。 "substitute(iferror(vlookup(),""),0,"")" 这个公式：如果vlookup有错误，则返回空白+将0更改为空白。 - pablorenato
之后我意识到，当文本中包含0或数字时，这是一个不好的解决方案！我不小心删除了数字！！ :( 如果你的文本中有数字，请不要使用这个！ - pablorenato
2
在某些情况下，MAX()或MIN()可以发挥出色的作用。
例如，像这样的内容： =IF(SUMIFSformula>0,SUMIFSformula, 0) 可以简化为： =MAX(0,SUMIFSformula)
1
自 Excel 2007 起，IFERROR 语句可以实现 OP 所要求的功能。根据帮助文件所述：返回您指定的值，如果公式评估为错误，则返回该公式的结果。[我强调]

语法：IFERROR(value, value_if_error)

我后来意识到这已经被 @barry houdini 回答过了。

“(SUMIFS formula)=0” 不一定会导致错误。实际上，它通常不会出错。 - GSerg
0

这里有一个技巧 - 取决于您是只对显示的值感兴趣，还是需要在另一个公式中使用该值：

将您的SUMIF公式放入单元格中（不包括IF部分）

创建一个条件格式规则，当单元格值为0时将字体颜色设置为背景颜色

嘿，你就得到了想要的结果。

正如我所说 - 这是一个技巧，但它确实可以防止双重评估。

据我所知，没有“干净”的解决方案。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接