需求背景:
有时候我们在开发的过程中,会需要做一些Excel模板供系统用户进行编辑批量上传到系统中,
用户在编辑使用过程中,有一些信息属于绑定信息(固定);
这个时候我们可以创建一个数据源表,绑定某一列来进行触发,在输入这一列数据的时候,会自动把其它的资料输入到指定的单元格中。
用户编辑(操作)模板,表:查询
数据源表 :基本信息
以“查询”A列为触发查询条件
在工作表“查询”B、C、D列输入公式,
=IFERROR(INDEX(基本信息!$A$2:$D$10000,MATCH($A$2,基本信息!$A$2:$A$1000,0),COLUMN()),"")
公式说明:
基本信息:数据源表的工作表名称;
函数:MATCH(lookup_value, lookup_array, [match_type])
lookup_array 以 lookup_array 值为条件在数据源表查找的值;$A$2
lookup_array必需参数,要搜索的单元格区域;基本信息!$A$2:$A$1000
match_type可选参数,数字 -1、0 或 1;
(1)Match_type:1 或省略,表示MATCH 函数会查找小于或等于 lookup_value 的最大值。lookup_array 参数中的值必须按升序排列,例如:...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。
(2)Match_type:0,表示MATCH 函数会查找等于 lookup_value 的第一个值。lookup_array 参数中的值可以按任何顺序排列。
(3)Match_type:-1,表示MATCH 函数会查找大于或等于 lookup_value 的最小值。lookup_array 参数中的值必须按降序排列,例如:TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ... 等等。
函数:INDEX(array, row_num, [column_num])
Array必需。单元格区域或数组常量。
如果数组只包含一行或一列,则相对应的参数 row_num 或 column_num 为可选参数。
如果数组有多行和多列,但只使用 row_num 或 column_num,函数 INDEX 返回数组中的整行或整列,且返回值也为数组。
Row_num必需。选择数组中的某行,函数从该行返回数值。如果省略 row_num,则必须有 column_num。
Column_num可选。选择数组中的某列,函数从该列返回数值。如果省略 column_num,则必须有 row_num。
函数:COLUMN():公式所在的列。
函数:IFERROR(value, value_if_error)
value 公式的计算结果。
value_if_error 必需。公式的计算结果为错误时要返回的值。
隐藏数据源工作表
1、在工作表标签上点击右键,选择“隐藏”工作表,如下图
2、工作表标签上点击右键,选择“查看代码”,如下图:
在VBA窗口中,将需要隐藏的工作表的可见属性(Visible),改为”2-xlSheetVeryHidden“,如下图:关闭VBA窗口就把数据源表隐藏了