用了N年浑不知多种Excel疑难杂症解决方法

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