excel公式

这是一篇关于excel公式的思维导图，ExCe中该公式是一个公式表达式的字符串，通常包含一个函数，运算符和常数的集合体。

Excel

引用

引用方法

区域

，

并集

空格

交集

跨表

表名 +！+区域

跨簿

[工作簿名]+工作表名+！+区域

引用方式

相对

绝对

混合

查看公式运算过程

点击‘公式’--＞点击‘求值’

使用F9

常用函数

求和

sum

求平均值

average

averagea

求平均值，将文本处理为0

计数

Count

计算区域数值单元格个数，忽略错误值

Counta

计算区域非空单元格个数

极值

MAX

最大值

不计算文本

min

最小值

数组

构成

：

区域构成数组

row函数

构成纵向数组

column函数

构成横向数组

sequence函数

生成多行多列数组

，分割

行排列

；分割

列排列

用Ctrl+shift+enter

生成｛｝，组成数组

写入

365

自动溢出

普通

手动选择区域

运算

一维数组与单值运算

生成一维数组

一维同向数量相同预算

生成一维数组

一维不同向数组运算

得到二维数组

一维数组与二维素组运算

得到与二维数组相同维度的数组

二维数组与单值运算

得到与二维数组相同的二维数组

二维数组之间的运算

维度必须相同，并产生维度相同的数组

条件判断

if

判断、选择

嵌套使用

特性

可返回单元格区域引用

如条件为数组，可返回数组

多条件判断选择

Ifs

返回对应条件的值

Switch

返回对应条件的值，并可设定条件不满足时的默认值

逻辑运算函数

and

or

not

错误

错误类型

#VALUE

使用错误的参数或运算类型对象

#DIV/0

除数为0

#NAME？

使用了不能识别的文本

#N/A

函数或公式中没有可用数值

#REF!

单元格引用无效

#NUM！

公式或函数中某个数字有问题

#NULL！

当试图为两个不交叉区域制定交叉点时

信息函数

IStext

检测是否为文本

Isnontext

检查是否为非文本

Isnumber

检测是否为数值

Iseven

检测是否为偶数

Isodd

检测是否为奇数

ISblank

检测是否为空

Isformula

检测是否包含公式

Isref

检测是否为单元格引用

Islogical

检测值是否为逻辑值

N

将不是数值格式的值转为数值格式

数值

返回原数值

文本数值

0

文本

0

日期

返回日期序列

逻辑值

0或1

错误值

错误值

T

将不是文本格式的值转为文本格式

数值

返回空值

文本

返回文本

日期、逻辑值

返回空值

错误值

返回错误值

识别错误

ISerror

识别所有错误

Iserr

识别除NA之外的所有错误

Isna

识别NA错误

运算函数

求和

sum

求和

可将文本处理为0

利用数组

单条件求和

多条件求和

单条件计数

多条件计数

sumproduct

数组、区域的相乘后求和

数组、区域维度要相同

非数值行处理为0

sumif

对单元格、区域进行条件求和

第一参数为条件区域、第三参数为计算区域

不能为数组

不要求必须为一维

第三参数与第一参数区域相同可以省略

第二参数为判断条件

可以为数组

做大小判断时符号需要使用双引号

通配符使用方法

？

匹配任意单个字符

*

匹配任意长度字符

~

查找实际问号或星号，加在该字符钱

Sumifs

多条件求和

第一参数为求和区域

可以为区域，不可以为数组

可以是一维、二维区域

之后为条件区域和判断条件对

条件区域

可以为区域，不可以为数组

可以是一维、二维区域

判断条件

计数

count

统计数值个数

不计算文本、错误和空

借助数组

单条件计数

多条件计数

counta

统计非空个数

包括数字、文本、错误

Countif

参数一

条件判断区域

参数二

条件

支持通配符

平均

average

平均

Averageif

条件平均

用法与求和相同

Averageifs

多条件平均

Trimmean

修剪平均

去掉最高值，去掉最小值

参数一

区域

参数二

分子

去掉值的个数

分母

取值个数

极值

Minifs、maxifs

条件最小值、最大值

Small、 large

极小值、极大值

可制定个数

min、MAX

最小值、最大值

时间函数

year、month、day

年、月、日

hour、minute、sector

时、分、秒

date

日期合并

Today、now

系统日期、时间

edate

月份偏移函数

通过第二参数指定偏移月份数

Eomonth

月末日期函数

Datedif

计算时间间隔的函数

weekday

返回星期几

weeknum

返回第几周

workday

计算工作日函数

指定工作天数、返回日期

可以指定假期

workday.intl

功能跟workday类似

第三参数可以通过“0101010”的方式设置休息、工作日

networkday

计算工作日天数

第三参数可以指定休息日

networkday.intl

与networkday函数类似

用法与workday.intl类似

文本处理函数

Len lenb

文本长度计算函数

Left left b

从左提取字符函数

right tightb

从右提取字符函数

Mid midb

从指定位置提取字符

find findb

文本查找函数

不支持通配符

参数

参数一

要查找的字符串

参数二

被查找的字符串

参数三

开始查找的位置

search searchb

文本查找函数

支持通配符

参数

参数一

要查找的字符串

参数二

被查找的字符串

参数三

开始查找的位置

replace replaceb

按位置替换字符函数

Substitute

按值替换字符函数

code char

字符与数字代码转换

rept

重复字符函数

clean trim

清除不可见字符

concatenate phonetic

字符串链接函数

concat

数组合、区域并为文本

