Excel 公式: 根据一个单元格的用户输入值, 自动设置另一个单元格的值

这篇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函数, 不支持中文,日本等全角字符, 有匹配错误的现象.

详见附件.

而方法一则可以支持这些全角字符,

因此我们最终采用方法一实现.

另: 而且在附件中, 也测试了空格, -, 还有括号的情况.

 

 

 

评论

1 楼 wjason 2014-10-28  

今天在excel 2010上面写了一些代码，果然lookup有问题，找不准。
所以今后的原则就是只是用“方法一: 利用INDEX和MATCH函数”， 不使用lookup。
因为不知道lookup什么时候会报错。