乔山办公网我们一直在努力
您的位置:乔山办公网 > excel表格制作 > excel2007官方下载-如何使用Excel新插件制作智能查询汇率及计算外币兑换小工具

excel2007官方下载-如何使用Excel新插件制作智能查询汇率及计算外币兑换小工具

作者:乔山办公网日期:

返回目录:excel表格制作

之前出去旅游,剩了一些外币没有花完,趁今天天高云淡,中午出门溜达一趟给换成咱们人民币吧。

第一家ZG银行业务员的左手右手慢动作,不但考验着众人的耐心,而且最后给出的汇率竟然比外汇牌价要低,而柜员无法给出合理解释,果断换另一家PA银行。


没想到PA汇率竟然高一点点,200美元换因为汇率差异,换成人民币差异有11元(够买一套煎饼果子了)。


事情办妥后,登录到中国银行外汇牌价(中国银行_金融市场_外汇牌价)研究汇率差的原因,ZG银行的汇率是664.69,而PA银行的汇率是670.14,前者低于后者5.45。


这是怎么回事

外汇牌价的结汇方式不同造成汇率差异


注意到外汇牌价的表头,是由于结汇方式不同造成的汇率差异。664.69表头是现钞买入价,而670.14对应的是现汇买入价。说白了,一个给现金,一个存账户,存账户性价比高。


于是心血来潮用Excel插件Power Query和简单函数做了一个汇率查询和货币兑换的小工具。


汇率查询和货币兑换小工具


在红圈圈处,选择外币币种,并填上外币金额,使用的汇率及兑换的最终人民币金额就自动计算出来了。


而且由于是用Power Query链接了中国银行外汇牌价,因此只需要用的时候点击刷新,汇率就会自动同步。在我们下一次去银行交易之前,自己就能准确地算出兑换外币的人民币金额了。


小工具怎样制作呢?

分两部分组成:一是将外汇牌价链接到咱们自己的Excel数据表,实现动态更新,二是将连接的汇率提取到换算外币和人民币的表格,实现自动计算。


第一部分:使用Power Query链接外部数据到自己的Excel表

Power query是Excel的一个功能插件,从2010版本开始出现,帮助我们从外部获取数据,并擅长于数据清洗与数据分析。


Excel官方对Power Query连接用途的说明


怎样用它链接外部网页数据呢?


  • 第一步:菜单栏"数据"中,选择"自网站";

Power Query获取网页数据


在弹出的对话框中粘贴外汇牌价网址"http://www.boc.cn/sourcedb/whpj/";


Power Query获取网页数据


  • 第二步: 在弹出的"导航器"中,单击选择"Table 0",外汇牌价出现在右侧窗体中,单击按钮"加载";

使用Power Query加载外部数据


  • 第三步:这样网页上的数据就通过连接,传输到咱们自己的表格中,并且可通过菜单栏"设计"中的"刷新"按钮进行数据同步更新。

Power Query动态更新数据


表哥Tips:


如果需要将链接表格存到自定义的位置,那么找到按钮"加载"旁边有个下拉三角,点开后会看到选项"加载项",在那里可以修改加载位置。


第二部分:使用查询函数VLOOKUP将第一部分的数据链接到工具表
  • 第一步:设置外币币种下拉列表

数据验证制作下拉列表


在"数据"菜单栏中,选择"数据验证",在对话框中选择"序列",数据来源选择,在第一部分导入表格第1列地址即可,点击确认按钮;


链接来源地址生成下拉列表


  • 第二步:使用VLOOKUP查询函数链接汇率

以蓝色单元格C8为例,公式为VLOOKUP($C$4,中国银行外汇牌价!$A:$E,3,FALSE)注意外汇牌价表头与实际外币汇兑的对应关系


外汇牌价表头与实际外币汇兑结算方式的对应关系


表哥Tips:


据观察,通常现汇买卖的方式比现钞买卖性价比更高。因此尽量选择自己已开户银行进行兑换。


  • 第三步:设置人民币金额的公式

四个人民币金额(蓝色单元格)的公式模式一致,公式为:


人民币金额=外币金额×汇率÷100


以上就是刚刚出炉的兑换外币小工具的制作要领,通过Excel插件Power Query将网页数据链接到我们的本地表格,再将其汇率及外币链接到计算表格,以获取最新的兑换金额。


希望表哥的思路能够抛砖引玉。


如果你用EXCEL遇到问题或是心得,


欢迎留言给表哥,


也许下一篇就是为你定制的专题哦!


就到这里吧↖(^ω^)↗撒花


相关阅读

关键词不能为空
极力推荐
  • excel标准差-Excel的一些统计函数

  • 本文简介一些excel的统计函数,包括计算最大值、最小值、平均值、中位值、众数、方差、标准差和四分位值等。-excel标准差

ppt怎么做_excel表格制作_office365_word文档_365办公网