第六章-数据统计-EXCEL常用函数使用技术

1 Excel四则运算

1.1 表达式

1.2 常见错误

2 Excel单元格行列混合引用

3 常见函数应用

3.1 综合类

最大值——MAX()
最小值——MIN()
第N大值 ——LARGE(区域或数组，N)
第N小值——SMALL(区域或数组，N)
平均值——AVERAGE()
排名——RANK()

3.2 计数

COUNT函数——返回包含数字以及包含参数列表中的数字的单元格的个数。利用COUNT函数可以计算单元格区域或数字数组中数字字段的输入项个数。
COUNTA函数——返回参数列表中非空值的单元格个数。利用函数COUNTA可以计算单元格区域或数组中包含数据的单元格个数。
COUNTBLANK——返回参数列表中空值的单元格个数。利用函数COUNTBLANK可以计算单元格区域或数组中包含空格的单元格个数。

3.3 整数和小数

ROUND函数——可将某个数字四舍五入为指定的位数。
ROUND(number,0)——四舍五入

INT函数——将数字向下舍入到最接近的数字

本节小结

1-排名函数RANK第二个区域往下拖动的时候，区域会相对往下变化。为了区域不变化保持排名的准确，所以F4键锁定第二个区域。
心
2-三个计数函数用处大，后续统计数据经常用的到。
3-整数和小数两个函数使用频率高，特别是财务报表数据。

4 统计函数

4.1 SUM函数

（1）交叉数据求和

方法1：

方法2：=SUM(7:8 C:C,7:8 E:F)
注意是英文状态下的空格哦！

（2）小计与合计

步骤：选中区域——定位条件——空值——按求和快捷键"alt+="

4.2 PRODUCT函数

PRODUCT函数——求乘积

4.3 SUMPRODUCT函数

微软官方学习资料

5 条件计数函数

5.1 COUNTIF函数

（1）使用方法

微软官方学习资料
=COUNTIF(要检查哪些区域? 要查找哪些内容?)

（2）案例1

（3）案例2-结合单元格引用

（4）案例3-包含特定字符的计数-通配符

1）姓名为2字——=COUNTIF(D56:D62,“??”)
2）姓名为2字且姓为张——=COUNTIF(D56:D62,“张?”)
3）张姓的人员——=COUNTIF(D56:D62,“张*”)
4）第二个字为"十"——=COUNTIF(D56:D62,“?十*”)
5）姓名中含"三"——=COUNTIF(D56:D62,“*三*”)

5.2 COUNTIFS函数

微软官网学习资料

5.3 常见通配符

6 统计不重复个数

使用SUM函数和COUNTIF函数

思路：
1）先计算每个项目出现的次数
=COUNTIF(B$4:B$14,B4)
2）多个数被1除，多个数只算1个
=1/C4
3）通过sum函数进行求和
=sum(D4:D14)

✳简化方法：=SUM(1/COUNTIF(B4:B14,B4:B14))——按ctrl+shift+enter
注意：ctrl+shift+enter数组公式
数组公式，就是在统计条件的时候，有多个条件进行计算、查找、求和，需要按ctrl+shift+enter

7 条件求和函数

7.1 SUMIF单条件求和函数

（1）使用方法

微软官方学习资料

（2）案例

7.2 SUMIFS多条件求和函数

（1）使用方法

（2）案例

8 中式排名PK美式排名

8.1 美式排名RANK

美式排名：出现相同数据时，会相同排名，但是名次会断裂

8.2 中式排名SUMPRODUCT

中式排名：出现相同数据时，会相同排名，但是名次不会断裂


公式！！
SUMPRODUCT((区域>=区域中排名的数字)*1/COUNTIF(区域,区域))

9 逻辑判断函数

9.1 TRUE/FALSE用法

9.2 AND/OR用法

9.3 IF及IF多层嵌套

10 ROW+COLUMN获取行号和列标

10.1 使用方法

ROW微软官方学习资料
COLUMN微软官方学习资料

