Excel还能这么玩—建立数据自动化监控平台（Excel进阶功能）

Excel作为数据分析师必备的数据工具之一，它的功能是十分强大的。从数据界处理数据，到游戏界设计游戏，再到艺术界画图，Excel不断地刷新着人们对它的认知。

Excel还能这么玩—建立数据自动化监控平台（Excel进阶功能）

注：右图为日本77岁的艺术家堀内辰男在Excel Autoshape Contest大赛中的获奖作品

尽管Excel涉及的领域很广，但它在它的“本职工作”（表格处理）上尤为出色。我们大都知道Excel在数据处理领域十分出色，但很少知道Excel在数据自动化监控方面也能发挥巨大作用。大家平时使用的数据监控平台大都是由专业的程序员用编程语句编写的，本文将告诉你通过简单的几个公式我们也可以建立数据自动化监控平台。部分示例如下，我们可以通过选择任意日期知道某段时间内的销售情况。

上图用到的函数主要是countifs、sumifs以及averageifs三个函数，接下来将详细介绍如何做这个。源数据是取自某电商平台2011-09/01-2011-09-20日的销售明细数据。

Excel还能这么玩—建立数据自动化监控平台（Excel进阶功能）

函数介绍

1、countifs函数

函数定义：用来计算多个区域中满足给定条件的单元格的个数。

函数用法：countifs(criteria_range1,criteria1,criteria_range2,criteria2,…)

参数解释：

criteria_range1为第一个需要计算其中满足某个条件的单元格数目的单元格区域(简称条件区域)，criteria1为第一个区域中将被计算在内的条件(简称条件)，其形式可以为数字、表达式或文本。例如，条件可以表示为 48、"48"、">48" 、 "广州" 或 A3;

同理，criteria_range2为第二个条件区域，criteria2为第二个条件，依次类推。最终结果为多个区域中满足所有条件的单元格个数。

countifs和countif(range，criteria)的区别：countifs是统计满足多个条件的单元格的数量（包括一个条件），而countif是统计满足一个条件的单元格的数量。一个条件时也可以用countifs，所以为了方便，我个人偏向于都用countifs函数。

案例详解：

如下图，求订单量公式如下：=COUNTIFS(源数据!B:B,"<="&B2,源数据!B:B,">="&B1)。

目的是要求开始日期到结束日期之间的订单量。由于订单号是唯一值，均只有一条记录。所以不需要去重，只需要限制日期条件即可计算出订单量。因此限定日期“≥开始日期”且“≤结束日期”，“&”符号是连接符，源数据B列代表日期。

Excel还能这么玩—建立数据自动化监控平台（Excel进阶功能）

2、sumifs函数

函数定义：使用该函数可快速对多条件单元格求和

