常用函数的参数

wangg913

本贴主要对函数的参数，简单进行一下归类，以便清晰地了解函数用法，为实际解决问题提供帮助。

一、数据类型
在函数参数分类之前，首先温习一下表格存储和公式计算的数据类型。
在单元格里存储的数据类型包括：数字、文本、逻辑值和错误值，以及由上述4种数据类型组成的集合，即数组、引用。

1、数字
数字包括整数和小数。单元格输入数字，在较大（大于1E+21）、较小(小于-1E+21)或者从两侧逼近0到一定区间后(小于1E-20，大于-1E-20)，单元格自动调整为科学记数；按照科学记数、百分比、分数、日期、时间的格式输入数据时，会自动调整为相应的单元格格式。
Excel将日期存储为序列号，称为串行值。1900年1月1日是序列号为1，9999年12月31日为2958465。因此所有日期值介于1至2958465之间。
Excel将时间存储为小数，时间被视为一天的一部分。因此，时间值介于0到0.99999999之间，表示时间从 0:00:00 到 23:59:59。
因此，科学记数、百分比、分数、日期和时间都属于数字类型。

2、文本
单元格文本包括字符、数值型文本，在公式中输入文本，需要将数据两端加双引号“"”，比如：="姓名:"&"张三"。
数值型文本经过减负运算，可转化成数字，转化过程中如果为日期、时间、百分比、分数格式的将转化为小数或整数，适配科学记数法的转化为科学记数。

3、逻辑值
逻辑值TRUE、FALSE。

4、错误值
错误值包括#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?和#NULL!七种。错误值的产生原因、追踪、更正与排除、预防比较复杂，本贴不讨论。
公式出现错误，通常是未使用预期的语法、参数或数据类型。

5、数组
数组中的数据类型可以为上述4种类型，数组分为数组常量和由公式生成的数组两类。
1）数组常量中，所有数据都是常量。例如：{5,FALSE, "A"}，，数组中的数值不能有运算符，数值必须是确定的值，不允许是中间运算过程，例如：5/7、2%、"姓名:"&"张三"，6^7，67*8，54-2都是不允许的。
数组常量，可以是单个数值常量数组，例如：{5}；可以是水平数组，例如：{5,FALSE, "A"}；
可以是垂直数组，如：{5;TRUE; "B"}；
也可以是二维数组，例如：{1,TRUE; "A",4E+50}。
2）由公式生成的数组，包括数组常量或引用经过运算生成的数组。
引用在参与运算时（EXCEL帮助称为“存储数组”），按照数组常量的方式进行。行引用转化为水平数组，列引用转化为垂直数组，多行多列引用转化为二维数组。

6、引用
单元格引用是指对工作表中的单元格或单元格区域的引用。引用是区域的标识，指明所使用数据的位置。
引用分为普通引用、三维引用和多维引用三种类型。
1）普通引用代表一块矩形区域，使用区域运算符“:”将矩形区域的左上角单元格和右下角单元格组合在一起，格式如：A1:B2，代表A1、A2、B1和B2这个四个单元合并在一起的区域。
2）三维引用，引用同一单元格或范围在多个工作表的引用是名为三维引用。格式比如：Sheet1:Sheet2!A1，同时代表Sheet1!A1和Sheet2!A1。
3）多维引用在Excel帮助中没有明确定义，经过网友多年的讨论有多个命名，本贴沿用“使用特定函数和数组生成的多维引用”。例如：

1. OFFSET(A1,,,{3,5})

复制代码

是由OFFSET函数和数组常量{3,5}生成的一组引用，共2个元素，同时代表 A1:A3 和 A1:A5 两块区域。

wangg913

下面回到正题，我们在使用函数的时候，如果不熟悉函数的用法，常常会查询函数的帮助（打开EXCEL后，按F1键即可调用EXCEL帮助），EXCEL的帮助比较全面详细。目前的EXCEL帮助可选择Office.com的联机帮助，或者本机的帮助文件，或者登陆官网Excel帮助中心。




Excel帮助中心
https://support.office.com/zh-cn/excel

wangg913

二、参数归类

Excel的函数帮助，包括语法、备注和示例，通过帮助可以获得基本的了解，学会简单的使用方法。
函数的语法就是说明函数参数、返回值以及注意事项的，例如： COUNT 函数。

1. COUNT(value1, [value2], ...)
   
2. COUNT 函数语法具有下列参数：
   
3. Value1必需。要计算其中数字的个数的第一项、单元格引用或区域。
   