10.2 九九乘法表

11 VLOOKUP+COLUMN+MATCH查找函数

11.1 MATCH

（1）使用方法

MATCH微软学习资料
使用 MATCH 函数在 范围 单元格中搜索特定的项，然后返回该项在此区域中的相对位置。

（2）案例

11.2 VLOOKUP

（1）使用方法

VLOOKUP微软学习资料
当需要按行查找表格或区域内容时，请使用 VLOOKUP。
=VLOOKUP (要查找的项、要查找位置、区域中包含要返回的值的列号、返回近似匹配或精确匹配 - 指示为 1/TRUE 或 0/FALSE)

（2）案例1-精确查找

方法1：

方法2：VLOOKUP+COLUMN

方法3：字段不匹配的情况用VLOOKUP+MATCH

（3）案例2-模糊查找

【注意】vlookup查找的值必须在首列，升序排序

（4）案例3-通配符查找

12 INDEX+MATCH 组合查找函数

12.1 VLOOKUP+MATCH和INDEX+MATCH比较

12.2 使用方法

INDEX微软学习资料

12.3 案例

13 LOOKUP多条件查找函数

13.1 使用方法

掌握Offset的使用方法，理解Offset函数返回的可以是一个单元格，也可以是个区域适用于偏移区域，后续用于动态图表数据引用有非常好的用途!
LOOKUP微软学习资料

13.2 案例-多条件查询

通用格式=LOOKUP(0,0/((区域1=条件单元格1)*(区域2=条件单元格2),数据结果列)

14 OFFSET偏移函数

14.1 使用方法

OFFSET微软学习资料

14.2 案例

15 INDIRECT转置函数

15.1 使用方法

在 Excel 中，Indirect函数用于返回文本字符串指定的单元格引用；它共有两个参数，第一个参数是文本字符串，第二个参数是引用类型，特别要注意第一个参数必须为文本，否则会返回错误。
INDIRECT微软学习资料

15.2 案例1

15.3 案例2

15.4 案例3-一列变6列

方法1：


方法2：非函数方法

16 MID字符串截取函数

16.1 使用方法

MID函数微软学习资料

16.2 案例1-从身份证提取生日

16.3 案例2-从身份证提取性别

ISODD判断是奇数
ISEVEN判断是偶数

17 LEFT,RIGHT,LEN,LENB函数

17.1 使用方法

17.2 案例

方法1：输入数据——数据——快速填充

方法2：LEFT+LEN+LENB
函数解题的前提是数据有规律

18 FIND+SEARCH函数应用

18.1 FIND函数使用方法

https://support.microsoft.com/zh-cn/office/find-findb-%E5%87%BD%E6%95%B0-c7912941-af2a-4bdf-a553-d0d89b0a0628

18.2 SEARCH函数使用方法

https://support.microsoft.com/zh-cn/office/search-searchb-%E5%87%BD%E6%95%B0-9ab04538-0e55-4719-a72e-b6f54513b495

18.3 案例

（1）提取用户名

（2）MID+SEARCH

19 SUBSTITUTE+REPLACE函数

19.1 SUBSTITUTE使用方法

https://support.microsoft.com/zh-cn/office/substitute-%E5%87%BD%E6%95%B0-6434944e-a904-4336-a9b0-1e58df3bc332

19.2 REPLACE使用方法

https://support.microsoft.com/zh-cn/office/replace-replaceb-%E5%87%BD%E6%95%B0-8d799074-2425-4a8a-84bc-82472868878a

19.3 案例

（1）

（2）

（3）

（4）

20计算年月日

20.1 使用方法

20.2 案例

21计算时间

21.1 使用方法

21.2 案例

（1）

步骤1：

步骤2：

效果图：

（2）

22计算工作日

22.1 使用方法

22.2 案例

（1）

（2）

23 DATEDIF综合日期函数

23.1 使用方法

23.2 案例

24 TEXT函数应用

24.1 使用方法

24.2 案例