[第95期]数字转换[已总结]

袁勇

B6:
=IF(ISERR(FIND(B$2,$A6)),SMALL(IF(ISERR(FIND($B$2:$K$2,$A6)),$B$1:$K$1),COLUMN()-COUNT(0/FIND($B$2:B$2,$A6))-1),B$1)
数组，右拉下拉。116个字符。

pxiceplay

公式太长了，只好用自定义名称给压了一下，不知道符合要求不……

Bodhidharma

B6:K6多單元格數組公式：

1. =IF(ISERR(FIND(B$2:K$2,A6)),SMALL(IF(ISERR(FIND(B$2:K$2,A6)),B$1:K$1),MMULT(-ISERR(FIND(B$2:K$2,A6)),-(ROW($1:$10)<=COLUMN(A:J)))),B$1:K$1)

复制代码

如果可以用一般數組公式的話，B6儲存格

1. =IF(ISERR(FIND(B$2,$A6)),SMALL(IF(ISERR(FIND($B$2:$K$2,$A6)),$B$1:$K$1),COLUMN()-COUNT(FIND($B$2:B$2,$A6))-1),B$1)

复制代码

I000000I

普通公式=IF(ISERR(FIND(B$2,$A6)),SMALL(IF(ISERR(FIND($B$1:$K$1,$A6)),$B$2:$K$2),SUM(N(ISERR(FIND($B$2:B$2,$A6))))),B$1)
B6:K6多单元格数组公式 =IF(ISERR(FIND(B$2:K$2,A6)),SMALL(IF(ISERR(FIND(B$1:K$1,A6)),B$2:K$2),-MMULT(-ISERR(FIND(B$2:K$2,A6)),N(B$2:K$2+1>=ROW($1:$10)))),B$1:K$1)

xcmrbxmq

=IF(ISNA(MATCH(B$2:K$2,--MID(A6,ROW($1:10),1),)),SMALL(IF(ISNA(MATCH(B$2:K$2,--MID(A6,ROW($1:10),1),)),B$1:K$1),MMULT(--ISNA(MATCH(B$2:K$2,--MID(A6,ROW($1:10),1),)),N(ROW($1:$10)<=COLUMN(A:J)))),B$1:K$1)

syz105729913

没精简到120字符以下，先占个位置

1. =IF(ISERR(FIND(B$2:K$2,A6)),LARGE(ISERR(FIND(B$2:K$2,A6))*B$1:K$1,MMULT(1*ISERR(FIND(B$2:K$2,A6)),N(ROW($1:$10)>B$2:K$2))),B$1:K$1)

复制代码

11005046

1. =IF(ISERR(FIND(B$2:K$2,A6)),SMALL(IF(ISERR(FIND($2:$2,A6)),$1:$1),SUM(N(ISERR(FIND(B$2:INDIRECT("R2C",),A6))))),B$1:K$1)

复制代码

先交了，过几天在想想。要是俺有师傅就好了{:soso_e105:}

zheng_0925

B6:K6多单元格数组公式下拉

177:

1. =IF(ISERR(FIND(B$2:K$2,A6)),SMALL(IF(ISERR(FIND(B$2:K$2,A6)),B$1:K$1),B$2:K$2-MMULT(COLUMN(A:J)^0,COUNTIF(OFFSET(B$1,,,,B$2:K$2+1),MOD(MID(A6,ROW($1:$10),1)+5,10)))+1),B$1:K$1)
   

复制代码

158

1. =IF(ISERR(FIND(B$2:K$2,A6)),SMALL(IF(ISERR(FIND(B$2:K$2,A6)),B$1:K$1),B$2:K$2-MMULT((B$2:K$2+1)^0,N((0&MID(A6,ROW($1:$10),1))-B$2:K$2<1))-LEN(A6)+11),B$1:K$1)

复制代码

143

1. =IF(ISERR(FIND(B$2:K$2,A6)),LARGE(B$1:K$1*ISERR(FIND(B$2:K$2,A6)),MMULT((B$2:K$2+1)^0,N((0&MID(A6,ROW($1:$10),1))-B$2:K$2<1))-B$2:K$2),B$1:K$1)

复制代码

endend1980

得到结果并不难，难的是如何缩短公式长度，120搞不定，行数组公式

1. =IF(ISERR(FIND(B$2:K$2,A6)),SMALL(IF(ISERR(FIND(B$2:K$2,A6)),B$1:K$1),MMULT(--ISERR(FIND(B$2:K$2,A6)),N(ROW($1:$10)<COLUMN(B:K)))),B$1:K$1)

复制代码

476363814

终于等到楼主的总结了，期待好久了，
基本是一天要来看一次，太好了，，