EXCEL的資料驗證 2，下拉式選單與多層下拉式選單

來跟大家介紹一下這個 Excel 資料剖析中的這個清單功能 這個是一個很棒的功能 我們在做很多事情的時候 你就可以考慮是用這種清單的方式來處理 因爲至少你可以不用打字 用拉的就可以解決很多問題 那我們在上一個影片有關於這個驗證的時候 我們有提到 你要整條這樣的選項 譬如說你這邊需要一個下拉 整欄都需要一個下拉式選單的時候 就可以選起來 那我選資料 選驗證 那我們這個時候就選清單了 如果內容不多 你在這邊改就可以 好 譬如說性別你就打男跟女 對不對 我打一下 譬如說性別是男 對不對 來 那中間用逗號分開 或者你要更多也可以 用逗號分開 好 那這個時候你看 那就是 男女 對不對 每一格都可以加男女 你就很方便 就可以至少可以不用這個 一格一格打 當然這類的資料非常多 那你可以譬如說學歷 也可以什麼高中大學研究所 或者是說有時候我們 譬如說有婚姻狀態 已婚未婚 好像這種 簡單選項你就可以這樣做 那你如果要大量的選項 那你就可以這樣做 譬如說你就可以選一個 譬如說我們S這一欄 你就可以這樣選 你把整欄選起來 不用選到整欄 我想應該不用選那麼多吧 大概一個範圍 多選一點沒有關係 蠻確定 那這個時候你內容就可以打在這邊 譬如說好 學歷有 國小 好 國中 好 就可以打在這邊 高中 好 專科 好 大學 好 碩士 博士 對不對 好 你可以打在這邊 那你打完以後這邊就當場就可以拉了 對不對 那當然就可以拉 你也很方便 對不對 大學就大學 專科就專科 碩士就碩士 對不對 博士你就博士 你就很方便 可是這樣會有一些問題 我們來稍微討論一下 第一個就是 按的那個給你偷改 給你這個不要再給別人用 就給你偷改 所以這個時候其實有很多方法 可以防止這個事情發生 譬如說你可以建一個新的工作本 我先講一下 你的版本如果比較就是2007 可能沒辦法做這個事情 因為 到了2010以後 這個 清單的資料來源是可以跨過工作表 譬如說跨過這個工作表 那我這個時候 我在設的時候 資料在驗證的時候 我就怎麼辦 我就可以選擇另外一個工作表的內容 我可以選擇這個工作表2 我選這裡 然後 來 你看這個時候我是不是一樣可以這樣拉 我這邊就可以刪掉 對不對 那你一定是說你這樣弄人家不會在這邊弄 對 你講的好像有道理 你可以把它隱藏起來 甚至你可以把它保護起來 你可以用保護工作表的方式把它保護起來 對不對 保護工作表 對不對 設一個密碼12345678 我現在是亂設 絕對不可以設這樣 12345678 你把它保護起來 保護起來以後 接下來怎麼辦 你把它隱藏起來 那隱藏起來還可以拉嗎 照拉讀 對不對 可是當別人要修改的時候 他就 你看 有一張未隱藏的工作表 那你看 你這個時候要不要顯示隱藏工作表 取消隱藏 工作表2 然後你有保護 因為我們剛剛是我們有設保護 如果你存起來再開的時候 他就馬上就怎麼樣 馬上就出現一樣密碼 那這個時候人家就沒有辦法去改你的內容 對不對 所以這個可以用這個方法來做 那這個東西除了會有這個需求 其實有有一件事情需求就更有意思 所以我們來看一下 這個 下拉式清單有時候我們遇到一個比較有趣的問題是這樣 來我們來看我我先做了一部分 因為我怕到時候這個大家那個 我先把這邊刪除掉 好是這樣 這個時候大家一定會想到一件事情 如果地址可以這樣拉 對一定很方便 所以我特別在這邊先做好一部分 比如說我們有臺北市、新北市、臺中市 比如我們還有高雄 對不對 高雄 我已經打不出來 高雄市 臺南市我不要打太多好了 好 那這個時候 我在做這個下拉式清單的時候 我這邊就是要顯示名稱 對不對 所以我就直接怎麼樣 我資料的驗證的來源 我就可以選擇什麼清單 來源就哪裡 就這些縣市名稱 高雄、臺南、花蓮、屏東 好說多拉一點沒關係 反正沒價錢 好那這時候其實很方便了 對不對 臺北市 新北市 臺北市 桃園市就很棒 現在比較麻煩的就是 你接下來這邊 你新北市你好歹要出現新北市的區 對你一定要出現新北市的區 所以說這個時候 你就一定要怎麼樣 把這個新北市的區 把它弄進來 對不對 所以你這邊最好就是怎麼樣 可以出現這個 新北市的區 那這樣你才會方便 對不對 那這個時候你要下拉式清單 那你要怎麼做 這個時候就有意思了 你選清單 很多人說新北市 新北市不是在這裡嗎 對不對 你只把新北市這個 哇一大堆 這應該選起來 好像也對了 好 可以 那我這邊如果改臺北市 拉出來還是新北市 你沒有被人家罵的隨便 這樣就沒有意義 對不對 每一個拉出來都是新北市 現在的重點就是 你這邊如果出現臺北市 這邊應該拉出臺北市的區 那纔是合理的 這邊如果拉出是新北市 那我覺得應該是新北市的區 對不對 桃園市就桃園市的區 花蓮縣就花蓮縣的鄉鎮 那你這樣你纔有意思 你不能說我每次都去改 那這樣你就沒有意義 所以這個時候怎麼辦 這個時候就很有意思 你這邊要出現什麼 這個區要出現什麼 要看前面這個地方 對不對 那很說那簡單 那我們就 有人就會想到這個辦法 那你看就 就等於什麼 很多人就那我們就 就來給他等於 來 你就給他等於 隔壁這個 對不對 好像也對 你左邊是臺北市 你這邊就臺北市 你這邊新北市就新北市 對不對 而且我剛剛沒設好 他每個都變臺北市 對不對 這個時候你要注意 你只能鎖藍不能鎖烈 因為我們在做驗證的時候 如果你要整藍做的時候 你不能鎖烈他才會自動的 譬如說我在A1的時候 我B1是對應A1 你如果沒有鎖烈 他到B2才對應到B2 再對應到A2 你看我這個時候 新北市桃園市 我這樣還是沒解決問題 為什麼 這樣有做跟不就跟沒做一樣 可是這個時候有一件很有趣的事情 就要發生 你注意看 臺北市的 區 是不是就是 這一塊 對不對 沒有錯 這個一定沒有問題 臺北市的區域定義就是這一塊 對不對 那你要不要做一件事情 Excel有個功能叫做定義名稱範圍 就是我可以把這一塊取一個名字 那取一個名字有什麼用 取一個名字其實很好用 我們如果能夠把一個範圍取一個名字 以後我們在運算的時候 你在 參照的時候 你就根本就不用記什麼A1到什麼 A1貓號什麼到C53 都不用 你只要記住那個名字就可以了 就很快 這個事情非常有意思 我來做一個檔案給大家看 這個參考一下 參考跑馬就好 譬如說我把這一段取一個名稱 那怎麼取名稱呢 選起來 你就可以在公司裡面定義名稱 你不要叫國文好了 叫AAA 注意一下你定義名稱 你第一個字不可以是數字 不可以使用符號 那我這個時候如果要把這一塊加起來 其實最快的方法就是什麼 四個碼 什麼 AAA 他保證你把這一塊都加起來 馬上加起來 你根本就不用記那個範圍 好這很方便 那尤其是在很多人很喜歡用 lookup這個函數 或者Vlookup Hlookup 每次那個範圍記得頭或腦上 你要不要定義名稱範圍 你在參照的時候是非常非常的方便 而且名稱範圍我們後面會提到這個 後面有機會跟大家提這個事情 這個定義名稱的話也可以做個動態 他可以隨著你的資料長大慢慢長大 或者是你乾脆使用表格也可以 我們後面有機會再跟大家談表格的問題 那這個時候重點就來了 我們在做這個東西的時候 我們是可以把這塊取一個名稱 取一個取一個名稱要取什麼名字比較好 這不就臺北市嗎 所以我就把它取一個名稱叫做臺北市 這我剛剛已經取過 你看這一塊他叫做臺北市 那這樣有什麼好處呢 好我們剛剛在這邊的時候 也做了一件很有趣的事情 我現在呢 來 我在驗證的時候 你看 我選擇清單 對不對我要等於隔壁的 隔壁不就等於臺北市嗎 對不對 那我如果把那一塊的名稱定義成 臺北市的時候 其實有一個很大的好處 就是他可以去找一個定義名稱 叫做臺北市的範圍 那他就會把裡面的資料帶到這邊來 讓你可以下拉 那你寧願說你不是定義的 他不會出來 就不會出來 因為你現在是等於左邊的那個內容 A1的內容 所以你拉下來就是A1的內容 你要跟他講 你要等於左邊跟左邊同名的資料範圍 就是說我現在要找一個 叫做臺北市的資料範圍 那這個時候怎麼辦 你只要給他一個函數就有一個叫做 indirect i-n-d-i-r-e-c-t 你把它掛掛起來 這什麼意思 就是說 我現在 我要做清單 那我的清單的來源 資料來源在哪裡 就是叫做 左邊的你左邊叫什麼名字 我的來源就是跟他同名 我左邊現在叫臺北市 所以我要去找一個 叫做臺北市的資料來源 就像剛剛我們有定義的 你看這樣是不是就有點大安區 對不對 松山區 可是這邊新北市不行 因為我們還沒有定義一個範圍叫新北市 來趕快來做一做 對不對 於是我們就可以把這一塊怎麼樣 哇這超長的 新北市怎麼這麼多 你可以把這一塊選起來要怎麼樣 我們公司這邊也在定義名稱 對不對 好你就叫做 新北市這個就容易了 對不對新北 一個字也都沒打出來 新北市 好你注意看 我這個時候定義成新北市的時候 你注意看 應該是這個 你看到這邊的三支區 對不對 中和區 那你注意看我這邊如果改成臺北市的 你看他是不是變臺北市的區 我這邊如果改成新北市的時候 我這個是變成新北市的區 哇方便了 對不對 所以有空桃園市再給他弄一下 不然怎麼辦 對不對 好像沒有桃園市的資料 有啦有啦 好 那你就把 定義一下名稱 這個叫桃園市 對不對 好來 臺中後面給他弄一弄好了 好臺中市對不對 好來 所以這個時候你叫定義名稱就可以 然後呢 來看一下 那這樣子就可以拉 來來來可不可以用馬上就知道了 來桃園市給你看 那這邊就桃園市的區 對不對 好我們一樣平鎮對不對 八德大園我們就改臺中看看 臺中市對不對 哇臺中市的區通通都出來 那你不就很方便嗎 所以我們在這邊只要 這樣做就可以了再給大家看一次 來資料點選對不對 清單好然後你跟他講說 我要看左邊的 左邊如果叫新北市呢 那我就去找一個資料範圍 名字叫新北市的 那我就找到了對不對 好那你定義好名稱範圍 這你可以檢查那個名稱管理員 你看就臺中市臺北市 一大堆對不對 好 好我現在來補充檢查 很多人就說哦我知道 你這樣做不錯 靠我這個找到死了 好一個慢慢打不要慢慢打 我先講一個爛的方法去google 那google上面一定會有這個 郵遞區號快查表對不對 好 譬如說我們現在用的是高雄市 對不對 好沒有高雄 對不對 好這高雄市就快了對不對 我跟你講他給他選起來比較快 不用這麼多 我給你們把它複製起來 郵遞區號沒有沒關係 他為什麼不排直排直就是剛好 複製起來 你到excel裡面來貼 找盡量找那個排直的 你排那個橫的你忙 貼上 好這個時候你就說哇靠排成三排 排成三排還不簡單 給他弄成一排就好了 對不對 好你貼的時候不要直接貼 我好心的勸你不要直接貼 怎麼貼 你用選擇性貼上 你看剛剛是html貼成文字 他貼成文字他就是一排 好可是你現在說 看一下一排還不是郵遞區號 沒關係 我們有資料剖析 對不對 你跟他講固定換這個你看注意一下 那馬上就切開 完成 你拿這一塊來用就好了 其他就不要理他 那留著也沒用對不對 可是現在重點又來嘍 我這個貼過來的時候 你看哦是這個嗎 貼到哪貼到這個 哎我要貼橫的 貼橫的就貼橫的 你剛纔不是複製的嗎 還是用選擇性貼上給他轉字就好 貼好 然後貼好順便把它定義名稱範圍啊 對不對 都已經搞到這種程度了 好 你就可以怎麼樣 定義名稱範圍 好這個時候你就定義叫高雄市 對不對 趕快來試試看可不可以用 這個很重要對不對 高雄市哎你看高雄市的區你就出來哇 大社岡山路 阿聯天寮煙草橋頭 你看千金營養 鹽埕啊通通都可以用 那你這個就很方便對不對 就快速的就可以這樣子 那這個東西你還可以再繼續往下做嗎 可以 好 譬如說你可以把那個路名再把它弄出來 對不對 好譬如說啊新北市平溪 對不對 那這個時候你就可以做一個叫 平溪區的路名 好譬如說我們用我們用臺北市來做 臺北市那臺北市你可能很多區 臺北市有市林區啊對不對 你這個時候 其實就應該要在這邊再做一個表 這個這個表你要做多久 所以如果你有空 可以把全臺灣都做一遍對不對 那你這個時候你要好做的話 你就把這個地方再把它佈置過來 一個區一個區做對不對 好 那你就這個時候你還可以貼成紙 選擇性貼上 你還可以貼成紙的啊 轉字一下啊 好貼市林區有什麼中正路啊 什麼東山北路五段啊 這個你可以去那個 其實郵局好像也有也有這個字 郵局好像也有這個字 這種東西啊到處都有啊 好譬如說什麼文林路啊 成德路啊這個 ok 好那你這個時候就把這一塊 選起來 對不對 好 然後呢 定義名稱 好這叫做市林區啊 好 我沒有定義到啊 來定義名稱 好你可以把它叫什麼呢 定義不到 定義名稱啊 你可以叫做市林區啊 好像有點當掉的感覺 那定義成市林區以後你就很好做 好名稱關聯這邊 完全都不出來 反而是當掉了 那這個時候你 你如果這樣做的話其實你就很毛病 以後以後你拉出來 靠我一定要給他做成功 那你就可以直接使用這個東西 啊這個叫市林區對不對 市林區 很確定 好那我們這個時候一樣 在這邊呢 我們就可以來試試看對不對 好我們這邊一樣什麼 定義名稱範圍 啊不對啊驗證 好我們一樣把它驗證 好來選擇清單 對不對 這個時候你的 indirect 就是等於什麼 好就等於 這一格哦 注意哈索然不要索列 他才會上下自動對應 好試試看哦 很確定 好你看市林區的讀者組 那你不就很方便嗎 那就超方便 那這個東西會有一個問題就是 呃 有一些區的名稱是像 有這個情況嗎 像臺北有中正區 基隆也有中正區 你遇到這種情況 最好的方法就是 像譬如說有一些比較特殊的 區名如果會重複的說你在 驗證的時候 你取的名稱你不要很單純的 就是怎樣 就很單純的看這邊 你可以把這兩個加起來 就是說我們在 indirect 的時候 這個你參考反正機會不大 那萬一你遇到的時候 你至少人生有一個方向對不對 好 你知道驗證的時候其實可以這樣做 你要加哪裡 你要把這個區名 加進來 臺北市把它加起來 用 AND 把它 AND 起來 然後呢 把這個 $把它弄掉 那這個時候他會跟你講錯誤 不要理他 那你現在唯一要做的就是 你在這個定義這個 市林你不要定義成市林區 你要定義成什麼 臺北市市林區 那你就不會有問題了 區名如果其實還不少 好像基隆 跟臺北有一些這個 重複 沒幾個我查過臺北市市林區 好那你這樣你就安全 市林區 好就不會發生這個問題 你看這個時候來看一下 他就出來了 臺北市市林區 其實這個東西很有意思 有人是這樣做了 這個很單純的這個做給大家參考 看看就好 有人就假髮精靈 這邊還可以設什麼級段對不對 然後這個級號 對不對 好 所以這個時候你就譬如說 這個是段對不對 好其實段我跟你講段你可以做在地址 裡面 好比如中南北有二段三段四段五段 對不對 好那個在 這邊下拉前面的然後 幾項幾號對不對譬如說 這邊是項 這邊是號 好 那這個時候你就可以這樣做 你就可以弄一個函數 這函數就簡單了 對不對你弄一個文字的函數 對不對 好這個 這個最快了對不對 你跟他講第一段是這個對不對 第二段 對不對第三段是這個對不對 第四段是 這個 好 那這個時候後面如果 要項你就打一個項 對不對好來 好好就太過頭了 太過頭了 好那 幾號對不對 好 好那你後面就看你要幾樓對不對 樓在這邊對不對 啊樓在這邊 這邊打樓 好比如 好三樓 然後就三樓 那你說可是我沒有號啊 好 哎 這個時候就有意思了啊沒有樓了 他沒有項 這個時候你在這個函數在做的時候 你可能要改一下 好這個時候你就可以改 好譬如說啊我有些地方我沒有項 對不對 好那你這個時候你就這邊可以再加 一個一筆函數對不對 好就跟他講說如果啊 來 如果 這一個對不對 是空的 對不對 好 那怎麼辦 那我就沒有項啊對不對 好這個用簡單的函數就可以 就這樣啊如果那個不是空格的話 那我就會有項 好那這樣就好了 這樣試試看看可不可以用 這個好處理啊你看 這個時候把他殺掉對不對 你看就沒有項了 對不對 好那沒有樓一樣 號一定會有啊 我想這個沒有號還得了對不對 好那這個時候你這邊也可以這樣做 好這個我們做做參考就好了 好來 這個是一定要在函數的設定 來我們來看一下 那這個時候我也一樣啊對不對 好來這個 樓的部分 靠我們一拉就100多 真是太好了 好你看這個 幾號幾樓你也可以一樣這樣做 對不對 就讓他檢查對不對 if 對不對 如果勒 好這個 幾樓這一格呢 是空的空就打兩個雙一號啊 那我們就怎麼辦 我們就出現 空的那如果沒有空那就只有樓對不對 好好看看的話 透氣掛滿 你看這個時候是5號3樓對不對 我把這個 3樓拿掉你看他就沒有沒有樓 好你要有項就打 145項 你看145項5號 對不對什麼14樓 對不對啊你如果不要樓拿掉 你看他就不會有樓 我不要有項拿掉 號應該不要號一定你看 他就不會出現像那個字 那你就方便了 就是你全部通通給他拉一拉 對不對啊 大家資料就給他弄一弄對不對 好好這個時候你就比如說 我現在亂弄 比如說 好啦5項4號8樓對不對 5項4號8樓 那你這時候打這個地址 你不就是超級方便 好所以我們這個時候就可以用這種 方式怎麼樣 用用 indirect 的方式再給大家看一下啊 好來 我們就可以這樣做這種多重選單 啊非常非常方便啊 這個就是多重選單的用法啊