Excel笔记

表格排版

添加边框

  1.选中需要添加边框的表格项，然后点击下方按钮，选择要添加的边框样式（在其他边框即可选择不同的边框样式）

  2.隐藏网格线：当添加完边框后，如果想把边框外的网格线去掉，可通过把下面的勾去掉来实现

合并单元格

  1.选中需要合并的单元格，然后点击开始菜单中的【合并后居中】即可实现

  2.开始菜单中的【格式刷】可以把当前单元格合并后的样式复制到其他目标单元格

调整字号、行高/列宽（美化表格）

  1.调整行高/列宽：把鼠标放在行与行或列与列中间，就可以调整行高或列宽；当鼠标移动到最左边的序号然后出现一个向右的箭头时可以同时选中多行或多列，然后继续调整会同时调整多行和多列的宽高（注意不能直接选中多行多列然后调整，那样只会调整一行或一列的宽高）

  2.字体常用微软雅黑

案例：梳理会议记录

  1.先梳理清楚会议，记录表格需要的内容：
   ● 会议主题
   ● 会议时间、地点、记录人’
   ● 参会人员
   ● 会议内容等等
   ● 然后找张纸，画出大体的框架

  2.照着草稿制作出初始的表格：

  3.合并单元格：

  4.调整字号、行高和列宽：

保护、移动和备份表格

  1.简单的隐藏工作表：隐藏后点击取消隐藏则可显示工作表，注意隐藏工作表时，至少含有一种可视工作表，不能所有都隐藏

  2.禁用取消隐藏，保护工作表：选择审阅菜单中的【保护工作簿】，然后设置密码。设置完后就不能取消隐藏了，如果想要查看工作簿，则需要再次点击【保护工作簿】，然后输入设置的密码，就能取消隐藏并观看了

  3.给整个文件加密：选择文件菜单中的【保护工作簿】，在下拉菜单中选择【用密码进行加密】；再次点击【用密码进行加密】，把设置的密码删除掉，就能删除密码了

  4.汇总和复制表格：打开要汇总的所有Excel文件，然后选择【移动或复制】

  然后点击这里就可以显示目前打开的所有工作簿，选择要汇总的工作簿，再选择工作表的存放位置，最后记得勾选建立副本，如果没有建立副本，则移动的工作表会从原来的工作簿消失，相当于剪切

表格打印前的设置

  1.调整表格为一页打印：一个整体的表格打印出来变为两页，是因为分页线的问题。在正常情况下是看不到分页线的，需要在视图菜单中选择【分页预览】才能看到分页线（蓝色线条），如下图，打印部分为蓝色线框内的内容，可以通过拖动分页线，设置打印的范围。当打印的范围大于使用的纸张大小，Excel会自动添加分页线，把这部分内容分为几页打印，如果想要把这个内容用一张纸打印下来，就需要手动删除多余的分页线

  2.在打印出来的每一页上自动打印标题行：选择页面布局菜单的【打印标题】→【工作行】→【顶端标题行】左边的箭头，然后框选要打印的标题行，最后确定即可

  3.在打印出来的每一页上添加页眉和页脚：选择页面布局菜单，点击页面设置右下角的小箭头。选择【页面/页脚】，可以选择自带的页眉和页脚，也可以自定义页面和页脚

  4.选择视图菜单的【页眉布局】，也可以看到打印效果同时可以设置页眉和页脚

