這次要練習的查表動作，需要較多的公式，感覺會較難，所以要有耐心和毅力才能有助於學會。
(一)
在 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函數將其顯示為空字串。
(二)
如果和(一)相反，在一個有幹部名稱的學生班級名單中(如下圖)，要將所有各班的幹部名稱摘要出來(如下下圖)，該如何處理？
儲存格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欄即可以找到其姓名。
詳細函數說明請參閱微軟網站：
INDIRECT：http://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx
| INDIRECT：傳回文字串所指定的參照位址。 |
| 語法：INDIRECT(ref_text,[a1]) ref_text：單一儲存格的參照位址，其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱，或定義為字串的儲存格參照位址。 a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。 |
MATCH：http://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 引數內的值必須以遞減次序排序。 |
OFFSET：http://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 必須是正數。 |
IFERROR：http://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!。 |
留言列表