You are watching: Which of the following is an unqualified structured reference
The Department Sales table exampleThe 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:E8) A column and column header (D1:D8) A calculated column (E1:E8) The Totals row (A8:E8)
Components of a structured referenceTo 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 specifiersEach 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,
Reference operatorsFor added flexibility in specifying ranges of cells, you can use the following reference operators to combine column specifiers.
Special item specifiersFor 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:
Qualifying structured references in calculated columnsWhen 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:
Examples of using structured referencesThere 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:
Working with structured referencesConsider the following when you work with structured references.Using Formula AutoCompleteYou 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-selectionBy 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 the Formulas category in the Excel Options dialog box.Converting a range to a table and a table to a rangeWhen 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 headersIf you turn off table column headers (On the table Design tab, in the Table Style Options group, clear Header 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 tableBecause 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 columnIf 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 referencesAll 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.
See more: 40% Of 25000 ? What Is 40% Of 25000