Excel公式大全

第一部分:常用函数和公式

查找重复内容公式: =IF(COUNTIF(A:A,A2)>1,”重复”,””)。

用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,”2009/8/30″,FALSE))/360,0)。

从输入的18位身份证号的出生年月计算公式:=CONCATENATE(MID(E2,7,4),”/”,MID(E2,11,2),”/”,MID(E2,13,2))。

从输入的身份证号码内让系统自动提取性别,可以输入以下公式:

=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,”男”,”女”),IF(MOD(MID(C2,17,1),2)=1,”男”,”女”))公式内的“C2”代表的是输入身份证号码的单元格。

求和: =SUM(K2:K56) ——对K2到K56这一区域进行求和;

平均数: =AVERAGE(K2:K56) ——对K2 K56这一区域求平均数;

排名: =RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名;

等级: =IF(K2>=85,”优”,IF(K2>=74,”良”,IF(K2>=60,”及格”,”不及格”)))

学期总评: =K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩;

最高分: =MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分;

最低分: =MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分;

分数段人数统计:

(1)=COUNTIF(K2:K56,”100″) ——求K2到K56区域100分的人数;假设把结果存放于K57单元格;

(2)=COUNTIF(K2:K56,”>=95″)-K57 ——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格;

(3)=COUNTIF(K2:K56,”>=90″)-SUM(K57:K58) ——求K2到K56区域90~94.5分的人数;假设把结果存放于K59单元格;

(4)=COUNTIF(K2:K56,”>=85″)-SUM(K57:K59) ——求K2到K56区域85~89.5分的人数;假设把结果存放于K60单元格;

(5)=COUNTIF(K2:K56,”>=70″)-SUM(K57:K60) ——求K2到K56区域70~84.5分的人数;假设把结果存放于K61单元格;

(6)=COUNTIF(K2:K56,”>=60″)-SUM(K57:K61) ——求K2到K56区域60~69.5分的人数;假设把结果存放于K62单元格;

(7)=COUNTIF(K2:K56,”<60″) ——求K2到K56区域60分以下的人数;假设把结果存放于K63单元格;

说明:COUNTIF函数也可计算某一区域男、女生人数。

如:=COUNTIF(C2:C351,”男”) ——求C2到C351区域(共350人)男性人数;

优秀率:=SUM(K57:K60)/55*100

及格率:=SUM(K57:K62)/55*100

标准差: =STDEV(K2:K56)——求K2到K56区域(55人)的成绩波动情况(数值越小,说明该班学生间的成绩差异较小,反之,说明该班存在两极分化);

条件求和: =SUMIF(B2:B56,”男”,K2:K56) ——假设B列存放学生的性别,K列存放学生的分数,则此函数返回的结果表示求该班男生的成绩之和;

多条件求和: {=SUM(IF(C3:C322=”男”,IF(G3:G322=1,1,0)))} ——假设C列(C3:C322区域)存放学生的性别,G列(G3:G322区域)存放学生所在班级代码(1、2、3、4、5),则此函数返回的结果表示求一班的男生人数;这是一个数组函数,输完后要按Ctrl+Shift+Enter组合键(产生“{……}”)。“{}”不能手工输入,只能用组合键产生。

根据出生日期自动计算周岁:=TRUNC((DAYS360(D3,NOW()))/360,0)

———假设D列存放学生的出生日期,E列输入该函数后则产生该生的周岁。

在Word中三个小窍门:

①连续输入三个“~”可得一条波浪线。

②连续输入三个“-”可得一条直线。

连续输入三个“=”可得一条双直线。

第二部分:必备的Excel技巧

一、excel中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色

比如: 
A1〉1时,C1显示红色 
0<A1<1时,C1显示绿色 
A1<0时,C1显示黄色

方法如下: 
1、单元击C1单元格,点“格式”>“条件格式”,条件1设为: 
公式 =A1=1 
2、点“格式”->“字体”->“颜色”,点击红色后点“确定”。 
条件2设为: 
公式 =AND(A1>0,A1<1) 
3、点“格式”->“字体”->“颜色”,点击绿色后点“确定”。 
条件3设为: 
公式 =A1<0 
点“格式”->“字体”->“颜色”,点击黄色后点“确定”。 
4、三个条件设定好后,点“确定”即出。

