贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

下圖的 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應該要尋找完全符合還是大約符合的值。

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

vincent 發表在 痞客邦 留言(2) 人氣()


留言列表 (2)

發表留言
  • LJ1820
  • 感謝Vincent~真的幫助我們解決很多excel問題
  • 謝謝你光臨我的網站。

    vincent 於 2017/04/21 11:47 回覆

  • sandy
  • 您好:

    請教一下,資料驗證的清單可以跨欄嗎?
    例如清單設在 A1-A5 和D1-D6
    謝謝!

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