网易首页 > 数码频道 > 正文

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

0
分享至

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

相关推荐
热点推荐

有一种“铁饭碗”叫年薪20万,待遇不输公务员,目前人才缺口大

苗老师教育观察
2021-05-10 16:10:46

制造业老板的抖音逆袭:3个月涨粉200万,还要打造更多工厂网红

行业快报
2021-05-10 09:46:12

国家生育率暴跌,第一批生了二胎的父母都后悔了!没做好这4件事千万别生

科学育婴手册
2021-05-08 08:13:44

娘家拆迁赔偿360万,一分没给女婿,妻子:老爸来养老,丈夫:滚

小鹿与驴
2021-05-10 18:59:57

女人身上由上到下三道‘沟壑’最为吸引男人

亲子动画乐园
2021-05-11 09:19:16

湖州市为杭州第二国际机场落户谋先机

记录之路
2021-05-11 09:56:25

“你52了还怀孕生孩子,糊涂啊”“我养不动,还有我女儿养呢”

天亮了
2021-05-10 21:33:06

昆凌17岁就被周杰伦追到手,看到她的旧照,你能理解杰伦的心急吗?

者七者八
2021-05-10 12:11:39

林志玲患癌住院,丈夫抱头痛哭?林志玲回应:小手术而已

社会de记忆
2021-05-10 08:03:36

第七次人口普查户均人口2.62人 较第六次人口普查减少0.48人

金融界
2021-05-11 10:25:23

一下子招惹俩军事大国!英航母群被“锁死”?俄军:你们不该来

无线泉州
2021-05-10 21:11:10

金融圈又曝出大瓜,55页PPT曝出,已婚已育的老男人出轨女下属

攀登顶峰
2021-05-11 03:49:41

22场制造37球!梅西踢疯了,冲击9年神迹,巴萨取胜=登上西甲榜首

侃球熊弟
2021-05-11 07:29:40

世卫组织最新通报!印度发现的新冠变异株传染性又增强,还提到中国和国药新冠疫苗……

每日经济新闻
2021-05-11 09:45:56

被称为全欧第一“嫖妓国”,西班牙性交易现状到底什么样?

中新社华舆
2021-05-10 15:37:04

关键时期,亚洲传出两大噩耗,印度已展开内战,缅甸或将爆发战争

百姓关注
2021-05-09 20:13:26

法国电视名嘴喊话:性侵个毛,法国都要完蛋了!

新欧洲
2021-05-11 00:05:49

卢卡申科说到做到 修改总统继任法!儿子受益最大 普京或已认可

肖赞赞
2021-05-11 03:18:01

俺为艺术男友的工作,自己主动上了老板的床,被潜规则一夜

阿东开聊
2021-05-10 22:20:03

公安部最新消息!6月1日起实施!

包头早知道
2021-05-10 18:57:19
2021-05-11 13:49:08

头条要闻

美军临走前将武器打成碎片 阿富汗人怒了:只留了垃圾

头条要闻

美军临走前将武器打成碎片 阿富汗人怒了:只留了垃圾

体育要闻

第182次三双!威少独享NBA历史三双王

娱乐要闻

性感撩人!钟楚曦穿黑裙露香肩美腿

财经要闻

科技要闻

王兴一条饭否,毁了美团千亿市值

汽车要闻

韩国旗舰电动车? 现代将推IONIQ 6轿车

态度原创

亲子
时尚
手机
公开课
军事航空

亲子要闻

小学生因名字“简单”爆红,连名带姓只有2笔,老师:还好有弯钩

某内衣品牌逐渐重口味?竟然选了她

手机要闻

iPhone折叠屏预计将于2023年发布:屏幕大得惊人

公开课

细思极恐!14种被心理操纵的迹象,你中了几个

军事要闻

央视曝光国产新型车载榴弹炮射击细节

×