《21天Excel新手到高手》函数篇：函数公式入门到进阶

这是一篇关于《21天Excel新手到高手》函数篇的思维导图，《21天Excel新手到高手》函数篇：函数公式入门到进阶，提升你的excel处理能力。

21天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表示统计单元格的区域，可根据需要进行调整）
    • 可以为了让提示更加人性化，修改刚刚做好的数据验证切换到出错警告选项卡中进行修改