Excel 資料驗證與名稱功能,製作兩層下拉式選單教學

這裡介紹如何在 Excel 表格中製作兩層以上的下拉式選單,方便使用者快速選擇輸入的資料。

Excel 的資料驗證功能可以讓我們建立下拉式選單,但是普通的選單只有一層,如果想要讓表格的下拉式選單可以有兩層以上的階層式選擇,就必須配合 Excel 的「名稱」功能,以下是製作兩層下拉式選單的操作步驟。

兩層下拉式選單

假設我們現在有一個 Excel 檔案如下,左邊橘色的表格要讓使用者輸入資料,類別的欄位可以選擇「水果」、「蔬菜」或「薯芋」,而各種類別中可以選擇的項目則列在右邊的綠色表格中。

清單與表格

以下我們將使用「名稱」與「資料驗證」的功能,製作兩層的下拉式選單,讓使用者可以更方便輸入這樣的表格資料。

建立名稱

首先我們要先將每一個類別可用的選項都各自建立成 Excel 的「名稱」。
Step 1
選擇其中一個類別所有選項的清單範圍,選擇時要包含標題列。

選取清單資料範圍

Step 2
選擇「公式」籤頁中的「從選取範圍建立」。

從選取範圍建立

Step 3
選擇建立名稱時所用的值,也就是選擇標題列的位置,這裡我們的標題列在最上方,所以勾選「頂端列」。

以選取範圍建立名稱

按下「確定」之後,就會自動建立一個「名稱」。
Step 4
按照上述的方式,將每一個類別都分別建立「名稱」。

建立其餘清單名稱

Step 5
建立好每一個類別的「名稱」之後,點選「名稱管理員」。

點選「名稱管理員」

Step 6
在「名稱管理員」中可以查詢目前所有已經建立的「名稱」。每一個要顯示在第二層下拉式選單中的類別項目都要加進來,這裡先檢查一下是否有錯誤或是遺漏。

名稱管理員

設定資料驗證

在「名稱」建立好之後,接著使用「資料驗證」建立兩層的下拉式選單。
Step 1
選擇第一層下拉式選單的作用範圍,也就是要讓使用者選擇類別的儲存格範圍。

選擇儲存格範圍

Step 2
點選「資料」籤頁中的「資料驗證」。

點選「資料驗證」

Step 3
仿照一般以資料驗證製作下拉式選單的方式,選擇「清單」,並在來源欄位填入三種類別的儲存格範圍。

資料驗證

Step 4
接著選擇第二層下拉式選單的作用範圍,也就是要讓使用者選擇各種子項目的儲存格範圍,然後點選「資料驗證」。

設定第二層項目資料驗證

Step 5
這一步是最重要的關鍵,資料驗證的類型一樣選擇「清單」,但是來源要填入 INDIRECT 的公式:

=INDIRECT(A2)

其中 INDIRECT 的參數就填入類別欄位的第一格儲存格位置,在這個例子中就是 A2,如果您的表格位置跟這裡不同,就要自己修改一下。

第二層資料驗證

Step 6
這樣設定好資料驗證之後,會跳「目前評估為錯誤」的訊息,請直接按下「是」繼續套用資料驗證規則。

評估錯誤

Step 7
這樣就完成兩階層的下拉式選單了,接著我們就可以先從類別欄位中,以第一層選單選擇類別。

第一層下拉式選單

Step 8
當類別欄位輸入之後,在項目欄位的第二層選單中,就會自動顯示該類別可用的選項,這樣輸入資料就完全不需要打字,而且也不用擔心輸入錯誤的資料。

第二層下拉式選單

Windows

1 Comment

  1. catherine

    感謝~看你的blog真的讓我的excel進步很多!!!

Leave a Reply