用了N年浑不知!老司机教你解决Excel疑难杂症
2021-05-03 22:17:44  出处:太平洋电脑网  作者:幼儿园里的轩爸 编辑:陈驰     评论(0)点击可以复制本篇文章的标题和链接

几乎每个人都在自己的简历中写过“精通Excel”,可在现实工作中……还是书到用时方恨少!

其实作为办公室里的当家一哥,Excel绝对能算得上是那个最容易上手却最不容易精通的一位。

那么在你的日常工作中,是否也遇到过一些书本里没讲透的小难题?如果答案是肯定的话,那么下面这篇文章就绝对不能错过了!

1. 隐藏数据后,图表没了!

如果你的表格加入了很多中间数据,并且用这些中间数据做成了图表,那么一定会遇到下面这个尴尬。将数据表隐藏后(右击列标→“隐藏”),图表也随之清空了。遇到这种情况,我们又该如何处理呢?

用了N年浑不知!老司机教你解决Excel疑难杂症
数据被隐藏后,图表也会随之清空

解决方法:

1) 右击图表→“选择数据”,点击弹出面板左下角的“隐藏的单元格和空单元格”;

2) 勾选“显示隐藏行列中的数据”前面的复选框,确定后。再次隐藏数据列就不会影响到图表的正常显示了;

用了N年浑不知!老司机教你解决Excel疑难杂症
勾选“显示隐藏行列中的数据”

2. 工作表保护后,控件没法点了!

有时想在老板面前显摆一回,费了九牛二虎之力,用控件搞了个交互报表。当你兴冲冲地将报图摆在老板面前时,尴尬的事发生了,原本没有问题的控件不知为啥就是点不了。

结果显摆没显成,反而弄了个欺骗上司的罪名,那么问题到底出在哪儿呢?

用了N年浑不知!老司机教你解决Excel疑难杂症
为啥控件就是点不了?

出于表结构保护等方面的考虑,我们通常会对最终成表执行一次工作表保护。

正是这一步保护操作,才最终导致了控件失效。当然并不是说带有控件的报表就无法使用工作表保护了,想让这两项功能并存其实非常简单,那就是右击控件链接单元格,取消“设置单元格格式”→“保护”→“锁定”前面的复选框。

处理完成后,再次执行工作表保护就不会妨碍到控件的正常运行了。

用了N年浑不知!老司机教你解决Excel疑难杂症
取消控件链接单元格的“锁定”状态即可

3. VLOOKUP怎么不能反着查?

VLOOKUP算是日常点击率较高的一组函数了,这个函数什么都好,就是没法倒着查。于是很多小伙伴一遇到拿姓名查工号的操作,就开始头疼,这个又该如何处理呢?

用了N年浑不知!老司机教你解决Excel疑难杂症
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的正常运行。

用了N年浑不知!老司机教你解决Excel疑难杂症
通过添加一个IF数组函数临时调换B列与C列,保证VLOOKUP正常运行

此外,小编平时用得比较多的还有一个LOOKUP函数,同样也能完成上述操作,而且比VLOOKUP更简洁(=LOOKUP(R8,C:C,B:B)),感兴趣的小伙伴不妨一试!

4. F9这个键还有这个用

Excel中有很多快捷键,比方说F9键。通常大家所了解的F9是全表重算(比如随机生成演示数据),但它的另一个用途却很少有人知道。举个例子,比方说你建立了一个超级超级复杂的公式,结果……公式出错啦!

当然我们知道Excel的函数排错是非常垃圾的。于是你开始想到了一个“笨办法”,即将主公式拆分成若干个子公式单独排错。

当然剧本通常是以主人公成功找到错误来皆大欢喜,却很少有人注意到,主人公其实早就累趴在排错的路上。

用了N年浑不知!老司机教你解决Excel疑难杂症
通过F9键单独计算选中区域

好吧,还是上面这个剧本,我们换一种解法。即将你感觉有问题的公式先部分选中,然后按下F9键,单独计算所选区域的结果。

