execl常用的微函数系列
发布网友
发布时间：2022-08-20 20:50
共1个回答
热心网友
时间：2023-11-10 09:21
execl常用的微函数系列
Offset函数是一个常见的引用类函数，作用与Indirect函数相似，与Indirect函数所不同的在于，Offset函数是基于目标基准区域的偏移引用。
Offset函数首先需要指定一个基准区域，然后通过行偏移量和列偏移量来确定实际的引用位置。偏移量是一个矢量值，以正负符号来表示偏移方向，以数值大小来表示偏移距离。
Offset函数的第三参数和第四参数分别表示引用区域的高度和宽度，两个参数均可省略，在省略的情况下默认与基准区域的维度尺寸相一致。高度和宽度的参数值也可以使用负数，表示负方向(向上/向左)上的维度扩展。
一、Offset函数
Offset的常见应用场景包括：
1，与Match函数相结合的查询，可以突破Vlookup、Hlookup等函数的单一方向性的*
2，可以动态更新的区域引用(常用于生成下拉菜单的数据有效性序列)
3，通过数学构造，部分引用某个区域，比如间隔引用等等(例如生成工资单)
4，三维引用
当Offset函数的行列偏移或高度宽度参数使用数组作为参数值时，就会产生三维引用甚至更*度的引用。例如=OFFSET(A1:B1,,,{1;2;3;4;5})公式产生如下图所示的引用：
Indirect函数是比较常见的引用类函数，与其功能相仿的函数包括Offset函数、Index函数等等。与Offset函数所不同的在于，Offset函数是基于目标基准位置的偏移引用，而Indirect函数则是通过构造单元格地址的文本书写方式来对单元格区域直接进行引用。
在Excel中单元格的引用方式包括A1样式和R1C1样式，因此Indirect函数在构造引用字符串时，也可以使用上述两种样式的文本字符串，但需要在第二参数中使用不同的参数值进行标识区别。
A1样式和R1C1样式两种不同引用方式的来源是由于单元格地址表示方式的不同：如果用字母来表示列标，数字来表示行号，就是A1引用样式;如果用Rn表示第n行，用Cn表示第n列，就是R1C1引用样式。
由于可以构造文本形式的单元格引用地址，因此利用文本连接符&就可以构造“常量”+“变量”、”静态“+”动态“相结合的单元格引用方式。
例如=INDIRECT("A2:A"&COUNTA(D:D))中，"A2:A"的部分就是静态地址，其中的列标A和行号2都是常量，而COUNTA(D:D)部分形成了第二个行号的动态引用。整个公式的引用方式就等同于“A2:An”，其中的n是变量，由D列的非空单元格数量所决定。
基于这样的特性，Indirect函数常见的用途包括：
1，引用地址事先未知，需要通过其他运算得到部分引用元素的引用
2，可以动态更新的区域引用(常用于生成下拉菜单的数据有效性序列)
3，表名称有数字规律的多表引用(三维引用)
4，构造生成指定的数字序列或数组
5，与Text函数相结合，通过一组数值生成相应的一组R1C1式引用
Match函数是Excel中十分常用的匹配查询类函数，其作用和地位与Vlookup、Lookup等查询类函数相近。与Lookup类函数有所区别的地方在于：Match函数返回的结果是匹配元素的所在位置，而非匹配元素本身。
此外，Match函数还支持包含“*”、“?”等通配符的模糊查询，但不能区分英文字母的大小写。如果要在目标数组中进行区分大小写的查询，可结合Exact函数来组合公式，例如要在数组{"EXCEL","book","excel","SHEET"}查询"excel"的位置，可以使用公式：
=MATCH(TRUE,EXACT({"EXCEL","book","excel","SHEET"},"excel"),0)
返回结果为3，不会受数组中第一个元素"EXCEL"的干扰。
需要注意的是，Exact函数不支持通配符，因此不能通过上述方法同时进行区分大小写和包含通配符的模糊查询。
当目标数组中包含多个与查询数据相匹配的元素时，Match函数只返回其中第一个匹配元素的所在位置，因此Match函数通常只用于唯一性的查询。但利用这一特性，Match函数还可以用来对数组中的非重复数据进行统计，例如统计非重复数据的个数，返回非重复数据的'列表等等。
二、Indirect函数
Indirect函数是比较常见的引用类函数，与其功能相仿的函数包括Offset函数、Index函数等等。与Offset函数所不同的在于，Offset函数是基于目标基准位置的偏移引用，而Indirect函数则是通过构造单元格地址的文本书写方式来对单元格区域直接进行引用。
在Excel中单元格的引用方式包括A1样式和R1C1样式，因此Indirect函数在构造引用字符串时，也可以使用上述两种样式的文本字符串，但需要在第二参数中使用不同的参数值进行标识区别。
A1样式和R1C1样式两种不同引用方式的来源是由于单元格地址表示方式的不同：如果用字母来表示列标，数字来表示行号，就是A1引用样式;如果用Rn表示第n行，用Cn表示第n列，就是R1C1引用样式。
由于可以构造文本形式的单元格引用地址，因此利用文本连接符&就可以构造“常量”+“变量”、”静态“+”动态“相结合的单元格引用方式。
例如=INDIRECT("A2:A"&COUNTA(D:D))中，"A2:A"的部分就是静态地址，其中的列标A和行号2都是常量，而COUNTA(D:D)部分形成了第二个行号的动态引用。整个公式的引用方式就等同于“A2:An”，其中的n是变量，由D列的非空单元格数量所决定。
基于这样的特性，Indirect函数常见的用途包括：
1，引用地址事先未知，需要通过其他运算得到部分引用元素的引用
2，可以动态更新的区域引用(常用于生成下拉菜单的数据有效性序列)
3，表名称有数字规律的多表引用(三维引用)
4，构造生成指定的数字序列或数组
5，与Text函数相结合，通过一组数值生成相应的一组R1C1式引用
三、Match函数
Match函数是Excel中十分常用的匹配查询类函数，其作用和地位与Vlookup、Lookup等查询类函数相近。与Lookup类函数有所区别的地方在于：Match函数返回的结果是匹配元素的所在位置，而非匹配元素本身。
此外，Match函数还支持包含“*”、“?”等通配符的模糊查询，但不能区分英文字母的大小写。如果要在目标数组中进行区分大小写的查询，可结合Exact函数来组合公式，例如要在数组{"EXCEL","book","excel","SHEET"}查询"excel"的位置，可以使用公式：
=MATCH(TRUE,EXACT({"EXCEL","book","excel","SHEET"},"excel"),0)
返回结果为3，不会受数组中第一个元素"EXCEL"的干扰。
需要注意的是，Exact函数不支持通配符，因此不能通过上述方法同时进行区分大小写和包含通配符的模糊查询。
当目标数组中包含多个与查询数据相匹配的元素时，Match函数只返回其中第一个匹配元素的所在位置，因此Match函数通常只用于唯一性的查询。但利用这一特性，Match函数还可以用来对数组中的非重复数据进行统计，例如统计非重复数据的个数，返回非重复数据的列表等等。
;