今天这个案例难度不高,算是比较有意思吧。
前阵子正好有朋友在讨论各自身份证号码的 4 到 6 位数,分别代表了哪个区。所以就想到做个完整的区号列表,可以根据身份证号码自动匹配出区县。
案例:
下图 1 中左侧的数据表是身份证号码中的区县编号,请根据 D 列的证件号码匹配出所在的区。
效果如下图 2 所示。
解决方案 1:
先添加辅助列。
1. 在 E2 单元格中输入以下公式 --> 下拉复制公式:
=XLOOKUP(MID(D2,4,3)*1,A:A,B:B)
公式释义:
MID(D2,4,3):从 D2 单元格的第 4 个字符开始,一共提取 3 个字符;
...*1:上述提取结果是文本,如要与 A 列的的数值匹配,需要通过 *1 转换成数值;
XLOOKUP(...,A:A,B:B):在 A 列中查找上述结果,找到后返回 B 列的值
解决方案 2:
1. 选中左侧数据表的任意单元格 --> 选择菜单栏的“数据”-->“来自表格/区域”
2. 在弹出的对话框中点击“确定”。
表格已上传至 Power Query。
3. 选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载至”
4. 在弹出的对话框中选择“仅创建连接”--> 点击“确定”
5. 重复步骤 1、2 将右侧的数据表也上传至 Power Query。
6. 点击标题左边的格式符号 --> 在弹出的菜单中选择“文本”
7. 在弹出的对话框中点击“替换当前转换”。
8. 选择菜单栏的“主页”-->“拆分列”-->“按字符数”
9. 在弹出的对话框的“字符数”区域输入 3 --> 点击“确定”
10. 选中第二列 --> 选择菜单栏的“主页”-->“删除列”-->“删除其他列”
11. 选择菜单栏的“主页”-->“合并查询”
12. 在弹出的对话框中按以下方式设置 --> 点击“确定”:
选中“表2”的列
在下拉菜单中选择“表1”--> 选中“编号”列
13. 选择菜单栏的“添加列”-->“索引列”
14. 点开“表1”旁边的扩展钮 --> 在弹出的菜单中仅勾选“区”--> 取消勾选“使用原始列名作为前缀”--> 点击“确定”
15. 选中“区”列 --> 删除其他列
16. 选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载至”
17. 在弹出的对话框中选择“表”--> 选择“现有工作表”及所需上传至的位置 --> 点击“确定”
绿色的表格就是匹配结果。