二、EXCEL中如何控制每列数据的长度并避免重复录入

1、用数据有效性定义数据长度。

用鼠标选定你要输入的数据范围,点”数据”->”有效性”->”设置”,”有效性条件”设成”允许””文本长度””等于””5″(具体条件可根据你的需要改变)。 
还可以定义一些提示信息、出错警告信息和是否打开中文输入法等,定义好后点”确定”。

2、用条件格式避免重复。

选定A列,点”格式”->”条件格式”,将条件设成“公式=COUNTIF($A:$A,$A1)>1”,点”格式”->”字体”->”颜色”,选定红色后点两次”确定”。 
这样设定好后你输入数据如果长度不对会有提示,如果数据重复字体将会变成红色。

三、在EXCEL中如何把B列与A列不同之处标识出来?

(一)、如果是要求A、B两列的同一行数据相比较: 
假定第一行为表头,单击A2单元格,点“格式”->“条件格式”,将条件设为: 
“单元格数值” “不等于”=B2 
点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。 
用格式刷将A2单元格的条件格式向下复制。 
B列可参照此方法设置。

(二)、如果是A列与B列整体比较(即相同数据不在同一行): 
假定第一行为表头,单击A2单元格,点“格式”->“条件格式”,将条件设为: 
“公式”=COUNTIF($B:$B,$A2)=0 
点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。 
用格式刷将A2单元格的条件格式向下复制。 
B列可参照此方法设置。 
按以上方法设置后,AB列均有的数据不着色,A列有B列无或者B列有A列无的数据标记为红色字体。

四、EXCEL中怎样批量地处理按行排序

假定有大量的数据(数值),需要将每一行按从大到小排序,如何操作? 
由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。所以,这一问题不能用排序来解决。

解决方法如下:

1、假定你的数据在A至E列,请在F1单元格输入公式:

=LARGE($A1:$E1,COLUMN(A1)) 
2、用填充柄将公式向右向下复制到相应范围。 
你原有数据将按行从大到小排序出现在F至J列。如有需要可用“选择性粘贴/数值”复制到其他地方。 
注:第1步的公式可根据你的实际情况(数据范围)作相应的修改。如果要从小到大排序,公式改为:=SMALL($A1:$E1,COLUMN(A1))

五、巧用函数组合进行多条件的计数统计

例:第一行为表头,A列是“姓名”,B列是“班级”,C列是“语文成绩”,D列是“录取结果”,现在要统计“班级”为“二”,“语文成绩”大于等于104,“录取结果”为“重本”的人数。统计结果存放在本工作表的其他列。

公式如下: 
=SUM(IF((B2:B9999=”二”)*(C2:C9999>=104)*(D2:D9999=”重本”),1,0)) 
输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号”{}”。

六、如何判断单元格里是否包含指定文本?

假定对A1单元格进行判断有无”指定文本”,以下任一公式均可: 
=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))

七、一个工作薄中有许多工作表如何快速整理出一个目录工作表

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())) 
将公式向下填充,直到出错为止,目录就生成了。

来源:数据分析网