大表格的快速浏览

  1.大表格：把一些又长又宽的表格称为大表格，在浏览数据时，要不停的滚动鼠标，而且标题总是跑到上面，查找数据也十分困难

  2.操作大表格的快捷键：
   ● Ctrl + ↓ ：移动到表格的最后一行
   ● Ctrl + ↑ ：移动到表格的第一行
   ● Ctrl + → ：移动到表格的最右一行
   ● Ctrl + ← ：移动到表格的最左一行
   ● 注意：以上快捷键在使用时，中间不能有空格，不然就是移动到最先出现的空格处
   ● Ctrl + END：快速跳转到整个数据区域右下角
   ● Ctrl + HOME：快速跳转到整个数据区域左上角
   ● Ctrl + Shift + 上下左右键：快速选择连续的单元格

  3.滚动浏览固定标题行：先选择标题栏的下一行数据，然后点击视图菜单的【冻结窗格】即可，注意要冻结哪行或哪列都得选择该行或列的下一行以及下一列。如果只冻结一个单元格，则该单元格上面的行和左边的列将保持不动

  4.快速浏览表格：可以通过左上角这个框给不同的单元格或表格区域定义一些名字，下次需要查找这些内容直接按下拉按钮就能看到

  5.查找指定内容：快捷键（Ctrl + F）然后输入要查找的内容

多表格高效浏览

  1.快速切换工作表：当有很多张工作表时，浏览起来会很麻烦，可以通过右下角的Ctrl+向左或向右箭头进行快速切换，点击右键还可以快速定位想要的工作表

  2.快速隐藏所有表格：选择第一个表格，然后移动到最后，按Shift后再点击倒数第二个工作表，就能选中第一个到倒数第二个的工作表，然后右键隐藏即可

  3.快速取消隐藏所有表格：在没有隐藏工作表之前，选择视图菜单中的【自定义视图】，点击添加并命名来保存当前的视图状态，最后再隐藏所有表格，然后再次点击【自定义视图】中的显示，就能快速取消隐藏所有表格了

  4.同时查看大表哥的不同位置：选择视图菜单中的【拆分】即可，双击拆分线可以删除或者再次点击【拆分】可以删除所有拆分线，横向的拆分先还可以通过拖到顶部或底部进行删除

  5.同步滚动查看不同的表格：选择视图菜单中的【重排窗口】即可对打开的所有Excel进行指定的排序；选择【重排窗口】中的垂直平铺然后点击【并排比较】→【同步滚动】就能同步滚动查看不同的表格

分类查看数据

  1.按条件筛选数据：先选择标题行，然后选择开始菜单或数据菜单中的【筛选】按钮，在标题行就会出现一个筛选按钮，然后就可以进行筛选了

  2.给数据排序和分类：使用【排序】功能可以把相同类型的数据集中在一起。先选中需要排序的列中的任意一个单元格，然后选择开始菜单中的【排序】进行升序或降序排序即可

  3.快速隐藏不想看到的数据：先对数据进行排序，然后选择数据菜单中的【分类汇总】，选择相应的分类字段即可。注意：如果不先排序会导致折叠按钮过多，因为折叠按钮是按连续的数据出现的。点击【取消组合】中的清除分级显示即可清除所有折叠按钮

人员信息的快速录入

  1.给定一个文本文件，然后把文本中的人员信息输入到表格。最笨的方法是一个一个的输，但是输入过程除了打字速度，最影响效率的动作就是选择单元格，所以可以通过【上下左右键】来选择单元格，【回车键】可以换到下一行的单元格，【Tab键】可以换的同一行的下一个单元格（框选一个区域，可以一直使用）

  2.输入效率提升后，可能还会出现如下问题：

  3.身份证超过15位数会被Excel误认为是超大数据，然后用科学计数法记录，解决方法如下：

  4.序号没补零：可以按照上面的身份证方法，也可以通过设置单元格格式（快捷键：Ctrl + E，也可以通过选择一列然后右键打开），然后选择数字自定义，输入三个0即可

  5.日期排序错误：


   ● Ctrl + 分号键：快速输入当前日期
   ● Ctrl + Shift + 分号键：快速输入当前时间
   ● Ctrl + H：查找并替换

