excel常用函数大全

excel 职场办公, 数据分析常用函数, 分类解析，用户可以更快速、更准确地完成数据处理和计算工作，提高工作效率！

函数

日期时间

快捷键

ctrl + shift + ~ 常规

ctrl + shift + # 日期

ctrl + shift + @ 时间

ctrl + ; 当前日期

ctrl + shift + ; 当前时间

文本转换

timevalue

文本--->时间

datevalue

文本--->日期

日期

提取拼接

year 年

month 月

day 日

date 日期

月

datedif 日期差

第三参数：y,m,d,ym,md,yd

eomonth 月底日期

参数：日期，偏移月份

edate 日期偏移

偏移月，日不变

周

weekday 本周第几天

注意第二参数

weeknum 今年第几周

工作日

workday

例：3个工作日后日期

workday.intl

自定义 ，1 表示休息，0 表示上班。例双休："0000011"

networkdays

两个日期间工作日

networkdays.intl

两个日期间工作日，自定义上班日

日历制作

时间

提取拼接

hour 时

minute 分

second 秒

time 时间

数字函数

计数

count 计算数字单元格

counta 计算非空

countblank 计算空白

counta(区域&"") 计算全部单元格

countif

单列不重复计算

条件可为引用

countifs

多列不重复计算

平均

average

数字区域平均

averagea

非空区域平均

averageif

条件平均，可计算后几位平均值

trimmean

去掉极值后平均，注意第二参数

求和

sum

合并单元格求和

sum从下往上求和

sumproduct

参数数组维度相同

非数字元素当0处理

sumif

条件数组

计算区域if选择

相对区域条件

sumifs

通配符

数组

引用区域条件

乘积类

product 乘

注：乘积区域文本视为1

sumproduct

数组先相乘后相加

mmult

行列式乘积

power 幂

简写 ^

sqrt 平方根

subtotal

功能

以上各模块都支持

优点

可以计算所有值

可以只计算可见值

隐藏单元格

筛选单元格

数字操作

截取

int

向下取整

trunc

截尾取数

四舍五入

round

<5舍去，>=5向着远离0方向进位

roundup

全部向着远离0的方向进位

rounddown

同trunc

倍数取数

ceiling

按参数倍数向上取数

floor

按参数倍数向下取数

取余

mod

符号相同，求余的部分

符号不同，求差的部分

结果值与除数的正负相同

绝对值

abs

进制转换

base

错误值

NA生成错误值

rank 排名

多列排名加括号

阿拉伯和罗马转换

arabic 罗马-->阿拉伯

roman 阿拉伯-->罗马

正负标识 sign

判断奇偶

iseven 偶

isodd 奇

N函数 文本转0

文本

字符串操作类

字符串连接

concat

concatenate

phonetic 合并文本，忽略数字

字符串截取

left 从左截取

right 从右截取

mid 从中间截取

字符串查询

len 字符长度

lenb 字节长度

find

返回字符位置，区分大小写 findb 字节

search

同find，不区分大小写，可使用通配符 searchb 字节

字符替换

replace

替换一段值，参数为0可插入字符

substitute

查找字符并替换

数字字符转换

char

数字转换字符

code

字符转换数字

字符比较

=

不区分大小写

exact

区分大小写

rept

重复

T

非文本转换文本

通配符

? 单个字符

* 多个字符

~ 代表通配符

字符串格式类

英文大小写

proper

每个词首字母大写，其余小写

upper

全部大写

lower

全部小写

去除空格

trim

去除字符串两端空格

人民币美元

RMB

数字转换人民币格式

DOLLAR

数字转换美元格式

文本转换

numbervalue

文本转数字

value

文本、日期转数字

clean

清除非打印字符，无法计算时使用

numberstring

数字转汉字

单元格格式

内容格式

[>0]格式;[<0]格式;0格式;文本格式

数字范围可变

字体颜色

[颜色10][>50]"优秀";[洋红][<20]"差";"合格";

[黑色]、[白色]、[红色]、[蓝色]、[绿色]、[洋红]

颜色1.....颜色56

占位符

G/通用格式

常规

0

数字占位符，当数字比代码符少时用0占位

#

数字占位符，只显示有效数字，不显示无意义的0值

？

数字占位符，当数字比代码少时用空格占位，可用于分数显示

@

文本占位符，表示输入文本本身

特殊代码

_

留出与后面字符一样宽的空位

*

重复下一个字符，直到充满列宽

,

千位分隔符，一般与“#”配合使用，简化数字

\

格式化，强制显示。

!

同\，显示不能显示出的字符

%

将数字缩小100倍

汉字格式

[dbnum1]G/通用格式

1、2、3

日期时间

日期

示例：yyyy-m-d

时间

示例： AM/PM hh:mm:ss.0

TEXT函数

text函数可以直接以上格式做参数

查找匹配

vlookup

精确匹配

参数为0

模糊匹配

参数为1

反向匹配

2列

if({1,0},列1,列2)

多列

choose({1,2,3...},列1,列2,列3...)

数组

第一、第三参数可用数组

hlookup

同vlookup，横向

lookup

二参数

要求：条件第一列，结果最后一列

三参数

条件不用在第一列

升序排列

就算不是升序lookup也当做升序处理

二分法查找

match

精确匹配

参数0

模糊匹配

升序

参数1

降序

参数-1

二分法·

模糊匹配使用二分法，效率高

模糊匹配中查找值重复，则找到最后一个位置

index

三参数

二维区域

一维区域

四参数

增加多区域选择

choose

相当于java的switch

可制作自定义区域

frequency

分布函数

hyperlink

网址链接

文件、文件夹

绝对地址

相对地址

局域网

其他同事电脑共享

small

最小的数提取

large

最大的数提取

mode.mult

众数

数字

文本

用match转换数字

median

中数

区域函数

offset

区域平移

数组

表示多维区域，分别计算

address

数字-->地址

indirect

地址-->区域

rows

区域所占行数

areas

引用区域包含不连续区域数

cell

第一参数

address

文本地址

row

行

col

列

filename

文件名

contents

内容

第二参数

选择区域，缺省表示鼠标区域左上单元格

数组

transpose转置

munit

生成斜角矩阵

区域交叉

两个区域中间空格

配合定义名称计算交叉值

定义名称

根据所选内容创建

逻辑

xor异或

判断函数

isformula是否公式

isref是否引用

isblank是否空白

islogical是否逻辑值

数据透视表函数

getpivotdata

格式

第一参数：求什么字段

第二参数：在哪个透视表求

第三参数：初步判断哪个类别

第四参数：具体哪个类别哪个条件

其他条件...

方法

一、保证分析菜单栏中pivotdata打开

二、引用透视表单元格

三、将文本条件修改为引用

优点

公式简单效率高

直接引用透视表数据而非数据源

随着透视表刷新而刷新数据

循环引用

流程

一、设置迭代器

二、设置开关、计数器

三、写公式

数据库函数

举例

DSUM(数据区域,列,条件...)

特点

可以应用单元格的条件

数据区域

引用

数据从首行开始只引用列号

列

支持数组

条件

支持通配符

无“=”表示包含

有“=”表示等于

空白单元格表示所有数据