excel - 共享公式和数组公式有什么区别？

Question

Excel 定义了共享公式和数组公式。有什么不同？我的理解是数组公式现在已经过时了。这是真的？是否可以将数组公式转换为共享公式？

Answer 1

查看 The Microsoft Excel File Format 的第 4.8 节 (PDF 引用)来自 OpenOffice:

An array formula (BIFF2-BIFF8) and a shared formula (BIFF5-BIFF8) is a formula spanning over a range of cells. Array formulas are handled different from single cell formulas in a spreadsheet. Shared formulas are only an optimisation to decrease the file size, they are not distinguishable from other cell formulas. Naturally an array formula cannot be a shared formula at the same time. Shared formulas are created for instance when filling a cell range from a single formula cell.

In general an array or shared formula is stored only once in a file, either in the ARRAY record (➜5.4) for array formulas, or in the SHAREDFMLA record (➜5.94) for shared formulas. These records are part of the Formula Cell Block (➜4.7.2). They immediately follow the first FORMULA record (➜5.50) for this range²⁰. All array or shared formula cells contain a reference to the formula data. This reference (tExp token, ➜3.10.1) consists of the cell address of the top left cell of the range. In this way each formula cell can be associated with its formula data.

If a formula returns a string value, a STRING record (➜5.102) follows the FORMULA record normally. In the case of array and shared formulas, this STRING record follows the ARRAY or SHAREDFMLA record.

^{20 _{For shared formulas the first FORMULA record may not be the top-left cell of the range. It is possible to overwrite single cells of a shared formula range without invalidating the shared formula itself (the remaining formula cells).}}