excelperfect

導(dǎo)語：繼續(xù)研究來自于excelxor.com的案例。建議結(jié)合本文閱讀原文，會(huì)了解更多的細(xì)節(jié)，會(huì)有更大的收獲。

本次的練習(xí)是：給定一個(gè)任意大小的單元格區(qū)域，其每個(gè)單元格中的數(shù)據(jù)要么是0要么是1，并且每行至少有一個(gè)1，要求使用一個(gè)公式返回一個(gè)數(shù)組，該數(shù)組由區(qū)域每行中第一次出現(xiàn)1的相對列位置組成。例如下圖1所示的單元格區(qū)域A1:E10，要求返回?cái)?shù)組{2;1;1;2;1;5;1;4;1;3}。

圖1

注意，公式中可以使用單元格區(qū)域A1:E10，但應(yīng)該適用于任何其他區(qū)域。并且，所返回的數(shù)組中的元素對應(yīng)的是區(qū)域內(nèi)的相對列位置，例如將圖1中的區(qū)域替換成H1:L10不應(yīng)該影響公式的結(jié)果。

先不看答案，自已動(dòng)手試一試。

公式

下面列出了各種解決上述問題的公式。

公式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)

或者將數(shù)據(jù)區(qū)域命名為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))

擴(kuò)展：

1. 每行的第一個(gè)正值的位置（數(shù)據(jù)中沒有負(fù)值）

=-INT(LOG(MMULT(SIGN(Data),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10))

2. 每行的第一個(gè)正值的位置（數(shù)據(jù)中可能有負(fù)值）

=-INT(LOG(MMULT(--(Data>0),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10))

3. 每行的第一個(gè)非零值的位置

=-INT(LOG(MMULT(--(Data<>0),10^-ROW(OFFSET(A1,,,COLUMNS(Data)))),10))

4. 每行中指定的數(shù)據(jù)第一次出現(xiàn)的位置

=IFERROR(-INT(LOG(MMULT(--(Data='指定數(shù)據(jù)'),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)

小結(jié)

這里只列出了10組公式，原文中還有很多公式，有興趣的朋友可以花點(diǎn)時(shí)間研究這些公式，打磨自已的公式技能。