4. value2,…可选。要计算其中数字的个数的其他项、单元格引用或区域，最多可包含 255 个。
   
5. 注意:这些参数可以包含或引用各种类型的数据，但只有数字类型的数据才被计算在内。

复制代码

（这里主要分析参数名）再比如： MMULT 函数和 PRODUCT 函数：

1. MMULT(array1, array2)

复制代码

1. PRODUCT(number1, [number2], ...)

复制代码

这三个函数的参数名很明确，都属于Excel的数据类型，分别是 number 数字、array 数组、value 数值。
但是，有一些函数的参数名，标注相对复杂一些，例如：HLOOKUP 函数、REPLACE 函数和 SUMIF 函数。

1. HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

复制代码

1. REPLACE(old_text, start_num, num_chars, new_text)

复制代码

1. SUMIF(range, criteria, [sum_range])

复制代码

其中一些参数名由两部分组成，下划线前后各一部分。例如：lookup_value，table_array，old_text，new_text。
通过进行整理比对可以发现，通常情况下，下划线后是数据类型，下划线前是参数说明。

Excel的函数分为逻辑、文本、日期和时间、查找与引用、数学和三角函数、统计、工程、信息、财务等几个大类。





下面按照函数大类，对常用函数的参数名，进行简单归类。
1、逻辑函数的参数，多为 Logical，即逻辑值。
2、文本函数的参数，多为 Text、*_text、Number、*_num 几种。
3、日期和时间函数的参数，多为 Date、*_date 和 serial_number 三种。
4、查找与引用函数的参数，以 Reference、*_ref、Array 、 ——和 Value 这五种较多。
5、数学和三角函数的参数，因为涉及数学运算，基本上都是 Number 和 Array。
6、统计函数的参数，如果不考虑分布函数，参数种类 Number、Value、Range、*_ref 和 Array 都有。
7、信息函数的参数，主要包括 Value、Reference 两种。

好了，通过筛选和简要分类，可以发现上述七大类函数的参数有明确名称的参数，分为value、number、date、logical、text、reference、ref、range、array。依据上一章介绍的数据类型，这9种参数名称可以重新划分为6类。

1）Number数字，包含 serial_number（日期序列号），以及整数、小数、分数、科学记数等等。
>>Date日期，就是serial_number，属于Number数字。
2）Logical逻辑值。
3）Text文本，在单元格中以文本方式存储的数据。
4）Reference引用，ref 是 Reference 的缩写。Reference 包括单个区域（矩形区域）、多个区域（联合引用）或者一组区域（多维引用）。
>>Range区域，单个区域（普通引用）和一组区域（多维引用）。Range 属于 Reference引用，但不包括联合引用。
5）Array数组，一组数据，数据类型包括除三维引用和多维引用以外的其他数据类型。
6）Value数值，包括数字、文本、逻辑、错误、数组，以及引用（含普通引用、三维引用、联合引用，不含多维引用）。

ZhouFfett

我补充说几句。

一、要真正理解函数，既要弄清楚函数参数需要什么类型的数值，还要弄清楚函数返回的值属于什么类型。

——扩展的说，要真正理解一个公式，要弄清楚公式每一步计算前和后数据类型的变化

二、关于引用，既可以是对单个单元格的引用，也可以是对多个单元格的引用，甚至是多个区域的单元格的引用。

而函数参数中的reference，通常可以理解为可以引用多个单元格的。

——可以把函数参数中的reference理解为数组的一种形式。

——在函数参数中，array也是数组的一种形式。

reference和array都是数组，只不过是函数对其参数提现更具体的数据形式而已

szoe

学习了，感谢分享~~~

wangg913

三、引用

参数类型 Logical逻辑值、Text文本、Number数字容易理解，Value数值包含大多数数据类型。值得一提、相对复杂一点的是Reference和Range。
上一章提到，引用包括普通引用、三维引用和多维引用三种，下面简述一下引用对函数的适应性。

1、单个区域
单个区域，或者是单个单元格，例如：A1，或者是矩形区域，例如：A1:B2。
1）区域运算
区域运算符“:”可以将左上角和右下角之间的单元格结合起来，实际上只要是普通引用都可以运用区域运算符结合。例如：

1. A2:1:1

复制代码

A2:1:1 表示 A2 和第1行结合，代表 1:2，即第1行和第2行；
同理，C2:B:B 是 C2 和 B列结合，代表 B:C ，即 B列和 C列；
同理，A1:B4:2:2 是 A1:B4 和 第2行结合，代表 1:4 ，即 第1行至第4行。

