老司机教你解决Excel疑难杂症 分享的几组Excel小技巧 你都Get到了么!
几乎每个人都在自己的简历中写过“精通Excel”,可在现实工作中……还是书到用时方恨少!
其实作为办公室里的当家一哥,Excel绝对能算得上是那个最容易上手却最不容易精通的一位。
那么在你的日常工作中,是否也遇到过一些书本里没讲透的小难题?如果答案是肯定的话,那么下面这篇文章就绝对不能错过了!
1. 隐藏数据后,图表没了!
如果你的表格加入了很多中间数据,并且用这些中间数据做成了图表,那么一定会遇到下面这个尴尬。将数据表隐藏后(右击列标→“隐藏”),图表也随之清空了。遇到这种情况,我们又该如何处理呢?
解决方法:
1) 右击图表→“选择数据”,点击弹出面板左下角的“隐藏的单元格和空单元格”;
2) 勾选“显示隐藏行列中的数据”前面的复选框,确定后。再次隐藏数据列就不会影响到图表的正常显示了;
2. 工作表保护后,控件没法点了!
有时想在老板面前显摆一回,费了九牛二虎之力,用控件搞了个交互报表。当你兴冲冲地将报图摆在老板面前时,尴尬的事发生了,原本没有问题的控件不知为啥就是点不了。
结果显摆没显成,反而弄了个欺骗上司的罪名,那么问题到底出在哪儿呢?
出于表结构保护等方面的考虑,我们通常会对最终成表执行一次工作表保护。
正是这一步保护操作,才最终导致了控件失效。当然并不是说带有控件的报表就无法使用工作表保护了,想让这两项功能并存其实非常简单,那就是右击控件链接单元格,取消“设置单元格格式”→“保护”→“锁定”前面的复选框。
处理完成后,再次执行工作表保护就不会妨碍到控件的正常运行了。
3. 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的正常运行。
此外,小编平时用得比较多的还有一个LOOKUP函数,同样也能完成上述操作,而且比VLOOKUP更简洁(=LOOKUP(R8,C:C,B:B)),感兴趣的小伙伴不妨一试!
4. F9这个键还有这个用
Excel中有很多快捷键,比方说F9键。通常大家所了解的F9是全表重算(比如随机生成演示数据),但它的另一个用途却很少有人知道。举个例子,比方说你建立了一个超级超级复杂的公式,结果……公式出错啦!
当然我们知道Excel的函数排错是非常垃圾的。于是你开始想到了一个“笨办法”,即将主公式拆分成若干个子公式单独排错。
当然剧本通常是以主人公成功找到错误来皆大欢喜,却很少有人注意到,主人公其实早就累趴在排错的路上。
好吧,还是上面这个剧本,我们换一种解法。即将你感觉有问题的公式先部分选中,然后按下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到了么!
标签: Excel
相关推荐:
最新新闻:
- 全球球精选!闲鱼卖二手,破财难消灾
- 《圣斗士星矢》真人电影先导预告和海报 过于霸气:环球即时
- FS社员工称工资不高有加班 但工作像玩黑魂有成就感:今日关注
- 热门:尹锡悦准备“量身定制”福利以吸引特斯拉超级工厂入驻韩国
- TI后更新来了 《DOTA2》7.32d平衡性更新上线:今日聚焦
- 热点评!《鬼怪》编剧新作 宋慧乔与前夫宋仲基“复仇之战”
- 分不清“事实与观点”的讨论,只是在浪费时间
- 出厂即6GHz i9-13900KS上架了 价格很贵
- 天天要闻:4款N卡停产 以后新卡只能买30系和40系了
- 环球动态:NUC玩跨界!英特尔公版NUC X15游戏本预售5999元
- 冠脉支架集采提价超25%,医药行业预期改变了吗?| 见智研究-全球焦点
- 《孤岛惊魂6》开启免费体验12月6日推出异界DLC
- 沉寂十年之后,大宗商品对冲基金强势归来
- 全球新消息丨类银河恶魔城射击游戏《魔骑少女》现已正式上市
- 动态焦点:华硕ROG幻16 2023款曝光 或将搭载13代酷睿和RTX 40显卡
- 损失严重!苹果iPhone产量极具下滑:预计减少5%至10%:环球快看点
- 每日热门:威尔士0-3不敌英格兰!英格兰队世界杯总进球数破百
- 补贴十三年,肥了谁?废了谁?|焦点精选
- 今日播报!高性价比推荐!一线大牌15.6英寸轻薄本3499元秒
- 特斯拉开始从它降价!每千瓦时降约0.35元
- 199元!网易UU加速盒2发布:支持所有游戏主机、VR
- SE公布《浪漫沙加:吟游诗人之歌》8名主角介绍预告:每日速读
- 《街头霸王6》日本相扑E·本田场景音乐欣赏-短讯
- 焦点资讯:任天堂公布《超级马里奥兄弟大电影》第二部预告
- 开发商表示《火星孤征》比《飞向月球》长得多
- 12月PS+会免游戏阵容爆料:《质量效应传奇版》等_观速讯
- 《重生边缘》国际版增加主机版 PC公测12/9-1/15进行:动态
- 天天百事通!《战神:诸神黄昏》幕后系列第七集 展示游戏电影如何构建
- 《最终幻想7:核心危机 再融合》发行预告片公布 全球快资讯
- 时讯:生活模拟游戏《花园小径》将于2023年春季推出
- 精选!世界杯“大嫂团” 内马尔的巴西模特女友热情火辣
- 快讯:影驰全家桶:冬日装机,纯白平价优选,颜值实力兼具
- 每日观察!AMD锐龙7000 3D版未发布即被封神,明年必买
- RGB控制一键超频 耕升GW-SOUL软件详解
- 谷歌手机广告造假 连带广告公司被FCC起诉
- 保护用户不力!Meta欧洲被罚2.65亿欧元_当前要闻
- 《如龙维新极》冈田以藏介绍:沉默寡言的危险剑客
- 天天快资讯:法拉利首款SUV停止接单!已经供不应求了
- 《传送门RTX》12月8日发售 DLSS 3特性展示_天天快看
- 一边办世界杯,一边狂“卖气”!卡塔尔与德国达成一份15年天然气供应协议
- 当前视讯!韩国罢工潮愈演愈烈!与工会谈崩后,政府下达强制开工令
- 出险房企等来了红包_全球快看点
- 【独家】传闻:《使命召唤》以幽灵为主角的内容正在开发
- 快播:苹果AirPods Pro 2耳机-500 只要1399
- 天天通讯!小米12 Pro降到2599 小米12s Ultra也只要4999
- 上财校长称40万博士生月收入仅3000元 建议提高博士生的收入_微动态
- C919大型客机获颁生产许可证_天天速读
- 世界快看点丨第二春?《大镖客2》Steam在线人数超6.6万创下新高
- 天文学家痛斥:美国一卫星比天上99.8%的星星都亮
- 《木卫四协议》前期2个小时视频被泄露
- PS4主机9岁了!销量近1.2亿 生命周期已基本至终点_天天热闻
- 特斯拉诉品玩名誉侵权案胜诉 后者不服将上诉:天天滚动
- 世界滚动:8核i7+RTX 3070显卡:笔记本准系统仅5999元
- 观点:1TB固态SSD限时284元降价
- 竞猜赢大礼:2022世界杯竞猜活动上线,惊喜大礼等你来!|即时
- 北通宙斯2光轴精英手柄正式亮相 五大最新功能进化线索全公开
- 全球观热点:小米推出首款迷你主机 12月1日发布
- 掌控4610亿美元资产,卡塔尔太后有多牛?:全球观点
- 焦点要闻:买飞机、建机场、收并购,中国物流如何“抄作业”?
- 得罪女性还能挣钱,婚恋博主的另类生意
- 大学生打电竞比赛聊考研 网友:学习好 游戏也打得好
- 今日讯!世界航空界首次:劳斯莱斯成功测试氢动力喷气发动机
- 小米首款台式机来了!小米迷你主机与小米13同步发布
- 火山引擎智能外呼上线抖店,双十一助力商家提升营销转化:全球报资讯
- 天天观天下!小米13跑《原神》30分钟测试:跑满帧 43度不发烫
- 网友曝小米13真机渲染图 你感觉如何?:世界新动态
- 特斯拉Model 3新款将开售:降到20万以内
- 【世界速看料】小米无线鼠标减价 史低价30元
- 发生了什么?上证50暴涨4%,离岸人民币狂拉800点,恒生科技飙升逾7%,债市延续大跌_最新资讯
- 高盛:对冲基金正在大规模做空能源股 环球微动态
- 印度首枚私人太空火箭发射升空,只有6米长、半吨重
- 《怪物猎人崛起:曙光》分享设计草图原画 凶恶怨虎龙霸气外露:环球热门
- LOL心之钢即将迎来削弱:伤害加成从10%下调至5%
- 加密货币贷款公司BlockFi 申请破产保护-当前速看
- 全球今头条!支付宝上线生僻字键盘 可方便6000万人