数据分析最常用的excel函数公式大全.docx

《数据分析最常用的excel函数公式大全.docx》由会员分享，可在线阅读，更多相关《数据分析最常用的excel函数公式大全.docx（17页珍藏版）》请在优知文库上搜索。

1、数据分析最常用的excel函数公式大全一、数学函数1 .取整=INT（数字）2 .求余数=MoD（除数，被除数）3 .四舍五入Bl=ROUND（数字，保留小数位数）=ROUND(AlzI)ABC_DE1I78.4578.5)4 .取绝对值Bl=ABS（数字）=ABS(Al)ABCDE1I-78.4578.455 .算术平方根=SQRT（数字）6 .产生随机数=RANDQ7 .返回一个介于指定的数字之间的随机数=RANDBETWEEN（数字，数字）二、文本函数1 .取子串=MID（字符串,起始位置,取子符串位数）A2:J:X=MID(A1,4,4)A_B_CDEICDA数据分析师2 .从左取子串

2、=1.EFT（字符串,取子符串位数）A2X=1.EFT(A1,3)ABCD_1 CDA数据号析!)-2 Icda3 .从右取子串=RlGHT（字符串,取子符串位数）A2:KVfiC=RIGHT(A1,3)ABCDEICDA数据里而2分析师14 .文本长度=1.EN（字符串）A2-:X=1.EN(Al)AB_D_E1 CDA数据号析师2 85 .数字转化文本格式=TEXT（数字,选用的文本格式）B3fx=TExT(A3,OQOo.00”)C=TEXT(A1,O%)=TExT(A2Jhh:mm:ss) =TEXT(A3,0,000.00)AB10.2121%222:3522:35:00323,45

3、0,023.45,6 .文本重复=REPT（文本，重复次数）7 .替换特定位置处的文本=REP1.ACE（要替换的字符串，开始位置，替换个数，新的文本）A2fx=REP1.ACE（Al,4,6,人工智能学院）ABC1 .DA数据分析师2 ICDA人工智能学院8.替换文本A3 I K y .A1 CDA数据分析师CDA数据分析师2 CDA人工智能学院CDA数据分析师3 Icda数据分析师CDA人工智能学院=SUBsTlTUTE（AlJ数据分析师人工智能学院”,2）=SUBSTrnJTE（需要替换的文本，旧文本，新文本，第N个位置旧文本）_B=SUBSTITUTE（Alj数据分析师，“人工智能学院

4、,1,1）=SUBSTITUTE（A1J数据分析师”J人工智能学院”,2）三、统计函数1 .求最大=MAX（输入需要提取的数据范围）C2f=MAX(B2:B5)2 .求最小=MlN（输入需要提取的数据范围）3 .求和=SUM（输入需要提取的数据范围）fx=SUM(B2:B5)C2AABCDE1姓名成绩2王二602363张三544李四735钱五494.数值计数=COUNT（输入需要提取的数据范围）C2f=COUNT(B2:B5)BCDE12345名二三四五姓王张李钱=COUNTA（输入需要提取的数据范围）f=COUNT(B2:B5)ABC1姓名成绩2王二6003张三5444李四735钱五49C3

5、D=COUNT(A2:A5)=COUNT(B2:B5)6 .求平均=AVERAGE（输入需要提取的数据范围）C2fx=AVERAGE(B2:B5)ABCDE7 .条件计数=COUNTIf(条件区域，条件)C2f=CONTIF(B2B5z=60)/A B CD8 .条件求和=SUMIF（条件区域,求和条件，实际求和区域）D2f=SUMIF（C2:C5J男”,B2:B5）ABCDEFI12 3 4 5-名二三四五姓王张李钱别性男男女女0 4 3 9 绩 6 5 7 49 .条件平均=AVERAG曰F（条件区域，求平均条件，实际求平均区域）C2*f=AVERAGElF（B2:B5,”=60”）10多

6、条件计数=COUNTIFS（条件匹配查询区域1,条件1,条件匹配查询区域2,条件2,以此类推.）D2: XyG =COUNTIFS(B2:B5,=60,C2:C5,男)11 .多条件求和=SUMIFS（实际求和区域，第一个条件区域，第一个对应的求和条件，第二个条件区域，第二个对应的求和条件，第N个条件区域，第N个对应的求和条件）D2-=X=SUMIFS(B2:B5,C2:C5,男,B2:B5,=60)4ABCIDEFGHIJ姓名成绩性别H王二60男I6男女女4 3 9 5 7 4三四五张惇钱 3 4 512 .多条件平均=Averageifs（实际求和区域，第一个条件区域，第一个对应的求和条件