2）公式生成的引用
单个的矩形区域可以通过 INDEX、OFFSET、INDIRECT等函数生成，例如：

1. OFFSET(A1,,,4)

复制代码

1. INDEX(A1:B4,,1)

复制代码

1. INDIRECT("A1:A4")

复制代码

这3个公式的结果都是引用，都代表 A1:A4。

3）公式生成的引用结合公式生成的引用，也可以用区域运算符结合起来。例如：

1. INDEX(A:A,3):B5

复制代码

1. OFFSET(A1,2,):B5

复制代码

1. INDIRECT("A3"):B5

复制代码

这3个公式的结果也都是引用，都代表 A3:B5。

同理，区域运算符两侧的引用都可以是公式生成的，例如：

1. INDEX(A:A,3):INDEX(B:B,5)

复制代码

1. OFFSET(A1,2,):OFFSET(B1,4,)

复制代码

1. INDIRECT("A3"):INDIRECT("B5")

复制代码

1. INDEX(A:A,3): OFFSET(B1,4,)

复制代码

1. INDIRECT("A3"):OFFSET(B1,4,)

复制代码

单个区域，无论是直接引用，还是公式生成的引用，都可以匹配 Value、Number、Logical 或者是 Array 几种参数数据类型。


2、联合引用
使用联合运算符“,”，将多个区域组成一个区域集，这时每个区域的行列数可以不相同，根据运算符将这种引用叫做“联合引用”。例如：

1. (A1:B2,A3,A5:C5)

复制代码

三个区域大小各不相同，行列数分别为2*2、1*1、1*3。

使用IS函数判断一下，检查它是否为引用

1. ISREF((A1:B2,A3,A5:C5))=TRUE

复制代码

当然，联合引用中的各区域也可以用函数生成，例如：

1. (OFFSET(A1,,,2,2),A3,INDIRECT("A5:C5"))

复制代码

1. (INDEX(A:A,1):B2,A3,INDEX(A:C,5,))

复制代码

联合引用，可以作为部分同类型、多参数的数学函数、统计函数的参数，但是单个参数的函数无法使用。
另外，参数为range的不适用。举例：
参数为多个 Number 的适合

1. SUM((A1:B2,A3,A5:C5))

复制代码

参数为 Array 的适合

1. LARGE((A1:B2,A3,A5:C5),1)

复制代码

参数为单个 Number 不适合

1. ABS((A1:B2,A3,A5:C5))=#VALUE!

复制代码

参数为 Range 不适合

1. SUMIF((A1:B2,A3,A5:C5),">0")

复制代码

联合引用是多区域集，因此，即便参数为 Reference 的也不全适用。例如：

1. COLUMN((A1:B2,A3,A5:C5))= #REF!

复制代码

1. OFFSET((A1:B2,A3,A5:C5),0,)= #VALUE!

复制代码

汇总一下，可以将联合引用作为参数的函数，包括同类型、多参数的部分数学函数（SUM、SUMSQ、AGGREGATE等）、统计函数（MAX、MIN、LARGE、SMALL、FREQUENCY等），部分引用函数（AREAS、INDEX），部分信息函数（ISREF、ISERROR、ISERR等）。

3、三维引用
三维引用是多表相同位置的引用集合，不能直接参与运算，例如：

1. Sheet1:Sheet2!A1+1=#REF!

复制代码

也不能用引用函数生成，例如：

1. INDIRECT("Sheet1:Sheet2!A1")=#REF!

复制代码

实际上，三维引用并不是引用。判断一下：

1. ISREF(Sheet1:Sheet2!A1)=FALSE

复制代码

可以将三维引用作为参数的函数，包括同类型多参数的部分数学函数（SUM、SUMSQ）、统计函数（MAX、MIN、LARGE、SMALL、FREQUENCY、COUNT、VAR类、STDEV类等），凡是参数为Reference、ref和Range的都不适用。举例：

1. =FREQUENCY(Sheet1:Sheet2!A1,{1,2})

复制代码

1. =SUMSQ(Sheet1:Sheet2!A1)

复制代码

1. =VAR(Sheet1:Sheet2!A1)

复制代码

1. ROW(Sheet1:Sheet2!A1)= #VALUE!

复制代码

1. COUNTIF(Sheet1:Sheet2!A1,1) = #VALUE!

复制代码

micch

学习了，明白了一些。
什么时候讲到参数之间运算，尤其是数组运算作为参数

amarantine

感谢分享！

wangg913

