|
工作中经常遇到省市县这种类似关联输入的问题，下面就说一说三级动态下拉菜单的制作方法。 百度搜索全国省市县名称，找到了一张Excel表，下载下来如下： 1、首先，处理升省级。在【A列】前面插入一列，并将【A列】复制过去。【选中A列】-菜单栏【数据】-【删除重复项】-【以当前选定的区域排序】-【删除重复项】-在弹出窗中【全选】-【确定】。然后删除【A3】单元格。 2、处理市、区县。在【C列】前插入两列，将【B列】省复制到【C列】，将【E列】市复制到【D列】；选中【B、C、D、E】列，按【F5】键定位-【定位条件】-【空值】-【确定】，在编辑栏中输入公式【=B2】，然后按【Ctrl+Enter】确定。 选中【C、D列】，菜单栏【数据】-【删除重复项】-【只勾选市】-【确定】 3、定义名称【省】。按【Ctrl+F3】或菜单栏【公式】-【名称管理器】，打开名称管理器，【新建】，输入如下（此处已将原表名改为表2）： 4、定义名称【市】。根据【C、D列】，当【C列】省相同时，选中对应的【D列】数据作为下拉菜单，用OFFSET函数来进行偏移。具体为：插入一空白表，将表名分别改为1、2，表2作为数据源，表作为测试表。
5、新建名称【县】。当【表1B3中】输入市后，在在【表2】中【E列】找到对应的市名，用MATCH函数返回其第一个位置，用COUNTIF函数统计其重复的个数作为偏移行数。得出OFFSET公式为【=OFFSET('2'!$F1,MATCH('1'!$B3,'2'!$E:$E,0)-1,,COUNTIF('2'!$E:$E,'1'!$B3))】。即【选中表1C3单元格】-新建名称【县】，引用上述公式。 6、设置有效性。选中表1【A3：A20】-菜单栏【数据】-【数据验证】-【数据验证…】，在【验证条件】里选择【序列】，在【来源】里输入【=省】；同理，选择【B3：B20】，在【验证条件】里选择【序列】，在【来源】里输入【=市】；选择【C3：C20】，在【验证条件】里选择【序列】，在【来源】里输入【=县】。完成。
|
|
|
当在表1【A3】中输入省名之后，则根据表1【A3】中省名，在【表2】中【C列】找到对应的省名，用MATCH函数返回其第一个位置，用COUNTIF函数统计其重复的个数作为偏移行数。得出OFFSET公式为【=OFFSET('2'!$D1,MATCH('1'!$A3,'2'!$C:$C,0)-1,,COUNTIF('2'!$C:$C,'1'!$A3))】。即【选中表1B3单元格】-新建名称【市】，引用上述公式。
