地质资源量计算常用的Excel三个函数
2017-02-27 10:01阅读：
一、分类汇总函数（SUBTOTAL）
Excel中
SUBTOTAL函数是一个汇总函数，优点在于可以忽略隐藏的单元格、支持三维运算和区域数组引用。
SUBTOTAL函数就是返回一个列表或数据库中的分类汇总情况。SUBTOTAL函数可谓是全能王,可以对数据进行求平均值、计数、最大最小、相乘、标准差、求和、方差。
SUBTOTAL函数语法：SUBTOTAL(function_num, ref1, ref2, ...)
第一参数：Function_num为 1-11（包含隐藏值）或
101-111（忽略隐藏值）之间的数字，指定使用何种函数在列表中进行分类汇总计算。
第二参数：Ref1、ref2为要进行分类汇总计算的 1 到 254 个区域或引用。
SUBTOTAL函数的使用关键就是在于第一参数的选用。
SUBTOTAL第1参数代码对应功能表如下：
参数
|
参数
|
相当函数
|
中文含义
|
1
|
101
|
AVERAGE
|
可见单元格平均值
|
2
|
102
|
|
COUNT
可见单元格包含数字的单元格个数
|
3
|
103
|
COUNTA
|
可见单元格包含非空单元格个数
|
4
|
104
|
MAX
|
可见单元格最大值
|
5
|
105
|
MIN
|
可见单元格最小值
|
6
|
106
|
PRODUCT
|
可见单元格内所有值的乘积
|
7
|
107
|
STDEV
|
可见单元格内估算基于给定样本的标准偏差
|
8
|
108
|
STDEVP
|
可见单元格内计算基于给定的样本总体的标准偏差
|
9
|
109
|
SUM
|
可见单元格求和
|
10
|
110
|
VAR
|
可见单元格内估算基于给定样本的方差
|
11
|
111
|
VARP
|
可见单元格内计算基于给定的样本总体的方差
|
SUBTOTAL函数功能比较全面，与其它专门函数相比有其独特性与局限性：
1：可以忽略隐藏的单元格，对可见单元格的结果进行运算;经常配合筛选使用
，但只对行隐藏有效，对列隐藏无效；此处第一参数与对二参数对隐藏的区别：在于手工隐藏,对筛选的效果是一样的。
2：支持三维运算。
3：只支持单元格区域的引用
4:第一参数支持数组参数。
二、Excel中加权平均数
1.公式：
•
公式：“=SUMPRODUCT(B2:B4,C2:C4)/SUM(B2:B4)”。
•
输入SUM(B2:B4*C2:C4)/SUM(B2:B4)，然后按下Ctrl Shift
Enter三键结束数组公式的输入。
2.含义
•
SUMRPOUCT函数求得品位厚度的乘积之和，再除以总厚度
3、实例运用
•
用SUMPRODUCT和SUM函数计算加权平均数。
•
在Excel中用SUMPRODUCT和SUM函数可以很容易地计算出加权平均数。如何计算下图所示的采购的加权平均数？
三、多条件求和函数（Sumifs）
1.sumifs函数的含义
sumifs函数是多条件求和，用于对某一区域内满足多重条件（两个条件以上）的单元格求和。
2.sumifs函数的语法格式
=sumifs(sum_range，criteria_range1，criteria1，[criteria_range2，criteria2],
...)
sumifs（实际求和区域，第一个条件区域，第一个对应的求和条件，第二个条件区域，第二个对应的求和条件，第N个条件区域，第N个对应的求和条件）。
3.sumifs函数案列
如图，计算各发货平台9月份上半月的发货量。两个条件区域（1.发货平台。2.9月上半月。）
=SUMIFS(C2:C13,A2:A13,D2,B2:B13,'<=2014-9-15')
=SUMIFS(C2:C13—
求和区域发货量,A2:A13—
条件区域各发货平台,D2—
求和条件成都发货平台,B2:B13—
条件区域发货日期,'<=2014-9-15'—
求和条件9月份上半月)
4.sumifs函数注意问题
（1）绝对引用与相对引用
我们通常会通过下拉填充公式就能快速把整列的发货量给计算出来。但不使用绝对引用的话，求和区域和条件区域会变动，这时就涉及到绝对引用和相对引用的问题。通过添加绝对引用“$”，公式就正确了。
=SUMIFS($C$3:$C$14,$A$3:$A$14,D2,$B$3:$B$14,'<=2014-9-15')
（2）sumif函数和sumifs函数是有区别的。
Sumifs函数的语法格式，第一个参数是求和区域，这个和sumif函数刚好相反，sumif的求和区域在最后。
有关sumif函数的经验可以观看小便的经验Excel中Sumif函数的使用方法。
（3）sumif函数参数criteria如果是文本
sumif函数参数criteria如果是文本，要加引号；且引号为英文状态下输入
=sumifs(sum_range, criteria_range1, criteria1, [criteria_range2,
criteria2], ...)。的参数criteria如果是文本，要加引号。且引号为英文状态下输入。
（4）我们在sumifs函数是使用过程中，我们要选中条件区域和实际求和区域时，当数据上万条时，手动拖动选中时很麻烦，这时可以通过ctrl
shift ↓选中。
