(职场1-100集)(职场连续剧)

一、没有的要创造
subtotal——万能聚合
语法：=subtotal(聚合类型，聚合区域)，聚合类型有11种，为1-11或者101-111之间的数字，返回一个数列的分类聚合，常用的有求和、计数、平均值、最大值、最小值、方差、标准差等，其中1-11表示求筛选结果区域内的聚合，101-111表示求可见区域内的聚合(针对隐藏行而言)；
案例：计算所有部门实发工资：

筛选招商部，对应计算该部门下的实发工资总额：

aggregate——subtotal升级版
语法：=aggregate(聚合类型，聚合条件，聚合区域)，聚合类型一共有19种，为1-19之间的数字，常用的有求和、计数、平均值、最大值、最小值、方差、标准差等等；聚合条件有7种，为1-7之间的数字，常用的有忽略空值、隐藏行、错误值及其嵌套的subtotal和aggregate函数等等；
案例：

sumifs——条件求和
语法：=sumifs(求和列，条件区域1，条件1，条件区域2，条件2......条件区域N，条件N)，返回一个数列中满足某些条件的和；
案例：计算行政部2022年8月份实发工资总额：

countifs——条件计数
语法：=countifs(计数区域1，条件1，计数区域2，条件2......计数区域N，条件N)，返回一个数列中满足某些条件的记录数；
案例：统计行政部在职人数：

二、已有的要加工
mid——顺序截取
语法：=mid(被截取的字符串，从第几位开始截取，截取长度)，返回被截取字符串中的一部分；
案例：获取员工出生日期：

left——从左往右截
语法：=left(被截取的字符串，截取长度)，返回被截取字符串中的一部分；
案例：获取员工姓氏：

right——从右往左截
语法：=right(被截取的字符串，截取长度)，返回被截取字符串中的一部分；
案例：获取联系方式后面四位尾号：

三、相关的要匹配
vlookup——新手必备
语法：=vlookup(查找的值，查找的范围，返回的值在所选范围内的第几列，精确匹配/模糊匹配)；
案例：获取员工的实发工资：

index——返回单元格内容
语法：=index(数组或者区域范围，行编号，列编号[可省略])，返回指定区域内第M行，第N列对应单元格内的值，类似于通过经纬坐标定位；
案例：返回区域内第8行第4列对应单元格的值：

match——查找特定字符
语法：=match(查找的值，查找的范围，精确匹配/模糊匹配)，返回指定数据在范围内的位置；
案例：获取员工姓名在第几列第几列：

四、逻辑函数
if——条件判断
语法：=if(逻辑值，真，假)，返回判断条件为真或者假对应的结果；
案例：判断工资是否高于6000元：

and——与
语法：=and(表达式1，表达式2......表达式N)，返回多个逻辑值的真或假，同为真则真，遇假则假；
案例：判断招商部的员工工资是否超过6000元：

or——或
语法：=or(表达式1，表达式2......表达式N)，返回多个逻辑值的真或假，同为假则假，遇真则真；
案例：判断部门为行政部，或者工资超过6000元：

not——非
语法：=not(表达式)，返回逻辑表达式的相反数；
案例：返回部门不属于行政部的员工：

五、数字运算
abs——绝对值
语法：=abs(数字N)，返回数字N的绝对值；
案例：abs(-100)=100
int——取整
语法：=int(数字N)，返回数字N的整数位
案例：int(5.8)=5，int(-5.8)=-6
round——四舍五入
语法：=round(数字N，小数位数M)，返回数字N保留M位小数的值
案例：round(5.49，1)=5.5
roundup——向上舍入（远离0）
语法：=round(数字N，位数M)，当M=0表示取整
案例：roundup(5.8，0)=6，roundup(-5.8，0)=-6
rounddown——向下舍入（靠近0）
语法：=round(数字N，位数M)，当M=0表示取整
案例：rounddown(5.8，0)=5，rounddown(-5.8，0)=-5

六、进阶函数
text——格式化
语法：=text(格式化对象，单元格格式)，返回按照指定格式处理过后的对象；
案例：出生日期按照XXXX年XX月XX日格式显示

substitute——替换
语法：=substitute(原数据，需要替换的字符，替换后的字符)，返回把原数据中某部分替换成新字符之后的数据；
案例：隐藏电话号码中间四位

offset——位移函数
语法：=offset(原坐标，上下位移行数，左右位移列数，[结果区域的高度]，[结果区域的宽度])，返回在原坐标的基础上，位移之后单元格对应的值或区域；
案例：原坐标定在单元格E1，查找孔秀芳在第N行，然后向下移动第N-1行，得到孔秀芳的联系方式，效果等同于vlookup函数；

switch——多重if判断
语法：=switch(表达式，值1，结果1，值2，结果2......值N，结果N，默认值)，N最大为126；
案例：通过出生年份计算生肖，步骤解析：1.获取出生年份，2.用年份除以12取余数，3.通过余数计算生肖

iferror——屏蔽错误值
语法：=iferror(默认计算公式，计算出错的替代值)；
常见的Excel错误类型有以下几种#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!
案例：通过销售金额和销售数量计算单价，除数不能为0；

sumproduct——区域内聚合函数
1.返回相应的数组区域乘积的和
语法：=sumproduct(数组1，数组2……数组N）
注意事项：
1、如果SUMPRODUCT函数具有多个参数数组，这些数组之间必需具有相同的维数，否则SUMPRODUCT将返回#VALUE!错误值REF！
2、函数Sumproduct将非数据类型的元素作为0处理

2.单/多条件求和
语法：=sumproduct((条件区域1=值1)*(条件区域N=值N)*(求和区域))
使用场景：计算1-6月金鹰半山花园的销售收入总额，求和区域为1-6月的金额=G4:L30，条件区域1=E4:E30，值1=销售收入，条件区域2=F4:F30，值2=金鹰半山花园；完整的公式=sumproduct((E4:E30="销售收入")*(F4:F30="金鹰半山花园")*(G4:L30))