贊助廠商

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

搜尋本部落格文章資料

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

參考網址: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。

創作者介紹

學不完.教不停.用不盡

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


留言列表 (7)

發表留言
  • Anita
  • 請問版主,到step3,卻出現不能參照其它表單的錯誤訊息。請問如何解?謝謝
  • 改為"=OFFSET(list!$A$2,0,MATCH(A2,年級,0)-1,15,1) "即可!

    vincent 於 2014/04/18 20:08 回覆

  • woody
  • 改為"=OFFSET(list!$A$2,0,MATCH(A2,年級,0)-1,15,1) "即可!
  • 謝謝您的補充說明

    vincent 於 2014/04/18 20:08 回覆

  • 好奇心
  • Woody您好,我覺得你的方法很不錯,但到STEP3,驗證會出現錯誤"您不能在 資料驗證 的準則中參照到其他工作表或活頁簿。",有方法可解嗎?謝謝!
  • 改為"=OFFSET(list!$A$2,0,MATCH(A2,年級,0)-1,15,1) "即可!

    vincent 於 2014/04/18 20:09 回覆

  • Bobby
  • 你好,我也是到Step3,驗證會出現錯誤"您不能在 資料驗證 的準則中參照到其他工作表或活頁簿。"
    改成"=OFFSET(list!$A$2,0,MATCH(A2,年級,0)-1,15,1) "仍然出現問題,可以解決嗎?謝謝
  • 阿菈蕾
  • 您好,以語法:MATCH(lookup_value, lookup_array, [match_type])出現錯誤
    是否可以以B8 提供範例參考
  • =OFFSET(list!A8,0,MATCH(A8,年級,0)-1,15,1)

    vincent 於 2015/05/12 18:18 回覆

  • 訪客
  • hi 板主

    你好像是我高中老師...
  • hi
    你好。

    vincent 於 2015/06/06 11:03 回覆

  • 特弱一
  • 老師您好,
    我是您忠實的讀者。
    最近遇到難題。

    下面是Surface產品的配置和價格。
    Model CPU Memory Storage GPU Price
    Surface Pro 4 i7 16GB 1TB No 2699
    Surface Pro 4 i7 16GB 512GB No 2199
    Surface Pro 4 i7 16GB 256GB No 1799
    Surface Pro 4 i5 16GB 512GB No 1899
    Surface Pro 4 i7 8GB 256GB No 1599
    Surface Pro 4 i5 8GB 512GB No 1699
    Surface Pro 4 i5 16GB 256GB No 1499
    Surface Pro 4 i5 8GB 256GB No 1299
    Surface Pro 4 i5 4GB 128GB No 999
    Surface Pro 4 m3 4GB 128GB No 899
    Surface Book i7 16GB 1TB Yes 3199
    Surface Book i7 16GB 512GB Yes 2699
    Surface Book i7 8GB 256GB Yes 2099
    Surface Book i5 8GB 256GB Yes 1899
    Surface Book i5 8GB 256GB No 1699
    Surface Book i5 8GB 128GB No 1499

    我想要做如下下拉試選單,選完model到GPU後自動出現價錢。請問要怎麼做才好呢。
    Model Surface Book
    CPU
    Memory
    Storage
    GPU
    Price

  • 請參考:http://isvincent.pixnet.net/blog/post/45510331

    vincent 於 2015/11/03 18:39 回覆

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