2013年6月1日 星期六

Excel-在公式中善用名稱和資料驗證清單(VLOOKUP,INDIRECT)

下圖的 Excel 例子多年前曾經用過,可以用來學習在公式中使用:名稱和資料驗證清單,使用 VLOOKUPINDIRECT 函數來執行查詢的動作,可以讓公式簡捷可用。

我們要來設計使用下拉式清單來選取年級別和成績別,並能自動傳回對應的比重,因為查詢內容分佈在三個資料範圍中,該如何來撰寫公式呢?

(1) 建立名稱

選取[公式/名稱管理員]按鈕,在[名稱管理員]對話框中新增:

名稱:一年級,參照到:儲存格B5:C9。

名稱:二年級,參照到:儲存格E5:F9。

名稱:三年級,參照到:儲存格H5:I9。

(2) 使用資料驗證清單來製作下拉式清單

 

選取取儲存格C1,按一下[資料/資料驗證]按鈕,再選取[資料驗證]選項。在[資料驗證]對話框中設定資料驗證準則:

儲存格內允許:清單,來源:「一年級,二年級,三年級」。

選取取儲存格C2,再選取[資料/資料驗證]按鈕,再選取[資料驗證]選項,設定資料驗證準則:

儲存格內允許:清單,來源:「=$B$6:$B$9」。

(3) 套用公式

儲存格C3:=VLOOKUP(C2,INDIRECT(C1),2,FALSE)

INDIRECT(C1):將儲存格C1的內容字串(例如:二年級)轉換為已定義的名稱(代表一個儲存格範圍,例如:儲存格E5:F9)。

VLOOKUP(C2,INDIRECT(C1),2,FALSE):在儲存格範圍中(上式中所定義的名稱)的第 1 欄查詢儲存格C2內容,並傳回其第 2 欄所對應的內容。

 

【補充資料】

相關函式的說明,請詳閱微軟網站: 

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

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

語法:INDIRECT(ref_text,[a1])

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

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

VLOOKUPhttp://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx

VLOOKUP:用來搜尋儲存格範圍的第一欄,然後從範圍同一列的任何儲存格傳回一個值。

語法:VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value:在表格或範圍的第一欄中搜尋的值。

table_array:包含資料的儲存格範圍。可以使用範圍的參照,也可以使用範圍名稱。

col_index_numtable_array 引數中必須傳回相符值的欄號。

range_lookup:這是一個邏輯值,用以指定VLOOKUP應該要尋找完全符合還是大約符合的值。

沒有留言:

張貼留言

好康東東