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

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

1、精选学习资料 - - - - - - - - - 精品资料 欢迎下载电子表格常用函数公式及用法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、男女人数统计：=COUNTIFD1:D15, 男 统计男生人数 =COUNT

2、IFD1: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 页精选学习资料 - - - - - - - - - 精品资料 欢迎下载求 A2 到 A56 区域大于等于 60 分而小于 80 分的人数；=COUNTIFA1

3、: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,=90SUMA57:A58 求 A2 到 A56区域大于等于 90 而小于 95 分的人数；假设把结果存放于 A59 单元格；（4）=COUNTIFA2:A56,=85SUMA57:A59 求 A2 到 A56区域大于等于

4、 85 而小于 90 分的人数； 8、求 A2 到 A56 区域优秀率 ：=COUNTIFA2:A56,=90/55*100 9、求 A2 到 A56 区域及格率 ：=COUNTIFA2:A56,=60/55*100 10、排名公式：=RANKA2 ,A$2:A$56 对 55 名同学的成果进行排名；11、标准差：=STDEVA2:A56 求 A2 到 A56 区域55 人的成 绩波动情形（数值越小,说明该班同学间的成果差异较小,反之,说 明该班存在两极分化） ；12、条件求和：=SUMIFB2:B56, 男,K2:K56 假设 B 列存放 同学的性别, K 列存放同学的分数, 就此函数返回的

5、结果表示求该班名师归纳总结 - - - - - - -第 2 页,共 9 页精选学习资料 - - - - - - - - - 精品资料 欢迎下载男生的成果之和；13、多条件求和： =SUMIFC3:C322= 男,IFG3:G322=1,1,0 假设 C 列（C3:C322 区域）存放同学的性别,G 列（G3:G322区域）存放同学所在班级代码（1、2、3、4、5）,就此函数返回的结果表示求一班的男生人数；这是一个数组函数,输完后要按 CtrlShiftEnter 组合键 产生“ ” ；“”不能手工输入,只能用组合键产生；“ 1,1,0 ” 中的第一个 1 表示班级,其次个 1 表示运算次数（

6、倍数）；14、查找重复内容公式：（1）标记重复两次以上的全部数据=IFCOUNTIFA:A,A21,重复, ；“ 重复” 可以依据需要更换为其它数据； A2 表示此栏结果对应的是其次行；（2）标记超过一个以上的其它重复数据（只留一个）=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列,单击数据高级挑选完成；挑选操作的数

7、据区域, 设置数据的复制位置, 最重要的就是勾选上 “ 选 择不重复的记录” ；方法 3：用数据比对公式 =COUNTIF$A$9:A9,A9；通过挑选,挑选出名师归纳总结 - - - - - - -第 3 页,共 9 页精选学习资料 - - - - - - - - - 精品资料 欢迎下载“ 1” 的数据,然后复制粘贴到其他的单元格中即可；这样 C 列提取 到的就是 A列不重复数据；（3）多行多列提取不重复数据,如下：D2 输入公式：=INDIRECTTEXTRIGHTMINIFCOUNTIF$D$1:D1,$A$2:$B$ 9, 48,ROW$2:$ 9*100+COLUMNA:B*1000

8、1,4,r0c00,&,然后按 ctrl+shift+enter 三键终止；这样就把 AB 列不重复数据提取 到 D 列；15、用诞生年月来运算年龄公式：=DATEDIFA3,TODAY,y 或=DATEDIFA3,2022-8-24,y；=TRUNCDAYS360D3,NOW /360,0 16、用身份证来运算年龄公式：=DATEDIFTEXTMIDA2,7,8,0000-00-00,TODAY,y 17、从输入的 18 位身份证号的诞生年月运算公式：名师归纳总结 =CONCATENATEMIDE2,7,4,/,MIDE2,11,2,/,MIDE2,13,2；第 4 页,共 9 页- - -

9、 - - - -精选学习资料 - - - - - - - - - 精品资料 欢迎下载18、从输入的身份证号码内让系统自动提取性别公式：=IFLENC2=15,IFMODMIDC2,15,1,2=1,男,女,IFMODMIDC2,17,1,2=1,男,女 19、在 Word 中三个小窍门：连续输入三个 “ ”可得一条波浪线；连续输入三个 “-”可得一条直线；连续输入三个 “ =”可得一条双直线；20、excel 中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色比如：A11 时,C1 显示红色 0A11 时,C1 显示绿色A1“条件格式 ”,条件 1 设为：公式 =A1=1 2、点“格式

10、”-“字体”-“ 颜色”,点击红色后点 “确定 ”；条件 2 设为：公式 =ANDA10,A1“字体”-“ 颜色”,点击绿色后点 “确定 ”；条件 3 设为：公式 =A1“字体”-“颜色”,点击黄色后点 “确定”；名师归纳总结 - - - - - - -第 5 页,共 9 页精选学习资料 - - - - - - - - - 精品资料 欢迎下载4、三个条件设定好后,点“确定 ”即出；21、EXCEL 中如何掌握每列数据的长度并防止重复录入（1）用数据有效性定义数据长度；用鼠标选定你要输入的数据范畴,点数据 -有效性 -设置,有效性条件 设成答应文本长度 等于5（详细条件可依据你的需要 转变）；仍

11、可以定义一些提示信息、出错警告信息和是否打开中文输入法等,定义好后点 确定 ；（2）用条件格式防止重复；选 定A列 , 点 格 式 - 条 件 格 式 , 将 条 件 设 成 “ 公 式=COUNTIF$A:$A,$A11” ,点格式- 字体-颜色,选定红色后 点两次 确定；这样设定好后你输入数据假如长度不对会有提示,将会变成红色；假如数据重复字体22、在 EXCEL 中如何把 B 列与 A 列不同之处标识出来？（一）、假如是要求 A、B 两列的同一行数据相比较：假定第一行为表头,单击 设为: A2 单元格,点 “格式”-“条件格式 ”,将条件“单元格数值 ”“不等于 ” =B2点“格式”-“

12、字体”-“颜色”,选中红色,点两次 “确定”；用格式刷将 A2 单元格的条件格式向下复制；B 列可参照此方法设置；名师归纳总结 - - - - - - -第 6 页,共 9 页精选学习资料 - - - - - - - - - 精品资料 欢迎下载（二）、假如是 A 列与 B 列整体比较（即相同数据不在同一行） ：假定第一行为表头,单击 设为: A2 单元格,点 “格式”-“条件格式 ”,将条件“公式” =COUNTIF$B:$B,$A2=0 点“格式”-“字体”-“颜色”,选中红色,点两次 “确定”；用格式刷将 A2 单元格的条件格式向下复制；B 列可参照此方法设置；按以上方法设置后, AB 列

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

14、际情形（数据范畴）作相应的修改；假如要从小到大排序 ,公式改为 :=SMALL$A1:$E1,COLUMNA1 名师归纳总结 - - - - - - -第 7 页,共 9 页精选学习资料 - - - - - - - - - 精品资料 欢迎下载24、巧用函数组合进行多条件的计数统计 例：第一行为表头, A 列是“姓名”,B 列是“班级”,C 列是“语文成果 ”,D 列是 “录用结果 ”,现在要统计 “班级 ”为“二”,“语文成果 ”大于等于 104,“录用结果 ”为“重本”的人数；统计结果存放在本工作表的其他列；公式如下：=SUMIFB2:B9999=二 *C2:C9999=104*D2:D99

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

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