\ 参考excel怎么不规则分段转置? - 知乎 (zhihu.com)
1、原始数据
从数据库导出的原始数据的省、市、区,结构如下:
2、构建省市数据
1)、省份数据
id | 省 |
1 | 河北 |
2 | 山西 |
3 | 内蒙古 |
4 | 辽宁 |
5 | 吉林 |
6 | 黑龙江 |
7 | 上海 |
8 | 江苏 |
9 | 浙江 |
10 | 安徽 |
11 | 福建 |
12 | 江西 |
13 | 山东 |
14 | 河南 |
15 | 湖北 |
16 | 湖南 |
17 | 广东 |
18 | 广西 |
19 | 海南 |
20 | 重庆 |
21 | 四川 |
22 | 贵州 |
23 | 云南 |
24 | 西藏 |
25 | 陕西 |
26 | 甘肃 |
27 | 青海 |
28 | 宁夏 |
29 | 新疆 |
30 | 台湾 |
31 | 香港 |
32 | 北京 |
33 | 天津 |
2)市数据和省数据对应,先拷贝市级数据到单独的数据表(方便处理),按照市数据parent_id对省份内的市进行计数,在name右侧增加一列"序号",序号那列的公式 ”=countif(G$2:G2,G2)
3) 将序号这列,复制到其他列,比如X列,在对X列进行去除重复值处理,即点击【数据】-【删除重复值】,处理之后,x列显示1-21,再对该X列复制,转置粘贴到下方位置
4)点击单元格N2,输入公式:=IFERROR(LOOKUP(1,0/(($G$2:$G$344=$L2)*($J$2:$J$344=N$1)),$I$2:$I$344),""),{上面公式中的“”,可以用“0”替换,来解决后面假空定位不到的问题,另外一个解决方法:选择""的加空格,右键点击【清楚内容】};将公式拖动覆盖整个区域,结果见下表
3、构建市区数据
过程类似上方处理,省略。。。
4、构建多级联动查询
1)选中下方区域(*,此表为公式生成,需要复制再粘贴为值)
2)ctrl+G,选择定位条件,选择常量,此处问题待解决,目的是把选择区域内的空格去掉,解决方法参考(55条消息) excel 某个单元格不是等于空值_一招清除Excel中的假空,简单易学,3秒搞定!_weixin_39927623的博客-CSDN博客
3)选择上方标题栏的【公式】,点击【根据所选内容创建】
4)弹出弹层,选择【最左列】
5)ctrl+F3,查看名称创建情况
6)省市数据表,选择A2,点击【数据】-【数据验证】
7) 【允许】下方选择【许列】,来源,点击右侧图标,前往上述省市的表,选择省份列,点击数据验证右侧图标,点击【确定】
8)在回到省、市的表格,点击A2,下拉显示省份列表
9) 鼠标点击B2,点击【数据】-【数据验证】,点击【序列】,在来源输入:=indirect(A2)
10) 点击B2,上下滑动,显示省份下属的市
*问题解决
三级联动,个别位置联动的不对, 比如沧州市,下属区县,显示的非区县,而是其他市区,此时,需要ctrl+F3,检查到名称定义有重复,且其他的名称定义,显示的区域不是工作簿,而是一个sheet,所以需要删除这两个重复的名称定义,在重新定义名称