从 Excel 中的公式返回空单元格
我需要从 Excel 公式返回一个空单元格，但 Excel 处理空字符串或对空单元格的引用的方式似乎与真正的空单元格不同。 所以本质上我需要像
=IF(some_condition,EMPTY(),some_value)
我尝试做诸如
=IF(some_condition,"",some_value)
and
=IF(some_condition,,some_value)
之类的事情，并假设 B1 是一个空单元格
=IF(some_condition,B1,some_value)
，但这些似乎都不是真正的空单元格，我猜测是因为它们是公式的结果。 当且仅当满足某些条件时，是否有任何方法可以填充单元格，否则使单元格真正为空？
编辑：按照建议，我尝试返回 NA()，但出于我的目的，这也不起作用。 有没有办法用VB来做到这一点？
编辑：我正在构建一个工作表，该工作表从其他工作表中提取数据，该工作表的格式根据将数据导入数据库的应用程序的非常具体的要求而定。 我无权更改此应用程序的实现，如果该值为“”而不是实际为空，则会失败。
I need to return an empty cell from an Excel formula, but it appears that Excel treats an empty string or a reference to an empty cell differently than a true empty cell. So essentially I need something like
=IF(some_condition,EMPTY(),some_value)
I tried to do things such as
=IF(some_condition,"",some_value)
and
=IF(some_condition,,some_value)
and assuming B1 is an empty cell
=IF(some_condition,B1,some_value)
but none of these appear to be true empty cells, I'm guessing because they are the result of a formula. Is there any way to populate a cell if and only if some condition is met and otherwise keep the cell truly empty?
EDIT: as recommended, I tried to return NA(), but for my purposes this did not work either. Is there a way to do this with VB?
EDIT: I am building a worksheet that pulls in data from other worksheets that is formatted to the very specific demands of an application that imports the data into a database. I do not have access to change the implementation of this application, and it fails if the value is "" instead of actually empty.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（21）
在
IF语句中使用COUNTBLANK(B1)>0而不是ISBLANK(B1)。与
ISBLANK()不同，COUNTBLANK()将""视为空并返回 1。Use
COUNTBLANK(B1)>0instead ofISBLANK(B1)inside yourIFstatement.Unlike
ISBLANK(),COUNTBLANK()considers""as empty and returns 1.哇，有很多人误读了这个问题。 很容易让单元格看起来为空。 问题是，如果您需要单元格为空，Excel 公式不能返回“无值”，而只能返回一个值。 返回零、空格，甚至“”都是一个值。
因此，您必须返回一个“神奇值”，然后使用搜索和替换或使用 VBA 脚本将其替换为无值。 虽然您可以使用空格或“”，但我的建议是使用明显的值，例如“NODATE”或“NONUMBER”或“XXXXX”，以便您可以轻松地看到它发生的位置 - 很难找到“”在电子表格中。
Wow, an amazing number of people misread the question. It's easy to make a cell look empty. The problem is that if you need the cell to be empty, Excel formulas can't return "no value" but can only return a value. Returning a zero, a space, or even "" is a value.
So you have to return a "magic value" and then replace it with no value using search and replace, or using a VBA script. While you could use a space or "", my advice would be to use an obvious value, such as "NODATE" or "NONUMBER" or "XXXXX" so that you can easily see where it occurs - it's pretty hard to find "" in a spreadsheet.
尝试使用
LEN评估单元格。 如果它包含公式LEN将返回0。 如果它包含文本，它将返回大于0的值。Try evaluating the cell using
LEN. If it contains a formulaLENwill return0. If it contains text it will return greater than0.如此多的答案返回一个看起来为空的值，但实际上并不是所要求的空单元格...
根据要求，如果您实际上想要一个返回空单元格的公式。 通过 VBA 可以实现。 所以，这里的代码就是用来完成这个任务的。 首先编写一个公式，在您希望单元格为空的任何位置返回 #N/A 错误。 然后我的解决方案自动清除包含 #N/A 错误的所有单元格。 当然，您可以修改代码以根据您喜欢的任何内容自动删除单元格的内容。
打开“Visual Basic 查看器”（Alt + F11）
在项目资源管理器中找到感兴趣的工作簿并双击它（或右键单击并选择查看代码）。 这将打开“查看代码”窗口。 在（常规）下拉菜单中选择“工作簿”，在（声明）下拉菜单中选择“SheetCalculate”。
将以下代码（基于 JT Grimes 的答案）粘贴到 Workbook_SheetCalculate 函数中
将文件保存为启用宏的工作簿
注意：此过程就像手术刀。 它将删除任何计算为 #N/A 错误的单元格的全部内容，因此请注意。 它们会消失，如果不重新输入它们曾经包含的配方，您就无法将它们取回。
NB2：显然，您需要在打开文件时启用宏，否则它将无法工作，并且 #N/A 错误将保持未删除状态
So many answers that return a value that LOOKS empty but is not actually an empty as cell as requested...
As requested, if you actually want a formula that returns an empty cell. It IS possible through VBA. So, here is the code to do just exactly that. Start by writing a formula to return the #N/A error wherever you want the cells to be empty. Then my solution automatically clears all the cells which contain that #N/A error. Of course you can modify the code to automatically delete the contents of cells based on anything you like.
Open the "visual basic viewer" (Alt + F11)
Find the workbook of interest in the project explorer and double click it (or right click and select view code). This will open the "view code" window. Select "Workbook" in the (General) dropdown menu and "SheetCalculate" in the (Declarations) dropdown menu.
Paste the following code (based on the answer by J.T. Grimes) inside the Workbook_SheetCalculate function
Save your file as a macro enabled workbook
NB: This process is like a scalpel. It will remove the entire contents of any cells that evaluate to the #N/A error so be aware. They will go and you cant get them back without reentering the formula they used to contain.
NB2: Obviously you need to enable macros when you open the file else it won't work and #N/A errors will remain undeleted
我使用的是一个小技巧。
我使用 T(1)，它返回一个空单元格。
T 是 Excel 中的一个函数，如果它是字符串，则返回其参数，否则返回空单元格。 所以，你能做的是：
What I used was a small hack.
I used T(1), which returned an empty cell.
T is a function in excel that returns its argument if its a string and an empty cell otherwise. So, what you can do is:
这个答案并没有完全解决OP，但我已经好几次遇到类似的问题并寻找答案。
如果您可以重新创建必要时的公式或数据（从您的描述来看，您似乎可以），然后当您准备好运行要求空白单元格实际上为空的部分时，您可以选择该区域并运行以下 vba 宏。
这将清除当前显示
""或仅包含公式的任何单元格的内容This answer does not fully deal with the OP, but there are have been several times I have had a similar problem and searched for the answer.
If you can recreate the formula or the data if needed (and from your description it looks as if you can), then when you are ready to run the portion that requires the blank cells to be actually empty, then you can select the region and run the following vba macro.
this will wipe out off of the contents of any cell which is currently showing
""or has only a formula我使用以下解决方法使我的 Excel 看起来更干净：
当您进行任何计算时，“”会给您错误，因此您想将其视为数字，因此我使用嵌套的 if 语句返回 0 而不是“”，并且那么如果结果是 0 这个方程将返回 ""
这样 excel 工作表就会看起来很干净......
I used the following work around to make my excel looks cleaner:
When you make any calculations the "" will give you error so you want to treat it as a number so I used a nested if statement to return 0 istead of "", and then if the result is 0 this equation will return ""
This way the excel sheet will look clean...
答案是肯定的 - 您不能使用 =IF() 函数并将单元格留空。 “看起来空”并不等于空。 遗憾的是，两个连续的引号不会产生一个空单元格而不擦除公式。
The answer is positively - you can not use the =IF() function and leave the cell empty. "Looks empty" is not the same as empty. It is a shame two quotation marks back to back do not yield an empty cell without wiping out the formula.
到目前为止，这是我能想到的最好的办法。
它使用
ISBLANK函数检查IF语句中的单元格是否确实为空。如果单元格中有任何内容（本例中为
A1），即使是空格字符，则该单元格不是EMPTY，并且计算结果为。这将防止显示计算错误，直到您有数字可以使用。
如果单元格为
EMPTY，则计算单元格将不会显示计算错误。如果单元格为NOT EMPTY，则将显示计算结果。如果您的数据不好，这将引发错误，即可怕的
#DIV/0！根据需要更改单元格引用和公式。
Well so far this is the best I could come up with.
It uses the
ISBLANKfunction to check if the cell is truly empty within anIFstatement.If there is anything in the cell,
A1in this example, even a SPACE character,then the cell is notEMPTYand the calculation will result.This will keep the calculation errors from showing until you have numbers to work with.
If the cell is
EMPTYthen the calculation cell will not display the errors from the calculation.If the cell isNOT EMPTYthen the calculation results will be displayed.This will throw an error if your data is bad, the dreaded
#DIV/0!Change the cell references and formula as you need to.
如果您像我一样，在一个空单元格之后，以便单元格中的文本可以溢出到相邻单元格，请返回
""但将单元格格式文本方向设置为旋转几度。 如果左对齐，您会发现这会导致文本溢出到相邻的单元格，就好像该单元格为空一样。请参阅之前和之后：
注意，空单元格不是空的，而是包含
""，但文本仍然可能溢出。If you are, like me, after an empty cell so that the text in a cell can overflow to an adjacent cell, return
""but set the cell format text direction to be rotated by a few degrees. If you align left, you will find this causes the text to spill to an adjacent cell as if that cell were empty.See before and after:
Note the empty cells are not empty but contain
"", yet the text can still spill.如果您使用 HLOOKUP 和 VLOOKUP 等查找函数将数据带入工作表，请将函数放在括号内，函数将返回空单元格而不是 {0}。 例如，
如果查找单元格为空，这将返回零值：
如果查找单元格为空，这将返回一个空单元格：
我不知道这是否适用于其他函数...我还没有尝试过。 我正在使用 Excel 2007 来实现此目的。
编辑
要真正获得 IF(A1="", , ) 作为 true 返回，需要在同一单元格中进行两次查找，并用 & 分隔。 解决此问题的简单方法是将第二次查找设置为位于行末尾且始终为空的单元格。
If you are using lookup functions like HLOOKUP and VLOOKUP to bring the data into your worksheet place the function inside brackets and the function will return an empty cell instead of a {0}. For Example,
This will return a zero value if lookup cell is empty:
This will return an empty cell if lookup cell is empty:
I don't know if this works with other functions...I haven't tried. I am using Excel 2007 to achieve this.
Edit
To actually get an IF(A1="", , ) to come back as true there needs to be two lookups in the same cell seperated by an &. The easy way around this is to make the second lookup a cell that is at the end of the row and will always be empty.
我删除了单引号，因此电话号码列（例如 +1-800-123-4567）不会导致计算并产生负数。
我尝试在空单元格上删除它们，禁止引用，然后也遇到了这个问题（F 列）。 在源单元格上调用文本要容易得多，瞧！：
I was stripping out single quotes so a telephone number column such as +1-800-123-4567 didn't result in a computation and yielding a negative number.
I attempted a hack to remove them on empty cells, bar the quote, then hit this issue too (column F). It's far easier to just call text on the source cell and voila!:
这可以在 Excel 中完成，无需使用将 #N/A 设置为间隙的新图表功能。 但它很繁琐。 假设您想在 XY 图表上绘制线条。 然后：
第 1 行：点 1
第 2 行：第 2 点
第 3 行：硬空
第 4 行：第 2 点
第 5 行：第 3 点
第 6 行：硬空
第 7 行：第 3 点
第 8 行：第 4 点
第 9 行：硬空
等等
结果是很多单独的行。 点的公式可以控制是否省略#N/A。 通常，将点 INDEX() 转换为另一个范围的公式。
This can be done in Excel, without using the new chart feature of setting #N/A to be a gap. But it’s fiddly. Let’s say that you want to make line on an XY chart. Then:
Row 1: point 1
Row 2: point 2
Row 3: hard empty
Row 4: point 2
Row 5: point 3
Row 6: hard empty
Row 7: point 3
Row 8: point 4
Row 9: hard empty
etc
The result is a lot of separate lines. The formula for the points can control whether omitted by a #N/A. Typically the formulae for the points INDEX() into another range.
我还通过Google找到了这一点，并找到了一个解决方案，不是返回一个真正的空值（因为这是不可能完成的），也不是通过VBA（因为基于网络的Excel365不支持VB），而是通过
IFERROR在下游函数中处理。我自己的问题是在一系列公式单元格上进行数组操作，其中只有一个子集是“活动的”。 该范围内的每个单元格如果处于活动状态，则返回一个数值，否则返回一个
""空字符串，例如，每个单元格中都有一些来自我的单元格AK32的公式：= IF(ISNUMBER(AG32), MAX(AG32,MIN(12,AI32)), "")-- 请不要批评这一点，它是真实版本的说明性最小版本。问题是，如果我随后聚合第 32 行中的那些条件计算单元格，并尝试对它们求和，如果有任何
""结果，我会收到#VALUE!错误清空例如AG32中的可选输入。 如果它们是字面上的空单元格，那就没问题，但""既不是空的也不是数字的。这个问题可以在我的下游聚合函数中通过
VALUE()和ISERROR()的组合来处理：尝试将范围转换/转换为为数值，捕获错误情况，并将其替换为零：= SUM( IFERROR(VALUE(AK32:OQ32),0) )。 [为了预期“有用”的评论，我省略了一堆从AK32:OQ32范围中挑选正确单元格的FILTER()内容。 就好像我真的想对连续范围内的所有内容求和一样。]我渴望一些更现代的、Numpy/Pandas 类型的数组操作语言可以在（网络）Excel 公式中使用，但至少这个是可能的。
I also found this through Google, and found a solution not in returning a true null value (because that can't be done) and not via VBA (because the web-based Excel365 doesn't support VB) but via
IFERRORhandling in the downstream function.My own issue was in array operations over a range of formula cells where only a subset were "active". Each cell in the range returned a numeric value if it was active, and a
""empty string otherwise, e.g. each having some formula like this from my cellAK32in it:= IF(ISNUMBER(AG32), MAX(AG32,MIN(12,AI32)), "")-- please don't critique this, it's an illustrative minimal version of the real one.The problem is that if I then aggregate those conditionally calculated cells in row 32, and try to sum them, I get
#VALUE!errors should there be any""results due to empty optional inputs in e.g.AG32. If they'd been literal empty cells, that would have been fine, but a""is neither empty nor numeric.This issue can be handled in my downstream aggregation function by combination of
VALUE()andISERROR(): try to convert/cast the range to be numeric-valued, catch the error-cases, and replace them with zeroes:= SUM( IFERROR(VALUE(AK32:OQ32),0) ). [To anticipate "helpful" comments, I've elided a bunch ofFILTER()stuff that picks the right cells out of thatAK32:OQ32range. Treat it as if I really want to sum everything in that contiguous range.]I pine for some more modern, Numpy/Pandas type of array-operation language to be usable in (web) Excel formulae, but at least this one is possible.
谷歌给我带来了一个非常相似的问题，我终于找到了一个适合我的需求的解决方案，它也可能对其他人有帮助......
我使用了这个公式：
Google brought me here with a very similar problem, I finally figured out a solution that fits my needs, it might help someone else too...
I used this formula:
Excel 没有任何方法可以做到这一点。
Excel 单元格中公式的结果必须是数字、文本、逻辑（布尔）或错误。 没有“空”或“空白”的公式单元格值类型。
我看到的一种做法是使用 NA() 和 ISNA()，但这可能会也可能不会真正解决您的问题，因为其他函数处理 NA() 的方式存在很大差异（SUM(NA( )) 为#N/A，而如果 A1 为空，SUM(A1) 为 0）。
Excel does not have any way to do this.
The result of a formula in a cell in Excel must be a number, text, logical (boolean) or error. There is no formula cell value type of "empty" or "blank".
One practice that I have seen followed is to use NA() and ISNA(), but that may or may not really solve your issue since there is a big differrence in the way NA() is treated by other functions (SUM(NA()) is #N/A while SUM(A1) is 0 if A1 is empty).
那么您将必须使用
VBA。 您将迭代范围内的单元格，测试条件，并删除匹配的内容。就像是：
You're going to have to use
VBA, then. You'll iterate over the cells in your range, test the condition, and delete the contents if they match.Something like:
对的，这是可能的。
如果满足条件，则公式可以返回 trueblank。 它通过了
ISBLANK公式的测试。 唯一的不便之处在于，当满足条件时，公式将消失，您将不得不重新输入它。 您可以设计一个免于自毁的公式，使其将结果返回到相邻单元格。 是的，这也是可能的。您所需要的只是设置一个命名范围，例如
GetTrueBlank，然后您将能够使用以下模式，就像您的问题中一样：第 1 步。 将此代码放入 VBA 的模块中。
第 2 步。 在
A1单元格的Sheet1中添加命名范围GetTrueBlank，其中包含以下内容公式：就是这样。 没有进一步的步骤。 只需使用自毁式即可。 在单元格（例如
B2）中输入以下公式：如果在
A2中键入 0，则B2中的上述公式将计算为 trueblank。您可以 在此处下载演示文件。 它在 Google 表格中打开，但在那里不起作用。 首先下载它以便在 Excel 中使用 xlsm 文件。
在上面的示例中，将公式计算为 trueblank 会产生空单元格。 使用
ISBLANK公式检查单元格结果为 TRUE。 这就是切腹。 当满足条件时，公式将从单元格中消失。 目标已经达到，尽管您可能不希望公式消失。您可以修改公式以将结果返回到相邻单元格中，这样公式就不会自行终止。 了解如何在相邻单元格中获取 UDF 结果。
Yes, it is possible.
It is possible to have a formula returning a trueblank if a condition is met. It passes the test of the
ISBLANKformula. The only inconvenience is that when the condition is met, the formula will evaporate, and you will have to retype it. You can design a formula immune to self-destruction by making it return the result to the adjacent cell. Yes, it is also possible.All you need is to set up a named range, say
GetTrueBlank, and you will be able to use the following pattern just like in your question:Step 1. Put this code in Module of VBA.
Step 2. In
Sheet1inA1cell add named rangeGetTrueBlankwith the following formula:That's it. There are no further steps. Just use self-annihilating formula. Put in the cell, say
B2, the following formula:The above formula in
B2will evaluate to trueblank, if you type 0 inA2.You can download a demonstration file here. It opens in Google Sheets and will not work there. Download it first to get xlsm file work in Excel.
In the example above, evaluating the formula to trueblank results in an empty cell. Checking the cell with
ISBLANKformula results positively in TRUE. This is hara-kiri. The formula disappears from the cell when a condition is met. The goal is reached, although you probably might want the formula not to disappear.You may modify the formula to return the result in the adjacent cell so that the formula will not kill itself. See how to get UDF result in the adjacent cell.
也许这是作弊，但它确实有效！
我还需要一个作为图表来源的表格，并且我不希望任何空白或零单元格在图表上生成点。 确实，您需要设置图形属性，选择数据、隐藏单元格和空单元格以“将空单元格显示为间隙”（单击单选按钮）。 这是第一步。
然后，在可能不希望绘制结果的单元格中，将公式放入带有
NA()结果的 IF 语句中，例如=IF($A8>; TODAY(),NA(), *要绘制的公式*)当出现无效单元格值时，这确实会给出没有点的所需图形。 当然，这会使所有具有该无效值的单元格无法读取
#N/A，这很混乱。要清除此问题，请选择可能包含无效值的单元格，然后选择条件格式 - 新规则。 选择“仅设置包含以下内容的单元格格式”，然后在规则说明下从下拉框中选择“错误”。 然后在格式下选择字体 - 颜色 - 白色（或任何背景颜色）。 单击各种按钮退出，您应该看到包含无效数据的单元格看起来是空白的（它们实际上包含
#N/A，但白色背景上的白色文本看起来是空白的。）然后链接的图表也显示为空白。不显示无效值点。Maybe this is cheating, but it works!
I also needed a table that is the source for a graph, and I didn't want any blank or zero cells to produce a point on the graph. It is true that you need to set the graph property, select data, hidden and empty cells to "show empty cells as Gaps" (click the radio button). That's the first step.
Then in the cells that may end up with a result that you don't want plotted, put the formula in an IF statement with an
NA()results such as=IF($A8>TODAY(),NA(), *formula to be plotted*)This does give the required graph with no points when an invalid cell value occurs. Of course this leaves all cells with that invalid value to read
#N/A, and that's messy.To clean this up, select the cell that may contain the invalid value, then select conditional formatting - new rule. Select 'format only cells that contain' and under the rule description select 'errors' from the drop down box. Then under format select font - colour - white (or whatever your background colour happens to be). Click the various buttons to get out and you should see that cells with invalid data look blank (they actually contain
#N/Abut white text on a white background looks blank.) Then the linked graph also does not display the invalid value points.如果目标是能够在单元格的值实际上为零时将其显示为空，则不要使用导致空白或空单元格的公式（因为没有
empty()函数），在您想要空白单元格的地方，返回
0而不是""然后设置单元格的数字格式，如下所示，您必须在其中想出您想要的正数和负数（前两项用分号分隔）。 就我而言，我拥有的数字是 0、1、2...，我希望 0 显示为空。 （我从来没有弄清楚文本参数的用途，但它似乎是必需的）。
<前><代码>0;0;"";"文本"@
If the goal is to be able to display a cell as empty when it in fact has the value zero, then instead of using a formula that results in a blank or empty cell (since there's no
empty()function) instead,where you want a blank cell, return a
0instead of""and THENset the number format for the cells like so, where you will have to come up with what you want for positive and negative numbers (the first two items separated by semi-colons). In my case, the numbers I had were 0, 1, 2... and I wanted 0 to show up empty. (I never did figure out what the text parameter was used for, but it seemed to be required).
这就是我对我正在使用的数据集所做的事情。 这看起来很复杂而且很愚蠢，但它是学习如何使用上述 VB 解决方案的唯一选择。
q，因为它我的数据表上没有任何地方。q替换为空。这三个步骤的过程将所有“空”单元格变成“空白”单元格”。我尝试通过简单地将空白单元格替换为空白单元格来合并步骤 2 和 3，但这不起作用 - 我必须替换空白单元格包含某种实际文本，然后将该文本替换为空白单元格。
This is how I did it for the dataset I was using. It seems convoluted and stupid, but it was the only alternative to learning how to use the VB solution mentioned above.
qsince it wasn't anywhere on my data sheet.qwith nothing.This three step process turned all of the "empty" cells into "blank" cells". I tried merging steps 2 & 3 by simply replacing the blank cell with a blank cell, but that didn't work--I had to replace the blank cell with some kind of actual text, then replace that text with a blank cell.