定义名称
File Format Support (click to show)
Defined names have evolved over the decades, with new features added over time:
- "English" refers to defined names with English letters and numbers (ASCII)
- "Unicode" refers to defined names with non-English characters.
- "Comment" refers to comments that can be attached to defined names.
| Formats | English | Unicode | Comment |
|---|---|---|---|
| XLSX / XLSM | ✔ | ✔ | ✔ |
| XLSB | ✔ | ✔ | ✔ |
| XLS | ✔ | ✔ | ✔ |
| XLML | ✔ | ✔ | |
| SYLK | ✔ | ✕ | |
| ODS / FODS / UOS | ✔ | ✔ |
X (✕) marks features that are not supported by the file formats. There is no way to specify a Unicode defined name in the SYLK format.
定义的名称（有时称为 "命名范围"）是对单元格、范围、常量或公式的标记引用。有意义的标签可以使公式表达式更具可读性并且对工作表更改更稳健。
¥Defined names (sometimes called "named ranges") are labeled references to cells, ranges, constants or formulae. Meaningful labels can make formula expressions more readable and more robust to worksheet changes.
Why are Defined Names useful? (click to show)
For example, the NPV formula function calculates the net present value of a
series of cashflows. In large workbooks, raw data will be stored in separate
worksheets and the interest rate will be stored in a separate "Model Parameters"
worksheet. Formulae may have references to multiple sheets:
=NPV('Model Parameters'!B2,Data!B2:F2)
^^^^^^^^^^^^^^^^^^^^^ --- interest rate
A defined name Interest referencing 'Model Parameters'!B2 would greatly
simplify the formula:
=NPV(Interest,Data!B2:F2)
^^^^^^^^ --- interest rate
Judicious use of Defined Names generally lead to fewer formula errors.
贮存
¥Storage
SheetJS 工作簿对象的 Workbook 属性存储工作簿属性。Workbook 的 Names 属性是 SheetJS 定义的名称对象的数组。
¥The Workbook property of SheetJS workbook objects store workbook attributes.
The Names property of Workbook is an array of SheetJS defined name objects.
解析器并不总是创建 Names 数组或 Workbook 结构。代码应在使用前测试定义的名称数组是否存在：
¥Parsers do not always create the Names array or Workbook structure. Code
should test for the existence of the defined names array before use:
var wb = XLSX.utils.book_new();
/* ensure the workbook structure exists */
if(!wb.Workbook) wb.Workbook = {};
if(!wb.Workbook.Names) wb.Workbook.Names = [];
/* add a new defined name */
wb.Workbook.Names.push({ Name: "MyData", Ref: "Sheet1!$A$1:$A$2" });
定义名称对象
¥Defined Name Object
SheetJS 定义的名称对象支持以下属性：
¥SheetJS defined name objects support the following properties:
| 键 | 应用中的名称 | 描述 |
|---|---|---|
Sheet | "范围" | 工作表索引（0 = 第一张工作表）或 null（工作簿） |
Name | "名称" | 区分大小写的名称。适用标准规则 |
Ref | "指的是" | A1 样式参考 ("Sheet1!$A$1:$D$20") |
Comment | "注释" | 注释（支持的文件格式） |
范围
¥Ranges
公式中定义的名称引用在内部转移到单元格地址。例如，给定定义的名称
¥Defined name references in formulae are internally shifted to the cell address. For example, given the defined name
{ Name: "MyData", Ref: "Sheet1!A1:A2" } // no $ means relative reference
如果 D4 设置为 =SUM(MyData)：
¥If D4 is set to =SUM(MyData):
ws["D4"].f = "SUM(MyData)";
电子表格软件会将定义的名称范围翻译为单元格。Excel 将尝试计算 SUM(D4:D5) 并分配给单元格 D4。这将引发循环引用错误。
¥Spreadsheet software will translate the defined name range down to the cell.
Excel will try to calculate SUM(D4:D5) and assign to cell D4. This will
elicit a circular reference error.
推荐的方法是修复引用的行和列：
¥The recommended approach is to fix the rows and columns of the reference:
{ Name: "MyData", Ref: "Sheet1!$A$1:$A$2" } // absolute reference
范围
¥Scope
Excel 允许两个工作表范围的定义名称共享相同的名称。但是，工作表范围名称不能与工作簿范围名称冲突。工作簿编写者可能不会强制执行此限制。
¥Excel allows two sheet-scoped defined names to share the same name. However, a sheet-scoped name cannot collide with a workbook-scope name. Workbook writers may not enforce this constraint.
以下代码片段创建一个工作表级别定义名称 "Global" 和一个本地定义名称 "Local"，第一张工作表和第二张工作表具有不同的值：
¥The following snippet creates a worksheet-level defined name "Global" and a
local defined name "Local" with distinct values for first and second sheets:
/* "Global" workbook-level -> Sheet1 A1:A2 */
wb.Workbook.Names.push({ Name: "Global", Ref: "Sheet1!$A$1:$A$2" });
/* "Local" scoped to the first worksheet -> Sheet1 B1:B2 */
wb.Workbook.Names.push({ Name: "Local", Ref: "Sheet1!$B$1:$B$2", Sheet: 0 });
/* "Local" scoped to the second worksheet -> Sheet1 C1:C2 */
wb.Workbook.Names.push({ Name: "Local", Ref: "Sheet1!$C$1:$C$2", Sheet: 1 });
在线演示
¥Live Demo
以下示例创建 3 个定义的名称：
¥The following example creates 3 defined names:
-
"全局的" 是引用
Sheet1!$A$1:$A$2的工作簿级别名称¥"Global" is a workbook-level name that references
Sheet1!$A$1:$A$2 -
第一个工作表中的 "本地" 引用
Sheet1!$B$1:$B$2¥"Local" in the first worksheet references
Sheet1!$B$1:$B$2 -
第二个工作表中的 "本地" 引用
Sheet1!$C$1:$C$2¥"Local" in the second worksheet references
Sheet1!$C$1:$C$2
这两个工作表都包含引用 "本地" 和 "全局的" 的公式。由于引用的范围不同，使用 "本地" 的表达式也会不同。
¥Both worksheets include formulae referencing "Local" and "Global". Since the referenced ranges are different, the expressions using "Local" will differ.
/* The live editor requires this function wrapper */ function DefinedNameExport() { return ( <button onClick={() => { /* Create empty workbook */ var wb = XLSX.utils.book_new(); /* Create worksheet Sheet1 */ var ws1 = XLSX.utils.aoa_to_sheet([[1,2,3],[4,5,6],["Global",0],["Local",0]]); XLSX.utils.book_append_sheet(wb, ws1, "Sheet1"); /* Create worksheet Sheet2 */ var ws2 = XLSX.utils.aoa_to_sheet([["Global",0],["Local",0]]); XLSX.utils.book_append_sheet(wb, ws2, "Sheet2"); /* Create defined names */ if(!wb.Workbook) wb.Workbook = {}; if(!wb.Workbook.Names) wb.Workbook.Names = []; /* "Global" workbook-level -> Sheet1 A1:A2 */ wb.Workbook.Names.push({ Name: "Global", Ref: "Sheet1!$A$1:$A$2" }); /* "Local" scoped to the first worksheet -> Sheet1 B1:B2 */ wb.Workbook.Names.push({ Name: "Local", Sheet: 0, Ref: "Sheet1!$B$1:$B$2" }); /* "Local" scoped to the second worksheet -> Sheet1 C1:C2 */ wb.Workbook.Names.push({ Name: "Local", Sheet: 1, Ref: "Sheet1!$C$1:$C$2" }); /* Create formulae */ ws1["B3"].f = "SUM(Global)"; // Sheet1 B3 =SUM(Global) 1 + 4 = 5 ws1["B4"].f = "SUM(Local)"; // Sheet1 B4 =SUM(Local) 2 + 5 = 7 ws2["B1"].f = "SUM(Global)"; // Sheet2 B1 =SUM(Global) 1 + 4 = 5 ws2["B2"].f = "SUM(Local)"; // Sheet2 B2 =SUM(Local) 3 + 6 = 9 /* Export to file (start a download) */ XLSX.writeFile(wb, "SheetJSDNExport.xlsx"); }}><b>Export XLSX!</b></button> ); }