|
小窍门：IOS系统轻划屏幕可暂停动图哦！ 方法：MID+MOD+TEXT 详解 EXCEL2007举例 1、计算“星座”需添加辅助G列、H列，如下图，G列日期按升序排列。 2、“星座”单元格C2输入如下公式，按回车并向下复制完成： =VLOOKUP(--TEXT(CONCATENATE(YEAR(TODAY()),MID(B2,11,4)),'0000-00-00'),G:H,2,1) 红色部分是公式的核心： CONCATENATE(YEAR(TODAY()),MID(B2,11,4)) 其中：YEAR(TODAY())，返回2015。 MID(B2,11,4)取身份证号中的“月“和“日”，返回0820。 CONCATENATE函数将年月日连接起来，这段公式的运算结果为：20150820。 然后，TEXT函数将20150820转换成日期格式2015-08-20，TEXT前面两个负号，将日期转换成数值42236。 最后用VLOOKUP在G列模糊查找，小于42236的最大值为7月22日（数值42207），返回同行H列“狮子座”。 3、或者“星座”单元格C2也可以输入如下公式，按回车并向下复制完成： =VLOOKUP(CONCATENATE(YEAR(TODAY()),'/',MID(B2,11,2),'/',MID(B2,13,2))*1,G:H,2,1) 这个公式跟第2步的公式类似，但不需要用TEXT转换。 4、“生肖”单元格D2输入如下公式，按回车并向下复制完成： =MID('猴鸡狗猪鼠牛虎兔龙蛇马羊',1+MOD(MID(B2,7,4),12),1) 内层函数：MOD(MID(B2,7,4),12) 求身份证中的“年”除以12的余数，MID(B2,7,4)返回1999,MOD(1999,12)=7。 外层MID函数，从'猴鸡狗猪鼠牛虎兔龙蛇马羊'中，提取第一个字符的位置为1+7=8，返回1个字符为“兔”。 5、“性别”单元格E2输入如下公式，按回车向下复制完成： =MID('男女',2-(MOD(MID(B2,17,1),2)),1) 身份证号倒数第二位单号为“男” 身份证号倒数第二位双号为“女” MID(B2,17,1)取身份证最后第二位数字，用MOD取该数字除以2的余数，单号余数为1，双号余数为0。 2-(MOD(MID(B2,17,1),2))将余数1和0调整为1和2，最外层用MID从'男女'中取数。 |
|
|
