ecel常用函数笔记.docx

ecel常用函数笔记.docx

《ecel常用函数笔记.docx》由会员分享，可在线阅读，更多相关《ecel常用函数笔记.docx（22页珍藏版）》请在冰豆网上搜索。

ecel常用函数笔记

清洗处理类：

3-10关联匹配类：

1-2，11-14

1）Vlookup（）

VLOOKUP（lookup_value,table_array,col_index_num,[range_lookup]）。

VLOOKUP（查找值，查找范围，查找列数，精确匹配或者近似匹配）

vlookup就是竖直查找，即列查找。

通俗的讲，根据查找值参数，在查找范围的第一列搜索查找值，找到该值后，则返回值为：

以第一列为准，往后推数查找列数值的这一列所对应的值。

以后几乎都使用精确匹配，最后项的参数一定要选择为false。

需求：

A分销商需要7/31销量数据

操作：

在I3单元格输入：

=VLOOKUP（H3,$A$3:

$F$19,5,FALSE）

分析：

H3为我们想查找的型号，即iphone5。

为什么要写H3，而不是直接写iphone5，因为方便公式进行拖拽填充，以及保证准确性。

$A$3:

$F$19为我们需要在此范围内做查找，为什么要加上绝对引用呢，因为下面的ip4和剩余的查找都引用这个区域，即我们的数据源，加上了绝对引用后，就可以对公式进行直接的拖拽。

5从我们的数据源第一列起，我们要查询的7/31号的销量在我引用的第一列（即型号列）后面的第五列。

注意这里的列数是从引用范围的第一列做为1，而不是以A列作为第一列，万万注意此处。

2）Lookup（）

1矢量形式的LOOKUP

矢量形式的LOOKUP在一行或一列区域（称为矢量）中查找值，然后返回另一行或一列区域中相同位置处的值。

如果要指定其中包含要匹配的值的区域，请使用这种形式的LOOKUP函数。

矢量形式的语法

LOOKUP（lookup_value,lookup_vector,result_vector）

Lookup_value是LOOKUP在第一个矢量中搜索到的值。

Lookup_value可以是数字、文本、逻辑值，也可以是代表某个值的名称或引用。

Lookup_vector是一个仅包含一行或一列的区域。

lookup_vector中的值可以是文本、数字或逻辑值。

重要说明：

lookup_vector中的值必须按升序顺序排列。

例如，-2、-1、0、1、2或A-Z或FALSE、TRUE。

否则，LOOKUP返回的值可能不正确。

大写和小写文本是等效的。

Result_vector是一个仅包含一行或一列的区域。

它的大小必须与lookup_vector相同。

从A1:

A11找D2（3），返回同行的C列的值a

注意：

如果LOOKUP找不到lookup_value，它会匹配lookup_vector中小于或等于lookup_value的最大值。

A列找不到6就返回A列5同行的C列值g

如果lookup_value小于lookup_vector中的最小值，则LOOKUP会返回#N/A错误值。

2数组形式的LOOKUP

数组形式的LOOKUP在数组的第一行或列中查找指定值，然后返回该数组的最后一行或列中相同位置处的值。

如果要匹配的值位于数组的第一行或列中，请使用这种形式的LOOKUP。

数组形式的语法

LOOKUP（lookup_value,array）

Lookup_value是LOOKUP在数组中搜索到的值。

Lookup_value可以是数字、文本、逻辑值，也可以是代表某个值的名称或引用。

如果LOOKUP找不到lookup_value，它会使用该数组中小于或等于lookup_value的最大值。

如果lookup_value小于第一行或列（取决于数组维度）中的最小值，则LOOKUP会返回#N/A错误值。

Array是一个单元格区域，其中包含要与lookup_value进行比较的文本、数字或逻辑值。

数组形式的LOOKUP与HLOOKUP函数和VLOOKUP函数相似。

其区别是HLOOKUP在第一行中搜索lookup_value，VLOOKUP在第一列中进行搜索，而LOOKUP根据数组的维度进行搜索。

如果array所覆盖区域的宽度大于高度（列多于行），则LOOKUP会在第一行中搜索lookup_value。

如果array所覆盖的区域是正方形或者高度大于宽度（行多于列），则LOOKUP会在第一列中进行搜索。

使用HLOOKUP和VLOOKUP时，可以向下索引或交叉索引，但LOOKUP始终会选择行或列中的最后一个值。

重要说明：

array中的值必须按升序顺序排列。

例如，-2、-1、0、1、2或A-Z或FALSE、TRUE。

否则，LOOKUP返回的值可能不正确。

大写和小写文本是等效的。

从A1:

C11查找D6（4），返回最后一列同样位置的f

3）TRIM（）

TRIM（）去掉字符串的两边空格，类似于python字符串函数str.strip（）

