欢迎您访问本网站,请 注 册了解更多!
网站首页 > 技能学习 > excel教程 > 正文

Tables Part 2_附着,结构化选中_Excel 2007新知


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

Tables Part 2: Stickiness, Structured Selection, And More

One of the key benefits of tables is how other features in Excel 12 behave more predictably and more like you would expect when a table is present. This is made possible by the fact that Excel knows exactly where the table starts and ends, where the header row is, which cells make up the data and which columns they belong to, where the total row is, etc. So how exactly does this benefit the user? Here are some of the different ways in Excel’s awareness of the structure of your data changes the user experience.
Excel 12列表的一个核心优点在于-当使用列表时,其他相关功能能够更有预测性的迎合用户的需求,为了使这一切成为可能,Excel必须准确定知道列表的相关信息,如:开始和结束位置以及标题行位置,哪些单元格包含数据,它们分别属于哪些列等等。那么这些功能到底对终端用户有什么好处呢?Excel所具有的识别用户数据结构的不同方式可以带给用户不同以往的感受。

When a user does something to a column of data in a table, it “sticks”. What exactly does that mean? If, for example, a user applies a conditional format to an entire column of a table, Excel assumes that the conditional format is always meant to cover the entire table column. So as new rows are added to the table, either in the middle or at the end, and as rows are deleted, Excel will automatically extend or contract the conditional formatting rule appropriately. The rule “sticks” to the column. We already saw this with the example in my previous post, but it doesn’t just stop with conditional formatting. This concept works with just about anything you can apply to a column, such as formatting (anything in the Format Cells dialog), cell protection, data validation, etc. In addition, this behaviour also applies to anything that holds on to a reference, such as formulas, charts and PivotTables.

How do you make something “sticky”? There’s no explicit gesture for this. Excel just assumes that anytime something is done to an entire column, the setting is meant to always follow the column. So, if a user selects a table column and applies a data validation rule, that rule will grow and shrink with the table. Create a PivotTable that uses a table as its data source, and that Pivot Table will automatically pick up new rows from the table. Ditto for charts. To be precise, when I say “entire column” in this context, I really mean all the data rows. It is not necessary to also include the header cell – this way you don’t have to include the header in order to, for example, make the “currency” number format stick to a column for example.

This stickiness also applies to new columns. For example, if a table is used as the data source for a chart, then any new columns I add to the table will get picked up by the chart. In addition, properties set on the entire table, headers, and the total row (more on what a total row is in a later post) are also “sticky”.


Now that Excel recognizes things like rows and columns in my tables, it is only natural that it provide simple ways to select those elements of a table. A single mouse click can select one or more rows, one or more columns, or the entire table. To select a column in a table, simply hover the mouse over the top of the header until the mouse cursor turns into a down-arrow and click.

To select a row, hover the mouse over to the left edge of a row until the mouse cursor turns into a right-arrow and click. And to select the entire table, hover the mouse over the top-left corner of the table until you see an arrow that points down and right and click.

Note that column and table selection have two behaviours. The first click always selects just the data portion of the column/table. This makes it easy to select a column of numbers and apply formatting or other properties to the data in the column. The second click expands the selection to cover everything in the column/table, header and all. This is useful for copy/paste operations or rearranging columns in a table.

If you are a keyboard junkie (like many of the people reading this seem to be), note that there is keyboard support for these selection behaviours. The existing shortcut keys for row and column selection, SHIFT+SPACE and CTRL+SPACE respectively, have been modified to work with tables. For example, pressing CTRL+SPACE once selects all the data rows in a table column. The second press expands the selection to encompass the entire table column, and the third press expands it again to encompass the entire worksheet column.

What Column am I in again?

This is one of my favorites. When working with large data sets, it’s easy to scroll the headers off the screen and forget which column you are looking at, especially if many of the columns have similar data in them. Once you have declared something a table in Excel 12, this problem will go away – when navigating a large table and the table’s headers have been scrolled off the screen, Excel will insert the table headers into the sheet header row where the A, B, C headings appear (see the screen shot below). For example, when I scroll this table upwards …
这是我非常喜欢的功能之一,当你处理大量数据时,需要滚动屏幕,标题行时常消失的无影无踪,如果有很多列都有类似的数据,那么用户根本无法记得自己在查看哪列的数据,如果你在Excel 12中使用列表功能,这个问题将不再存在,用户浏览很大的列表时,当列表的标题行将要移出屏幕时,Excel会将系统原来的列标题的A,B,C替换为列表的标题行(请看下面的截屏图),例如当我在不停地滚动屏幕时……


the header text shows up in place of the sheet headers.


This makes it much easier to keep track of things without having to split screen/freeze panes. Note, the table headers will only be visible as long as the active cell is somewhere inside the table. If you select outside the table, the standard A, B, C headers return.

More Conditional Formatting Goodness

Back at the start of my conditional formatting posts, I said that one of the things we set out to do was to provide a better experience in Tables – lowering the bar for conditional formatting rules that previously required formulas or fiddling. Here are two features we added to conditional formatting when a user is working with a table.

First, we added a “format entire row” option that appears when defining a conditional format rule on a table.


When this option is turned on, the entire table row will be highlighted when a condition is met within a specific column. Achieving this in the past required more advanced knowledge of conditional formatting. Now you can treat it like any simple rule – just select the olumn you are interested in evaluating, and select this option if you want the result to show across the entire row.

Second, we added a “compare columns” rule to the list of rules available when setting up conditional formatting on tables. This rule allows you to format cells of a table column based on comparisons with values in another column – you can use this, for example, to highlight any rows where the values in the “2005 Profits” column are less than the values in “2004 Profits” column. Again, the fact that Excel knows that there are columns in your table makes it possible to surface very easy UI to quickly set up these sorts of conditions.
第二,我们在列表的条件格式中添加了“对比列”,这可以基于本列和其他列数据的比较来设置格式,例如,突出显示“2005 Profits”少于“2004 Profits”的数据行,正是因为Excel能够识别列表的列,所以我们才能通过简单的用户界面来设置条件。


That’s it for now. In my next table post I’ll go into all the work we’ve done around tables, formulas, and referencing.

Published Thursday, October 27, 2005 10:42 AM by David Gainer

注:本文翻译自http://blogs.msdn.com/excel ,原文作者为David Gainer(a Microsoft employee),Excel Home 授权转载。严禁任何人以任何形式转载,违者必究。




取消回复欢迎 发表评论: