EXCEL实例疑难解析.docx
- 文档编号:30119798
- 上传时间:2023-08-05
- 格式:DOCX
- 页数:38
- 大小:56.48KB
EXCEL实例疑难解析.docx
《EXCEL实例疑难解析.docx》由会员分享,可在线阅读,更多相关《EXCEL实例疑难解析.docx(38页珍藏版)》请在冰豆网上搜索。
EXCEL实例疑难解析
=TEXT(DATE(LEFT(A1,4),MID(A1,6,2),RIGHT(A1,2)),"yyyy-m-d")
LEFT(A1,4)是指对A1单元格的值,从第一位算起,向右取4位,即1988
MID(A1,6,2)是指对A1单元格的值,从第六位算起,向右取2位,即01
RIGHT(A1,3)是指对A1单元格的值,从最后一位算起,向左取3位,即.01
DATE(,,)函数是指将年月日组成“年-月-日”格式
TEXT(,"YYYY-M-D")是指将上述所求出来的答案以“YYYY-M-D”日期格式的形式体现。
最后的答案为1988-01-01
=RIGHT(LEFT(A2,10),1)这个excel公式是什么意思?
A2单元格是个字符串:
首先从A2单元格第一个字符从左往右取10个字符,
然后再对取出来的10个字符从右向左取1个字符。
假如A2中是“abcdefghijkl”,先从左向右取10个变成
abcdefghij,然后从右往左取1个,就变成j了。
如果不足10个字符的话,那就是最后一个字符了。
left(a2,10)
left是左边的意思;
left(a2,10)意思是:
对于a2单元格的内容,取其10个位数。
从左边起第一位开始算到第10位。
假如a2单元格的内容位数不足10位(例:
8位)的话,那就取其全部内容(8位数)。
right(left(a2,10),1)
right是右边的意思;
right(left(a2,10),1)的意思是:
在上述left(a2,10)求出的答案上,从其右边算起,取一位数值。
3-SUM(--ISERROR(FIND(MID($P4,{1,2,3},1),LEFT(R$1,6))))中的双横线是什么意思?
这个公式中,ISERROR(FIND(MID($P4,{1,2,3},1),LEFT(R$1,6)))计算后会返回一串由TRUE和FALSE组成的数组,但是这些数组无法直接运算,前面加上--,就可以将这一串数组是TRUE的变成1,是FALSE的变成0,便于外面嵌套的SUM计算。
类似的还有多种相似的方法,1*,-0,+0,/1都是可以的,道理一样。
=SIN(PI()*(LEFT(A1,LEN(A1)-4)+MID(A1,LEN(A1)-3,2)/60+RIGHT(A1,2)/3600)/180)这个公式是怎么来地?
最外面一层括号是正弦函数sin函数
PI()就是圆周率
LEFT(A1,LEN(A1)-4)就是取A1单元格从最左边第一个字符开始,共取(A1的字符串长度-4)个字符;
MID(A1,LEN(A1)-3,2)就是取A1单元格从第(A1的字符串长度-3)个字符开始,共取两个字符;
RIGHT(A1,2)就是取A1单元格最后两个字符
D2=INDEX($A$2:
$C$33,MATCH(LARGE((MID($A$2:
$A$33,5,1)="1")*$C$2:
$C$33-ROW($A$2:
$A$33)/100,ROW(A1)),$
函数意思是
在A2:
C33里面返回一个单元格这个单元格行数是MATCH(LARGE((MID($A$2:
$A$33,5,1)="1")*$C$2:
$C$33-ROW($A$2:
$A$33)/100,ROW(A1)),$C$2:
$C$33-ROW($A$2:
$A$33)/100,0)
列数被忽略
MATCH(LARGE((MID($A$2:
$A$33,5,1)="1")*$C$2:
$C$33-ROW($A$2:
$A$33)/100,ROW(A1)),$C$2:
$C$33-ROW($A$2:
$A$33)/100,0)
意识是最外面一层是MATCH函数
LARGE((MID($A$2:
$A$33,5,1)="1")*$C$2:
$C$33-ROW($A$2:
$A$33)/100,ROW(A1))是要查找的值
$C$2:
$C$33-ROW($A$2:
$A$33)/100是查找范围(查找范围很好理解不详细讲了就说查找的值)
0是精确匹配意思.
LARGE((MID($A$2:
$A$33,5,1)="1")*$C$2:
$C$33-ROW($A$2:
$A$33)/100,ROW(A1))
意识在MID($A$2:
$A$33,5,1)="1")*$C$2:
$C$33-ROW($A$2:
$A$33)/100这里面找出最大的一个值ROW(A1)返回1配合LARGER就是返回第一个最大的值
MID($A$2:
$A$33,5,1)="1")*$C$2:
$C$33-ROW($A$2:
$A$33)/100
这个意思是a2:
a33里面的值满足第5个是1(MID函数判断)的情况下返回C2:
C33对应的值减去当前行数除以100后的值
IF(MOD(IF(LEN(M6)=15,MID(M6,15,1),MID(M6,17,1)),2)=1,"男","女"),详细解答一下这个语句的意思?
如果M6单元格中的字符串长度是15,就从15位置截取一个字符,否则就从17处截取一个字符。
截取的字符与2取模,如果是奇数给单元格显示男,否则显示女。
在后一列输入=IF(LEN(A4)=17,MID(A4,1,13),)注意A4是前一列单元格所在的位置。
你是意思是从身份证中提取出生年月,对吧!
年月日的部份是if判定的否定结果,需增加一个(),如下公式便可达成你的需求,但前提是函数的存储格格式设定为日期!
=IF(LEN(E4)=17,5,(DATE(MID(E4,7,4),MID(E4,11,2),MID(E4,13,2))))
请问下EXCEL里我写的这个公式有什么问题=IF(LEN(E4)=17,5,DATE(MID(E4,7,4),MID(E4,11,2),MID(E4,13,2)))
你是意思是从身份证中提取出生年月,对吧!
年月日的部份是if判定的否定结果,需增加一个(),如下公式便可达成你的需求,但前提是函数的存储格格式设定为日期!
=IF(LEN(E4)=17,5,(DATE(MID(E4,7,4),MID(E4,11,2),MID(E4,13,2))))
您的公式是不是这样解释:
在E4中有一个字符串,比如身份证号码,可能是18位的,如果检测是17为,就返回数字5,如果检测17位,就返回一个日期,比如1996-09-26。
当然,必须设定这个单元格是日期格式。
假如是15位的老身份证号码,这个公式就需要修改一下,因为老身份证的年度不是四位数,二是两位数,所以要将DATE(MID(E4,7,4),MID(E4,11,2),MID(E4,13,2))修改为DATE(MID(E4,7,2),MID(E4,9,2),MID(E4,11,2))
INDEX($3:
$3,RIGHT(LARGE($B$2:
$K$2*100+COLUMN($B:
$K),ROW(A1)),2))这个数组公式是什么意思
公式用来返回B2:
K2的数据降序排列后,从大到小提取对应的第三行数据.
建议你点击公式所在单元格,然后点击菜单中的"工具","公式审核","公式求值","求值",看excel是怎样一步一步运行的,对你会大有启发.
=INDIRECT("sheet2!
"&ADDRESS(ROW(A1),(COLUMN(A1)-1)*3+1))的意思?
=INDIRECT("sheet2!
"&ADDRESS(ROW(A1),(COLUMN(A1)-1)*3+1))
先讲最后一部分的:
ADDRESS(ROW(A1),(COLUMN(A1)-1)*3+1)
row(A1)=1,取得A1单元格的行号
column(A1),取得A1单元格的列号.
(COLUMN(A1)-1)*3+1,这个是以3的倍数递增.即数列:
1,4,7,10,……
所以ADDRESS(ROW(A1),(COLUMN(A1)-1)*3+1)变成ADDRESS(1,(1-1)*3+1)=$A$1
ADDRESS函数的意思是:
按照给定的行号和列标,建立文本类型的单元格地址。
相见excel帮助.
"sheet2!
"&ADDRESS(ROW(A1),(COLUMN(A1)-1)*3+1))就变成("sheet2!
"&$A$1)=sheet2!
$A$1.
INDIRECT函数的意思是:
返回由文本字符串指定的引用。
即引用单元格的内容.
=INDIRECT("sheet2!
"&ADDRESS(ROW(A1),(COLUMN(A1)-1)*3+1))
的意思就是返回sheet2表中A1单元格的值.
1、ROW(A1):
返加A1所在的行,1
2、(COLUMN(A1)-1)*3+1):
其中COLUMN(A1)返回A1所成的列,1。
然后(1-1)*3+1为1
3、ADDRESS(1,1):
第一个参数为行,第二个参数为列,还可以有第三个参数,没有缺省为绝对引用。
返回该行和列的单元格地址,$A$1
4、&连接字符将"sheet2!
"和以上的计算结果连接起来:
"sheet2!
$A$1"
5、INDIRECT:
返回由文本字符串指定的引用,因为在4中"sheet2!
$A$1"为字符串,通过此函数返回该引用。
是工作表2里面的操作一个数乘以3再加1
求excel中身份证提取年龄公式详解
=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"00-00-00"),TODAY(),"y")
这个公式中的*号是什么意思?
*2是什么意思?
+号是什么意思?
关于LZ公式的详解
(首先应说明:
逻辑运算参与计算,True=1,False=0,*即乘号,+即加号)
(LEN(A1)=15)*19如果A1中15位,1*19=19,否则为0*19=0
此部分为在15位号码前加上19,但如果是2000年出生,则为错误结果(此部分就交电脑判断,不宜画蛇添足,修正公式在后)
MID(A1,7,6+(LEN(A1)=18)*2)从号码的第7位起取字,15位号码取6(6+0*2),18位取8个(6+1*2=6+2=8)
TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"00-00-00")
将取出的字用格式显示,即加上日期分隔符-
Datedif函数为用来计算日期间隔,"y"指计算周期为年
此公式应简化为:
=DATEDIF(TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"00-00-00"),TODAY(),"y")
比如你试一个08年的身份证号
370201*********X
这是根据身份证号码(15位和18位通用)自动提取性别和出生年月的自编公式:
说明:
公式中的B2是身份证号
1、根据身份证号码求性别:
=IF(LEN(B2)=15,IF(MOD(VALUE(RIGHT(B2,3)),2)=0,"女","男"),IF(LEN(B2)=18,IF(MOD(VALUE(MID(B2,15,3)),2)=0,"女","男"),"身份证错"))
2、根据身份证号码求出生年月日:
=IF(LEN(B2)=15,CONCATENATE("19",MID(B2,7,2),".",MID(B2,9,2)),IF(LEN(B2)=18,CONCATENATE(MID(B2,7,4),".",MID(B2,11,2)),"身份证错"))
3、根据身份证号码求年龄:
=IF(LEN(B2)=15,year(now())-1900-VALUE(MID(B2,7,2)),if(LEN(B2)=18,year(now())-VALUE(MID(B2,7,4)),"身份证错"))
其他通用公式:
(新旧身份证号通用)
(1)、根据“身份证号”获得“年龄”
A1为身份证号,
B1中输入公式
=DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y")
(2)、根据出生“年-月-日”获得“年龄”
B1为出生日期,
C1中输入公式
=INT((TODAY()-B1)/365)
或者
=DATEDIF(B1,TODAY(),"Y")
判断身份证格式是否正确:
B1为身份证号,
C1中输入公式
=IF(OR(LEN(B1)=18,LEN(B1)=15),IF(LEN(B1)=18,IF(OR(MONTH(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1*(MID(B1,11,2)),DAY(DATE(1*(MID(B1,7,4)),1*(MID(B1,11,2)),1*(MID(B1,13,2))))<>1*(MID(B1,13,2))),"月日错误",""),IF(LEN(B1)=15,IF(OR(MONTH(DATE(1*(MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,9,2)),DAY(DATE(1*(MID(B1,7,2))+1900,1*(MID(B1,9,2)),1*(MID(B1,11,2))))<>1*(MID(B1,11,2))),"月日错误",""))),"身份证位数不对")&IF(AND(LEN(B1)=18,1*MID(B1,7,4)>YEAR(TODAY())-6),"年份错误","")
假设C2为身份证号,输入公式
=IF(LEN(C2)=15,19&MID(C2,7,6),MID(C2,7,8))
这是获得出生日期
=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")
这是区分性别
dateif函数是EXCEL隐藏函数具体用法网上很好找到!
这里不解释
TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"00-00-00")
TEXT函数也很好理解也不解释就说
(LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2)首先讲述一下身份证
18位的身份证号码跟15位身份证号码的区别在于出生年份上多了2位,末尾多了一位数字。
比如你的生日是1980/08/08
那你的旧身份证号码因该是XXXXXX800808XXX
新号码就是XXXXXX19800808XXXX
这个函数的意思是身份证长15的时候呢TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2)计算的时候就是
1*19&MID(A1,7,6+(0)*2
这时候公式里面的LEN(A1)=15结果就是1(LEN(A1)=18)结果就是0EXCELTRUEFALSE分别是10来表示的
刚才说了身份证了15位只要走第7位开始取6个数字就好了但是象89年08月08日这样的要认为的+19变成1989/08/08然后再DATEIF计算
18位么就也一样意思走7位开始取8个长度的数字因为他本来就带年份的所以0*19就是06+(LEN(A1)=18)*2正好是8个长度
其实*就是乘号主要是看EXCEL里面TRUEFALSE返回值参与运算
2000出身后这个公式失效~
在excel中,身份证号码提取年龄大于18岁的人,用什么公式
在后面添加一列,标题为年龄:
输入公式:
=(TODAY()-DATE(MID(C11,7,4),MID(C11,11,2),MID(C11,13,2)))
再设置单元格格式,自定义格式:
输入YY,确定
再做高级筛选,条件:
年龄
>18
excel中根据身份证号码计算年龄(x岁x月x天)的公式
计算年龄(x岁x月),=DATEDIF(IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2))),TODAY(),"Y")&"岁"&DATEDIF(IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2))),TODAY(),"m")-12*DATEDIF(IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2))),TODAY(),"Y")&"月"这个公式是否正确,是否考虑到闰年的情况?
若不正确,请高手提供正确的公式!
相应的,计算年龄(x岁x月x天)的公式又该是怎样的呢?
:
=DATEDIF(TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00"),today(),"Y")&"岁"&DATEDIF(TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00"),today(),"YM")&"月"&DATEDIF(TEXT(MID(A1,7,LEN(A1)/2-1),"#-00-00"),today(),"MD")&"天"
你的公式思路没错,但缺了天数,我给补上
=DATEDIF(IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2))),TODAY(),"Y")&"岁"&DATEDIF(IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2))),TODAY(),"YM")&"月"&DATEDIF(IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2))),TODAY(),"MD")&"天"
另外,不必“-12*Y”那样计算,用“YM”表示不计年月数,“MD”表示不计月天数。
=IF(D2="","",DATEDIF(TEXT((LEN(D2)=15)*19&MID(D2,7,6+(LEN(D2)=18)*2),"#-00-00"),TODAY(),"y"))
=YEAR(TODAY())-MID(身份证所在单元格数,7,4)
出生日期
A2=TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")
岁数:
A3=DATEDIF(A2,TODAY(),"Y")&"岁"&DATEDIF(A2,TODAY(),"YM")&"月"&DATEDIF(A2,TODAY(),"MD")&"天"
=NOT(AND(OR(LEN($B2)=15,LEN($B2)=18),ISNUMBER(--LEFT($B2,17))))
--负负得正通过这个符号可以把数值型文本转换为数值,然后可以参与其他运算
在你的这个公式中,其实用一个"-"也可以
ISNUMBER(-LEFT($B2,17))
ISNUMBER(1*LEFT($B2,17))
都可以,数值型的数字通过一次运算就可以直接变成数字的
将LEFT($B2,17)的结果转换成数值格式.
假设B2为身份证号,身份证号必须存成文本形式才能显示的,所以LEFT($B2,17)就是一个文本值.
--就是负负的正的意思.
=(LEN($A9)=15)+(LEN($A9)=18)*ISNUMBER(--LEFT($A9,17))-1请问高手帮我解释下这个函数从前到后的含义
LEFT($A9,17),取A9单元格前17位数据。
由于LEFT取出的是文本格式,所以前面加上“--”,将结果转为数值格式。
ISNUMBER(--LEFT($A9,17)),判断LEFT取得的数据是不是数值。
(LEN($A9)=15),判断A9是不是15位,如果是,返回TRUE,否则返回FALSE。
(这两个逻辑值参与四则计算时,分别相当于1和0)
(LEN($A9)=18),(同上,判断A9是不是18位)
(LEN($A9)=18)*ISNUMBER(--LEFT($A9,17)),两个判断结果相乘,即是两个条件同时成立时,后回1,否则返回0
最后就是0和1相加减。
整个公式就是A9单元格字符长度是15个,或者是18位且前17位是数字的话,返回0;否则返回-1。
excel两组数据中有没有没相同的数的工式
比如A1是123B1是145用什么公式可以得到,B1和A1里有没有相同的数,有的话用1表示,没有就用空,
第二个就是A1是1B1是2C1是3B1是1B2是4B3是5用什么公式可以得到,B1B2B3和A1A2A3里有没有相同的数,有的话用1表示,没有就用空,
我用=IF(A1=B1,B1,"")公工只能的到一个数不能三个数一起,高手们帮帮小弟,用什么公式可以答到上面的要求!
!
弟在这先谢了
=IF(AND(ISERROR(MATCH(MID(A1,ROW(INDIRECT("1:
"&LEN(A1))),1),MID(B1,ROW(INDIRECT("1:
"&LEN(B1))),1),0))),"",1)
=IF(SUMPRODUCT(COUNTIF(A1:
A3,B1:
B3)),1,"")
都是三键结束CTRL+SHIFT+ENTER
假设两组数,一组在sheet1的A列,另一组在sheet2的A列,要比较sheet2的A列数据是否与sheet1的A列数据相同,则可在sheet2的B1中写入公式:
=if(countif(sheet1!
a:
a,a1),"相同","")
下拉复制公式。
公式结果为“相同”的,说明对应的A列数据与sheet1的A列数据中某个数据相同。
你可以根据自己的实际情况修改公式。
----------------------------------
针对补充问题1:
公式为:
=IF(COUNT(FIND(MID(A1,ROW(INDIRECT("1:
"&LEN(A1))),1),B1))>0,1,"")
上式为数组公式,需按ctrl+shift+enter三键结束。
测试通过!
补充问题2:
没看懂,不过应该跟第一问类似。
求Excel函数公式:
五月桂圆9640.35540.85639.5
六月木耳12312.56313.54214.5
六月虾米8729.54726.94628.5
六月香菇8733.76624.15833.7
六月桂圆9841.28741.94440.5
原始图
二月桂圆10640.65237.2¥1,934.45138.5
三月木耳10914.66412.9¥825.63813.9
三月虾米11728.54126.5¥1,086.53928.7
三月香菇
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 实例 疑难 解析
![提示](https://static.bdocx.com/images/bang_tan.gif)