Open XML Excel共享公式不支持某些公式 [英] Open XML Excel some formulas are not supported by shared formula
问题描述
Excel doesn't support the shared formulas (created by the OpenXML SDK https://www.microsoft.com/en-us/download/details.aspx?id=30425) as expected:
- 具有对图纸的引用但不使用绝对引用的公式(例如"= VLOOKUP(A1; Sheet2！A:B; 2; 0)")
- 使用结构化引用的公式，例如[@ [销售金额]](在打开或保存文件时会引发错误，有时会出现以下错误:一个或多个公式...超出了允许的8192个字符的限制"
provoke errors when the file is opened or saved, sometimes with the following error: "one or more formula ... are longer than the allowed limit of 8192 characters"
另一方面，以下共享公式可以正常工作:
On the other hand, the following shared formulas work correctly:
- "= VLOOKUP(A1; Sheet2！$ A:$ B; 2; 0)"(使用带美元的绝对引用).
- "= A1"或"= $ A1"(在将公式扩展到整个列时)
XML结构如下:
B2: <c r="B2"><f t="shared" ref="B2:B3" si="0">VLOOKUP(A2,Sheet2!A:B,2,0)</f><v></v></c> B3: <c r="B3"><f t="shared" si="0"></f><v></v></c>用美元代替一个公式，它将起作用.通过结构化引用，不会，等等.
Replace the formula by the one with dollars, it will work. By a structured references, it won't, etc.
通过Excel UI使用此类公式时，扩展公式将导致是否创建共享公式，这取决于是否支持该公式(我想).问题是应该支持所有公式，否则应该在某个地方进行记录.
When using such formulas through the Excel UI, expanding the formula will either lead to a shared formula or not, depending on whether the formula is supported (I suppose). Trouble is that all formulas should be supported, or else it should be documented somewhere.
当公式是共享公式(共享t值)时，则此值指示此特定单元格的公式所属的组.一组共享公式中的第一个公式保存在f中元素.这被视为主"公式单元格.随后的共享此公式的单元格无需在公式中编写公式f元素.而是，特定单元格的属性si值是用来计算公式表达式应基于的内容单元格相对于主配方单元格的相对位置.
When a formula is a shared formula (t value is shared) then this value indicates the group to which this particular cell's formula belongs. The first formula in a group of shared formulas is saved in the f element. This is considered the 'master' formula cell. Subsequent cells sharing this formula need not have the formula written in their f element. Instead, the attribute si value for a particular cell is used to figure what the formula expression should be based on the cell's relative location to the master formula cell.
参考: https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.cellformula.aspx
还有其他人遇到过吗?
推荐答案
在与Microsoft支持进行了长期交谈之后，他们的答案是:
After long talks with Microsoft support, their answer is:
首先，即使我们清楚地理解，这样做更容易具有共享公式，而不是Excel的工作方式.Excel本身可以不要创建您要创建的共享公式.如果你在Excel中输入相同的公式，填写并保存，重复公式而不是共享公式.
First of all, even though we clearly understand it would be easier to have Shared Formulas, it is not how Excel works. Excel itself does not create the shared formulas that you are trying to create. If you enter the same formulas in Excel, fill down and save, you get the repetitive formulas stored and not the Shared Formulas.
(是的，但是Excel在工作时也会创建共享公式，我的问题是，并非所有公式都起作用.)
(This is true, but Excel also creates shared formula when they work, my issue here is that not all formulas work.)
错误很明显Excel无法使用它，这是在2007年及以后的所有版本的Excel中保持一致.在这个在一种情况下，作为一种解决方法，您应该模仿Excel并复制像Excel一样在表格上进行公式计算，而不会出现错误.
The error is clear that Excel will not work with it, which is consistent across all versions of Excel from 2007 and higher. In this case, as a workaround, you should mimic Excel and replicate the formulas down the sheet as Excel does which works without errors.
我应该如何事先知道Excel是否支持该公式?微软甚至没有提议官方库来解析Excel公式.我们可以使用看起来很扎实的 https://github.com/spreadsheetlab/XLParser 使用共享公式的目的不是解析公式，而是让Excel进行.
And how am I suppose to know beforehand if the formula will be supported by Excel? Microsoft doesn't even propose an official library to parse Excel formula. We could use https://github.com/spreadsheetlab/XLParser that looks pretty solid, but the whole point of using shared formula is not to parse the formulas, and let Excel do it.
可以更改此行为的唯一方法是实施设计更改进入Excel的行为，但这样做整个倒退兼容性将一直追溯到2007年版本.这不是我们的产品小组有兴趣进行更改的事情.
The only way this behavior can be altered is to implement a design change into Excel’s behavior but by doing so the whole backwards compatibility will be broken all the way back to the 2007 version. This is not something our Product Group is interested in changing.
因此，当保存带有奇怪错误消息的文件时，Excel将继续崩溃...
So Excel will keep crashing when saving the file with weird error messages...
当我在内部进行验证以更好地回答您的问题时，我现在我的升级团队已明确确认该讨论有关文件格式处理或内存中处理的详细信息上述共享公式的Excel不会改变结果.事项事实上，在某些情况下，不能使用共享公式许多不同的原因，其中一些可能不明显和不可见从外部Excel.最好的经验法则是已经概述:当Excel本身不使用共享公式时，不要使用共享公式.这需要考虑到一些细微差别，例如公式，无论是绝对引用还是相对引用(如您的A:B与$ A:$ B的示例)，无论引用是否引用同一工作表或另一个工作表，或在不同的工作簿等中，然后还要使用哪种公式(某些函数，例如OFFSET或INDIRECT取决于要遵守的范围在计算过程中发生变化)，以及是先例还是从属范围/公式表示不使用共享可能更安全公式.文件格式文档中没有明确说明可以使用共享公式的条件是什么.它指定一个文件读者可能会遇到这样的公式，当发生这种情况时，可以解决这个问题，但这并不意味着共享公式总是每当连续范围的单元格包含在编辑栏中使用相同的公式.对于文件编写器，它不是鼓励使用，除非方案仅限于与Excel编写共享公式时相同.
As I was verifying internally to better answer your questions I now have a clear confirmation from my Escalation Team that the discussion about details in the file format handling, or in-memory handling in Excel of said shared formulas won’t change the outcome. Matter of fact, there are scenarios where one cannot use shared formulas for many different reasons, some of them might not be apparent and visible from outside Excel. The best rule of thumb is the one already outlined: don’t use shared formulas when Excel itself doesn’t use one. And this needs to take things into account like subtle differences in the formulas, whether there are absolute or relative references (as in the your example with A:B vs $A:$B), whether the reference refers to the same worksheet or another one, or in a different workbook, etc., then also what kind of formula it is used in (some functions like OFFSET or INDIRECT have dependencies on ranges that are subject to change during a calculation), and whether precedent or dependent ranges/formulas indicate that it might be safer to not use shared formulas. The file format documentation doesn’t clearly state under what conditions a shared formula can be used. It specifies that a file reader could encounter such formulas, and when this happens how to handle that, but that doesn’t mean that shared formulas are always possible or encouraged whenever a contiguous range of cells contains the same formula in the formula bar. For a file writer, it isn’t encouraged to use unless the scenario is restricted to exactly the same as when Excel writes a shared formula.
不过，LibreOffice可以完美地处理它.看起来像修复起来太复杂"或修复错误太冒险".
Still, LibreOffice handles it perfectly. Looks like "it's too complicated to fix" or "it's too risky to fix the bug" excuse.
由于他们建议我创建一个用户语音"条目，因此您可以在此处投票:
Since they suggested that I create a User Voice entry, you can vote for it there:
这篇关于Open XML Excel共享公式不支持某些公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