4、多维引用
多维引用——本节只做简单介绍，是一组引用，包括只有单个引用的多维引用，水平多维引用、垂直多维引用和多行多列多维引用。

举例：
a、只有单个引用的多维引用

1. =OFFSET(A1,{0},)

复制代码

虽然只有一个引用，而且该引用中也只有一个单元格A1，但它是由引用函数和数组生成，确确实实是一个多维引用。

b、水平多维引用

1. =OFFSET(A1,,{0,1},,2)

复制代码

OFFSET 函数生成水平一组引用，共2个，同时代表 A1:B1 和 B1:C1 两个区域。
因为水平数组 {0,1}的作用，生成的多维引用也是水平的，参照数组的格式，这个多维引用可以形象的表示为：
{A1:B1 , B1:C1 }

c、垂直多维引用

1. =OFFSET(A1,,{0;1},,2)

复制代码

OFFSET 函数生成垂直一组引用，共2个，同时代表 A1:B1 和 B1:C1 两个区域。
因为垂直数组 {0;1}的作用，生成的多维引用也是垂直的，这个多维引用可以形象的表示为：
{A1:B1 ;
B1:C1 }

d、多行多列多维引用

1. =OFFSET(A1,,{0,1;2,3},,2)

复制代码

OFFSET 函数生成多行多列一组引用，一共4个，同时代表 A1:B1 、 B1:C1 、 C1:D1 、D1:F1 四个区域。
因为二维数组{0,1;2,3}的作用，生成的多维引用也是多行多列，这个多维引用可以形象的表示为：
{A1:B1，B1:C1;
C1:D1 , D1:F1}

这里用数组的形式表现引用，只是为了便于理解，多维引用不是数组，是一组引用。

通常情况下，多维引用不能直接参与运算；使用运算符运算，比如四则运算、脱字符运算、比较运算符、连接运算符、引用运算符，都会产生错误。
能将多维引用作为参数的函数，其参数必须是Reference、ref或者Range这三种，这些函数包括OFFSET、AREAS、COUNTIF、COUNTIFS、COUNTBLANK、SUMIF、SUMIFS、SUBTOTAL、RANK、AGGREGATE。即便有些函数的参数为Reference也不适用，比如ROW、COLUMN、INDEX。

其他说明：
1）有些函数使用多单元格数组公式，能够在多单元格得出预期结果。
例如，在 C2:D2 多单元格输入下面的公式，可以分别求出2个区域的和：

1. =SUM(OFFSET(A1,{0,2},,2))

复制代码

2）如果多维引用中只有一个引用，而且这个引用中只有一个单元格，则可以直接参与运算，例如：

1. =OFFSET(A1,{0},)+1

复制代码

wangg913

四、参数类型的转化

在函数中的输入数据，其数据类型能够一定程度的与参数的数据类型相适应，进行转化后参与运算。

1、文本参数转为数字
参数输入文本，按照数字进行计算。例如：

1. DAY("2018-4-5")=5

复制代码

DAY的参数为 Serial_number 串行号，而 "2018-4-5" 是文本型，在运算时 "2018-4-5" 自动转化为日期串行号。
能够进行此类转化的，一般为数学函数、日期函数和统计函数。

2、数字参数转化为文本
参数输入数字，按照文本进行计算。例如：

1. LEN(34567)=5

复制代码

能够进行此类转化的，一般为文本函数。

3、逻辑值参数
文本"TRUE"、"FALSE"无法转化为逻辑值， 但是数字0和非0数字可以在逻辑判断时替代 FALSE 和 TRUE，例如：

1. IF({1,0},4,5)={4,5}

复制代码

1. IF({9.9,0},4,5)={4,5}

复制代码

按照参数类型要求，理论上应该是 IF({TRUE,FALSE},4,5)，只不过0/1使用起来简洁一些。

4、数组参数和常量参数的区别
使用数组做参数，有时侯结果和普通参数有区别，例如：

1. MAX(22,"25")=25

复制代码

此时 "25" 也参与了统计；

1. MAX(22,{"25"})=22

复制代码

{"25"} 没有参与统计；

1. SUM(22,TRUE)=23

复制代码

TRUE 转化为1参与求和；

1. SUM(22,{TRUE})=22

复制代码

{TRUE} 没有参与求和；

A1=22A2=TRUE
SUM(A1:A2)=22
引用也参照数组的运算方式，忽略掉非数字类型参数。

现在可以归纳一下，数组或引用中的数字型文本或逻辑值，在作为数学和统计类函数参数时，不参与统计和计算。