2022/10/13

使用建立名稱+資料驗證+INDIRECT建立兩層下拉式選單

      , , ,      No comments   

使用資料驗證製作下拉式選單，是個很便利的功能，還可以減少資料輸入錯誤的機會。

但什麼是兩層下拉式選單呢？最基本的資料驗證，是建立一層下拉式選單，而第二層的功能，則是要能夠隨著選擇第一層之後，對可以選擇的內容進行調整，雖然設定方式相對複雜一些，但絕對能夠大大提升表單的便利與實用性。

其他參考：

《 如何製作下拉選單選擇名稱並顯示對應資料 》 --- INDEX+MATCH

    基本說明

    Microsoft Excel 與 Google Sheet 使用資料驗證建立一層下拉選單的設定，請參考這裡。
    建立名稱的方式兩者相同，但 Microsoft Excel 的網頁版本有一些限制，所以在命名前要再多加確認與注意了。
    INDIRECT函數公式在 Microsoft Excel 和 Google Sheet 中皆可使用，且用法相同。為方便辨識，儲存格標示為  黃底  的是帶有函式資料的儲存格；儲存格標示為  綠底  的為可修改資料，且會影響公式結果的儲存格；標示為  淺灰底  的為黃底儲存格的公式文字。
    此篇使用 Google Sheet 作為範例。



    建立名稱/ 命名範圍


    為儲存格範圍命名、取名稱，能讓儲存格範圍的用途更清楚易懂、一目了然，直接帶入還能夠簡化公式。Excel 和試算表的位置相同，不同之處有補充在下面的注意中。
      建立方式：
    1. 選擇好要命名的範圍，且不用包含標題列。
      (下方範例中為 B6 至 B10 )
    2. 找到公式列位於左上角的儲存格名稱方塊。
    3. 將要取的名稱直接輸入至方塊內。
    4. 完成。

    注意：
    • 名稱限制：
      開頭不能以字母、數字、底線( _ )、反斜槓( \ ) 以及 TRUE 或 FALSE
      其餘字元中不能有空格與標點符號，但可以使用底線及英文句點

    • 試算表：可至資料→已命名範圍，中進行修改與移除，快捷鍵為Ctrl + J
    • Excel 電腦版本：可至公式→名稱管理員，中進行修改與移除
    • Excel 網頁版本：在名稱建立後就無法移除與修改 (2022/10/8)



    函數INDIRECT介紹

    指定字串內容組成儲存格位置，並回傳內容。
    函數公式：  INDIRECT( "文字或儲存格位置" )

    ① 直接寫入儲存格位置 (同一工作表)：
    　 內容為文字，加上「"」雙引號框起。
    　EX：INDIRECT ( "B3" )

    ② 代入其他儲存格位置 (同一工作表)：
    　EX：INDIRECT ( C2 )

    ③ 直接寫入儲存格位置 (不同工作表)：
    　 工作表名稱後還需要加入英文小寫的「!」驚嘆號識別，後面直接接續儲存格位置，最後用「"」雙引號框起。
    　EX：INDIRECT ( "工作表2!A3" )

    ④ 代入其他儲存格位置 (不同工作表)：
    　 工作表名稱後還需要加入英文小寫的「!」驚嘆號識別，並用「"」雙引號框起，再加上「&」連結儲存格位置。
    　EX：INDIRECT ( "工作表3!" & D5 )



    實際操作

    此篇的函數公式結果顯示如下(  黃底  內容)：
      《 步驟 》
    1. 建立第一層選單 (C2)：
      選擇儲存格C2，找到上排文字功能列中的資料→資料驗證，條件選擇為「範圍內的清單」，並選擇下方資料的標題儲存格位置，為B5:C5。

    2. 命名資料範圍 (水果班 B6:B10)：
      選擇B6~B10，點擊鍵盤快捷鍵 Ctrl + J，輸入名稱「水果班」。

    3. 命名資料範圍 (蔬菜班 C6:C10)：
      選擇C6~C10，點擊鍵盤快捷鍵 Ctrl + J，輸入名稱「蔬菜班」。

    4. 建立第二層選單的資料範圍 (E6)：
      因為已建立好的名稱可以直接用於儲存格中，因此使用INDIRECT函式，讓它等於第一層選單的儲存格內容，記得下方要預留空間給資料，需要與命名資料範圍一樣大小，不然會有溢出的情況。

    5. 建立第二層選單 (C3)：
      選擇儲存格C3，找到上排文字功能列中的資料→資料驗證，條件選擇為「範圍內的清單」，並選擇下方資料的標題儲存格位置，為E6:E10。

    6. 完成~~~~~
      驗證範圍會隨著第一層選單的調整而改變。

    EXCEL公式小工具

    使用方式
    1. 輸入儲存格位置
    2. 選擇儲存格所在工作表位置
    3. 若為不同工作表，請再輸入工作表的名稱
    4. 按下 Enter 按鈕
    5. 點擊輸出的函數公式就可以直接複製
    6. 將它貼在想要顯示的儲存格內即可
    儲存格位置
    儲存格位於：
    工作表名稱
    Copied!

    0 Comments:

    張貼留言