用了 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 的涵数排错是十分废弃物的。因此你逐渐想起了一个“笨办法”，将要主公式拆分为数个子公式独立排错。自然台本一般是以主角取得成功寻找不正确来烈火雄心3，却非常少有些人注意到，主角实际上早已累趴到排错的道上。

▲根据 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 到么！