文章目录
- 一、运算符
- 1、数学运算符
- 2、比较运算符
- 3、连接运算符
- 4、引用运算符
- 5、运算符优先级
- 6、绝对引用和相对引用
- 二、信息和逻辑函数
- 1、逻辑值
- 2、if函数
- 3、逻辑函数
- 4、信息和逻辑判别函数
- 5、转换数值函数和返回#N/A函数
- 三、基础数学和统计函数
- 1、基础运算
- 2、条件运算
- 3、扩展
- 4、条件标准的写法——通配符
- 四、时间函数
- 1、年月日的获取和转化&时分秒的获取和转化
- 2、获取当前日期函数
- 3、日期间隔函数
- 4、获取指定日期
- 5、工作日期和间隔
- 6、日期的序数
- 五、数学函数
- 1、运算函数
- 2、数值截取和位数保留
- 3、随机函数
- 4、数组函数
- 5、转换函数
- 六、字符串函数
- 1、字符串截取函数
- 2、字符串长度
- 3、查找字符位置
- 4、字符替换
- 5、代码数字转换
- 6、文本转换
- 7、连接函数
- 8、其他函数
- 9、text函数
- 10、自定义数值格式的应用
- 七、查找和引用函数
-
- 八、高级函数
一、运算符
1、数学运算符
算术运算符 | 含义 |
---|
+(加号) | 加法 |
–(减号) | 减法/负数 |
*(星号) | 乘法 |
/(正斜杠) | 除法 |
%(百分号) | 百分比 |
^(脱字号) | 乘方 |
2、比较运算符
名称 | 符号 |
---|
等于 | = |
大于 | > |
大于等于 | >= |
小于 | < |
小于等于 | <= |
不等于 | <> |
3、连接运算符
4、引用运算符
引用运算符 | 含义 |
---|
:(冒号) | 区域运算符,生成对两个引用之间的所有单元格的引用,包括这两个引用 |
,(逗号) | 联合运算符,将多个引用合并为一个引用 |
(空格) | 交叉运算符,生成对两个引用共同的单元格的引用 |
5、运算符优先级
6、绝对引用和相对引用
二、信息和逻辑函数
1、逻辑值
2、if函数
函数名称 | 功能介绍 | 语法格式 |
---|
if | 指定要执行的逻辑检测 | =IF(Logical,Value_if_true,Value_if_false) |
3、逻辑函数
函数名称 | 功能介绍 | 语法格式 |
---|
AND | 如果其所有参数均为 TRUE,则返回 TRUE | =AND(logical1,[logical2],…) |
OR | 如果任一参数为 TRUE,则返回 TRUE | =OR(logical1,[logical2],…) |
NOT | 对其参数的逻辑求反 | =NOT(logical) |
XOR | 返回所有参数的逻辑“异或”值 | =XOR(logical1,[logical2],…) |
4、信息和逻辑判别函数
用途 | 函数名称 | 功能介绍 | 语法格式 |
---|
判别错误值和处理错误值 | iserror | 识别任何错误值,返回 TRUE | =ISERROR(value) |
iserr | 识别除 #N/A 以外的任何错误值,返回 TRUE | =ISERR(value) |
isna | 识别 #N/A 错误值,返回 TRUE | =ISNA(value) |
iferror | 识别所有错值,并对错误值处理,非错误值保持原值 | =IFERROR(value,value_if_error) |
ifna | 识别#N/A错误值,并对错误值处理,非错误值保持原值 | =IFNA(value,value_if_error) |
判别数字和文本 | isnumber | 识别数字,返回 TRUE | =ISNUMBER(value) |
istext | 识别文本,返回 TRUE | =ISTEXT(value) |
判别奇偶 | isodd | 识别奇数,返回 TRUE | =ISODD(number) |
iseven | 识别偶数,返回 TRUE | =ISEVEN(number) |
判别引用和公式 | isref | 识别引用,返回 TRUE | =ISREF(value) |
isformula | 识别公式,返回 TRUE | =ISFORMULA(reference) |
判别空值和逻辑值 | isblank | 识别引用,返回 TRUE | =ISBLANK(value) |
islogical | 识别公式,返回 TRUE | =ISLOGICAL(value) |
5、转换数值函数和返回#N/A函数
函数名称 | 功能介绍 | 语法格式 |
---|
N | 返回转换为数字的值 | =N(value) |
NA | 返回错误值 #N/A | =NA() |
三、基础数学和统计函数
1、基础运算
函数名称 | 功能介绍 | 语法格式 |
---|
sum | 参数列表求和 | =SUM(number1,[number2],…) |
average | 参数列表求平均 | =AVERAGE(number1, [number2], …) |
count | 计算参数列表中数字的个数 | =COUNT(value1, [value2], …) |
max | 参数列表求最大值 | =MAX(number1, [number2], …) |
min | 参数列表求最小值 | =MIN(number1, [number2], …) |
2、条件运算
函数名称 | 功能介绍 | 语法格式 |
---|
sumif | 符合条件的单元格求和 | SUMIF(range, criteria, [sum_range]) SUMIF(条件区域, 条件标准, [求和区域]) |
sumifs | 同时符合多个条件的单元格求和 | SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …) SUMIFS(求和区域, 条件区域1, 条件标准1, [条件区域2, 条件标准2], …) |
averageif | 符合条件的单元格求算数平均值 | AVERAGEIF(range, criteria, [average_range]) |
averageifs | 符合多个条件的单元格求算数平均值 | AVERAGEIFS(average_range,criteria_range1, criteria1, [criteria_range2, criteria2], …) |
countif | 符合条件的单元格计数 | COUNTIF(range,criteria) COUNTIF(区域,条件标准) |
countifs | 符合多个条件的单元格计数 | COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…) COUNTIFS(条件区域1,条件标准1,[条件区域2,条件标准2],…) |
counta | 非空单元格计数 | COUNTA(value1, [value2], …) |
countblank | 空单元格计数 | COUNTBLANK(range) |
注意:当调整条件标准列的顺序时,公式或不会随之变动
3、扩展
函数名称 | 功能介绍 | 语法格式 |
---|
averagea | 可以对数值、文本、逻辑值求平均数而不报错 | AVERAGEA(value1, [value2], …) |
trimmean | 修剪两端极值后求平均数 | TRIMMEAN(array,percent) TRIMMEAN(求值区域,要被修剪掉数值数量的百分比) |
rank | 返回一个数在数组中排名, 相同的值排名相同 | RANK(number,ref,[order]) |
4、条件标准的写法——通配符
通配需求 | 写法 |
---|
开始是 | 张* |
结束是 | *大众 |
包含 | *汽* |
固定长度 | ??? |
四、时间函数
日期可存储为序列号,这里的序列号就是日期。
1、年月日的获取和转化&时分秒的获取和转化
函数名称 | 功能介绍 | 语法格式 |
---|
year | 将序列号转换为年/获取日期的年份 | YEAR(serial_number) |
month | 将序列号转换为月/获取日期的月份 | MONTH(serial_number) |
day | 将序列号转换为日/获取日期的日份 | DAY(serial_number) |
date | 返回特定日期的序列号/组合年月日形成日期 | DATE(year,month,day) |
datevalue | 将文本格式的日期转换为序列号/文本格式转日期格式 | DATEVALUE(date_text) |
函数名称 | 功能介绍 | 语法格式 |
---|
hour | 将序列号转换为小时/获取日期的小时 | HOUR(serial_number) |
minute | 将序列号转换为分钟/获取日期的分钟数 | MINUTE(serial_number) |
second | 将序列号转换为秒/获取日期的秒数部分 | SECOND(serial_number) |
time | 返回特定时间的序列号/组合时分秒形成时间 | TIME(hour,minute, second) |
timevalue | 将文本格式的时间转换为序列号/文本格式转时间格式 | TIMEVALUE(time_text) |
2、获取当前日期函数
函数名称 | 功能介绍 | 语法格式 |
---|
now | 获取当前日期和时间 | NOW() |
today | 获取当前日期 | TODAY() |
3、日期间隔函数
函数名称 | 功能介绍 | 语法格式 |
---|
datedif | 计算两个日期之间的间隔 | =DATEDIF(Start_date,End_date,mode) |
mode选项 | 含义 | 示意表达式 |
---|
“Y” | 起始日到终止日之间的整年数 | |
“M” | 起始日到终止日之间的整月数 | |
“D” | 起始日到终止日之间的整日数 | |
“YM” | 起始日到终止日在前一年对日之间的整月数 | MONTH of [ y/m - (y-1)/M] |
“YD” | 起始日到终止日在前一年对日之间的整日数 | DAY of [ y/m/d - (y-1)/M/D] |
“MD” | 起始日到终止日在上个月对日之间的整日数 | DAY of [y/m/d - y/(m-1)/D] |
4、获取指定日期
函数名称 | 功能介绍 | 语法格式 |
---|
edate | 某日期若干月份前或后的对日 | EDATE(start_date,months) |
eomonth | 返回某日期若干月前或后对日所在月的最后一日 | EOMONTH(start_date,months) |
5、工作日期和间隔
函数名称 | 功能介绍 | 语法格式 |
---|
workday | 某日期若干工作日前或后的日期 | WORKDAY(start_date,days,[holidays]) WORKDAY(开始日期,若干工作日,[另外要从工作日中刨除的日期集合]) |
workday-intl | 自定义工作日,某日期若干工作日前或后的日期 | WORKDAY.INTL(start_date,days,[weekend],[holidays]) WORKDAY.INTL(开始日期,若干工作日,周末模式编号或者逻辑值文本,[另外要从工作日中刨除的日期集合]) |
networkdays | 两个日期之间完整工作日数 | NETWORKDAYS(start_date,end_date,[holidays]) |
networkdays-intl | 自定义工作日,两个日期之间完整工作日数 | NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays]) |
6、日期的序数
函数名称 | 功能介绍 | 语法格式 |
---|
weekday | 某日是一周中的周几 | WEEKDAY(serial_number,[return_type]) WEEKDAY(日期,[周序类型]) |
weeknum | 某日处于该年第几周 | WEEKNUM(serial_number,[return_type]) |
五、数学函数
1、运算函数
函数名称 | 功能介绍 | 语法格式 |
---|
int | 数值取整 | INT(number) |
mod | 取一个数被除后结果的余数 | MOD(number,divisor) |
product | 所有参数相乘 | PRODUCT(number1,[number2],[number3]…) |
power | 幂函数 | POWER(number,power) |
sqrt | 开平方 | SQRT(number) |
abs | 绝对值 | ABS(number) |
2、数值截取和位数保留
函数名称 | 功能介绍 | 语法格式 |
---|
trunc | 指定小数位数截取数值 | TRUNC(number,[num_digits]) TRUNC(数值,[要保留的小数位数]) |
round | 四舍五入 | ROUND(number,num_digits) |
roundup | 向上进位 | ROUNDUP(number,num_digits) |
rounddowm | 向下舍位 | ROUNDDOWN(number,num_digits) |
ceiling | 基数s成倍增大,返回直到刚大于n的值 | CEILING(number,signficance) CEILING(参考值,基数值) |
floor | 基数s成倍增大,返回直到将要超过n的值 | FLOOR(number,signficance) |
3、随机函数
函数名称 | 功能介绍 | 语法格式 |
---|
rand | 随机生成一个[0,1)的小数 | RAND() |
randbetween | 生成指定区间内的整数 | RANDBETWEEN(bottom, top) |
4、数组函数
函数名称 | 功能介绍 | 语法格式 |
---|
sumproduct | 同维度同方向区域数据乘积和 | PRODUCT(array1,[array2]…) |
mmult | 维度相同方向互相垂直的两个区域的乘积和 | MMULT(array1,array2) |
munit | 生成指定维度的单位矩阵 | MUNIT(dimension) |
5、转换函数
函数名称 | 功能介绍 | 语法格式 |
---|
base | 进制转换 | BASE(Number, Radix, [Min_length]) BASE(要转化的数 ,进制, [最小长度]) |
sign | 返回数字-1=负数,0=0,1=正数 | SIGN(number) |
roman | 阿拉伯数字转换成罗马数字 | ROMAN(number,[form]) |
arabic | 罗马数字转换成阿拉伯数字 | ARABIC(text) |
六、字符串函数
1、字符串截取函数
函数名称 | 功能介绍 | 语法格式 |
---|
left | 从左提取指定字符数的字符串 | LEFT(text,[num_chars]) |
right | 从右提取指定字符数的字符串 | RIGHT(text,[num_chars]) |
mid | 从指定位置提取指定字符数的字符串 | MID(text,start_num,num_chars) |
2、字符串长度
函数名称 | 功能介绍 | 语法格式 |
---|
len | 字符串包含的字符个数 | LEN(text) |
lenb | 字符串包含的字节个数 | LENB(text) |
3、查找字符位置
函数名称 | 功能介绍 | 语法格式 |
---|
find | 返回一个字符在另一个字符出现的位置,区分大小写 | FIND(find_text,whinin_text,[start_num]) |
search | 返回一个字符在另一个字符出现的位置,忽略大小写,支持通配符 | SEARCH(find_text,whinin_text,[start_num]) |
4、字符替换
函数名称 | 功能介绍 | 语法格式 |
---|
replace | 指定位置指定长度替换替换 | REPLACE(old_text,start_num,num_chars,new_text) REPLACE(原文本,开始位置,字符数,要去替换的新字符串) |
substitute | 指定内容替换 | SUBSTITUTE(text,old_text,new_text,[instance_num]) SUBSTITUTE(文本,被替换的旧字符串,要去替换的新字符串,[第几个旧字符串]) |
5、代码数字转换
函数名称 | 功能介绍 | 语法格式 |
---|
char | 返回由代码数字指定的字符 | CHAR(number) |
code | 返回文本字符串中第一个字符的数字代码 | CODE(text) |
6、文本转换
函数名称 | 功能介绍 | 语法格式 |
---|
proper | 单个单词首字母大写 | PROPER(text) |
upper | 转大写 | UPPER(text) |
lower | 转小写 | LOWER(text) |
trim | 删除多余空字符,仅保留词间1个空格 | TRIM(text) |
exact | 比较两个字符串是否完全相等,区分大小写 | EXACT(text1,text2) |
rept | 字符串指定次数重复 | REPT(text,number_times) |
dollar | 数值转美元格式文本 | DOLLAR(number,[decimals]) DOLLAR(数值,[小数位数]) |
rmb | 数值赚人民币格式文本 | RMB(number,[decimals]) |
numbervalue | 文本转数字 | NUMBERVALUE(text,[decimal_separator],[group_separator]) NUMBERVALUE(文本,[小数分隔符],[位数分隔符]) |
7、连接函数
函数名称 | 功能介绍 | 语法格式 |
---|
concat | 将多个区域和/或字符串的文本组合起来,但不提供分隔符或 IgnoreEmpty 参数 | CONCAT(text1,…) |
concatenate | 将几个文本项合(不是区域)并为一个文本项 | CONCATENATE(text1,…) |
textjoin | 将多个区域和/或字符串的文本组合起来,并包括在要组合的各文本值之间指定的分隔符。如果分隔符是空的文本字符串,则此函数将有效连接这些区域。 | TEXTJOIN(dilimiter, ignore_empty, text1, [text2], …) TEXTJOIN(分隔符, 是否忽略空格, 区域或文本1, [区域或文本1], …) |
phonetic | 提取文本字符串中的拼音(汉字注音)字符,连接连续区域,不支持持数字、日期、时间、逻辑值、错误值等 | PHONETIC(reference) |
8、其他函数
函数名称 | 功能介绍 | 语法格式 |
---|
clean | 删除文本中非打印字符 | CLEAN(text) |
value | 将文本参数转换为数字 | VALUE(text) |
valuestring | 数字转中文大写 | NUMBERSTRING(value,type) |
9、text函数
函数名称 | 功能介绍 | 语法格式 |
---|
text | 设置数字格式并将其转换为文本 | TEXT(value,format_text) |
正;负;0;文本
10、自定义数值格式的应用
暂略
七、查找和引用函数
1、行和列
函数名称 | 功能介绍 | 语法格式 |
---|
column | 返回引用的列号 | COLUMN([reference]) |
row | 返回引用的行号 | ROW([reference]) |
2、查找函数
函数名称 | 功能介绍 | 语法格式 |
---|
vlookup | 在数组第一列中查找,然后在行之间移动以返回单元格的值 | VLOOKUP(look_value,table_array,col_index_num,[range_lookup]) VLOOKUP(要查找的值,查找区域,要返回的列数,是否精确匹配) |
hlookup | 在数组第一列中查找,然后在行之间移动以返回单元格的值 | HLOOKUP(look_value,table_array,col_index_num,[range_lookup]) |
lookup | 在单行或单列区域查找值,匹配比它略小或相等的一个值,返回对应结果区域的值 | LOOKUP(lookup_value,lookup_vector,[result_vector]) LOOKUP(要查找的值,查找向量,[结果向量]) |
查找一个区域中比较略小或相等的值 | LOOKUP(lookup_value,array) LOOKUP(要查找的值,区域) |
3、引用和索引
函数名称 | 功能介绍 | 语法格式 |
---|
index | 引用区域中指定行列索引坐标处的单元格 | INDEX(array,row_num,[column_num]) INDEX(区域,行数,[列数]) |
多个区域可选,引用区域中指定行列索引坐标处的单元格 | INDEX(reference,row_num,[column_num],[area_num]) INDEX(一个或多个引用区域,行数,[列数],[要返回值的区域编号]) |
match | 在序列数组中查找到值,并返回索引数 | MATCH(lookup_value, lookup_array, [match_type]) |
choose | 根据索引号在列表中选择值 | CHOOSE(index_num, value1, [value2], ...) |
indirect | 根据单元格地址指向的单元格,a1=TRUE then A1样式 else R1C1样式 | INDIRECT(ref_text, [a1]) |
offset | 返回原引用区域进行指定行列偏移后的单元格 | OFFSET(reference,rows,cols,[height],[width]) OFFSET(原引用区域,偏移行数,偏移列数,占行数,占列数) |
address | 指定行号和列号获取单元格地址 | ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text]) ADDRESS(行号,列号,[引用模式],[引用样式],[工作表名称]) |
4、其他
函数名称 | 功能介绍 | 语法格式 |
---|
transpose | 返回转置后的数组,使用数组输入方式键入 | TRANSPOSE(array) |
formulatext | 以字符串形式返回引用单元格的公式内容 | FORMULATEXT(reference) |
colums | 返回引用中涉及的列数 | COLUMNS(array) |
rows | 返回引用中涉及的列数 | ROWS(array) |
areas | 返回引用中涉及的区域个数 | AREAS(reference) |
getpivotdata | 返回数据透视表中存储的数据 | GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], …) |
hyperlink | 创建快捷方式或跳转,以打开存储在网络服务器、Intranet 或 Internet 上的文档 | hyperlink(link_location,[friendly_name]) |
八、高级函数
函数名称 | 功能介绍 | 语法格式 |
---|
small | 返回数据集中的第 k 个最小值 | SMALL(array,k) |
large | 返回数据集中第 k 个最大值 | LARGE(array,k) |
subtotal | 返回列表或数据库中的分类汇总 | SUBTOTAL(function_num,ref1,[ref2],…) SUBTOTAL(函数编号,引用) |
mmult | 返回两个数组的矩阵乘积 | mmult(数组1,数组2) |
frequency | 以垂直数组的形式返回频率分布 | FREQUENCY(data_array, bins_array) FREQUENCY(数值数组,分界数组) |
cell | 返回有关单元格格式、位置或内容的信息 | CELL(info_type, [reference]) cell(信息类型,引用) |
mode | 返回在数据集内出现次数最多的值(众数) | mode(数值1,数值2…) |
median | 返回给定数值集合的中值 | median(数值1,数值2…) |
发布评论