减少重复输入内容

  1.数据验证：先选中要进行验证的列，然后选择数据菜单中的【数据验证】，然后选择序列，把部门表作为来源，这样在部门所在的列就可以通过下拉列表选择相应的部门，同时也可以手动输入，但是如果不是部门表中存在的部门会提示数据错误，起到验证数据的作用。而且对部门表的修改也会同步更新到下拉列表

   ● 补充：如果只是单纯验证性别，则不需要多创建一个表，只需要在来源中输入：男,女（注意中间是英文逗号）

  2.快捷操作批量生成数据：如果输入的数据相同，则可以使如下快捷键：
   ● Ctrl + D：把上方单元格数据快速复制到下方的单元格（可以一次复制一个或者一次选择多行然后复制，要注意第一行必须有数据）
   ● Ctrl + R：向右填充，跟上面的类似
   ● Ctrl + Enter：批量填充。步骤如下：

数据的导入和导出

  1.把文本中的数据导入到Excel中：选择数据菜单中的【自文本】，然后通过文本中特定的符号进行拆分，选择特定的分隔符号将目标文件导入。导入后的Excel文件内容还会随着原始文本文件的改变而实时更新

  2.如果直接将文本文件复制粘贴到Excel文件中，则只会占用一列的单元格，这时可以通过选择数据菜单的【分列】对数据进行分隔

  3.将Excel中的通讯录同步到手机：

   ● QQ同步助手地址：ic.qq.com
   ● 进去后点击【更多操作】→【导入联系人】→选择Excel【模板下载】，然后把要导入的信息对应的粘贴到模板，回到QQ同步助手，选择【点击选择Excel文件】导入模板文件，最后手机下载同步助手即可导入上传的通讯录

  4.CSV文件：

   ● 把文件目录下的所有文件名导入到Excel中：先在该目录创建一个文本文档，打开文本文档输入：dir /b > 文件名.csv，保存文本文档，把文本文档的后缀改为 .bat，然后双击可以看到一个黑窗口一闪而过，这时目录就会多出一个 文件名.csv 的文件，用Excel打开就是该目录下的所有文件名

给表格加点商务范

  1.用智能表格快速美容：先选中任意一个单元格，然后选择插入菜单中的【表格】，即可将表格转为智能表格，在设计菜单还能选择不同的样式

  2.三招美化商务表：
   ● 弱化表格内容：删除多余线条或把线条变为灰色
   ● 突出标题内容
   ● 给文字留点空白

数据图形化

  1.数据条，让数据更加直观：先选中要图形化的数据，然后选择开始菜单中的【条件格式】，选择对应的图形化样式，还可以通过管理规则去设置相应的规则

  2.迷你图，单元格里的图表：先选择插入菜单的【折线图】
   ● 选择数据范围：

   ● 选择位置范围：

   ● 要确保两个选择的行数是一样的：

   ● 点击迷你图中的单元格，然后选择设计菜单，还能设置折线样式

  3.图表，数据可视化必备工具：


   ● 先选择要图表画的表格，然后选择插入菜单的【折线图】

   ● 图表是单独的对象，类型丰富，设置的选项细致入微，而迷你图则是在单元格中的图表，不是单独的

动态图表

  1.自动突出指定数据：
   ● ①通过【数据验证】给部门添加下拉列表

   ● ②选择所有数据，选择开始菜单的【条件格式】中的【新建规则】，接着操作如下：


  2.给高亮数据加个图表：动态图表的本质，实际上是动态的数据
   ● ①先选中部门那一行数据，然后插入折线图

   ● ②通过【数据验证】给部门添加下拉列表

   ● ③设置公式：VLOOKUP(要查找的内容（注意要锁定行和列，F4可以快速锁定），要查找的范围（注意要锁定行和列，范围需包括要查找的内容），要返回的数据列数，0（0为精确查找，1为模糊查找）)
   ● 公式COLUMN：返回当前列序号


   ● ④右键图表→选择数据，把原始的数据一条条加入图表并设置颜色为灰色


  3.动态表格神器之切片器：先把表格转为智能表格，然后选中任意一个单元格，点击设计菜单中的【插入切片器】，选择部门，就能得到如下的切片器：

  接着选择表格数据，插入折线图表，就可通过切片器实现动态图表了