函数用法：sumifs(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

参数定义：

criteria_range1为计算关联条件的第一个区域。criteria1为条件1，条件的形式为数字、表达式、单元格引用或者文本，可用来定义将对criteria_range1参数中的哪些单元格求和。例如，条件可以表示为32、">32"、B4、"苹果"、或"32"。

同理，criteria_range2为第二个条件区域，criteria2为第二个条件，依次类推。最终结果为多个区域中满足所有条件的单元格个数。

sum_range 是需要求和的实际单元格。包括数字或包含数字的名称、区域或单元格引用。忽略空白值和文本值。

sumifs和sumif(range，criteria，sum_range)的区别：

sumifs是求满足多个条件的单元格的和，而sumif是统计满足一个条件的单元格的和。另外，sumifs和sumif用法上有区别，sumifs待求和列在第一个参数上，sumif待求和列在第三个参数上。一个条件时也可以用sumifs，所以为了方便，我个人偏向于都用sumifs函数。

案例详解：

如下图，求销售数量公式如下：=SUMIFS(源数据!F:F,源数据!B:B,"<="&B2,源数据!B:B,">="&B1)。

目的是要求开始日期到结束日期之间的商品销售数量。只需限制日期条件，得出满足条件的值并求和即可。因此限定日期“≥开始日期”且“≤结束日期”，“&”符号是连接符，源数据B列代表日期，源数据F列是销售数量列。

Excel还能这么玩—建立数据自动化监控平台（Excel进阶功能）

3、averageifs

函数定义：averageifs函数是一个求平均值函数，主要是用于返回多重条件所有单元格的平均值。

函数用法averageifs(average_range,criteria_range1,criteria1,criteria_range2,criteria2,...)

参数解释：

criteria_range1为计算关联条件的第一个区域。criteria1为条件1，条件的形式为数字、表达式、单元格引用或者文本，可用来定义将对criteria_range1参数中的哪些单元格求平均值。例如，条件可以是数字10、表达式">12"、文本"上海发货平台" 或 C2。

同理，criteria_range2为第二个条件区域，criteria2为第二个条件，依次类推。最终结果为多个区域中满足所有条件的单元格个数。

average_range是需要求平均值的实际单元格。包括数字或包含数字的名称、区域或单元格引用。忽略空白值和文本值。

averageifs和averageif(range, criteria, [average_range])区别：

averageifs是求满足多个条件的单元格的平均值，而averageif是统计满足一个条件的单元格的和。另外，averageifs和averageif用法上有区别，averageifs待求平均值列在第一个参数上，averageif待求平均值列在第三个参数上。一个条件时也可以用averageifs，所以为了方便，我个人偏向于都用averageifs函数。

案例详解：

如下图，求整体平均客单价金额公式如下：=AVERAGEIFS(源数据!G:G,源数据!B:B,"<="&B2,源数据!B:B,">="&B1)。

目的是要求开始日期到结束日期之间的商品平均客单价。只需限制日期条件，得出满足条件的值并求平均值即可。因此限定日期“≥开始日期”且“≤结束日期”，“&”符号是连接符，源数据B列代表日期，源数据G列是销售金额列。

Excel还能这么玩—建立数据自动化监控平台（Excel进阶功能）

4、绝对引用、混合引用以及相对引用

①定义

绝对引用：单元格中的绝对单元格引用(例如 $A$1)总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，需要将它们转换为绝对引用。例如，如果将单元格B2 中的绝对引用复制到单元格B3，则在两个单元格中一样，都是 $A$1。

混合引用：混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用 $A1、$B1 等形式。绝对引用行采用 A$1、B$1 等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。例如，如果将一个混合引用从 A2 复制到 B3，它将从 =A$1 调整到 =B$1。

相对引用：公式中的相对单元格引用(例如 A1)是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。例如，如果将单元格 B2 中的相对引用复制到单元格 B3，将自动从 =A1 调整到 =A2。

②案例详解：

绝对引用和混合引用

产品A在2011-09-01日的销售金额公式为：=SUMIFS(源数据!$G:$G,源数据!$B:$B,"="&H$14,源数据!$D:$D,$F15)。公式中的：“源数据!$G:$G”、“源数据!$B:$B”，“源数据!$D:$D”是对源数据中G列、B列以及D列绝对引用，“H$6”和“$F15”是对单元格H6和F15的混合引用。为什么要这么用呢？

这个区域单元格的目的是为了求产品X在X日的销售金额，一个一个单元格地输入公式是很费时低效率的一种方法。这时用到绝对引用和混合引用之后，只需要在H15输入公式后，拉动H15单元格右下角的十字填满目标区域即可。在这里，绝对引用在公式中相当于固定列或者固定单元格了。混合引用在公式中相当于固定单元格“横移动竖移不动”和“竖移动横移不动”。

H15单元格的目的是为了求产品A在2011-09-01日的销售金额。只需限制：【源数据!$B:$B,"="&H14】（也可【源数据!$B:$B,H14】），【源数据!$D:$D,F15】。（源数据B列是日期，源数据D列是产品）

竖向的：H16单元格则为：【源数据!$B:$B,"="&H14】，【源数据!$D:$D,F16】；H17单元格则为：【源数据!$B:$B,"="&H14】，【源数据!$D:$D,F17】... ...；

横向的：I15单元格则为：【源数据!$B:$B,"="&I14】，【源数据!$D:$D,F15】；J15单元格则为：【源数据!$B:$B,"="&J14】，【源数据!$D:$D,F15】... ...；

也就是我们产品类型是列变动，日期是行变动。最终H15单元格公式为：=SUMIFS(源数据!$G:$G,源数据!$B:$B,"="&H$14,源数据!$D:$D,$F15)。“H$14”代表固定单元格“14”不动，“H”动，即“横移动竖移不动”，“$F15”代表固定单元格“F”不动，“15”动，即“竖移动横移不动”。（绝对引用、混合引用以及相对引用的快捷键是:Fn+F4，电脑型号不一样情况不一样，有的电脑直接F4即可）

Excel还能这么玩—建立数据自动化监控平台（Excel进阶功能）

绝对引用和相对引用

产品A的销售数量公式为：=SUMIFS(源数据!$F:$F,源数据!$B:$B,"<="&$B$2,源数据!$B:$B,">="&$B$1,源数据!$D:$D,数据看板!A15)。公式中的：“源数据!$F:$F”、“源数据!$B:$B”，“源数据!$D:$D”是对源数据中G列、B列以及D列绝对引用，“数据看板!A15”是对单元格H6和F15的相对引用引用。复制B15单元格粘贴到B16单元格，公式就会变成：=SUMIFS(源数据!$F:$F,源数据!$B:$B,"<="&$B$2,源数据!$B:$B,">="&$B$1,源数据!$D:$D,数据看板!A15)；复制B15单元格粘贴到C17单元格，公式就会变成：=SUMIFS(源数据!$F:$F,源数据!$B:$B,"<="&$B$2,源数据!$B:$B,">="&$B$1,源数据!$D:$D,数据看板!B16)。单元格随着相对位置的相对变化，这就是相对引用。

Excel还能这么玩—建立数据自动化监控平台（Excel进阶功能）

总结建议

建立数据自动化监控平台，首先先将需要监控的指标体系列出来（下面有建立数据指标体系的具体文章辅助阅读）；接着将这些指标归纳排版列入Excel表中，通过上述公式整合运算完成；最后添加辅助列表以及图表，帮助更直观的了解现况和发现问题。