已标记关键词 清除标记
相关推荐
excel常用函数公式及技巧搜集 从身份证号码中提取出生年月日 从身份证号码中提取出性别 从身份证号码中进行年龄判断 按身份证号号码计算至今天年龄 以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式 按身份证号分男女年龄段 【年龄和工龄计算】 根据出生年月计算年龄 根据出生年月推算生肖 如何求出一个人到某指定日期的周岁? 计算距离退休年龄的公式 求工齡 计算工龄 年龄及工龄计算自动算出工龄日期格式为(yyyy.mm.dd) 【时间和日期应用】 自动显示当前日期公式 如何在单元格中自动填入当前日期 如何判断某日是否星期天 某个日期是星期几 什么函数可以显示当前星期 求本月天数 显示昨天的日期 关于取日期 如何对日期进行上、中、下旬区分 如何获取一个月的最大天数 日期格式转换公式 【排名及排序筛选】 一个具有11项汇总方式的函数SUBTOTAL 自动排序 按奇偶数排序 自动生成序号 如何自动标示A栏中的数字大小排序? 如何设置自动排序 重复数据得到唯一的排位序列 按字符数量排序 排序字母与数字的混合内容 随机排序 排序的问题 怎样才能让数列自动加数 一个排序问题 数字的自动排序,插入后不变? 根据规律的重复的姓名列产生自动序号 排名的函数 自动排名公式 百分比排名的公式写法为: 平均分及总分排名 求名次排名 排名次根据分数进行普通排名 对于普通排名分数相同时,按顺序进行不重复排名 依分数比高低名次成绩排名 美国式排名 中国式排名 求最精简的自动排名公式 排序后排名 位次排名 根据双列成绩进行共同排名 在双列间排名 等次排名 不等次排名(行小排先) 不等次排名(行大排先) 顺次排名 有并列排名 无并列排名 有并列分段排名 无并列分段排名 成绩排名 如何排名 数据排名(隔几行排名) 根据分数进行倒排名 倒数排名函数是什么 如何实现每日各车间产量的排名 分数相同时按照一科的分数进行排名 筛选后自动产生序列号并汇总 如何筛选奇数行 函数筛选姓名 名次筛选 如何实现快速定位(筛选出不重复值) 如何请在N列中列出A1:L9中每列都存在的数值 自动为性别编号的问题 【文本与页面设置】 EXCEL中如何删除*号 将字符串中的星号“*”替换为其它字符 去空格函数如何去掉字符和单元格里的空格 怎样快速去除表中不同行和列的空格 如何禁止输入空格 代替单元格中字符串 把单元格中的数字转变成为特定的字符格式 把有六百多个单元格的一列,变成一页的多列 将N列变M列公式归纳为 一列变四列 四列变一列 重复四次填充 多行数据排成一列 将单元格一列分为多列 首写字母大写 把单元格编号中的小写字母变成大写字母 让姓名左右对齐 数字居中而小数点又对齐 计算指定单元格编号组中非空单元格的数量 比较两个单元格内容是否一致 怎么样设置才能让这一列的每个单元格只能输入12位 如何让工作表奇数行背景是红色偶数行背景是蓝色 计算特定的一组单元格中,满足条件的单元格的个数 把文本格式的数字转换成真正的数字 设置页码 Excel表格里如何插入页码的? 如何设置页脚首页为第5页 表格的页脚问题 无拘无束的页眉 打印表头 Excel打印中如何不显示错误值符号 对于一些不可打印的字符的处理 用那个函数可将个位数前面的零值显示出来? 如果你要在A3的前面插入100行 请问如何每隔30行粘贴一新行 在工作表里有连续10行数据, 现在要每行间格2行 一个大表每一行下面需要加一行空行,怎么加最方便 Excel中插入空白行 快速删除工作表中的空行快速删除空行一次删完Excel里面多出很多的空白行 每30行为一页并加上一个标题如何实现如何实现隔行都加上标题项 如何把标签页去掉的? 去掉默认的表格线(网线)表格的框线 列标的标识变了 符号的意义 双击格式刷竟也能COPY文本(不是文本格式) 查找+格式刷的妙用 光标移动 最后一行为文本最后一行为数字 如何在EXCEL中快速定位最后一行数据 用SUN函数快速求和 在Excel中快速查看所有工作表公式Excel中设置行间距 怎样同时改变多行行高 快速换行 让文本换行 在Excel中行列快速转换 将原有列中的内容倒置过来 快速回到A1单元格 复制粘贴中回车键的妙用 一次选中批注单元格 一次在所有单位格中插入批注 在公式中插入批注 不连续单元格填充同一数据 空白行的填充 怎样用函数向下实现自动填充 怎么设置自动保存 避免输入网址和电子邮件地址时的超链接 单元格前面自动加了等号 加盖公章 查找+格式刷的妙用 Excel中鼠标双击妙用 Excel中快速定位的技巧实例 在Excel中插入Flash时钟的步骤 小写数字转换成人民币大写 轻轻松松制作超复杂Excel表头 【字符截取与增减】 截取单元格里某个字符后的字符 截取字符的公式 如何确定*号
本书从函数功能、函数格式、参数说明、注意事项、Excel 版本提醒、案例应用、交叉参考7 个方面,全面、细致地介绍了Excel 2016/2013/2010/2007/2003 中公式和函数的使用方法、实际应用和操作技巧。最后3 章还将公式与函数的应用扩展到了条件格式、数据验证及图表中,以便使它们发挥更强大的功能。本书采用理论与实践相结合的方式,提供了457 个案例,涉及多个行业,读者可以根据书中的案例举一反三,将其直接应用到实际工作中,有效提高学习效果与实际应用能力。 本书既可以作为函数速查工具手册,又可以作为丰富的函数应用案例宝典,适合对Excel 公式与函数有需求的读者阅读。 第1章 公式与函数基础 1 1.1 了解公式 1 1.1.1 公式的组成部分 1 1.1.2 数据类型及其相互转换 2 1.1.3 运算符及其优先级 3 1.1.4 普通公式与数组公式 5 1.1.5 单个单元格公式与多个单元格公式 5 1.1.6 Excel对数字精度、公式与函数等方面的限制 5 1.2 输入与编辑公式 6 1.2.1 A1引用样式与R1C1引用样式 6 1.2.2 输入公式 8 1.2.3 修改公式 9 1.2.4 移动和复制公式 9 1.2.5 删除公式 12 1.2.6 改变公式的计算方式 13 1.3 在公式中使用函数 14 1.3.1 为什么使用函数 14 1.3.2 函数的类型 14 1.3.3 函数的参数 15 1.3.4 在公式中输入函数 16 1.4 在公式中使用名称 18 1.4.1 名称的作用范围 19 1.4.2 命名区域 19 1.4.3 命名公式 20 1.4.4 命名常量 21 1.4.5 将名称应用到公式中 21 1.5 使用数组公式 23 1.5.1 单个单元格数组公式与多单元格数组公式 23 1.5.2 数组的维数 24 1.5.3 输入数组公式 25 1.5.4 修改数组公式 25 1.5.5 扩展或缩小多单元格数组公式 26 1.5.6 选择数组公式所在区域 27 1.5.7 使用常量数组 27 1.6 创建跨工作表和跨工作簿引用的公式 28 1.6.1 创建引用其他工作表中的数据的公式 28 1.6.2 创建引用其他工作簿中的数据的公式 29 1.6.3 创建对多个工作表中相同单元格区域的三维引用 30 1.6.4 更新跨工作簿引用的公式 31 1.7 审核公式 31 1.7.1 使用公式错误检查器 32 1.7.2 定位特定类型的数据 33 1.7.3 追踪单元格之间的关系 33 1.7.4 监视单元格内容 35 1.7.5 使用公式求值器 36 1.8 处理公式中的错误 36 1.8.1 括号不匹配 37 1.8.2 单元格被#符号填满 37 1.8.3 空白但非空的单元格 37 1.8.4 显示值与实际值 38 1.8.5 返回错误值 39 1.8.6 循环引用 41 1.9 公式使用技巧 42 1.9.1 在多个单元格中输入同一个公式 42 1.9.2 显示公式而不是值 42 1.9.3 查看公式的中间结果 42 1.9.4 将公式转换为值 43 1.9.5 复制公式但不使用相对引用 43 1.9.6 隐藏公式 44 1.9.7 禁止用户修改公式 45 第2章 数学和三角函数 46 2.1 常规计算 48 2.1.1 SIGN——返回数字的符号 48 2.1.2 ABS——计算数字的绝对值 49 2.1.3 SUM——计算数字之和 50 2.1.4 PRODUCT——计算数字之积 52 2.1.5 SQRT——计算正平方根 52 2.1.6 MOD——返回商的余数 53 2.1.7 QUOTIENT——返回商的整数部分 55 2.1.8 GCD——计算最大公约数 56 2.1.9 LCM——计算最小公倍数 57 2.1.10 SUMIF——按给定条件对指定单元格求和 57 2.1.11 SUMIFS——按多个条件对指定单元格求和 59 2.1.12 SUMPRODUCT——计算数组元素的乘积之和 60 2.1.13 SUMSQ——计算参数的平方和 61 2.1.14 SUMXMY2——计算数组对应值之差的平方和 62 2.1.15 SUMX2MY2——计
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页