excel导入：省市区联动+key-value对应

zqy_cs

已于 2022-07-20 08:37:01 修改

阅读量5.1k

点赞数 1

分类专栏：与开发有关又不完全有关文章标签： java

于 2022-07-07 08:24:10 首次发布

本文链接：https://blog.csdn.net/qiachengsi7271/article/details/125651173

版权

与开发有关又不完全有关专栏收录该内容

2 篇文章

订阅专栏

需求：excel实现下拉框之间级联关系包括客户类型对应客户证件类型（例如：选择客户为个人则证件类型只能为身份证、通行证护照等，而不能为统一信用代码）、省市区联动；同时由于该excel模板是为了向系统导入数据，因此各枚举值需要将对应的码值传入后台，以免在后台再进行大规模kv对应的操作。

解决思路：

1.下拉框采用excel自带数据验证解决；

2.级联关系采用excel自带数据验证解决；

3.kv对应采用excel中VBA代码+隐藏列的方式解决。

具体解决方案：

1.其中下拉框枚举值设置比较简单，建立一个后期隐藏掉的sheet页，将枚举值放在其中，对需要下拉框的列点击【数据】-【数据验证】-【数据验证】，设置验证条件为“序列”，数据来源用鼠标选择全部枚举值即可。

2.级联关系需要先在【公式】-【名称管理器】中添加全部级联关系，具体添加方法可参考Excel级联操作_迷茫的老年人的博客-CSDN博客_excel 级联

然后使用数据验证设置与相关单元格的相关关系，设置验证条件为序列，数据来源使用INDIRECT。

3.这个是我做下来花费时间最多的地方，但其实也相当的简单，只是因为不熟悉VBA代码才花了很多时间。我直接把代码贴出来逐行说一下。首先是环境设置，点击【开发工具】-【宏安全性】，信任所有宏，然后点击旁边的Visual Basic，在弹出来的页面中左边，点击需要进行kv对应的sheet页，在上面写相关的代码。

其次需要在之前的下拉框枚举值后附加码值，例子见下图：

Rem 省市区对应--输入后对应到code
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Rng As Range
  For Each Rng In Target
   Rem 省
    If Rng.Column = 7 Then
        Rng.Offset(0, 2).ClearContents
        Rng.Offset(0, 1).ClearContents
       Cells(Rng.Row, 108) = "=IF(ISNA(VLOOKUP(G" & Rng.Row & ",Sheet2!$A$2:$B$35,2,FALSE)),"""",VLOOKUP(G" & Rng.Row & ",Sheet2!$A$2:$B$352,2,FALSE))"
    End If
    Rem 市
    If Rng.Column = 8 Then
        Rng.Offset(0, 1).ClearContents
       Cells(Rng.Row, 109) = "=IF(ISNA(VLOOKUP(H" & Rng.Row & ",Sheet2!$D$2:$E$132,2,FALSE)),"""",VLOOKUP(H" & Rng.Row & ",Sheet2!$D$2:$E$132,2,FALSE))"
    End If
    Rem 区
    If Rng.Column = 9 Then
       Cells(Rng.Row, 110) = "=IF(ISNA(VLOOKUP(I" & Rng.Row & ",Sheet2!$G$2:$H$707,2,FALSE)),"""",VLOOKUP(I" & Rng.Row & ",Sheet2!$G$2:$H$707,2,FALSE))"
    End If
  Next
End Sub

整个方法来说大概就是，光标在该sheet进行操作，如操作到方法中约束的单元格，则触发对应方法。个人需要改的代码是每个Rem下面的那几行（Rem为备注）。对应省的代码我用伪代码解释一下。

if 点击到第七列

那么将点击的单元格所在行的第七列后的第二列相交叉的单元格内容清空

将点击的单元格所在行的第七列后的第一列相交叉的单元格内容清空

令点击的单元格所在行的第118列的值设为 {

根据第G列和所在行相交叉的单元格的内容，在sheet2的A2到B35的区域中寻找，如找到的话则返回找到结果向右一格的数据如果找不到则返回空空

}

写完后保存。然后就可以生效啦，大概的效果如图：