以初學者的角度,來看待電腦教學這回事。

標籤: 下拉式選單

Excel深度教學:「二層」、「三層」的動態「下拉式選單」設定

Excel的多階層選單

在前一篇Excel的教學文中,我介紹了使用「資料驗證」的方式,來製作「下拉式選單」( Excel深度教學:使用「資料驗證」來製作「下拉式選單」),若您還不曉得這是什麼,您應該先看完那篇之後,再回過頭來看這篇。

而本篇要說明的是,有時我們的清單資料,是有「階層」的分類,尤其是產品很多的公司,一定都會把數量龐大的產品分門別類,要不然將所有的產品都設定成單一個「下拉式選單」,你可能也很難找的到。

例如下圖,是我簡單製作的文具用品清單,在每個「分類」中,都有屬於它自己的文具用品項目,因此在「品名」出現的下拉式選單,才不會一次出現所有的文具用品,而讓你選的眼花繚亂。

excel_step_combo-1.jpg

那問題就來了,「分類」還不是什麼問題,就直接設定「清單」的來源就好了,因為它的清單項目都是「固定」的,但「品名」這一欄就不一樣了,因為這一欄的「清單」,是根據「分類」下去改變,是不是將「清單來源」給設死的,因此這邊就有個小技巧來分享給各位,教大家如何來處理這種狀況。

Excel二層下拉式選單設定教學

Step 1. 首先,在其它的工作表(例如:Sheet2)製作好一個有階層的資料清單,在下面的例如中,我簡單製作了一個文具用品的階層資料,所有的文具用品分為三類,分別是「書寫用品」、「辦公用品」及「紙製品」,並將分類的資料,填入第一列中,而該分類中的文具用品項目,就分別填入每個欄位裡,例如「書寫用品」裡有「原子筆、筆芯、鉛筆」等項目,如下圖:

excel_step_combo-2.jpg

Step 2. 製作好分類的資料庫清單後,接著新增「文具用品分類」及每個分類下的文具用品的清單「名稱」,總共會新增四個「名稱」出來,如下圖名稱管理員所示,若您對於新增「名稱」的方法有問題的話,請參考「Excel深度教學:使用「資料驗證」來製作「下拉式選單」」這篇文章。

excel_step_combo-3.jpg

Step 3. 接著先將「分類」的資料驗證,設定為「清單」,並將來源設定為「名稱」中的「文具用品分類」。

excel_step_combo-4.jpg

若您能設定成功,我們就能在分類中,利用「下拉式選單」來選取文具用品的大分類項目了,如下圖:

excel_step_combo-5.jpg

Step 4. 接著就是本篇文章的重點所在,就是「品名」欄位的「資料驗證」如何設定,因為在這個欄位中的清單是「變動」的,也就是隨著我們選擇的「分類」,而在「品名」欄位中,出現該分類的文具用品項目。

首先,先選取整個「品名」欄位,也就是「欄位C」,接著在來源中輸入「=INDIRECT(B1)」,最主要就是利用「INDIRECT」這個函數,而儲存格B1就是分類的那一欄位,因此這邊要根據您的情況來做設定。

這邊你也許會有個問題,就是我怎麼會將這一整個C欄位,都設定「=INDIRECT(B1)」呢?第二列不應該要設定成「=INDIRECT(B2)」,而第三列應該是「=INDIRECT(B3)」嗎?

沒錯,您的疑問是對的,不過Excel真的蠻聰明的,它會主動幫我們做跳號的動作,厲害吧?

excel_step_combo-6.jpg

而由於我們也將「標題」列也設定「資料驗證」了,因此會有底下這個「來源 目前評估為錯誤。您要繼續嗎?」的提示對話盒出現,這個地方不用管它,直接點擊〔是〕即可。

excel_step_combo-7.jpg

Step 5. 最後再來到我們輸入資料的表單中,你就會發現到「品名」這欄位所有的儲存格,已經能根據該列的「分類」來產生出不同的「外拉式選單」了。

excel_step_combo-8.jpg

如果您要設定「三層」的下拉式選單,第三層的作法也和第二層是一樣的。

Excel深度教學:使用「資料驗證」來製作「下拉式選單」

使用Excel「下拉式選單」來輸入資料