字符串中间的空格可以用SUBSTITUTE（）

SUBSTITUTE（text,old_text,new_text,[instance_num]）类似于python的replace（）

Text为需要替换其中字符的文本，或对含有文本的单元格的引用。

Old_text为需要替换的旧文本。

New_text用于替换old_text的文本。

Instance_num为一数值，用来指定以new_text替换第几次出现的old_text。

如果指定了instance_num，则只有满足要求的old_text被替换；否则将用new_text替换TEXT中出现的所有old_text。

4）CONCATENATE（）

CONCATENATE（text1,text2,...）

Text1,text2,...为1到30个将要合并成单个文本项的文本项。

这些文本项可以为文本字符串、数字或对单个单元格的引用。

也可以用&（和号）运算符代替函数CONCATENATE实现文本项的合并。

5）Replace（）

=Replace（指定字符串，哪个位置开始替换，替换几个字符，替换成什么）

=REPLACE（"abcdefg",1,2,"aaa"）结果

6）Left／Right／Mid

=Mid（指定字符串，开始位置，截取长度）

7）Len／Lenb

返回字符串的长度，在len中，中文计算为一个，在lenb中，中文计算为两个。

8）Find

Find（要查找的文本，文本所在的单元格，从第几个字符开始查找[可选，省略默认为1，从第一个开始查找]）类似于python的str.find（）

查找某字符串出现的位置，可以指定为第几次出现，与Left／Right／Mid结合能完成简单的文本提取

注意：

指定查找起始位置start_num为3，是从第3个字符开始查找，但结果还是从文本开头计算。

所以返回的是9

区分大小写

Find函数是精确查找，区分大小写。

Search函数是模糊查找，不区分大小写。

9）Search

和Find类似，区别是Search大小写不敏感，但支持*通配符

search函数的参数find_text可以使用通配符“*”，“？

”。

通配符——星号“*”可代表任何字符串,所以返回1

如果参数find_text就是问号或星号，则必须在这两个符号前加上“～”符号。

10）Text

TEXT（value,format_text）

Value为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。

Format_text为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。

说明

•Format_text不能包含星号（*）。

•通过“格式”菜单调用“单元格”命令，然后在“数字”选项卡上设置单元格的格式，只会更改单元格的格式而不会影响其中的数值。

使用函数TEXT可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。

11）index

返回表或区域中的值或值的引用。

函数INDEX（）有两种形式：

数组和引用。

数组形式通常返回数值或数值数组；引用形式通常返回引用。

INDEX（array,Row_num,column_num）返回数组中指定单元格或单元格数组的数值。

INDEX（reference,Row_num,column_num,area_num）返回引用中指定单元格区域的引用。

语法1（数组）

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。

•如果同时使用Row_num和column_num，函数INDEX返回Row_num和column_num交叉处的单元格的数值。

•如果将Row_num或column_num设置为0，函数INDEX则分别返回整个列或行的数组数值。

若要使用以数组形式返回的值，请将INDEX函数以数组公式（数组公式对一组或多组值执行多重计算，并返回一个或多个结果。

数组公式括于大括号（{}）中。

按Ctrl+Shift+Enter可以输入数组公式。

）形式输入，对于行以水平单元格区域的形式输入，对于列以垂直单元格区域的形式输入。

若要输入数组公式，请按Ctrl+Shift+Enter。

语法2（引用）

返回指定的行与列交叉处的单元格引用。

如果引用由不连续的选定区域组成，可以选择某一连续区域。

INDEX（reference,Row_num,column_num,area_num）

Reference对一个或多个单元格区域的引用。

•如果为引用输入一个不连续的区域，必须用括号括起来。

•如果引用中的每个区域只包含一行或一列，则相应的参数Row_num或column_num分别为可选项。

例如，对于单行的引用，可以使用函数INDEX（reference,,column_num）。

Row_num引用中某行的行序号，函数从该行返回一个引用。

COLUMN_num引用中某列的列序号，函数从该列返回一个引用。

Area_num选择引用中的一个区域，并返回该区域中Row_num和column_num的交叉区域。

选中或输入的第一个区域序号为1，第二个为2，以此类推。

如果省略area_num，函数INDEX使用区域1。

例如，如果引用描述的单元格为（A1:

B4,D1:

E4,G1:

H4），则area_num1为区域A1:

B4，area_num2为区域D1:

E4，而area_num3为区域G1:

H4

•在通过reference和area_num选择了特定的区域后，Row_num和column_num将进一步选择指定的单元格：

Row_num1为区域的首行，column_num1为首列，以此类推。

函数INDEX返回的引用即为Row_num和column_num的交叉区域。

•如果将Row_num或column_num设置为0，函数INDEX分别返回对整个列或行的引用。