筛选方法大全

  1.数字筛选：看情况筛选

  2.日期筛选：与数字筛选类似，注意：只有正确的日期格式才能实现日期分组

  3.文本筛选：与前面的类似，不过有时候得使用通配符：
   ●  * ：表示任意数量任意字符
   ●  ？：表示单个任意字符

  4.按颜色筛选

  5.实用筛选小技巧之按右键筛选：

  6.实用筛选小技巧之多条件筛选：比如先筛选姓马的，接着：

  7.高级筛选：

   ● 先把筛选条件创建成一个表：



   ● 接着选择数据菜单中筛选旁边的【高级】，然后选择区域即可

  8.清除筛选：筛选完后在原本的筛选旁边就有清除筛选的功能

排序方法大全

  1.排序的位置：

  2.简单排序：

  3.高级的自定义排序：
   ● 多列排序：选择数据菜单中的【排序】，接着按优先级添加条件（上面的优先级大）：

   ● 颜色排序：选择数据菜单中的【排序】，接着添加条件：

   ● 自定义序列排序：


   ● 横向排序：


   ● 按笔划排序：


  4.实用排序技巧：

   ● ①添加辅助列，填充序号

   ● ②把标题创建到所有空行里：

   ● ③在辅助列上右键选择排序：升序排序，即可得到工资条，最后删除辅助列即可

常见筛选排序错误

  1.筛选内容不全：表格中用空行导致，解决方法如下：

   ● 筛选快捷键：Ctrl + Shift + L
   ● 批量删除空行：先任选一列，然后 Ctrl + G 定位，定位条件选择空值，这样就能选中该列所有空值，然后右键空值→删除→整行

  2.筛选按钮位置不对：如第一行为空值，则直接点【筛选】按钮会出现在第一行上，因此需要先选中要出现筛选按钮的一行再点击【筛选】

  3.筛选记录不全：合并单元格导致的问题

   ● ①先把该列复制下来：

   ● ②选择合并的单元格，取消合并，选择空值，批量填充：

   ● ③然后点击【筛选】，再把原来复制的那列格式刷到已经进行筛选的列即可（使用格式刷会保存原来的数据）

  4.无法使用筛选功能：一般是工作表被保护导致的，撤销保护即可

数据核对

  1.条件格式，核对重复值：选择开始菜单的【条件格式】：

   ● 删除重复值：

  2.比较运算符，同行对比：

   ● ①先设置辅助列：

   ● ②新建格式规则，标出重复值（注意只锁定列即可）：


  3.行内容差异单元格，高效核对：

   ● 填充即可标出差异的单元格：

   ● 快捷键：Ctrl + \

数据核对常用公式

  1.COUNTIF（查找的数据区域，要查找的数据）：统计次数
   ● 案例：通过统计人员出现次数，来找出未出现的人

  2.COUNTIFS（查找的数据区域，要查找的数据，…）：多条件统计次数

  3.VLOOKUP(要查找的内容（注意要锁定行和列，F4可以快速锁定），要查找的范围（注意要锁定行和列，范围需包括要查找的内容），要返回的数据列数，0（0为精确查找，1为模糊查找）) ：匹配数据
   ● 因为VLOOKUP一次只能判断一个条件，所以需要先把多个查找条件合为一个，另一个也是对比的表也是同样操作

高级核对技巧

  1.选择性粘贴：先复制系统导出的数据，然后粘贴到人工记录数据，注意不是直接粘贴，而是右键【选择性粘贴】


  2.合并计算，核对不同行数据（注意只能针对数据进行核对）：
   ● ①先选中一个单元格，然后选择数据菜单中的【合并计算】

  3.高级筛选，核对各种类型数据：
   ● ①先通过高级筛选，筛选出重复的数据

   ● ②给筛选后的数据填充颜色，然后取消高级筛选，没有填充颜色的数据就是不重复的

  4.VLOOKUP精确匹配：快速核对上述高级筛选空白区域的差异

