Excel area tabular and structured references

Referring to regional forms, most students may be puzzled because the concept of regional and tabular language is vague. What is a zone? What is a tabular format? The Excel worksheet is not a table, why should it be tabular? Next, I'll explain these questions to you first. The concept is clear, and look at what functions are available after the table.

The concept of areas and tables we use the following two screenshots to explain:

, is the Excel worksheet that we use, the so-called area, which is the cells before the table

650) this.width=650; "Src=" https://mmbiz.qlogo.cn/mmbiz/ 4bvhjmzzhlzpdhict0c3en1fjmdrdtl5ufczkmgto9twhtgcr9pw8pibkmcsxvblnlmic9vvnqzfdnklqiaxhodhg/0?wx_fmt=jpeg "alt=" 0 ? wx_fmt=jpeg "/>

Select any cell in the range, and by doing so, we can format the area.

650) this.width=650; "Src=" https://mmbiz.qlogo.cn/mmbiz/ 4bvhjmzzhlzpdhict0c3en1fjmdrdtl5uzm1eqwe7hib7ufuqiekvhho8iagdauxet1jxc4myiboibsbpo4k1fxf3bq/0?wx_fmt=jpeg "alt= "0?wx_fmt=jpeg"/>

For tabular tables:

650) this.width=650; "Src=" https://mmbiz.qlogo.cn/mmbiz/ 4bvhjmzzhlzpdhict0c3en1fjmdrdtl5uez5jlzjhttdj2gclqqfqnucnqouxolx5epiibs2fokcicgt4bgtiaz4zg/0?wx_fmt=jpeg "alt=" 0?wx_fmt=jpeg "/>

You can see that the tabular table is used as a whole (relative to the previous area) and has its own design label. We can name the table, apply styles, add title columns, summarize columns, and so on.

What are the specific differences between the table and the area? (Personal opinion, please correct me if there is an error)

1 when you activate any cell in the table, the Table Tool tab appears in the toolbar.

2 The title of each column will automatically appear drop-down buttons for sorting and filtering

3 column header rows are automatically locked.

4 Table styles for table automatic bars after table formatting

5 Tables support structured referencing (making your tables smarter and easier to edit formulas)

6 table size can be expanded by small icons in the lower right corner of the table

7 You can delete duplicate rows by buttons.

8 Add data to automatically expand the table area (adding data below the Totals row does not automatically expand)

In short, it is tabular, automatically added a lot of features, more convenient to operate.

The area can be structured as a reference after the table has been formatted.

Only tables can be used for structured referencing.

Next we use the following two worksheets to illustrate structured references.

650) this.width=650; "Src=" https://mmbiz.qlogo.cn/mmbiz/ 4bvhjmzzhlxdid9fw2nxq6s6v4okjxkiropmmanuzhueh6ipzuppmvlacwrfroe1qcxqueoxhhfqmyw2lkqttq/0?wx_fmt=jpeg "alt=" 0?wx _fmt=jpeg "/>
We then use the S/N column as the lookup value in the sheet IT stock, and the SN column in worksheet 201407 to the module column lookup range for VLOOKUP

Here in the VLOOKUP formula written by B2 Table4[s/n] is the S/N column representing the current table (table), and Table3[sn]:[module] is the range representing the SN column of Table3 (sum201407) to the module column. When you edit a formula, the structured app automatically gives you a drop-down list for you to choose from, very smart. 650) this.width=650; "Src=" https://mmbiz.qlogo.cn/mmbiz/ 4bvhjmzzhlxdid9fw2nxq6s6v4okjxkitkosg4l7dpgiaxlouibeq74iajytdprjgziamnadqibqdz7qr8pqrc5bg/0?wx_fmt=jpeg "alt=" 0 ? wx_fmt=jpeg "/>

We write the formula in B2, and the formula automatically fills in the column of the table's formula, which is very convenient. For example, because the S/N column has no value, the return is #n/a. Here's the formula we've edited.

650) this.width=650; "Src=" https://mmbiz.qlogo.cn/mmbiz/ 4bvhjmzzhlxdid9fw2nxq6s6v4okjxki5bd3jijnz6fmyhibtsbafwoxedpgjfejyw8gm1hhk2s9avwzltafnlg/0?wx_fmt=jpeg "alt=" 0? Wx_fmt=jpeg "/>

Next we're going to glue the value of the SN column of Table3 (sum201407) to the S/N column of Talbe4, and you'll see that no matter how many lines you stick, the table will automatically expand with the number of data rows you have, and the formula will automatically expand.

650) this.width=650; "Src=" https://mmbiz.qlogo.cn/mmbiz/ 4bvhjmzzhlxdid9fw2nxq6s6v4okjxkiyapljsl5kckrurifn4ehsukbwwhysvauqojouxygfb7ffljgm7ycxq/0?wx_fmt=jpeg "alt=" 0?wx _fmt=jpeg "/>

Structured references to tables are very helpful in improving the efficiency of our daily work.

However, a structured reference to a table has canonical formatting and syntax.

We're going to explain it with the formula just now.

=vlookup ([S/n],table3[[sn]:[module]],2,false)

1 The table name is a meaningful name that provides reference to the actual tabular data (if there are header rows and total rows, excluding them). Each time you insert a table, Excel creates default table names (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 SUM, you can use the Edit Name dialog box. (On the Design tab, in the Properties group, in the Table name box, edit the table name.) ）

2 column specifier [column header name] such as [SN] with the same name as the column header, expanded by brackets to refer to this column (if there is a header row and a total row, will not include them) apply area is Table3[[sn]:[module]]

The following is a structured project descriptor

=table4[#All] the entire table, including column headings, data, and summaries, if any.

=table4[#Data] data only.

=table4[#Headers] only header row.

=table4[#Totals] totals rows only. If no summary row exists, it returns null.

=table4[#ThisRow] only the column portion of the current row. #ThisRow cannot be combined with any other special item specifiers. It can be used to enforce implicit cross-referencing of references, or to override implicit cross-behavior and to refer to single values in columns.

Examples of structured applications

=table4[[#All],[s/n]] " S/ N all cells in the column.

=table4[[#Headers],[s/n]] " S/ N Column's title.

=table4[[#Totals],[region]] " Region Summary of the column. If no totals row exists, NULL is returned .

=table3[[#All],[sn]:[module]] " TABLE3 "and" SN to all the cells in themodulecolumn.

=table3[[#Data],[sn]:[module] only the data for the "SN" and "MODULE" columns.

=table3[[#Headers],[sn]:[module]] only " SN "and" MODULE The title of the column between.

=table3[[#Totals],[sn]:[module]] from the SN "to" MODULE Summary of the column. If no totals row exists, NULL is returned .

=sn[[#Headers],[#Data],[module]] only " compct header and data for the column.

=table[[#This Row], [SN]] on the current row and SN "Cell of the intersection section of the column ... can also be written as [@SN]

This article from the "Guo Yong Technology Blog" blog, reproduced please contact the author!

Excel area tabular and structured references