关注可了解更多的分享内容,问题或建议，请公众号留言;

本文首发自微信公共账号：XrayStudio；

无需授权即可转载，可保留版权声明；

转载时请务必注明作者。

• Excel查找替换及定位

• Excel分类汇总和数据有效性

1-2-3垄断了年10年间的表格应用市场，直到1993年Windows平台Excel第5版的推出。Excel保留了对Lotus的支持，在文件-选项-高级-转换Lotus1-2-3公式那里打开。可以在需求量大(输入不带等号的公式)的时候打开，使用后关闭。

提升工作效率、批量操作、避免重复操作.

• 视图-新建窗口(增加窗口)-全部重排-垂直并排
  两个文档互为镜像，可相互修改，数据互通
  如果要经常使用这个场景，不用每次重复此动作，利用保存工作区

• 工作区：工作时的表格布局，工作区不保存数据
  2016貌似没有这一项，但2016可以方便的对比查看

工作簿，工作表，单元格

• 工作表名上右键-表标签颜色

• 表可以创建、删除、插入等

• 借助ctrl、shift可以对工作表进行多选/减选和连选

• 选择多行，连续插入多行

• 交换列-选中列-在列边拖动列-同时按shift

• 选中多列，双击列名-自适应列宽

• 选中多列，拖动某一列宽度，其他同时调整-等宽调整，行高同理

• 在选中单元格边线双击，到达收尾位置(连续单元格)

• 选择指定行到指定行区域：在名称框输入 a2:a900

视图-冻结窗格-冻结首行/首列/窗格

• 对于数据行比较多的数据，查看时首行不滚动，方便查看数据列名

拖拽操作：选中单元格-拖拽右下实心点

• 推拽时ctrl可切换复制与序列填充

• 对于时间填充，可以使用右键填充，会有多种选择：按月填充、按工作日填充、年、月等

• 文件-选项-高级-编辑自定义列表-新序列-填充自己的序列

• 单元格匹配(单元格必须完全一样)

• 替换-取消单元格匹配-选项-格式(颜色、字体等)-填充-颜色(相应设置目标色)

• 选择后会严格按照单元格格式进行匹配替换，颜色、边框、填充、字体等

• • * 在替换中代表任意多个字符

  • ? 代表一个字符，一般要加上单元格匹配，可以限制替换的字符数量，多个问号代表多个字符

  • ~ 强制替换通配符本身，这样可以把：张* 替换为 张三而不替换别的以张开头的单元格

• Excel单元格左上角那个框是名称框，可以定位单元格，区域使用：分隔

• 可以为单元格和区域定义名字(选中后名称框输入)，不能设置为已有的名称

• 查找替换-转到可打开面板

• 单元格右上角会有一个红色箭头

• 选中单元格可插入、删除、编辑、隐藏、显示批注

• 选中所有-右键删除所有批注

• • 在边框右键-设置批注格式-颜色那里可将批注的背景设置为图片

  • 首先添加编辑工具栏到界面

  • 光标选中批注外框，点击快速访问工具栏中的-更改图形-选择图形即可

• 选中待批注的单元格-查找和选择-转到-定位条件-批注-确定-设置颜色

• 查找选择-定位条件-公式

• 填充解除单元格合并后遗留的空白单元格

• 快速填充空白区域：选区域-输字符-ctrl+enter-区域全部填充

• 填充解除合并的空白列：选中数据-选择替换-条件格式-空值-选中数据框-输入等号-按上箭头选中上面单元格-ctrl+enter完成

• 查找选择-定位条件-对象-选择所有图片对象-delete

单元格格式工具美化表格

• 合并居中,设置背景、改变颜色

• 斜线设置：右键-设置-边框-斜线(写两行文字 - alt+enter)

• 表的格式通过格式刷扩展到其他类似数据表

• 双击格式刷进行连续刷属性

• 选中-右键-设置单元格格式-数值

• 日期本身是数字，1 对应 ,依次类推

• 数字可以转换为汉字的数字(本质是数值)

• 设置单元格格式-自定义-修改
  单元格格式不改变数值大小
  自定义类型中分号表示不同情况的划分，可以用于隐藏数据等

• 自定义数字格式修改日期
  dddd 英文星期全称
  aaa 一二三四五六日
  

• 数字文两类：数值和文本

  • 对于数值excel只能保证前15位准确，后面为0

  • 对于身份证号设置为文本格式

  • 文本格式数字转数字：选中-右上角感叹号-转换为数字

  • ==对于文本日期格式：数据-分列-下一步-设置格式(数据、日期等)==

  • txt数据不好统计，复制数据到excel，数据行分开，但列被合并

  • 选中-(数据)分列-按符号/固定宽度等

  • 首次分列先别点完成，选择下一步，可以对分开的数据二次设置

