When I press enter to commit the change, Excel adds another column to my table – the appropriate formatting is applied to the rows, the border around the table adjusts, etc. So far so good.
按下回車鍵之后，Excel為我的列表添加新的一列——相應的格式將會應用到所有的新添加的行，列表的邊框也將進行相應當調整。
The next step is to write the formula. I might do something like type "=(" and then use the keyboard to move selection over to the cell I want to calculate – in this case FY05. Excel gives me the following reference: [FY05].
下一步是輸入公式，輸入“=(”之后，然后用鍵盤選中需要計算的區域——也就是本例子中的FY05，Excel將自動輸入[FY05]。
As you can see, when referencing a table from within the table itself it is not necessary to prefix the table name. As I use other columns in my formula, they get similar references.
這里我們可以看到，如果在列表中使用本列表的相關引用名稱，那么不需要列表名稱作為前綴，使用類似的方法可以在公式中引用其他的列。
When I am finished with my formula and press ENTER, Excel automatically fills that formula down for all rows in the Percent Growth column.
當我輸入完公式并按回車后，Excel將自動在“Percent Growth”列中向下填充公式。
This is another new feature of tables called calculated columns. Any time a formula is entered into an empty table column it will automatically fill. We think this will help reduce errors introduced by manual filling or copy/paste. Not only does it fill, but it continues to fill down as you add or delete rows in the table. This is another one of those “sticky” properties I mentioned in my previous post. As with other table features, the user does have control over its behavior – at the time you enter a formula in a cell in a calculated column, the auto-fill can be undone using on-object UI (which you can see in the previous screen shot). Further, if you really do not want columns filling down ever, the feature itself can be turned off.
這是另一個新的數據表功能稱為計算列（譯者注：“計算列”是“calculated columns”的直譯，其含義為如果列表中的某個數據列包含計算公式則被稱為“計算列”），任何時候只要在空白的表格列中輸入公式，公式都會自動填充，我們認為這個功能會減少手動填充和復制/粘貼帶來的錯誤，填充功能并不僅限于當前列表，用戶在列表中添加或者刪除列時，公式填充也會進行相應當調整，這也是我在上個帖子中提到“附著”功能的另一個例子，實際上，用戶可以完全掌控這些功能——在計算列的某個單元格中輸入公式時，利用系統的提示標志（譯者注：英文名稱為“on-object UI ”，即上圖中位于H4單元格的提示標志）可以取消自動填充公式（在上圖中可以看到），當然用戶也可以通過系統設置關閉這項功能。
If the calculated column ever needs to be updated, it is only necessary to edit one copy of the formula and the change will propagate to all rows. In addition, it is possible to change any single row (e.g. enter a static value or custom formula) so that it is not consistent with the calculated column. Such cells will be flagged visually so that inconsistencies will be easy to spot. For example, if I enter =RAND() in the middle of my column and choose not to fill down, I see a green triangle in the upper left-hand corner of the cell.
如果計算列需要更新公式，只需要修改其中的一個公式，計算列中的其他公式會相應當更新，另外，單獨更改計算列中的某行數據（例如輸入靜態數值或者自定義公式）也是可以的，這樣會使這部分單元格不再與計算列保持同步，為了容易識別這類單元格（譯者注：下文中稱此類單元格為“另類單元格”），系統會為它們設置一個標記，例如我在某列的單元格輸入=RAND()，并選擇取消自動填充公式，這個單元格的左上角將出現一個綠色的三角標志。
Furthermore, once a calculated column contains inconsistencies, subsequent edits to cells in the calculated column do not propagate because Excel does not want to overwrite custom values. However, the same UI shown above will appear allowing the user to opt-in to the behavior.
請注意，一旦計算列中包含了另類單元格，為了避免覆蓋自定義數據或者公式，對于計算列單元格的所有后續操作都不再擴展到整列，但是上面提到的提示標志仍然會出現。
The last feature I want to talk about is the table totals row. (For those that use Excel 2003 Lists, you will recognize the totals row, but we have made some nifty improvements since 2003, so please read on.) The totals row is another special area of the table, like the header row. It lives at the bottom of the table and its purpose is to calculate totals for the columns in a table.
現在介紹一下列表的最后一個功能匯總列（Excel 2003用戶可能會認為已經知道這個功能了，然而在2003基礎之上我們做了很多非常好的改進，請大家繼續閱讀本文），像標題行一樣，匯總行是列表中的另一個特殊區域，它位于列表的最下部，用于計算列表中每列數據的總和。
The total row can be enabled via the table tab in the ribbon, right-click on the table, or by using the existing AutoSum functionality. The nice thing about having a special area for totals in the table is that it participates in the table’s activities. It grows and shrinks properly with the table, it gets special “total row” formatting when you apply a style to the table (more on table styles in a later post), and the formatting behaves appropriately as more columns are added to the table.
用戶如果需要使用匯總行功能，可以通過下面的幾種方法：點擊Ribbon上的列表標簽、在列表上點擊鼠標右鍵或者使用傳統的自動求和功能，列表中保留這個特殊區域的好處在于，當列表擴展或者收縮時，匯總行會相應地更新，另外，用戶設置的列表風格同樣對于“匯總行”有效（關于列表風格的更多內容請參考隨后的帖子），其格式的處理方法類似于表格中增加新的列。
The total row cells can contain pretty much any kind of formula (the formulas don’t have to reference the table at all but we don’t expect that to be the common case) as well as text labels. The cells in the totals row also contain a dropdown that shows you some of the most commonly used functions, lowering the bar for the new Excel user to write formulas.
匯總行可以使用各種公式（公式甚至可以引用列表區域之外的數據，但是我們不推薦這種用法）和文本標簽，匯總行中的單元格具備下拉按鈕，可以為用戶提示一些常用的功能，當然用戶也可以使用最下面的工具條輸入公式。
The total row can be toggled on and off, and any functions that exist in the total row will be “remembered” until the next time you turn it back on.
用戶可以很容易的選擇是否使用匯總行，Excel能夠“記住”匯總行中已有的設置，在用戶下次激活匯總行功能時這些設置可以自動回復。
So now let’s finish our discussion of syntax, since some of you are probably asking questions like “how do I reference the entire table, headers and all?” Referencing the entire table can be done this way: =Table1[#All]. Or if you just want to reference the headers: =Table1[#Headers]. To reference the entire column: =Table1[[#All], [Column1]]. To reference just the header value of a column: =Table1[[#Headers], [Column1]]. The special keywords can also be combined: =Table1[[#Headers],[#Data],[Column1]]. At this point I think you get the idea.
現在我結束關于語法的討論，因為某些用戶可能會問到下面的問題“我如何引用全部數據、標題和整個列表？”其實很簡單，使用=Table1[#All]就可以引用這個列表，如果你只需要引用標題行可以用=Table1[#Headers]，引用列表中的某列數據用=Table1[[#All], [Column1]]，如果只是引用某列第標題的內容則用=Table1[[#Headers], [Column1]]，這些關鍵字可以組合使用如=Table1[[#Headers],[#Data],[Column1]]，到現在為止我想你已經知道如何引用列表的相關部分。
Some other points about structured references: table names are globally unique to a workbook and do not require a sheet reference like Sheet1!Table1. Table column names must be unique within the table itself. Table names co-exist in the same namespace as named ranges, meaning you can’t have a defined name with the same name as a table, and vice-versa. If the table name or any of the column names change then any formulas that reference those names will automatically update as well. Structured references can be created using selection with the mouse (and yes, there is an option to turn this off). In fact, structured selection (I talked about it in my last post) is one way to very quickly generate structured references when writing formulas.
關于結果引用還有幾點要說明：列表的名稱在某個工作簿中是唯一的，所以引用列表時不需要類似 Sheet1!Table1的顯式聲明其所在地工作表，在某個列表中列名稱也必須是唯一的，列表名稱和區域名稱是共同存在的，也就是說不能將某個已經存在的列表名稱定義區域的名稱，反之亦然。如果改變列表名稱或者任何列的名稱，那么包含這個引用的所以公式將同時自動更新，結構化選中可以使用鼠標（當然可以關閉這項功能），實際上結構化選中（請參考我的上一個帖子 Tables Part2：附著，結構化選中…）是在公式中輸入結構化引用的快捷方法。
That’s it for now. For my next post I will delve into the work we’ve done around the area of autofilter. Multi-select anyone?
今天就到這里，我的下一個帖子將研究自動篩選或者多選。
