日常excel函数汇总.docx

日常excel函数汇总.docx

《日常excel函数汇总.docx》由会员分享，可在线阅读，更多相关《日常excel函数汇总.docx（13页珍藏版）》请在冰豆网上搜索。

日常excel函数汇总

1lookup関数

=LOOKUP（A1,{"a","b","c","d","e","h","I"},{9,9,8.5,8,7,5,4}）

这个最直观也最容易，

2.if関数

if怎么在数据中设置《优》《良》《中》《差》《极差》这几个条件啊？

IF函数不能同时输入这么多条件啊？

怎么办

=IF（A1>7,"优",IF（A1>5,"良",IF（A1>4,"中",IF（A1>3,"差","极差"））））

或者

=IF（OR（A1={2,3}）,"极差",IF（A1=4,"差",IF（A1=5,"中",IF（OR（A1={6,7}）,"良",IF（A1=8,"优",""）））））

可以用lookup函数做

=LOOKUP（A1,{2,4,5,6,8},{"极差","差","中","良","优"}）

单元格的7层以上的引用

=IF（A1=1,"A",IF（A1=2,"B",IF（A1=3,"C",IF（A1=4,"D",IF（A1=5,"E",IF（A1=6,"F",IF（A1=7,"G",IF（A1=8,"H",""））））））））&IF（A1=9,"I",IF（A1=10,"J",IF（A1=11,"K",IF（A1=12,"L",IF（A1=13,"M",IF（A1=14,"N",IF（A1=15,"O",IF（A1=16,"P",""））））））））&IF（A1=17,"Q",IF（A1=18,"R",IF（A1=19,"S",IF（A1=20,"T",IF（A1=21,"U",IF（A1=22,"V",IF（A1=23,"W",IF（A1=24,"X",""））））））））&IF（A1=25,"Y",IF（A1=26,"Z",""））。

方法2

A1=IF（A1=1,"A",IF（A1=2,"B",IF（A1=3,"C",IF（A1=4,"D",IF（A1=5,"E",IF（A1=6,"F",IF（A1=7,"G",IF（A1=8,"H","A2"）））））））A2=IF（A2=9,"I",IF（A2=10,"J",IF（A2=11,"K",IF（A2=12,"L",IF（A2=13,"M",IF（A2=14,"N",IF（A2=15,"O",IF（A2=16,"P","A3"））））））））.

A3=IF（A3=17,"Q",IF（A3=18,"R",IF（A3=19,"S",IF（A3=20,"T",IF（A3=21,"U",IF（A3=22,"V",IF（A3=23,"W",IF（A3=24,"X","A4"））））））））

A4=IF（A4=25,"Y",IF（A4=26,"Z",""））。

MATCH

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

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

语法

MATCH（lookup_value,lookup_array,match_type）

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

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

Match_type为数字-1、0或1。

Match-type指明MicrosoftExcel如何在lookup_array中查找lookup_value。

1

product

count

2

Bananas

25

3

Oranges

38

4

Apples

40

5

Pears

41

如果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（39,B2:

B5,1）由于此处无正确的匹配，所以返回数据区域B2:

B5中最接近的下一个值（38）的位置。

（2）

=MATCH（41,B2:

B5,0）数据区域B2:

B5中41的位置。

（4）

=MATCH（40,B2:

B5,-1）由于数据区域B2:

B5不是按降序排列，所以返回错误值。

（#N/A）

Index

INDEX用于返回表格或区域中的数值或对数值的引用。

函数INDEX（）有两种形式：

数组和引用。

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

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

Array为单元格区域或数组常数。

Row_num为数组中某行的行序号，函数从该行返回数值。

Column_num为数组中某列的列序号，函数从该列返回数值。

需注意的是Row_num和column_num必须指向array中的某一单元格，否则，函数INDEX返回错误值#REF!

。

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

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

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

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

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

。

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

area_num为0时，Reference中所有行或列。

　Excel中INDEX函数是很常用的查找引用函数。

通常情况下，嵌套其余函数一起使用，比如index+match。

　　在使用INDEX（）函数时，第二、三参数一般情况与MATCH（）函数配合使用，以实现动态查找引用的目的。

第一：

index函数用法解释

　　INDEX函数的用法是返回列表或数组中的元素值，此元素由行序号和列序号的索引值确定。

即行列交叉点所在的引用。

　　INDEX函数的语法为：

INDEX（区域,行,列）

