2022年Excel常用电子表格公式大全 .pdf

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

1、Excel 常用电子表格公式大全1、查找反复内容公式 :=IF(COUNTIF(A:A,A2)1,重复,). 2、用出生年月来计算年纪公式:=TRUNC(DAYS360(H6,2009/8/30,FALSE)/360,0). 3、从输入的 18 位身份证号的诞生年月盘算公式:=CONCATENATE(MID(E2,7,4),/,MID(E2,11,2),/,MID(E2,13,2). 4、从输入的身份证号码内让体系主动提取性别, 能够输入以下公式 : =IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,男, 女),IF(MOD(MID(C2,17,1),2)=1,男

2、, 女) 公式内的 C2代表的是输入身份证号码的单元格 . 1、求和: =SUM(K2:K56) 对 K2到 K56这一区域进行求和 ; 2、平均数 : =AVERAGE(K2:K56) 对 K2 K56 这一区域求均匀数 ; 3、排名: =RANK(K2,K$2:K$56) 对 55 名学生的成绩进行排名 ; 4、等级 : =IF(K2=85,优,IF(K2=74,良,IF(K2=60,及格, 不迭格 ) 5、学期总评 : =K2*0.3+M2*0.3+N2*0.4 假设 K列、M列和 N列分辨存放着学生的 平时总评 、 期中 、 期末三项成绩 ; 6、最高分 : =MAX(K2:K56)

3、求 K2到 K56区域(55 名学生 )的最高分 ; 7、最低分 : =MIN(K2:K56) 求 K2到 K56区域(55 名学生 )的最低分 ; 8、分数段人数统计 : (1) =COUNTIF(K2:K56,100) 求 K2到 K56区域 100 分的人数 ; 假设把结果存放于 K57单元格 ; (2) =COUNTIF(K2:K56,=95)-K57 求 K2到 K56区域 9599.5 分的人数 ;假设把结果存放于K58单元格 ; (3)=COUNTIF(K2:K56，F1 知识,=90)-SUM(K57:K58) 求 K2到 K56区域9094.5 分的人数 ; 假设把成果寄存于

4、K59单元格 ; (4)=COUNTIF(K2:K56,=85)- SUM(K57:K59) 求 K2到 K56区域 8589.5 分的人数 ; 假设把结果存放于K60单元格 ; (5)=COUNTIF(K2:K56,=70)- SUM(K57:K60) 求 K2到 K56区域 7084.5 分的人数 ; 假设把结果存放于K61单元格 ; (6)=COUNTIF(K2:K56,=60)- SUM(K57:K61) 求 K2到 K56区域 6069.5 分的人数 ; 假设把结果存放于K62单元格 ; (7) =COUNTIF(K2:K56,60) 求 K2到 K56区域 60 分以下的人数 ;

5、假设把结果存放于 K63单元格 ; 阐明:COUNTIF 函数也可计算某一区域男、女生人数. 如:=COUNTIF(C2:C351,男) 求 C2到 C351区域( 共 350 人) 男性人数 ; 9、优良率 : =SUM(K57:K60)/55*100 10、合格率 : =SUM(K57:K62)/55*100 11、尺度差 : =STDEV(K2:K56) 求 K2到 K56区域(55 人) 的成绩稳定情况 (数值越小 , 解释该班学生间的成绩差别较小, 反之, 说明该班存在两极分化 ); 12、前提乞降 : =SUMIF(B2:B56, 男,K2:K56) 假设 B列存放学生的性别 ,K

6、列存放学生的分数 , 则此函数返回的结果表现求该班男生的成就之跟; 13、多条件求和 : =SUM(IF(C3:C322= 男,IF(G3:G322=1,1,0) 假设 C名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 3 页 - - - - - - - - - 列(C3:C322 区域)存放学生的性别 ,G 列(G3:G322区域)存放学生所在班级代码(1 、2、3、4、5), 则此函数返回的结果表示求一班的男生人数; 这是一个数组函数, 输完后要按 Ctrl+Shif

7、t+Enter组合键 ( 产生 不能手工输入 , 只能用组合键产生. 14、根据出诞辰期自动计算周岁:=TRUNC(DAYS360(D3,NOW( )/360,0) 假设 D列存放学生的出身日期 ,E 列输入该函数后则发生该生的周岁. 15、在 Word中三个小秘诀 : 连续输入三个 可得一条波浪线 . 连续输入三个 - 可得一条直线 . 持续输入三个 = 可得一条双直线 . 一、excel 中当某一单元格合乎特定条件, 如何在另一单元格显示特定的颜色比方:A1 1时,C1 显示红色 0A11时,C1 显示绿色 A1 条件格式 , 条件 1设为: 公式 =A1=12、 点格式-字体- 颜色,

8、点击红色后点 确定. 条件 2 设为: 公式 =AND(A10,A1 字体- 颜色, 点击绿色后点 确定. 条件 3 设为: 公式 =A1 字体- 颜色, 点击黄色后点 确定.4 、 三个条件设定好后 , 点 确定即出. 二、EXCEL 中如何把持每列数据的长度并避免重复录入1、用数占有效性定义数据长度. 用鼠标选定你要输入的数据范围, 点 数据-有效性 - 设置, 有效性条件 设成 容许 文本长度 即是5( 详细条件可根据你的需要转变 ). 还可以定义一些提醒信息、 犯错忠告信息和是否翻开中文输入法等 , 定义好后点 肯定2、用条件格式防止重复 . 选定 A列, 点 格局- 条件格式, 将条

9、件设成 公式=COUNTIF($A:$A,$A1)1,点 格式- 字体- 色彩,选定红色后点两次 断定. 这样设定好后你输入数据如果长度错误会有提示, 如果数据重复字体将会变成红色. 三、在 EXCEL 中如何把 B列与 A列不同之处标识出来 ?( 一) 、如果是请求 A、B两列的统一行数据相比拟: 假定第一行为表头 , 单击 A2单元格，F1 知识, 点格式- 条件格式 ，F1知识, 将条件设为 : 单元格数值 不等于 =B2 点 格式-字体- 颜色, 选中红色 , 点两次 确定. 用格式刷将 A2单元格的条件格式向下复制.B 列可参照此方法设置 .( 二) 、如果是 A列与 B列整体比较

10、(即相同数据不在同一行 ): 假定第一行为表头 , 单击 A2单元格 , 点格式- 条件格式 , 将条件设为: 公式=COUNTIF($B:$B,$A2)=0点 格式- 字体- 颜色, 选中红色 , 点两次 确定. 用格式刷将 A2单元格的条件格式向下复制.B 列可参照此方法设置 .按以上方法设置后 ,AB列均有的数据不着色 ,A 列有 B列无或者 B列有 A列无的数据标志为红色字体 . 四、EXCEL 中怎么批量地处置按行排序假定有大批的数据 (数值), 须要将每一行按从大到小排序, 如何操作 ?因为按行排序与按列排序都是只能有一个主关键字, 主要害字雷同时才干按次症结字排序.所以, 这一问

11、题不能用排序来解决. 解决方式如下 :1 、假定你的数据在A至 E列,请在 F1 单元格输入公式 :=LARGE($A1:$E1,COLUMN(A1) 用填充柄将公式向右向下复制到相应规模 . 你原有数据将按行从大到小排序呈现在F至 J列. 如有需要可用抉择性粘贴 / 数值 复制到其余处所 . 注: 第 1 步的公式可依据你的实际情形( 数据范畴 ) 作相应的修正 . 假如要从小到大排序 , 公式改为:=SMALL($A1:$E1,COLUMN(A1) 五、巧用函数组合进行多条件的计数统计名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - -

12、- - 名师精心整理 - - - - - - - 第 2 页，共 3 页 - - - - - - - - - 例: 第一行动表头 ,A 列是 姓名,B 列是 班级,C 列是 语文成绩 ,D 列是 录取结果, 当初要统计 班级为二, 语文成绩 大于等于 104, 录取结果 为重本的人数 . 统计结果存放在本工作表的其他列. 公式如下 :=SUM(IF(B2:B9999= 二)*(C2:C9999=104)*(D2:D9999=重本),1,0)输入完公式后按Ctrl+Shift+Enter键, 让它自动加上数组公式符号. 六、如何断定单元格里是否包括指定文本?假设对 A1单元格进行判定有无 指定文

13、本, 以下任一公式均可 :=IF(COUNTIF(A1,*& 指定文本 &*)=1,有, 无)=IF(ISERROR(FIND( 指定文本 ,A1,1),无, 有) 求某一区域内不重复的数据个数例如求A1:A100 范围内不重复数据的个数 , 某个数重复屡次出现只算一个. 有两种计算方法 : 一是利用数组公式:=SUM(1/COUNTIF(A1:A100,A1:A100)输入完公式后按 Ctrl+Shift+Enter键,让它自动加上数组公式符号.二是应用乘积求和函数:=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100) 七、一个工作薄中有很多工作表如何疾速收拾出一个目

14、录工作表1、用宏 3.0 掏出各工作表的名称 , 方法:Ctrl+F3涌现自定义名称对话框 , 取名为X,在 引用地位 框中输入:=MID(GET.WORKBOOK(1),FIND(,GET.WORKBOOK(1)+1,100)确定 2、用HYPERLINK 函数批量插入衔接 , 办法: 在目录工作表 (个别为第一个 sheet) 的 A2单元格输入公式 :=HYPERLINK(#&INDEX(X,ROW()&!A1,INDEX(X,ROW()将公式向下填充 , 直到出错为止 , 目录就天生了名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 3 页 - - - - - - - - -