【Excel】第2节 基础操作与技巧（二）

主要介绍Excel的第二部分基础操作：排序、筛选、数据验证和填充，数据验证可以帮助我们设置一些填写要求，方便后期数据的整理。智能填充则帮助我们快速提取信息

第2节 基础操作与技巧（二）

• 排序
  • 排序的在哪里？
    • 注意：排序的时候数据之间不能有空行
    • 1、【数据选项卡】/【开始选项卡】--> 排序与筛选
    • 2、右键 -->排序
    • 3、筛选中带排序
  • 排序的功能：
    • 常用排序
    • 自定义排序
    • 按笔画排序
    • 多条件排序
    • 按颜色/条件格式图标排序
  • 对合并单元格相邻的数据区域排序
    • • 选择需要排序的区域，把数据包括标题的选项取消掉就可以了
  • 排序后悔怎么办
    • 开始的时候就给数据添加一列，给数据编号
  • 利用排序做工资条
    • • 借助辅助列排序就可
  • 组内排序
    • 巧妙利用辅助列！！
• 筛选
  • 筛选的在哪里？
    • 注意：筛选数据之间不能有空行
    • 1、【数据选项卡】/【开始选项卡】--> 排序与筛选
    • 2、右键 -->筛选
    • 3、Ctrl+Shift+L
  • 基础筛选：
    • 1、值筛选
    • 2、条件筛选
    • 3、格式筛选
    • 4、按所选单元格筛选
    • 5、日期组合筛选
    • 6、模糊筛选
    • 7、反向选择筛选
  • 高级筛选：
    • 通常的筛选,可能会发生表格折叠,如果在某个Sheet当中,很多表格，建议你实用excel的高级筛选
    • • 方式
        • 在原有区域显示筛选结果：就是普通的筛选
        • 将筛选结果复制到其他的位置：将筛选结果显示指定的位置（常用）
      • 列表区域：数据源区域
      • 条件区域：
        • 同行为且，不同则为或
        • 由标题和值所组成的区域;
        • 常量条件：
          • 直接写
          • 标题：需要标题与列标题一致
          • 条件：可以使用比较运算符：=(等于)（省略）、>(大于)、<(小于)，>=(大于等于)、<=(小于等于)、<> (不等于)
        • 变量条件：
          • 用公式写
          • 标题：不能与数据源的某个标题一样；可以为空
          • 条件：以“=”开始的公式表示的，公式的结果是TRUE或FALSE
      • 复制到：
        • 你要将结果放在什么位置。如果在【方式】中选择【将筛选结果复制到其他位置】，则为失效状态;那么这个【复制到】编辑框会有效，否则为失效状态
      • 选择不重复的记录：提取不重复数据；筛选非重复的数据，重复的只保留一个
    • 注意事项：
      • 1、如果需要将结果显示在其他表，一定要在显示结果的表格开始做高级筛选
      • 2、在筛选对话框中“复制到”不能有引用，必须是单纯的表格表示（如：A1:A1，不能是sheet1!A1:A1）
    • 常量条件
      • 条件类型
        • 同行为且，不同则为或
        • 一类多个条件
          • 筛选销售额大于2000并且订单日期大于2020/6/1的数据
          • 筛选华东地区或者华北地区的数据
        • 一类多组条件
          • 筛选销售额大于1000并且小于3000，或者大于4000的数据
        • 多类多个条件
          • 筛选类别为办公用品，地区为华北地区的数据
          • 筛选类别为办公用品或者销售额大于3000的数据
        • 多类多组条件
          • 筛选（订单日期大于2020/6/1 并且 地区为华北的数据） 或者（类别子类别为椅子，销售额大于1000的数据） 或者（地区为华东，销售额大于2000的数据）
      • 通配符条件
        • 筛选子类别中带有”具“字并且地区是华北地区的数据
      • 字段选择
        • 将语文，数学，英语成绩大于60的学员筛选出来 要求筛选后的字段只保留姓名，语文，数学，英语
    • 变量条件
      • • • 函数：LARGE：求第几大值
      • • • FIND：查找；ISNUMBER：是否为数值
