ARTICLE · 1349780
Excel下拉列表怎么做,一张不会填错的表
Excel下拉列表怎么做,一张不会填错的表周五上午9点，你收到各部门报上来的表。部门那一列五花八门：华东、华东地区、华东区。汇总的时候，SUMIFS把它们当成三个不同的部门，结果全是错的。 问题不在汇总公式，在填表的时候就没锁死选项。填的人怎么写都行，汇总的人就得一个一个改。 第一个原因，同一个意思有三种写法。华东、华东地区、华东区，在人看来是一个部门，在SUMIFS看来是三个不同的条件，汇总结果自然全错。 第二个原因，错在填表时，改在汇总时。填表的人随手一写，汇总的人要一个一个对、一个一个改，表越多人越多，改到最后还不如重填。 第三个原因，公式救不了写法。SUMIFS、COUNTIFS再熟，也分不清“华东”和“华东 ”是不是同一个部门，因为它们在Excel里就是两个不同的文本。 所以解决办法不在汇总公式，在填表的时候就把选项锁死。选项只有三个，填的人只能从三个里选，写法就统一了。 选中要填部门的那一列，点数据菜单里的数据验证，允许选序列，来源里写选项，用英文逗号隔开：华东，华北，华南。 确定之后，那一列的单元格旁边会出现下拉箭头，只能从这三个里选，手打别的写不进去。 月份那一列也一样：1月，2月，3月，写进来源，填表的人就不用自己敲月份了。 注意两个细节。第一个细节，逗号要用英文逗号，中文逗号会被当成选项的一部分，选出来就是错的。第二个细节，来源里的选项不要带空格，“华东 ”和“华东”在汇总时是两个部门，做选项之前先用TRIM清一遍。 选项只有3个，手写在来源里就行。选项一多，或者以后要改，就别手写。 单独找一列当选项区，比如在表边上另起一列写华东、华北、华南，来源里引用那一列。 以后要加一个“华中”，只改选项区那一列，所有下拉列表跟着变，不用一个一个单元格改来源。 选项区放哪有讲究。放在同一张表的边上，找得到、改得了，又不会被填表的人误删。不要放在另一张表里藏起来，别人接手这张表的时候找不到选项区，改都不知道从哪改。选项区上面写一行标题，比如“部门选项（勿删）”，再把那一列标个底色，一眼就能看出来。 数据验证里还有两处要顺手设好。 出错警告选“停止”，样式选停止，标题写“只能从列表里选”，填错了会直接弹出来拦住，不会悄悄放过去。 输入信息写一行提示，比如“请从下拉列表选择部门，不要手打”。填表的人点到单元格就看到这行字，不用再问你怎么写。 这两处不设，下拉列表就是半成品。出错警告不设，填错了也能写进去，等于没锁；输入提示不写，填表的人不知道要点下拉箭头，还是会手打。 部门选完，下一列要选对应城市。华东对应上海、杭州，华北对应北京、天津，这种联动下拉用INDIRECT做。 先把城市列表按部门分开写，每一列的标题就是部门名，比如一列标题写“华东”，下面写上海、杭州；另一列标题写“华北”，下面写北京、天津。 然后给每一列定义名称，名称就是部门名，华东那一列命名为“华东”，华北那一列命名为“华北”。 最后在城市那一列做数据验证，来源写=INDIRECT(部门单元格)，部门选华东，城市下拉就只有上海、杭州；部门选华北，城市下拉就只有北京、天津。 联动下拉的坑在名称上。名称里不能有空格，部门名带空格要先处理；名称和部门选项必须一字不差，差一个字就联动不上。 第一步，用TRIM清空格。空一列写=TRIM(原单元格)，往下拖，把清过的值粘贴回原列，值粘贴，不要带公式。 第二步，统一写法。把“华东地区”“华东区”全部替换成“华东”，用查找替换一次做完，不要一个一个改。替换之前先备份原表，替换错了还能找回来。 第三步，去重检查。选中那一列，点数据菜单里的删除重复项，看剩下几个，剩下的就是标准写法。以后发收集表，先做下拉列表，再发出去。 下面是完整的收集表模板，字段加验证规则加填写说明都在这里，照着建就行。 字段一，部门：下拉列表，选项华东、华北、华南，来源引用选项区，出错警告设为停止，输入提示写“请从下拉列表选择部门，不要手打”。 字段二，月份：下拉列表，选项1月、2月、3月，来源手写，输入提示写“请从下拉列表选择月份”。 字段三，姓名：文本，不做下拉，但要写输入提示“请填写真实姓名，不要写昵称”。 字段四，金额：数值，数据验证选小数，介于0和100000之间，出错警告设为停止，标题写“金额请填数字”。 字段五，备注：文本，不做验证，输入提示写“有特殊情况写在这里，没有写无”。 示例行：部门华东，月份1月，姓名张三，金额1200，备注无。 发收集表之前，先把部门、月份这两列做成下拉列表，再发出去。收表之后先检查三样：部门列有没有手打的值、月份列有没有超出选项的值、金额列有没有文本。检查完再汇总。 第一个坑，选项里混进空格。“华东”和“华东 ”在汇总时是两个部门，做选项之前先用TRIM清一遍。 第二个坑，来源写死在公式里。选项改了，来源没改，下拉列表还是旧的。 第三个坑，下拉列锁死了，汇总列写法仍不统一。汇总用的条件也要从同一份选项里取，不要手打。 第四个坑，选项区被删了。选项区没有标题、没有底色，别人以为是废数据顺手删了，整个下拉列表就空了。选项区要写标题、标底色、写“勿删”。 第五个坑，收集表发出去才发现验证没设。发之前先自己填一行测试，填错一个值看拦不拦得住，拦不住就别发。 下周发收集表之前，先把部门、月份这两列做成下拉列表，再发出去。 ——打工人充电所｜周报、PPT、Excel、简历模板，每周更新
