自切换到包含filterxml和WebS服务的callycul和重新计算后,随机出现随机错误,自切换到365以来

发布于 01-21 08:30 字数 1699 浏览 3 评论 0原文

我有三列的公式,分别提供了纬度,经度和旅行时间到达该目的地,在旅行计划工作簿中,使用filterXML和Web服务与bingmaps API在Excel 2013中完美工作,除了Bingmaps以外,Bingmaps始终低估了旅行时间。约0.8倍(但Bingmaps API比Google Maps免费且更易于集成)。

自从我“升级”到Office 365以来,发生了两件事:

  1. Excel将FilterXML更改为@filterxml,
  2. 每次重新计算它们时,这三列都会产生随机错误。 IE在每个重新计算时,任何给定的单元格都有可能显示一个值误差。这些细胞似乎是随机的,并且随着每个重新计算的变化。
  • 激活错误单元和击中Enter始终会产生正确的结果。
  • 尝试“文本到列”技巧激活列中的每个单元格无法正常工作,并导致所有三个列的新随机值错误,而不仅仅是激活的一个列。
  • 这三列中的哪些细胞产生误差与行无关。即第3列中可能存在错误,但在第1-2列中没有错误。如果1或2中有错误,则第3列中总会出现错误,因为3取决于前面的列。

在下面的图片中,您可以看到一个示例。请注意,在第三列(旅行时间)中,已经设置了错误以显示空白。

这是公式,按顺序:

=IF(OR($G62="",$W62=FALSE,$W62=""),"",@FILTERXML(WEBSERVICE("dev.virtualearth.net/REST/v1/Locations?countryRegion="&$H62&"&adminDistrict="&$I62&"&locality="&$G62&"&maxResults=1&o=xml&key="&BingKey),"//Latitude[1]"))

=IF(OR($G62="",$W62=FALSE,$W62=""),"",@FILTERXML(WEBSERVICE("dev.virtualearth.net/REST/v1/Locations?countryRegion="&H62&"&adminDistrict="&$I62&"&locality="&$G62&"&maxResults=1&o=xml&key="&BingKey),"//Longitude[1]"))

=IF(OR($G62="",$W62=FALSE,$W62=""),NA(),@FILTERXML(WEBSERVICE("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="&LatOrigin&","&LongOrigin&"&destinations="&$J62&","&$K62&"&travelMode=driving&o=xml&key="&BingKey),"//TravelDuration[1]")/1440)

有人可以帮助我弄清楚这里出了什么问题,以及从2013年到365的变化以在先前工作的公式中产生这些错误?

I have three columns with formulas providing the latitude, longitude, and travel time to that destination respectively, using FILTERXML and WEBSERVICE with a Bingmaps API in a travel planning workbook that used to work perfectly in Excel 2013, apart from Bingmaps always underestimating travel times by a factor of around 0.8 (but Bingmaps API is free and easier to integrate than google maps).

Ever since I "upgraded" to office 365, two things have happened:

  1. Excel changed FILTERXML to @FILTERXML
  2. These three columns produce random errors every time they are recalculated. I.e. with every recalculation, there is a chance any given cell will display a VALUE error. Which cells these are seems random and changes with every recalculation.
  • Activating the error cell and hitting enter will always produce the correct result.
  • Trying the "Text to columns" trick to activate each cell in the column doesn't work as expected and results in new random VALUE errors across all three columns, not just the activated one.
  • Which cells in these three columns produce errors is not correlated to their rows. I.e. there might be an error in column 3 but not in columns 1-2. There will always be an error in column 3 if there is an error in either 1 or 2 because 3 is dependent on the preceding columns.

In the picture below you can see an example. Note that in the third column (Travel Time), Errors have been set to display blanks.

enter image description here

Here are the formulas, in order:

=IF(OR($G62="",$W62=FALSE,$W62=""),"",@FILTERXML(WEBSERVICE("dev.virtualearth.net/REST/v1/Locations?countryRegion="&$H62&"&adminDistrict="&$I62&"&locality="&$G62&"&maxResults=1&o=xml&key="&BingKey),"//Latitude[1]"))

=IF(OR($G62="",$W62=FALSE,$W62=""),"",@FILTERXML(WEBSERVICE("dev.virtualearth.net/REST/v1/Locations?countryRegion="&H62&"&adminDistrict="&$I62&"&locality="&$G62&"&maxResults=1&o=xml&key="&BingKey),"//Longitude[1]"))

=IF(OR($G62="",$W62=FALSE,$W62=""),NA(),@FILTERXML(WEBSERVICE("https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="&LatOrigin&","&LongOrigin&"&destinations="&$J62&","&$K62&"&travelMode=driving&o=xml&key="&BingKey),"//TravelDuration[1]")/1440)

Can anyone help me figure out what is going wrong here, and what changed from 2013 to 365 to produce these errors in previously working formulae?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。
列表为空,暂无数据
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文