Excel」和「Word」一樣,看似很簡單,像是Word只是把文字給輸入進去,而Excel充其量也只是把一些數字與文字,填進去一個個儲存格罷了,但實際上,在每個小細節之中,都有更「高明」的做法出現。

之前我曾經寫過「使用Microsoft Word最常犯的五個錯誤」及「每個人都要學的Word實用技巧教學-合併列印」這幾篇文書處理的文章,雖然瀏覽的人數並不高,但卻受到網友們的肯定,剛好我對於Excel也還蠻熟的,因此也想分享一下使用Excel的一些經驗。

而這篇文章,我會針對「輸入文字到儲存格」的方式,由簡單到困難,來示範一些相當有用的做法,這些做法都是非常實用的技巧,學起來後,包準您能製作出高等的Excel文件,當然如果您是高手,還請高抬貴手,不要笑我教這麼粗淺的東西啊。

經常我們都會在Excel上輸入一些少量的「固定清單資料」,像是在輸入人事資料中的「性別」:「男,女」,「血型」:「A,B,AB,O」…等等,如以下的人事資料清單:

excel_combobox-1.jpg

這些資料是我自己杜撰的,而在清單中所輸入的性別及血型有一個特點,就是「不會變動」,因為不會有「男、女」之外的選項出現。

但你有發現到怪怪的嗎?

由於這些資料都是我「手動」輸入的,相信有大部份的人也都一樣,在輸入一些固定資料時,都是用直接打字的方式,將資料給填入儲存格中。你一定覺得很奇怪,若不用手輸入,難不成用腳嗎?

我的意思不是這樣的,直接用打字的方式輸入這種固定資料,可能是最簡單的的方式,但並不是一個最好的方式,原因就是「容易出錯」,你一、兩次一定不會打錯,但要是打了上百次,你能保證你完全都不出錯,而且都能注意到嗎?就好比我輸入的這四筆員工資料中,就已經有出錯的地方了,你看出來了嗎?

答案就是「黃色小鴨」的「血型」出錯了,原本應該是英文的「O」,結果被打成數字的「0」,但因為都是一個圈圈,因此不容易看出來,你會覺得就算打錯,會有什麼問題?

我舉個例子,假設你今天到銀行裡面存錢,行員不小心少收了1塊錢,而你也沒有發現到,但因為只有1塊錢,你會想說銀行自己貼錢進去就好了,就好像一般商店的店員,當每日結算的帳款不符時,若金額只有差幾塊錢而已,可能就都是當班店員自行吸收就好,但銀行可就不一樣了,雖然我沒有在銀行任職過,但聽說他們碰到這種情況時,就要加班把帳抓出來,你能想像這浩大的工程嗎?簡直可以拍攝Discovery的節目了。

回到我們這個員工資料表,如果您用「樞紐分析表」或是「資料庫」來分析員工在每個血型的人數時,你會發現到「A型」1人、「B型」0人、「AB型」1人、「O型」1人,怎麼所有血型加起來只有3人?整個公司不是有4人嗎?怎麼少1人?這就是輸入資料錯誤時,會產生的問題。

現在由於只有4筆,所以仔細檢查一下,還可以找出問題所在,但要是員工資料有1萬筆時,是不是就像大海撈針一樣,而且,並不是所有的資料,都可以透過最後的結果,來看出所得出的資料是有問題的資料,若出現這種問題時,事情可就大條了。

限制可以輸入文字資料的「資料驗證」

在Excel裡,有一個功能叫做「資料驗證」,就是設計用來避免這種輸入無效資料的狀況,使用的方式如下:

Step 1. 首先,選取您想要設定的欄位,因為我們要設定的通常是一整個欄位,因此在選取時,一次選取一整個「欄」或一整個「列」即可,因為總不能一格一格的設定吧?

在選取之後,再點擊「資料」頁籤中的「資料驗證」,如下圖:

excel_combobox-2.jpg

Step 2. 接著在「資料驗證」對話盒中,在「儲存格內允許」中選擇「清單」,並在「來源」的位置,填入「男,女」,也就是將所有想要出現的文字數值,用「,」分號來做區隔,而組合而成的一個字串,最後再點擊〔確定〕即可。

excel_combobox-3.jpg

