Tables Part 3: Using Formulas with Tables
列表（第三部分）：在列表中使用公式

One of our goals with tables was to create a set of features that reduce the overall maintenance required to keep a spreadsheet functioning well over time. This involves making spreadsheets less prone to error, as well as making them more understandable days, months, and years after the spreadsheet was created. Rethinking the interaction between tables and formulas proved to be an important part of meeting that goal.
在以前的电子数据表格中，为了使其正常工作，用户需要大量的维护工作，我们引入列表的一个目的就是减少这部分维护工作，它可以使电子数据表格更不容易出错，也可以使生成的电子数据表格更好的理解年月日。列表和公式的协同工作被证明是实现这个目标的重要部分。

As many readers have presaged, Excel 12 provides some new ways to reference tables and parts of tables. We refer to our work in this area as “structured referencing” (that is a working title, so it may be called something else when we ship the product). In a nutshell, the structured referencing feature allows you to reference a table and/or subsets of the table directly by name as opposed to by cell coordinates. The feature is similar in concept to named ranges with a few crucial differences. First, the names that can be referenced are automatically generated when the table is created. Specifically, this includes the name of the table itself (which by default is something like “Table1”), and the names of all the columns. Also, the names are automatically removed as columns are deleted or the entire table is deleted. Finally, and perhaps most importantly, the names automatically adjust as the table grows and shrinks. As a result, the majority of the headaches of maintaining named ranges go away with structured referencing.
正如很多读者预测的一样，Excel 12提供了一些新的方法用于引用整个列表和部分列表，我们谈到的这部分工作成为“结构化引用”（这是开发过程中的名称，在最终发布的产品中可能使用其他的名称）。简单的说，相比过去我们只能通过单元格坐标进行引用，结构化引用功能使得用户可以通过名称引用整个列表或者部分列表，这个功能和区域名称的概念类似，只有很少的一点区别。首先，在生成列表时会自动产生一个可以引用的名称，这包括列表本身的名称（系统缺省为类似于“Table1”的名称）和全部列的名称，其次，这些名称会随着列或者列表的删除而消失，最后，可能是最重要的一点，名称会随着列表的扩展和收缩而自动调整。使用结构化引用将能够解决令人头痛最主要的问题——名称的维护。

So how is all this manifested in the product? Structured referencing represents an addition to the syntax for formulas in Excel. Here are the basics of how it works.
系统如何识别这些引用呢？结构化引用可以应用于Excel的公式中，下面我们来简单介绍一下它是如何工作的。
· A reference to a table looks like this: =Table1, so if you wanted to sum the values in a table, you could use =SUM(Table1). Note that =Table1 returns all of the data in Table1 without the headers – this is because many of the common functions that work on ranges, like VLOOKUP, assume no headers.
列表的引用类似于=Table1，用户使用=SUM(Table1)就可以对整个工作表数据进行求和，注意=Table1将返回除了标题之外列表中的所有数据——这是因为一般的使用区域作为参数的函数如VLOOLUP，都会假设该区域不包含标题。
· A reference to a column looks like this: =Table1[Column1]. Again, this reference returns just the data. So, for example, if you wanted to SUM a column, you could type =SUM(Sales[2004]).
列的引用类似于=Table1[Column1]，同样的这个引用也只是返回数据，例如用户需要对某列求和，那么可以输入公式=SUM(Sales[2004])。

There’s more to the syntax than that, but first I want to talk about something I personally love – integration between structured referencing and Formula AutoComplete. The Formula AutoComplete feature I talked about a week ago is fully integrated with structured references – meaning Formula AutoComplete for tables names as well as columns within tables is possible. For example, here is what it would look like to build the structured reference pictured above. First, let’s start with just a table.
除了上面的例子，还有很多语法格式，在继续讲解之前我想解释一个我个人很喜欢的东西——结构化引用和公式记忆式键入功能的结合，公式记忆式键入功能完美的和结构化引用结合在一起，也就是说列表名称和其中列的名称可以在公式记忆式键入功能中使用。例如，我们来看一下如何应用上图中生产的结构化引用，首先，我们的工作表中只有一个列表。

上一篇：表格制作excel教程：值字段技巧（一） 下一篇：表格制作快速入门：Excel以图形方式查看名称

郑重声明：本文版权归原作者所有，转载文章仅为传播更多信息之目的，如作者信息标记有误，请第一时间联系我们修改或删除，多谢。