2011年8月10日 星期三

Excel-找出資料所在的欄和列(陣列應用)

在 Excel 的工作表中,要由一堆資料裡查詢某資料所在的位置(指出第幾欄和第幾列)(參考下圖),該如何處理?這次練習以陣列公式來處理。

首先,將儲存格A1:G20定義名稱為「資料」。

儲存格J2:{=MAX((資料=J1)*COLUMN(資料))}

儲存格J3:{=MAX((資料=J1)*ROW(資料))}

此為陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。

以查詢「欄」為例:

(1) 在陣列公式中的「資料=J1」會產生一堆 True/False 組成的陣列。

假設姓名不重覆,則只有一個True,其餘均為False。

(2) COLUMN(資料):可以產生資料範圍內「欄」的陣列,本例為:{1,2,3,4,5,6,7}。

(3) 當(1) X (2) 時,True/False 陣列會轉換為 1/0 陣列,最後結果只有為 True(=1)的那一欄數(=5),相乘結果為不為0的數字(1X5=5),其餘均為0。因此取MAX(陣列)的結果即為欄數。

如果在不同欄有相同姓名,則會取得欄數較高者。

查詢「列」的原理同上。

沒有留言:

張貼留言

檢視其他文章

好康東東