导图社区 《21天Excel新手到高手》函数篇:函数公式入门到进阶
这是一篇关于《21天Excel新手到高手》函数篇的思维导图,《21天Excel新手到高手》函数篇:函数公式入门到进阶,提升你的excel处理能力。
编辑于2022-07-22 17:10:2521天Excel新手到高手 函数篇:函数公式入门到进阶
1.掌握函数基本语法,学会和Excel高效对话
了解公式和函数概念
公式:以=为引导,通过将运算符,函数,参数等按照一定的顺序组合,进行数据运算的处理方式
函数:excel内部预先定义,并按照特定顺序和结构执行运算任务的功能模板,是一个个分装好,用来简化操作的公式
初次体验函数公式
选择需要求和的区域,点击公式-函数库-自动求和(也可在单元格输入=SUM()进行求和)
函数公式注意事项
1.无论是手动输入,还是通过菜单栏的函数库插入的函数,均是以等号开始,以括号结束
2.函数括号内的内容称为函数的参数,参数之间用英文逗号分隔(同时用方括号显示的参数为非必须参数,可以不填)
3.函数的输入字母不分大小写,输入完成后excel会自动的将小写的字母转化为大写的字母
4.函数中的所有符号都是英文状态下的(即英文半角状态)
单元格引用类型
相对引用
从属单元格和引用单元格的相对位置,也就是行列的位置,保持绝对不变
即:相对引用的单元格地址会变
例如:E4*F1
绝对引用
不管公式引用到哪,所引用的单元格地址绝对不会发生改变
例如:E4*$F$1(所用F1即不会发生改变)(输入方式,选择对应数据,按F4(或FN+F4))
2.使用SUM函数完成各种数据求和
一键快速求和
选择表格区域和需要放置求和值的区域,然后使用快捷键ALT+=即可
注意:每个单元格选取一次,不能重合选择,否则无法识别并求和(因为一件求和就是智能填写公式)
多数据批量求和
选择表格区域和需要放置求和值的区域,按下Ctrl+G键,调出定位面板,定位空值,接着按求和快捷键,即可实现
多表快速求和
在需要填写的单元格输入公式,用shift键选中需要汇总的表格,接着点击需要求和的单元格,完成公式即可(此操作是相对引用,可进行填充)
数据累加求和
选择需要填写的单元格,输入公式(注意:累计求和相当于固定初始位置值然后进行依次累加,如$B$2:B2),其余位置进行填充即可
3.利用函数公式,轻松搞定比赛计分和排名
RANK(NUMBER(需要排名的数值),REF(排名的数值区域),[ORDER](排名的方式))
公式的结果为当前需要排名的数值在数值区域中的排名,排名的方式可按升序或倒序排名,当第三参数省略或为零时,表示数值按降序排名,数值越大排名越靠前,当第三参数不省略或数值不为零时,表示数值按升序排名
例子:RANK(O3,$O$3:$O$25,0)(故可进行填充操作)
优点:当出现数据变动时,不需要进行操作
4.核对数据必备:VLOOKUP函数
函数作用:查找与引用,在数组第一列中查找,然后在行之内移动返回单元格的值
VLOOKUP基本语法规则
VLOOKUP(lookup_value(要查什么),table_array(在哪查),col_index_num(查到了要哪部分),range_lookup(精确查还是模糊查))
要保证查找值在查找区域的首列
VLOOKUP的精确查找
例如:VLOOKUP(C3,$J$2:$k$13,2,FALSE)(因为固定了查找范围,所以可进行填充)
应用范围:统计产品销售量时按单价表填入对应单价
VLOOKUP的近似查找
例如:VLOOKUP(F3,$M$2:$N$8,2,TURE)
应用例子:通过各级数据范围进行评级操作
5.VLOOKUP数据验证制作交互式图表
做一个辅助数据,复制原始表头,然后粘贴到下方空白位置,接着利用数据验证功能,制作产品的下拉选项(点击单元格-数据-数据验证-选择序列-来源选择复制的表头单元格区域)
接下来输入VLOOKUP公式,根据产品名称批量查询所需数据, 例如:VLLOKUP($B$15,$B$2:$N$12,COLUMN(B1),false)
COLUMN函数作用:获取单元格的列号(即返回单元格所在的列标对应的数字A-1,B-2等)(即COLUMN(B1)中实际有用只有B,返回值为2)
选择做好的辅助数据,点击菜单栏-插入-选择需要的图形,调整图形的位置,使其符合要求即可
6.随机抽奖这样玩:Rand函数
将人员名单放置在A列,然后在B列使用RAND函数生成随机数,作为后面数据排序的依据。
具体操作:
1.在单元格输入RAND(),按下回车键生成一个随机数,选中该单元格进行填充(这个函数不需要任何参数,作用是返回大于等于0及小于1的均匀分布随机实数,并且RAND在每次计算时都将返回一个新的随机实数,因此可以按F9对数据进行刷新操作)
2.接着在人员列插入一列数据,利用RAND函数进行排名操作,作为辅助序列(RAND的作用是求某个数值在某一区域内的排名,在此我们使用的是刚刚生成的随机数的排名信息。)输入RANK函数,第一个参数为排名的数字,第二个参数为参与排名的区域,第三个区域为排列方式(例如RANK(C2,C;C,0))
3.选择单元格制定好中奖人员的标题后,设置中奖人数序号,使用VLOOKUP函数将姓名提取出来,第一个参数为填充序列信息,第二个参数为左侧的排名和姓名列数据,因为需要返回姓名,第三个参数为2,第四个参数为精确匹配。最后进行填充即可
4.此时已制作完成,抽奖时一直按F9,停止的一刻即是中奖的人员
7.让Excel自动判断:IF函数
IF函数基本概念
IF函数是逻辑函数,判断是否满足某个条件,如果满足,返回一个值,如果不满足,返回另外一个值
IF(logical_test(条件判断),value_if_true(结果为真返回值),value_if_false(结果为假返回值))
IF函数单条件判断
例如:IF(A2>=60,"及格","不及格")
两个IF函数的嵌套
IF函数最多可以嵌套64层
例如:IF(A2>=60,IF(A2>=85,"优秀","及格"),"不及格")
贴士:在应用多个IF函数时,尽量使用相同的比较方式,结果也按升序或降序排列。是一个好习惯
if函数不能出现200<B2<500这样的形式,否则无法进行判断并填入数据
IF函数综合使用
防止填充时空单元格也进行的操作:增加IF函数判断其是否为空 (例如IF(B2<>"",IF(B2="本地",300,800),"")),此操作的优点是可在后续进行数据添加时自动判断并加入数据
8.自动统计重复次数:Countif函数
有时要求输入的数据不能重复,例如员工工号,电话号码,身份证号码等,为避免输入出错,采用数据验证中自定义的方式让excel自动对重复数据进行核对检查。
COUNTIF函数概念
COUNTIF是一个统计函数,用于统计满足某个条件的单元格数量
COUNTIF(range(需要在哪些区域),criteria(查找哪些内容))
参数一range要计算其中符合条件单元格格数目的区域;参数二:criteria以数字表达式或文本形式定义的条件
例如:COUNTIF(B:B,E2)
如何同时设置多个限定条件?
使用判断函数
AND函数是满足所有条件返回为true,否则返回为false
OR函数是至少满足其中一个条件即返回true,否则false
LEN函数是返回字符串的位数,可以计算文本的长度,数字的位数
例如:请在限定录入的基础上,新增加一个条件电话号码必须是11位。=AND(COUNTIF(B:B,B2)<=1,LEN(B2)=11)
使用
选择需要重复验证的所有单元格,单击数据-数据工具-数据验证-数据验证....(或数据有效性)-在设置中允许下拉列表选择“自定义”选项,同时选择忽略空值-在公式框中输入COUNTIF(C:C,C2)=1(C:C表示统计单元格的区域,可根据需要进行调整)
可以为了让提示更加人性化,修改刚刚做好的数据验证切换到出错警告选项卡中进行修改