序号
分类
函数名称及链接
语法
作用和功能
应用及备注
1
日

期

与

时

间

函

数
DATE
date(year,month,day)
返回特定的日期序列号
2
EDATE
edate(start_date,months)
按指定日期上下推月份并回到相应日期
3
DATEVALUE
datevalue(date_text)
将各种日期格式改为序列号便于计算
4
YEAR
year(serial_number)
返回日期序列号或日期格式中的年份
5
MONTH
month(serial_number)
返回日期序列号或日期格式中的月份
6
DAY
day(serial_number)
返回日期序列号或日期格式中的天数
7
HOUR
hour(serial_number)
返回日期序列号或日期格式中的小时数
8
MINUTE
minute(serial_number)
返回日期序列号或日期格式中的分钟数
9
TODAY
today()
返回当前日期序列号
易失性函数
10
NOW
now()
返回当前日期序列号并含当前时间
易失性函数
11
WORKDAY
workday(start_day,days,holidays)
一段时间内工作日计算函数
不含法定及特殊假日
12
NETWORKDAYS
networkdays(start_dayte,end_date,holidays)
计算两个日期之前的有效工作日天数,不包括周末和专门指定假日
13
WEEKDAY
weekday(serial_number,return_type)
返回某日期序列号代表的是星期数
14
DATEDIF
dateif(start_date,end_date,unit)
与Lotus兼容而设,用于计算两个日期之间的天数、月数和年数
年龄和工龄
15
时间函数应用实例
16
查



找



函



数
OFFSET
offset(reference,rows,cols,height,width)
以指定的引用为参照系，通过给定偏移量得到新引用
对记录进行排序、生成工资条、建立动态引用区
17
MATCH
match(lookup_value,lookup_array,match_type)
返回指定方式下的与指定数值匹配的数组中元素的相应位置
特性是返回区域内每个记录第一次出现的位置
18
INDIRECT
indirect(ref_text,al)
返回由文本字符串指定的引用，并将数值计算
19
CHOOSE
choose(index_num,valuel,value2,…)
按照给定的索引号，返回列表中的数值
常用于学生成绩等级转换、个税统计、返回区域查询
20
ADDRESS
address(row_num,column_num,abs_num,al,sheet_text)
按给定的行号和列标来建立文本类型的单元格地址，行号和列标可指定，也可由公计算产生,文本类型的地址也可以计算；
 第四个参数sheet_text如果是false，则表示R1C1样式
21
COLUMN
column(refernce)
返回单元格所在列的列标
22
COLUMNS
columns(refernce)
返回数组或引用的列数
23
HYPERLINK
hyperlink(link_location,friendly_name)
快捷的跳转方式以打开网络及其它工作簿
24
LOOKUP向量
lookup(lookup_value,lookup_vector,result_vector)
在单行区域或单列区域中查打数值，然后返第2个单行区域或单列区域中相同位置的数值
当查找区域无时，自动在取数区域取最大值
25
LOOKUP数组
lookup(lookup_value,array)
在数组的第一行或列中查找指定的数值，然后返回最后一行或列中相同位置的数值
26
LOOKUP应用1
 
27
HLOOKUP
hlookup(lookup_value,table_array,row_index_num,range_lookup)
在表格或数值数组的首行查找指定数值，并由此返回表格或数组当前列中指定行处的数值
28
VLOOKUP
vlookup(lookup_value,table_array,col_index_num,range_looup)
在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定的列处的数值
模糊查询、重复记录查询、多条件查询等，效果非常好
29
VLOOKUP应用1
30
VLOOKUP应用实例2
31
VLOOKUP应用3
32
VLOOKUP处理查找错误
33
VLOOKUP模块
34
数组公式构造原理
35
INDEX__数组形式
index(array,row_num,column_num)
返回列表或数组中的元素值，此元素由行序号和列序号的索引值给定
36
INDEX__引用形式
index(reference,row_num,column_num,area_num)
返回指定的行与列交叉处的单元格引用，如果引用由不连续区域组成，可选择其中之一区域
37
数


学


函