有问题处理它,没问题继续下一个,直到把问题完全解决。

其实这条操作与上一条本质上没有区别,却省去了很多拆分子公式的麻烦。而这恰恰是F9键的另一项功能——单独计算选中区域结果。

5. 设好的格式无法跟随记录增长怎么办?

当你辛辛苦苦地为表格制作好边框、色条,却发现新记录无法自动继承这些格式,是不是想si的心都有了。不光是你,很多使用Excel制作流水表的童鞋,其实都遭遇过类似的尴尬。

用了N年浑不知!老司机教你解决Excel疑难杂症
新记录不会自动继承上一行格式

怎么解决呢?很简单,首先选中你已经设好格式的表格域,按下Ctrl+T键,将其转换成“超级表”。

然后点击“表格工具”→“表格样式”,将当前的表格样式设置为“无”(即不使用超级表默认样式)。

接下来,取消“表格样式”里的“筛选按钮”(当然如果需要也可以保留)。

这时你会发现,新记录已经可以自动继承前面设好的表格样式了。

用了N年浑不知!老司机教你解决Excel疑难杂症
通过“超级表”实现格式自动继承

6. 哎!上图里的“色条”怎么来的?

细心的小伙伴或许已经发现,在上面这组演示图里,我的表格似乎可以隔行换色。而且无论怎么对表格进行添加删除记录,都不会影响到色条的排列。

那么,这个又是如何实现的?

用了N年浑不知!老司机教你解决Excel疑难杂症
色条可以不受添行删行影响

这项功能的实现,同样也有两种方法。第一种依旧是使用“超级表”功能,与普通表相比,超级表本身就自带表格拓展功能,当一条记录被新建到超级表的最后一组行列时,就会自动扩展原表格域。

与此同时,新的行列也将继承原表格的格式。

第二种方法是使用条件公式,首先选中要处理的区域,点击“条件格式”→“新建规则”→“使用公式确定要设置格式的单元格”,然后在公式框内输入“=MOD(ROW(),2)=0”,并设置一组背景色。

这条公式的作用,是通过取余函数对行号计算,从而产生隔一跳一的效果,然后为符合条件的行(即每隔一行)刷上设好的背景色,同样也可以实现上述效果。

用了N年浑不知!老司机教你解决Excel疑难杂症
通过条件格式+公式的方式,实现隔行换色

7. 如何快速制作一个模板?

现在的表格越来越复杂,很多都夹杂了大量的公式。那么问题来了,如何快速生成一组模板,又不把公式删除掉呢?

首先选中数据区域,点击“开始”→“编辑”→“查找和选择”→“定位条件”,接下来点击“常量”→“确定”,按下键盘上的Del键。

这时你会发现,表格中的所有常量都被删除了,而公式却没有受到影响,于是一张仅带有公式的空白模板表就这样出炉了!

用了N年浑不知!老司机教你解决Excel疑难杂症
借助“定位条件”快速删除常量制作模板

8. 数据有效性里的空值太多怎么办?

制作大型表格时,常常会利用“数据有效性”来统一数据。不过这也会导致另一个问题,即先期为数据预留的空间太多,就会在下拉列表产生大量空值。那么这个问题又该如何规避呢?

用了N年浑不知!老司机教你解决Excel疑难杂症
默认制作的下拉菜单空值很多,不便于操作

要想解决这个问题,还是要利用一组函数。以上图为例,依旧进入“数据”→“数据验证”→“序列”栏,然后在“来源”框中填入公式“=OFFSET($O$6,,,COUNTA($O$6:$O$19))”。

它的意思就是,首先通过COUNTA函数求出当前数据源的有效记录数(即“主讲教师”列),再通过OFFSET函数确定好最终的提取范围,这样我们便得到了一组没有空值的下拉列表。

用了N年浑不知!老司机教你解决Excel疑难杂症
通过修改序列来源,去除选单中多余空值

