2010年6月25日 星期五

Excel-使用多層下拉式清單結構輸入資料(進階)

前一篇文章提到:使用多層下拉式清單結構輸入資料(基礎)

參考網址:http://isvincent.blogspot.com/2010/06/excel_25.html

這次要來練習:不需要每個年級都定義一個名稱,而是要以整個基本資料表為單位來查詢。

各年級資料置於list工作表

 

1. 將A欄的儲存格設定資料驗證的準則為:

(1) 儲存格內允許:清單

(2) 來源=list!$A$1:$C$1

2. 定義名稱

年級:=list!$A$1:$C$1

3.將B欄的儲存格設定資料驗證的準則為:(以儲存格B2為例)

(1) 儲存格內允許:清單

(2) 來源=OFFSET(list!A2,0,MATCH(A2,年級,0)-1,15,1)

此處假設每個年級的最多班級數為14班,所以公式中:OFFSET(list!A2,0,MATCH(A2,年級,0)-1,15,1)使用參數15。此公式保留了彈性,如果年級數(欄數)有變動時,只要修改「年級」名稱定義的位址即可。

如此,只要於A欄的儲存格中選取一個年級,即可以在B欄中選取一個對應的相關班級。 

參考資料:

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

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

Reference:必要參數。這是用以計算位移的起始參照位址。

Rows:必要參數。這是左上角儲存格要往上或往下參照的列數。

Cols:必要參數。這是結果的左上角儲存格要往左或往右參照的欄數。

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

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

 

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

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

lookup_value:必要項。這是您要在 lookup_array 中尋找比對的值。

lookup_array:必要參數。要搜尋儲存格範圍。

match_type:選用參數。這是一個數字,其值有三種可能:-1、0 或 1。

沒有留言:

張貼留言

檢視其他文章

好康東東