数
SUM
sum(number1,number2,…)
返回该区域内所有数据之和
文本为0,逻辑值为1
38
SUMIF
sumif(number1,number2,…)
按指定条件对区域内所数据之和
39
SUMPRODUCT
sumproduct(array1,array2,…)
在给定的几给数组中,将数组间对应的元素相乘,并返回乘积之和
40
SUBTOTAL
subtotal(function_num,ref1,ref2…)
在数据库分类汇总后再进行相关汇总工作
41
ROUND
round(number,num_digits)
对数值进行四舍五入
42
ROUNDUP
roundup(number,num_digits)
对数值按指定位数进行向上取整
43
ROUNDDOWN
rounddown(number,num_digits)
对数值按指定位数进行向下取整
44
ABS
abs(number)
对单元格数值进行绝对值处理
45
MOD
mod(number,divisor)
求余函数
正负数相除结果不同
46
RAND
rand()
产生随机函数
47
文


本


函


数
ASC
asc(text)
将双字节字符转换为单字节字符
即将全角字符改为半角字符
48
LEN_____LENB
len(text)

lenb(text)
测试字符串的长度
lenb测试双字节字符
49
LEFT____LEFTB
left(text,num_chars)

leftb(text,num_bytes)
从字符串左边第一个字符按参数定义提取字符LEFTB则提取双字节字符
50
RIGHT__RIGHTB
right(text,num_chars)

right(text,num_bytes)
从字符串右边第一个字符按参数定义提取字符RIGHTB则提取双字节字符
51
MID_____MIDB
mid(text,start_num,num_chars)
在单元格中按指定位置按参数定义提取字符
52
CONCATENATE
concatente(text1,text2…)
用于连接字符串,其功能与&符号相同
53
REPLACE_____REPLACEB
replace(old_text,start_num,num_shars,new_text)
使其它文本字符串并根据所指定的字符数替换某文本字符串中的部份文本
B则为更换的字节数
54
SUBSTITUTE
substitute(text,old_text,new_text,instance_num)
替换字符串中的字符的,可指定字符替换,也可批量执行替换
55
TEXT
text(value,format_text)
将数值转换为按指定数字格式表示文本
56
文本典型实例1
57
文本典型实例2
58
文本典型实例3
59
逻


辑


函


数
AND
and(logical1,logical2,…)
所有参数的逻辑值为真则TRUE,只要一个参数假则返回FALSE
在公式中经常使用*来表示and,效果一样
60
OR
or(logical1,logical2,…)
所有参数只要有一个参数为TRUE,即返回TRUE,任何一个参数的逻辑值为FALSE,即返回FALSE
在公式中经常使用+来表示or
61
NOT
not(logical)
对参数值求反,当确保一个值不等于某一特定值时使用
62
IF
if(logical_test,value_if_true,value_if_false)
执行真假值判断,根据逻辑计算的真假值,返回不同结果
对数值和公式进行条件检测
63
逻辑值与运算符
64
AND与OR和逻辑符区别
65
突破函数参数限制
66
统



计



函



数
AVERAGE
average(number1,number2,…)
统计所有参数的算术平均值,AVREAGEA则剔除非数值的参数
全部参数参与计数,逻辑值文本值为零
67
COUNT
count(value1,value2…)
返回包含数字\参数列表中数字的单元格的个数
只取含数值的单元格个数
68
COUNTA
counta(value1,value2…)
返回参数列表中非空值的单元格个数,忽略各种数据的格式,全部统计并计个数
统计区域中包含数据的单元格个数
69
COUNTBLANK
countbland(range)
返回参数列表中空值的单元格个数,忽略单元格中各种数据的格式
本公式与COUNTA相反,注意:=""的假空单元格也被视为空
70
COUNTIF
countif(range,criteria)
计算满足条件的单元格个数
71
FREQUENCY
frequency(data_array,bins_array)
以一列垂直数组返回某个区域中数据的频率分布
此函数必须用数组公式输入
72
LARGE
large(array,k)
返回数据集中第K个最大值
非常有用
73
SMALL
small(array,k)
返回数据集中第K个最小值
74
MODE
mode(number1,number2…)
测量函数返回左某一数组或数据区域中出现频率最多的数值
75
MAX
max(number1,number2…)
返回一个区域中数值最大的值
76
MIN
min(number1,number2…)
返回一个区域中数值最小的值
77
TRIMMEAN
trimmean(array,percent)
先将数据集中按比例去掉最大值和最小值之后返回平均值
78
RANK
rank(number,ref,order)
返回一个数字在列表中的排位,按其大小与列表中其它值的比值
默认0,表示从最大开始排
79
统计典型实例1
80
统计典型实例2
81
统计典型实例3
82
工

程

函

