=OFFSET(题目!A2,LEFT(RIGHT(900&SMALL(IF(PHONETIC(OFFSET(题目!A2,MATCH(题目!H3:H188,题目!C3:C188,),,,4))=PHONETIC(OFFSET(题目!F2,ROW(1:186),,,4)),999,ROW(1:186)+MATCH(题目!H3:H188,题目!C3:C188,)/10^3),ROW()-3),{7,7,7,7,3,3}),3),{7,8,5,6,0,1})&""
选中B4:G28,输入多单元格数组公式,刚好230字符,汗!
思路:首先利用MATCH函数找到调动前的人员在调动后的位置,再用OFFSET得到一个和调动后顺序一样的,利用PHONETIC分别对调动前后的每个人资料合并,进而判断每个人调动前后是否一致,如果一致,取999,如果不一致,取调动后的排序号加调动前的排序号除1000(因题目中最大排序号为三位数),对所取得的数字进行排序,然后以小数点为界,利用RIGHT和LEFT取两边的数字,考虑到调动后的序号有可能为一位或者两位(但至少有一位)且999无法正常取到,故在排序后的数字前加900(此处处理有瑕疵,当源数据多于900条时会出错),最后用OFFSET实现取数,完成!
PS:判断部分最初是用OFFSET及MMULT实现的
=MMULT(N(T(OFFSET(题目!A2,MATCH(题目!H3:H188,题目!C3:C188,),{0,1,2,3}))<>题目!F3:I188),{1;1;1;1})
字符虽少,但最后嵌套超限,无奈只得出此下策。
=========================================================================================
稍做修改
=OFFSET(题目!A2,MID(SMALL(IF(PHONETIC(OFFSET(题目!A2,MATCH(题目!H3:H188,题目!C3:C188,),,,4))=PHONETIC(OFFSET(题目!F2,ROW(1:186),,,4)),1-10^-6,ROW(1:186)/10^3+MATCH(题目!H3:H188,题目!C3:C188,)/10^6),ROW()-3),{3,3,3,3,6,6},3),{7,8,5,6,0,1})&""
比之前节省三个字符,现227字符,且节省一层嵌套,解决了源数据大于900可能出错的情况,拓展性更强,如果源数据达到四位,只需要将幂数改为4/8,MID的参数修改为4/8即可,字符数不受影响,但仍然不完善,即可能出现“甲”、“乙丙”合并得到“甲乙丙”,与“甲乙”、“丙”合并同样得到“甲乙丙”,导致判断出错的情况,利用MMULT可避免出现此情况,但仍然嵌套超限,努力改进,加油!
=========================================================================================
再改
=OFFSET(题目!A2,MID(SMALL(IF(PHONETIC(OFFSET(题目!A2,MATCH(题目!H3:H188,题目!C3:C188,),,,4))=PHONETIC(OFFSET(题目!F2,ROW(1:186),,,4)),1-1%%%%,ROW(1:186)%%+MATCH(题目!H3:H188,题目!C3:C188,)%%%%),ROW()-3),{3,3,3,3,7,7},4),{7,8,5,6,0,1})&""
以%代替幂的表示方式,公式减少到223字符,再努力!
========================================================================================
用MMULT实现判断,逻辑上更严密
=OFFSET(题目!A2,MID(SMALL(MMULT(N(T(OFFSET(题目!A2,MATCH(题目!H3:H188,题目!C3:C188,),{0,1,2,3}))<>题目!F3:I188),{1;1;1;1})*(ROW(1:186)%%+MATCH(题目!H3:H188,题目!C3:C188,)%%%%-1+1%%%%)+1-1%%%%,ROW()-3),{3,3,3,3,7,7},4),{7,8,5,6,0,1})&""
221字符(此公式结果正确,但仅提供思路参考,有待确认,因有可能产生浮点误差)
[ 本帖最后由 sunbin200388 于 2010-11-22 16:12 编辑 ] |