![在 Excel 中使用长 IF 值](https://linux22.com/image/1589925/%E5%9C%A8%20Excel%20%E4%B8%AD%E4%BD%BF%E7%94%A8%E9%95%BF%20IF%20%E5%80%BC.png)
我想编写一个非常简单的 IF 语句,其中包含一个非常长的值。
=IF(C2=1, "Please review the format of the plan; it needs to be cleaned up. Each learning outcome should be entered as a separate row. Each outcome can have multiple measures. Without this, it is difficult to demonstrate which outcomes align with which measure, benchmarks, findings, and changes. Please correct this as soon as possible, for the 2018-19 plan, so that a reviewer could potentially look in the system to decipher how these items align. The system is now open for such changes. This formatting issue likely made a fair evaluation of the 2017-18 assessment plan/report challenging, and would be equally challenging for an external reviewer to understand.", )
错误信息是
“公式中的文本值限制为 255 个字符。要在公式中创建更长的文本值,请使用 CONCATENATE 函数或连接运算符 (&)。“
我理解这个概念,只是不知道如何运用它。
答案1
您的消息超过 600 个字符。所以您收到错误。错误消息提供了解决方案,即使用 Concatenate 函数或 & 来规避 255 个字符的限制。
使用 Concatenate 划分您的信息。
Concatenate(text1,[text2],[text3],...)
你的公式将会是这样的:
=IF(C2=1,CONCATENATE("Please review the format of the plan; it needs to be cleaned up."," Each learning outcome should be entered as a separate row."," Each outcome can have multiple measures. Without this, it is difficult to demonstrate which outcomes align with which measure, benchmarks, findings, and changes."," Please correct this as soon as possible, for the 2018-19 plan,"," so that a reviewer could potentially look in the system to decipher how these items align. The system is now open for such changes."," This formatting issue likely made a fair evaluation of the 2017-18 assessment plan/report challenging, and would ","be equally challenging for an external reviewer to understand"),)
答案2
=IF(C2=1,=Z1&Z2&Z3)
将单元格 Z1、Z2 和 Z3 的内容合并在一起。使用与号“&”合并的单元格数量不受限制,并且单元格或值之间无需留空格(但您可以使用 分隔单元格内容)
=IF(C2=1,=Z1&" "&Z2&" "&Z3)
。
=IF(C2=1,CONCATENATE(cell1, " " ,cell2, " " ,cell 3, " " ... " " ,cell255))
可以代替上面带有与号“&”的表达式,但仅限于汇集 255 个单元格的内容,并且每个元素之间需要有空格。
不同版本的 Excel 对每个单元格的字符数限制不同;通常为 255。由于您的短语长度为 656 个字符,因此您可能需要三 (3) 个单元格来容纳要连接的所有文本。
为了便于知道从哪里将短语拆分成三个部分,Microsoft Word 提供了一种计算字符数的方法。将长短语放入一个空白的 Word 文档中,然后单击底部状态栏中的“字数”指标。将弹出一个“字数”对话框,并显示文件摘要,其中包括该文件的页数、字数、字符数(带空格和不带空格)、段落和行数。