数
CELL
cell(info_type,reference)
返回某一个引用区域的左上角单元格的格式位置或内容等信息
公式中可设不同格式,相当于单元格格式设置
83
ERROR.TYPE
error.type(error_val)
返回对应于EXCEL中某一错误值的数字,或者没有错误返回#N/A,主要用于查错有追查出错原因
84
INFO
info(type_text)
便于用户了解系统运行环境以及工作表的运行状态memavail显示内存空间memused数据占用空间totmem全部内存空间
85
IS_类函数
iserror(value)

isblank(value)

iserr(value)

islogical(value)

istext(value)

isna(value)

isnontext(value)

isnumber(value)

isref(value)
值为任意错误值;

值为空白单元格;

值为除#N/A之外的任意错误值;

值为逻辑值;

值为文本;

值为错误值,即不存在之值;

值为不是文本的任意项(此函数的值为空);

值为数字;

值为引用;

excel常用函数


同一张工作表最多有256列（列、属性、字段、维度），最多有65536行（行、记录、数据）


Ctrl+多个工作表可形成工作组


保护单元格步骤：

取消整张工作表的锁定
锁定要保护的单元格
设置保护密码

相对引用与绝对引用：绝对引用前加$

相对引用：随着拖动引用单元格位置会变化
绝对引用：随着拖动引用单元格位置不会变化

count函数、countif函数、countifs函数countif(区间，“条件”)
match函数返回某个标签所在位置（组合成行、列坐标）
index函数可检索得到二维表中的数值
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)




参数
简单说明
输入数据类型




lookup_value
要查找的值
数值、引用或文本字符串


table_array
要查找的区域
数据表区域


col_index_num
返回数据在查找区域的第几列数
正整数


range_lookup
模糊匹配/精确匹配
TRUE（或不填）/FALSE


HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)




参数
简单说明
输入数据类型




lookup_value
要查找的值
数值、引用或文本字符串


table_array
要查找的区域
数据表区域


row_index_num
返回数据在区域的第几行数
正整数


range_lookup
模糊匹配/精确匹配
TRUE /FALSE（或不填）


indirect函数：间接使用，引用，并显示其内容
=INDIRECT(ref_text,[a1])
=INDIRECT(“A1”)——加引号，文本引用——即引用A1单元格所在的文本（B2)
=INDIRECT(A1)——不加引号，地址引用——因为A1的值为B2，B2又=11，所以返回。
left函数：得到字符串左部指定个数的字符

LEFT( string, n )
string ：必要参数。字符串表达式其中最左边的那些字符将被返回。如果 包含 Null，将返回 Null。
n ：必要参数；为 Variant (Long)。数值表达式，指出将返回多少个字符。如果为 0，返回零长度字符串 ("")。如果大于或等于 string 的字符数，则返回整个字符串。

right函数：从字符串右端取指定个数字符

Right ( string, n )

mid函数：从一个字符串中截取出指定数量的字符

MID(text,start_num,num_chars)

find函数：对原始数据中某个字符串进行定位

FIND(find_text,within_text,start_num)
Find_text 是要查找的字符串
Within_text 是包含要查找关键字的单元格
Start_num 指定开始进行查找的字符数

len函数：返回文本串的字符数
substitute函数：替换指定的文本

SUBSTITUTE(text,old_text,new_text,[instance_num])
Text 为需要替换其中字符的文本，或对含有文本的单元格的引用
Old_text 为需要替换的旧文本
New_text 用于替换 old_text 的文本
Instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换；如果缺省则将用 new_text 替换 TEXT 中出现的所有 old_text

concatenate函数：可将最多 255 个文本字符串合并为一个文本字符串。联接项可以是文本、数字、单元格引用或这些项的组合
exact函数用于检测两个字符串是否完全相同，相同返回TRUE，不同返回False

EXACT(text1,text2)

数据集中趋势描述：
算术平均数：


average（number1，[number2],…)


sumproduct(array1,array2,…)


几何平均数：

geomean(number1,number2,…)

众数：

mode(number1,number2,…)

中位数：

median(number1,number2,…)

数据离散程度描述：
极差 最大值-最小值:

max-min

四分位数

quartile(array,quart)
四分位差Qr = Q3 - Q1

方差：

var(number1,number2,…)
varp(number1,number2,…)

标准差：

stdev(number1,number2,…)
stdevp(number1,number2,…)

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


INDEX(array, row_num, [column_num])
INDEX 函数的数组形式具有下列参数:

数组    必需。 单元格区域或数组常量。

如果数组只包含一行或一列, 则相应的 row_num 或 column_num 参数是可选的。
如果数组具有多行和多列, 并且仅使用 row_num 或 column_num, 则 INDEX 返回数组中整个行或列的数组。