• 数据验证
  • 意义：让数据按照一定的规则填入
    • 限制表格中输入的内容-即输入的值有效才允许输入
  • 位置
  • • 允许的类型：
      • 任何值：默认
      • 整数
      • 小数
      • 日期
      • 时间
      • 文本长度
      • 类似
      • 序列：下拉菜单
        • 一级菜单
          • 1.直接输入创建下拉菜单
            • 用英文状态下的逗号隔开
          • 2.使用单元格引用创建下拉菜单
            • 直接选取
          • 3.使用定义“名称”创建下拉菜单
          • 4.使用超级表创建动态下拉菜单
            • 超级表：创建动态数据源的很好用的工具
            • • ctrl+T
              • 可以动态增加部门内容
          • 5.使用公式创建动态下拉菜单
            • offset
            • counta
        • 二级菜单
          • 1、先设置一级菜单
          • 2、给需要输入的二级菜单起个名称
            • • 不想一个一个地改名称可以这样做
            • • 使用Ctrl+G（定位），定位到常量（就可以不包含空白）
          • 3、在二级菜单的单元格中使用公式引用一级菜单的选项
          • 4、在二级菜单中创建序列
      • 自定义：公式
        • 1、仅限数值
          • ISNUMBER
        • 2、仅限文本
          • ISTEXT
        • 3、限制输入空格
          • ISERROR
          • FIND
        • 4、只能录入某种特征的数据
          • LEFT
            • • 从左
              • “1”文本1
              • 长度为11
              • and是并且的关系
          • LEN
          • AND
        • 5、限制输入周末日期
          • WEEKDAY
            • 设置的你的工作日是周几到周几
        • 6、不允许重复录入
          • COUNTIF
        • 7、强制顺序录入
          • MAX
        • 8、各项预算不能超过总预算
          • SUM
        • 9、根据其他列限制输入内容
      • 忽略空值：不勾选，则通过“Backspace”键删除excel单元格中的内容会报错
  • • 设置解释出错原因
  • 利用有效性圈释不符合要求的数据
    • 要求
      • 1)用数据验证圈释出数据1-5中低于产品标准的数据
      • 2) 假设产品标准是5，圈释偏差超过0.08的数据
• 名称
  • 名称是什么？
    • 实际上是对Excel“对象”的命名。而对象可以是：单元格、单元格区域、数值、数组常量、公式、宏表函数
  • 使用名称的好处：
    • 1、使公式、有效性、条件格式等等易于理解，比如用“税率”代替单元格引用
    • 2、简化公式
    • 3、利于安全，易于维护
    • 4、可以在条件格式、有效性中引用其他工作表单元格或常量数组
    • 5、调用Excel40宏表函数必须使用名称
    • 6、可以在控件中调用单元格内容
    • 7、Excel的几乎任何地方那个都可以使用名称
  • 定义名称的三种方式：
    • 1、插入名称
      • 名称是不可以重复的！！
    • 2、使用名称框
    • 3、根据所选内容创建
  • 批量删除/查看名称的方式：
    • 【公式选项卡】-->名称管理器
  • 名称的约定：
    • 1、名称不得与单元格地址相同。
    • 2、名称不得使用空格。
    • 3、名称不得以数字开头或单独使用数字。
    • 4、Excel识别名称时，不区分大小写。
    • 5、名称是一种特殊形式的“公式”，因此其长度、内部嵌套等同样受到限制，比如内部长度最大16,384个字符。
    • 6、名称所用字符不能超过255个字符。
    • 7、当名称引用单元格区域时，要考虑相对引用和绝对引用
  • 练习
    • 将超出预算的标黄
      • 使用条件格式，需要注意绝对引用和相对引用
    • 在名称中使用常量
    • 在名称使用函数
      • • 注意：此时使用的是相对引用，需要在定义名称的相对于“活动单元格”设定名称