在和值表中查找號碼

問題是這樣的：

請從下圖所示的和值表中查找一個號碼，號碼順序不限，比如，圖示和值表區域中的「138」，要求不論查找318、381、138、183、813還是831，都能在第10行第4列找到這個號碼，並且：

（1）找出該號碼對應的行列號（上面已說了）；

（2）該位置的號碼；

（3）該位置上一行的號碼、下一行的號碼；

（4）這3個號碼中的不重複號碼。

解決辦法如下：

第1個問題。在C29單元格中輸入數組公式：{=MAX((SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4:K25,MID(B29,1,1),"",1),MID(B29,2,1),"",1),MID(B29,3,1),"",1)="")*(ROW(4:25)*100+COLUMN(B:K)))}

解釋：在和值表區域B4:K25所有單元格中，逐個單元格進行比較，分別查找所要查找號碼中的3個號碼（本例中即3、1、8）是否在當前單元格中，如果在其中，則把找到的第1個位置的該號碼替換為空字元串（""）。注意，不能一次全部替換為空字元串，因為，一個單元格的3個號碼中，可能有2個以上的相同號碼，如220、333。這樣，一次替換一個號碼，經過3次替換後，如果為空字元串，說明當前單元格中的號碼與欲查找的號碼相同（順序不一定一致），此時，(SUBSTITUTE(...)="")為TRUE，與後面數值相乘時等同1處理，否則為FALSE，等同0。後面的(ROW(4:25)*100+COLUMN(B:K)))就是區域中各個單元格的行列號的數值。前後相乘的結果，要麼為0，要麼為找到的單元格所對應的行列號的數值。這個數組中只有一個大於0的值，其餘都是0，所以，數組中的最大值就是該號碼對應的行列號。

第2個問題。在D29單元格中輸入公式：=INDIRECT(TEXT(C29,"!R0C00"),0)，第2個參數0（或FLASH）表示為R1C1樣式的引用。

第3個問題。

在E29單元格中輸入公式：=INDIRECT(TEXT(C29-100,"!R0C00"),0)

在F29單元格中輸入公式：=INDIRECT(TEXT(C29+100,"!R0C00"),0)

第4個問題。在G29單元格中輸入數組公式：{=IF(COUNT(FIND(0,D29:F29))>0,0,"") & SUBSTITUTE(SUM(ISNUMBER(FIND(ROW($1:$9),D29 & E29 & F29))*ROW($1:$9)*10^(9-ROW($1:$9))),0,"")}

COUNT函數計數時，空白單元格、邏輯值、文字或錯誤值都將被忽略。公式前半部分（加粗部分）判斷灰色區域3個單元格中是否至少有一個單元格中包括0，有為0，沒有則為空字元串。公式後半部分中，順序用1至9這9個數字在灰色區域3個單元格連接生成的文本中進行查找，如果有，則ISNUMBER(FIND(...))為TRUE，與後面的ROW($1:$9)*10^(9-ROW($1:$9))相乘；如果沒有，則ISNUMBER(FIND(...))為FALSE，與後面相乘後的結果還是0。本例中，用SUM(...)求和後的結果為123000080，通過SUBSTITUTE()函數把所有0用空字元串替換掉，還剩1238四個號碼，前面加一個0（原因前面已說過），最終結果為01238。
推薦閱讀：