Step 3. 經過這樣的設定,只要點擊每一個「性別」的儲存格,就會出現一個下拉式的選單,而這個選單內的內容,就是我們剛才所設定的「男」和「女」,如下圖:

excel_combobox-4.jpg

相信透過這樣的選擇,就不會有輸入錯誤的情況發生了。

除此之外,在「生日」欄位,也可以利用此「資料驗證」的方式,來避免使用者手誤,而填入太誇張的日期,操作方式如下:

和之前一樣,同樣選取想要設定的儲存格,接著在「儲存格內允許」中選擇「日期」,並且設定「開始日期」及「結束日期」,如下圖:

excel_combobox-5.jpg

從這樣的設定之後,只要你在生日這個欄位,沒有輸入介於你所設定的日期時,就會出現「您所輸入的值不正確」的錯誤訊息。

excel_combobox-6.jpg

從以上的兩個例子可以了解到「資料驗證」的好用之處,而在「資料驗證的準則」中,除了「清單」及「日期」之外,還有「任意值」、「整數」、「實數」、「時間」、「文字長度」及「自訂」可供設定。

碰到會常變動及新增的清單資料怎麼辦?

相信您對這個好用的功能已經躍躍欲試了,可是至今,我們輸入的「清單」都是很「簡單」,而且是「不變」的,但要是我們輸入的,是一個產品資料表呢?相信那可就麻煩了吧。

你能想像要在一個充滿分號的字串中,維護數以百計的產品項目嗎?

麻煩到包你不想再使用這個「資料驗證」的功能,但這可就失去這功能原本的立意了,但其實建立清單的方式,是可以改變的。

你一定是想,若清單也可以直接建立在「工作表」上的「儲存格」中,那「清單」就更好維護了吧?

沒問題,清單也是可以另外建立在工作表上的喔,首先,一樣開啟「資料驗證」,接著我們不直接輸入想要的清單文字,而是點擊「來源」欄位右邊的小圖示,如下圖:

excel_list-1.jpg

接著選擇「文具清單」中的這些「文具項目」,如下圖:

excel_list-2.jpg

最後這個「來源」就會以「儲存格範圍」的方式,來設定來源,如下圖:(當然,我們也可以直接填入儲存格欄位的值,若您會填的話)

excel_list-3.jpg

最後在左方的「文具用品」中,只要點選了裡面的儲存格,就會出現右方清單中的項目,日後若是文具用品有新增的話,只要再去修改「儲存格範圍」的值即可。

excel_list-4.jpg

但您有沒有看出,這有一個非常「致命」的問題,我們「通常」不會把「清單資料」,和要輸入的儲存格建立在「同一工作表」中,而是會將這些清單資料,獨立到另一個工作表裡,這樣版面才會比較整齊乾淨,但要怎麼做呢?

結合「名稱管理員」的「清單」資料驗證

由於在資料驗證中輸入儲存格範圍時,會有需同一個工作表的限制,因此這個限制要透過「定義名稱」來解決。

Step 1. 首先,我們先在其它的資料表中,建立這個「文具清單」的項目,接著選取這些項目,並點擊「公式」裡的「定義名稱」,接著在跳出的「新名稱」對話盒中,在「名稱」處,輸入您想要定義的名稱,我們這填「文具清單」即可,最後再點擊〔確定〕,操作如下圖:

excel_name_manager-1.jpg

經過這樣的設定,您若是點擊「名稱管理員」時,就會看到您所設定的「名稱」已經出現在這裡面了。

excel_name_manager-2.jpg

Step 2. 定義好名稱之後,同樣的操作來到「資料驗證」中,這時我們就可以直接在「來源」中,直接填入「=文具清單」這個名稱(注意有要「=等於符號」),如下圖:

excel_name_manager-3.jpg

Step 3. 接著就是見證奇蹟的時刻了,你會發現,不需要在同一個資料表,也能有相同的效果出現,但要注意的是,將來若清單中的項目有增減,你就要使用「名稱管理員」來變修改一下所定義名稱的範圍設定。

excel_name_manager-4.jpg

在我舉出這一連串的例子,相信您應該能充份了解到「資料驗證」的使用了吧,實際上,還有另一個使用VBA程式來製作選單的方式,這個由於比較困難,因此我將來會獨立出一篇文章,來做這方面的教學。

Powered by WordPress & Theme by Anders Norén