设为首页收藏本站
开启辅助访问

Excel精英培训网

 找回密码
 注册
Excel精英培训网»论坛 › Excel 培训班 › Excel 函数初级班 › 【201502函初】练习五 提交贴(已开贴)
数据透视表40+个常用小技巧，让你一次学会！
123下一页
返回列表 发新帖
查看: 3697|回复: 22

[习题] 【201502函初】练习五 提交贴(已开贴)

[复制链接]
发表于 2015-10-26 21:42 | 显示全部楼层 |阅读模式
工作中常用的Excel函数公式，全印在一张超大鼠标垫上
本帖最后由 xk465592765 于 2015-11-7 22:48 编辑

1、学号
2、只要公式,不要附件
3、修改帖子在原楼
4、满分+10  +10
5、提交时间截止时间2015年11月1日 19:00前
回复

使用道具 举报

工作中最常用的函数公式，全印在一张超大的鼠标垫上
 楼主| 发表于 2015-10-26 21:42 | 显示全部楼层
工作中常用的Excel函数公式，全印在一张超大鼠标垫上
本帖最后由 xk465592765 于 2015-11-7 22:47 编辑

开贴后参考其他同学的！！！

汇总如下
  1. 第一题
  2. ①=IFERROR(VLOOKUP(LEFT(B$$$$3,6),表1!A:B,2,0),"无此人")
  3. =IF(LEN(B3)=18,VLOOKUP(LEFT(B3,6),表1!A:B,2,0),"无此人")
  4. ②=IF(B4="无此人","无此人",DATE(MID(B3,7,4),MID(B3,11,2),MID(B3,13,2)))
  5. =IF(LEN(B3)=18,TEXT(MID(B3,7,8),"0-00-00"),"无此人")
  6. ③=IF(B4="无此人","无此人",IF(ISODD(MID(B3,17,1)),"男","女"))
  7. =IF(B4="无此人","无此人",TEXT((-1)^MID(B3,9,9),"[>0]女;[<0]男"))
  8. =IF(LEN(B3)=18,TEXT(-1^MID(B3,17,1),"女;男"),"无此人")
  9. =IFERROR(IF(ISODD(MID(B3,17,1)),"男","女"),"无此人")
  10. =IFERROR(IF(MOD(MID(B3,17,1),2)=1,"男","女"),"无此人")
  11. ④=IF(B4="无此人","无此人",DATEDIF(B5,TODAY(),"y"))
  12. =IF(LEN(B3)=18,DATEDIF(TEXT(MID(B3,7,8),"0-00-00"),TODAY(),"y"),"无此人")
  13. =IFERROR(DATEDIF(--B5,NOW(),"y"),"无此人")
  14. ⑤=IF(B4="无此人","无此人",CHOOSE(MOD(MID(B3,9,2),12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪"))
  15. =IF(B4="无此人","无此人",MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(B5)-4,12)+1,1))
  16. =IF(LEN(B3)=18,MID("鸡狗猪鼠牛虎兔龙蛇马羊猴",MOD(MID(B3,7,4),12),1),"无此人")
  17. =IFERROR(MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD((--(LEFT(B5,4)))-4,12)+1,1),"无此人")
  18. =IFERROR(CHOOSE(MOD(YEAR(B5)-4,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪"),"无此人")
  19. ⑥=IF(LEN(B3)=18,DATEDIF(TODAY(),IF(TEXT(B5,"mm-dd")>=TEXT(TODAY(),"mm-dd"),2015,2016)&TEXT(B5,"-m-d"),"d"),"无此人")
  20. =IFERROR(365-DATEDIF(--B5,NOW(),"YD"),"无此人")
  21. 第二题
  22. =SUM(IFERROR(--LEN(表1!B2:B3528),0))
  23. =SUM(IF(ISTEXT(表1!B2:B3528),LEN(表1!B2:B3528),))
  24. 第三题
  25. =INT((ROW(A1)-1)*9.9+1)&"-"&ROW(A1:$$$$A$$$$10)*10
  26. =TEXT((ROW(A1)-1)*10,"[=0]1")&"-"&TEXT(ROW(A1)*10,"[>100]1!0!0")
  27. =MAX((ROW(A1)-1)*10,1)&"-"&MIN((ROW(A1)*10),100)
  28. =MAX(1,ROW(A1)*10-10)&"-"&MIN(ROW(A1)*10,100)
  29. =ROUNDUP(ABS((ROW(A1)-1)*10-0.1),0)&"-"&IF(ROW(A1)*10>=100,100,ROW(A1)*10)
  30. =MEDIAN(0.1,10,ROW(A1)-1)*10&"-"&MEDIAN(1,100,ROW(A1)*10)
  31. =MAX(MIN(ROW(A1)-1,10),0.1)*10&"-"&MAX(MIN(ROW(A1),10),1)*10
  32. =CONCATENATE(MAX((ROW(A1)-1)*10,1),"-",MIN(ROW(A1)*10,100))
  33. 第四题
  34. =MID(A27,SEARCH("-",A27)+1,LEN(A27))
  35. =MID(A27,FIND("-",A27)+1,99)
  36. =--RIGHT(A27,FIND("-",A27)-1)
  37. =--MID(SUBSTITUTE(A27,"-",REPT(" ",99)),100,99)
  38. =TRIM(RIGHT(SUBSTITUTE(A27,"-",REPT(" ",99)),99))
  39. =REPLACE(A27,1,FIND("-",A27),)
  40. =ABS(RIGHT(A27,3))
  41. =ABS(MID(A27,4,9))
  42. 第五题
  43. =SUM(IFERROR(表1!A2:A3528*1,0))
  44. =SUM(IF(ISNUMBER(--表1!A2:A3528),--表1!A2:A3528))
复制代码
回复

使用道具 举报

发表于 2015-10-27 20:26 | 显示全部楼层
工作中最常用的Excel函数公式，全印在一张鼠标垫上
[201502函初] A03
                 第一题：=IFERROR(VLOOKUP(LEFT(B$3,6),表1!A:B,2,0),"无此人")
=IF(B4="无此人","无此人",DATE(MID(B3,7,4),MID(B3,11,2),MID(B3,13,2)))
=IF(B4="无此人","无此人",IF(ISODD(MID(B3,17,1)),"男","女"))
=IF(B4="无此人","无此人",DATEDIF(B5,TODAY(),"y"))
=IF(B4="无此人","无此人",CHOOSE(MOD(MID(B3,9,2),12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪"))
=IF(B4="无此人","无此人",DATEDIF(TODAY(),DATE(YEAR(TODAY())+1,MONTH(B5),DAY(B5)),"d"))

第二题：=SUM(IFERROR(--LEN(表1!B2:B3528),0)) 数组公式

第三题：=INT((ROW(A1)-1)*9.9+1)&"-"&ROW(A1:$A$10)*10  下拉

第四题：=MID(A27,SEARCH("-",A27)+1,LEN(A27))  下拉

第五题：=SUM(IFERROR(表1!A2:A3528*1,0))数组公式


点评

橘子红
第三题好强!学习了  发表于 2015-10-29 02:42

评分

参与人数 1 +10 金币 +10 收起 理由
橘子红 + 10 + 10 赞一个!

查看全部评分

回复

使用道具 举报

皮皮2
发表于 2015-10-28 10:07 | 显示全部楼层
本帖最后由 皮皮2 于 2015-10-29 10:11 编辑

C06：皮皮2

1.1，431230199910111111

1.2
  1. =VLOOKUP(MID(B3,1,6),表1!A:B,2,)
复制代码
1.3
  1. =--TEXT(MID(B3,7,8),"0-00-00")
复制代码
1.4
  1. =TEXT((-1)^MID(B3,9,9),"[>0]女;[<0]男")
复制代码
1.5
  1. =YEAR(TODAY())-YEAR(B5)
复制代码
1.6
  1. =MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(YEAR(B5)-4,12)+1,1)
复制代码
4.
  1. =MID(A27,FIND("-",A27)+1,99)
复制代码
5.
  1. =SUM(--表1!A3528:表1!A2)
复制代码
1.5

点评

橘子红
做个记号,第五题错误  发表于 2015-10-29 02:47

评分

参与人数 1 +5 金币 +5 收起 理由
xk465592765 + 5 + 5 神马都是浮云

查看全部评分

回复

使用道具 举报

发表于 2015-10-28 11:43 | 显示全部楼层
  1. B03:珺珺妈交练习，星空大师辛苦啦。
  2. 1.=IF(LEN(B3)=18,VLOOKUP(LEFT(B3,6),表1!A:B,2,0),"无此人")
  3. =IF(LEN(B3)=18,TEXT(MID(B3,7,8),"0-00-00"),"无此人")
  4. =IF(LEN(B3)=18,TEXT(-1^MID(B3,17,1),"女;男"),"无此人")
  5. =IF(LEN(B3)=18,DATEDIF(TEXT(MID(B3,7,8),"0-00-00"),TODAY(),"y"),"无此人")
  6. =IF(LEN(B3)=18,MID("鸡狗猪鼠牛虎兔龙蛇马羊猴",MOD(MID(B3,7,4),12),1),"无此人")
  7. =IF(LEN(B3)=18,DATEDIF(TODAY(),IF(TEXT(B5,"mm-dd")>=TEXT(TODAY(),"mm-dd"),2015,2016)&TEXT(B5,"-m-d"),"d"),"无此人")
  8. 2.=LEN(PHONETIC(表1!B2:B4000))
  9. 3.=TEXT((ROW(A1)-1)*10,"[=0]1")&"-"&TEXT(ROW(A1)*10,"[>100]1!0!0")
  10. 4.=MID(A27,FIND("-",A27)+1,9)
  11. 5.=SUM(IF(ISNUMBER(--表1!A2:A3528),--表1!A2:A3528))
复制代码

点评

橘子红
做个记号. 第二题 phoneic连接倒3392行时有问题  发表于 2015-10-29 03:07

评分

参与人数 1 +9 金币 +9 收起 理由
xk465592765 + 9 + 9 很给力!

查看全部评分

回复

使用道具 举报

发表于 2015-10-28 15:05 | 显示全部楼层
本帖最后由 thelastdance 于 2015-10-31 07:50 编辑

B组长
第一题
  1. =IFERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,1),"无此人")
复制代码
  1. =IFERROR(IF(ISODD(MID(B3,17,1)),"男","女"),"无此人")
复制代码
  1. =IFERROR(YEAR(TODAY())-(MID(B3,7,4)),"无此人")
复制代码
  1. =IFERROR(MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD((--(LEFT(B5,4)))-4,12)+1,1),"无此人")
复制代码
第二题
  1. =SUM(IFERROR(LEN(表1!B2:B3528),0))
复制代码
第三题
  1. =MAX((ROW(A1)-1)*10,1)&"-"&MIN((ROW(A1)*10),100)
复制代码
第四题
  1. =--RIGHT(A27,FIND("-",A27)-1)
复制代码
第五题
  1. =SUM(IFERROR(--表1!A2:A3528,0))
复制代码

评分

参与人数 1 +7 金币 +7 收起 理由
xk465592765 + 7 + 7 很给力!

查看全部评分

回复

使用道具 举报

fjmxwrs
发表于 2015-10-28 21:10 | 显示全部楼层
本帖最后由 fjmxwrs 于 2015-10-28 21:16 编辑

C11：fjmxwrs
  1. =IF(OR(LEN(B3)<>18,--MID(B3,7,4)>YEAR(TODAY()),--MID(B3,11,2)>12,--MID(B3,13,2)>31,AND(--MID(B3,11,2)=2,--MID(B3,13,2)>28),ISERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,0))),"无此人",VLOOKUP(LEFT(B3,6),表1!A:B,2,0))
