Excel XML 电子表格文件条件格式中的数组公式？
Excel 通常将条件格式公式视为数组公式，除了从 Excel 2002/2003 XML 电子表格文件加载条件格式公式时除外。
这只是 Excel 2002/2003 XML 电子表格格式的问题...本机 Excel 格式可以正常工作，较新的 Excel 2007 XML 格式 (xlsx) 也可以正常工作。
加载电子表格后，可以通过选择格式化范围、转到“条件格式”对话框并单击“确定”来使其正常工作，但这只能解决会话的问题。
测试用例：
在新工作表中输入以下内容：
A B C
1 N N N
2 x x x
3 x x x在单元格 A1:C1 上创建此条件格式公式（您为格式选择漂亮的颜色）：
=(SUM(($A1:$C1="N")*($A$2:$C$2=A$3))>0)这是一个针对 A1、B1 激活的数组公式，和 C1 ，只要它们中的任何一个具有“N”，并且“N”下方的第 2 行中的单元格等于当前列的第 3 行中的单元格。
（这是从现实世界的业务电子表格中简化的。对于测试用例的复杂性，我很抱歉，我正在尝试找到一个更简单的测试用例在这里展示。）
并且它有效......您可以更改 N 或 x以您想要的任何方式，并且格式设置得很好。
将其另存为 XML 电子表格。 关闭 Excel，然后重新打开该文件。 格式现在已损坏。 现在，仅当 A1 为“N”并且 A2 与 A3、B3 或 C3 相同时，您才能激活条件格式。 B1、B2、C1 和 C2 的值对格式没有影响。
现在，选择 A1:C1 并查看条件格式公式。 和以前一模一样。 点击“确定”。 条件格式再次开始工作，并将在文件打开的整个会话期间工作。
考虑的解决方法：
以本机 (BIFF) Excel 格式提供文件。 不是一种选择，这些电子表格是由网络服务器动态生成的，这只是我们的系统动态生成的数十种类型的工作簿之一。
提供 Excel 2007 本机 XML 格式 (xlsx) 的文件。 不行，当前用户群没有 Office 2007 或兼容性插件。
要求用户选择范围，进入“条件格式”对话框，然后单击“确定”。 对于不熟练的用户来说，在这种情况下不是一个选项。
要求用户打开 XML 电子表格、另存为本机 XLS、关闭并重新打开 XLS 文件。 这不起作用！如果从 XML 文件加载损坏的原始 XLS 格式，则格式仍然损坏。 如果在保存之前执行上述 (3)，XLS 文件将正常工作。
我最终重写了条件格式以不使用数组公式。 所以我猜想这在某种程度上已经得到了“解答”，但它仍然是 Excel 2002/2003 处理 XML 文件中的一个未记录的、甚至是模糊的错误。
Excel usually treats Conditional Formatting formulas as if they are array formulas, except when loading them from an Excel 2002/2003 XML Spreadsheet file.
This is only an issue with the Excel 2002/2003 XML Spreadsheet format... the native Excel format works fine, as does the newer Excel 2007 XML format (xlsx).
After loading the spreadsheet, it is possible to make it work correctly by selecting the formatted range, going to the Conditional Formatting dialog, and clicking OK--but this only fixes the problem for the session.
Test case:
Enter the following into a new sheet:
A B C
1 N N N
2 x x x
3 x x xCreate this conditional format formula on cells A1:C1 (your choice of pretty colors for the format):
=(SUM(($A1:$C1="N")*($A$2:$C$2=A$3))>0)This is an array formula that activates for A1, B1, and C1 whenever any of them has an "N" and the cell in row 2 below the "N" is equal to the cell in row 3 of the current column.
(This has been simplified from a real-world business spreadsheet. Sorry for the complexity of the test case, I am trying to find an easier test case to present here.)
And it works... you can alter the N's or the x's in any way you want and the formatting works just fine.
Save this as an XML Spreadsheet. Close Excel, and re-open the file. Formatting is now broken. Now, you can only activate conditional formatting if A1 is an "N" and A2 is the same as A3, B3, or C3. The values of B1, B2, C1, and C2 have no effect on the formatting.
Now, select A1:C1 and look at the conditional formatting formula. Exactly the same as before. Hit OK. Conditional formatting starts working again, and will work during the entire session the file is open.
Workarounds considered:
Providing the file in native (BIFF) Excel format. Not an option, these spreadsheets are generated on the fly by a web server and this is only one of dozens of types of workbooks generated dynamically by our system.
Providing the file in the Excel 2007 native XML format (xlsx). Not an option, current user base does not have Office 2007 or the compatibility plug-in.
Asking users to select the range, enter the Conditional Formatting dialog, and hitting ok. Not an option in this case, unsophisticated users.
Asking users to open the XML spreadsheet, save as native XLS, close, and re-open the XLS file. This does not work! Formatting remains broken in the native XLS format if it was loaded broken from an XML file. If (3) above is performed before saving, the XLS file will work properly.
I ended up rewriting the conditional formatting to not use array formulas. So I guess this is "answered" to some degree, but it's still an undocumented, if obscure, bug in Excel 2002/2003's handling of XML files.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您可以在以下页面找到一些用于自学条件格式问题的教程视频：
条件格式
You can find some tutorial videos for self studying the conditional formatting issue over the following pages:
conditional formatting
我尝试重现您所描述的问题。 这是我发现的。
可以一致地重新创建
在 Windows 上使用 Excel 2003 的问题
另存为 XML 时的 XP
电子表格。
无法重现该问题
在 Windows XP 上使用 Excel 2003 时
另存为标准 xls
电子表格。
无法重现该问题
在 Windows Vista 上使用 Excel 2007
在本机中保存文件时
xlsx 格式。
无法重现该问题
在 Windows Vista 上使用 Excel 2007
将文件保存在 Excel 中时
97-2003 xls 格式。
（注意：Excel 和 Windows 的所有实例均已安装所有 Windows 更新。）
我还在每个测试中添加了一个简单的条件格式公式。 在每种情况下，保存文件、关闭 Excel 并重新打开文件后，它都会按预期工作。
所以答案似乎是在保存文件时使用标准的 Excel 2003 文件格式。
顺便说一句，这是一个非常奇怪的格式化公式。 很难想象你会如何使用它。 它必须是一个非常具体的& 不寻常的商业案例。 我也感觉你的帖子里缺少了一些东西。 （我并不是指责您不诚实 - 只是想知道您是否可能为了可读性而缩短了公式。）如果这不是您正在使用的准确公式，请使用完整的内容编辑您的原始帖子公式，我很乐意重新讨论这个问题。
I tried to recreate the problem you describe. Here is what I found.
Could consistently recreate the
problem using Excel 2003 on Windows
XP when saving as an XML
spreadsheet.
Could not reproduce the problem
using Excel 2003 on Windows XP when
saving as a standard xls
spreadsheet.
Could not reproduce the problem
using Excel 2007 on Windows Vista
when saving the file in the native
xlsx format.
Could not reproduce the problem
using Excel 2007 on Windows Vista
when saving the file in the Excel
97-2003 xls format.
(Note: All instances of Excel and Windows are current with all Windows updates.)
I also added a simple conditional formatting formula to each test. In every case, it worked as expected after saving the file, closing Excel, and reopening the file.
So the answer seems to be to use the standard Excel 2003 file format when saving the file.
BTW, this is a very odd formatting formula. It is difficult to imagine how you would use it. It must be a very specific & unusual business case. I also have the feeling something is missing in your post. (I'm not accusing you of being dishonest – just wondering if you may have shortened the formula for readability.) If this is not the exact formula you are using, please edit your original post with the complete formula and I will be happy to revisit this issue.