最常见Excel函数的使用技巧.docx
- 文档编号:1904638
- 上传时间:2022-10-25
- 格式:DOCX
- 页数:12
- 大小:33.52KB
最常见Excel函数的使用技巧.docx
《最常见Excel函数的使用技巧.docx》由会员分享,可在线阅读,更多相关《最常见Excel函数的使用技巧.docx(12页珍藏版)》请在冰豆网上搜索。
最常见Excel函数的使用技巧
常见Excel函数的使用
世界上的数据分析师分为两类,使用Excel的分析师,和其他分析师。
每一个数据新人的入门工具都离不开Excel。
因为Excel涵盖的功能足够多。
今天,我们主要讲常见的Excel函数的使用。
一、什么是函数
如:
=subtotal(1,a1:
a10)
函数由“=”,“英文单词”,“()”以及“各种参数”所构成。
二、函数支持的数据类型
1、字符型
包括汉字、英文字母、空格等
2、数值型
包括0~9中的数字以及含有正号、负号、货币符号、百分号等任一种符号的数据
3、日期型数据和时间型数据
年、月、日之间要用“/”号或“-”号隔开,如“2017-11-4”“2017/11/04”
三、不同数据类型在函数中的使用
1、数值型可直接进行运算
如:
=sum(1,3,5,7,9)
2、非数值型,常常加“”。
如:
=trim(“你好吗?
”)
注意:
如果0-9的数字加“”,表示作为字符型来进行运算。
如:
=sumif(a2:
a5,”>15000”,b2:
b5)
如:
if(a2>a3,”a2>a3”,”a2 四、函数使用注意事项 1、不区分大小写 2、区分中英文状态,尤其是“=”、“”(即: 等号、引号); 3、不同参数间的运算,以“,”或“*”隔开。 五、常见的函数类别 (一)清洗处理类 主要是文本、格式以及脏数据的清洗和转换。 很多数据并不是直接拿来就能用的,需要经过数据分析人员的清理。 数据越多,这个步骤花费的时间越长。 1、Trim 作用: 清除掉字符串两边的空格。 如: =trim(“你好吗? ”),结果: 你好吗? Excel常见函数副本/数据清洗类/trim函数.xls 2、Concatenate 作用: 合并单元格中的内容 公式: =Concatenate(单元格1,单元格2……) 如: =concatenate(“你””好””吗? ”),结果: 你好吗? Excel常见函数副本/数据清洗类/CONCATENATE函数.xls 3、Replace 作用: 指定替换 公式: =Replace(指定字符串,哪个位置开始替换,替换几个字符,替换成什么) 如: a1=abcdefg =raplace(a1,4,2,”&”),结果abc&fg Excel常见函数副本/数据清洗类/replace函数.xls 4、Substitute 作用: 全局替换 公式: =SUBSTITUTE(指定字符串,被替换的字符,替换字符,要替换的是第几次出现的字符) 如: a1=2011年第一季度 =SUBSTITUTE(a1,"1","2",2),结果: 2012年第一季度 Excel常见函数副本/数据清洗类/substitute函数.xls 5、Left 作用: 从左到右截取 公式: =Left(string,n),n代表截取的字符数。 如: a1=蓝天白云 =left(a1,2),结果: 蓝天。 Excel常见函数副本/数据清洗类/left函数.xls 6、right 作用: 从右向左截取 公式: =right(string,n),n代表截取的字符数。 如: a1=蓝天白云,结果: 白云。 Excel常见函数副本/数据清洗类/right函数.xls 7、Mid 作用: 从中截取 公式: =Mid(指定字符串,开始位置,截取长度) 如: a1=碧海蓝天 =mid(a1,3,2),结果: 蓝天 Excel常见函数副本/数据清洗类/mid函数.xls 8、Len/Lenb 作用: 返回字符串的长度。 公式: =Len(string|varname) 如: =len(““),结果: 1;=len(”你好”),结果: 2 备注: 空格“”也计数。 如: lenb(“你好”),结果: 4 备注: 在lenb中,中文计算为2个字符,len计算为1个字符。 Excel常见函数副本/数据清洗类/len函数.xls 9、Find 作用: 查找 公式: =Find(要查找的字符,指定字符串,第几个字符) 如: a1=baidujingyan =find(“I”,a1,1),结果: 3 Excel常见函数副本/数据清洗类/find函数.xls 10、search 作用: 查找 公式: =search(要查找的文本字符,要在哪一个字符串查找,从第几个字符开始查找) 如: a1=你的大学是什么学校? =search(“学”,a1),结果: 4 =search(“学”,a1,5),结果: 8。 备注: 实际上还是从第1位开始查找,只不过会跳过第5位前的数字。 Excel常见函数副本/数据清洗类/search函数.xls 11、Text 作用: 将数值转化为指定的文本格式 公式: =TEXT(字符串,参数) 如: 将“19850909”转换成“1985-09-09” =text(19850909,”0000-00-00”) 备注: 这里面的值,并非是真正的时间,不能和时间直接进行计算。 Excel常见函数副本/数据清洗类/text函数.xls (二)关联匹配类 在进行多表关联或者行列比对时用到的函数,越复杂的表用得越多。 多说一句,良好的表习惯可以减少这类函数的使用。 1、Lookup 作用: 在表中查找值 公式: =Lookup(查找的值,值所在的位置,返回相应位置的值) 如: a1=2,a2=5.5,a3=6.4,b1=1212,b2=1.333,b3=45 =lookup(5.5,a1: a3,b1: b3),结果: 1.333 说明: 在ai: a3中找出5.5,并且对应到b1: b3中对应的位置。 Excel常见函数副本/关联匹配类/LOOKUP.xls 2、Vlookup 作用: 在表中查找值 公式: =Vlookup(查找的值,哪里找,找哪个位置的值,是否精准匹配) 如: =vlookup(a11,$a$1: $d$10,3,0),结果: 在a11行中第3列的数值 说明: (1)查找的值,必须在该查找区域的第1列; (2)“找哪个位置的值”,指的是查找区域中的列数; (3)“精确匹配”为0/false;“模糊匹配“为1/true; Excel常见函数副本/关联匹配类/VLOOKUP.xls 3、Index 作用: 在表中查找值 公式: =Index(查找的区域,区域内第几行,区域内第几列) 如: a1=苹果,a2=香蕉,b1=柠檬,b2=梨 =index(a1: b2,1,2),结果: 柠檬 =index((a1: a2,b1: b2),2,1,2),结果: 梨,备注: 分别为2行,1列,第2区域 Excel常见函数副本/关联匹配类/INDEX.xls 4、Match 作用: 在表中查找值 公式: =Match(查找指定的值,查找所在区域,查找方式的参数) 和Lookup类似,但是可以按照指定方式查找,比如大于、小于或等于。 返回值所在的位置。 参数说明: (1)1/省略: 小于或等于指定内容的最大值,升序排列 (2)0: 等于指定内容第一个数值 (3)-1: 大于或等于指定内容的最小值,降序排列; 备注: 必须是1行多列或1列多行。 如: a1=25,b1=35,c1=45,d1=55,e1=65 =match(45,a1: e1,0),结果: 3 Excel常见函数副本/关联匹配类/MATCH.xls 5、Row 作用: 返回单元格所在的行 公式: =row(单元格/单元格区域) 如: =row(c9),结果: 9 Excel常见函数副本/关联匹配类/ROW.xls 6、Column 作用: 返回单元格所在的列 公式: =column(单元格/单元格区域) 如: =column(b7),结果: 2 Excel常见函数副本/关联匹配类/COLUMN.xls 7、Offset 作用: 偏移定位 公式: =Offset(指定点,偏移多少行,偏移多少列,返回多少行,返回多少列) 如: =offset(c3,2,3,1,1) 说明: (1)c3,为指定点; (2)2,指+2行; (3)3,指+3列 (4)1,1指以1行1列做显示。 Excel常见函数副本/关联匹配类/OFFSET.xls (三)逻辑运算类 数据分析中不得不用到逻辑运算,逻辑运算返回的均是布尔类型,True和False。 很多复杂的数据分析会牵扯到较多的逻辑运算。 1、IF 作用: 判断,“如果……否则……” 公式: =if(判断值,判断值true选择,判断值false选择) 如: a2=50,a3=80 =if(a2>a3,true,false),结果: false =if(a2>a3,”a2>a3”,”a2 a2 Excel常见函数副本/逻辑运算类/IF.xls 2、And 作用: 多条件判断,全部true则true,否则false 公式: =and(判断值,参数) 如: =and(1>2,1),结果: false 参数说明: 1代表true,0代表false 备注: 结果只有true和false。 Excel常见函数副本/逻辑运算类/AND.xls 3、Or 作用: 多条件判断,有一个true,则为true。 公式: =or(参数1,参数2,……) 如: =or(1+1=3,1,5),结果: true 备注: 结果只有true和false。 Excel常见函数副本/逻辑运算类/OR.xls (四)计算统计类 常用的基础计算、分析、统计函数,以描述性统计为准。 1、Sum/Sumif/Sumifs 作用: 求和 公式: =sum(参数1,参数2……) 公式: =sumif(条件区域,求和条件,实际求和区域) 如: =sum(1,2,3),结果: 6 如: a1=100000,a2=200000,a3=300000,a4=400000 b1=7000,b2=14000,b3=21000,b4=28000 =sumif(a1: a4,”>150000”,b1: b4),结果: 63000 参数说明: (1)a2: a5,”>150000”指找出a2: a5区域内>150000的值 (2)b2: b5指再对应到b2: b5区域中进行求和。 Excel常见函数副本/计算统计类/SUM.xls Excel常见函数副本/计算统计类/SUMIF.xls 2、Sumproduct 作用: 统计求和: 返回相应的数组或区域乘积的和 公式: =sumproduct(数组1,数组2……) 如: b2=3,b3=8,b4=1,c2=4,c3=6,c4=9 d2=2,d3=6,d4=5,e2=7,e3=7,e4=3 =sumproduct(b2: c4,d2: e4)/=sumproduct((b2: c4)*(d2: e4)) 即: 3*2+4*7+8*6+6*7+1*5+9*3=156 备注: 数据区域大小要一致。 Excel常见函数副本/计算统计类/SUMPRODUCT.xls 3、Count/Countif/Countifs 作用: 统计满足条件的字符串个数 公式: =count(字符串1,字符串2……) 公式: =countif(字符串,条件) 如: a1=你好,b1=100,c1=2006/1/19 =count(a1: c1),结果: 2 备注: 统计结果包括: 数字、空值、逻辑值(true/false)、日期 如: a1=1,a2=2,a3=1,a4=4,a5=5 =countif(a1: a5,”>2),结果: 2(即: >2的个数) Excel常见函数副本/计算统计类/count函数.xls Excel常见函数副本/计算统计类/countif函数.xls 4、Max 返回数组或引用区域的最大值 5、Min
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 常见 Excel 函数 使用 技巧