|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 5blessyou 于 2011-11-26 16:07 编辑
缩减到190个字符- =INDEX($A:$A,MOD(MIN((ROW($2:$25)/1%+IF($B$2:$B$25=F$2,ROW($2:$25),IF($C$2:$C$25,MATCH(F$2&MAX($C:C)+1-$C$2:$C$25,$B$1:$B$25&$C$1:$C$25,),99)))*IF(COUNTIF($F$2:G2,$A$2:$A$25),13,1)),100))&""
复制代码 188个字符- =INDEX($A:$A,MOD(MIN(IF($C$2:$C$25,IF($B$2:$B$25=F$2,ROW($2:$25),MATCH(F$2&MAX($C:C)+1-$C$2:$C$25,$B$1:$B$25&$C$1:$C$25,))+ROW($2:$25)/1%+IF(COUNTIF($F$2:G2,$A$2:$A$25),5^5),5^5)),100))&""
复制代码 ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
不知道这样引用整列是否楼主要求
给一个不使用整列的,完全符合只引用A1:C25,F2:G2
198个字符- =OFFSET($A$1,MOD(MIN(IF($C$2:$C$25,IF($B$2:$B$25=F$2,ROW($2:$25),MATCH(F$2&MAX($C$1:$C$25)+1-$C$2:$C$25,$B$1:$B$25&$C$1:$C$25,))+ROW($2:$25)/1%+IF(COUNTIF($F$2:$G2,$A$2:$A$25),5^5),5^5)),100)-1,)&""
复制代码 继续简化,196个字符
- =OFFSET($A$1,MOD(MIN(IF($C$2:$C$25,IF($B$2:$B$25=F$2,ROW($1:$24),MATCH(F$2&MAX($C$1:$C$25)+1-$C$2:$C$25,$B$2:$B$25&$C$2:$C$25,))+ROW($2:$25)/1%+IF(COUNTIF($F$2:$G2,$A$2:$A$25),5^5),5^5)),100),)&""
复制代码
以上公式皆为普通数组公式! |
本帖子中包含更多资源
您需要 登录 才可以下载或查看,没有账号?免费注册
x
评分
-
1
查看全部评分
-
|