复制代码
  1. =IF(OR(LEN(B3)<>18,--MID(B3,7,4)>YEAR(TODAY()),--MID(B3,11,2)>12,--MID(B3,13,2)>31,AND(--MID(B3,11,2)=2,--MID(B3,13,2)>28),ISERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,0))),"无此人",TEXT(MID(B3,7,8),"0000-00-00"))
复制代码
  1. =IF(OR(LEN(B3)<>18,--MID(B3,7,4)>YEAR(TODAY()),--MID(B3,11,2)>12,--MID(B3,13,2)>31,AND(--MID(B3,11,2)=2,--MID(B3,13,2)>28),ISERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,0))),"无此人",IF(MOD(MID(B3,17,1),2),"男","女"))
复制代码
  1. =IF(OR(LEN(B3)<>18,--MID(B3,7,4)>YEAR(TODAY()),--MID(B3,11,2)>12,--MID(B3,13,2)>31,AND(--MID(B3,11,2)=2,--MID(B3,13,2)>28),ISERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,0))),"无此人",DATEDIF(TEXT(MID(B3,7,8),"0000-00-00"),TODAY(),"y"))
复制代码
  1. =IF(OR(LEN(B3)<>18,--MID(B3,7,4)>YEAR(TODAY()),--MID(B3,11,2)>12,--MID(B3,13,2)>31,AND(--MID(B3,11,2)=2,--MID(B3,13,2)>28),ISERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,0))),"无此人",CHOOSE(MOD(MID(B3,7,4),12),"鸡","狗","猪","鼠","牛","虎","兔","龙","蛇","马","羊","猴"))