三步搞定统计分析

  1.需求分析：在统计分析之前要弄清楚对方的需求是什么，比如老板可能想知道盈亏，财务部门想知道工资情况，人力资源部门想知道离职率等等

  2.数据整理：避免阅读型表格，把表格整理成连续的数据型表格，可以让统计分析更加高效

   ● 数据型表格：首行为标题行，数据逐行排列

  3.统计分析：掌握各种不同的分析方法，实现数据的轻松查找，快速比对差异，可视化呈现（交叉分析、对比分析、数据可视化）

数据整理

  1.把阅读型表格整理成连续的数据型表格


  2.智能表格，一键规范数据表：注意勾选表包含标题

  3.二维表转一维表：


   ● 先选中二维表中的一个单元格，然后选择数据菜单中的【从表格】，接着选中要转换的数据，点击转换菜单的【逆透视列】再点击开始菜单的【关闭并上载】即可：

常见的统计分析方法

  1.分组对比法：按照时间、地点、任务、产品类型等原则，对数据进行分组，可以有效的减少数据量，让数据变的清晰。接着通过求和、平均值、百分比、计数等方式，把相同类别的数据，汇总成一个数据，减少数据量

  2.交叉分析法：把需要查询的数据做成一个二维表，方便查询

数据透视表

  1.先选中一个单元格，然后选择插入菜单的【数据透视表】，可以配合切片器使用或者和分析菜单的【数据透视图】配合使用

时间安排表

  1.日期本质：


  2.快速输入会议时间：输入公式

  3.计算迟到时间：


   ● ①先计算相差的时间（小数形式）：

   ● ②设置单元格格式，舍弃小数位数，把数值转为对应的分钟数：

   ● ③用 IF 函数进行完善：

  4.准确计算转正日期：
   ● 转正日期（方法1）：直接入职时间减去90天
   ● 转正日期（方法2）：


   ● 转正日期（方法3）：

  5.快速输入进削存日期：

提取-文本函数

  1.提取身份证号码生日：



  2.提取身份证号码籍贯：


  第二个参数为对应的创建好的地址码的表格

  3.提取名字：


  4.提取括号里的内容：




  上述公式合并：

  5.提取第二行的内容（换行符对应的ASCII码为10）：

查询公式

  1.VLOOKUP函数：只能从左向右查找

  2.INDEX+MATCH函数：不受数据顺序影响



  3.LOOKUP函数：默认模糊查找，比如查找下面表中如果查找的是75000，会去找比它小的50000而不会找80000

单元格内容提取

行列转换

快速合并汇总多个表格

  1.使用方方格子插件，不过不能实时更新

  2.使用 Power Query 合并工作簿：

小技巧

隔列求和

  1.SUMIF(求和的条件区域，求和的条件，真正的求和区域)：求和

  注意：在将公式向下复制之前，需要对求和的条件区域进行锁定；求和的条件的行锁定，列变化；真正的求和区域的列锁定，行变化，即：SUMIF($B$3:$O$3, P$3, $B4:$O4)

避免Excel自动创建超链接

  1.避免Excel自动创建超链接：在填写邮箱时，Excel会自动创建超链接，但是有时候不想要超链接就需要如下操作：

  2.上面这种方法只能撤销单个超链接，下次输入Excel还是会自动创建超链接，因此如果想取消自动创建，就需要：

  3.若需要恢复自动创建，则需选择文件菜单的【选项】，接着如下（同时也可以通过取消这个来取消自动创建）：

解决图表上折线出现断裂的问题

  1.解决因空单元格导致图表上折线出现断裂的问题：在图表折线图上右击，点击【选择数据】，接着：

使饼图中一小块延伸出柱形图

  1.实现：在修改前的图表右键选择【更改图表类型】，接着：

  2.上面是把表格最后几个数据作为柱形图延伸出来，要是想把最小的几块合在一起延伸出来，就需要对数据源进行降序排序；如果想要增加柱形图的数量，就右键选择【设置数据系列格式】，然后将第二绘图区中的值改为想要的数量即可：

实现折线图的自动更新

  1.表格每天增加一列，实现折线图的自动更新：通过插入菜单的【表格】把普通表格转为智能表格即可，转化后可以通过设计菜单的【转化为区域】把智能表格重新转为普通表格（把普通表格转为智能表格还可以通过开始菜单的【套用表格样式】选择任意一种样式即可；还有一种是快捷键：Ctrl + L）

