我需要从Excel公式返回一个空单元格,但看起来Excel处理空字符串或对空单元格的引用与真正的空单元格不同 . 所以基本上我需要类似的东西
=IF(some_condition,EMPTY(),some_value)
我试着做一些事情
=IF(some_condition,"",some_value)
和
=IF(some_condition,,some_value)
并假设B1是一个空单元格
=IF(some_condition,B1,some_value)
但这些似乎都不是真正的空单元格,我猜是因为它们是公式的结果 . 当且仅当满足某些条件并且保持单元格真正为空时,是否有任何方法可以填充单元格?
编辑:按照建议,我试图返回NA(),但为了我的目的,这也没有用 . 有没有办法用VB做到这一点?
编辑:我正在构建一个工作表,从其他工作表中提取数据,格式化为将数据导入数据库的应用程序的特定需求 . 我没有权限更改此应用程序的实现,如果值为“”而不是实际为空,则失败 .
17 回答
那你将不得不使用
VBA
. 您将遍历范围内的单元格,测试条件,并删除匹配的内容 .就像是:
Excel没有任何方法可以执行此操作 .
Excel中单元格中公式的结果必须是数字,文本,逻辑(布尔值)或错误 . 没有公式单元格值类型“空”或“空白” .
我所看到的一种做法是使用NA()和ISNA(),但这可能会或可能不会真正解决您的问题,因为NA()被其他函数处理的方式存在很大差异(SUM(NA) ))是#N / A,而如果A1为空则SUM(A1)为0) .
对的,这是可能的 .
如果满足条件,则可以使湮灭公式评估为真实空白 . 它通过了ISBLANK公式的测试 . 我在FrankensTeam的集合中找到了这个看似不可能的技巧(参见下文) .
设置3个步骤后,您可以在答案中使用命名范围
GetTrueBlank
:Step 1. 将此代码放在VBA模块中 .
关于VBA代码的两个评论 .
不要被_937350误导_命名的愤怒
GetTrueBlank
最终会成为一个真正的空白,而不是像双引号=""
那样的空字符串 .在我的测试中,FrankensTeam最初使用的第一行代码
ThisWorkbook.Application.Volatile
被证明是不必要的 .Step 2. 在
Sheet1
A1
单元格中,使用以下公式添加命名范围 GetTrueBlank :Step 3. 使用自毁消式公式 . 放入单元格,说
B2
,以下公式:如果在
A2
中键入0,则B2
中的上述公式将计算为trueblank .你可以 download a demonstration file here .
在上面的示例和FrankensTeam的所有其他示例中,将公式计算为trueblank会导致空单元格 . 使用ISBLANK公式检查结果结果为TRUE . 这些是hara-kiri喜欢的公式 . 当满足条件时,公式从单元格中消失 . 虽然问题的作者可能希望公式不会消失,但目标已达成 .
请注意,可能会修改这些示例以在相邻单元格中生成结果,而不是自杀 .
我已经通过这些例子得到了一个真实的空白作为一个公式结果由FrankensTeam重新审视:https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell
也许这是作弊,但它的确有效!
我还需要一个表格作为图表的来源,我不希望任何空白或零单元格在图形上产生一个点 . 确实,您需要设置图形属性,选择数据,隐藏和清空单元格以“将空单元格显示为间隙”(单击单选按钮) . 这是第一步 .
然后在可能最终得到您不想绘制的结果的单元格中,将公式放在带有
NA()
结果的IF语句中,例如=IF($A8>TODAY(),NA(), *formula to be plotted*)
当无效单元格值出现时,这确实为所需图形提供了无点 . 当然,这会使所有具有无效值的单元格读取
#N/A
,这很麻烦 .要清除它,请选择可能包含无效值的单元格,然后选择条件格式 - 新规则 . 选择'format only cells that contain'并在规则说明下从下拉框中选择'errors' . 然后在格式下选择字体 - 颜色 - 白色(或任何你的背景颜色) . 单击各个按钮以退出,您应该看到具有无效数据的单元格看起来为空白(它们实际上包含
#N/A
但白色背景上的白色文本看起来是空白的 . )然后链接图形也不显示无效值点 .这就是我为我使用的数据集做的 . 它似乎令人费解和愚蠢,但它是学习如何使用上述VB解决方案的唯一选择 .
我做了"copy"的所有数据,并将数据粘贴为"values" .
然后我突出显示了粘贴的数据并用一些字母做了"replace"(Ctrl-H)空单元格,我选择
q
,因为它不在我的任何地方数据表 .最后,我做了另一个"replace",并取而代之的是
q
.这个三步过程将所有“空”细胞变成“空白”细胞“ . 我尝试通过简单地用空白细胞替换空白细胞来合并步骤2和3,但这不起作用 - 我不得不更换带有某种实际文本的空白单元格,然后用空白单元格替换该文本 .
如果目标是当它实际上具有零值时能够将单元格显示为空,则代替使用导致空单元格或空单元格的公式(因为没有
empty()
函数),你想要一个空白单元格,返回
0
而不是""
然后返回设置像这样的单元格的数字格式,在这里你必须得到你想要的正数和负数(前两个项目用分号分隔) . 在我的情况下,我的数字是0,1,2 ......我希望0显示为空 . (我从未弄清楚文本参数的用途,但似乎是必需的) .
在
IF
语句中使用COUNTBLANK(B1)>0
而不是ISBLANK(B1)
.与
ISBLANK()
不同,COUNTBLANK()
认为""
为空并返回1 .尝试使用
LEN
评估单元格 . 如果它包含公式LEN
将返回0
. 如果它包含文本,则返回大于0
.哇,很多人误解了这个问题 . 让单元格看起来很空洞很容易 . 问题是,如果您需要单元格为空,Excel公式不能返回"no value"但只能返回一个值 . 返回零,空格或甚至“”是一个值 .
因此,您必须返回“魔术值”,然后使用搜索和替换或使用VBA脚本将其替换为无值 . 虽然你可以使用空格或“”,但我的建议是使用一个明显的值,例如“NODATE”或“NONUMBER”或“XXXXX”,这样你就可以很容易地看到它出现的位置 - 很难找到“”在电子表格中 .
如此多的答案返回一个LOOKS为空的值,但实际上并不像请求的那样是一个空的...
根据要求,如果您确实需要一个返回空单元格的公式 . 这可以通过VBA实现 . 所以,这里的代码就是这样做的 . 首先编写一个公式,以便在您希望单元格为空的任何位置返回#N / A错误 . 然后我的解决方案会自动清除包含#N / A错误的所有单元格 . 当然,您可以修改代码,根据您喜欢的任何内容自动删除单元格的内容 .
打开“visual basic viewer”(Alt F11)在项目资源管理器中找到感兴趣的工作簿,然后双击它(或右键单击并选择视图代码) . 这将打开“视图代码”窗口 . 在(常规)下拉菜单中选择“工作簿”,在(声明)下拉菜单中选择“SheetCalculate” .
在Workbook_SheetCalculate函数中粘贴以下代码(基于J.T.Grimes的答案)
将文件另存为启用宏的工作簿
注意:这个过程就像一把手术刀 . 它将删除评估为#N / A错误的任何单元格的全部内容,因此请注意 . 他们会去,你不能重新进入他们曾经包含的公式 .
NB2:显然你需要在打开文件时启用宏,否则它将无法工作,#N / A错误将保持未删除状态
这个答案并没有完全处理OP,但有几次我遇到了类似的问题,并寻找答案 .
If 你可以 recreate 公式或数据如果需要(从你的描述看起来好像你可以),然后当你准备好运行需要空白单元格的部分实际 empty ,那么你可以选择区域和运行以下vba宏 .
这将消除当前显示
""
或仅具有公式的任何单元格的内容我使用了以下工作来使我的excel看起来更干净:
当你进行任何计算时,“”会给你错误,所以你想把它当成一个数字,所以我用一个嵌套的if语句返回0而不是“”,然后如果结果为0,这个等式将返回“”
这样excel表看起来很干净......
如果您使用查找函数(如HLOOKUP和VLOOKUP)将数据放入工作表中,则将函数放在括号内,函数将返回空单元而不是{0} . 例如,
如果查找单元格为空,这将返回零值:
如果查找单元格为空,这将返回一个空单元格:
我不知道这是否适用于其他功能......我还没试过 . 我使用Excel 2007来实现这一目标 .
编辑
要实际获得一个IF(A1 =“”,)以回归为真,需要在由&分隔的同一个单元格中进行两次查找 . 解决这个问题的简单方法是使第二个查找成为行末的单元格,并始终为空 .
那么到目前为止这是最好的我能拿出来 .
它使用
ISBLANK
函数检查IF
语句中的单元格是否为空 . 如果单元格中有任何内容,在此示例中为A1
,即使是空格字符 `` ,则单元格不是EMPTY
,将导致计算结果 . 这将使计算错误无法显示,直到您有数字可供使用 .如果单元格为
EMPTY
,则计算单元格将不显示计算中的错误 . 如果单元格为NOT EMPTY
,则将显示计算结果 . 如果您的数据不好,这会引发错误,可怕的#DIV/0
!根据需要更改单元格引用和公式 .
答案是肯定的 - 您不能使用= IF()函数并将单元格留空 . “看起来空”与空不一样 . 令人遗憾的是,两个引号背靠背不会产生空单元而不会消除公式 .
Google给我带来了一个非常类似的问题,我终于想出了一个符合我需求的解决方案,它也可以帮助别人......
我用这个公式:
这似乎是一个简单的答案,工作已被遗漏 .
在电子表格
BlankCell
中命名一个空单元格 .在公式中返回
BlankCell
,例如如果
ISBLANK(VLOOKUP(A2,SomeTable,2,False))
是TRUE
,这将返回TRUE
.