2012年9月19日 星期三

[Excel 應用] 進階複合式下拉式選單+參照函數

之前寫過一篇「excel 下拉選單」的文章,最近被「編講義」這回事追得快喘不過氣,不過差不多完成,突然發現,下拉選單如果加個「vlookup」的函數,範例可以更完整一些,哈哈,分享一下吧!

在本範例中,我們將許多電腦書分類,並且訂定價格,這份表單讓使用者只要輸入「訂購數量」即可,其他欄位接以下拉選單或是函數自動生成。
成果圖

進階下拉選單製作

定義來源資料名稱
  • 依序將資料來源做好歸類:
  1. 類別:文書、平面、網頁。
  2. 分類: 文書有 W、E、P;平面有 A、P、I;網頁有 F、D 等類。
  3. 各類別的書籍以及價格總表。
    各分類群組命名 

建立第一組下拉選單
  1. 在類別下的儲存格,點擊「資料 ->資料驗證」,選擇「清單」,來源輸入「= 類別」。
  2. 在分類下的儲存格,點擊「資料 ->資料驗證」,選擇「清單」,來源輸入「=INDIRECT(B6)」以參照「類別」資料。
  3. 在書名下的儲存格,點擊「資料 ->資料驗證」,選擇「清單」,來源輸入「=INDIRECT(D6)」以參照「分類」資料。

參照價格
  • 在單價下的儲存格,輸入「=VLOOKUP(E6, 價格,2,0)」參照「書名」資料的價格。
輸入折扣與計算金額
  1. 將折扣下的儲存格格式改為「自訂」,於「類型」欄位輸入「0"折"」。
    自訂儲存格格式 
  2. 於金額下的儲存格輸入「=F6*G6*H6/100」。

vlookup 函數

語法:VLOOKUP(指定某儲存格, 來源儲存格範圍, 第幾欄,0 或 1)

VLOOKUP 是很常用到「參照」函數,通常用於「拿 A 去比對 B」。例如,當我們選好一本書,接著就拿這本書的「書名」去比對來源參考資料的「書價」。

其中最後一個參數如果是 0,就是精準參照,也就是完全符合;如果是 1,代表相似參照,或是模糊比對

14 則留言:

  1. 您好:

    網路搜尋到這篇教學,下載了範例!
    預看您寫的公式,可是被密碼保護了!
    可否提供無密碼版本~感謝!!^^

    回覆刪除
    回覆
    1. 已幫您解除囉~有空歡迎多來這裡坐坐~~交流交流~哈哈

      刪除
  2. 這教學一定要用讓人下載的檔案才能完成
    我是新手 自己創一個檔案 是無法完成的
    發先一些奇怪的地方
    在"資料來源"選取B1 C1 D1 會出現類別
    煩請詳細指導

    回覆刪除
    回覆
    1. 如本文一開始的說明唷,需先定義範圍的名稱(選範圍,公式,定義名稱)

      刪除
  3. 你好 最後的 =F6*G6*H6/100 應該是 /10 金額才會正確喔

    回覆刪除
  4. 您好 最後的 =F6*G6*H6/100 應該是/10才會正確唷!!

    回覆刪除
  5. 您好!我有個問題想請教一下

    來源儲存格能外聯其他的EXCEL檔嗎?

    因為我列印範圍都選整張工作表,忘了去挑選範圍,結果浪費了很多紙....

    還請您不吝指教!

    謝謝

    回覆刪除
    回覆
    1. 來源範圍可以跨檔案挑選別的EXCEL檔案內的範圍唷~~OK的~~^^沒問題

      刪除
  6. 請問來源範圍除了透過預先建立的公式(名稱管理員),還有其他方式嗎??

    回覆刪除
  7. 因為要搭配函數,所以這個方式比較ok

    回覆刪除
  8. 請問為何在 "資料來源" 中將 類別裡面的名稱改了以後, "完成" 表格內的 "分類" 下拉就沒辦法運作了? 要如何調整呢? 謝謝

    回覆刪除
    回覆
    1. 檢查名稱管理員是否有對應到即可

      刪除
    2. 檢查名稱管理員是否有對應到即可

      刪除