Structured references make it much easier and more intuitive to work with table data when you are using formulas that reference a table, either portions of a table or the entire table. They are especially useful because table data ranges often change, and the cell references for structured references adjust automatically. This minimizes the need to rewrite formulas as rows and columns are added and deleted in a table, or when external data is refreshed.
THIS STRUCTURED REFERENCE IS EASIER TO UNDERSTAND: | THAN THIS CELL REFERENCE: |
=SUM(DeptSales[SaleAmt]) | =Sum(C2:C7) |
· The Department Sales table example
· Components of a structured reference
· Table names and column specifiers
· Qualifying structured references in calculated columns
· Examples of using structured references
· Working with structured references
· Structured reference syntax rules
The Department Sales table example
The following is an example, referenced throughout this article, of a table that is based on sales in a department of six employees with the latest sales amounts and commissions.
A Department Sales table
The entire table (A1:E8)
The table data (A2:E7)
A column and column header (D1:D8)
A calculated column (E1:E8)
The Totals row (A8:E8)
Components of a structured reference
To work with tables and structured references effectively, you need to understand how to create the syntax of structured references when you are creating formulas. The components of a structured reference are illustrated in the following example of a formula that adds up total sales amounts and commission amounts:
A table name is a meaningful name that you provide to reference the actual table data (excluding the headers row and totals row, if any).
A column specifier is derived from the column header, enclosed in brackets, and references the column data (excluding the column header and total, if any).
A special item specifier is a way to refer to specific portions of the table, such as the Totals row.
The table specifier is the outer portion of the structured reference that is enclosed in square brackets following the table name.
A structured reference is the entire string beginning with the table name and ending with the table specifier.
Table names and column specifiers
Each time that you insert a table, Microsoft Office Excel creates a default table name (Table1, Table2, and so on) at the global workbook level or scope. You can easily change the name to make it more meaningful to you. For example, to change Table1 to DeptSales, you can use the Edit Name dialog box. (On the Design tab, in the Properties group, edit the table name in the Table Name box.)
A table name refers to the entire range of data in the table with the exception of the header and total rows. In the Department Sales table example, the table name, DeptSales, refers to the cell range A2:E7.
Similar to table names, column specifiers represent references to the entire column of data with the exception of the column header and total. In the Department Sales table example, the column specifier, [Region], refers to the cell range B2:B7, and the column specifier, [ComPct], refers to the cell range D2:D7.
Reference operators
For added flexibility in specifying ranges of cells, you can use the following reference operators to combine column specifiers.
THIS STRUCTURED REFERENCE: | REFERS TO: | BY USING THE: | WHICH, IN THEEXAMPLE, IS CELL RANGE: |
=DeptSales[[SalesPers]:[Region]] | All of the cells in two or more adjacent columns | : (colon) range operator | A2:B7 |
=DeptSales[SaleAmt],DeptSales[ComAmt] | A combination of two or more columns | , (comma) union operator | C2:C7, E2:E7 |
=DeptSales[[SalesPers]:[SaleAmt]] DeptSales[[Region]:[ComPct]] | The intersection of two or more columns | (space) intersection operator | B2:C7 |
Special item specifiers
For added convenience, you can also use special items to refer to various portions of a table, such as just the Totals row, to make it easier to refer to these portions in formulas. The following are the special item specifiers that you can use in a structured reference:
THIS SPECIAL ITEM SPECIFIER: | REFERS TO: | WHICH, IN THEEXAMPLE, IS CELL RANGE: |
=DeptSales[#All] | The entire table, including column headers, data, and totals (if any). | A1:E8 |
=DeptSales[#Data] | Just the data. | A2:E7 |
=DeptSales[#Headers] | Just the header row. | A1:E1 |
=DeptSales[#Totals] | Just the total row. If none exists, then it returns null. | A8:E8 |
=DeptSales[#This Row] | Just the portion of the columns in the current row. #ThisRow cannot be combined with any other special item specifiers. Use it to force implicit intersection behavior for the reference or to override implicit intersection behavior and refer to single values from a column. For more examples, see Examples of using structured references. | A5:E5 (If the current row is 5) |
Qualifying structured references in calculated columns
When you create a calculated column, you commonly use a structured reference to create the formula. This structured reference can be unqualified or fully qualified. For example, to create the calculated column called, ComAmt, that calculates the amount of commission in dollars, you can use the following formulas:
TYPE OF STRUCTURED REFERENCE | EXAMPLE | COMMENT |
Unqualified | =[SaleAmt]*[ComPct] | Multiplies the corresponding values from the current row. |
Fully qualified | =DeptSales[SaleAmt]*DeptSales[ComPct] | Multiples the corresponding values for each row for both columns. |
The general rule to follow is this: If you are using structured references within a table, such as when you create a calculated column, you can use an unqualified structured reference, but if you use the structured reference outside of the table, you need to use a fully qualified structured reference.
Examples of using structured references
There are a number of ways that you can use these special items and combine them with table names and column references as the following information shows:
THIS STRUCTURED REFERENCE: | REFERS TO: | WHICH, IN THEEXAMPLE, IS CELL RANGE: |
=DeptSales[[#All],[SaleAmt]] | All the cells in the SaleAmt column. | C1:C8 |
=DeptSales[[#Headers],[ComPct]] | The header of the ComPct column. | C1 |
=DeptSales[[#Totals],[Region]] | The total of the Region column. If there is no Totals row, then it returns null. | B8 |
=DeptSales[[#All],[SaleAmt]:[ComPct]] | All the cells in SaleAmt and ComPct. | C1:D8 |
=DeptSales[[#Data],[ComPct]:[ComAmt]] | Just the data of the ComPct and ComAmt columns. | D2:E7 |
=DeptSales[[#Headers],[Region]:[ComAmt]] | Just the headers of the columns between Region and ComPct ComAmt. | B1:E1 |
=DeptSales[[#Totals],[SaleAmt]:[ComAmt]] | The totals of the SaleAmt through ComAmt columns. If there is no Totals row, then it returns null. | C8:E8 |
=DeptSales[[#Headers],[#Data],[ComPct]] | Just the header and the data of ComPct. | D1:D7 |
=DeptSales[[#This Row], [ComAmt]] | The cell at the intersection of the current row and the ComAmt column. | E5 (if the current row is 5) |
Working with structured references
Consider the following when you work with structured references.
Using Formula AutoComplete
You may find that using Formula AutoComplete is very useful when you enter structured references and to ensure the use of correct syntax. For more information, see Use Formula AutoComplete.
Deciding whether to generate structured references for tables in semi-selection
By default, when you create a formula, clicking a cell range within a table semi-selects the cells and automatically enters a structured reference, instead of the cell range in the formula. This semi-selection behavior makes it much easier to enter a structured reference. You can turn this behavior on or off by selecting or clearing the Use table names in formulas check box in the Working with formulas section of theFormulas category in the Excel Options dialog box.
Workbooks containing external links to Excel tables in other workbooks
If a workbook contains an external link to an Excel table in another workbook, that linked "source" workbook must be open in Excel to avoid #REF! error codes in the "destination" workbook that contains the links. If you open the destination workbook first and #REF! error codes appear, they will be resolved if you then open the source workbook. If you open the source workbook first, you should see no error codes.
Converting a range to a table and a table to a range
When you convert a table to a range, all cell references change to their equivalent A1 style references. When you convert a range to a table, Excel does not automatically change any cell references to this range to their equivalent table names and column references.
Turning off column headers
If you turn off table column headers (On the table Design tab, in the Table Style Options group, clearHeader Row), structured references that use these headers are not affected, and you can still use them in formulas.
Adding or deleting columns and rows to the table
Because table data ranges often change, the cell references for structured references adjust automatically. For example, if you use a table name in a formula to count all of the cells of data in the Department Sales table, such as =COUNTA(DeptSales) in The Department Sales table example, the returned value is 30 because the data range is A2:E7. If you then added a row of data, the cell reference automatically adjusts to A2:E8, and the new returned value is 35.
Renaming a table or column
If you rename a column or table, Excel automatically changes the use of that table and column header in all structured references that are used in the workbook.
Moving, copying, and filling structured references
All structured references remain the same when you copy or move a formula that uses a structured reference.
When you fill a formula, fully qualified structured references can adjust the column specifiers like a series as summarized in the following table.
IF THE FILL DIRECTION IS: | AND WHILE FILLING, YOU PRESS: | THEN: |
Up or down | Nothing | There is no column specifier adjustment. |
Up or down | CTRL | Column specifiers adjust like a series. |
Right or left | None | Column specifiers adjust like a series. |
Right or left | CTRL | There is no column specifier adjustment. |
Up, down, right, or left | SHIFT | Instead of overwriting values in current cells, current cell values are moved and column specifiers are inserted. |
Structured reference syntax rules
The following is a list of syntax rules that you need to be aware of when you create and edit structured references.
NOTE Table names follow the same rules as defined names. For more information, see Use names to clarify formulas.
The use of brackets in specifiers
All table, column, and special item specifiers must be enclosed in matching brackets ([ ]). A specifier that contains other specifiers requires outer matching brackets to enclose the inner matching brackets of the other specifiers.
Example =DeptSales[[SalesPers]:[Region]]
Column headers are text strings
All column headers are text strings, but do not require quotes when they are used in a structured reference. If a column header contains numbers or dates, such as 2004 or 1/1/2004, these are still considered text strings. Because column headers are text strings, you cannot use expressions within brackets.
Example =DeptSalesFYSummary[[2004]:[2002]]
Special characters in table column headers
If a table column header contains one of the following special characters, the entire column header must be enclosed in brackets. Effectively, this means double brackets are required in a column specifier with the following special characters: Space, tab, line feed, carriage return, comma (,), colon (:), period (.), left bracket ([) , right bracket (]), pound sign (#), single quotation mark ('), double quotation mark ("), left brace ({), right brace (}), dollar sign ($), caret (^), ampersand (&), asterisk (*), plus sign (+), equal sign (=), minus sign (-), greater than symbol (>), less than symbol (<), and division sign (/).
Example =DeptSalesFYSummary[[Total$Amount]]
The only exception to this is if the only special character that is used is a space character.
Example =DeptSales[Total Amount]
Special characters in column headers that require the use of the escape character
The following characters have special meaning and require the use of a single quotation mark (') as an escape character: Left bracket ([), right bracket (]), pound sign(#), and single quotation mark (').
Example =DeptSalesFYSummary['#OfItems]
Using the space character to improve readability in a structured reference
You can use space characters to improve the readability of a structured reference in the following way:
· One space after the first left bracket ([) and preceding the last right bracket (]).
· One space after a comma.
Example =DeptSales[ [SalesPers]:[Region] ]
Example =DeptSales[[#Headers], [#Data], [ComPct]]
No comments:
Post a Comment