日常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
升级会员 