3-1掌握函数基本语法,学会和Exce高效对话
在Excel中公式是以等号开始的数据运算方式,而函数是对一些算法的集成模块。
可以使用菜单栏直接调取函数,也可以直接手动输入函数再选择对应的参数计算。
每个函数都是以等号开始,且书写中不分大小写,每个参数间以英文逗号隔开。
在函数公式中,分为相对引用,绝对引用和混合引用三种形式,可以使用F4切换。
3-2使用SUM函数完成各种数据求和
选中表格区域和需要放置求和值的区域,然后按下键盘上的【Alt+=】即可快速求和。
使用定位条件选择需要求和区域,然后配合【Alt+=】快速求和实现批量求和操作。
使用SUM函数配合批量选择单元格区域,既可实现多表数据的快速批量求和操作。
改变SUM中参数的引用方式,将起始区域锁定,可以实现同类数据的累加求和效果。
3-3利用函数公式,轻松搞定比赛计分和排名
=RANK(需要排名的数值,排名的数值区域,排名的方式)
当第3参数省略或为0时,表示数值按降序排名,反之则表示数值按升序排名
在使用RANK函数排名中,一般第二参数需要进行绝对引用操作
3-4核对数据必备:VLOOKUP函数
查找与引用,在数组第一列中查找,然后在行之内移动以返回单元格的值。
=VLOOKUP(要查什么,在哪查,查到了要哪部分,完全一样还是差不多就行?)
当第4参数为FALSE或0的时候为精确匹配,绝大多数情况下都为此类匹配方式。
当第4参数为TRUE或1的时候为近似匹配,一般应用于区间值的匹配查询
3-5Vlookup数据验证制作交互式图表
借助数据验证构建下拉菜单,实现搭建单元格数据与图表数据动态关联的桥梁
复制原始表头,利用VLOOKUP函数的查找功能,构建制作图表的辅助数据数据
锁定第一参数,同时借助COLUMN函数生成第三参数实现数据的批量查询操作
利用辅助序列制作图表,并调整图表样式和数据标签,同时利用数据验证实现切换
3-6随机抽奖这样玩:Rand函数
在姓名旁边构建辅助序列,利用RAND函数生成不规律的随机数作为排序参考
在姓名的左侧构建辅助序列,利用RANK函数对刚刚生成的随机数进行排序操作
构建抽奖数据区域,使用VLOOKUP函数提取排名符合要求的数据作为中奖人员
抽奖时可以按下键盘上的F9键不停地刷新数据,当停止刷新时所显示的就是中奖人员
3-7让Exce自动判断:IF函数
判断是否满足某个条件,如果满足返回一个值,如果不满足返回另一个值。
单条件判断适用于比较简单的场景,只会输出满足条件和不满足条件两种结果。
通常一个IF有两种结果,如果需要多种结果,可以使用多个IF函数嵌套进行判断。
IF函数第一参数可以关联单元格,数据填写如果满足某个条件则自动触发满足结果。
3-8自动统计重复次数:Countif函数6:58|300次观
工作中电话号码、身份证号码等数据具有唯一识别性,日常工作中应注意不能重复。
用于统计满足某个条件的单元格数量,参数分别为:要在哪些区域,查找哪些内容。
使用数据验证中自定义项目加上COUNTIF的唯一值判断,实现不重复数据录入操作。
配合数据验证的提醒和警告功能,更好地防止填写者数据录入出错,起到预警作用。
3-9.掌握函数嵌套基本规则,创建动态序列
显示目标单元格的行号,参数为空时则显示当前单元格的行号。
用于制作附加条件的动态表格序号,增加表格的交互性和功能体验。
当一个函数的计算结果等于另一个函数的参数时,两个函数具备嵌套的条件。
3-10.使用Indirect函数+Vlookup函数,完成跨表查询主题
显示目标单元格中地址指向的结果。其参数可以是单元格也可以是地址文本
根据Indirect函数参数的不同,结果会产生差异
如果括号中的参数是单元格,则Indirect函数会先把单元格中所写内容视为一个地址,然后根据这个地址,找到对应的结果;如果括号中的内容是一个文本,则Indirect函数会直接将这个文本视为地址,找到对应的结果
用Indirect函数的计算结果替换Vlookup函数的第二个参数。
3-11.利用Indirect函数+条件格式,制作动态下拉列表
将验证条件设置为序列,手动输入下拉选项,用英文状态下的逗号隔开或者直接选择事先准备好的选项列表。
借助超级表格的范围扩展功能和表名称功能,结合INDIRECT函数公式,完成动态更新的下拉列表制作。
使用定义名称中的“根据所选内容创建”批量定义名称,结合INDIRECT函数制作二级下拉列表。
3-12.使用Match函数+Vlookup函数,实现乱序匹配
复盘lookup函数与Column函数的使用场景,梳理函数嵌套的设计思路和推导过程。
引入Match函数,学习Match函数的功能和使用方法,使用Match+Vlookup函数嵌套,对标题顺序不一致的表格进行快速匹配。
3-13.使用Index函数+Match函数,进行交叉查询
在给定的单元格区域中,返回特定行列交叉处单元格的值或引用。
INDEX(array,row_num,[column_num])
返回符合特定值特定顺序的项在数组中的相对位置。
MATCH(lookup_value,lookup_array,[match_type])
3-14.使用Mid函数+Text函数,从身份证号中提取生日
通过格式代码,向数字应用格式,更改数字的显示方式。格式代码中用“0”表示数字占位符。
将文本进行四则运算,比如负负运算,即可将文本转换为数值。