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）。

注意這里的數字前面也不能加美元符號$。

點擊“確定”后會提示“源當前包含錯誤”，點擊“是”就可以。

回到“數據表模板”頁面，可以看到二級聯動下拉菜單也設置成功。

參照二級下拉菜單的設置，可以完成三級下拉菜單的設置。

?

最后可根據需要，將數據字典頁面隱藏。