EXCEL表格函数公式大全Word文档格式.docx
- 文档编号:19334986
- 上传时间:2023-01-05
- 格式:DOCX
- 页数:111
- 大小:328.94KB
EXCEL表格函数公式大全Word文档格式.docx
《EXCEL表格函数公式大全Word文档格式.docx》由会员分享,可在线阅读,更多相关《EXCEL表格函数公式大全Word文档格式.docx(111页珍藏版)》请在冰豆网上搜索。
,19&
=MID<
A1,7,4>
年"
A1,11,2>
月"
A1,13,2>
日"
A1<
从中提取出性别
MOD<
A1,15,3>
2>
男"
女"
〔最简单公式〕
RIGHT<
LEFT<
A1,17>
A2,17>
>
VALUE<
ROUND<
A1,1>
/2,2>
=1,"
从中进行年龄判断
A3<
DATEDIF<
TEXT<
A3>
=15*19&
A3,7,6+<
=18*2>
TODAY<
Y"
=DATEDIF〔A1,TODAY〔〕,"
〕
〔以上公式会判断是否已过生日而自动增减一岁〕
=YEAR<
NOW<
E2,IF<
E2>
=18,9,7>
-1900
TODAY<
-IF<
A1,7,2>
-VALUE<
B1,7,4>
岁"
B1,18,1>
="
CONCATENATE<
B1,7,2>
按号计算至今天年龄
=DATEDIF<
y"
以20##10月31日为基准日,按按计算年龄<
周岁>
的公式
*1,"
2006-10-31"
按号分男女年龄段
按号分男女年龄段,号在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<
K1,7,4>
K1,11,2>
K1,13,2>
{0,16,50,60}-{0,0,5,5}*ISEVEN<
K1,17,1>
=SUM<
--<
={0,16,45,55}+{0,0,5,5}*MOD<
㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜㎜
[年龄和工龄计算]
根据出生年月计算年龄
=DATEDIF<
A1,TODAY<
周岁"
A1,NOW<
根据出生年月推算生肖
中国人有12生肖,属什么可以推算出来。
即用诞生年份除以12,再用除不尽的余数对照如下:
0→猴,1→鸡,2→狗,3→猪,4→鼠,5→牛,6→虎,7→兔,8→龙,9→蛇,10→马,11→羊例如:
XXX出生于1921年,即用1921年除以12,商得数为160,余数为1,对照上面得知余数1对应生肖是鸡,XXX就属鸡。
猴鸡狗猪鼠牛虎兔龙蛇马羊"
MOD<
YEAR<
12>
+1,1>
〔2007〕
如何求出一个人到某指定日期的周岁?
起始日期,结束日期,"
计算距离退休年龄的公式
E2="
=V2,"
已经退休"
距离退休还有"
DATE<
U2>
+<
V2>
MONTH<
DAY<
YM"
个月"
Md"
天"
其中E2为年龄〔可用的公式生成〕;
V2为法定退休年龄〔男60,女50〕公式为:
D2="
60,50>
D2为男或女〔可用的公式生成〕;
U2为出生年月日〔可用的公式生成〕。
求工齡
B2,TODAY<
ym"
md"
计算工龄
C6,C8,"
求两日期间的年数
求两日期间除去整年数剩余的月数
m"
求两日期间的总月数
如果只需要算出周年的话,可以用=datedif<
1978-8"
2006-5"
年龄与工龄计算
有出生年月如何求年龄?
有工作时间如何求工龄?
〔求出的结果为多少年另几个月,如:
0303的形式,即3年零3个月〕。
a1是出生年月或工作时间:
=datedif<
a1,today<
=text<
datedif<
00"
text<
如[B2]=1964-9-1则:
'
显示4009
00年"
00月"
显示40年09月
如果你找不到DATEDIF函数,也可以不用DATEDIF函数,
-B2>
MONTH<
-1,12>
'
显示40年09个月
自动算出工龄日期格式为<
yyyy.mm.dd>
能否用:
〔yyyy.mm.dd〕这种格式来计算出工龄有多长呢~?
以前用这样一段〔=TEXT<
-A1>
〕。
但这种方法只能用:
〔yyyy-mm-dd〕这样的日期格式才能实现!
你不妨把"
."
替换成"
不就行了吗,再说后者是日期的一种标准格式,
-SUBSTITUTE<
A1,"
[时间和日期应用]
自动显示当前日期公式
当前年
=MONTH<
当前月
=DAY<
当前日
如何在单元格中自动填入当前日期
Ctrl+;
如何判断某日是否星期天
=WEEKDAY<
A2,2>
aaaa"
=MOD<
A1,7>
2
某个日期是星期几
比如20##2月9日,在一单元格显示星期几。
aaa"
〔五〕
〔星期五〕
ddd"
〔Fri〕
dddd"
〔Friday〕
什么函数可以显示当前星期
如:
星期二10:
41:
56
aaaa
hh:
mm:
ss"
求本月天数
设A1为2006-8-4
A1=DAY<
+1,0>
也有更簡便的公式:
EOMONTH<
0>
需加載分析工具箱。
当前月天数:
=DATE<
-DATE<
1>
用公式算出除去当月星期六、星期日以外的天数
=SUMPRODUCT<
ROW<
INDIRECT<
:
7>
1>
显示昨天的日期
每天需要单元格显示昨天的日期,但双休日除外。
例如,今天是7月3号的话,就显示7月2号,如果是7月9号,就显示7月6号。
AAA"
一"
-3,IF<
-2,TODAY<
-1>
-3,TODAY<
关于取日期
怎么设个公式使A1在年月日向后推5年,变成2011-7-15
+5,MONTH<
=EDATE<
A1,12*5>
如何对日期进行上、中、下旬区分
=LOOKUP<
DAY<
{0,11,21,31},{"
上旬"
中旬"
下旬"
}>
如何获取一个月的最大天数
2002,3,1>
或"
B1-1>
B1为"
2001-03-01
日期格式转换公式
将"
01/12/2005"
转换成"
20050112"
格式
=RIGHT<
A1,4>
A1,4,2>
A1,2>
=YEAR<
$A2>
该公式不用设置数据有效性,但要设置储存格格式。
也可以用下列两方法:
1、先转换成文本,然后再用字符处理函数。
2、[数据]-[分列][日期]-[MDY]
将"
20##9月"
转换成"
200509"
先用公式:
a1,"
yyyymm"
+0然后将单元格格式为常规。
2005-8-6"
格式转换为"
20050806"
用公式:
YYYYMMDD"
反之,将20050806转为日期2005-8-6格式,可用公式:
A1,5,2>
RIGHT<
另四种公式:
=text<
显示:
2005-08-06
=--TEXT<
把单元格设置为日期格式显示:
2005-8-6
=TEXT<
20050806,"
*1,单元格设置日期型显示:
=VALUE<
20060501"
转换为"
2006-05-01"
A2,4>
A2,5,2>
199306"
1993-6"
公式1:
=LEFT<
A3,4>
A3,2>
公式2:
=--TEXT<
A3*100+1,"
公式2需要设置单元格格式,自定义:
e-m
公式3:
A3&
01"
e-m"
把198405转换成1984.05
一、查找—1984,替换—1984.
二、如果全部是年月的话,我个人建议,
1、采取辅助=mid<
xxxxxx,1,4>
&
"
right<
xxxxxx,2>
2、选中这列,用数据中的分列。
然后……………
三、单元格格式/数字/自定义,类型下面输入:
####"
##
将文本"
2004.01.02"
转换为日期格式:
2004-1-2
A1,1,4>
A1,6,2>
A1,9,2>
将2005-8-6转换为20##8月6日格式
yyyy"
d"
;
象22怎样转换成22日?
转成当年当月的日子
公式为:
=date<
year<
now<
month<
22>
20##5月"
20##05月"
A8,"
mm"
也可以这样处理:
选中单元格,设置单元格公式-数字-自定义,将yyyy"
改为:
即可。
但这方法打印出来显示为:
2006/5/
1968年6月12日"
1968/6/12"
1968/6/12
yyyy/mm/dd"
1968/06/12
1968-6-12"
1968-6-12
yyyy-mm-dd"
显示:
1968-06-12
将1993-12-28的日期格式转换成1993年12月
=CONCATENATE<
也可以自定义格式[$-404]e"
1978-5-2"
包含年月日的日期转换成"
197805"
只有年月的格式
=year<
month<
要将"
99.08.15"
格式转换成"
1999.08.15"
如何做
选中列,数据菜单中选分列,分列过程中"
格式"
选"
日期YMD"
结束。
要保持2005/8/6格式
当输入2005/8/6后系统自动变成2005-8-6,要保持2005/8/6格式,可以使用强制文本〔前面加'
号〕或使用公式=TEXT<
YYYY/MM/DD"
。
也可以用另一种公式:
ISERROR<
0000!
/00!
/00"
二○○三年十二月二十五日"
转为"
2003-12-25"
格式,
1、可以用数组公式将中文日期转化为日期系列数{=14610+MATCH<
SUBSTITUTE<
A3,"
元"
$14611:
$55153>
[DBNum1]yyyy年m月d日"
}
该公式速度较慢。
2、改进后的公式,速度要快的多:
{=DATE<
1899+MATCH<
A7,4>
$1900:
$2100>
[DBNum1]0000"
MATCH<
A7,6,7>
$1:
$366>
[DBNum1]m月d日"
要设置为1900年的日期格式。
日期格式转换
如A列是月份数为8,B列是日期数为18,如何在C列显示"
8月18日"
=A1&
B1&
反之,要将C列的"
直接分别到D、E列,显示月份和日期,
月数份=LEFT<
C5,FIND<
C5>
日期数=MID<
+1,FIND<
-FIND<
也可分别用公式:
=month<
--c5>
=day<
日期格式转换问题
输入的日期是:
04-07-26.与另一格的"
001"
合并,合并出来是:
040726001.
YYMMDD"
要想自动取得"
编制日期:
XXXX年X月X日"
可在该单元格输入="
yyyy年m月d日"
[排名与排序筛选]
一个具有11项汇总方式的函数SUBTOTAL
=SUBTOTAL<
9,$B$2:
B2>
在数据筛选求和上有意想不到的功能,11项功能为:
1、求平均数,2、求计数,3、求计数值〔自动筛选序列〕4、求最大值,5、求最小值,6、求乘积,7、求总体标准偏差,8、求标准偏差、9、求和,10、求方差,11、求总体方差。
自动排序
3,$B$2:
A1,1,N<
C1>
+1>
按奇偶数排序
我想请教怎样按奇数顺序然后再按偶数顺序排序
0,1>
50,<
-100,<
=ROW<
*2-1-<
50>
*99
自动生成序号
比如在第二列中输入容回车后第一列的下一行自动生成序列号。
B2<
A2+1,"
如何自动标示A栏中的数字大小排序?
=RANK<
A1,$A$1:
$A$5>
A1,A:
A>
如何设置自动排序
A列自动变成从小到大排列
B=SMALL<
A$2:
A$28,ROW<
1:
A列自动变成从大到小排列
B=LARGE<
重复数据得到唯一的排位序列
想得到数据的出现总数吗〔{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<
下拖,单击C2,单击工具栏上的"
升序排列"
即可,删除C列。
排序字母与数字的混合容
日常使用中,表格经常会有包含字母和数字混合的数据,对此类数据排序时,通常是先比较字母的大小,再比较数字的大小,但EXCEL是按照对字符进行逐位比较来排序的,如下表:
A7排在第5位,而不是第1位。
排序结果无法令人满意。
A
1
A122
A29
3
A317
4
A43
5
A7
6
B20
7
B3
8
C144
9
C5
10
C33
B
A007
A029
A043
B003
B020
C005
C033
如果希望EXCEL改变排序的规则,需要将数据做一些改变。
在B1中输入公式:
RIGHT<
000"
A1,LEN<
3>
下拖
单击B2,单击工具栏上的"
即可。
随机排序
次序"
=RAND〔〕,下拖,单击C2,单击工具栏上的"
降序排列"
即可对歌曲清单进行随机排序。
排序的问题
我想要这样的排序:
2001-2003
2004-2006
2007-2009
2010-2012;
其实不是数据排序,应该是数据填充。
输入公式=LEFT<
E3,4>
+3&
+3即可。
怎样才能让数列自动加数
怎样做才能让数列自动加数
A
A0001
B
B0001
A0002
C
C0001
A0003
B0002
C0002
公式为=A1&
COUNTIF<
A$1:
A1,A1>
向下拖
!
A1&
0000"
否则数字超过9就错误了。
一个排序问题
一个电子表格,格式
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 表格 函数 公式 大全