Excel多级联动下拉菜单设置
1.问题描述
现有数据表如下图所示：
该表中包括省、市、县三级目录。
现要将其整理成数据表模板，如下图所示：
要求制作成下拉菜单的形式，且每一级目录的下拉菜单列表要根据上一级目录的内容来确定。
如上图所示，只有在“省”级列表中选择了“北京市”，才能在“市”级列表中选择“北京市市辖区”，进而才能在“县区”级列表中选择“海淀区”“朝阳区”等内容。
2.主要思路
2.1第一步：数据字典整理
首先根据数据表中的内容，整理出多级数据字典，存放在Excel文件中不同的页面。如下图所示：
上图中分别整理出了省、市、县区三级字典。
其中一级字典（省）中，将数据项垂直排列即可；
二级字典（市）中，每行的第1个元素为上一级字典中的项，后续元素为该一级字典对应的二级字典中的项。如在二级字典（市）中，第1行中，第1个元素为“河南省”，后续元素分别为“开封市”“郑州市”。
三级字典（县区）以此类推。
2.2第二步：下拉菜单设置
根据前面制作的数据字典，在Excel中进行相关设置，制作出多级联动下拉菜单。
详细过程参见后文。
3.数据整理
（如果能够手动整理出上述的数据字典，则该部分可以略过。）
为了制作出所需的数据字典，使用Python对数据表进行处理，详细代码如下。
其中path为源文件路径，sheet_name为Excel文件内页面的名称，path_out为输出的字典文件路径。
import pandas as pd
"""文件路径设置"""
path = r"D:\temp\data.xlsx" # 源文件路径
sheet_name = "Sheet1"
path_out = r"D:\temp\data2.xlsx" # 输出文件路径
"""转换函数定义"""
"""用于将DataFrame格式转换为list（二维列表）格式"""
def frame2list(data):
data_1 = data.iloc[:,0] # 提取出DataFrame的第1列
set_1 = set(data_1) # 构建集合，用于将DataFrame第1列的数据去重
if len(data.columns) == 1:
return list(set_1)
else:
list_all = list() # 初始化二维列表，用于存放DataFrame第1列每一项的处理结果
for item in set_1: # 依次处理DataFrame第1列每项数据
data_temp = data[data.iloc[:,0]==item] # 获取DataFrame中该项对应的所有行
set_temp = set(data_temp.iloc[:,1]) # 将对应到的第2列中所有数据进行去重
list_item = [item] # 初始化一维列表，用于存放对应到的DataFrame第2列中的每项数据
for item_temp in set_temp: # 依次处理对应到的DataFrame第2列每项数据
list_item.append(item_temp) # 将该第2列数据加入一维列表
list_all.append(list_item) # 将一维列表加入二维列表
return list_all
"""主函数"""
if __name__ == "__main__":
data = pd.read_excel(path, sheet_name=sheet_name, header=0) # 读取Excel文件
class_num = len(data.columns) # 获取列数
with pd.ExcelWriter(path_out) as f: # 打开输出文件并写入
for i in range(class_num): # 逐列计算并写入输出文件
dic_i = set() # 初始化集合，用于消除重复值
if i == 0:
dic_i = set(data.iloc[:,0])
else:
dic_i = frame2list(data.iloc[:,i-1:i+1])
df_i = pd.DataFrame(dic_i) # 转换为DataFrame格式，便于输出为Excel文件
df_i.to_excel(f, index=False, header=False, sheet_name=data.columns[i]) # 输出文件该程序最终输出一个Excel文件，其中包含3个页面，分别对应3级数据字典。
如前文2.1节中的图所示。
4.下拉菜单制作
4.1数据表准备
将上述生成的数据字典各页面与数据表模板放在一个Excel文件内，保存为不同的页面，如下图所示：
其中“数据表模板”页表示要制作出下拉菜单的数据表页面，如下图所示：
“省”“市”“县区”页分别为数据字典页。
4.2一级下拉菜单设置
对于一级菜单，选中所有需要填写一级目录数据的表格，在“数据”页点击“数据验证”。
进入“数据验证”对话框后，在“允许”项选择“序列”；
在“来源”项选择一级目录中的所有数据。
如：一级数据主要存放于Excel中“省”页面下的A1至A4格，则“来源”中填写“=省!$A1:$A4”。
其中“省”为Excel中存放以及数据的页面名称；“!”为页面与单元格之间的分隔符；“$A1:$A4”为数据在“省”页面中的分布范围。
注意在“来源”的输入框中，“A1”“A4”字母前面要有美元符号$，而数字前面不能有该符号。
如果手动选择字典中的单元格，默认字母和数字前面都会带上美元符号，所以需要手动取消。
返回“数据表模板”页面，看到一级目录已经设置完成。
4.3多级下拉联动菜单设置
在Excel中，对于二级、三级等目录，均进行如下设置：
4.3.1字典内容创建
对于二级目录，选中字典区域，然后点击“公式”栏中的“根据所选内容创建”。
对于三级目录，参照二级目录设置。
在弹出的对话框中勾选“最左列”，确定。
4.3.2下拉菜单与字典的关联
以二级菜单为例。
在“数据表模板”页面中，选中所有需要填写二级数据表的单元格，点击“数据”页的“数据验证”。
在弹出的“数据验证”对话框中，“允许”一栏仍选择“序列”，“来源”一栏输入“=indirect($A2:$A4)” 。
其中indirect函数表示对上一级单元格的关联；
$A2:$A4表示该“数据表模板”页面中需要填写上一级目录的单元格范围。如上图单元格B2至B4内要填写“市”的内容，则indirect函数内要输入“省”的单元格范围（$A2:$A4）。
注意这里的数字前面也不能加美元符号$。
点击“确定”后会提示“源当前包含错误”，点击“是”就可以。
回到“数据表模板”页面，可以看到二级联动下拉菜单也设置成功。
参照二级下拉菜单的设置，可以完成三级下拉菜单的设置。
最后可根据需要，将数据字典页面隐藏。
