March 15,2008 11:28

Excel 下拉式選單

  有時候在Excel輸入資料時,有些資料是重複的,例如性別只有男和女.對於這些資料,如果每次都手動輸入或是copy & paste都挺浪費時間的.所以我們可以考慮利用下拉式選單(如下圖)的功能,直接按下箭頭來選取這些重複的資料.

     

製作下拉式選單的方法:

1.選擇欲產生下拉式選單的儲存格(例如A1)*

2.然後在空白地方(例如C1~C5的儲存格 )輸入預設的文字資料(也就是重複的資料)

3.到工具列點選  「資料」→「驗證」→「設定」→「資料驗證準則中」**

4.在「儲存格內允許」的下拉方塊選擇「清單

5.接著會出現「來源」要你指定參照位置(即選擇資料來源,本例中是C1~C5)

6.按確定

7.接著A1儲存格旁邊就會產生箭頭.按下箭頭就會看到可供選取的資料了.


注:
*如果是整個欄位(例如A欄)都要使用此下拉式選單,那麼請選擇整個A欄.選擇A1儲存格的話只有該儲存格可以使用此下拉式選單

**英文的名稱隨後補上

  • cheeseng 發表於樂多回應(41)引用(4)電腦。科技編輯本文
    樂多分類:日記/一般切換閱讀版型 │昨日人次:23 │累計人次:132968
     

    引用URL

    http://cgi.blog.roodo.com/trackback/5699945
    引用列表:
    有時候在Excel輸入資料時,有些資料是重複的,例如性別只有男和女.對於這些資料,如果每次都手動輸入或是copy
    Clouds【生命列車:Excel 下拉式選單 - 樂多日誌】 at September 29,2014 20:32
    有時候在Excel輸入資料時,有些資料是重複的,例如性別只有男和女.對於這些資料,如果每次都手動輸入或是copy
    Amazing Lightning【生命列車:Excel 下拉式選單 - 樂多日誌】 at September 29,2014 21:08
    有時候在Excel輸入資料時,有些資料是重複的,例如性別只有男和女.對於這些資料,如果每次都手動輸入或是copy
    Purple【生命列車:Excel 下拉式選單 - 樂多日誌】 at September 29,2014 21:41
    有時候在Excel輸入資料時,有些資料是重複的,例如性別只有男和女.對於這些資料,如果每次都手動輸入或是copy
    Amazing Lightning【生命列車:Excel 下拉式選單 - 樂多日誌】 at September 30,2014 06:32

    回應文章
    這學過,記得那時候老師說她的名稱不叫\'下拉式選單\'...


    【cheeseng 說】
    哦....正確名字我就不知道拉.我是用下拉式選單來搜尋到的
    | 檢舉 | Posted by bAyI at March 16,2008 16:52

    為了報答你的教學
    擅自弄了excel2007的作法(有少少不同)
    用小畫家弄弄而已是有點粗糙啦
    不過應該還是可以看清楚
    請笑納 XD

    「http://blog.roodo.com/evelynchong/79e76e8a.jpg」

    p/s: 順便推薦一下,office2007系列很好用喔!


    【cheeseng 說】
    不錯,看來我也要轉用office 2007了
    | 檢舉 | Posted by 布丁許願 at March 21,2008 11:27
    | 檢舉 | Posted by addd at December 12,2008 23:11

    謝謝你的資訊整理~~
    幫你補上英文:)

    3.到工具列點選 「資料」(Data)→「驗證」(Validation)→「設定」(Settings)→「資料驗證準則中」(Validation criteria)**

    4.在「儲存格內允許」(Allow)的下拉方塊選擇「清單」(List)


    【cheeseng 說】
    感恩.....
    | 檢舉 | Posted by 海豚 at January 29,2010 09:27
    手動輸入來源也可以,只要用逗號分隔開來就可以了
    | 檢舉 | Posted by 山本健太 at May 15,2010 00:42

    很棒ㄚ...泥果然是很有底子的~
    | 檢舉 | Posted by 阿里山來的啦啦&水里來的牛牛 at November 4,2010 14:40

    謝謝分享~ 寫得很清楚~!! 剛好用上~! 感恩~!!!
    | 檢舉 | Posted by freedom at November 8,2010 10:14

    謝謝分享
    如果清單資料來源在不同工作表,該如何解決這個問題?或是要如何用其他方法解決?
    | 檢舉 | Posted by 王郁明 at February 14,2011 09:47

    要如何用EXCEL做進貨和銷貨帳請簡單舉例
    | 檢舉 | Posted by aut0804544 at July 11,2011 22:57

    excellent article.感謝分享,很實用
    | 檢舉 | Posted by gene at August 28,2011 09:20

    >如果清單資料來源在不同工作表,該如何解決這個問題?或是要如何用其他方法解決?
    那就把清單資料「定義名稱」(ex 定義為"book")
    然後驗證一樣選擇清單,來源用「=book」,就可以了~
    | 檢舉 | Posted by 路人甲 at September 8,2011 15:46

    如果臨時要寫入僅用一次的文字有辦法不用用到下拉式選項嗎??????
    ---------------------------------------------
    版主回覆:
    你是指在選項中加入只使用一次的文字嗎?
    | 檢舉 | Posted by Sandy at May 11,2012 09:10
    謝謝分享,很受用 ^^
    | 檢舉 | Posted by m at October 2,2013 15:32
    小贤:我昨天帮家里的狗洗澡,结果它……。死了。八力:洗澡?不可能吧?小贤:嗯,如果不是洗衣机要了她的命,那么就是烘衣机了……。八力:*@!$
    www.38f88.info http://www.38f88.info
    | 檢舉 | Posted by www.38f88.info at October 26,2013 08:46
    上高中的时候,邻居家买了一台新电视机和VCD,但是他们家不会装就请我爸去帮忙。我也跟着去了,当时屋里有很多人,我爸到那边三下五除二搞定以后,一打开电视只见图像没有声音。我见了之后便大叫了一声:“肯定是音道没插好!”说完之后我就后悔了,连死了的心都有了!暴汗!
    | 檢舉 | Posted by 鸿博娱乐城 at November 6,2013 14:22
    一对新婚夫妇,晚上动静很大,弄得四方邻居很有意见,有一家邻居实在不能忍受,就在其门口留一纸条,上写 晚上的动静小点可以吗? 主人见了,在下面批示: 我家的猫这几天在叫春,请忍耐几天吧。 次日,邻居又留一条,上写: 请问你家养了几只猫?
    | 檢舉 | Posted by 博彩网新时代 at November 6,2013 14:22
    昨天晚上回家忘记关门,而家里只有我和老公两人,天热你懂的,家里没其他人你懂的,所以裸奔了,在洗手间与房间之间来回穿梭了一个小时左右,也就是说经过那扇没合上的防盗门无数次,客厅的灯偏偏坏了一直没修,偏偏lz神经大条一直没发现,当准备睡觉老公去检查门是否反锁时 后果你懂的 给我一顿好捶啊,现在想起都还是想死想死的啊,但愿没人经过我家门口,但愿没人看见 让我再哭会
    | 檢舉 | Posted by www.hanfengshanghai.com/bbs/indexjin.aspx at November 6,2013 14:22
    | 檢舉 | Posted by ugg femme classic short at November 11,2013 23:32
    Aw, this was a certainly nice post. In concept I would like to put in writing like this moreover - taking time and actual effort to make a pretty superb article?- but what can I say?- I procrastinate alot and by no indicates seem to obtain some thing performed.
    cheap sneakers http://cheapjordansjja.fotopages.com/
    | 檢舉 | Posted by cheap sneakers at November 19,2013 18:51
    This web web page is actually a walk-through for all of the information you wanted about this and didn't know who to ask. Glimpse here, and you will undoubtedly discover it.
    jordan shoes for sale http://cheapjordansjjk.blinkweb.com/
    | 檢舉 | Posted by jordan shoes for sale at November 19,2013 18:52
    It is difficult to acquire knowledgeable individuals on this subject, but you sound like you know what you're talking about! Thanks
    sneakers for cheap http://jordanslnr.freeblog.biz/
    | 檢舉 | Posted by sneakers for cheap at November 19,2013 18:52
    Aw, this was a really nice post. In concept I would like to put in writing like this in addition - taking time and actual effort to make a fairly fine article?- but what can I say?- I procrastinate alot and by no indicates seem to obtain some thing done.
    jordan retro 11 http://cheapsneakerslyj.blogshells.com/
    | 檢舉 | Posted by jordan retro 11 at November 19,2013 18:52
    I'm impressed, I should say. Genuinely rarely do I encounter a blog that is both educative and entertaining, and let me tell you, you've got hit the nail on the head. Your notion is outstanding; the issue is something that not enough people today are speaking intelligently about. I'm very pleased that I stumbled across this in my search for something relating to this.
    jordans free shipping http://www.horsenicker.com/members/profile/2558/blog-view/custom-jordan-sneakers-for-christmas_33688.html
    | 檢舉 | Posted by jordans free shipping at November 19,2013 18:52
    There are absolutely a whole lot of details like that to take into consideration. That is a fantastic point to bring up. I provide the thoughts above as general inspiration but clearly you'll find questions like the one you bring up exactly where by far the most significant factor are going to be working in honest beneficial faith. I don?t know if most effective practices have emerged around items like that, but I am sure that your job is clearly identified as a fair game. Both boys and girls really feel the impact of just a moment's pleasure, for the rest of their lives.
    cheap sneakers http://www.flixya.com/blog/5313355/-Proven-Tactics-for-Creating-a-Profitable-Business-Website
    | 檢舉 | Posted by cheap sneakers at November 19,2013 18:52

    | 檢舉 | Posted by 杜文琪 at November 30,2013 14:01

    | 檢舉 | Posted by 杜文琪 at November 30,2013 14:01
    | 檢舉 | Posted by UGG Bailey Button 1873 Black Boots Sale at December 20,2013 00:11
    I think other website proprietors should take this website as an model, very clean and excellent user friendly style and design, as well as the content. You’re an expert in this topic!
    | 檢舉 | Posted by Faith at December 20,2013 21:27
    If you dont mind, exactly where do you host your blog? I am looking for a good quality web host and your website appears to be quick and up all the time
    | 檢舉 | Posted by Abigail at December 25,2013 03:38
    | 檢舉 | Posted by uggs paris prix at January 17,2014 20:22
    An impressive share, I just given this onto a colleague who was doing a little analysis on this. And he the reality is purchased me breakfast since I discovered it for him.. smile. So let me reword that: Thnx for the treat! But yeah Thnkx for spending the time to discuss this, I really feel strongly about it and adore reading additional on this topic. If possible, as you become expertise, would you mind updating your blog with a lot more details? It's extremely valuable for me. Major thumb up for this blog post!
    etoile isabel marant http://www.shop-isabelmarant.com
    | 檢舉 | Posted by etoile isabel marant at January 18,2014 18:41
    absolutely adore companions don't usually seem to be connaissance won't designs avons fait in addition to durante Pina Colada sous sweetheart soleil certains Cara?bes. Même del tant bahsettirrim, nous-mrrmes styles sommes engagés à consacrer bient?t réflexions storage containers . aider leur reason décoratif.
    | 檢舉 | Posted by http://www.nedaycrafts.co.uk/ at March 26,2014 02:04
    |carolina|arizona|idaho|the state of california} traditionnelle galette tutu Portman porte avec certains perles lourd. D'autant and ils pueden seront battus difficile dom offrir seul couronne some sort of métal white plug young child , sound Swan surface Noire.
    | 檢舉 | Posted by http://www.mamutech.de/ at March 26,2014 02:04
    the actual haut checklist démarrage, ces pieds arbore à peu près certainement incroyables vêtements wishes ayant l'apparence ainsi que plusieurs manteaux. Ce seront certainement ce meilleur costumes costume adapté pour signifiant multiples personnes à travers vos tenues a deluge leur assortiment {pour|put|serve|fill|dump|add|strain|dans le but de|storage containers .
    | 檢舉 | Posted by http://www.equationonline.co.uk/ at March 26,2014 02:04
    Styles venons à Richmond incline à partir d'une semaine paradisiaque à l'intérieur République dominicaine egalement déjà, auparavant derniers comprehensive dom sable ont déjà été secoué delaware Birkenstock, ou bien derniers vestiges south rhum purgés environnant ces organisme, variations dans travail. Climate, florida vérité opleve trouve rrtre, search engine trouve rrtre l'ordre s l'essence autant cual {chicago|chi town|chicago, il|detroit|dallas|chicago.
    | 檢舉 | Posted by http://www.thehoog.co.uk/ at March 26,2014 02:04
    Thanks Brian to get a good word of advice, also non-developers just like us should be able to draw this away: )This is really essential any multi-international web site in terms of SEARCH ENGINE OPTIMIZATION.
    | 檢舉 | Posted by Louboutin Pas Cher at April 26,2014 16:30
    This would seem similar to often the RIAA manipulating the authorized system for any easy greenback when compared with all of them truly attempting to quit pirating. Really just like new music is actually typically the the lure for true profit-making machine. My partner and i wouldn't end up being surprised when they expose their very own new music about the net.
    While this individual definitely should possess downloaded the music, the one thing the justice need to observe here's how damaged often the "victim" is along with assign treatment as necessary.
    If it's several sounds, throw out these people $7 us dollars. That could be justice. Not really this particular expensive amount.
    | 檢舉 | Posted by casquette gucci pas cher at April 29,2014 01:13
    sir
    some tissue are definitely not editable. how can all of us revise these The Outstanding Software program,,,,, It has the extremely helpful. Could you suggest for enhancing a selection of "Person liable for Sighning".
    | 檢舉 | Posted by louboutin pas cher femme at April 29,2014 01:13
    magnificent points altogether, you simply gained a new booklover. Come again? would you suggest about your post that you complete a few existence ago? Some positive?
    | 檢舉 | Posted by Ellie at May 13,2014 20:27
    FYI: My spouse and i tried your own the particular BlogWealthMaker web link and also Pattern Micros hindered this some sort of destructive site thus be careful!
    | 檢舉 | Posted by Louboutin Femme at May 30,2014 21:42
    For reasons unknown, We have such a hassle using these awful captcha limitations. Thus delighted this specific place doesnt' put it to use! You could scarcely read these individuals!
    | 檢舉 | Posted by sac longchamp pliage at July 9,2014 13:35