几乎每个人都在自己的简历中写过“精通 Excel”，可在现实工作中…… 还是书到用时方恨少！其实作为办公室里的当家一哥，Excel 绝对能算得上是那个最容易上手却最不容易精通的一位。那么在你的日常工作中，是否也遇到过一些书本里没讲透的小难题？如果答案是肯定的话，那么下面这篇文章就绝对不能错过了！ 如果你的表格加入了很多中间数据，并且用这些中间数据做成了图表，那么一定会遇到下面这个尴尬。将数据表隐藏后（右击列标→“隐藏”），图表也随之清空了。遇到这种情况，我们又该如何处理呢？ ▲数据被隐藏后，图表也会随之清空 解决方法： 1） 右击图表→“选择数据”，点击弹出面板左下角的“隐藏的单元格和空单元格”； 2） 勾选“显示隐藏行列中的数据”前面的复选框，确定后。再次隐藏数据列就不会影响到图表的正常显示了； ▲勾选“显示隐藏行列中的数据” 有时想在老板面前显摆一回，费了九牛二虎之力，用控件搞了个交互报表。当你兴冲冲地将报图摆在老板面前时，尴尬的事发生了，原本没有问题的控件不知为啥就是点不了。结果显摆没显成，反而弄了个欺骗上司的罪名，那么问题到底出在哪儿呢？ ▲为啥控件就是点不了？ 出于表结构保护等方面的考虑，我们通常会对最终成表执行一次工作表保护。正是这一步保护操作，才最终导致了控件失效。当然并不是说带有控件的报表就无法使用工作表保护了，想让这两项功能并存其实非常简单，那就是右击控件链接单元格，取消“设置单元格格式”→“保护”→“锁定”前面的复选框。处理完成后，再次执行工作表保护就不会妨碍到控件的正常运行了。 ▲取消控件链接单元格的“锁定”状态即可 VLOOKUP 算是日常点击率较高的一组函数了，这个函数什么都好，就是没法倒着查。于是很多小伙伴一遇到拿姓名查工号的操作，就开始头疼，这个又该如何处理呢？ ▲VLOOKUP 无法执行反向查询 想要解决这个问题，我们可以先用 IF 函数中转一下，即通过 IF 函数的数组功能，将 VLOOKUP 的查找域调换一下，变相解决这个问题。具体方法就是，将公式修改为“=VLOOKUP (R8,IF ({1,0},C:C,B:B),2,FALSE)”。这里“IF ({1,0},C:C,B:B)”所产生的作用，就是将 B 列与 C 列临时调换一下，以保证 VLOOKUP 的正常运行。 ▲通过添加一个 IF 数组函数临时调换 B 列与 C 列，保证 VLOOKUP 正常运行 此外，小编平时用得比较多的还有一个 LOOKUP 函数，同样也能完成上述操作，而且比 VLOOKUP 更简洁（=LOOKUP (R8,C:C,B:B)），感兴趣的小伙伴不妨一试！ Excel 中有很多快捷键，比方说 F9 键。通常大家所了解的 F9 是全表重算（比如随机生成演示数据），但它的另一个用途却很少有人知道。举个例子，比方说你建立了一个超级超级复杂的公式，结果…… 公式出错啦！当然我们知道 Excel 的函数排错是非常垃圾的。于是你开始想到了一个“笨办法”，即将主公式拆分成若干个子公式单独排错。当然剧本通常是以主人公成功找到错误来皆大欢喜，却很少有人注意到，主人公其实早就累趴在排错的路上。 ▲通过 F9 键单独计算选中区域 好吧，还是上面这个剧本，我们换一种解法。即将你感觉有问题的公式先部分选中，然后按下 F9 键，单独计算所选区域的结果。有问题处理它，没问题继续下一个，直到把问题完全解决。其实这条操作与上一条本质上没有区别，却省去了很多拆分子公式的麻烦。而这恰恰是 F9 键的另一项功能 —— 单独计算选中区域结果。 当你辛辛苦苦地为表格制作好边框、色条，却发现新记录无法自动继承这些格式，是不是想 si 的心都有了。不光是你，很多使用 Excel 制作流水表的童鞋，其实都遭遇过类似的尴尬。 ▲新记录不会自动继承上一行格式 怎么解决呢？很简单，首先选中你已经设好格式的表格域，按下 Ctrl+T 键，将其转换成“超级表”。然后点击“表格工具”→“表格样式”，将当前的表格样式设置为“无”（即不使用超级表默认样式）。接下来，取消“表格样式”里的“筛选按钮”（当然如果需要也可以保留）。这时你会发现，新记录已经可以自动继承前面设好的表格样式了。 ▲通过“超级表”实现格式自动继承 细心的小伙伴或许已经发现，在上面这组演示图里，我的表格似乎可以隔行换色。而且无论怎么对表格进行添加删除记录，都不会影响到色条的排列。那么，这个又是如何实现的？ ▲色条可以不受添行删行影响 这项功能的实现，同样也有两种方法。第一种依旧是使用“超级表”功能，与普通表相比，超级表本身就自带表格拓展功能，当一条记录被新建到超级表的最后一组行列时，就会自动扩展原表格域。与此同时，新的行列也将继承原表格的格式。 第二种方法是使用条件公式，首先选中要处理的区域，点击“条件格式”→“新建规则”→“使用公式确定要设置格式的单元格”，然后在公式框内输入“=MOD (ROW (),2)=0”，并设置一组背景色。这条公式的作用，是通过取余函数对行号计算，从而产生隔一跳一的效果，然后为符合条件的行（即每隔一行）刷上设好的背景色，同样也可以实现上述效果。 ▲通过条件格式 + 公式的方式，实现隔行换色 现在的表格越来越复杂，很多都夹杂了大量的公式。那么问题来了，如何快速生成一组模板，又不把公式删除掉呢？ 首先选中数据区域，点击“开始”→“编辑”→“查找和选择”→“定位条件”，接下来点击“常量”→“确定”，按下键盘上的 Del 键。这时你会发现，表格中的所有常量都被删除了，而公式却没有受到影响，于是一张仅带有公式的空白模板表就这样出炉了！ ▲借助“定位条件”快速删除常量制作模板 制作大型表格时，常常会利用“数据有效性”来统一数据。不过这也会导致另一个问题，即先期为数据预留的空间太多，就会在下拉列表产生大量空值。那么这个问题又该如何规避呢？ ▲默认制作的下拉菜单空值很多，不便于操作 要想解决这个问题，还是要利用一组函数。以上图为例，依旧进入“数据”→“数据验证”→“序列”栏，然后在“来源”框中填入公式“=OFFSET ($O$6,,,COUNTA ($O$6:$O$19))”。它的意思就是，首先通过 COUNTA 函数求出当前数据源的有效记录数（即“主讲教师”列），再通过 OFFSET 函数确定好最终的提取范围，这样我们便得到了一组没有空值的下拉列表。 ▲通过修改序列来源，去除选单中多余空值 有时我们会在某些单元格的左上角看到一些小三角，特别是在一些格式化好的文档中，这些小三角会显得特别碍眼。其实这是 Excel 的自动查错功能，说白了就是和 Word 里的“波浪线”一个道理。通常小三角往往意味着该单元格存在问题（比如公式不正常，数据类型不正确等等），但如果这就是我们故意为之（比如使用文本格式存储身份证号等），那就没必要让它提示了。 ▲你是不是也经常看到这种“碍眼”的小三角 解决方法有两种：一是点击三角左侧的提示符，勾选“忽略错误”，但这种方法只能对连续单元格有效，如果表格中要处理的单元格很多时，效率就很低了。还有一种方法是直接关闭错误检查功能，点击三角左侧的提示符，在选单里选择“错误检查选项”，接下来取消“允许后台错误检查”前面的复选框，这样小三角也将不再提示。 ▲关闭“允许后台错误检查”可以一劳永逸解决掉小三角 如果你制作了一个流水表，又不希望录入时出现重复记录，那么就可以借助“数据验证”搞定它。具体方法是：首先选中要限制的数据列，点击“数据”→“数据工具”→“数据验证”。然后将验证条件修改为“自定义”，并在公式栏内输入“=COUNTIF (B:B,B1)=1”。这里公式的含义是在 B:B 范围内，匹配与 B1 单元格内容相同的记录并计数，一旦发现有重复（即 COUNTIF 值 > 1），便中止录入，具体效果如下。 ▲设置好后就不能输入重复数值了 除了完全禁止重复数值录入外，我们也可以将出错禁止形式修改为“警告”，来实现仅提示不禁止的效果。 ▲修改这里，可以仅提示不禁止 Excel 中隐藏的秘密很多，有些不但书里没写，甚至连帮助文件中都查不到。当然就像千千万万个 Excel 命令一样，这些“隐藏版”小技巧同样也能在关键时候帮上大忙。好了，这就是本期要和大家分享的几组 Excel 小技巧，你都 Get 到了么！ 1. 隐藏数据后，图表没了！
2. 工作表保护后，控件没法点了！
3. VLOOKUP 怎么不能反着查？
4. F9 这个键还有这个用
5. 设好的格式无法跟随记录增长怎么办？
6. 哎！上图里的“色条”怎么来的？
7. 如何快速制作一个模板？
8. 数据有效性里的空值太多怎么办？
9. 单元格左上角三角很碍眼怎么弄？
10. 怎样禁止录入重复值
写在最后
本网页所有视频内容由 imoviebox边看边下-网页视频下载, iurlBox网页地址收藏管理器 下载并得到。
ImovieBox网页视频下载器 下载地址: ImovieBox网页视频下载器-最新版本下载
本文章由: imapbox邮箱云存储,邮箱网盘,ImageBox 图片批量下载器,网页图片批量下载专家,网页图片批量下载器,获取到文章图片,imoviebox网页视频批量下载器,下载视频内容,为您提供.
阅读和此文章类似的: 程序员专区
官方软件产品操作指南 (170)
