工作中 Excel 哪个功能最实用？
作为一位Excel发烧友，使用过Excel中非常多的功能，借着这个话题给大家分享 47 个非常实用的Excel功能，有些你经常接触，有些肯定会让你大开眼界，帮你大大提升工作效率~ 被评为Excel中最强大的快捷键——Ctrl+E，这是一个最懂你的快捷键，是Office 2013版本推出的一个快捷键，充分体现了你想要什么，Excel就帮你处理什么。 1.1 快速拆分单元格 在没有这个快捷键之前，拆分单元格要用到MID/LEFT/LEN等函数，现在直接给Excel一个案例，然后回车按Ctrl+E，就能自动识别了，例如下方的拆分案例： 1.2 快速合并单元格 除了拆分单元格，合并单元格也非常智能，平时只能用&连接符号或者函数的方式连接，同样给Excel录入一个示例，然后回车Ctrl+E，也能自动合并了，超级智能： 1.3 提取身份证日期 在Excel中使用MID或者TEXT函数提取身份证中的出生日期是不是特别麻烦？这个时候懂你的Ctrl+E又来了。 1.4 格式化字符串 想要快速格式化某列数据，Ctrl+E也可以实现，比如要在手机号打码，同样只要给Excel一个“例子”进行学习，剩下的都交给Ctrl+E。 作为Excel里最废纸的一个功能，新手往往在打印文档的过程中会碰到各种奇怪的问题，来分享 8 个超级高效实用的Excel页面设置打印技巧，帮助你轻松提升工作效率。 2.1 只打印部分数据 选中需要打印的部分，点击点击「页面布局」选项卡下的「打印区域」，这样就ok啦~ 2.2 快速调整打印比例 在「页面布局」选项卡下填写「缩放比例」就可以了，这样不仅不会更改页面显示，还能控制打印比例。再也不用通过修改单元格&字体的大小来控制了。 2.3 多页打印重复标题 数据列表太多，每一页都需要打印标题，不需要将标题加入到数据中，利用Excel的打印标题功能即可轻松搞定。 点击「页面布局」选项卡下的「打印标题」，然后设置「顶端标题」，这样在打印的时候每一页都有标题了。 2.4 文档插入页码 如果想在打印的Excel文档中插入并且显示页码，设置页超级简单。 点击底下的「页面布局」按钮，然后点击「添加页眉」，使用「设计」中的页码就ok啦~ 2.5 设置特殊页码 如果不太想使用Excel自带的页码格式，也可以自定义设置页码。 例如修改成：- 1 - 这种格式，可以在上一步插入完成页码之后，使用 & 连接符号链接短横杆，轻松搞定。 2.6 快速改变页面的大小 如果想在页面中看到打印机打印了几页，并且每一页的范围是哪里，可以点击「视图」下的「分页预览」。 这个时候还可以轻松利用鼠标来改变每页的大小&宽度。 2.7 一键缩放页面 另外如果觉得通过鼠标的方式来调整页面太繁琐的话，Excel也为我们设置了一键缩放为一页的快捷操作。 按「Ctrl+P」打开打印预览，在最下面的配置中，选择「将工作表调整为一页」，这样就缩放到一页啦。 2.8 快速调整列宽页边距 仍然是按「Ctrl+P」打开打印预览，鼠标点击右下角的「显示边距」就可以使用鼠标拖动边距了。 谈到实用，Excel中的名称定义绝大部分小伙伴都没使用过，巧妙利用名称定义能大大简化公式的复杂程度，并且让公式更加规范。 3.1 如何给单元格起名 起名的步骤其实非常简单，选中区域，然后在名称框中输入名称，接着按下Enter键就搞定了。 命名完成后发现选中区域之后，名称框里就会显示「语文」，也就是我们在引用这块区域的时候可以用名称「语文」，来进行代替。 先来看下原本的评级公式有多复杂，虽然用的是最基础的IF，但是因为嵌套的层数导致看起来略复杂： 如果将这些区域进行命名，例如「语文=$B$2:$B$18」，这个时候公式可以简化为： 如果将所有区域都进行命名，那么公式就会更加简洁了，变成了这种语义非常强的写法，初学者也能快速理解这个公式的含义： 3.2 同时命名&删除命名 如果某个函数列用到了非常多的字段，我们要为每个区域单独命名，其实也是一件非常繁琐的事情，好在Excel为我们提供了一个批量命名的功能。 选中数据之后，需要保证所选的数据区域第一行是数据的标题，然后选择「公式」选项卡下的「根据所选内容创建」，单独勾选「首行」。录制了一个动画操作如下： 这样我们就可以根据数据的标题，快速进行批量命名了。 既然可以添加命名，那么肯定可以删除命名，删除的办法也很简单，点击「公式」选项卡下的「名称管理器」，然后找到想要删除的，选中直接删除即可。 如果想批量删除多条，按住Ctrl键或者Shift键选中多条，点击删除即可。 是不是非常强大，特别在一些复杂的公式中，利用单元格命名，能大大提升公式的可阅读性，对新手也更加友好。 在Excel中，除了有Ctrl+E这个快捷键，还有另外一个按键也经常被大家忽略，就是Ctrl+T智能表格按键，不仅能一招美化表格，还能自动将数据区域命名，同时自带高级筛选功能。 4.1 五秒美化表格 鼠标点击表格内的任意一个单元格，按「Ctrl+T」生成智能表，然后选择「设计」选项卡下的任意一个样式即可，五秒内轻松美化表格。 虽然谈不上特别好看，但是好在速度特别快，整体也不差，并且Excel内置了几十种配色方案。 4.2 自动扩展表格 如果使用传统的办法美化表格额话，如果插入了新的数据行，需要重新美化一次表格，非常麻烦，而「智能表格」能自动扩展表格，无论是插入行/列都能扩展，格式当然也会自动扩展： 并且在每一行的末尾直接按「Tab」键就能生成新一行，非常智能。 4.3 高级交互式筛选 在Excel中，除了透视表能使用切片器之外，智能表格同样也支持切片器的操作。选中表格，点击「插入」选项卡下的「切片器」，勾选需要筛选的字段即可。 可视化的「筛选」，铁定可以让你的Boss眼前一亮，而且非常强大的是「切片器」也可以直接套用内部的样式： 交互式筛选+样式统一，一样都不差，鼠标点点就能搞定！ 4.4 自动汇总计算 既然是智能表格，肯定不能只当一个好看的花瓶，除了好看之外，还自带「汇总计算」功能，最常规的汇总计算公式一个都不用写，鼠标点点就能完成。 勾选「设计」选项卡中的「汇总行」即可，常规内置的七八种汇总方式，鼠标选择就能快速计算： 如果汇总不想要了，直接取消勾选「汇总行」即可，下次勾选的时候，仍然会显示你上次操作的数据，非常便捷。 4.5 自动填充公式&结构化引用公式 在智能表格中同样可以使用公式，并且所有公式都是「结构化引用」的，在前面的分享中，我们将到过利用“名称定义”来简化公式，而在智能表格中会自动生成名称，非常方便。 并且输入公式后，就会自动填充一整列，也不需要双击。 同样，修改公式也可以立马一键同步，省去了双击填充的操作。 4.6 一键选中数据 如果表格数据非常长，你想选中某一列的数据，操作是不是非常麻烦？看下「智能表格」如何一秒选中一整列数据： 智能表格中还有非常多强大地操作，例如配合透视表可以实现动态添加数据区域等。 作为Excel中最不起眼的功能，实际上却有着非常多惊为天人的技巧，例如：隔行填充、剪切图表、刷指定内容、刷整行整列、合并相同单元格等等。 5.1 固定格式刷 每次刷完需要重新选中下区域在点击格式刷，非常麻烦，其实只需要快速「双击格式刷」就能将格式刷给固定住了，想刷哪就刷哪。 另外在格式刷的状态下，点击「列/行标」可以刷一整列或者一整行，点击左上角的「三角形」可以快速填充一整个工作表。 5.2 制作斑马条纹（隔行填充） 除了利用Ctrl+T智能表格的方式来创建斑马条纹，还可以利用格式刷的方式来创建，选中最开始的2个单元格（注意是两个颜色不同的单元格），然后点击格式刷，最后「选择整个要刷的区域」即可。 5.3 只刷指定内容 如果只想利用格式刷刷指定的内容呢，例如只刷“姓名”这个单元格。「双击格式刷」，按「Ctrl+F」，查找“姓名”，点击「查找全部」，然后按「Ctrl+A」全选即可。 5.4 快速合并单元格 如果想将某一列的合并单元格拷贝到另外一列上，只需要选中这一列，然后点击格式刷，直接刷到另外一列上即可将合并单元格拷贝过去，非常简单。 06. 超高频使用按键——Shift按键 作为Excel里使用频率最高的几个快捷键，绝大部分Excel使用者只用到了Shift键的大小写功能，其实单纯的Shift按键就有大大小小不下20种技巧，这里分享 8 个视频频率超高的技巧给大家。 6.1 快速选中数据区域 这是Shift键最基础的一个功能，点击数据区域的「左上角」单元格，接着按住Shift按键，再点击「右下角」单元格，这样就可以快速选中一个区域了。 当然，先选中「右下角」单元格，接着在选中「左下角」单元格也可以。 6.2 快速向下选中区域 如果需要快速选中向下或者向右的连续数据区域，可以在选中第一个单元格之后，按住「Shift键」，然后双击下边线或者右边线即可。 这里的数据必须是连续的，如果数据出现断层，那么就不能选中所有数据。 6.3 绘制“正”形状 在Excel中我们可以插入各种各样的形状，如果我们想让插入的形状宽高比为「1:1」的话，那么我们在插入形状的时候，就可以按住「Shift键」，这样左右插入的形状都是“正”的。 6.4 反向Tab/Enter按键 Tab是跳到下一个单元格，Enter是跳到下一行单元格，如果配上Shift按键，这两个按键的方向就会变得相反。 在需要录入大量数据的时候，利用这个技巧就可以大大提升数据录入的效率。 6.5 Excel快速插入批注 选中单元格，按住「Shift+F2」就可以快速在单元格插入批注，如果单元格本身已经存在批注了，那么会进入快速编辑状态。 6.6 快速插入空白行/列 选中某一行或者某一列之后，按住「Ctrl+Shift+等号」就可以调出插入对话框，选择需要插入的行或者列就可以了。 6.7 快速新增工作表 除了新增空行和空列，Shift这个按键也可以新增工作表，按住快捷键「Shift+F11」就可以快速新增工作表了，注意下工作表名字的变化： 6.8 插入任意多行空行/列 在某个位置想要批量插入3列空白列，一次一次操作非常繁琐，其实利用「Shift」可以快速完成这个任务。按住「Shift键」不放，把光标放行/列标右下角呈上向分开形状时，可以拖动插入任意多空行或空列，插入后还可以调整数量。 作为Excel里最强大的数据分析神器，在90%的情况下都可以代替一些复杂函数的工作，让你的效率倍增，来学习这几招关于透视表非常强大使用的技巧。 更加详细的课程学习链接：网易云课堂精品课-Excel就这样用，透视表篇 7.1 插入数据透视表 数据透视表其实就是Excel提供的一个可视化的数据分析工具，在使用它之前，我们需要根据「数据源」生成透视表。 操作很简单，选中数据之后，点击「插入」选项卡下的「数据透视表」，就可以快速生成透视表了。 这一步操作之后，Excel默认会在一个新的Sheet新建一份空透视表，如下图这样： 至此，我们就完成了高效数据分析的第一步，在透视表中，总共有4个区域，分别是「筛选区域」、「列区域」、「行区域」、「值区域」。 最基础的使用办法就是想让哪个字段出现在什么位置，就拖动到什么位置即可，操作非常简单。 上面的动图分析了什么呢？是不是在3秒钟内就完成了「不同地区不同部门的销售额汇总」，如果使用函数就会非常麻烦。 7.2 日期数据快速分组 如果想在透视表中查看“每月的销售额汇总”，如果用公式来写的话，需要判断不同的月份、部门，然后再将他们的销售额相加起来，要写一大长串非常复杂的公式。 而使用透视表，拖动日期到行标签（标准日期会自动分组），接着将不同产品拖动到列标签，这样就能实现交叉分析，轻松搞定，总花时长10秒钟。 制作出来的效果也非常棒，节省下来的时候，睡个懒觉真香。 7.3 拆分为单独的Sheet工作表 如果想将透视表中不同的筛选项每次拆分成一个单独的工作表，手动一张一张新建工作表，然后复制数据，效率非常低。 使用透视表就非常简单，只需要将需要拆分的字段，例如：「城市」拖动到「筛选区域」，然后点击「分析」选项卡，选择「显示报表筛选页」，点击「确定」即可。 又是一个GIF动图的时间，完成了这么多的操作，效率非常高。 7.4 统计占比数据 利用传统的函数来统计占比数据的话，需要用SUM函数+绝对定位，而在透视表中就非常简单。在字段上右击「值显示方式」，选择「总计的百分比」即可完成。 7.5 合并相同项目标签 合并相同标签也是一个非常使用的操作，如果使用合并单元格去合并的话，效率非常的低，并且会修改数据的结构，使用透视表就可以非常便捷地完成这个操作。 右击「数据透视表选项」，勾选「合并且居中标签」，点击「确定」即可。 7.6 合并文档多重分析 透视表除了能拆分文档，还能合并文档，对于相同结构的多份文档，如果要使用手动合并的话，效率非常低下。 一次按「Alt+D+P」，选择「多重合并计算」，然后将「每一张表的数据一一添加」，点击「确定」即可。合并前的12份文档： 合并后的效果： 7.7 一键修改数据格式 在透视表中如果按照单元格的方式去修改数据格式，会非常麻烦，其实可以利用透视表自带的「数字格式」功能，轻松搞定。 右击「数字格式」，选择「数值」，勾选「千分位」，小数点后位数为「0」，点击「确定」即可。 7.8 一键规范数据 在使用数据的时候，经常会碰上一些非常不规范的数据，例如一些不能成为字段的数据：一月、二月、三月或者像销售员名字这样的同类字段被做成了字段名。 这类表格如果要进行数据统计分析，会非常麻烦，例如下方这个就是典型的“同类字段”： 处理也非常简单，依次按「Alt+D+P」，选择「多重合并计算区域」，然后「创建单页字段」，点击「确定」，在新建的透视表中，拖动到「右下角的汇总单元格」，双击，就可以看到规范的数据了。 当然在透视表中还有非常多强大的功能，例如“多表的联动”、“透视图”、“数据透视表看板”等等，还有非常多的功能，录制了一个小视频来看看透视表有多强大： 透视表课程学习链接：网易云课堂精品课-Excel就这样用，透视表篇 想要学习Excel的也可以关注下我哦~