译者：taller 来源：http://blogs.msdn.com/excel
发表于：2006年7月7日
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.
除了上面的例子，还有很多语法格式，在继续讲解之前我想解释一个我个人很喜欢的东西——结构化引用和公式记忆式键入功能的结合，公式记忆式键入功能完美的和结构化引用结合在一起，也就是说列表名称和其中列的名称可以在公式记忆式键入功能中使用。例如，我们来看一下如何应用上图中生产的结构化引用，首先，我们的工作表中只有一个列表。
Next, I type “=SUM(S”. You will note this lists everything starting with “S” in the Formula AutoComplete drop-down, including formulas, named ranges, and my table, “Sales”.
接下来，我输入“=SUM(S”，你可以注意到在公式记忆式键入下拉列表中，所有的以“S”开头的条目会出现，这包括公式、区域名称和我的列表名称“Sales”。
After I select my table “Sales”, I can now see all the columns in the table.
我选择“Sales”之后，就可以看该列表的区别列名称。
And with a single click or keystroke, Excel 12 completes the reference.
只需要单击鼠标或者按下相应的键，Excel 12将输入这个引用。
All I need to do is close the parenthesis and press enter and I am done. We believe this will improve accuracy and efficiency.
剩下的我所要做的就是输入右括号，并按下回车键，我们确信这种方式会提高公式输入的准确性和效率。
Structured references can be used inside tables as well, and here Excel has even more new behaviors. Say I’m looking at some sales figures and I want to calculate the percentage change in sales over the last few years for each row in my table. My table looks something like the screenshot below.
结构化引用也可以用于列表内部，Excel为其提供了一些新的功能，比如我在查看销售图表，能够计算出列表中每一行相对于过去几年变化的百分比。我的列表如下面的截屏图：
The formula I want is something like: =(FY05-FY02)/FY05. The first thing I want to do is add a column, so I start typing “Percent Growth” after the last column in my table.
我需要一个像=(FY05-FY02)/FY05的公式，我要做的第一件事就是添加一列，在列表的最后一列之后输入“Percent Growth”。
评论(0)