2022年电子表格常用函数公式及用法.docx

《2022年电子表格常用函数公式及用法.docx》由会员分享，可在线阅读，更多相关《2022年电子表格常用函数公式及用法.docx（18页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、精品_精品资料_资料word 精心总结归纳 - - - - - - - - - - - -名师举荐细心整理学习必备电子表格常用函数公式及用法1、求和公式：=SUMA2:A50 对 A2 到 A50 这一区域进行求和.2、平均数公式：=AVERAGEA2:A56 对 A2 到 A56 这一区域求平均数.3、最高分：=MAXA2:A56 求 A2 到 A56 区域（ 55 名同学）的最高分.4、最低分：=MINA2:A56 求 A2 到 A56 区域（ 55 名同学）的最低分.5、等级：=IFA2=90, 优,IFA2=80, 良,IFA2=60, 及格,不及格 6、男女人数统计：=COUNTIF

2、D1:D15,男统计男生人数=COUNTIFD1:D15,女7、分数段人数统计：统计女生人数方法一：求 A2 到 A56 区域 100 分人数： =COUNTIFA2:A56,100求 A2 到 A56 区域 60 分以下的人数. =COUNTIFA2:A56,=90求 A2 到 A56 区域大于等于 80 分而小于 90 分的人数.=COUNTIFA1:A29,=80-COUNTIFA1:A29, =90可编辑资料 - - - 欢迎下载精品_精品资料_学习资料 名师精选 - - - - - - - - - -第 1 页,共 9 页 - - - - - - - - - -可编辑资料 - - -

3、 欢迎下载精品_精品资料_资料word 精心总结归纳 - - - - - - - - - - - -名师举荐细心整理学习必备求 A2 到 A56 区域大于等于 60 分而小于 80 分的人数.=COUNTIFA1:A29,=80-COUNTIFA1:A29, =90方法二：（ 1） =COUNTIFA2:A56,100 求 A2 到 A56 区域 100 分的人数.假设把结果存放于A57 单元格.（ 2） =COUNTIFA2:A56,=95A57 求 A2 到 A56 区域大于等于 95 而小于 100 分的人数.假设把结果存放于A58 单元格.（ 3）=COUNTIFA2:A56,=90

4、SUMA57:A58 求 A2 到 A56区域大于等于 90 而小于 95 分的人数.假设把结果存放于A59单元格.（ 4）=COUNTIFA2:A56,=85 SUMA57:A59 求 A2 到 A56区域大于等于 85 而小于 90 分的人数.8、求 A2 到 A56 区域优秀率 ：=COUNTIFA2:A56,=90/55*1009、求 A2 到 A56 区域及格率 ：=COUNTIFA2:A56,=60/55*10010、排名公式：=RANKA2 ,A$2:A$56 对 55 名同学的成果进行排名.11、标准差：=STDEVA2:A56 求 A2 到 A56 区域55 人的成果波动情形

5、（数值越小,说明该班同学间的成果差异较小,反之,说 明该班存在两极分化） .12、条件求和：=SUMIFB2:B56,男,K2:K56 假设 B 列存放同学的性别, K 列存放同学的分数, 就此函数返回的结果表示求该班可编辑资料 - - - 欢迎下载精品_精品资料_学习资料 名师精选 - - - - - - - - - -第 2 页,共 9 页 - - - - - - - - - -可编辑资料 - - - 欢迎下载精品_精品资料_资料word 精心总结归纳 - - - - - - - - - - - -名师举荐细心整理学习必备男生的成果之和.13、多条件求和： =SUMIFC3:C322=男,

6、IFG3:G322=1,1,0 假设 C 列（ C3:C322 区域）存放同学的性别, G 列（ G3:G322 区域）存放同学所在班级代码（ 1、2 、3、4、5）,就此函数返回的结果表示求一班的男生人数.这是一个数组函数,输完后要按 Ctrl Shift Enter组合键 产生“”.“ ”不能手工输入,只能用组合键产生.“ 1,1,0 ”中的第一个 1 表示班级,其次个1 表示运算次数（倍数）.14、查找重复内容公式：（ 1）标记重复两次以上的全部数据=IFCOUNTIFA:A,A21,重复, .“重复” 可以依据需要更换为其它数据. A2 表示此栏结果对应的是其次行.（ 2）标记超过一个

