Excel精英培训网

 找回密码
 注册
数据透视表40+个常用小技巧,让你一次学会!
查看: 4860|回复: 5

[已解决][求助大神]根据特定单元格日期获取该日汇率

[复制链接]
发表于 2017-1-19 16:36 | 显示全部楼层 |阅读模式
本帖最后由 Godinall 于 2017-1-19 18:32 编辑

新人提问, 各种不懂还请多关照

我们有一张表,结构如下,其中的汇率需要根据上个月最后一天的汇率来计算
Date
Deatails
EXCH
RMB
USD
Total Sent

11月-16
Runnning Cost
6.8865
25000
3630.29
3630.29
Base Fee

11600
1684.46
5314.75
Float

7434.25
1079.54
6394.29
Stock items

1356.9
197.04
6591.32





12月-16
Running Cost
6.9321
25000
3630.29
3630.29
Credit Note

-300
-43.56
3586.73
Base Fee

11600
1684.46
5271.18
Float

10149
1473.75
6744.94
Stock items

1356.9
197.04
6941.97

获取汇率的API:h t t p : / / api。fixer。io/2017-01-07?base=USD&symbols=CNY
抱歉我不能发URL链接,请去掉空格替换句号。返回的数据是JSON格式
{"base":"USD","date":"2017-01-06","rates":{"CNY":6.9178}}

我想做到的是,根据日期单元格的内容,自动更新汇率栏的数字。每个月的行数并非完全一致。
但是如果这是障碍的话我可以修改为每月4行。然后空一行起下个月。

我试过用POWER QUERY,可以正常获取到汇率数字,但是无法使用单元格日期作为参数。
VBA不太懂,试着参照论坛前辈发的修改了一个,但是每次需要弹出对话框用户手动输入。

还请各位大神赐教,感谢!

Sub 查询()
    Dim sDate As String
    sDate = Application.InputBox("请输入日期", "输入", Text)
    Cells.Clear
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;h t t p://api。fixer。io/ " & sDate & "?base=USD&symbols=CNY", _
        Destination:=Range("A1"))
        .Name = "我的查询"
        .PreserveFormatting = True
        .WebTables = "2"
        .Refresh
    End With
End Sub




最佳答案
2017-1-19 21:53
  1. =SUBSTITUTE(MID(WEBSERVICE("http://api.fixer.io/"&TEXT(EOMONTH(A1,0),"yyyy-mm-dd")&"?base=USD&symbols=CNY"),50,6),"}","")
复制代码
excel精英培训的微信平台,每天都会发送excel学习教程和资料。扫一扫明天就可以收到新教程
发表于 2017-1-19 18:55 | 显示全部楼层
  1. =SUBSTITUTE(MID(WEBSERVICE("http://api.fixer.io/"&TEXT(VALUE(42737)+ROW(A1),"yyyy-mm-dd")&"?base=USD&symbols=CNY"),50,9),"}","")
复制代码
回复

使用道具 举报

 楼主| 发表于 2017-1-19 21:18 | 显示全部楼层

感谢日天兄的解答,代码简洁,近乎完美
几点问题不太明白,再请教一下:
1. 这段公式的含义我说下我的理解,不对请指正
WEBSERVICE获取数据
TEXT(VALUE(42737)+ROW(A1),"yyyy-mm-dd")提供日期参数并且格式化
MID从第50个字符开始截取,截取9位
SUBSTITUTE替换掉右边截取到的花括号

2. 不明白的地方
TEXT(VALUE(42737)+ROW(A1),"yyyy-mm-dd"),VALUE(42737)实际上是2017-01-01,这个和A1单元格相加是什么意思呢?
另外,不论左侧单元格日期是几号,我都需要当月的最后一天作为参数。比如单元格内饰2017-01-25,公式中需要使用2017-01-31
如果汇率固定式小数点后4位,是否可以精确截取而无须使用SUBSTITUTE函数?

以上,多谢!
再次感谢日天兄的解答。


回复

使用道具 举报

 楼主| 发表于 2017-1-19 21:48 | 显示全部楼层

自己试着修改了下
=SUBSTITUTE(MID(WEBSERVICE("h t t p://api.fixer.io/"&TEXT(DATE(YEAR(A1),MONTH(A1)+1,0),"yyyy-mm-dd")&"?base=USD&symbols=CNY"),50,6),"}","")

SUBSTITUTE目前来看还是需要,因为有的时候汇率最后一位是0,就不显示,只有三位

回复

使用道具 举报

发表于 2017-1-19 21:53 | 显示全部楼层    本楼为最佳答案   
  1. =SUBSTITUTE(MID(WEBSERVICE("http://api.fixer.io/"&TEXT(EOMONTH(A1,0),"yyyy-mm-dd")&"?base=USD&symbols=CNY"),50,6),"}","")
复制代码
回复

使用道具 举报

 楼主| 发表于 2017-1-19 22:19 | 显示全部楼层

果然还是你的代码更加优雅

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-28 17:30 , Processed in 1.440897 second(s), 8 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

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