問(wèn)題描述

參考格式：?

[{"from": "四川","to": "浙江","fromLat": 30.65089,"fromLng": 104.07572,"toLat": 30.26667,"toLng": 120.2},{"from": "北京","to": "浙江","fromLat": 39.91667,"fromLng": 116.41667,"toLat": 30.26667,"toLng": 120.2},{"from": "遼寧","to": "浙江","fromLat": 41.8,"fromLng": 123.38333,"toLat": 30.26667,"toLng": 120.2} ]

數(shù)據(jù)：

中國(guó)各大城市經(jīng)緯度?

中國(guó)各省省會(huì)經(jīng)緯度

解決方案

新建EXCEL

使用數(shù)據(jù)分列、替換等功能處理數(shù)據(jù)

?

使用公式生成JSON

="{ ""from"": """&A1&""", ""to"": ""浙江"", ""fromLat"": "&C1&", ""fromLng"": "&B1&", ""toLat"": 30.26667, ""toLng"": 120.2 }"

合并

="["&TEXTJOIN(",",TRUE,D1:D34)&"]"

?

運(yùn)行結(jié)果

[{"from": "山東","to": "浙江","fromLat": 36.675807,"fromLng": 117.000923,"toLat": 30.26667,"toLng": 120.2},{"from": "河北","to": "浙江","fromLat": 38.03333,"fromLng": 115.48333,"toLat": 30.26667,"toLng": 120.2},{"from": "吉林","to": "浙江","fromLat": 43.88333,"fromLng": 125.35,"toLat": 30.26667,"toLng": 120.2},{"from": "黑龍江","to": "浙江","fromLat": 47.75,"fromLng": 127.63333,"toLat": 30.26667,"toLng": 120.2},{"from": "遼寧","to": "浙江","fromLat": 41.8,"fromLng": 123.38333,"toLat": 30.26667,"toLng": 120.2},{"from": "內(nèi)蒙古","to": "浙江","fromLat": 41.818311,"fromLng": 111.670801,"toLat": 30.26667,"toLng": 120.2},{"from": "新疆","to": "浙江","fromLat": 43.76667,"fromLng": 87.68333,"toLat": 30.26667,"toLng": 120.2},{"from": "甘肅","to": "浙江","fromLat": 36.03333,"fromLng": 103.73333,"toLat": 30.26667,"toLng": 120.2},{"from": "寧夏","to": "浙江","fromLat": 37.46667,"fromLng": 106.26667,"toLat": 30.26667,"toLng": 120.2},{"from": "山西","to": "浙江","fromLat": 37.86667,"fromLng": 112.53333,"toLat": 30.26667,"toLng": 120.2},{"from": "陜西","to": "浙江","fromLat": 34.26667,"fromLng": 108.95,"toLat": 30.26667,"toLng": 120.2},{"from": "河南","to": "浙江","fromLat": 34.76667,"fromLng": 113.65,"toLat": 30.26667,"toLng": 120.2},{"from": "安徽","to": "浙江","fromLat": 31.86119,"fromLng": 117.283042,"toLat": 30.26667,"toLng": 120.2},{"from": "江蘇","to": "浙江","fromLat": 32.05,"fromLng": 119.78333,"toLat": 30.26667,"toLng": 120.2},{"from": "浙江","to": "浙江","fromLat": 30.26667,"fromLng": 120.2,"toLat": 30.26667,"toLng": 120.2},{"from": "福建","to": "浙江","fromLat": 26.08333,"fromLng": 118.3,"toLat": 30.26667,"toLng": 120.2},{"from": "廣東","to": "浙江","fromLat": 23.16667,"fromLng": 113.23333,"toLat": 30.26667,"toLng": 120.2},{"from": "江西","to": "浙江","fromLat": 28.68333,"fromLng": 115.9,"toLat": 30.26667,"toLng": 120.2},{"from": "海南","to": "浙江","fromLat": 20.01667,"fromLng": 110.35,"toLat": 30.26667,"toLng": 120.2},{"from": "廣西","to": "浙江","fromLat": 22.82402,"fromLng": 108.320004,"toLat": 30.26667,"toLng": 120.2},{"from": "貴州","to": "浙江","fromLat": 26.56667,"fromLng": 106.71667,"toLat": 30.26667,"toLng": 120.2},{"from": "湖南","to": "浙江","fromLat": 28.21667,"fromLng": 113,"toLat": 30.26667,"toLng": 120.2},{"from": "湖北","to": "浙江","fromLat": 30.584355,"fromLng": 114.298572,"toLat": 30.26667,"toLng": 120.2},{"from": "四川","to": "浙江","fromLat": 30.66667,"fromLng": 104.06667,"toLat": 30.26667,"toLng": 120.2},{"from": "云南","to": "浙江","fromLat": 25.05,"fromLng": 102.73333,"toLat": 30.26667,"toLng": 120.2},{"from": "西藏","to": "浙江","fromLat": 30.6,"fromLng": 91,"toLat": 30.26667,"toLng": 120.2},{"from": "青海","to": "浙江","fromLat": 36.56667,"fromLng": 96.75,"toLat": 30.26667,"toLng": 120.2},{"from": "天津","to": "浙江","fromLat": 39.13333,"fromLng": 117.2,"toLat": 30.26667,"toLng": 120.2},{"from": "上海","to": "浙江","fromLat": 31.2,"fromLng": 121.55333,"toLat": 30.26667,"toLng": 120.2},{"from": "重慶","to": "浙江","fromLat": 29.56667,"fromLng": 106.45,"toLat": 30.26667,"toLng": 120.2},{"from": "北京","to": "浙江","fromLat": 39.91667,"fromLng": 116.41667,"toLat": 30.26667,"toLng": 120.2},{"from": "臺(tái)灣","to": "浙江","fromLat": 25.03,"fromLng": 121.3,"toLat": 30.26667,"toLng": 120.2},{"from": "香港","to": "浙江","fromLat": 22.2,"fromLng": 114.1,"toLat": 30.26667,"toLng": 120.2},{"from": "澳門","to": "浙江","fromLat": 22.2,"fromLng": 113.5,"toLat": 30.26667,"toLng": 120.2} ]

參考文章

多列數(shù)據(jù)合并為一列并添加分隔符

EXCEL合并字符串的幾種方法

總結(jié)

以上是生活随笔為你收集整理的EXCEL——批量生成中国各省省会经纬度JSON的一种方法的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。