7、，第二个条件区域，第二个对应的求和条件，第N个条件区域，第N个对应的求和条件）D2Vf=AVERAGEIFS（B2:B5,C2:C5,男，B2:B5,=60）ABCDEFGH名二三四五姓住张李钱 1 2 3 4 5别性男男女女.(O 4 3 9 资 6 6 7 413 .求数据分布频率=FREQUENCY（要统计的数组，间隔点数组）E2f=FREQUENCY(B2:B5,D2:D4)名二三四五姓王张李钱 :T ； 1 2 3 4 5BC D E F G成绩性别男男女女143 96 6 7 4注意：其函数需要选中三格，输入完函数，按CTR1.+Sh阴+Enter14 .排名次=RANK（排名的数

8、字，引用区域，排序方式0：降序，1：升序）/ABCDE1!姓名成绩性别=RANK(B2,$B$2:$B$5,0)2王二61男33张三64男2=RANK(B3,$B$2:$B$5,0)4!李四73女1=RANK(B4,$B$2:$B$5,0)5钱五49女4=RANK(B5,$B$2:$B$5,0)四、逻辑函数1.判断=F（条件判断，结果为真返回值，结果为假返回值）2.与=AND（条件判断1,条件判断2）D2K=AND(C2=男”,B260)ABCDE1姓名成绩性别2王二61男TRUE3张三64男TRUE4李四73女FA1.SE5钱五49女FA1.SE3.或4俳=NC）T（条件判断1）D2X=NO

9、T(C2=男”)AABCD.1姓名成绩性别2王二61男FA1.SE13张三64男FA1.SE4李四73女TRUE5钱五49女TRUE五、日期函数1 .求年=YEAR（输入日期）2 .求月=MoNTH（输入日期）Bl-J:f=MONTH(Al)ABCT2021/4/1318:144.3 .求日=DAY（输入日期）Bl=DAY(Al)2021/4/1318:15134 .当前日期=TODAY()AlIXI=TODAY()AABC12021/4/130:0。15 .计算给定的日期=DATE（输入年，输入月，输入日）Bl-.XVf=DATE(AI,A2,A3)ABCDE1202if2021/4/132

10、4|3136 .当前日期和时间=NOW（）7 .指定日期前后月份的日期=EDATE(开始日期，月份)Blf=EDATE(Alz2)ABCD12021/4/1318:2412021/6/1318 .某个月份最后一天的序列号=EOMONTH（开始日期，月份数）bifx=eomonth(aizi)ABCD11I2021/4/1318:242021/5/319.计算日期差=DATEDlF(起始日期，结束日期，所需信息的返回时间单位代码)ClIfx=DATEDlF(A1,BIjer)ABCD1I2021/4/1318:262021/5/3148所需信息的返回时间单位代码： y返回时间段中的整年数 m返回

11、时间段中的整月数 返回时间段中的天数 md”参数1和2的天数之差,忽略席口月 ym”参数1和2的月数之差,忽略箍口日 yd参数1和2的天数之差，忽略年。按照月、日计算天数六、查找与引用函数1 .垂直方向查找=V1.OoKUP(查找目标，查找范围，返回值的列数，精确OR模糊查找)E2Vfie=V1.OoKUP(D2zA265,2,TRUE)姓名成绩性别王二61男张三64张三64男李四73女钱五49女2 .计算偏移量=OFFSET（参照单元格,行偏移量，列偏移量，返回几行,返回几列）注意：其函数需要选中四格，输入完函数，按CTR1.+Sh阴+Enter3 .MATCH函数=MATCH（查找的值，查找区域，查找类型）4.INDEX函数INDEX（引用的区域,引用的行数引用的列数）/ABCDEF1姓名成绩性别2王二61男64.3张三64男4李四73女5钱五49女=INDEX(A2C5,2,2)D25 .文本字符串指定的引用=INDIRECT（引用的单元格,引用的类型）6 .引用行的数据=Rc）W（选中的单元格）7 .引用列的数据=CO1.UMN（j中的单元格）8 .水平方向查找=H1.oC）KUP（查找目标，查找范围，返回值的行数，精确OR模糊查找）