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/zh-cn/office/find-findb-%E5%87%BD%E6%95%B0-c7912941-af2a-4bdf-a553-d0d89b0a0628
18.2 SEARCH函数使用方法
https://support.microsoft/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/zh-cn/office/substitute-%E5%87%BD%E6%95%B0-6434944e-a904-4336-a9b0-1e58df3bc332
19.2 REPLACE使用方法
https://support.microsoft/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 案例
更多推荐
第六章-数据统计-EXCEL常用函数使用技术
发布评论