|
送人玫瑰，手有余香，请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径！ 【置顶公众号】或者【设为星标】及时接收更新不迷路
小伙伴们好，今天和大家分享一道题目。如何制作多级下拉清单。本篇2700字左右，阅读时间在20分钟左右。 我从网上下载了一份全国行政区域的清单，今天就用这个作为例子向大家演示制作的过程，其中包含了基本操作技巧、函数高级技巧等知识点，向大家展示在低版本下，如何运用我们学到的技巧来完成题目。 原始数据如下，大家可以看到，数据比较混乱，既有半角标点，也有全角标点，还有多余的空格。  因此我们还要将进行数据整理。 上面的结构对于我们是没有用处的。我们需要将省市分列。这里不写什么复杂的公式了，以下基本的操作就可以完成。  A列中选择非空白，然后删除B列中从第二行开始以下的数据  选中B2:B823，F5键定位空值。编辑栏中输入“=B1”后CTRL+ENTER回车。  复制粘贴成数值。接下来A列选中空值。  删除所有行。单元格C1中输入公式“=B1=A1”，下拉。  删除所有TRUE，再删除C列。将B列调整到最左侧。插入一列新列。输入公式“=IFERROR(LEFT(A1,FIND("(",SUBSTITUTE(A1,"（","("))-1),A1)”。完成后粘贴成数值。  用同样的方法，将C列中的数据做同样的整理。  这部分主要用到的工具除了上述部分用到的之外，还有TRIM函数和CLEAN函数。由于都比较简单，这里就不在详细介绍了。 同时我们又看到，同一个市的很多区分了很多行，这个要将他们合并到一个单元格中。 由于C列中文本字符间的空格是来自于外部文件的，因此我们先要把它处理一下。打开查找替换对话框，将字符间的空格复制，粘贴到“查找内容”出；在“替换为”中输入一个空格，点击全部替换后即可。  我们首先给C列中的数据最后面都加个空格，为后面的数据合并做准备。这个很简单，利用“&”就可以完成，这里不再赘述。完成后将数据粘贴会C列中。 接下来我们开始数据合并。复制A列和B列中的内容到新的工作表，选中两列后删除重复值。  在单元格C2中输入公式“=PHONETIC(OFFSET(Sheet1!$A$1,MATCH(A2&B2,Sheet1!$A$2:$A$421&Sheet1!$B$2:$B$421,0),2,SUM((Sheet1!$A$2:$A$421=Sheet5!A2)*(Sheet1!$B$2:$B$421=Sheet5!B2)),1))”，三键回车并向下拖曳即可。 这个公式看起来长，其实逻辑思路很简单。它确认了OFFSET函数的始发点，向下偏移量，向右偏移量，高度和宽度后，由PHONETIC函数合并数据。 最后，不要忘记删除文本最后的那个小空格哦！ 完成后我们发现有些区县名字中间有空格，这个也需要处理一下。 通过替换功能，解决掉上述的空格问题。到此为止，基础数据的整理工作基本上结束了。接下来，我们要进入本篇的另一个重点，用公式将上述省市区的清单改写成一维表格的形式。 我们在C列插入一列辅助列。 在单元格C2输入公式“=A2&B2”，并向下拖曳。 在单元格F2中输入公式“=LEN(D2)-LEN(SUBSTITUTE(D2," ",""))+1”，求出每个市下辖区县的数量。 在单元格G2中输入公式“=INDEX(A:A,SMALL(IF($F$2:$F$352<COLUMN($A:$AB),1000,ROW($1:$351)),ROW(A1))+1)”，三键回车并向下拖曳； 在单元格H2中输入公式“=INDEX(B:B,SMALL(IF($F$2:$F$352<COLUMN($A:$AB),1000,ROW($1:$351)),ROW(A1))+1)”，三键回车并向下拖曳。 这两组公式的逻辑思路是一样的。由于篇幅所限，这里不再详细介绍公式的思路了。如有兴趣的小伙伴可以私信我！ 在I列处添加辅助列2，输入公式“=G2&H2”，并向下拖曳。 最后，在单元格J2中输入公式“=TRIM(MID(SUBSTITUTE(VLOOKUP(G2&H2,$C$2:$D$352,2,FALSE)," ",REPT(" ",99)),COUNTIF($I$2:I2,I2)*99-98,99))”，三键回车并向下拖曳。 至此，我们完成了数据的整理工作。将公式粘贴为熟知并删除辅助列侯，整理的数据如下，复制到新的工作表“sheet3”中。 接下里我们就要开始创建三级动态下拉清单了。 新建一个工作表，命名为“辅助表”。复制Sheet3中的A列到辅助表中的A列，并去除重复项。 在单元格C2中输入公式“=IFERROR(INDEX(Sheet3!$B$2:$B$3027,SMALL(IF((Sheet3!$A$2:$A$3027=Sheet4!$A$2)*(MATCH(Sheet3!$A$2:$A$3027&Sheet3!$B$2:$B$3027,Sheet3!$A$2:$A$3027&Sheet3!$B$2:$B$3027,)=ROW(Sheet3!$A$2:$A$3027)-1),ROW(Sheet3!$A$2:$A$3027)-1),ROW(A1))),"")”，三键回车并向下拖曳至单元格C35。 在单元格E2中输入公式“=IFERROR(INDEX(Sheet3!$C$2:$C$3027,SMALL(IF((Sheet3!$B$2:$B$3027=Sheet4!$B$2)*(Sheet3!$A$2:$A$3027=Sheet4!$A$2),ROW(Sheet3!$A$2:$A$3027)-1),ROW(A1))),"")”，三键回车并向下拖曳至单元格E35。 建工作表“sheet4”， 在单元格B2设置数据验证，并输入公式“=OFFSET(辅助表!C1,1,,COUNTA(辅助表!C:C)-COUNTBLANK(辅助表!C1:C35)-1,1)” 在单元格C2设置数据验证，并输入公式“=OFFSET(辅助表!$E$1,1,,COUNTA(辅助表!E:E)-COUNTBLANK(辅助表!E1:E35)-1)”。 -END- 长按下方二维码关注EXCEL应用之家 面对EXCEL操作问题时不再迷茫无助 我就知道你“在看” |
|
|
