在Excel中,SUBTOTAL函数被誉为“万能函数”,因为它可以实现多种数据汇总操作,包括求和、计数、求平均值、最大值、最小值等。无论是在数据处理、统计分析还是报表制作中,SUBTOTAL函数都能发挥巨大作用。接下来,我们将详细介绍SUBTOTAL函数的各个方面,帮助大家更好地理解和应用这个函数。
SUBTOTAL函数的主要作用是返回一个数据列表或数据库的分类汇总。它的语法结构很简单:
```excel
=SUBTOTAL(功能代码, 数值区域)
```
这里,“功能代码”决定了SUBTOTAL函数执行的汇总操作类型,而“数值区域”则是你要进行汇总操作的数据范围。
SUBTOTAL函数的功能代码分为两组:1-11和101-111。这两组代码对应的函数名称相同,但功能有所不同。
1-11:包含手动隐藏的值。也就是说,即使某些数据行或列被隐藏,SUBTOTAL函数仍然会考虑这些值进行计算。
101-111:不包含手动隐藏的值。只对可见单元格进行汇总操作。
常见的功能代码及其对应的函数名称如下:
1:求平均值(AVERAGE)
9:求和(SUM)
2:计数(COUNTA),非空单元格个数
3:计数(COUNT),忽略错误值、文本等,仅计算数值单元格个数
4:求最大值(MAX)
5:求最小值(MIN)
101:忽略隐藏值的求平均值
109:忽略隐藏值的求和
102:忽略隐藏值的计数(COUNTA)
103:忽略隐藏值的计数(COUNT)
104:忽略隐藏值的求最大值
105:忽略隐藏值的求最小值
如果你想要对一个数据区域进行求和,并且希望计算结果包含隐藏的行或列,可以使用功能代码9。如果你只希望对可见单元格求和,可以使用功能代码109。
```excel
=SUBTOTAL(9, D3:D9) // 包含隐藏值求和
=SUBTOTAL(109, D3:D9) // 忽略隐藏值求和
```
同样地,如果你希望计算包含隐藏值的平均值,可以使用功能代码1。如果你只希望对可见单元格求平均值,可以使用功能代码101。
```excel
=SUBTOTAL(1, D3:D9) // 包含隐藏值求平均值
=SUBTOTAL(101, D3:D9) // 忽略隐藏值求平均值
```
在数据表中,有时候我们需要在隐藏或筛选数据后保持序号的连续性。这时,可以使用SUBTOTAL函数的计数功能(功能代码103),因为它只对可见非空单元格进行计数。
```excel
=SUBTOTAL(103, B$2:B2)
```
这个公式会在每次隐藏或筛选数据时,自动更新序号,保持其连续性。
如果你想要找到当前可见单元格中的最大值,可以使用功能代码104。
```excel
=SUBTOTAL(104, D3:D9)
```
这个公式会忽略隐藏的行或列,只计算可见单元格中的最大值。
在数据筛选后,如果你想要计算当前可见单元格的平均值,可以使用功能代码1或101。但需要注意的是,SUBTOTAL函数在筛选后并不会改变其计算范围,它只是简单地忽略隐藏的行或列。
```excel
=SUBTOTAL(1, D3:D9) // 筛选后计算包含隐藏值的平均值(实际结果与筛选前相同,但可见部分正确)
=SUBTOTAL(101, D3:D9) // 筛选后计算可见单元格的平均值
```
虽然代码1在筛选后计算的结果与筛选前相同(因为它包含隐藏值),但如果你只关心可见单元格的平均值,应该使用代码101。
在财务报表中,经常会有小计和合计的行。如果你使用SUBTOTAL函数来计算合计,并且小计也是用SUBTOTAL函数计算的,那么SUBTOTAL函数会自动忽略由SUBTOTAL函数计算出来的数值。
```excel
=SUBTOTAL(9, C2:C12)
```
这个公式会计算C2:C12
49.56M清茶影视纯净版
49.56M清茶影视安卓版APP
49.56M清茶影视免费追剧软件
0B模拟驾驶2
64.14M魔豆影视正版软件
24.90M魔兽兄弟
64.14M魔豆影视手机版
9.70M地牢猎手2
322.42M未上锁的房间2
0Bace虚拟歌姬
本站所有软件来自互联网,版权归原著所有。如有侵权,敬请来信告知 ,我们将及时删除。 琼ICP备2023003481号-5