Excel函數：Find的數組用法幾例

06-01

1、字元串中第一個數字的位置

【公式注釋】

提示：輸入數組公式，需在公式編輯欄寫好公式化，同時按鍵盤 Ctrl Shirt Enter三鍵，方能得到正確結果。

=MIN(FIND(ROW($1:$10)-1,A1&1/17))

1、ROW($1:$10)生成數組

{1;2;3;4;5;6;7;8;9;10}

2、ROW($1:$10)-1生成數組

{0;1;2;3;4;5;6;7;8;9}

3、1/17返回一個包含0-9這10個數字的字元串

1/17=0.0588235294117647

4、A1&1/17 生成一個字元串，也就是在A1的字元串後鏈接1/17生成的0-9所有數字的字元串，形成一個新的字元串。

"wd123fdf0.0588235294117647"

5、FIND(ROW($1:$10)-1,A1&1/17)的意思是在

"wd123fdf0.0588235294117647"這個字元串中尋找

{0;1;2;3;4;5;6;7;8;9}這10個數字，並標記其在字元串中的位置，將公式用【F9】抹黑，生成如下數組

{9;3;4;5;20;12;24;23;13;19}，其對應{0;1;2;3;4;5;6;7;8;9}這10個數字在字元串"wd123fdf0.0588235294117647"中的位置，0的位置為9，1的位置為3，2得位置是4......

6、MIN({9;3;4;5;20;12;24;23;13;19}) 最後用min函數取

{9;3;4;5;20;12;24;23;13;19} 的最小值，自然就是第一個數字在字元串所在的位置。

2、模糊查找

根據姓名來查詢房屋面積，A列是包含姓名的地址。

【公式注釋】

=LOOKUP(9^9,FIND(D1,$A$2:$A$10),$B$2:$B$10)

1、9^9，9的9次方，代表一個很大的數字，在單元格輸入公式運算

2、FIND(D1,$A$2:$A$10)，返回數組

{#VALUE!;#VALUE!;16;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

如果在A列中找到改字元，那麼就顯示其在字元串中的位置，見16。百里守約在A4的字元串中的位置是16

3、=LOOKUP(9^9,FIND(D1,$A$2:$A$10),$B$2:$B$10)

Lookup函數是二分法的查找原理，不管懂不懂這個原理。

只要知道在FIND(D1,$A$2:$A$10)生成的數組當中，查找9^9這麼大的數字，lookup會返回小於9^9的最後一個數字。在{#VALUE!;#VALUE!;16;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!} 中就是16，也就是A4單元格所在的位置。

我們需要的結果自然就是A4單元格對應的B4單元格。

如果要反過來，根據姓名地址，查詢房屋面積。如下圖。

【結果】

可以看到Find函數的參數調換了。

FIND($D$1:$D$6,A2)返回的數組是：

{#VALUE!;#VALUE!;#VALUE!;12;#VALUE!;#VALUE!}

假如使用 FIND(A2,$D$1:$D$6)則發生錯誤，搜索不到結果。

只是調換了一下參數，為啥結果不相同了？

這是因為，Find函數的第一參數是支持數組的用法的。

使用FIND($D$1:$D$6,A2)的時候，我們用滑鼠抹黑$D$1:$D$6，按F9返回的是數組{"百里守約";"李白";"夏侯敦";"小李飛刀";"莊周";"曹操"}；

然後在A2單元格裡面，依次查找每個姓名，找不到就返回#VALUE!，找到就顯示姓名在A2字元串中的位置；

最後生成下面的數組；

{#VALUE!;#VALUE!;#VALUE!;12;#VALUE!;#VALUE!}

A2包含【小李飛刀】這個姓名，當Find搜索【小李飛刀】的時候自然就返回其在A2字元串中的位置12。然後Lookup大法和上面同樣的道理。

3、求「姓氏」得最大值

【公式注釋】

1、FIND(D2,$A$2:$A$8)返回數組

{1;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!;#VALUE!}

2、IFERROR(FIND(D2,$A$2:$A$8),0)返回數組

{1;0;0;1;0;0;0}，IFERROR是對

{1;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!;#VALUE!}

中的錯誤進行處理，使錯誤變成0

3、IFERROR(FIND(D2,$A$2:$A$8),0)*$B$2:$B$8

返回數組

{88;0;0;87;0;0;0}，因為姓氏都是在字元串的第一位的，所以和$B$2:$B$8【分數】相乘後就可以用max取其最大值了。

IFERROR(FIND(D2,$A$2:$A$8),0)生成的數組

乘以

$B$2:$B$8生成的數組

{1;0;0;1;0;0;0}

{88;45;26;87;56;68;98}

‖

{88;0;0;87;0;0;0}

最後Max函數取其最大值。

當然，我們也可以用Left函數生成一個數組，但姓氏不總是只有一個字元的，姓氏是「歐陽」就出錯了。當然拉，用Left函數也是可以的，len函數讀取一下姓氏的字元數，代入Left函數第二參數即可。

另外，如果版本支持，Excel2016新增的Maxifs函數可以使用，其支持通配符用法。

下面2010新增函數也可以完成，不需要數組三鍵。

公式來自：流浪鐵匠

=AGGREGATE(14,6,B$2:B$8/FIND(D2,A$2:A$8)^0,1)

4、統計包含某字元的單元格個數

統計姓名地址A列單元格區域單號「西關街」的單元格個數。

公式：=COUNT(FIND(C1,A2:A10))

有上面幾個例子的鋪墊，應該會明白此公式的思路了。

此例也可以通過用Countif輔助列的方法來實現。

輔助列公式：

=COUNTIF(A$2:A2,"*"&$C$1&"*")

求最大值公式：

=MAX(B2:B10)