复制代码
  1. =IF(OR(LEN(B3)<>18,--MID(B3,7,4)>YEAR(TODAY()),--MID(B3,11,2)>12,--MID(B3,13,2)>31,AND(--MID(B3,11,2)=2,--MID(B3,13,2)>28),ISERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,0))),"无此人",DATEDIF(TODAY(),DATE(YEAR(TODAY())+1,MID(B3,11,2),MID(B3,13,2)),"d"))
复制代码
  1. =LEN(PHONETIC(表1!B:B))-2
复制代码
  1. =MAX(1,ROW(A1)*10-10)&"-"&MIN(ROW(A1)*10,100)
复制代码
  1. =MID(A27,FIND("-",A27)+1,LEN(A27))
复制代码
  1. =SUM(VALUE(表1!A2:A4000))
复制代码

评分

参与人数 1 +6 金币 +6 收起 理由
xk465592765 + 6 + 6 很给力!

查看全部评分

回复

使用道具 举报

o豆苗
发表于 2015-10-29 12:37 | 显示全部楼层
本帖最后由 o豆苗 于 2015-10-31 18:40 编辑

B09:o豆苗
第一题
  1. '310101195602063452
复制代码
  1. =IFERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,),"无此人")
复制代码
  1. =TEXT(MID(B3,7,8),"0-00-00")
