用了N年浑不知！老司机教你解决Excel疑难杂症

[PConline 技巧]几乎每个人都在自己的简历中写过“精通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到了么！