这篇blog讨论的事儿, 是关于使用Excel, 如何根据一个单元格的用户输入值, 自动设置另一个单元格的值.
这个需求最开始的动机是这样的,
我们需要维护一个员工表, 员工便上面有员工所属于的部门名, 以及这个部门的编码这样的信息,
对于员工所属于的部门的名字, 我们可以使用数据有效性, 通过下拉列表的方式实现
但是, 员工部门的编码, 一来不好记忆, 二来他们彼此还十分相似, 很容易混淆.
因此我们希望:
当用户选择了部门名称之后, 可以在excel, 将部门的编码自动补全.
类似的情形还有, 比如:
使用excel管理图书的信息表,这个表里面很可能有图书名和ISBN,
我们希望当编辑图书名字的单元格的时候, 可以自动补全ISBN,
对于类似的需求, 我们可以使用vba来做,
但是如果在excel里面插入了vba, 用户在打开excel的时候, 会弹出安全性提示(根据安全性级别的设置而定).
所以跟vba比较, 我们还是更倾向于公式.
感谢S同学, :-) 通过公式我们可以有一下两种方法.
(在附件的excel文件中, 有这两种方法的具体示例)
方法一: 利用INDEX和MATCH函数
写出来的公式是形如下面这样, 其中method_1_config是一个单独的sheet, 用于配置连动的对应关系.
=IF(($B3<>""),INDEX(method_1_config!$A:$B,MATCH($B3,method_1_config!$A:$A,0),2),"")
方法二: 利用LOOKUP函数
写出来的公式是形如下面这样, 其中method_2_config是一个单独的sheet, 用于配置连动的对应关系.
=IF(($D3<>""),LOOKUP($D3,method_2_config!A:A,method_2_config!B:B),"")
注意的问题
在我们的测试中, 我们发现第二种方法LOOKUP函数, 不支持中文,日本等全角字符, 有匹配错误的现象.
详见附件.
而方法一则可以支持这些全角字符,
因此我们最终采用方法一实现.
另: 而且在附件中, 也测试了空格, -, 还有括号的情况.