9. 单元格左上角三角很碍眼怎么弄?

有时我们会在某些单元格的左上角看到一些小三角,特别是在一些格式化好的文档中,这些小三角会显得特别碍眼。其实这是Excel的自动查错功能,说白了就是和Word里的“波浪线”一个道理。

通常小三角往往意味着该单元格存在问题(比如公式不正常,数据类型不正确等等),但如果这就是我们故意为之(比如使用文本格式存储身份证号等),那就没必要让它提示了。

用了N年浑不知!老司机教你解决Excel疑难杂症
你是不是也经常看到这种“碍眼”的小三角

解决方法有两种:一是点击三角左侧的提示符,勾选“忽略错误”,但这种方法只能对连续单元格有效,如果表格中要处理的单元格很多时,效率就很低了。

还有一种方法是直接关闭错误检查功能,点击三角左侧的提示符,在选单里选择“错误检查选项”,接下来取消“允许后台错误检查”前面的复选框,这样小三角也将不再提示。

用了N年浑不知!老司机教你解决Excel疑难杂症
关闭“允许后台错误检查”可以一劳永逸解决掉小三角

10. 怎样禁止录入重复值

如果你制作了一个流水表,又不希望录入时出现重复记录,那么就可以借助“数据验证”搞定它。

具体方法是:首先选中要限制的数据列,点击“数据”→“数据工具”→“数据验证”。

然后将验证条件修改为“自定义”,并在公式栏内输入“=COUNTIF(B:B,B1)=1”。

这里公式的含义是在B:B范围内,匹配与B1单元格内容相同的记录并计数,一旦发现有重复(即COUNTIF值>1),便中止录入,具体效果如下。

用了N年浑不知!老司机教你解决Excel疑难杂症
设置好后就不能输入重复数值了

除了完全禁止重复数值录入外,我们也可以将出错禁止形式修改为“警告”,来实现仅提示不禁止的效果。

用了N年浑不知!老司机教你解决Excel疑难杂症
修改这里,可以仅提示不禁止

写在最后

Excel中隐藏的秘密很多,有些不但书里没写,甚至连帮助文件中都查不到。当然就像千千万万个Excel命令一样,这些“隐藏版”小技巧同样也能在关键时候帮上大忙。

好了,这就是本期要和大家分享的几组Excel小技巧,你都Get到了么!

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

- THE END -

#Excel#办公

原文链接:太平洋电脑网 责任编辑:陈驰

文章价值打分
当前文章打分0 分,共有0人打分
文章观点支持

+0
+0

  • 关注我们

驱动之家 关注驱动之家 微信公众号,每日及时查 看最新手机、电脑、汽车、智能硬件信息
  • 微博

    微博:快科技官方

    快科技(原驱动之家)官方微博
  • 今日头条

    今日头条:快科技

    带来硬件软件、手机数码最快资讯!
  • 抖音

    抖音:快科技mydrivers

    科技快讯、手机开箱、产品体验、应用推荐...
网站地图 太阳城申博代理总公司 www.jbp2222.com qq彩票可靠吗
太阳城申博娱乐网站 申博娱乐软件下载官网 菲律宾申博开户 申博138娱乐正网
舟山棋牌游戏 风行游戏征战四方 征途在线直营网登入 申博在线开户登入
广州大型捕鱼机厂家 澳门威尼斯人官网 澳门威尼斯人网站威尼斯人 凯发娱乐备用网址
淘金盈网站怎么登入不了 葡京城开户网址 15777.com 淘金盈直营网
107SUN.COM 66sbmsc.com XSB178.COM S618R.COM 983XTD.COM
1116118.COM S618Z.COM 1113898.COM 132sun.com 117PT.COM
8YQS.COM 188BBIN.COM 976SUN.COM DC761.COM 1113898.COM
518jbs.com 899BBIN.COM 115sj.com 718jbs.com 156tt.com