无分隔符

textjoin

数组区域合并为文本

可指定分隔符

let

简化公式中的变量

arraytotext evaluate

数组与文本的互转

text

格式化函数

upper lower proper

字母大小写转换

数据处理函数

int

向下取整函数

Trunc

截尾取整函数

mod

取余函数

round

四舍五入

rounddown

向下舍入函数

roundup

向上舍入函数

ceiling

按倍数向上进位函数

floor

按倍数向下舍位函数

ran'd

生成随机小数

randbetween

生成指定范围内的随机整数函数

randarray

生成随机数组

查找函数

查找值函数

match

根据值找位置

第一参数

要查找的值

第二参数

待超找的一维区域或数组

第三参数

0

精确查找

1

查找小于超找值的值

-1

查找大于超找值的值

模糊匹配必须升序排列

Xmatch

根据值查找位置

第一参数

要查找的值

第二参数

待查找的一维区域或数组

第三参数

0

精确匹配

-1

精确匹配或者下一个较小的项

1

精确匹配或下一个较大的项

2

通配符匹配

第四参数

0

从前往后查找

-1

从后往前查找

1

二进制文件搜索，升序排序

2

二进制文件搜索，降序排序

不需升序或降序排列

此函数为365函数

Vlookup

垂直查找函数

返回值，不能返回引用

参数

第一参数

查找值

使用N/T配合if使用数组

N（IF（｛1｝，区域））

数字

T（IF（｛1｝，区域））

文本

第二参数

查找区域

查找列必须为第一列

如果不在第一列，可以使用if构造

第三参数

返回值所在的列

可以为数组

第四参数

查找方式

0

精确匹配

1

模糊查找

HLOOKUP

水平查找函数

返回值，不能返回引用

用法与VLOOKUP类似

LOOKUP

只能近似匹配

可垂直查找和水平查找

参数

第一参数

查找值

第二参数

查找区域

要求升序排列

第三参数

返回区域

可返回引用函数

XLOOKUP

可垂直、水平查找

可返回引用

参数

第一参数

查找值

第二参数

查找区域或数组

第三参数

返回区域或数组

第四参数

错误返回值

此参数可嵌套其他函数，包括XLOOKUP

第五参数

匹配模式

0

精确匹配

-1

精确匹配或者下一个较小的项

1

精确匹配或者下一个较大的项

2

通配符匹配

第六参数

搜索模式

1

从前往后搜索

-1

从后往前搜索

2

二进制文件搜索，要求升序排列

-2

二进制文件搜索，要求降序排列

INDEX

通过索引返回值或引用

可返回引用

在第一参数为引用，且第二、三参数不为数组时

参数

第一参数

区域或数组

区域

数组

第二参数

行、列中的索引

此参数输入0，则表示所有值

可以为数组

第三参数

列、行中的索引（可选）

此参数输入0，则表示所有值

可以为数组

CHOOSE

根据第一参数返回值

可返回引用

参数

第一参数

值或单元格引用

数组

可以用来构造区域

第2——254参数

根据第一参数的返回值

也可以是引用

INDIRECT

根据文本地址返回引用

参数

第一参数

以文本形式表现的地址

可以为区域或数组

第二参数

0

参数一为R1C1样式

1

参数一为A1样式

OFFEST

单元格偏移，扩展后引用

参数2、3、4、5可以是负数

参数

参数一

定位单元格

参数二

行偏移量

参数三

列偏移量

参数四

高度扩展

参数五

宽度扩展

可以是数组，并可配合SUBTOTAL函数实现三维分组统计汇总

排序、筛选函数

UNIQUE

返回区域、引用的唯一值函数

参数

参数一

待处理的区域、数组

一维直接返回不重复的值

二维返回多列均不相同的值

参数二

0

比较各行返回唯一值

1

比较各列返回唯一值

参数三

1

返回只出现一次的值

0

返回每个不同的项

FILTER

排序函数

参数

参数一

待筛选的区域或数组

参数二

筛选条件

TRUE、FALSE的数组

参数三

筛选不到的情况

SORT

排序函数

参数

参数一

待排序的数组、区域

参数二

选择按行、列进行排列

参数三

1

升序

-1

降序

参数四

false

按行排序

true

按列排序

SORTBY

基于相应范围或数组中的值对范围或数组内容进行排序

参数

参数一

排序的区域或数组

参数二

排序依据的列

参数三

1

升序

-1

降序

可增加

TRANSPOSE

转置函数

参数

参数一

待转置的区域或数组

高级函数

cell

获取单元格信息

参数

参数一

选择要查找的信息类型

“ROW”

行

“co'l”

列

“address”

单元格地址

“contents”

单元格内容

“filename”

文件名

参数二

要查询信息的单元格

此参数省略，则返回活动当前单元格信息

MMULT

矩阵乘积函数

参数

参数一

区域、数组

参数二

区域、数组

根据矩阵相乘原则，参数一的列数必须与参数二的行数相同

SUBTOTAL

多种方式汇总函数

可忽略隐藏，不可忽略筛选

支持三维引用

参数

参数一

汇总方式

可以为数组

1-9

不忽略隐藏

101-109

忽略隐藏

参数二

汇总区域

可添加多个区域

三维引用

构造数组，进行多维计算

函数

构造区域、数组

INDIRECT

通过文本地址实现引用

可实现跨表引用

OFFSET

汇总计算

SUBTOTAL

条件、多条件求和、计数、平均、最大、最小值函数

条件格式