|
本期我们来了解一下公式中稍高级一点的技巧，主要是关于“表”与结构化引用的结合。 我们来看一下演示案例：汇总数据。 根据工作表中左侧的季度销售数据，在右侧的汇总表格中进行数据的统计。
在进行数据的汇总或统计之前，我们可以先将原始数据放在“表”中，创建数据“表”的益处之一是：“表”会视其中的数据为一个独立的范围，这样我们可以通过一些方式来获取与之相关的信息，例如数据一共有多少行等。 在M4单元格中，我们可以通过ROWS函数来得到当前的数据一共有多少行，即总的季度数。ROWS函数的参数为一个数组，所以我们可以直接引用已经为数据创建的“表”，名为“Sales”。M4单元格中完整的公式为：=ROWS(Sales)
关于如何创建数据“表”，请参考文章： Excel“表”的自动化操作——快速新增行列与统计数据！ 按Enter键后，ROWS函数返回的结果是数据“表”的数据总行数，这其中不包括数据表格的首行——标签行。
在I4单元格中，我们通过一个SUM函数来求单个季度的数据总和，其完整公式为：=SUM(Sales[@[Australia]:[China]])，当选择计算区域C4至H4单元格时，SUM函数中的参数会使用“表”名“Sales”与相对应的数据列的名称，而“@”表示的是当前行，即SUM函数求的是当前行的从“Australia”到“China”列的数据。
按Enter键后，其他季度的数据求和操作会自动进行，这也就是结构化引用的优势之一，极大地提升了数据处理的效率。
在以上各个季度的数据求和基础之上，我们了解到“@”是用于计算当前行的数据，那如果我们拿掉这个符号，是不是意味着可以求得所有季度的数据总和。 在M3单元格中输入公式：=SUM(Sales[[Australia]:[China]])，我们可看到会选中所有季度的数据区域。
在使用结构化引用时，我们会用到一个中括号符号“[ ]”，例如以上的公式中所表示的，在“Sales”表中的“[Australia]”列至“[China]”列的数据。 通过这种结构化引用的方式，我们在M7单元格中来获取这些国家对应的标题，在输入“=Sales[[”后，我们可以选择“#标题”项。
在“=Sales[[#标题],[”后，再选择“Australia”。
补充完整个公式后，按Enter键，即可返回第一个数据标题“Australia”。
我们将鼠标放在M7单元格的右下角，变成黑色十字后，向右拖拽，可快速填充其他数据标题；我们看到此处的结构化引用，实际上可以通过相对引用来获取数据。
横向拖拽时，结构化引用可进行相对引用的操作，但是纵向拖拽时，则仅可进行绝对引用的操作。 在L8单元格中，通过MAX函数来获取“Year”列中的年份，完整公式为：=MAX(Sales[Year])-K8，MAX函数得到“Year”列中最大的年份为“2017”，后面减去K8单元格中隐藏的数据“0”（K8至K12中依次递增为0到4）；当我们向下进行拖拽填充时，即可明白此处“Sales[Year]”结构化引用使用的是绝对引用，至于L8至L12单元格年份数据依次递减则是因为后面减去K8至K12单元格中隐藏的数据。
在M8单元格中，我们通过SUMIFS函数来获取各个国家当年的的数据总和：=SUMIFS(Sales[Australia],Year,$L8)。
SUMIFS函数中，求和的区域是“Sales”表的“Australia”列的数据，条件区域是“Year”列中的数据，条件是L8单元格中的年份，这里的条件使用混合引用，即锁定L列，不锁定行。因此，在向下填充时，仅会针对年份的不同而使用相对引用进行统计，向右填充时，仅会针对国家（列标题）的不同而使用相对引用。
我们也可以创建一个绝对的结构化引用，在“TopQtr”表格中，我们要获取各个国家的排在前三位的季度数据，在M16单元格中，我们使用LARGE函数：=LARGE(Sales[Australia],TopQtr[@[Best Quarters]:[Best Quarters]])。 LARGE函数中第一个参数是数组，即要得到的数据所在区域，第二个参数为k，当k为1时，得到最大的数值，k为2则得到第二大的数值，以此类推，所以当我们使用L16单元格中的数据时，需要其固定不变，即使用绝对引用，但是因为我们当前的数据在“表”中，所以会自动使用结构化引用，因此以“TopQtr[@[Best Quarters]:[Best Quarters]]”形式来使其绝对化，即在TopQtr“表”中，引用当前行的“[Best Quarters]”到“[Best Quarters]”的数据，实际上就是L16单元格。
以上即是关于Excel中数据“表”中使用结构化引用，以及相对的结构化引用和绝对的结构化引用的相关内容。我们可以这样去理解：在建好的一个房子里，分成不同的区域，在进行统计时，就是通过此房子找到我们所需要的区域。 通过以上的案例，我们会发现使用结构化引用可以很好地提高我们处理数据的工作效率，你是否也这样觉得呢？ #新技能get# |
|
|
