Excel中的计算就好比电子表格的引擎室，将数据激活，并帮助我们从中提取信息，因此，当我们构造公式时，要确保没有出错的余地。Excel对于公式和函数的语法有着非常明确的期待，即使是一个小错误，比如缺少一个括号或缺少一个参数，也会产生一条错误消息，并提示我们修复它。
随着电子表格的任务变得更加复杂，我们需要在上一期（Excel表格设计的一般原则，了解这些让你不再对数据无从下手！）所了解到的基本原则之上，尽我们所能防止这些错误。准确性至关重要，因此我们需要有内置的制衡机制，以捕获计算中的任何潜在错误。为了使我们的电子表格保持灵活性和响应性，我们需要对Excel表格中的计算进行一定的设计，以便公式能够自动进行更新，并且在工作簿的其他部分发生更改时不会导致公式中断。我们还需要确保工作簿易于维护且用户使用的友好性。因此，让他人能够理解我们的计算，并追踪其如何得出结果的，这些是很有必要的。换句话说，我们的计算必须是可审的和透明的。
我们通过以下的案例来了解一下在设计Excel运算的过程中需要注意些什么。
在处理数据的计算时，我们尽可能将计算单独放在一个指定的工作表中，但偶尔一些数据计算可以在原始数据表格中进行，视情况而定。例如，在如下的“Data”工作表中，我们通过计算来获取“Region”列的数据。
为了提高公式的可审性和有效性，我们在此使用INDEX和MATCH的经典搭配（Excel之INDEX与MATCH函数的经典搭配，兼具查询的效率与灵活性！）。
INDEX函数查询的区域（array）是Lookup工作表中Region列的数据，而在此之前我们已经为其创建了名称“Region”，所要查询的行号（row_number）使用MATCH函数来查找，查询当前M列“Suburb”的数据，所在的区域是Lookup工作表中Suburb列的数据。
在“Region”列后面，我们添加两列辅助数据列，分别为“Month”和“Year”用来获取“Date”列中日期的月份和年份。
新建一个名为“Calc”的工作表，用于单独的计算某些数据。
首先，我们先通过MAX函数来获取最近的的销售日期“Last Sale Date”：=MAX(tbl_Sales[Date])。
在此，我们看到函数中的参数基本上所使用的都是所创建的“表”和“名称”，这样在复杂的运算中可以省去不少时间和精力来跨表选择数据，并且当原始数据有所更新时，公式中引用的参数也会随之自动进行更新。
在“Last Sale Date”基础上，我们整理出一个数据表格，用于统计过去12个月中各个月不同类型的销售数据。
在“Date”列中，我们通过函数EOMONTH来进行计算：=EOMONTH(B3,-12)+1。
EOMONTH计算出“Last Sale Date”日期12个月之前的日期，加上1之后则是该日期之后的第一天。
在“House”列下，我们通过AVERAGEIFS函数来计算该类型在其对应的“Month”和“Year”日期下的平均值：=AVERAGEIFS(tbl_Sales[Price],tbl_Sales[Type],Calc!D$6,tbl_Sales[Year],Calc!$C7,tbl_Sales[Month],Calc!$B7)。
所要计算的平均值是“Price”列中的数据，第一个条件是“Type”，对应的是“House”（使用混合引用，即锁定行，确保公式在向右拖拽时随列的变化而变化），第二个条件是“Year”，对应的是年份（使用混合引用，即锁定列，确保公式在向下拖拽时随行的变化而变化），第三个条件是“Month”，对应的是月份（使用混合引用，即锁定列，确保公式在向下拖拽时随行的变化而变化）。
然而，在此我们需要注意的是，因为当前的公式中使用了结构化引用（例如：tbl_Sales[Price]等），所以不能直接进行公式的拖拽，但可以直接进行公式的复制。
在此数据表格中，我们仅计算了2016年10月的数据，通过另一个函数EDATE在“Date”中返回之后11个月的日期：=EDATE(A7,1)。
在“Date”的基础上，选中B7至F7单元格区域，鼠标放在F7单元格右下角，变成黑色十字后，双击鼠标快速完成数据的填充。
通过以上的案例，我们只是简单地了解一下如何从原始的数据集中，在公式函数的辅助下获取我们想要的数据来进行相关的计算，从而在处理后的数据上进行后续的操作，例如制作报表（图表）等。
关于以上使用到的一些函数，如AVERAGEIFS函数，可参考文章：Excel系列之AVERAGEIF和AVERAGEIFS函数，其他的一些与日期相关的函数如EOMONTH，EDATE等，在后续的更新中，我们会进一步介绍，敬请期待！
对于数据的运算，其实相关的原则已在本期的开始提到：准确、灵活、实时响应、易于维护且便于用户使用，尽量简短而不复杂，换句话说，就是既给自己省心，又要让其他用户使用得舒服。
网友评论