Excel常用公式函数使用.docx
- 文档编号:10251452
- 上传时间:2023-05-24
- 格式:DOCX
- 页数:118
- 大小:402.88KB
Excel常用公式函数使用.docx
《Excel常用公式函数使用.docx》由会员分享,可在线阅读,更多相关《Excel常用公式函数使用.docx(118页珍藏版)》请在冰点文库上搜索。
Excel常用公式函数使用
常用函数公式及技巧搜集
【身份证信息提取】
从身份证号码中提取出生年月日
=IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"-"&MID(A2,11,2),MID(A2,7,4)&"-"&MID(A2,11,2)&"-"&MID(A2,13,2))
从身份证号码中提取出性别
=IF(MOD(MID(A1,15,3),2),"男","女")
从身份证号码中进行年龄判断
以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式
=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1,"2006-10-31","y")
按身份证号分男女年龄段
按身份证号分男女年龄段,身份证号在K列,年龄段在J列(身份证号为18位)
男性16周岁以下为 1
男性16周岁(含16周岁)以上至50周岁为2
男性50周岁(含50周岁)以上至60周岁为3
男性60周岁(含60周岁)以上为 4
女性16周岁以下为 1
女性16周岁(含16周岁)以上至45周岁为2
女性45周岁(含45周岁)以上至55周岁为3
女性55周岁(含55周岁)以上为 4
=MATCH(DATEDIF(DATE(MID(K1,7,4),MID(K1,11,2),MID(K1,13,2)),TODAY(),"y"),{0,16,50,60}-{0,0,5,5}*ISEVEN(MID(K1,17,1)))
=SUM(--(DATEDIF(MID(K1,7,4)&"/"&MID(K1,11,2)&"/"&MID(K1,13,2),TODAY(),"y")>={0,16,45,55}+{0,0,5,5}*MOD(MID(K1,17,1),2)))
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【年龄和工龄计算】
根据出生年月计算年龄
=DATEDIF(A1,TODAY(),"y")
根据出生年月推算生肖
中国人有12生肖,属什么可以推算出来。
即用诞生年份除以12,再用除不尽的余数对照如下:
0→猴,1→鸡,2→狗,3→猪,4→鼠,5→牛,6→虎,7→兔,8→龙,9→蛇,10→马,11→羊例如:
XXX出生于1921年,即用1921年除以12,商得数为160,余数为1,对照上面得知余数1对应生肖是鸡,XXX就属鸡。
=MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(YEAR(A2),12)+1,1)(2007)
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
如何统计各年龄段的数量
如何计算20-50岁的人数?
=COUNTIF(C3:
C17,">=20")-COUNTIF(C3:
C17,">50")
如何统计40-50岁的人的个数
=countif(a:
a,">40")-countif(a:
a,">50")
=COUNTA(A1:
A10,D1:
D10)
如何让EXCEL自动从头统计到当前单元格
情况如下:
C列要根据A列的内容来统计B列的数据,范围从A1:
An,即当A列中An有数据时,Cn自动根据An的值,统计B1:
Bn的数据。
{=SUM(INDIRECT("B1:
B"&LARGE((A1:
A65535<>"")*(ROW(A1:
A65535)),1)))}
统计人数
见表:
性别
年龄
男
6
女
35
男
3
男
55
男
21
男
53.5
女
55
女
56
男
65
女
45
女
53
男
51
如何计算20-50岁的人数?
=COUNTIF(C3:
C17,">=20")-COUNTIF(C3:
C17,">50")
如何计算男20-50岁的人数?
=SUMPRODUCT((B3:
B17="男")*(C3:
C17>=20)*(C3:
C17<=50))
nnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnn
【时间和日期应用】
自动显示当前日期公式
=YEAR(NOW())当前年
=MONTH(NOW())当前月
=DAY((NOW()))当前日
如何在单元格中自动填入当前日期
Ctrl+;
显示昨天的日期
每天需要单元格内显示昨天的日期,但双休日除外。
例如,今天是7月3号的话,就显示7月2号,如果是7月9号,就显示7月6号。
=IF(TEXT(TODAY(),"AAA")="一",TODAY()-3,IF(TEXT(TODAY(),"AAA")="日",TODAY()-2,TODAY()-1))
=IF(TEXT(TODAY(),"AAA")="一",TODAY()-3,TODAY()-1)
关于取日期
怎么设个公式使A1在年月日向后推5年,变成2011-7-15
=DATE(YEAR(A1)+5,MONTH(A1),DAY(A1))
=EDATE(A1,12*5)
日期格式转换公式
将“01/12/2005”转换成“20050112”格式
=RIGHT(A1,4)&MID(A1,4,2)&LEFT(A1,2)
=YEAR($A2)&TEXT(MONTH($A2),"00")&TEXT(DAY($A2),"00")该公式不用设置数据有效性,但要设置储存格格式。
也可以用下列两方法:
1、先转换成文本,然后再用字符处理函数。
2、[数据]-[分列][日期]-[MDY]
将“2005年9月”转换成“200509”格式
先用公式:
=text(a1,"yyyymm")+0然后将单元格格式为常规。
将“2005-8-6”格式转换为“20050806”格式
用公式:
=TEXT(A1,"YYYYMMDD")
反之,将20050806转为日期2005-8-6格式,可用公式:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))
另四种公式:
=text(a1,"0000-00-00")显示:
2005-08-06
=--TEXT(A1,"#-00-00"),把单元格设置为日期格式显示:
2005-8-6
=TEXT(20050806,"0000-00-00")*1,单元格设置日期型显示:
2005-8-6
=VALUE(LEFT(A1,4)&"-"&MID(A1,5,2)&"-"&RIGHT(A1,2))显示:
2005-8-6
将“20060501”转换为“2006-05-01”格式
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
将“199306”转换为“1993-6”
公式1:
=LEFT(A3,4)&"-"&RIGHT(A3,2)*1
公式2:
=--TEXT(A3*100+1,"#-00-00")公式2需要设置单元格格式,自定义:
e-m
公式3:
=TEXT(TEXT(A3&"01","0000-00-00"),"e-m")
把198405转换成1984.05
一、查找—1984,替换—1984.
二、如果全部是年月的话,我个人建议,
1、采取辅助=mid(xxxxxx,1,4)&"."&right(xxxxxx,2)
2、选中这列,用数据中的分列。
然后……………
三、单元格格式/数字/自定义,类型下面输入:
####"."##
将文本“2004.01.02”转换为日期格式:
2004-1-2
=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))
将2005-8-6转换为2005年8月6日格式
=TEXT(A1,"yyyy""年""m""月""d""日"";@")
象22怎样转换成22日?
转成当年当月的日子
公式为:
=date(year(now()),month(now()),22)
将“2006年5月”转换成“2006年05月”
公式为:
=TEXT(A8,"yyyy""年""mm""月"";@")
也可以这样处理:
选中单元格,设置单元格公式-数字-自定义,将yyyy“年”m“月”改为:
yyyy“年”mm“月”,即可。
但这方法打印出来显示为:
2006/5/
将“1968年6月12日”转换为“1968/6/12”格式
=YEAR(A1)&"/"&MONTH(A1)&"/"&DAY(A1)显示:
1968/6/12
=TEXT(A1,"yyyy/mm/dd")显示:
1968/06/12
将“1968年6月12日”转换为“1968-6-12”格式
=YEAR(A1)&"-"&MONTH(A1)&"-"&DAY(A1)显示:
1968-6-12
=TEXT(A1,"yyyy-mm-dd")显示:
1968-06-12
将1993-12-28的日期格式转换成1993年12月
=CONCATENATE(YEAR(A1),"年",MONTH(A1),"月")
=YEAR(A1)&"年"&MONTH(A1)&"月"
也可以自定义格式[$-404]e"年"m"月"
将“1978-5-2”包含年月日的日期转换成“197805”只有年月的格式
=year(A1)&text(month(A1),"00")
要将“99.08.15”格式转换成“1999.08.15”如何做
选中列,数据菜单中选分列,分列过程中“格式”选“日期YMD”,结束。
要保持2005/8/6格式
当输入2005/8/6后系统自动变成2005-8-6,要保持2005/8/6格式,可以使用强制文本(前面加'号)或使用公式=TEXT(A1,"YYYY/MM/DD")。
也可以用另一种公式:
=IF(ISERROR(TEXT(A1,"yyyy/mm/dd")),TEXT(A1,"0000!
/00!
/00"),TEXT(A1,"yyyy/mm/dd"))
将“二○○三年十二月二十五日”转为“2003-12-25”格式,
1、可以用数组公式将中文日期转化为日期系列数{=14610+MATCH(SUBSTITUTE(A3,"元","一"),TEXT(ROW($14611:
$55153),"[DBNum1]yyyy年m月d日"),0)}
该公式速度较慢。
2、改进后的公式,速度要快的多:
{=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:
$2100),"[DBNum1]0000"),0),MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),"元","一"),TEXT(ROW($1:
$366),"[DBNum1]m月d日"),0)),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),"元","一"),TEXT(ROW($1:
$366),"[DBNum1]m月d日"),0)))}
要设置为1900年的日期格式。
日期格式转换
如A列是月份数为8,B列是日期数为18,如何在C列显示“8月18日”
=A1&"月"&B1&"日"
反之,要将C列的“8月18日”直接分别到D、E列,显示月份和日期,
月数份=LEFT(C5,FIND("月",C5)-1)
日期数=MID(C5,FIND("月",C5)+1,FIND("日",C5)-FIND("月",C5)-1)
也可分别用公式:
=month(--c5)
=day(--c5)
日期格式转换问题
输入的日期是:
04-07-26.与另一格的"001"合并,合并出来是:
040726001.
=TEXT(A1,"YYMMDD")&"001"
要想自动取得“编制日期:
XXXX年X月X日”
可在该单元格输入="编制日期:
"&TEXT(TODAY(),"yyyy年m月d日")
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【排名及排序筛选】
一个具有11项汇总方式的函数SUBTOTAL
=SUBTOTAL(9,$B$2:
B2)
在数据筛选求和上有意想不到的功能,11项功能为:
1、求平均数,2、求计数,3、求计数值(自动筛选序列)4、求最大值,5、求最小值,6、求乘积,7、求总体标准偏差,8、求标准偏差、9、求和,10、求方差,11、求总体方差。
自动排序
=SUBTOTAL(3,$B$2:
B2)*1
=IF(A2<>A1,1,N(C1)+1)
自动生成序号
比如在第二列中输入内容回车后第一列的下一行自动生成序列号。
=IF(B2<>"",A2+1,"")
如何自动标示A栏中的数字大小排序?
=RANK(A1,$A$1:
$A$5)
=RANK(A1,A:
A)
如何设置自动排序
A列自动变成从小到大排列
B=SMALL(A$2:
A$28,ROW(1:
1))
A列自动变成从大到小排列
B=LARGE(A$2:
A$28,ROW(1:
1))
重复数据得到唯一的排位序列
想得到数据的出现总数吗({1,2,2,3,4,4,5}数据的出现总数为5)?
解答:
不需要插列,不需要很多的函数就行了.=RANK(B3,B$3:
B$12)+COUNTIF(B$3:
B3,B3)-1
按字符数量排序
制作歌曲清单时,习惯按字符数量来排列分类,但是EXCEL并不能直接按字数排序。
需要先计算出每首歌曲的字数,然后再进行排序。
如A、B列分别为“歌手”和“歌名”,在C1输入“字数”,在C2输入公式:
=LEN(B2)下拖,单击C2,单击工具栏上的“升序排列”即可,删除C列。
排序字母与数字的混合内容
日常使用中,表格经常会有包含字母和数字混合的数据,对此类数据排序时,通常是先比较字母的大小,再比较数字的大小,但EXCEL是按照对字符进行逐位比较来排序的,如下表:
A7排在第5位,而不是第1位。
排序结果无法令人满意。
A
1
A122
2
A29
3
A317
4
A43
5
A7
6
B20
7
B3
8
C144
9
C5
10
C33
A
B
1
A7
A007
2
A29
A029
3
A43
A043
4
A122
A122
5
A317
A317
6
B3
B003
7
B20
B020
8
C5
C005
9
C33
C033
10
C144
C144
如果希望EXCEL改变排序的规则,需要将数据做一些改变。
在B1中输入公式:
LEFT(A1,1)&RIGHT("000"&RIGHT(A1,LEN(A1)-1),3)下拖
单击B2,单击工具栏上的“升序排列”即可。
随机排序
如A、B列分别为“歌手”和“歌名”,在C1输入“次序”,在C2输入公式:
=RAND(),下拖,单击C2,单击工具栏上的“降序排列”即可对歌曲清单进行随机排序。
排序的问题
我想要这样的排序:
2001-2003
2004-2006
2007-2009
2010-2012;
其实不是数据排序,应该是数据填充。
输入公式=LEFT(E3,4)+3&"-"&RIGHT(E3,4)+3即可。
怎样才能让数列自动加数
怎样做才能让数列自动加数
A A0001
B B0001
A A0002
C C0001
A A0003
B B0002
C C0002
公式为=A1&"000"&COUNTIF(A$1:
A1,A1)向下拖
=TEXT(COUNTIF(A$1:
A1,A1),"!
"&A1&"0000")否则数字超过9就错误了。
一个排序问题
一个电子表格,格式是101、102...999,10101、10102...99901,1010101,1020201...9990101,请问如何将它排列成101,10101,1010101,102,10201,1020101,...999,99901,9990101的形式。
我在数字前加了个字母,比如"d"&"数字",然后用排序就可以把它们按你的需求排列了.最后再把字母"d"去掉。
数字的自动排序,插入后不变?
1赵一总经理
2赵二副经理
3赵三副经理
4赵四技术员
5赵五
6赵六员工
如上的一个表,如何实现当我把赵六这一整行(第6行)插入到上面的表中时,A列的序列号不变?
最后的效果如下:
1赵一总经理
2赵二副经理
3赵六员工
4赵三副经理
5赵四技术员
6赵五
A1单元格输入公式=row(),往下拉,然后再插入。
=SUBTOTAL(3,$B$2:
$B2)
在A1中输入公式:
“=if(b1="","",counta($b$1:
b1)”后下拉复制至A列各行即可(“”不必输入)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
筛选后自动产生序列号并汇总
自动产生序列号:
在A1输入以下公式,往下拖。
=SUBTOTAL(3,$B$2:
B2)*1
自动汇总,用以下公式:
=SUBTOTAL(9,$B$2:
B2)
说明:
汇总时,不要在“全选”状态下进行,先“筛选”出某一单位,自动求和∑。
然后再恢复到“全选”或者选择任何单位,就能自动汇总了(在“筛选”出某一单位进行求和时,一般表格会自动产生以上汇总公式)。
其它:
如同时要在其它单元格显示人数,在“全选”状态下,选定单元格,点“fx”(用“sum”函数)再点击序列号最末尾数,即可。
如何筛选奇数行
公式=MOD(A1,2)=1
函数筛选姓名
如何把两列中只要包含A和A+的人员筛选出来
=IF(ISNUMBER(FIND("A",C2))+ISNUMBER(FIND("A",B2))>0,"OK","")
名次筛选
名次=RANK(K5,K$2:
K$435)
班名次=RANK(K6,OFFSET(K$2,MATCH(A6,A:
A,)-2,,COUNTIF(A$1:
A$500,A6)))
如何实现快速定位(筛选出不重复值)
=IF(COUNTIF($A$2:
A2,A2)=1,A2,"")
=IF((COUNTIF($A$2:
A2,A2)=1)=TRUE,A2,"")
=INDEX(A:
A,SMALL(IF(MATCH(A$1:
A$20,A$1:
A$20,)=ROW($1:
$20),ROW(A$1:
A$20),65536),ROW()))&""(数组公式)
如何请在N列中列出A1:
L9中每列都存在的数值
{=IF(ROW()>SUM(--x),"",INDEX(A:
A,SMALL(IF(x,ROW($A$1:
$A$9)),ROW())))}
自动为性别编号的问题
有一个编码,5位,第1位,1为男,2为女,后面4位,代表他的编号,从0001-9999,如何达到下表:
性别 编码
男 10001
男 10002
女 20001
男 10003
女 20002
男的也是从0001-9999
女的也是从0001-9999
如果你是已经输入了其它信息,仅仅为快速输入编码的话。
用筛选可以实现吧。
先以“男”为关键字进行排序,然后在第一个男的编码输入10001,下拉复制到最后一单即可。
同理再以“女”排序。
完成目标。
用公式:
=IF(A2="",TEXT(COUNTIF(A$2:
A2,A2),"10000"),TEXT(COUNTIF(A$2:
A2,A2),"20000"))向下拖
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
【文本与页面设置】
EXCEL中如何删除*号
在录入帐号是录入了*号,如何删除。
可以用函数SUBSTITUTE(a1,"*","")
查找~*,替换为空。
将字符串中的星号“*”替换为其它字符
在查找栏输入~*
替换为“-”即可。
去空格函数
如何删去单元格中的空格,如姓名前,中,后的空格,即单元格中是两个字的人名中间有一个空格,想删去有何方法。
如:
中 国,改为:
中国。
1、用公式:
=SUBSTITUTE(A2,"","")注:
第一对双引号中有一空格。
而第二个“”中是无空格的。
2、利用查找-替换,一次性全部解决。
“编辑”-“替换”(或Ctrl+H),在“查找”栏内输入一空格,“替换”什么也不输入(空白)。
然后“全部替换”即可。
3、有一个专门删除空格的函数:
TRIM()
在EXCEL编辑栏里,不管输中文还是英文只能输一个字节的空格,但如果字与字中间是两个字节的空格,那么TRIM()就不起作用了,它就不认为是一个空格,而是一个汉字,怎么去“TRIM”也没用。
如:
单元格A1中有“中 心 是”,如果用TRIM则变成“中 心 是”,想将空格全去掉,只能用SUBSTITUDE()函数,多少空格都能去掉。
如何去掉字符和单元格里的空格
89000795018900079501~
19000788011900078802~
=SUBSTITUTE(B2,"~","")
怎样快速去除表中不同行和列的空格
编辑-定位-定位条件-空值,可选中所有空单元格,再删除。
如何禁止输入空格
在Excel中如何通过编辑“有效数据”来禁止录入空格?
烦请大侠们费心解答。
解答:
有效性公式。
=COUNTIF(A1,"**")=0
(注:
COUNTIF(A1,"**")在单元格有空格时结果为1,没有空格时结果为0
如希望第一位不能输入空格:
countif(a1,"*")=0
如希望最后一位不能输入空格:
countif(a1,"*")=0)
代替单元格中字符串
单元格编号,开始位数,从开始位数算起第几位数,要用于代替的的字符串。
windows2000变成windows2K
=REPLACE(B2,8,3,"K")
单元格编号,要代替掉的字符,要用作代替的字符,第几个。
代替单元格B391中的全部TT,改为UU。
EETTCCTTFF变成EEUUCCUUFF
=SUBSTITUTE(B394,"TT","UU")
只代替单元格B391中的第一次出现的TT,改为UU。
EETTCCTTFF变成EEUUCCTTFF
=SUBSTITUTE(B397,"TT","UU",1)
把单元格中的数字转变成为特定的字符格式
函数中的第二个参数的双引号一定不能是中文格式的(不能用任意中文输入法输入的双引号。
)
实例:
20000目的:
变成带有美元符号的字符
10000变成带有人民币符号的字符
151581变成带有欧元符号的字符
1451451变成中文繁体的字符
15748415变成中文简体的字符
操作步骤:
=TEXT(B72,"$0.00")结果:
$20000.00
=TEXT(B73,"¥0.00")¥10000.00
=TEXT(B74,"€0.00")€151581.00
=TEXT(B75,"[DBNum2]G/通用格式")壹佰肆拾伍万壹仟肆佰伍拾壹
=TEXT(B76,"[DBNum1]G/通用格式")一千五百七十四万八千四百一十五
把有六百多个单元格的一列,变成一页的多列
有一张表,共有14页,但每页只有一列,如何把他们整合在一起,变成一页(
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 常用 公式 函数 使用