Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
12
返回列表 发新帖
楼主: bynbyn

[习题] (结束)【函中一班】第五讲作业A组上交处

  [复制链接]
发表于 2012-4-21 23:52 | 显示全部楼层
A08传递的函数中级班第五讲文本函数作业
第一题
出生日期
出生日期偷懒解法      
身份证有15位18位,开始位置都一样是第7位,但范围不同,15位的提取6位,18位的提取8位      
MID(A4,7,6+(LEN(A4)=18)*2) 中直接按6+2法提取,当len值不是18时,返回false,=6+false*2 直接返回16      
当数据源是18位时,=6+true*2 相当于 6+2 =8        
数据源中存在月份大于12的数据,所以加了判断,数据错误      
最终公式:
  1. =IFERROR(--TEXT(MID(A4,7,6+(LEN(A4)=18)*2),"0-00-00"),"数据错误")      
复制代码
性别公式: 偷懒减少判断15、18位         
直接根据文本函数特性.         
1.位数不足的话,只取到最后         
2.其中十五位身份证最后一位数字为性别位;十八位身份证倒数第二位数字为性别位。         
那将可以直接取递5位:MID(A4,15,3),2),并且连续取3位,当身份证是15位时,直接取15位后三位没有,依旧取的是第15位。         
十八位身份证倒数第二位数字为性别位,相当于第17位! {15;16;17 }组,于是MID(A4,15,3) 取3位后刚好是17位         
         
练手操作         
SUBSTITUTE 公式【=IF(A4="","",SUBSTITUTE((SUBSTITUTE(MOD(MID(A4,15,3),2),1,"男")),0,"女"))】中if只是判断有没有空行        
公式思路是直接把余数【1】替换成【男】,把【0】 替换成【女】,不用if判断
  1. =CHOOSE =CHOOSE(MOD(MID(A4,15,3),2)+1,"女","男")  
复制代码
  1. =LOOKUP  =LOOKUP(MOD(MID(A4,15,3),2),{0,"女";1,"男"})   
复制代码
  1. =ndex =INDEX({"女";"男"},MOD(MID(A4,15,3),2)+1)
复制代码
  1. =text =TEXT(MOD(MID(A4,15,3),2),"男;;女")   
复制代码
第二题
   
格式1 公式1 =TEXT(B12,"公元yyyy年mm月dd日")
公式2  =TEXT(B12,"公元yyyy年mm月dd日")
格式二  =TEXT(B12,"dd-mm-yyyy")
格式三  =TEXT(B12,"yyyy年mm月dd日 aaaa")
        =TEXT(B12,"e年mm月dd日 aaaa")
      
第三题
接替思路     
1 替换第三条横线和第四条横线     
wp-103-24传99递19  =SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3)   
      
2 找到“传”位置(mid,第二参数,开始位置)     
10 公式 =FIND("传",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))   
      
3 找到“递”位置(mid,第三参数,结束位置位置)     
13 公式 =FIND("递",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))   
      
