返回Excel公式中的空单元格

你必须使用 VBA ,然后。您将遍历范围内的单元格,测试条件,并删除匹配的内容。

类似:

For Each cell in SomeRange
  If (cell.value = SomeTest) Then cell.ClearContents
Next

Excel无法做到这一点。

Excel单元格中公式的结果必须是数字、文本、逻辑(布尔值)或错误。没有“空”或“空”的公式单元格值类型。

我在后面看到的一个实践是使用n a()和is n a(),但这可能解决您的问题,也可能解决不了您的问题,因为其他函数(sum(n a())处理n a()的方式存在很大差异(如果a1为空,则sum(a1)为0)。

是的,这是可能的。

在满足条件的情况下,可以对TrueBlank进行湮灭公式的计算。通过了isblank公式的测试。这似乎是不可能的技巧,我发现在收集弗兰肯斯泰姆(参考下文)。

设置3个步骤后,您将能够使用命名范围 GetTrueBlank ,正如您在回答中所希望的那样:

=IF(A1 = "Hello world", GetTrueBlank, A1)

步骤1。 将此代码放入VBA模块。

Function Delete_UDF(rng)
    ThisWorkbook.Application.Volatile
    rng.Value = ""
End Function

步骤2。 在 Sheet1 在里面 A1 单元格添加命名范围 获得真正的空白 用以下公式:

=EVALUATE("Delete_UDF("&CELL("address",Sheet1!A1)&")")

步骤3。 使用自湮灭公式。放进牢房,比如说 B2 ,公式如下:

=IF(A2=0,GetTrueBlank,A2)

上述公式 地下二层 如果在中键入0,将计算为TrueBlank A2 .

你可以 download a demonstration file here .

在上面的例子和Frankensteam的所有其他例子中,对TrueBlank的公式进行评估会导致一个空单元格。用isblank公式检查结果是正确的。这些是类似哈拉基里的公式。当条件满足时,公式从单元格中消失。虽然你可能希望公式不会消失,但目标已经实现。

您可以用这个公式进行实验,以在相邻的细胞中返回结果,而不是杀死它自己。玩得高兴!

以下是弗兰肯斯团队重新得出的一个公式结果——特鲁布兰克的例子: https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell

也许这是作弊,但它奏效了!

我还需要一个表格作为图表的源,我不希望任何空白或零单元格在图表上生成一个点。确实,您需要设置graph属性,选择data、hidden和empty cells以“将空单元格显示为间隙”(单击单选按钮)。这是第一步。

然后,在可能最终得到不希望绘制的结果的单元格中,将公式放入带有 NA() 结果如 =IF($A8>TODAY(),NA(), *formula to be plotted*)

当出现无效的单元格值时,这确实会给出不带点的所需图形。当然,这会使所有单元格都保留无效值,以便读取 #N/A 太乱了。

若要清除此项,请选择可能包含无效值的单元格,然后选择 条件格式 -新规则。选择“仅设置包含的单元格格式”,并在规则说明下从下拉框中选择“错误”。然后在“格式”下选择“字体-颜色-白色”(或任何背景颜色)。单击各个按钮退出,您应该看到具有无效数据的单元格看起来是空白的(它们实际上包含 αn/a 但是白色背景上的白色文本看起来是空白的。)然后链接的图表也不会显示无效的值点。

这就是我为使用的数据集所做的操作。这看起来既复杂又愚蠢,但它是学习如何使用上面提到的VB解决方案的唯一选择。

1. 我对所有数据进行了“复制”,并将数据粘贴为“值”。
2. 然后我突出显示粘贴的数据并进行了“替换”( Ctrl键 - H )我选择了带字母的空单元格 q 因为它不在我的数据表上。
3. 最后,我做了另一个“替换”,并替换了 Q 一无所有。

这三个步骤将所有的“空”单元格转换为“空”单元格。我尝试简单地用空白单元格替换空白单元格来合并步骤2和3,但这不起作用——我必须用某种实际文本替换空白单元格,然后用空白单元格替换该文本。

如果目标是能够在单元格的值为零时将其显示为空,则不要使用导致空白或空单元格的公式(因为没有 empty() 功能)相反,

• 如果需要空白单元格,请返回 0 而不是 "" 然后

• 像这样设置单元格的数字格式,在这里你必须想出你想要的正负数字(前两项用分号分隔)。在我的例子中,我得到的数字是0,1,2…我想让0空着出现。(我从来没有弄清楚文本参数的用途,但它似乎是必需的)。

  0;0;"";"text"@
  

尝试使用评估单元格 LEN . 如果它包含公式 伦恩 将返回 0 . 如果包含文本,则返回的值将大于 零 .

哇,有很多人误解了这个问题。很容易制造一个细胞 看 空的。问题是,如果需要单元格为空,Excel公式不能返回“无值”,而只能返回值。返回零、空格甚至“”是一个值。

因此,必须返回“magic value”,然后使用“search and replace”或使用VBA脚本将其替换为无值。虽然您可以使用空格或“”,但我的建议是使用一个明显的值,例如“nodate”或“nonumber”或“xxxxx”,这样您就可以很容易地看到它发生在哪里——在电子表格中很难找到“”。

如此多的答案返回的值看起来是空的,但实际上不是所请求的空as单元格…

根据要求,如果您实际需要返回空单元格的公式。它可以通过vba实现。所以,这里有这样做的代码。首先,编写一个公式,在希望单元格为空的任何位置返回n/a错误。然后,我的解决方案会自动清除包含该错误的所有单元格。当然,您可以修改代码,根据自己的喜好自动删除单元格的内容。

打开“Visual Basic查看器”(Alt+F11) 在Project Explorer中找到感兴趣的工作簿,然后双击它(或右键单击并选择“查看代码”)。这将打开“查看代码”窗口。在(常规)下拉菜单中选择“Workbook”,在(声明)下拉菜单中选择“SheetCalculate”。

将以下代码(基于J.T.Grimes的答案)粘贴到工作簿的sheetCalculate函数中

    For Each cell In Sh.UsedRange.Cells
        If IsError(cell.Value) Then
            If (cell.Value = CVErr(xlErrNA)) Then cell.ClearContents
        End If
    Next

将文件另存为启用宏的工作簿

注意:这个过程就像手术刀。它将删除任何计算为n/a错误的单元格的全部内容,因此请注意。它们会消失,如果你不重新输入它们原来包含的公式,你就无法取回它们。

NB2:显然,打开文件时需要启用宏,否则宏将不起作用,并且n/a错误将保持未删除状态

使用 COUNTBLANK(B1)>0 而不是 ISBLANK(B1) 在你的内心 IF 语句。

不像 ISBLANK() , COUNTBLANK() 考虑 "" 为空并返回1。

这个答案并不能完全解决操作问题,但有好几次我也遇到了类似的问题并寻找答案。

如果 你可以 再创造 如果需要的话,公式或数据(从您的描述看,如果您可以),那么当您准备运行需要空白单元格的部分时 空的 ,然后可以选择区域并运行以下VBA宏。

Sub clearBlanks()
    Dim r As Range
    For Each r In Selection.Cells
        If Len(r.Text) = 0 Then
            r.Clear
        End If
    Next r
End Sub

这将清除当前显示的任何单元格的内容 "" 或者只有一个公式

我使用以下方法使我的Excel看起来更清晰:

当您进行任何计算时,“”将给出错误,因此您希望将其视为数字,因此我使用嵌套的if语句返回0 istead of“”,然后如果结果为0,则此公式将返回

=IF((IF(A5="",0,A5)+IF(B5="",0,B5)) = 0, "",(IF(A5="",0,A5)+IF(B5="",0,B5)))

这样Excel工作表就看起来很干净…

如果使用查找函数(如hlookup和vlookup)将数据带到工作表中,请将函数放在括号内,函数将返回一个空单元格,而不是0。例如,

如果查找单元格为空,则返回零值:

    =HLOOKUP("Lookup Value",Array,ROW,FALSE)

如果查找单元格为空,则返回空单元格:

    =(HLOOKUP("Lookup Value",Array,ROW,FALSE))

我不知道这是否适用于其他功能…我还没试过。我正在使用Excel2007来实现这一点。

编辑

要使if(a1=,)恢复为真,需要在同一单元格中由&分隔两个查找。解决这个问题的简单方法是让第二次查找成为一个位于行末尾且始终为空的单元格。

到目前为止,这是我能想到的最好的办法。

它使用 ISBLANK 函数检查单元格是否在 IF 语句。 如果牢房里有什么东西, A1 在这个例子中,即使是空格字符, 那么这个牢房不是 EMPTY 然后计算结果。 这将防止计算错误显示,直到您有数字可供使用。

=IF(ISBLANK(A1)," ",STDEV(B5:B14))

答案是肯定的-您不能使用=if()函数并将单元格保留为空。”“看起来是空的”与“空的”不同。遗憾的是,两个引号背对背,如果不删除公式,就不会生成空单元格。

谷歌给我带来了一个非常相似的问题,我终于找到了一个适合我需要的解决方案,它也可能帮助其他人…

我用这个公式:

=IFERROR(MID(Q2, FIND("{",Q2), FIND("}",Q2) - FIND("{",Q2) + 1), "")

这似乎是一个简单的答案,但效果却被忽略了。

在电子表格中命名一个空单元格 BlankCell .

返回 空白细胞 在你的公式中,例如

=ISBLANK(IFNA(VLOOKUP(A2,SomeTable,2,False),BlankCell))

这个会回来的 TRUE 如果 ISBLANK(VLOOKUP(A2,SomeTable,2,False)) 是 真的 .