excel常用函数及快捷键

锁定快捷键：Fn+F4

选择空单元格:

Ctrl + A 全选

ctrl + G 定位

选择"空值"

Ctrl + Z 后退

选中错误单元格:

Ctrl + shift + L 快速筛选

自定义数据格式:

Ctrl + 1 设置单元格格式

选择自定义

@表示格中的值

#表示调用数字

Ctrl + F 查找加替换

DATEDIF 函数，求日期差

数学函数：

INT(number) 函数向下取整，比如-3.4取整是-4

MOD(number, divisor) 求余数

ROUND(number, num_digits) 四舍五入

ROUNDUP(number, num_digits) 向上四舍五入

ROUNDDOWN(number, num_digits) 向下四舍五入

ABS(number) 取绝对值

SQRT(number) 算术平方根

RAND() 产生随机数，从0到1之间的浮点型，括号里无参数

RANDBETWEEN(bottom, top) 生成指定区间的随机整数，是闭区间，包含bottom和top

文本函数：

UPPER(text) 都变成大写

LOWER(text) 都变成小写

CONCAT(text1, [text2], [text3],...) 连接列表或文本字符串区域

MID(text, start_num, num_chars) 取子串

LEFT(text, [num_chars]) 从左取子串

RIGHT(text, [num_chars]) 从右取子串

LEN(text) 文本长度

TEXT(value, format_text) 数字转化文本格式，"aaaa"是星期

REPT(text, number_times) 文本重复

REPLACE(old_text, start_num, num_chars, new_text) 替换特定位置处的文本

SUBSTITUTE(text, old_text, new_text, [instance_num]) 替换特定符号的文本

replace 和 substitute函数的区别是replace按照位置来替换，而substitute根据文本来替换，如果出现重复文本，substitute会替换所有重复文本。

统计函数(若条件使用单元格不能直接引用，必须使用&符号相连接)：

MAX(number1,[number2],…) 取最大值

MIN(number1,[number2],…) 取最小值

SUM(number1,[number2],...) 求和

COUNT(value1,[value2],...) 数值计数，不是数值不计数

COUNTA(value1,[value2],...) 对记录进行计数，空值不计数

AVERAGE(number1,[number2],…) 求平均，会自动忽略文本求和数值

COUNTIF(range,criteria) 条件计数

SUMIF(range,criteria,[sum_range]) 条件求和

AVERAGEIF(range,criteria,[average_range]) 条件平均

COUNTIFS(criteria_range1,criteria1,…) 多条件计数

SUMIFS(sum_range,criteria_range1,criteria1,...) 多条件求和

AVERAGEIFS(average_range,criteria_range1,criteria1,...) 多条件求平均

FREQUENCY(data_array,bins_array) 求数据分布频率，会返还bins_array里的n+1个值

RANK(number,ref,[order]) 排名次

LARGE(array,k) 返回第k大的值

SUBTOTAL(function_num, ref1, [ref2],...) 用于筛选之后的操作

逻辑函数：

IF(logical_test,[value_if_true],[value_if_false]) 判断

IFS(logical_test1, value_if_true1, ...) 多条件判断

IFERROR(value, value_if_error) 如果表达式是一个错误，则返回value_if_error

AND(logical1,[logical2],...) 有一个为假返回假

OR(logical1,[logical2],...) 有一个为真则返回真

NOT(logical) 非

日期函数：

YEAR(serial_number) 求年

MONTH(serial_number) 求月

DAY(serial_number) 求日

HOUR() 求时

MINUTE() 求分

SECOND() 求秒

WEEKDAY() 代表一周中的第几天

WEEKNUM() 代表一年中的第几周

TODAY() 当前日期，括号里没有参数

DATE(year,month,day) 计算给定的日期

NOW() 当前日期和时间（电脑系统的时间），括号里没有参数

EDATE(start_date,months) 指定日期前后月份的日期

EOMONTH(start_date,months) 某个月份最后一天的日期

DATEDIF(start_date,end_date,“d”) 计算日期差

Y	相差年数
M	相差总月数
YM	一年内相差月数
D	相差总天数
YD	一年内相差天数
MD	一月内相差天数

查找与引用函数：

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 垂直方向查找

OFFSET(reference, rows, cols, [height],[width]) 计算偏移量, ctrl+shift+enter

MATCH(lookup_value, lookup_array, [match_type]) 查找位置

INDEX(reference, row_num, [col_num], [area_num]) 查找数据

INDIRECT(ref_text, [a1]) 文本字符串指定的引用:

参数不加引号，直接引用单元格数据，公式求值看过程；

参数加引号，参数为文本，直接引用文本所代表的单元格。等同于=A2

ROW([reference]) 引用行的数据

COLUMN([reference]) 引用列的数据

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) 水平方向查找

CHOOSE(index_num, value1, [value2],...) 索引值指定参数列表中的数值

FIND(find_text, within_text, [start_num]) 一个字符串在另一个字符串的起始位置