　　需要注意一点的是：

此处的行列序号是相对于第一参数的区域而言，不是Excel工作表中的行或列序号。

　　如果区域只包含一行或一列，则相对应的参数row_num或column_num可以相应省略1个。

第二：

index函数简单举例应用

　　如下表所示，根据A1：

D5单元格区域，使用index函数查找A8单元格对应的产品名称。

　　提供三种解法，任意一种均可。

　　一：

=INDEX（A4:

D4,1,4）

　　二：

=INDEX（A4:

D4,,4）

　　三：

=INDEX（A1:

D5,4,4）

Round

=round四舍五入

＝roundup全入

＝rounddown全舍

$符号用了锁定行标或/和列标，公式复制的时候就由相对引用变为绝对引用了。

如在单元格B1输入=A1，在单元格C1输入=A$1，将B1、C1的公式向下复制，B2公式为=A2，而C2依然为=A$1。

同理，$符号在列标前，向右复制，锁定。

"$A$1"表示的意思是添中了绝对引用，与A1区别的是"$A$1"表示是不变的单元格的名称，A1就是单元格的名称，比如说在计算过程中每个数值都要乘以A1里的数据这时就可以在A1里加上绝对引用（按一下F4）就会变成"$A$1"了

条件在A1和B1的并列，

公式如下：

1、如果A1大于100，并且B1小于50（两个条件同时满足的情况）：

=if（and（A1>100,B1<50）,1,0）

2、如果A1>100,或者B1<50（并列条件的情况）：

=IF（OR（A1>100,B1<50）,1,0）

Lookup

1．LOOKUP函数

LOOKUP函数的功能是返回向量（单行区域或单列区域）或数组中的数值。

函数LOOKUP有两种语法形式：

向量和数组。

函数LOOKUP的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值；函数LOOKUP的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。

（1）向量形式：

公式为

=LOOKUP（lookup_value,lookup_vector,result_vector）

式中lookup_value—函数LOOKUP在第一个向量中所要查找的数值，它可以为数字、文本、逻辑值或包含数值的名称或引用；

lookup_vector—只包含一行或一列的区域lookup_vector的数值可以为文本、数字或逻辑值；

result_vector—为只包含一行或一列的区域其大小必须与lookup_vector相同。

（2）数组形式：

公式为

=LOOKUP（lookup_value,array）

式中array—包含文本、数字或逻辑值的单元格区域或数组它的值用于与lookup_value进行比较。

例如：

LOOKUP（5.2,{4.2,5,7,9,10}）=5。

注意：

lookup_vector的数值必须按升序排列，否则函数LOOKUP不能返回正确的结果。

文本不区分大小写。

如果函数LOOKUP找不到lookup_value，则查找lookup_vector中小于或等于lookup_value的最大数值。

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

2.VLOOKUP

“Lookup”的汉语意思是“查找”，在Excel中与“Lookup”相关的函数有三个：

VLOOKUP、HLOOKUO和LOOKUP。

下面介绍VLOOKUP函数的用法。

一、功能

在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的数据。

二、语法

标准格式：

VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）

三、语法解释

VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）可以写为：

VLOOKUP（需在第一列中查找的数据,需要在其中查找数据的数据表,需返回某列值的列号,逻辑值True或False）

1.Lookup_value为“需在数据表第一列中查找的数据”，可以是数值、文本字符串或引用。

2.Table_array为“需要在其中查找数据的数据表”，可以使用单元格区域或区域名称等。

⑴如果range_lookup为TRUE或省略，则table_array的第一列中的数值必须按升序排列，否则，函数VLOOKUP不能返回正确的数值。

如果range_lookup为FALSE，table_array不必进行排序。

⑵Table_array的第一列中的数值可以为文本、数字或逻辑值。

若为文本时，不区分文本的大小写。

3.Col_index_num为table_array中待返回的匹配值的列序号。

Col_index_num为1时，返回table_array第一列中的数值；

Col_index_num为2时，返回table_array第二列中的数值，以此类推。

如果Col_index_num小于1，函数VLOOKUP返回错误值#VALUE!

；

如果Col_index_num大于table_array的列数，函数VLOOKUP返回错误值#REF!

。

4.Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。

如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。

如果找不到，则返回错误值#N/A。

3.HLOOKUP函数

HLOOKUP函数的功能是从表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。

公式为

=（lookup_value，table_array，row_index_num，range_lookup）