•Row_num、column_num和area_num必须指向reference中的单元格；否则，函数INDEX返回错误值#REF!

。

如果省略Row_num和column_num，函数INDEX返回由area_num所指定的区域。

•函数INDEX的结果为一个引用，且在其他公式中也被解释为引用。

根据公式的需要，函数INDEX的返回值可以作为引用或是数值。

例如，公式CELL（"width",INDEX（A1:

B2,1,2））等价于公式CELL（"width",B1）。

CELL函数将函数INDEX的返回值作为单元格引用。

而在另一方面，公式2*INDEX（A1:

B2,1,2）将函数INDEX的返回值解释为B1单元格中的数字。

Eg1：

=INDEX（A2:

C11,3,3）返回区域A2:

C11中第3行和第3列交叉处的单元格C4的引用。

（15）

Eg2：

=INDEX（（A1:

C6,A8:

C11）,2,2,1）（A1:

C6,A8:

C11）为两个区域，2,2,1指获取第一个区域的第二行第二列交叉处的引用（0.69）

Eg3：

=SUM（INDEX（（A1:

C6,A8:

C11）,0,2,2））得到第二个区域A8:

C11第二列之和（9.35）

Eg4：

=SUM（B2:

INDEX（A2:

C6,5,2））由INDEX（A2:

C6,5,2）得到A2:

C6区域第五行第二列交叉处的引用（B6），再执行SUM（B2:

B6）得到累加和（2.42）

12）Match

MATCH（LOOKUP_VALUE,LOOKUP_ARRAY,MATCH_TYPE）

返回在指定方式下与指定数组匹配的数组中元素的相应位置。

如果需要找出匹配元素的位置而不是匹配元素本身，则应该使用MATCH函数而不是LOOKUP函数。

Lookup_value为需要在数据表中查找的数值。

•Lookup_value为需要在Look_array中查找的数值。

例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。

•Lookup_value可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。

Lookup_array可能包含所要查找的数值的连续单元格区域。

lookup_array应为数组或数组引用。

Match_type为数字-1、0或1。

MATCH-type指明WPS表格如何在lookup_array中查找lookup_value。

•如果Match_type为1，函数MATCH查找小于或等于lookup_value的最大数值。

lookup_array必须按升序排列：

...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE。

•如果Match_type为0，函数MATCH查找等于lookup_value的第一个数值。

lookup_array可以按任何顺序排列。

•如果Match_type为-1，函数MATCH查找大于或等于lookup_value的最小数值。

lookup_array必须按降序排列：

TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...，等等。

•如果省略Match_type，则假设为1。

说明

•函数MATCH返回lookup_array中目标值的位置，而不是数值本身。

例如，MATCH（"b",{"a","b","c"},0）返回2，即“b”在数组{"a","b","c"}中的相应位置。

•查找文本值时，函数MATCH不区分大小写字母。

•如果函数MATCH查找不成功，则返回错误值#N/A。

•如果MATCH_type为0且lookup_value为文本，lookup_value可以包含通配符、星号（*）和问号（?

）。

星号可以匹配任何字符序列；问号可以匹配单个字符。

13）Row/Column

Row返回单元格所在的行

Row（[reference]）

=row（）公式所在行的行号

=ROW（D4:

E6）引用中的第一行的行号（4）

Column与之类似返回列

14）Offset

＝Offset（指定点，偏移多少行，偏移多少列，返回多少行，返回多少列）

以指定点为原点建立坐标系，返回距离原点的值或者区域。

正数代表向下或向右，负数则相反。

选中e1：

f1，输入=OFFSET（A1:

B1,3,1），按下CTRL+SHIFT+回车键。

得出结果：

0.55，15

OFFSET（reference,rows,cols,height,width）

不写入返回行列数height,width，就默认与reference行列数相同

函数OFFSET实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。

函数OFFSET可用于任何需要将引用作为参数的函数。

例如，公式SUM（OFFSET（C2,1,2,3,1））将计算比单元格C2靠下1行并靠右2列的3行1列的区域的总值。

逻辑运算类：

15-18

15）If

IF（LOGICAL_TEST,VALUE_IF_TRUE,VALUE_IF_FALSE）

IF（设置的条件，满足条件返回的结果，不满足条件返回的结果）

16）And

AND（LOGICAL1,LOGICAL2,...）

所有参数的逻辑值为真时，返回TRUE；只要一个参数的逻辑值为假，即返回FLASE

17）Or

18）IS系列

常用判断检验，返回的都是布尔数值True和False。

常用ISERR，ISERROR，ISNA，ISTEXT，可以和IF嵌套使用。

计算统计类：

19-29

19）SUM/SUNMIF/SUMIFS