• 不能选择单列，会导致数据错乱

• 排序-筛选-自定义排序-分别定义添加要对比的数据列

• 通过单列排序，逆着重要性的关键字逐个排序

• 选中要排序的列-排序筛选-自定义排序-设置颜色级别

• 一部、二部、三部、四部等正常是按照拼音排序，达不到预想的效果

• 排序-自定义排序-自定义序列-定义我们要排序的列表

• 给工资行加入一列标示序列，建立等数量的表头，也赋予介于工资标示序列之间的序列如：1、2、3、4 和 1.5、2.5、3.5、4.5，利用升序排序即可完成工资条制作

• 页面布局-页面设置(右下三角)- 工作表-顶端标题行-选中首行，即可完成每页显示首行名称的设置

• 选择部分数据：开始-筛选-给列添加筛选

• 查找选择-定位条件-可见单元格(选择可见数据)

• 筛选-数字筛选-大于/等于/小于等可对数据按大小筛选

• 筛选-文本筛选-结尾是/开头是-填入设置字符-完成过滤

• 或者使用等于+通配符匹配

数据-筛选-右下高级，筛选值可在原位也可放置别的表

-b 高级筛选中使用常量条件区域
   常量写条件区域==一定带表头==，表头不能出错
-c 高级筛选中使用函数公式条件区域
   公式写条件区域==不带表头==，带表头会出错

-d 高级筛选也可以带多条件

5.Excel分类汇总和数据有效性

• 选中一个数据框-数据-分类汇总-设置三要素

• 按什么分类，把什么汇总，如何汇总(平均/求和等)

• 做分类汇总，第一步先排序

1、分地区统计金额的总计

先对地区进行排序，然后最分类汇总

2.分地区与产品分类统计数量、金额、成本的总计

先使用==嵌套排序==对地区和产品分类

• 使用取消替换当前分类汇总-可以用来把当次分类汇总嵌套到之前的分类汇总

• 复制分类汇总的结果区域

• 对分类汇总的数据要单独使用可以将其复制出来

• 使用分类汇总批量合并内容相同的单元格

• 分类汇总要合并的列，计数-把要合并的列拆分

• 选择要合并字段对应的空值列-查找和选择-空值-选择每个字段对应的空值区域-合并

• 用格式刷刷对应的要合并的列即可完成/或者复制-选择性粘贴-格式

1. 设置A列仅能输入500~1000之间的整数

2. 设置B列仅能输入字符长度为8位的产品编码

3. 设置C列付款方式中仅能输入现金、转账、支票

有效性针对整列操作-选择-数据-数据验证-根据需要进行设置

• 通过自定义序列来规范指定类别输入

• 选择列-数据验证-序列-来源手写(列名间用英文逗号)

• 可以对出错警告设置不同的级别，可自定义信息提醒

• 可以通过数据验证来设置不同列的输入法格式

• 使用完后可以对数据验证进行删除

• 选择一个数据框-插入-数据透视表(自动选择所有列)- 将需要作为分行的和分类的字段分别拖入表格框，并拖入要统计字段即可

  2.更改数据透视表汇总方式

• 大类中分小类-要进行分行嵌套可以把需要嵌套的行字段拖过去放在虚线位置即可，注意左右方向代表嵌套关系，左边大类，右边小类

• 对分类太过详细的列-选择一个数据框-右键-组合-对时间数据按月、季度等分区间等

  3.数据透视表中的组合

• 可对数据做==分区间汇总==，把不同的数据按照等差数列分区间-在数据列-右键-==组合==-构建等差数列

• 从多个角度进行统计数据

• 在分类字段列名上双击-分类汇总设置为 ==无== ,可去掉分类汇总，可以把嵌套分类放入同一行

• 拖入要分行的字段-拖入汇总字段，再次拖入一次，数据按行分项(目标是按列)，拖数据列到汇总列，继续拖入多次汇总字段-在列字段双击设置不同的汇总方法

• 最后可以对数据透视表选择模板美化

  5.在透视表中使用计算

• 数据透视表中使用函数(通过成本和合同额算利润率)

• 先汇总合同金额、成本，选择透视表单元格-数据透视表工具-分析-字段项目和集-计算字段

• 对于错误值-选择单元格-右键-数据透视表选项-显示-对于错误值显示为空

  6.利用筛选字段自动创建工作表

• 表名在一个工作表的列字段中

• 选择数据-插入透视表-将列名放在报表筛选- 列拖入数据汇总-选择单元格-数据透视表工具-选项-选项-显示表标筛选页

• 利用shift选择多个表，对透视表默认数据格子进行删除，整列表格

关注可了解更多的分享内容,问题或建议，请公众号留言;

感谢关注，与你一起进步。