7、以上的其它重复数据（只留一个）=IFCOUNTIFA2:A56,A2=1,重复（ 3）提取不重复数据方法 1：将 A 列的姓名查找重复, 并提取到 C列.C2单元格输入公式：=OFFSET$A$1,MATCH,COUNTIF$C$1:C1,$A$2:$A$17,&,然后按 ctrl+shift+enter三键终止.方法 2：将 A 列不重复数据标识到 C列,单击数据高级挑选完成.挑选操作的数据区域, 设置数据的复制位置, 最重要的就是勾选上 “挑选不重复的记录”.方法 3： 用数据比对公式 =COUNTIF$A$9:A9,A9.通过挑选,挑选出可编辑资料 - - - 欢迎下载精品_精品资料_学

8、习资料 名师精选 - - - - - - - - - -第 3 页,共 9 页 - - - - - - - - - -可编辑资料 - - - 欢迎下载精品_精品资料_资料word 精心总结归纳 - - - - - - - - - - - -名师举荐细心整理学习必备“ 1”的数据,然后复制粘贴到其他的单元格中即可.这样 C 列提取到的就是 A 列不重复数据.（ 3）多行多列提取不重复数据,如下：D2 输入公式：=INDIRECTTEXTRIGHTMINIFCOUNTIF$D$1:D1,$A$2:$B$9 , 48,ROW$2:$9 *100+COLUMNA:B*10001,4,r0c00,&,然

9、后按 ctrl+shift+enter三键终止.这样就把AB 列不重复数据提取到 D 列.15、用诞生年月来运算年龄公式：=DATEDIFA3,TODAY,y或=DATEDIFA3,2022-8-24,y.=TRUNCDAYS360D3,NOW /360,016、用身份证来运算年龄公式：=DATEDIFTEXTMIDA2,7,8,0000-00-00,TODAY,y17、从输入的 18 位身份证号的诞生年月运算公式：=CONCATENATEMIDE2,7,4,/,MIDE2,11,2,/,MIDE2,13,2.可编辑资料 - - - 欢迎下载精品_精品资料_学习资料 名师精选 - - - -

10、- - - - - -第 4 页,共 9 页 - - - - - - - - - -可编辑资料 - - - 欢迎下载精品_精品资料_资料word 精心总结归纳 - - - - - - - - - - - -名师举荐细心整理学习必备18、从输入的身份证号码内让系统自动提取性别公式：=IFLENC2=15,IFMODMIDC2,15,1,2=1,男,女,IFMODMIDC2,17,1,2=1,男,女19、在 Word 中三个小窍门：连续输入三个 “可”得一条波浪线.连续输入三个 “-”可得一条直线. 连续输入三个 “=可”得一条双直线.20、excel 中当某一单元格符合特定条件,如何在另一单元格

11、显示特定的颜色比如：A11 时, C1 显示红色0A11时, C1 显示绿色A1条“件格式 ”,条件 1 设为：公式 =A1=12、点“格式”-“字体”-“颜色”,点击红色后点 “确定 ”.条件 2 设为：公式 =ANDA10,A1“字体”-“颜色”,点击绿色后点 “确定 ”.条件 3 设为：公式 =A1“字体”-“颜色”,点击黄色后点 “确定”.可编辑资料 - - - 欢迎下载精品_精品资料_学习资料 名师精选 - - - - - - - - - -第 5 页,共 9 页 - - - - - - - - - -可编辑资料 - - - 欢迎下载精品_精品资料_资料word 精心总结归纳 - -

12、 - - - - - - - - - -名师举荐细心整理学习必备4、三个条件设定好后,点“确定 ”即出.21、EXCEL中如何掌握每列数据的长度并防止重复录入（ 1）用数据有效性定义数据长度.用鼠标选定你要输入的数据范畴,点数据 - 有效性 - 设置, 有效性条件 设成答应 文本长度 等于5（详细条件可依据你的需要转变）.仍可以定义一些提示信息、出错警告信息和是否打开中文输入法等,定义好后点 确定 .（ 2）用条件格式防止重复.选 定A列 , 点 格 式 - 条 件 格 式 , 将 条 件 设 成 “公 式=COUNTIF$A:$A,$A11 ” ,点格式- 字体- 颜色,选定红色后点两次 确

13、定.这样设定好后你输入数据假如长度不对会有提示,假如数据重复字体将会变成红色. 22、在 EXCEL中如何把 B 列与 A 列不同之处标识出来？（一）、假如是要求 A、B 两列的同一行数据相比较：假定第一行为表头,单击A2 单元格,点 “格式”-“条件格式 ”,将条件设为:“单元格数值 ”不“等于 ” =B2点“格式”-“字体”-“颜色”,选中红色,点两次 “确定”.用格式刷将 A2 单元格的条件格式向下复制.B 列可参照此方法设置.可编辑资料 - - - 欢迎下载精品_精品资料_学习资料 名师精选 - - - - - - - - - -第 6 页,共 9 页 - - - - - - - -