row_num    必需。 选择数组中的某行，函数从该行返回数值。 如果省略 row_num, 则需要 column_num。
column_num    可选。 选择数组中的某列，函数从该列返回数值。 如果省略 column_num, 则需要 row_num。



备注



如果同时使用 row_num 和 column_num 参数, INDEX 将返回 row_num 和 column_num 交叉处的单元格中的值。
row_num 和 column_num 必须指向数组中的一个单元格;否则, INDEX 将返回 #REF! 错误。
如果将 row_num 或 column_num 设置为 0 (零), 则 INDEX 将分别返回整列或整行的值的数组。 若要使用以数组形式返回的值, 请以数组公式的形式输入 INDEX 函数。



MATCH 函数在 范围 单元格中搜索特定的项，然后返回该项在此区域中的相对位置。


MATCH(lookup_value, lookup_array, [match_type])


MATCH 函数语法具有下列参数：




lookup_value    必需。 要在 lookup_array 中匹配的值。 例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。
lookup_value 参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。


lookup_array    必需。 要搜索的单元格区域。


match_type    可选。 数字 -1、0 或 1。 match_type 参数指定 Excel 如何将 lookup_value 与 lookup_array 中的值匹配。 此参数的默认值为 1。








Match_type
行为




1 或省略
MATCH 查找小于或等于 lookup_value 的最大值。 lookup_array 参数中的值必须以升序排序，例如：…-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE。


0
MATCH 查找完全等于 lookup_value 的第一个值。 lookup_array 参数中的值可按任何顺序排列。


-1
MATCH 查找大于或等于 lookup_value 的最小值。 lookup_array 参数中的值必须按降序排列，例如：TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, … 等等。




OFFSET函数从给定引用中返回引用偏移量


OFFSET(reference, rows, cols, [height], [width])
OFFSET 函数语法具有下列参数：

Reference    必需。 要作为偏移基准的参照。 引用必须引用单元格或相邻单元格区域。否则, OFFSET 返回 #VALUE! 。
Rows    必需。 需要左上角单元格引用的向上或向下行数。 使用 5 作为 rows 参数，可指定引用中的左上角单元格为引用下方的 5 行。 Rows 可为正数（这意味着在起始引用的下方）或负数（这意味着在起始引用的上方）。
Cols    必需。 需要结果的左上角单元格引用的从左到右的列数。 使用 5 作为 cols 参数，可指定引用中的左上角单元格为引用右方的 5 列。 Cols 可为正数（这意味着在起始引用的右侧）或负数（这意味着在起始引用的左侧）。
高度    可选。 需要返回的引用的行高。 Height 必须为正数。
宽度    可选。 需要返回的引用的列宽。 Width 必须为正数。

备注

如果 “行” 和 “cols 偏移” 引用覆盖了工作表的边缘, 则 offset 返回 #REF! 。
如果省略 height 或 width，则假设其高度或宽度与 reference 相同。
OFFSET 实际上并不移动任何单元格或更改选定区域；它只是返回一个引用。 OFFSET 可以与任何期待引用参数的函数一起使用。 例如，公式 SUM(OFFSET(C2,1,2,3,1)) 可计算 3 行 1 列区域（即单元格 C2 下方的 1 行和右侧的 2 列的 3 行 1 列区域）的总值。



HYPERLINK函数创建跳转到当前工作簿中的其他位置或以打开存储在网络服务器、 intranet或 Internet 上的文档的快捷方式。单击包含超链接函数的单元格时，Excel 将跳转到的位置列出，或打开您指定的文档。


HYPERLINK(link_location，[friendly_name])
HYPERLINK 函数语法具有下列参数：


Link_location    必需。可以作为文本打开的文档的路径和文件名。Link_location 可以指向文档中的某个更为具体的位置，如 Excel 工作表或工作簿中特定的单元格或命名区域，或是指向 Microsoft Word 文档中的书签。路径可以表示存储在硬盘驱动器上的文件，或是服务器上的通用命名约定 (UNC) 路径（在 Excel 中），或是在 Internet 或 Intranet 上的统一资源定位器 (URL) 路径。
注意   Excel Online HYPERLINK 函数仅对 Web 地址 (URL) 有效。Link_location 可以是放在引号中的文本字符串，也可以是对包含文本字符串链接的单元格的引用。
如果在 link_location 中指定的跳转不存在或无法定位，单击单元格时将出现错误信息。


