excelperfect 导语:继续研究来自于excelxor.com的案例。建议结合本文阅读原文,会了解更多的细节,会有更大的收获。 本次的练习是:给定一个任意大小的单元格区域,其每个单元格中的数据要么是0要么是1,并且每行至少有一个1,要求使用一个公式返回一个数组,该数组由区域每行中第一次出现1的相对列位置组成。例如下图1所示的单元格区域A1:E10,要求返回数组{2;1;1;2;1;5;1;4;1;3}。 图1 注意,公式中可以使用单元格区域A1:E10,但应该适用于任何其他区域。并且,所返回的数组中的元素对应的是区域内的相对列位置,例如将图1中的区域替换成H1:L10不应该影响公式的结果。 先不看答案,自已动手试一试。 公式 下面列出了各种解决上述问题的公式。 公式1: =COLUMNS(A1:E10)-INT(LOG(MMULT(A1:E10,2^(COLUMNS(A1:E10)-ROW(INDIRECT('1:'&COLUMNS(A1:E10))))),2)) 公式2: =COLUMNS(A1:E1)-INT(LOG(MMULT(A1:E10*2^(COLUMNS(A1:E1)-COLUMN(A1:E1)),TRANSPOSE(A1:E1*0+1)),2)) 或: =COLUMNS(A1:E10)-INT(LOG(MMULT(A1:E10,2^TRANSPOSE(COLUMNS(A1:E10)-COLUMN(A1:E10)+MIN(COLUMN(A1:E10))-1)),2)) 公式3: =MATCH(1,(OFFSET($A$1,ROW($A$1:$A$10)-1,,,COUNTA($A$1:$E$1))),0) 公式4: =FIND(1,TEXT(MMULT(A1:E10*10^(COLUMNS(A1:E10)-COLUMN(A1:E10)+MIN(COLUMN(A1:E10))-1),TRANSPOSE(COLUMN(A1:E10)^0)),REPT(0,COLUMNS(A1:E10)))) 或: =FIND(1,TEXT(MMULT(A1:E10,TRANSPOSE(10^(COUNT(A1:E1)-COLUMN(A1:E1)))),REPT('0',COUNT(A1:E1)))) 或: =FIND(1,TEXT(MMULT(A1:E10,10^(COLUMNS(A1:E10)-ROW(INDIRECT('1:'&COLUMNS(A1:E10))))),REPT(0,COLUMNS(A1:E10)))) 公式5: =MOD(SMALL(IF(A1:E10=1,ROW(A1:E10)*10^5+COLUMN(A1:E10)-MIN(COLUMN(A1:E10))+1),SUBTOTAL(9,OFFSET(A1,,,ROW(A1:E10)-MIN(ROW(A1:E10))+1,COLUMNS(A1:E10)))-MMULT(A1:E10,TRANSPOSE(COLUMN(A1:E10)^0))+1),10^5) 或者将数据区域命名为Data: =MOD(SMALL(IF(Data=1,ROW(Data)*10^COLUMNS(Data)+COLUMN(Data)-MIN(COLUMN(Data))+1),SUBTOTAL(9,OFFSET(Data,,,ROW(Data)-MIN(ROW(Data))+1,COLUMNS(Data)))-MMULT(Data,TRANSPOSE(COLUMN(Data)^0))+1),10^COLUMNS(Data)) 公式6: =MATCH(ROW(Data),INDEX(Data*ROW(Data),N(IF(1,1+(INT((COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(Data)*ROWS(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((COLUMN(INDEX(1:1,1):INDEX(1:1,COLUMNS(Data)*ROWS(Data)))-1),COLUMNS(Data)))))),0)-(ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data) 或: =MATCH(ROW(Data),INDEX(Data*ROW(Data),N(IF(1,1+(INT((COLUMN(INDEX(1:1,1):INDEX(1:1,COUNT(Data)))-1)/COLUMNS(Data))))),N(IF(1,1+(MOD((COLUMN(INDEX(1:1,1):INDEX(1:1,COUNT(Data)))-1),COLUMNS(Data)))))),0)-(ROW(Data)-MIN(ROW(Data)))*COLUMNS(Data) 公式7: =ROUND(COLUMNS(A1:E1)+1-ROUND(MOD(LARGE(ROWS(A1:E10)-ROW(A1:E10)+1+(10^-7*A1:E10*(COLUMNS(A1:E1)-(COLUMN(A1:E1)-COLUMN(A1)))),1+((ROW(A1:E10)-ROW(A1))*COLUMNS(A1:E10))),1),7)*10^7,0) 公式8: =-INT(LOG(MMULT(Data,2^-ROW(OFFSET(A1,,,COLUMNS(Data)))),2)) 或: =-INT(LOG(MMULT(Data,TRANSPOSE(2^-COLUMN(Data))),2)) 扩展: 1. 每行的第一个正值的位置(数据中没有负值) =-INT(LOG(MMULT(SIGN(Data),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10)) 2. 每行的第一个正值的位置(数据中可能有负值) =-INT(LOG(MMULT(--(Data>0),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10)) 3. 每行的第一个非零值的位置 =-INT(LOG(MMULT(--(Data<>0),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10)) 4. 每行中指定的数据第一次出现的位置 =IFERROR(-INT(LOG(MMULT(--(Data='指定数据'),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10)),0) 公式9: =MATCH(1,INDEX(Data,ROW(Data)-MIN(ROW(Data))+1,),) 公式10: =--RIGHT(TEXT(MMULT(Data*10^(-COLUMN(Data)+MIN(COLUMN(Data))-1),TRANSPOSE(COLUMN(Data)^0)),'0E+000'),3) 小结 这里只列出了10组公式,原文中还有很多公式,有兴趣的朋友可以花点时间研究这些公式,打磨自已的公式技能。
|
|
来自: hercules028 > 《excel》