14、- -可编辑资料 - - - 欢迎下载精品_精品资料_资料word 精心总结归纳 - - - - - - - - - - - -名师举荐细心整理学习必备（二）、假如是 A 列与 B 列整体比较（即相同数据不在同一行） ： 假定第一行为表头,单击A2 单元格,点 “格式”-“条件格式 ”,将条件设为:“公式” =COUNTIF$B:$B,$A2=0点“格式”-“字体”-“颜色”,选中红色,点两次 “确定”.用格式刷将 A2 单元格的条件格式向下复制.B 列可参照此方法设置.按以上方法设置后, AB 列均有的数据不着色,A 列有 B 列无或者 B列有 A 列无的数据标记为红色字体.23、EXCEL

15、中怎样批量的处理按行排序假定有大量的数据 数值 ,需要将每一行按从大到小排序,如何操作？由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序.所以,这一问题不能用排序来解决.解决方法 如下：1、假定你的数据在A 至 E 列,请在 F1 单元格输入公式：=LARGE$A1:$E1,COLUMNA1用填充柄将公式向右向下复制到相应范畴.你原有数据将按行从大到小排序显现在F 至 J 列.如有需要可用 “挑选性粘贴 /数值”复制到其他的方.注：第 1 步的公式可依据你的实际情形（数据范畴）作相应的修改.假如要从小到大排序 ,公式改为 :=SMALL$A1:$E1,COLUMN

16、A1可编辑资料 - - - 欢迎下载精品_精品资料_学习资料 名师精选 - - - - - - - - - -第 7 页,共 9 页 - - - - - - - - - -可编辑资料 - - - 欢迎下载精品_精品资料_资料word 精心总结归纳 - - - - - - - - - - - -名师举荐细心整理学习必备24、巧用函数组合进行多条件的计数统计例：第一行为表头, A 列是“姓名”,B 列是“班级”,C 列是“语文成果 ”, D列是 “录用结果 ”,现在要统计 “班级 ”为“二”,“语文成果 ”大于等于 104 ,“录用结果 ”为“重本”的人数.统计结果存放在本工作表的其他列. 公式如

17、下：=SUMIFB2:B9999=二 *C2:C9999=104*D2:D9999=重 本,1,0输入完公式后按 Ctrl+Shift+Enter键,让它自动加上数组公式符号 . 25、如何判定单元格里是否包含指定文本？假定对 A1 单元格进行判定有无 指定文本 ,以下任一公式均可 :=IFCOUNTIFA1,*&指定文本 &*=1, 有, 无=IFISERRORFIND指定文本 ,A1,1, 无,有求某一区域内不重复的数据个数例如求 A1:A100范畴内不重复数据的个数,某个数重复多次显现只算一个.有两种运算方法：一是利用数组公式：=SUM1/COUNTIFA1:A100,A1:A100输入

18、完公式后按 Ctrl+Shift+Enter键,让它自动加上数组公式符号 .二是利用乘积求和函数：=SUMPRODUCT1/COUNTIFA1:A100,A1:A10026、一个工作薄中有很多工作表如何快速整理出一个目录工作表1、用宏 3.0 取出各工作表的名称,方法：可编辑资料 - - - 欢迎下载精品_精品资料_学习资料 名师精选 - - - - - - - - - -第 8 页,共 9 页 - - - - - - - - - -可编辑资料 - - - 欢迎下载精品_精品资料_资料word 精心总结归纳 - - - - - - - - - - - -名师举荐细心整理学习必备Ctrl+F3显现自定义名称对话框,取名为X ,在“引用位置 ”框中输入：=MIDGET.WORKBOOK1,FIND,GET.WORKBOOK1+1,100确定2、用 HYPERLINK函数批量插入连接,方法：在目录工作表（一般为第一个sheet ）的 A2 单元格输入公式：=HYPERLINK#&INDEXX,ROW&.A1,INDEXX,ROW将公式向下填充,直到出错为止,目录就生成了.可编辑资料 - - - 欢迎下载精品_精品资料_学习资料 名师精选 - - - - - - - - - -第 9 页,共 9 页 - - - - - - - - - -可编辑资料 - - - 欢迎下载