有網友問到一個非常實用的問題:一般在 Excel 的工作表中輸入資料時,可以利用「資料驗證」中的清單來產生一個選單,方便使用者以選取選項的方式來輸入資料。但是,如果選項內容是會變動的,或是內容會出現一些空白選項(參考下圖),將會造成一些困擾,該如何克服這些問題?

image

我們要的是一個會自動增加內容的選單,而且沒有多餘的空白項:

image

做法如下:

假設選取用來作為選項的內容為儲存格D2:D22。

先新增一個名稱:MENU,其內容參照為:=OFFSET(D2,,,COUNTA(D2:D22))

正確的寫法如下:(注意工作表名稱及絶對參照($))

內容參照為:=OFFSET(工作表1!$C$2,,,COUNTA(工作表1!$C$2:$C$22))

其中使用 COUNTA 函數來取得儲存格範圍中有多少個含有資料的儲存格數,再透過 OFFSET 函數取得一個動態儲存格範圍(有內容的儲存格範圍)。

接著,選取儲存格A2:A22,設定資料驗證:

儲存格內允許:「清單」;來源:=MENU

.

如此,只要在選項的儲存格範圍中新增一筆資料時,選單中也會同步增加一個選項,而且不會出現一些空白項。

【延伸學習】

 Excel-在下拉式選單中選取日期

 Excel-製作隨輸入字元列出可選項目的下拉式清單

 Excel-下拉式清單選取月份列出該月日期

 Excel-設計二層的下拉式選單

 Excel-使用多層下拉式清單結構輸入資料(基礎)

 Excel-使用多層下拉式清單結構輸入資料(進階)

 Excel-選取月份/日期/時間(多層下拉式清單)

 Excel-下拉式選單顯示未選項目

 

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

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