前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >利用Excel的名称定义来使得计算公式更有逻辑化,自动化

利用Excel的名称定义来使得计算公式更有逻辑化,自动化

作者头像
逍遥之
发布2020-03-24 16:08:11
1.5K0
发布2020-03-24 16:08:11
举报
文章被收录于专栏:数据技巧

我们先来看下文档。

我们可以观察到当我们点击总海运费,也就是F8单元格的时候,在公示栏里显示的不是我们平时所看到的引用单元格的计算,而是是目的港费用+本地费,这个代表的可能就是使用了名称命名来进行计算。

我们通过公式中的追踪单元格,我们可以看到指向了非常多的单元格,说明虽然公式只有简单的目的港+本地费用,但是实际上在内部具有一整套计算逻辑。

我们打开名称管理器,可以看到既有表的内容引用,又有区域内容的引用,还有单元格的内容引用。同时在名称管理器计算中同时引用了其他区域数据名称。

例如公式:

代码语言:javascript
复制
目的港费用=
(SUM(目的港收费[提单])+ MAX(体积,实重/RT比)*SUM(目的港收费[RT]))
*欧元汇率-120*美元汇率*RT重

在目的港费用这个计算公式中,我们看到目的港收费[提单],这个是代表超级表的数据名称,引用的是提单这一列,但是不含提单这个标题,只引用数据部分。

我们来看下这部分数据的引用。

体积,实重引用的是单元格数据。

而RT比这个是什么情况呢?1:1000这样的格式也能被直接用于运算吗?

那我们来看下这个RT比的1:1000到底是个什么情况,实际上这个是数字显示的格式问题。

我们通过自定义格式看到,实际数字就是1000,只不过被设置成了1:1000的显示效果。

此外这些kg,CBM等也都是通过自定义格式来实现的。这样既能保证可视化的需求,也能保证数据运算的正常进行,而不用在文本格式的基础上进行转换和提取。

接着我们继续看,汇率这部分。汇率也是作为名称引用单元格,但是这个表格是通过网络实时抓取的汇率数据,此表格是通过Power Query进行网抓后整理而得。

我们抓取的就是这个页面上最下面部分的表格数据。只要我们通过刷新表格既能获取最新的实时汇率数据,以便于我们可以相对合理的计算成表。

在这里通过Power Query获取数据的方法也相对比较简单。

新建源,从web获取数据即可,我们可以填写网址后可以获取的数据有哪些,其中只有Table0的数据是符合我们需求的,也是我们所需要的。

最后通过数据整理,只保留我们所需要的最新汇率即可。

我们看到实际上整个数据获取及清洗,非常的简单,通过向导只需要填写上网址,随后找到抓取表格数据并确认,最后删除其他列。而这种简单的操作,可以使得下次所需要这些数据的时候,直接通过刷新的方式一键获取。

在这份文件中,我们运用了表格名称,自定义名称,Power Query网抓,单元格自定义格式,下拉式列表框等Excel技巧,从而使得在计算单元格显示的公式从业务逻辑上进行简化展示。

如果觉得有帮助,那麻烦您进行转发,让更多的人能够提高自身的工作效率。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-10-25,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 数据技巧 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档