Excel Home

Tables Part 3_在列表中使用公式(一)_Excel 2007新知

译者:taller  来源:http://blogs.msdn.com/excel

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.
· 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.
· 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]).

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”.

After I select my table “Sales”, I can now see all the columns in the table.

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.

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”。

未经允许不得转载:ExcelHome » Tables Part 3_在列表中使用公式(一)_Excel 2007新知

本站特聘法律顾问:李志群律师   沪ICP备11019229号-2

沪公网安备 31011702000001号