文章背景: 若要简化一组相关数据的管理和分析,可以将一组单元格范围转化为Excel表(Table
)。将数据区域转换为Table
后,具有能够自动筛选和排序、Table范围自动扩大等优点。下面主要对Table
的结构化引用的语法规则进行介绍。
示例表格如下(表名:DeptSales):
如果在表(DeptSales)外对Sales Amount这一列数据求和,可以采用的公式为:=SUM(DeptSales[Sales Amount])。
结构化引用的公式中涉及的内容主要有:
结构化引用的公式示例:
相对引用和绝对引用的变化:
When using structured referencing, a mixture of relative referencing and absolute referencing is employed by Excel. Generally, referencing tends to be absolute for formulas copied vertically but relative for formulas copied horizontally. To convert from relative to absolute structured referencing, the name for the column header needs to be duplicated
before the formula is copied horizontally.
例如:如果希望在水平方向上拖拽公式时,列名不发生变化,可以采用DeptSales[ [#Headers], [Sales Amount]:[Sales Amount] ],而不使用DeptSales[ [#Headers], [Sales Amount] ]。
参考资料:
[1] Excel Skills for Business: Advanced(https://www.coursera.org/learn/excel-advanced/supplement/7QhQv/keyboard-shortcuts-terminology-and-ninja-tips)
[4] Using structured references with Excel tables(https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e)