楼主
bbwsj |
 查间隔_题目.rar
5555555555
我要疯了，怎么一编辑，以前写的东东又没了
算了，发完汇总答案不编辑了
查间隔_答案.rar
|
2楼
rongjun |
数组公式- =IF(COUNTIF($A$1:$I$14,B$16)<ROW(A1),"",INDEX(FREQUENCY(ROW($1:$14)*10+COLUMN($A:$I),IF($A$1:$I$14=B$16,ROW($1:$14)*10+COLUMN($A:$I)))-1,ROW(A1)))
查间隔_rongjun.rar
|
3楼
liuguansky |
- =MIN(IF($A1:$I14=B16,ROW(1:14)*9-10+COLUMN($A:$I)))
- =TEXT(MMULT(SMALL(IF($A1:$I14=B16,ROW(1:14)*9-9+COLUMN($A:$I),127),IF({1,0},ROW(1:125),ROW(2:126))),{-1;1})-1,"0;;0")
一个公式
- =TEXT(MMULT(IF(ROW(1:125)+{1,2}>2,SMALL(IF($A1:$I14=B16,ROW(1:14)*9-10+COLUMN($A:$I),127),IF({1,0},ROW(1:125)-1,ROW(1:125))),-1),{-1;1})-1,"0;;0") B列区域数组，右拉
字符146 未上传附件。
- =TEXT(MMULT(IF(ROW(1:125)+{1,2}>2,SMALL(IF($A1:$I14=B16,ROW(1:14)*9-10+COLUMN($A:$I),127),ROW(1:125)-{1,0}),-1),{-1;1})-1,"0;;0") B列区域数组，右拉
|
4楼
wjh619993018 |
选中B17:B89单元格，输入数组公式- =IF(ROW()>COUNTIF($A1:$I14,B$16)+16,"",FREQUENCY(IF($A1:$I14<>B$16,ROW(1:14)*9+COLUMN($A:$I)),IF($A1:$I14=B$16,ROW(1:14)*9+COLUMN($A:$I))))
，右拉
查间隔_题目.rar
|
5楼
wjc2090742 |
- =IF(ROW(A1)>SUM(N($A$1:$I$14=B$16)),"",SMALL(IF($A$1:$I$14=B$16,ROW($1:$14)*9-9+COLUMN($A:$I)),ROW(A1))-SUM(B$16:B16)-ROW(A1)+B$16)
晒一下基本的解法。
查间隔_题目.rar
|
6楼
水星钓鱼 |
函数生疏了好多啊- =IF(ROW(A1)<=COUNTIF($A$1:$I$14,B$16),SUM(SMALL(IF(B$16=$A$1:$I$14,COLUMN($A:$I)+9*ROW($1:$14)-9),IF(ROW(A1)=1,1,ROW(1:2)-1))*IF(ROW(A1)=1,{0;1},{-1;1}))-1,"")
数组公式，左拉下拉
换个思路- =IF(ROW(A1)<=COUNTIF($A$1:$I$14,B$16),INDEX(FREQUENCY(COLUMN($A:$I)+9*ROW($1:$14)-9,IF(B$16=$A$1:$I$14,COLUMN($A:$I)+9*ROW($1:$14)-9))-1,ROW(A1)),"")
再来减几个字符
- =IF(ROW(A1)<=COUNTIF($A$1:$I$14,B$16),INDEX(FREQUENCY(ROW($1:$126),IF(B$16=$A$1:$I$14,COLUMN($A:$I)+9*ROW($1:$14)-9))-1,ROW(A1)),"")
|
7楼
wjh619993018 |
B17输入如下公式后，下拉，右拉- =IF(ROW()-17<COUNTIF($A$1:$I$14,M$16),SMALL(IF($A$1:$I$14=B$16,ROW($1:$14)*9+COLUMN($A:$I)),ROW(A1))-SUM(M$16:M16)-ROW()+7+M$16,"")
|
8楼
piny |
数组 右拉下拉
192字元
B17=IF(COUNTIF($A$1:$I$14,B$16)<ROW()-16,"",IF(ROW()=17,MIN(IF($A$1:$I$14=B$16,COLUMN($A:$I)+ROW($1:$14)*9-9)),SUM(SMALL(IF($A$1:$I$14=B$16,COLUMN($A:$I)+ROW($1:$14)*9),ROW(A1)-{1,0})*{-1,1}))-1)
===========================================================
185字元
B17=IF(COUNTIF($A$1:$I$14,B$16)<ROW()-16,"",IF(ROW()=17,MIN(IF($A$1:$I$14=B16,COLUMN($A:$I)+ROW(1:14)*9-9)),SUM(SMALL(IF($A$1:$I$14=B$16,COLUMN(A:I)+ROW(1:14)*9),ROW(A1)-{1,0})*{-1,1}))-1)
===========================================================
147字元
B17=IF(COUNTIF($A$1:$I$14,B$16)<ROW()-16,"",SUM(SMALL(IF(ROW($1:$15)<15,IF($A$1:$I$14=B$16,COLUMN($A:$I)+ROW($1:$14)*9-9),0),ROW(A1)+{8,9})*{-1,1})-1)
===========================================================
143字元
B17=IF(COUNTIF($A$1:$I$14,B$16)<ROW()-16,"",SUM(SMALL(IF(ROW($1:$15)<15,IF($A$1:$I$14=B$16,COLUMN($A:$I)+ROW(1:14)*9-9),0),ROW()-{8,7})*{-1,1})-1)
===========================================================
142字元
B17=IF(ROW()+SUM(-($A$1:$I$14=B$16))>16,"",SUM(SMALL(IF(ROW($1:$15)<15,IF($A$1:$I$14=B$16,COLUMN($A:$I)+ROW(1:14)*9-9),0),ROW()-{8,7})*{-1,1})-1)
===========================================================
141字元
B17=IF(SUM(N($A$1:$I$14=B$16))<ROW(A1),"",SUM(SMALL(IF(ROW($1:$15)<15,IF($A$1:$I$14=B$16,COLUMN($A:$I)+ROW(1:14)*9-9),0),ROW()-{8,7})*{-1,1})-1) |
9楼
庭院幽幽 |
有点想学，可惜学不到 |
10楼
wangg913 |
也看看高手怎么做.- =LOOKUP(999,IF({1;0},{0,""},SUM(SMALL(IF($A$1:$I$14=B$16,ROW($1:$14)*9+COLUMN($A:$I),9),110-COUNTIF($1:$14,B$16)+ROW()-{1,0})*{-1,1})-1))
|
11楼
gouweicao78 |
131字符，估计是这个：- =IF(ROW(A1)>COUNTIF($A$1:$I$14,B$16),"",SMALL(IF($A$1:$I$14=B$16,ROW($1:$14)*9+COLUMN($A:$I)),ROW(A1))-SUM(B$16:B16)+B$16-ROW(A10))
一般我还是习惯用ROW(1:1)，不用ROW(A1) |
12楼
amulee |
烦了点- =SUM(SMALL(($A$1:$I$14=B$16)*((ROW($A$1:$I$14)-1)*9+COLUMN($A$1:$I$14)),ROW(INDIRECT("A"&COUNTIF($A$1:$I$14,"<>"&B$16)+ROW(A1)&":A"&COUNTIF($A$1:$I$14,"<>"&B$16)+ROW(A1)+1))-1)*{-1;1})-1
|