ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 13813|回复: 14

[原创] OFFICE 365的这些功能,颠覆了我对Excel的认知①

[复制链接]

TA的精华主题

TA的得分主题

发表于 2018-11-22 09:23 | 显示全部楼层 |阅读模式

《料理鼠王》中美食家柯博对厨神餐厅的评价:昨晚,我有了一次全新的体验,品尝了一道异乎寻常的美味,出自某个想都不敢想的厨师之手。这道美味以及它的创造者改变了我对厨艺的肤浅看法,他们彻底颠覆了我的内心。
前言 “久病成医”的龙表哥,使用Excel多年,在掉进自己挖的坑里多次后,为了不再掉进坑底,总结了一些肤浅经验,这就是后来写到《“偷懒”的技术》第一章与大家分享的那些经验,比如一致性原则、规范性原则、整体性原则、可扩展性原则等等。真正掌握了这些原则,可以让我们少走弯路,少掉坑。但是,随着Excel的发展,微软开发出了一些功能,让这些原则都有逐步失效的“危险”,比如整体性原则是这样的:
同一事项数据放在同一工作表、同一类型的工作表放在同一个工作簿、同一类工作簿放置在同一文件夹。如第三章第三节所举的例子,各公司的报表都放在以月份命名的文件夹下,这样在报表翻新时才能批量修改公式。清单型表格的数据能在同一工作表记录就不要拆分为年或月分表保存。
但是,随着Power Query的应用,合并同一工作簿中的表格,甚至合并同一文件夹下不同工作簿的数据,都轻而易举,因而,整体性原则其"政治正确性”、必要性就没那么强了。今天,安装了Office 365体验版,体验了一下动态数组、新增的一些函数后,这些新功能所形成的冲击,给我的感受,完全可以套用本文开篇所引用的美食家柯博的评论:我有了一次全新的体验,品尝了一道异乎寻常的美味。。。。这道美味以及它的创造者改变了我对厨艺的肤浅看法,他们彻底颠覆了我的内心。本公众号“Excel偷懒的技术”将抽时间陆续给大家介绍Office 365里新增的这些颠覆性的功能。
动态数组之初体验
新手们在看《“偷懒”的技术:打造财务Excel达人》第一章熊孩子被罚抄500遍“我再也不给美女递纸条”那个公式时,很多人都问:为什么结果都在一个单元格显示,如下图:
而不能显示在一列的500行里?
以前我都是这样回复的:
一个单元格的公式,其结果只会在本单元格,不会跑到其他单元格去。
现在好了,微软啪啪啪地打我的脸,已经帮大家实现这个“侵占邻国领土的梦想”了:一个单元格的公式也可以跑到其他单元格去。这就是OFFICE 365新增的动态数组自动溢出功能。
上图的公式就是使用了动态数组的函数。尽管公式只在A1单元格输入,由于其结果有500条,它就自动扩展到500个单元格中。这在之前的Excel版本中,是不可想象的!
什么是动态数组 什么是动态数组,微软官方是这样描述的:
从2018年9月开始, 返回多个值的所有 Excel 公式 (也称为数组) 将结果返回到相邻单元格。此行为称为超过(龙逸凡按:此处英文版为 This behavior is called spilling,因而应该翻译为“溢出”才对)。
有可能返回多个结果的任何公式都可以称为动态数组公式。当前返回多个结果并成功超过的公式可能被称为 "溢出的数组公式"。
我们可以这样理解,Excel会根据公式结果的个数,自动动态调整在相应的单元格显示,动态数组公式能屈能伸,舒之弥四海,卷之不盈怀。比如在以前的版本中我们在一个单元格中输入公式:=ROW(1:5)它可以生成1到5的五个整数,并不会扩展填充到其他单元格。但在OFFICE 365最新体验版中却会自动扩展到5个单元格。
如果选中D1单元格,将公式改为=ROW(1:7)又会自动往下扩展两个单元格,同理,如果改为=ROW(1:3)就会自动收缩为3个单元格。
动态数组有啥用 我们使用最新函数UNIQUE提取A列的唯一值,其结果的范围为D2:D5单元格区域
如果将A7单元格的张三丰,改为龙逍遥,那么,D列的结果区域就自动收缩为D2:D4:
如果将前面图片中A4单元格改为“川普”,D列就往下自动扩展为D2:D6单元格区域:
与之配套,Excel还新增了A1#引用模式。
我们在E2单元格输入条件求和公式,当A列新增了唯一值后,D列会自动扩展,但是E列的公式并没有随之扩展,E6单元格仍然为空白。
要想让其也随之更新,可将公式改为:
=SUMIF($A$2:$A$9,D2#,$B$2:$B$9)
这样,E列的公式就可随D列自动扩展了。

后期将继续介绍OFFICE 365其他一些让人震憾的功能和函数,敬请关注。


TA的精华主题

TA的得分主题

发表于 2018-12-16 12:02 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2018-12-17 15:27 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
本帖最后由 龙逸凡 于 2018-12-17 18:40 编辑
星小星 发表于 2018-12-16 12:02
看不了图片,,,

把文章复制过来时,公众号可能不准外部链接,没有显示。
你可点击此链接查看

TA的精华主题

TA的得分主题

发表于 2018-12-17 18:31 | 显示全部楼层
很期待使用excel2016,不过电脑装不了。还有就是楼主的图片打不开!!!

TA的精华主题

TA的得分主题

发表于 2019-1-20 14:10 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2019-1-25 11:48 | 显示全部楼层
我的电脑上实现不了呀,office 365 pro plus正版订阅,是不是要自己设置?

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-1-27 13:22 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
要参加检验版计划,下载体验版才有这功能

TA的精华主题

TA的得分主题

发表于 2019-8-29 14:42 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
图片看不了

TA的精华主题

TA的得分主题

发表于 2020-5-26 08:17 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
龙逸凡 发表于 2018-12-17 15:27
把文章复制过来时,公众号可能不准外部链接,没有显示。
你可点击此链接查看

链接已经过期,可惜了,看不了动图演示

TA的精华主题

TA的得分主题

发表于 2020-11-29 10:03 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-28 22:54 , Processed in 0.055413 second(s), 8 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表