记录原由
喜欢记笔记的一个原因是，随着后续工作变更，工作重心转移，原来技能可能因应用少而熟练度下降，记笔记方便查找；另一方面也可以拿出来分享，方便自己的同时也能给其他人多少带来点有用的，事情本身的价值也就得到了延伸
这里记录下excel常用的函数，以及部分应用案例，示例中函数理论在2016（除个别2010版本应该可以应用）及以后版本中应用没问题，如有特殊在文中做特殊说明；
软件版本一直在更新，原本可能需要写很复杂的逻辑（多重嵌套），后续可能一个函数就能搞定，比如去重函数unique，这块大家可以关注下后续版本更新。
函数简要描述
什么是函数
在excel日常应用中，函数主要用来处理单元格的值：即给我一个值，然后按照一定的处理规则返回处理后的结果（有给有返回）。
函数输入的单元格通常是一个或多个（数组），输出同样的可以是一个或者多个（offset函数可以返回多个值[返回数组]）；当然函数也是可以返回非值或数组其他对象；
函数在单元格值以=号开始标记，比如：=sum(10,20)返回30；函数在单元格中生效的一个前提是，该单元格的格式不是文本格式。（文本格式意味着该单元格是用来记录字符串的）
在编辑单元格的值时，如果想生命这个单元格是文本类型，也可以在单元格输入最前面加上一撇（英文单引号），比如下图记录身份证号，如果直接输入身份证号，由于excel对数值保留长度有限制，后面长度全都变成了0；这种情况需要以文本格式记录；
一个特别说明是，excel中的ctrl + h替换功能，对于函数也适用。比如我一个区域内的函数有个单元格引用写错了，想把该区域内函数的引用G1单元格替换成H1，可以直接选中区域替换。（替换时需要考虑会不存在其他不需要被替换的部分也被替换了）
下面的案例，把函数内的10替换成50（单元格也可以替换）：
excel中不同处理规则的函数有不同的命名，比如sum求和（规则：把输入的数加总求和），average求平均数；
就笔者日常使用来说，wps表格和微软office函数基本互为适用。相比微软excel的函数，wps函数在函数提示表现更为友好（于国内大多数人）；
excel中函数的参数提示是英文的（下图：number1,…），而wps表格函数参数提示是中文的；
当然对于函数具体参数使用不明确的，也可以按在excel中安按F1键查看帮助（当然百度也是可以）。
函数来源
表格中函数应用来源，笔者知道的几处：
- 表格内置自带函数：比如：
sumifs多条件求和。 - 智能填充：如果你想对某列数据按一定的规则处理，但又苦于找不到合适的函数，或者函数写起来很麻烦；可以考虑手动输入几个值后，按
ctrl + e组合键智能填充，如果填充得并不理想，也可以手动修改，函数会跟着修改尝试调整逻辑以匹配你期望的逻辑。
- 自定义函数：有些函数excel本身没有，可以通过VBA编写自定义函数，再以插件形式加载即可；这样打开任何一个工作簿都可以使用自定义函数，比如文本分割函数，正则函数等；
只需在插件写一段简单的代码即可
Function text_split(str As String, sep As String, index As Long)
' 参数：str:被分割的字符串,sep:分隔符,index:分割后返回数组该索引的值，如果小于0返回数组
' 样例：text_split("abc,de,fg",",")(1) 返回de
If index >= 0 Then
text_split = Split(str, sep)(index)
Else
text_split = Split(str, sep)
End If
End Function
- 数组函数的支持
为一些原本不支持数组运算的函数添加了数组支持，使用方法是在函数输入后按住ctrl+shift不动，再按enter回车。俗称三键；
比如sumif函数，也可以拆成sum+if两个函数嵌套，比如：=SUM(IF(A1:A8="张三",B1:B8,0))输入后按三键确认，筛选a列张三再对符合条件的b列求和；
因为if函数本身的条件判断只支持单个值判断，这里使用了数组运算，IF(A1:A8="张三",B1:B8,0)函数最后的返回是数组；
函数自动计算
在excel中有函数自动计算选项，该设置一般作用于表格函数很多，每次变更触发重算表格卡顿。这种情况可以先设置为“手动计算”，等修改完后再修改为“自动计算”。当工作簿保存时也会触发工作簿的计算。
要留意sumproduct，lookuop多条件匹配（尤其是这个），vlookup等函数，在实践中，发现如果工作簿有很多这类函数，可能会比较卡；
该自动计算选项卡设置在“公式”选项卡的“计算”组的“计算选项”菜单按钮
有时候写了公式，函数不计算，也可以看看是否自动计算选项是“手动”
如果写vba代码，如果工作簿存在很多计算函数，也应该在程序运行最前面关闭自动计算，在逻辑处理完后再开启自动计算；
当开启自动计算后，触发函数重算的情况主要为以下两种：
1. 公式所依赖的单元格发生更改时：Excel 默认只有在公式所依赖的单元格发生更改时才会自动重新计算公式。
2. 打开工作簿及任何单元格变更：含有易失函数的单元格公式在打开工作簿时会自动重算，比如：randbetween生成随机数，now当前时间函数等；
如果是手动计算，想要刷新可以点击选项卡里的“开始选项”或者“计算工作表”或者按快捷键F9触发重算，也可以按ctrl+s保存时会触发计算；
有时候可能会有删除工作表特定行的场景，一般同学会筛选出这些行，再选中删除这些行；如果被引用的多了，可能会很卡；如果没有特别情况（不需要跟着删除的行号减少，函数行号发生变更），此类情况可以考虑先把需要的先筛选复制出来，再全选删除原来的数据（没有改变行号），再把复制的数据粘贴回去。
函数引用单元格行列锁定
函数如果引用单元格没有锁定，函数单元格在拖拉拽时，引用单元格会随着函数拖拉的偏移位置而同等偏移。
有一种情况，即使在单元格锁定的前提下，函数引用也会变：单元格行列删除或插入时，即使是绝对锁定函数引用也会跟着引用；比如原本引用的$A$4单元格，在A4单元格左边插入一列，那么原本的引用就会变成$B$4。因为原本的单元格在插入一列后往右偏移了一位，引用也跟着往右偏移；
锁定分为以下几种情况，可以在引用单元格位置按F4键切换：
- 行锁定：
B$9，单元格中间一个$符号，函数单元格托拉拽时，锁定单元格的行号不会变，列跟着同等列偏移变 - 列锁定：
$A10，单元格左边一个$符号，函数单元格托拉拽时，锁定单元格的列号不会变，行号偏移同等行偏移量 - 行列锁定：
$A$1:$D$4,单元格前后各一个$符号，函数单元格托拉拽时，锁定单元格的行列都不会变
这个锁定的$符号也可以按shift+4手敲$
比如下面单元格想要匹配出王五和张三的身高体重，可以在B10单元格写入函数：=VLOOKUP($A10,$A$1:$D$4,MATCH(B$9,$A$1:$D$1,0),0)
把匹配函数vlookup的范围锁定在$A$1:$D$4，因为查找的范围是固定的；查找的“身高”锁定行号B$9，因为往下拉时，还是用这一行（第九行），超找的姓名锁定列号$A10，往右拖拉时，还是用这一列。
编辑完B10单元格的函数后，选中B10单元格，ctrl+c复制，按住shift键不动，再鼠标左击C11单元格，选中函数要匹配填充得区域，再按ctrl+v粘贴即可；效果如下：
函数调试
如果涉及函数嵌套函数的情况，函数输出报错或者不是我们期望的结果，可能是某个子函数输出异常，这总情况可以在函数内选中子函数部分，然后按F9快捷键计算子函数的返回，由内而外定位看是哪个函数输出异常，再进一步排查具体函数问题。
按F9后，选中子函数部分就成为了确定的值，这时候可以通过ctrl+z撤销来返回原本的函数，或者有其他函数是通过该函数拖来拽形成的，再拽回来恢复即可。
名称管理器与函数引用追踪
函数引用除了单元格位置，也可以用定义好的名称管理器。新建名称的路径如下图所示（透视表和图表的数据源也可以使用定义好的名称）：
我们点击新建一个：将Sheet1!$D$1:$D$2命名为自定义单元格名称1。
然后我们尝试输入函数引用该名称：=sum(自定义单元格名称1)
效果如下图所示：
名称管理器可以通过offset+counta函数动态的框定数据范围，比如：=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))
另一方面，如果我们想看函数引用了哪些单元格（追踪引用单元格）或者这个单元格被哪些函数引用了（追踪从属单元格），可以点击功能区‘公式’ -> '公式审核’组的追踪单元格来定位引用或者从属单元格
假如一个表格有多个游戏的数据，我们想动态查看基于特定游戏的图表，可以先试用’filter’函数框定特定游戏的数据范围，filter函数筛选条件再引用一个特定单元格，这个单元格设置数据验证-序列，填写选项内容；最后，图表再引用该自定义名称生成，这样图表会随着数据验证单元格变动而动态变化：即当我选择王者荣耀即展示王者荣耀的数据，选择和平精英即展示和平精英的数据。
数据验证设置路径如下：
函数中如何表示数组
就函数参数，数组除了可以选择对应范围的单元格表示，也可以手动编写数组，使用大括号表示，分好;表示换行。比如:{"a","b";"c","d"}
如果我想匹配游戏的品类，使用vlookup函数，如果游戏种类有限，我们也可以这样写：
=VLOOKUP(A15,{"和平精英","射击";"王者荣耀","MOBA"},2,0)
常用函数介绍
日期函数
| 函数名称 | 函数解释 |
|---|---|
| today | 返回当前日期date |
| now | 返回当前时间datetime |
| year | 返回时间的年份 |
| month | 返回时间的月份 |
| day | 返回时间的天 |
| hour | 小时 |
| minute | 分钟 |
| weekday | 返回星期几，第二个参数2代表星期一为一周的一地天 |
| weeknum | 返回一年的周数，第二个参数选择星期几为一周的第一天 |
| eomonth | 返回月份最后一天，参数1：日期；参数2：整数，0表示当月最后一天，也可以使用负数，-1表示上个月最后一天 |
| edate | 返回偏移第二个参数月份的日期，第二个参数可为负数，-1表示上个月的这一天 |
| date | 传入年，月，日参数返回日期date |
| datedif | 返回开始日期和结束日期中的日期差。常用第三个参数：'d’天数差；'md’忽略年月的天数差;'m’月份差;'ym’忽略年份的月份差 |
这里记录了常用日期表达：
| 函数 | 解释 | 示例 | 备注 | |
|---|---|---|---|---|
| today | 今天：date | 2024/10/4 | c2单元格 | TODAY() |
| now | 现在：datetime | 2024/10/4 20:58 | NOW() | |
| year | 年 | 2024 | YEAR(C2) | |
| month | 月 | 10 | MONTH(C2) | |
| day | 日 | 4 | DAY(C2) | |
| hour | 时 | 20 | HOUR(C3) | |
| minute | 分 | 58 | MINUTE(C3) | |
| weekday | 星期几 | 5 | WEEKDAY(C2,2) | |
| weeknum | 一年第几周 | 40 | WEEKNUM(C2,2) | |
| eomonth | 当月第一天 | 2024/10/1 | eomonth返回当月最后一天 | EOMONTH(C2,-1)+1 |
| 当月最后一天 | 2024/10/31 | 第二个参数为0返回本月最后一天。 | EOMONTH(C3,0) | |
| 上个月第一天 | 2024/9/1 | EOMONTH(C2,-2)+1 | ||
| 上个月最后一天 | 2024/9/30 | EOMONTH(C3,-1) | ||
| date | 日期 | 2024/10/4 | 如果第三个参数为0，表示月份参数上个月最后一天 | DATE(C4,C5,C6) |
| 季度初始日期 | 2024/10/1 | roundup进一 | DATE(YEAR(C2),(ROUNDUP(MONTH(C2)/3,0)-1)*3+1,1) | |
| edate | 环比:上月这一天 | 2024/9/4 | 第二个参数：整数。可以为正、负、或者0。 | EDATE(C2,-1) |
| 返回本周一日期 | 2024/9/30 | C2-WEEKDAY(C2,2)+1 | ||
| 上周周一 | 2024/9/23 | C2-WEEKDAY(C2,2)-6 | ||
| 同比：去年这一天 | 2023/10/4 | DATE(YEAR(C2)-1,MONTH(C2),DAY(C2)) | ||
| 年初始日期 | 2024/1/1 | DATE(YEAR(C2),1,1) | ||
| datedif | 日期差 | 27 | 参数依次为：开始日期，结束日期，差值计算标准 | DATEDIF(C2,C12,“d”) |
| networkdays | 区间日期工作日 | 23 | NETWORKDAYS(C11,C12) |
计算函数
| 函数名称 | 函数解释 |
|---|---|
| sum | 求和 |
| sumif | 条件求和，支持通配符 |
| sumifs | 多条件求和，条件求和用这个就可以了 |
| maxifs | 多条件求最大值，2019及后版本支持 |
| minifs | 多条件求最小值，2019及后版本支持 |
| count | 数值计数 |
| counta | 非空单元格计数 |
| countblank | 空白单元格计数 |
| countif | 条件计数，支持通配符 |
| countifs | 多条件计数 |
| average | 平均数 |
| averageif | 符合条件平均数，忽略文本，支持通配符 |
| averageifs | 多条件求平均数 |
| sumproduct | 乘积和，也可以实现多条件求和，去重计数等，参数支持数组，尽量少用，卡 |
| min | 最小值 |
| max | 最大值 |
| large | 取出排名第几大的数 |
| small | 取出排名第几小的数 |
| rank | 排名，有重复排名，两个取最小排名，不想重复排名可结合countif使用。 |
| rand | 返回0-1随机数，配合其他灵活使用返回其他随机数 |
| randbetween | 返回指定区间随机整数 |
| int | 浮点转化为整数，舍去小数点 |
| round | 根据保留位数，四舍五入，参数2可以为负数 |
| roundup | 根据保留位数进一 |
| rounddown | 保留指定小数位数，舍去尾巴 |
| value | 将符合数值格式的文本转化为数值 |
| mod | 取余数 |
| median | 取中位数 |
| stdev | 返回标准差 |
字符串函数
| 函数名称 | 函数解释 |
|---|---|
| left | 从左往右截取指定长度字符串；leftb表示截取固定长度字节 |
| right | 从右往左截取指定长度字符串；rightb表示截取固定长度字节 |
| mid | 从中间截取指定长度字符串；mid表示截取固定长度字节 |
| len | 返回字符串长度 |
| lenb | 返回字节长度 |
| replace | 指定位置替换固定长度 |
| substitute | 替换指定字符串 |
| text | 数值文本化，具体参考单元格自定义格式 |
| trim | 去除字符串两边空格 |
| find | 查找查找值在字符串出现的位置（字符位置）区分大小写 |
| search | 查找查找值在字符串出现的位置（字符位置）不区分大小写，支持通配符 |
| findb | 查找查找值在字符串出现的位置（字节位置），区分大小写 |
| searchb | 查找查找值在字符串出现的位置（字符位置）不区分大小写，支持通配符 |
| textjoin | 根据分隔符合并字符串 |
| concatenate | 字符串合并 |
text格式化函数使用相对较多，这里列下常用格式说明：
关联匹配类
| 函数名称 | 函数解释 |
|---|---|
| vlookup | 匹配，条件范围列要放在第一列。多条件逆向匹配可以结合if数组实现，支持通配符 |
| lookup | 多条件匹配,数值分层，返回区域最后一个数值。这个函数多条件匹配用多了时真卡 |
| index | 根据区域相对行号，列号，返回单元格的值 |
| match | 返回匹配值在区域的位置，支持通配符 |
| offset | 根据单元格的偏移返回单元格的值，可以用来框定数组范围 |
| exact | 比较两个不同文本是否一致，是返回True。可以用来对比数值和数值格式的文本 |
| row | 返回单元格行号 |
| column | 返回单元格列号 |
| address | 返回单元格地址 |
| indirect | 根据单元格字符串地址返回单元格的值 |
| choose | 根据参数一返回指定位置的参数 |
| xlookup | 匹配，新版匹配函数，相比vlookup无需限定匹配值在第一列，需要在2019后或者365版本适用 |
逻辑类
| 函数名称 | 函数解释 |
|---|---|
| if | 判断，可以多重if嵌套 |
| and | 两个都是True，才是True，其他False |
| or | 两个都是False才是False，不然是True |
| iserror | 如果出现错误，返回True |
| iferror | 如果出现错误返回参数值，使用频率高 |
| ifs | 不用再if嵌套if了，2019及后版本支持 |
2021版本新增函数
SEQUENCE（生成序列号）、SORT（排序）、UNIQUE（去重，有了这个就可以去重计数了：=counta(unique(a1:A10))）、FILTER（筛选）
函数详细使用可以查看帮助文档，函数参数没必要刻意去记，知道干什么大概用什么函数，能看明白函数参数提示就可以了，可以备一个函数手册随时查找。