式中row_index_num—table_array中待返回的匹配值的行序号。

row_index_num为1时，返回table_array第一行的数值，row_index_num为2时，返回table_array第二行的数值，以此类推。

如果row_index_num小于1，函数HLOOKUP返回错误值#VALUE!

；如果row_index_num大于table_array的行数，函数HLOOKUP返回错误值#REF!

。

式中的其他参数含义参阅VLOOKUP函数。

HLOOKUP函数与VLOOKUP函数的区别是：

当比较值位于数据表的首行，并且要查找下面给定行中的数据时，使用函数HLOOKUP；当比较值位于要进行数据查找的左边一列时，使用函数VLOOKUP。

VLOOKUP函数在首列进行检索，先得到的是行号，然后根据col_index_num参数指定的列标返回指定的单元格数值；而HLOOKUP函数在首行进行检索，先得到的是列标，然后根据row_index_num参数指定的行号返回指定的单元格数值.

CHOOSE（index_num,value1,value2,...）

Index_num用以指明待选参数序号的参数值。

Index_num必须为1到29之间的数字、或者是包含数字1到29的公式或单元格引用。

如果index_num为1，函数CHOOSE返回value1；如果为2，函数CHOOSE返回value2，以此类推。

如果index_num小于1或大于列表中最后一个值的序号，函数CHOOSE返回错误值#VALUE!

。

如果index_num为小数，则在使用前将被截尾取整。

Value1,value2,...为1到29个数值参数，函数CHOOSE基于index_num，从中选择一个数值或执行相应的操作。

参数可以为数字、单元格引用、已定义的名称、公式、函数或文本。

例如，下面的公式：

=SUM（CHOOSE（2,A1:

A10,B1:

B10,C1:

C10））

相当于：

　=SUM（B1:

B10）

TRANSPOSE

函数返回转置的单元格区域。

即将一行单元格区域转置成一列单元格区域，反之亦然。

在行列数分别与数组的行列数相同的区域中，必须将TRANSPOSE输入为数组公式。

使用TRANSPOSE函数，可在工作表中转置数组的垂直和水平方向。

该函数的表达式为，

　　TRANSPOSE（array）

　　其中参数array就是要转置的单元格区域或数组。

将数组的第一行变成第一列，第二行变成第二列，依次类推。

　　例如，数组{1,2,3,4,5,6}是一个2行3列的数组，利用TRANSPOSE函数进行转置，即公式为"=TRANSPOSE（{1,2,3;4,5,6}）"，返回的是数组{1,4,2,5,3,6}时，为3行2列的数组。

例如

注意输入上式后，必须按下组合键Ctrol+Shift+Enter,让上边自动变成数组公式才有效果，即带上大括号。

Text

将数值转换为按指定数字格式表示的文本。

语法

TEXT（value,format_text）

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

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

说明

Format_text不能包含星号（*）。

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

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

例如

也可以用来转化成想表示的日期或时间的格式。

=TEXT（NOW（）,"hh:

mm:

ss"）这是当前的时间

=TEXT（NOW（）,"yyyy-mm-ddhh:

mm:

ss"）这是当前的日期和时间

SUMIF（range,criteria,sum_range）

　　1）range为用于条件判断的单元格区域。

　　2）criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。

例如，条件可以表示为32、"32"、">32"或"apples"。

　　3）sum_range是需要求和的实际单元格。

说明

　　只有在区域中相应的单元格符合条件的情况下，sum_range中的单元格才求和。

　　如果忽略了sum_range，则对区域中的单元格求和。

　　MicrosoftExcel还提供了其他一些函数，它们可根据条件来分析数据。

例如，如果要计算单元格区域内某个文本字符串或数字出现的次数，则可使用COUNTIF函数。

如果要让公式根据某一条件返回两个数值中的某一值（例如，根据指定销售额返回销售红利），则可使用IF函数

sumproduct

一、基本功能

　　1．函数SUMPRODUCT的功能返回相应的区域或数组乘积的和。

　　2．基本格式SUMPRODUCT（数据1，数据2，……，数据30）．

基本计算

　　区域计算要求：

计算A、B、C三列对应数据乘积的和。

公式：

=SUMPRODUCT（A2:

A6,B2:

B6,C2:

C6）

计算方式：

=A2*B2*C2+A3*B3*C3+A4*B4*C4+A5*B5*C5+A6*B6*C6

即三个区域A2:

A6,B2:

B6,C2:

C6同行数据积的和。

返回值788。

　　数组计算要求：

把上面数据表中的三个区域A2:

A6,B2:

B6,C2:

C6数据按一个区域一个数组，计算对应数组积的和。

把A2:

A6,B2:

B6,C2:

C6分别作为一个数组,即A2:

A6表示为数组-{2;5;9;7;1}B2:

B6表示为数组-{3;5;7;2;6}C2:

C6表示为数组-{4;6;8;7;2}公式：

　　=SUMPRODUCT（{2;5;9;7;1},{3;5;7;2;6},{4;6;8;7;2}）

　　=788

　　注意：

数组数据用大括号{}括起来。

行数据之间用分号"；"分隔，如果是同一行的数据，用逗号","分隔。

二、用于多条件计数用数学函数SUMOPRODUCT计算符合2个及以上条件的数据个数　　⑴数据表如下数据表所示：

ABC

1姓名性别职称

2A男中一

3B女中二

4C女中一

5D男中一

6E女中一

7F男中二

8G女中二

9H男中一

10I男中一

11J女中一

统计表E列F列G列中一中二男女要求：

统计上面数据表中男、女性中分别是中一、中二的人数。

如下图片，A1：

C11数据区域，在E1：

G3区域统计男、女中中一和中二的人数是多少。

计算结果

⑵公式在数据统计区域中的F2单元格编辑如下公式：

　　=SUMPRODUCT（（$B$2:

$B$11=$E2）*（$C$2:

$C$11=F$1））　　向下复制到F3，向右复制到G3

=4

三、用于多条件求和对于计算符合某一个条件的数据求和，可以用SUMIF函数来解决。

如果要计算符合2个以上条件的数据求和，用SUMIF函数就不能够完成了。

这就可以用函数SUMPRODUCT。

　　用函数SUMPRODUCT计算符合多条件的数据和，其基本格式是：

SUMPRODUCT（条件1*条件2*……，求和数据区域）例

在A1中是900*B1中是5#C1中是28，D1是500，要在E1中求出满足这三个条件的D列的和

SUMIF只能是单条件求和，2007版有一个SUMIFS是多条件求和。

如果是2003版，可用SUMPRODUCT（）函数进行多条件求和。

E1=SUMPRODUCT（（LEFT（A1:

A20,3）="900"）*（B1:

B20="5#"）*（C1:

C20=28）*D1:

D20）

数据范围根据具体的数据情况修改。

INT函数

将小数点去掉，只保留整数

例如INT（4.8）=4

语法

VALUE（text）

Text   为带引号的文本，或对需要进行文本转换的单元格的引用。

说明

∙Text可以是MicrosoftExcel中可识别的任意常数、日期或时间格式。

如果Text不为这些格式，则函数VALUE返回错误值#VALUE!

。

∙通常不需要在公式中使用函数VALUE，Excel可以自动在需要时将文本转换为数字。

提供此函数是为了与其他电子表格程序兼容。

LEN

LEN返回文本字符串中的字符数

=len（1234）,返回4（1234为4个字符0）

=len（a1）,返回A1单元格中的字符数。

（文本格式中一个空格也为一个字符）

FIND用于查找其他文本字符串（within_text）内的文本字符串（find_text），并从within_text的首字符开始返回find_text的起始位置编号。

也可使用SEARCH查找其他文本字符串中的某个文本字符串，但是，FIND和SEARCH不同，FIND区分大小写并且不允许使用通配符。

Find

FIND（find_text,within_text,start_num）

Find_text是要查找的文本。

Within_text是包含要查找文本的文本。

Start_num指定开始进行查找的字符。

within_text中的首字符是编号为1的字符。

如果忽略start_num，则假设其为1。

提示

使用start_num可跳过指定数目的字符。

例如，假定使用文本字符串“AYF0093.YoungMensApparel”，如果要查找文本字符串中说明部分的第一个“Y”的编号，则可将start_num设置为8，这样就不会查找文本的序列号部分。

FIND将从第8个字符开始查找，而在下一个字符处即可找到find_text，于是返回编号9。

FIND总是从within_text的起始处返回字符编号，如果start_num大于1，也会对跳过的字符进行计数。

说明

Find_text中不能包含通配符。

Search

SEARCH（find_text,within_text,start_num）

SEARCHB（find_text,within_text,start_num）

Find_text要查找的文本。

Within_tex