EXCEL常用函数公式
EXCEL常用函数公式
索引
文档
关联匹配
VLOOKUP
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
参数说明：
lookup_value：要查找的值或要比较的值。
table_array：包含要在其中进行查找的数据表的区域。
col_index_num：要返回的值所在列的索引号，从table_array的第一列开始计数。
range_lookup：可选参数，指示是否进行近似匹配。如果为TRUE（或忽略该参数），则默认进行近似匹配；如果为FALSE，则只进行精确匹配。
简明语法形式:
VLOOKUP(要查找的内容、要查找的数据区域、包含要返回的值的范围内的列号、FALSE)
基础示例:
注意:VlookUP只能自左向右查,不能通过右侧列的数据向左查
跨表使用
HLOOKUP
功能：
根据纵向查横向 查询某列的某行数据
在表格的首行或数值数组中搜索值，然后返回表格或数组中指定行的所在列中的值。HLOOKUP中的H代表“行”。
简明语法形式::
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
参数说明:
| 参数 | 简单说明 | 输入数据类型 |
|---|---|---|
| lookup_value | 要查找的值 | 数值、引用或文本字符串 |
| table_array | 要查找的区域 | 数据表区域 |
| row_index_num | 返回数据在要查找的区域的第几行数 | 正整数 |
| range_lookup | 模糊匹配/精确匹配 | TRUE /FALSE（或不填） |
例：
=HLOOKUP("车轴",A1:C4, 2, TRUE) 在首行查找车轴，并返回同列（列A）中第2行的值。
LOOKUP和HLOOKUP区别：当比较值位于数据表格的首行时，如果要向下查看指定的行数，则可使用HLOOKUP。当比较值位于所需查找的数据的左边一列时，则可使用VLOOKUP。
INDEX
功能
返回表格或区域中的值或值的引用
语法：
INDEX(array,row_num, [column_num])**
例：
INDEX(B2:D11,3,3)位于区域A2:B3中第三行和第三列交叉处的数值。
MATCH
功能：
用于返回指定内容在指定区域（某行或者某列）的位置。
语法：
=MATCH(lookup_value,lookup_array, [match_type])
例：
=MATCH(41,B2:B5,0) 单元格区域B2:B5中值41的位置
match_type：
1或省略：MATCH 查找小于或等于lookup_value的最大值。
0：MATCH查找完全等于lookup_value的第一个值。
-1：MATCH查找大于或等于lookup_value的最小值。
RANK
求某一个数值在某一区域内一组数值中的排名
语法：
=RANK(number,ref,[order])
例：
=RANK(A3,A2:A6,1) A3在上表中A2:A6的排位排名方式：0是降，1是升序，默认为0
ROW
功能：
返回引用的行号。
语法:
= ROW([reference])
例：
= ROW() 公式所在行的行号
COLUMN
功能：
返回单元格所在的列。
语法
=COLUMN（reference)
例：
=COLUMN (D10) 返回4，因为D列是第四列。
OFFSET
功能：
返回对单元格或单元格区域中指定行数和列数的区域的引用。返回的引用可以是单个单元格或单元格区域。可以指定要返回的行数和列数。
语法：
=OFFSET(reference, rows, cols,[height], [width])
例：
=OFFSET(D3,3,-2,1,1)显示单元格 B6中的值，其中3为下方三行，-2为左方两行，1为行高和列宽。
清洗处理类
一览
截取字符串：使用MID /LEFT/ RIGHT
替换单元格中内容：SUBSTITUTE /REPLACE
合并单元格：使用CONCATENATE
清除字符串空格：使用TRIM/LTRIM/RTRIM
MID
功能：
从中间截取字符串
语法：
=MID(text,start_num, num_chars)
例：
=MID(A2,1,5)
从A2内字符串中第1个字符开始，返回5个字符。
LEFT
功能：
从左截取字符串。
语法：
=LEFT(text,[num_chars])
例：
=LEFT(A2,4) 第一个字符串中的前四个字符
RIGHT
功能：
从右截取字符串。
语法：
=RIGHT(text,[num_chars])
例：
=RIGHT(A2,5)第一个字符串的最后5个字符
SUBSTITUTE
功能：
在文本字符串中用new_text替换old_text。
语法：
=SUBSTITUTE(text,old_text, new_text, [instance_num])
例：
=SUBSTITUTE(A2, "销售", "成本")将“销售”替换为“成本”（成本数据）替换部分电话号码。
REPLACE
功能：
替换掉单元格的字符串。
语法：
=REPLACE(old_text,start_num, num_chars, new_text)
例：
=REPLACE(A2,6,5,"") 在A2中，从第六个字符(f)开始使用单个字符替换五个字符。
REPLACE和SUBSTITUTE区别：两个函数很接近，不同在于REPLACE根据位置实现替换，需要提供从第几位开始替换，替换几位，替换后的新的文本；而SUBSTITUTE根据文本内容替换，需要提供替换的旧文本和新文本，以及替换第几个旧文本等。因此REPLACE实现固定位置的文本替换，SUBSTITUTE实现固定文本替换。
CONCATENATE
功能：
将两个或多个文本字符串联接为一个字符串。
语法：
=CONCATENATE(text1,[text2], ...)
合并单元格中的内容，还有另一种合并方式是&，需要合并的内容过多时，CONCATENATE 效率更快。
例：
=CONCATENATE(B2, " ", C2) 联接三部分内容：单元格B2中的字符串、空格字符以及单元格C2中的值。
TRIM
功能：
除了单词之间的单个空格之外，移除文本中的所有空格
语法:
=TRIM(text)
Text为要去掉空格的文本
例：
=TRIM("First Quarter Earnings ") 从公式的文本中移除前导空格和尾随空格。
LTRIM
功能：
从字符串左侧删除空格或其他预定义字符。
语法：
=LTRIM (string, [charlist])
FIND
功能：
查找文本位置
语法：
=FIND(find_text,within_text, [start_num])
例：=FIND("M",A2) 单元格A2中第一个“M”的位置
SEARCH
功能：
返回一个指定字符或文本字符串在字符串中第一次出现的位置,从左到右查找
语法：
=SEARCH(find_text,within_text,[start_num])
例：
=SEARCH("e",A2,6) 单元格A2中的字符串中，从第6个位置起，第一个“e”的位置。
LEN
功能：
返回文本字符串中的字符个数。
语法
=LEN(text)
LENB
功能：
返回文本字符串中用于代表字符的字节数。
语法：
=LENB(text)
例：
=LEN(A1)A1单元格字符串的字节数。
逻辑计算类
IF
功能：
使用逻辑函数IF 函数时，如果条件为真，该函数将返回一个值；如果条件为假，函数将返回另一个值。
语法：
=IF(Logical,Value_if_true,Value_if_false)
例:
COUNTIF
功能：
用于统计满足某个条件的单元格的数量；例如，统计特定城市在客户列表中出现的次数。
语法：
=COUNTIF(单元格1: 单元格2 ,条件)
例:
AND
功能：
逻辑判断，相当于“并”。
语法：
全部参数为True，则返回True，经常用于多条件判断。
例：
=AND(A2>1,A2<100) 如果A2大于1并且小于100，则显示TRUE；否则显示FALSE。
OR
功能：
逻辑判断，相当于“或”。
语法：
只要参数有一个True，则返回Ture，经常用于多条件判断。
例：
=OR(A2>1,A2<100) 如果A2大于1或者小于100，则显示TRUE；否则显示FALSE。
计算统计类
MIN
功能：
找到某区域中的最小值。
语法：
=MIN(number1, [number2], ...)
例：
=MIN(D2:D11) 区域D2:D11中的最小数。
MAX
功能：
找到某区域中的最大值。
语法：
=MAX(number1, [number2], ...)
例：
=MAX(A2:A6) 区域A2:A6中的最大值。
AVERAGE
功能：
计算某区域中的平均值。
语法：
=AVERAGE(number1, [number2], ...)
例：
=AVERAGE(D2:D11) 单元格区域D2到D11中数字的平均值。
COUNT
功能：
计算含有数字的单元格的个数。
语法：
=COUNT(value1, [value2], ...)
例：
=COUNT(A2:A7) 计算单元格区域A2到A7中包含数字的单元格的个数。
FOUNTIFS
功能：
统计一组给定条件所指定的单元格数。
语法：
COUNTIFS(criteria_range1,criteria1, [criteria_range2, criteria2],…)
例：
=COUNTIFS(A2:A7,"<6",A2:A7,">1")计算1和6之间（不包括1和6）有几个数包含在单元格A2到A7中。
SUM
功能：
计算单元格区域中所有数值的和。
语法：
=SUM(单元格1:单元格2)
例：
=SUM(A2:A10) 将单元格A2:10中的值加在一起。
SUMIF
功能：
求满足条件的单元格和。
语法：
=SUMIF(range,criteria, [sum_range])
例：
=SUMIF(A2:A7,"水果",C2:C7) “水果”类别下所有食物的销售额之和。
SUMIFS
功能：
对一组满足条件指定的单元格求和。
语法：
=SUMIFS(sum_range,criteria_range1, criteria1, [criteria_range2, criteria2], ...)
例：
=SUMIFS(A2:A9, B2:B9, "=香*", C2:C9, "卢宁") 计算以“香”开头并由“卢宁”售出的产品的总量。
SUMPRODUCT
功能：
返回相应的数组或区域乘积的和。
语法：
=SUMPRODUCT (array1， [array2]， [array3]， ...)
例：
=SUMPRODUCT(Table1!A1:Table1!A100,Table2!B1:Table2!B100) 计算表格1的A1到A100与表格2的B1到B100的乘积和，即A1B1+A2B2+A3*B3+…
STDEV
功能：
基于样本估算标准偏差。
语法：
STDEV(number1,[number2],...)
例：
=STDEV(D2:D17) 列的标准偏差
SUBSTOTAL
功能：
返回列表或数据库中的分类汇总。
语法：
=SUBTOTAL(function_num,ref1,[ref2],...)
例：
=SUBTOTAL(9,A2:A5)使用9作为第一个参数,算出的单元格A2:A5中分类汇总的值之和。
INT/ROUND
功能：
ROUND 函数将数字四舍五入到指定的位数。
语法：
=ROUND(A1, 2)
例：
=ROUND(2.15, 1)将2.15四舍五入到一个小数位
功能：
INT将数字向下舍入到最接近的整数。
语法:
=INT(8.9) 将 8.9 向下舍入到最接近的整数。