复制代码
  1. =IF(ISODD(MID(B3,17,1)),"男","女")
复制代码
  1. =DATEDIF(TEXT(MID(B3,7,8),"0-00-00"),TODAY(),"y")
复制代码
  1. =MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(YEAR(B5),12)+1,1)
复制代码
  1. =IF(TEXT(TEXT(MID(B3,7,8),"0-00-00"),"m/d")-TEXT(TODAY(),"m/d")>=0,DATEDIF(TODAY(),DATE(2015,MONTH(B5),DAY(B5)),"d")&"天",DATEDIF(TODAY(),DATE(2016,MONTH(B5),DAY(B5)),"d")&"天")
复制代码
第二题
  1. =SUM(IFERROR(LEN(表1!B2:B3528),0))
复制代码
第三题
  1. =TEXT(10*ROW(A1)-10,"0;;1")&"-"&TEXT(10*ROW(A1),"[>100]1!0!0")
复制代码
第四题
  1. =RIGHT(A27,LEN(A27)-FIND("-",A27))
复制代码
第五题
  1. =SUM(IFERROR(N(--表1!A2:A3528),0))
复制代码

评分

参与人数 1 +8 金币 +8 收起 理由
xk465592765 + 8 + 8 很给力!

查看全部评分

回复

使用道具 举报

发表于 2015-10-29 19:32 | 显示全部楼层
B13:红旗雪
第一题，发证地
  1. =IFERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,),"无此人")
复制代码
出生日期
  1. =TEXT(MID(B3,7,8),"0年00月00日;;;无此人")
复制代码
性别
  1. =IFERROR(IF(MOD(MID(B3,17,1),2)=1,"男","女"),"无此人")
复制代码
年龄
  1. =IFERROR(DATEDIF(--B5,NOW(),"y"),"无此人")
复制代码
生肖
  1. =IFERROR(CHOOSE(MOD(YEAR(B5)-4,12)+1,"鼠","牛","虎","兔","龙","蛇","马","羊","猴","鸡","狗","猪"),"无此人")
复制代码
下一次生日
  1. =IFERROR(365-DATEDIF(--B5,NOW(),"YD"),"无此人")
复制代码
第二题，数组公式
  1. =SUM(IF(ISTEXT(表1!B2:B3528),LEN(表1!B2:B3528),))
复制代码
第三题
  1. =ROUNDUP(ABS((ROW(A1)-1)*10-0.1),0)&"-"&IF(ROW(A1)*10>=100,100,ROW(A1)*10)
复制代码
第四题
  1. =--MID(A27,FIND("-",A27)+1,5)
复制代码
第五题，数组公式
  1. =SUM(IF(ISNUMBER(表1!A2:A3528*1),表1!A2:A3528*1,))
复制代码

评分

参与人数 1 +10 金币 +10 收起 理由
xk465592765 + 10 + 10 神马都是浮云

查看全部评分

回复

使用道具 举报

发表于 2015-10-29 20:34 | 显示全部楼层
本帖最后由 小合和 于 2015-11-1 13:52 编辑

[201502函初]练习五提交贴
B08:小合和作业
第一题身份证号码：先把B3格式设为文本，然后输入18位数字。
         发证地： =IFERROR(VLOOKUP(LEFT(B3,6),表1!A:B,2,0),"无此人")
         出生日期： =TEXT(--MID(B3,7,8),"0年00月00日")
         性别： =IF(MOD(MID(B3,17,1),2),"男","女")
         年龄：=DATEDIF(TEXT(--MID(B3,7,8),"0000-00-00"),TODAY(),"Y")
         生肖：=LOOKUP(MOD(--MID(B3,7,4),12),H3:I11)
         下一次生日： =TEXT(--MID(B3,11,4),"00-00")-TODAY()
第二题公式： ={SUM(IFERROR(LEN(表1!B2:B3528),0))}
第三题公式： =MAX(10*ROW(A1)-10,1)&"-"&MIN(10*ROW(A1),100)
第四题公式：=MID(A27,FIND("-",A27)+1,5)
第五题公式： ={COUNT(--表1!A2:A3528)}
         

评分

参与人数 1 +7 金币 +7 收起 理由
xk465592765 + 7 + 7 很给力!

查看全部评分

回复

使用道具 举报

下一页 »
123下一页
返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-5-16 13:32 , Processed in 0.389584 second(s), 18 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表