4 2者间范围     
2 公式 =FIND("递",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-FIND("传",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-1   
      
完成公式 99  =FIND("递",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-FIND("传",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-1   
转成数数值格式 99 公式 =--MID(A8,FIND("传",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))+1,FIND("递",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-FIND("传",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-1)   
      
      
简化公式 由于中间数据源是固定2位,区间范围的公式(公式可以中间不定位位数)     
直接简化【=FIND("递",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-FIND("传",SUBSTITUTE(SUBSTITUTE(A8,"-","递",4),"-","传",3))-1 】成2      
提取第三条横线,将数据源只要把数据源第三条横替换其他     
wp-103-24&99-19 公式=SUBSTITUTE(A8,"-","&",3)   
10 公式=FIND("&",SUBSTITUTE(A8,"-","&",3))   
最终简化公式1 99 =MID(A8,FIND("&",SUBSTITUTE(A8,"-","&",3))+1,2)   
      
公式2 99 =--MID(SUBSTITUTE(A8,"-",REPT(" ",99)),300,99)

第四题
               
公式1 =MID(SUM(RIGHT(LARGE(IF(ISNUMBER(MID(1&A10,ROW($1:$20),1)*1),MID(1&A10,ROW($1:$20),1)+ROW($1:$20)*100,0),ROW($1:$20)),1)*10^(ROW($1:$20)-1)),2,100)              
公式2 =LOOKUP(9E+307,--MID(A10,MATCH(,0/MID(A10,ROW($1:$99),1),),ROW($1:$99)))              
公式3 =-LOOKUP(,-MID(A10,MATCH(,0/MID(A10,ROW($1:$99),1),),ROW($1:$99)))              
公式4 =MAX(--TEXT(MID(A10,ROW($1:$99),COLUMN($A:$P)),"0;0;0;!0"))              
公式5 =MAX(--TEXT(MID(A10,ROW($99:$9999)/99,MOD(ROW($99:$9999),99)),"0;0;0;!0"))              
公式6 =MAX(IFERROR(MID(A10,ROW($1:$15),COLUMN(A:P))*1,0))              
公式7 =MAX((TEXT(MID(A10,ROW($1:$15),COLUMN(A:P)),"0;;0;!0")*1))              
公式8 =-LOOKUP(1,-RIGHT(LEFT(A10,MATCH(1,-MID(A10,ROW($1:$99),1))),ROW($1:$99)))              
公式9 =-LOOKUP(1,-MID(A10,MATCH(,0/MID(A10,ROW($1:$99),1),),ROW($1:$99)))              
公式10 =RIGHT(LEFT(A10,MATCH(1,-MID(A10,ROW($1:$99),1))),MATCH(1,-RIGHT(LEFT(A10,MATCH(1,-MID(A10,ROW($1:$99),1))),ROW($1:$99))))              
公式11 =MID(A10,MATCH(,0*(MID(A10,ROW($1:$99),1)&0),),COUNT(0*(MID(A10,ROW(INDIRECT("1:"&LEN(A10))),1)&0)))              
   
第五题
A8为例思路            
=LOOKUP(MOD(SUM((MID(A8,ROW($1:$17),1))*F26:F42),11),$F$7:$G$17)      
            
  把判断最后一位公式带入后判断真假,相关区域锁定 并注意文本和数字间转换         
   TRUE =--RIGHT(A8,1)=LOOKUP(MOD(SUM((MID(A8,ROW($1:$17),1))*$F$26:$F$42),11),$F$7:$G$17)         
            
   嵌套IF判断真假 最终把上面公式做IF函数条件用         
   真 ‘=IF(--RIGHT(A8,1)=LOOKUP(MOD(SUM((MID(A8,ROW($1:$17),1))*$F$26:$F$42),11),$F$7:$G$17),"真",假)         
            
   发现问题         
   当最后是X 时 转数字会错, 于是换思路 统一将最后值连上空【&""】强制为文本         
  最终公式为  =IF(RIGHT(A8,1)&""=(LOOKUP(MOD(SUM((MID(A8,ROW($1:$17),1))*$F$26:$F$42),11),$F$7:$G$17)&""),"真","假")         
    要是公式需要判断是否有空行,再加【IF(A8="",""】
公式2
=MID("真假",(RIGHT(A8)<>INDEX(G$7:G$17,MOD(SUM(MID(A8,ROW($1:$17),1)*D$7:D$23),11)+1)&"")+1,1)            
         
  

A08传递的函数中级班第五讲文本函数作业.rar

88.81 KB, 下载次数: 17

评分

参与人数 1 +10 金币 +10 收起 理由
無心 + 10 + 10 赞一个!

查看全部评分

excel精英培训的微信平台,每天都会发送excel学习教程和资料。扫一扫明天就可以收到新教程
回复

使用道具 举报

发表于 2012-4-22 15:24 | 显示全部楼层
A01寂寞深水鱼.rar (11.19 KB, 下载次数: 5)

点评

第一题是日期格式吗?第二题错,第五题未作  发表于 2012-4-23 00:47

评分

参与人数 1 +4 收起 理由
無心 + 4 继续努力啊

查看全部评分

回复

使用道具 举报

发表于 2012-4-22 15:28 | 显示全部楼层
作业做完了
交先

A16 hzqing.rar

12 KB, 下载次数: 5

点评

第一题小心坑,第二题注意细节,第三题错  发表于 2012-4-23 00:49

评分

参与人数 1 +6 收起 理由
無心 + 6 继续努力

查看全部评分

回复

使用道具 举报

发表于 2012-4-22 15:30 | 显示全部楼层
啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊啊

点评

作业不交,啊什么啊?  发表于 2012-4-23 00:30
回复

使用道具 举报

发表于 2012-4-23 17:26 | 显示全部楼层
最近的习题越来越难的,做的已经力不从心了,这次又有1题没做出来,看来还要持续持续的努力啊。辛苦学委了~

作业-函数中级班-第五讲-文本函数-A06醒着出场.zip

18.55 KB, 下载次数: 2

回复

使用道具 举报

发表于 2012-4-26 16:31 | 显示全部楼层
醒着出场 发表于 2012-4-23 17:26
最近的习题越来越难的,做的已经力不从心了,这次又有1题没做出来,看来还要持续持续的努力啊。辛苦学委了~

是不是作业交晚了,怎么都没给我批改啊?我看了下我是在截止时间前交的啊~{:021:}

点评

上课当天交的只统计上交,不做批改  发表于 2012-4-26 21:42
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

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

GMT+8, 2024-11-1 07:15 , Processed in 0.201284 second(s), 14 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

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