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為我的列表添加新的一列——相應(yīng)的格式將會(huì)應(yīng)用到所有的新添加的行，列表的邊框也將進(jìn)行相應(yīng)當(dāng)調(diào)整。
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].
下一步是輸入公式，輸入“=(”之后，然后用鍵盤(pán)選中需要計(jì)算的區(qū)域——也就是本例子中的FY05，Excel將自動(dòng)輸入[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.
這里我們可以看到，如果在列表中使用本列表的相關(guān)引用名稱，那么不需要列表名稱作為前綴，使用類似的方法可以在公式中引用其他的列。
When I am finished with my formula and press ENTER, Excel automatically fills that formula down for all rows in the Percent Growth column.
當(dāng)我輸入完公式并按回車后，Excel將自動(dòng)在“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.
這是另一個(gè)新的數(shù)據(jù)表功能稱為計(jì)算列（譯者注：“計(jì)算列”是“calculated columns”的直譯，其含義為如果列表中的某個(gè)數(shù)據(jù)列包含計(jì)算公式則被稱為“計(jì)算列”），任何時(shí)候只要在空白的表格列中輸入公式，公式都會(huì)自動(dòng)填充，我們認(rèn)為這個(gè)功能會(huì)減少手動(dòng)填充和復(fù)制/粘貼帶來(lái)的錯(cuò)誤，填充功能并不僅限于當(dāng)前列表，用戶在列表中添加或者刪除列時(shí)，公式填充也會(huì)進(jìn)行相應(yīng)當(dāng)調(diào)整，這也是我在上個(gè)帖子中提到“附著”功能的另一個(gè)例子，實(shí)際上，用戶可以完全掌控這些功能——在計(jì)算列的某個(gè)單元格中輸入公式時(shí)，利用系統(tǒng)的提示標(biāo)志（譯者注：英文名稱為“on-object UI ”，即上圖中位于H4單元格的提示標(biāo)志）可以取消自動(dòng)填充公式（在上圖中可以看到），當(dāng)然用戶也可以通過(guò)系統(tǒng)設(shè)置關(guān)閉這項(xiàng)功能。
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.
如果計(jì)算列需要更新公式，只需要修改其中的一個(gè)公式，計(jì)算列中的其他公式會(huì)相應(yīng)當(dāng)更新，另外，單獨(dú)更改計(jì)算列中的某行數(shù)據(jù)（例如輸入靜態(tài)數(shù)值或者自定義公式）也是可以的，這樣會(huì)使這部分單元格不再與計(jì)算列保持同步，為了容易識(shí)別這類單元格（譯者注：下文中稱此類單元格為“另類單元格”），系統(tǒng)會(huì)為它們?cè)O(shè)置一個(gè)標(biāo)記，例如我在某列的單元格輸入=RAND()，并選擇取消自動(dòng)填充公式，這個(gè)單元格的左上角將出現(xiàn)一個(gè)綠色的三角標(biāo)志。
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.
請(qǐng)注意，一旦計(jì)算列中包含了另類單元格，為了避免覆蓋自定義數(shù)據(jù)或者公式，對(duì)于計(jì)算列單元格的所有后續(xù)操作都不再擴(kuò)展到整列，但是上面提到的提示標(biāo)志仍然會(huì)出現(xiàn)。
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.
現(xiàn)在介紹一下列表的最后一個(gè)功能匯總列（Excel 2003用戶可能會(huì)認(rèn)為已經(jīng)知道這個(gè)功能了，然而在2003基礎(chǔ)之上我們做了很多非常好的改進(jìn)，請(qǐng)大家繼續(xù)閱讀本文），像標(biāo)題行一樣，匯總行是列表中的另一個(gè)特殊區(qū)域，它位于列表的最下部，用于計(jì)算列表中每列數(shù)據(jù)的總和。
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.
用戶如果需要使用匯總行功能，可以通過(guò)下面的幾種方法：點(diǎn)擊Ribbon上的列表標(biāo)簽、在列表上點(diǎn)擊鼠標(biāo)右鍵或者使用傳統(tǒng)的自動(dòng)求和功能，列表中保留這個(gè)特殊區(qū)域的好處在于，當(dāng)列表擴(kuò)展或者收縮時(shí)，匯總行會(huì)相應(yīng)地更新，另外，用戶設(shè)置的列表風(fēng)格同樣對(duì)于“匯總行”有效（關(guān)于列表風(fēng)格的更多內(nèi)容請(qǐng)參考隨后的帖子），其格式的處理方法類似于表格中增加新的列。
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.
匯總行可以使用各種公式（公式甚至可以引用列表區(qū)域之外的數(shù)據(jù)，但是我們不推薦這種用法）和文本標(biāo)簽，匯總行中的單元格具備下拉按鈕，可以為用戶提示一些常用的功能，當(dāng)然用戶也可以使用最下面的工具條輸入公式。
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能夠“記住”匯總行中已有的設(shè)置，在用戶下次激活匯總行功能時(shí)這些設(shè)置可以自動(dòng)回復(fù)。
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.
現(xiàn)在我結(jié)束關(guān)于語(yǔ)法的討論，因?yàn)槟承┯脩艨赡軙?huì)問(wèn)到下面的問(wèn)題“我如何引用全部數(shù)據(jù)、標(biāo)題和整個(gè)列表？”其實(shí)很簡(jiǎn)單，使用=Table1[#All]就可以引用這個(gè)列表，如果你只需要引用標(biāo)題行可以用=Table1[#Headers]，引用列表中的某列數(shù)據(jù)用=Table1[[#All], [Column1]]，如果只是引用某列第標(biāo)題的內(nèi)容則用=Table1[[#Headers], [Column1]]，這些關(guān)鍵字可以組合使用如=Table1[[#Headers],[#Data],[Column1]]，到現(xiàn)在為止我想你已經(jīng)知道如何引用列表的相關(guān)部分。
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.
關(guān)于結(jié)果引用還有幾點(diǎn)要說(shuō)明：列表的名稱在某個(gè)工作簿中是唯一的，所以引用列表時(shí)不需要類似 Sheet1!Table1的顯式聲明其所在地工作表，在某個(gè)列表中列名稱也必須是唯一的，列表名稱和區(qū)域名稱是共同存在的，也就是說(shuō)不能將某個(gè)已經(jīng)存在的列表名稱定義區(qū)域的名稱，反之亦然。如果改變列表名稱或者任何列的名稱，那么包含這個(gè)引用的所以公式將同時(shí)自動(dòng)更新，結(jié)構(gòu)化選中可以使用鼠標(biāo)（當(dāng)然可以關(guān)閉這項(xiàng)功能），實(shí)際上結(jié)構(gòu)化選中（請(qǐng)參考我的上一個(gè)帖子 Tables Part2：附著，結(jié)構(gòu)化選中…）是在公式中輸入結(jié)構(gòu)化引用的快捷方法。
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?
今天就到這里，我的下一個(gè)帖子將研究自動(dòng)篩選或者多選。