SUMIFS（SUM_RANGE,CRITERIA_RANGE1,CRITERIA1,[CRITERIA_RANGE2,CRITERIA2],…）

sum_range对一个或多个单元格求和，包括数值或包含数值的名称、区域或单元格引用。

忽略空白和文本值。

criteria_range1在其中计算关联条件的第一个区域。

criteria1条件的形式为数字、表达式、单元格引用或文本，可用来定义将对criteria_range1参数中的哪些单元格求和。

如，条件可以表示为41、">41"、D3、"香蕉"或"41"。

说明

仅在sum_range参数中的单元格满足所有相应的指定条件时，才对该单元格求和。

例如，假设一个公式中包含两个criteria_range参数。

如果criteria_range1的第一个单元格满足criteria1，而criteria_range2的第一个单元格满足critera2，则sum_range的第一个单元格计入总和中。

对于指定区域中的其余单元格，依此类推。

sum_range中包含TRUE的单元格计算为1；sum_range中包含FALSE的单元格计算为0（零）。

与SUMIF函数中的区域和条件参数不同，SUMIFS函数中每个criteria_range参数包含的行数和列数必须与sum_range参数相同。

您可以在条件中使用通配符，即问号（?

）和星号（*）。

问号匹配任一单个字符；星号匹配任一字符序列。

若要查找实际的问号或星号，请在字符前键入波形符（~）。

=SUMIFS（A2:

A7,B2:

B7,"=花*",C2:

C7,2）计算以“花”开头并由销售人员2售出的产品的总量（20）

=SUMIF（B2:

B7,"=花*",A2:

A7）计算以“花”开头总销售量（30）

=SUM（A2:

A7）计算以销售产品总量

20）SUMPRODUCT

在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

SUMPRODUCT（array1,array2,array3,...）

数组参数必须具有相同的维数，否则，函数SUMPRODUCT将返回错误值#VALUE!

。

函数SUMPRODUCT将非数值型的数组元素作为0处理。

=SUMPRODUCT（A2:

B4,C2:

D4）两个数组的所有元素对应相乘，然后把乘积相加，即3*2+4*7+8*6+6*7+1*5+9*3。

（156）

=SUMPRODUCT（A2:

A4,B2:

B4）相当于计算3*4+8*6+1*9=69

21）Count／Countif／Countifs

COUNTIFS（CRITERIA_RANGE1,CRITERIA1,[CRITERIA_RANGE2,CRITERIA2]…）

将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。

criteria_range1在其中计算关联条件的第一个区域。

criteria1条件的形式为数字、表达式、单元格引用或文本，可用来定义将对哪些单元格进行计数。

如，条件可以表示为41、">41"、D3、"香蕉"或"41"。

criteria_range2,criteria2,...附加的区域及其关联条件。

最多允许127个区域/条件对。

每个区域的条件一次应用于一个单元格。

若所有的第一个单元格都满足其关联条件，则计数增加1。

如果所有的第二个单元格都满足其关联条件，则计数再增加1，依此类推，直到计算完所有单元格。

若条件参数是对空单元格的引用，COUNTIFS会将该单元格的值视为0（零）。

您可以在条件中使用通配符，即问号（?

）和星号（*）。

问号匹配任一单个字符；星号匹配任一字符序列。

若要查找实际的问号或星号，请在字符前键入波形符（~）。

=COUNTIFS（A2:

A7,"<10",A2:

A7,">2"）计算2到10之间（不包括2和10）有几个数包含在单元格A2到A7中（3）

=COUNTIFS（A2:

A7,"<12",B2:

B7,">2011-3-4"）计算单元格A2到A7中包含小于12的数，同时在单元格B2到B7中包含晚于2011-3-4的日期的个数（3）

=COUNTIF（A2:

A7,">5"）计算A2:

A7单元格内容大于5的个数（4）

=COUNT（B2:

B4）计算B2:

B4包含数字的单元格数量（3）

=COUNT（B2:

B4,1）在上面基础上，再加一（4）

•函数COUNT在计数时，将把数字、日期、或以文本代表的数字计算在内；但是错误值或其他无法转换成数字的文字将被忽略。

•如果参数是一个数组或引用，那么只统计数组或引用中的数字；数组或引用中的空白单元格、逻辑值、文字或错误值都将被忽略。

如果要统计逻辑值、文字或错误值，请使用函数CountA，返回非空单元格数

22）Max/Min

返回数组或引用区域的最大/小值

23）Rank

RANK（number,ref,order）

返回一个数字在数字列表中的排位。

数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。

Number为需要找到排位的数字。

Ref为数字列表数组或对数字列表的引用。

Ref中的非数值型参数将被忽略。

Order为一数字，指明排位的方式。

如果order为0（零）或省略，对数字的排位是基于ref为按照降序排列的列表。

如果order不为