2011年6月14日 星期二

Excel-查表練習(班級幹部名單摘要)

這次要練習的查表動作,需要較多的公式,感覺會較難,所以要有耐心和毅力才能有助於學會。

(一)

在 Excel 中有一個全校班級幹部的摘要表(如下圖),如何自動轉換至學生名單中註記幹部名稱(如下下圖)?

儲存格F2:

{=IFERROR(OFFSET($H$1,0,MATCH(D2,OFFSET($H$1,MATCH(B2,$H$2:$H$29,0),1,1,7),0),1,1),"")}

這是陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。複製儲存格F2,往下各列貼上。

公式意義:

(A) MATCH(B2,$H$2:$H$29,0):查出B欄中的班級位在H欄中的位置(第幾個)。

(B) OFFSET($H$1,(A),1,1,7),0):根據班級找到對應的幹部名稱,例如102班在儲存格I3:O3。

(C) MATCH(D2,(B),0):根據D欄的學生姓名,找到(B)所指的儲存格格位址的位置(第幾個)。

(D) OFFSET($H$1,0,(C),1,1):根據(C)的位置,找到幹部名稱。

(E) IFERROR((D),""):如果不是幹部會出現錯誤訊息,所以使用IFERROR函數將其顯示為空字串。

(二)

如果和(一)相反,在一個有幹部名稱的學生班級名單中(如下圖),要將所有各班的幹部名稱摘要出來(如下下圖),該如何處理?

image

儲存格I2:{=INDIRECT("D" & LARGE(IF((班級=$H2)*(幹部=I$1),編號,FALSE),1)+1)}

這是陣列公式,輸入完成請按 Ctrl+Shift+Enter 鍵。複製儲存格F2,貼至儲存格I2:O2。再複製儲存格I2:O2,往下各列貼上。

這個運算要靠一個輔助欄位(A欄)將每個學生給予一個編號。

運算前先選取學生名單,按一下 Ctrl+Shfit+F3 鍵,以頂端列定義名稱。

公式意義:

(A) IF((班級=$H2)*(幹部=I$1),編號,FALSE):找出班級合於H欄和幹部合於第1列的「編號」之陣列。

(B) LARGE((A),1):由編號陣列中找到最大值。(因為這個編號陣列是由符合者以1表示,不符合者以0表示。而全部陣列中只有1個1,其餘皆為0。)

(C) INDIRECT("D" & (B)+1):將(B)找到的編號加1,即是學生姓名所在的列號,將該列號加1,透過INDIRECT函數,指定D欄即可以找到其姓名。

詳細函數說明請參閱微軟網站:

INDIRECThttp://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

INDIRECT:傳回文字串所指定的參照位址。

語法:INDIRECT(ref_text,[a1])

ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。

a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。

MATCHhttp://office.microsoft.com/zh-tw/excel-help/HP010342679.aspx

MATCH:搜尋某儲存格範圍內的指定項目,然後再傳回該項目在範圍內的相對位置。

語法:MATCH(lookup_value, lookup_array, [match_type])

lookup_value:在 lookup_array 中尋找比對的值。

lookup_array:要搜尋的儲存格範圍。

match_type:這是一個數字,其值有三種可能:(預設值為 1)

1 或省略:找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列。

0:找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。

-1:找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序。

 

OFFSEThttp://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。Rows可以是正數(表示在起始參照位址下方)或負數(表示在起始參照位址上方)

Cols:左上角儲存格要往左或往右參照的欄數。Cols 可以是正數(表示在起始參照位址右方)或負數(表示在起始參照位址左方)

Height:所傳回參照位址的高度 (以列數為單位)Height 必須是正數。

Width:所傳回參照位址的寬度 (以欄數為單位)Width 必須是正數。

 

IFERRORhttp://office.microsoft.com/zh-tw/excel-help/HA010342587.aspx

IFERROR:如果公式計算錯誤,會傳回指定的值;否則,會傳回公式的結果。

語法:IFERROR(value, value_if_error)

value:檢查此引數是否有錯誤。

value_if_error:公式計算錯誤時要傳回的值。

使用 IFERROR 函數,可以捕捉並處理公式中的錯誤。會評估下列錯誤類型:#N/A#VALUE!#REF!#DIV/0!#NUM!#NAME? #NULL!

 

沒有留言:

張貼留言

好康東東