取消保存文件时出现的对话框

  1.保存文件时，总是提示如下对话框：

  2.解决：点击文件菜单的【选项】【信任中心】→【信任中心设置】，接着（注意这个设置是一次性的）：

给折线图添加垂直的引线

  1.先点击修改前的折线图，然后选择设计菜单中的【添加图表元素】→【线条】→【垂直线】即可

  2.把引线美观化：双击任意一条引线，接着：

修改图表的数据源

  1.实现方法一：右键图表点击【选择数据】，接着重新选择数据区域即可：

  2.实现方法二：直接拖动表格右下方箭头：

  3.实现方法三：先复制要添加到图表的数据，然后点击柱形图进行粘贴即可

给图表添加新的数据系列

  1.复制要添加的两个列到图表中：

  2.粘贴后所有系列名称都是达成率，需要改成对应的月份

数据透视表-统计不重复次数

  1.注意：以下操作2013版本后才有

数据透视表刷新时保持列宽不变

  1.解决：右击透视表，选择【数据透视表选项】，接着：

取消透视表的分类汇总

  1.第一种方法：取消全部分类汇总

  2.第二种方法：取消部分分类汇总

行列互换

  1.选择性粘贴快捷键：Ctrl + Alt + V

二维表转一维表

快速合并同类别单元格

提取文本中的数字

  1.Ctrl + E：快速填充，需要数字特别有规律才能用

日期标准化

英文单词首字母全部改为大写

筛选后粘贴数据

  1.另一种方法就是选择筛选后的单元格，按 Alt + 分号键 即可

普通数据区域如何局部调整数据行列的次序

智能表格中如何局部调整数据行列的次序

  1.在智能表中只能通过选中整列进行移动，不能只选择一部分进行移动，而在普通表可以选择一部分拖动到另一部分实现数据的交换

跨行合并多行单元格

快速调整行高和列宽到合适的高度和宽度

给邮箱添加后缀

将多个单元格合并成一串文本并以逗号隔开

表格数据少，文件却很大的原因

  1.第一个原因可能是有太多空行和空列导致，可以先选中一个空白行然后通过快捷键：Ctrl + Shift + ↓ 选中下面所有空白行然后进行删除，列也同样的操作；第二个原因可能是隐藏图片导致，可以通过选择开始菜单的【查找和选择】→【选择窗格】然后显示后把隐藏图片删除

给不同的区域设置密码

  1.先全选表格，查看保护是否已经锁定

  2.选择审阅菜单的【允许用户编辑区域】，然后选择相应的区域设置不同的密码即可

合并单元格中输入连续序号

筛选后也有自动连续序号

函数按类别自动编号

表格打印文字显示不全原因

  1.第一种方法是去掉分页线

  2.第二种方法是选中要复制的区域，接着：

按某一列分类把数据拆分成多个表格

根据单元格颜色进行求和

  2.方法二：先定义公式名称

  根据颜色公式获取颜色代码：


  最后保存工作簿需要保存成启用宏：

对小数向上求整

消除单元格的绿色小三角

  1.上面的分列方法是选中需要转换的一列数据，然后点击数据菜单中的【分列】即可，但是只能一列一列进行消除

  2.第三种方法：先在一个单元格中输入0，然后复制该单元格，选中有三角形的数据，不同列不同行也可以，只要能同时选中就行，接着选择性粘贴：

让手机号自动分段显示

批量让数据以万单位显示

提取英文内容

  1.分列功能只能提取第一行的数据，因此直接分列就能把中文提取出来

避免输入重复项

在Excel中制作打勾的方框

编辑有超链接的单元格

批量输入有规律的序号

根据指定日期返回返回当月的最后一天

根据指定日期返回返回当月的第一天

插入形状快速对齐表格的网格

屏幕无法向下滚动，但是缩小又能看到一个数据表