Friendly_name    可选。单元格中显示的跳转文本或数字值。Friendly_name 显示为蓝色并带有下划线。如果省略 Friendly_name，单元格会将 link_location 显示为跳转文本。
Friendly_name 可以为数值、文本字符串、名称或包含跳转文本或数值的单元格。
如果 Friendly_name 返回错误值（例如，#VALUE!），单元格将显示错误值以替代跳转文本。




SUBTOTAL函数返回列表或数据库中的分类汇总。

SUBTOTAL(function_num,ref1,[ref2],…)

SUBTOTAL 函数语法具有以下参数：

Function_num     必需。 数字 1-11 或 101-111，用于指定要为分类汇总使用的函数。 如果使用 1-11，将包括手动隐藏的行，如果使用 101-111，则排除手动隐藏的行；始终排除已筛选掉的单元格。





Function_num （包含隐藏值）
Function_num （忽略隐藏值）
函数




1
101
AVERAGE


2
102
COUNT


3
103
COUNTA


4
104
MAX


5
105
MIN


6
106
PRODUCT


7
107
STDEV


8
108
STDEVP


9
109
SUM


10
110
VAR


11
111
VARP



Ref1     必需。 要对其进行分类汇总计算的第一个命名区域或引用。
Ref2,…     可选。 要对其进行分类汇总计算的第 2 个至第 254 个命名区域或引用。

常用快捷键

Ctrl+方向键，光标快速移动
Ctrl+Shift +方向键，快速框选
Ctrl+空格键，选定整列
Shift+空格键，选定整行
Ctrl+A ，选择整张表
Alt+Enter ，换行

Excel常用函数
1. RANK函数
说明：返回某一数值在某一组数值中的排名。
语法：RANK(number,ref,[order])
参数：
Number    必需：要找到其排位的数字。
Ref    必需：数字列表的数组，对数字列表的引用。 Ref 中的非数字值会被忽略。
Order    可选： 一个指定数字排位方式的数字。
如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是基于 ref 为按照降序排列的列表。
如果 order 不为零，Microsoft Excel 对数字的排位是基于 ref 为按照升序排列的列表。


2. COUNTIF函数
说明：统计满足某个条件的单元格的数量。
语法：COUNTIF(range, criteria)
参数：
range   （必需）：要统计数量的单元格组。
criteria   （必需）：用于决定要统计哪些单元格数量的数字、表达式、单元格引用或文本字符串。

3. COUNTA函数
说明：计算范围内不为空的单元格个数。
语法：COUNTA(value1, [value2], ...)
参数：
value1    必需：表示要计数的值的第一个参数。
value2, ...    可选： 表示要计数的值的其他参数，最多可包含 255 个参数。

4. COLUMN函数
说明：返回指定单元格引用的列号。
语法：COLUMN([reference])
参数：
reference　可选：要返回其列号的单元格或单元格范围。如果省略参数 reference 或该参数为一个单元格区域，并且 COLUMN 函数是以水平数组公式的形式输入的，则 COLUMN 函数将以水平数组的形式返回参数 reference 的列号。 


转载于:https://www.cnblogs.com/strivepy/p/10592956.html

1. countif函数
对指定区域中符合指定条件的单元格计数的一个函数;

该函数的语法规则如下：

countif（range，criteria）

参数：range 要计算其中非空单元格数目的区域

参数：criteria 以数字、表达式或文本形式定义的条件

例如：等于50=COUNTIF(数据区,"=50")

参考链接: countif函数说明及使用.

然而，countif仅能使用一个条件，countifs可以弥补这个遗憾**
2. countifs函数
用来统计多个区域中满足给定条件的单元格的个数;

countifs(criteria_range1,criteria1,criteria_range2,criteria2,…)

criteria_range1为第一个需要计算其中满足某个条件的单元格数目的单元格区域（简称条件区域），criteria1为第一个区域中将被计算在内的条件（简称条件），其形式可以为数字、表达式或文本。例如，条件可以表示为 48、“48”、">48" 、 “广州” 或 A3；

同理，criteria_range2为第二个条件区域，criteria2为第二个条件，依次类推。最终结果为多个区域中满足所有条件的单元格个数。

举例：=COUNTIFS(B2:B10,“广州”, C2:C10,">=3", D2:D10,">20")

参考链接：百度经验.

既然存在count，那必然也存在sum，对应的函数是sumif,sumifs

参考链接：

sumif函数介绍.
sumifs函数介绍.