Tính khoảng phương pháp theo tuyến phố chim bay và thực tế trong Excel, Google Sheets

có đông đảo bắt buộc về tính khoảng cách thức theo trục đường chim bay và thực tiễn, từ các hộ buôn bán nhỏ cho tới những công ty giao vận lớn, hệ thống bưu cục, hay đơn thuần là nhu cầu tư nhân để tra cứu… không những thế, chẳng hề người nào cũng biết bí quyết cũng như thường mang được nhóm lập trình để vun đắp nên hệ thống tính toán. Dù thế, ta hoàn có thể tự làm một biện pháp đơn giản dùng Google Sheets hoặc Excel. Học Excel Online sẽ hướng dẫn bạn làm cho điều ấy qua bài viết này.

Tính khoảng cách thức theo trục đường chim bay và thực tế

Tính toán khoảng phương pháp theo các con phố chim bay giữa hai điểm bằng công thức Haversine

1 công thức ko quá phức tạp được tiêu dùng để tính khoảng phương pháp theo trục đường chim bay giữa hai tọa độ mà ta với thể sử dụng là công thức Haversine. Công thức được mô tả:

Công thức Haversine

1 cách thức tổng quát, ta viết lại công thức này trong Excel như sau:

=6371*2*ASIN(SQRT(POWER(SIN(PI()/180*(lat2–lat1)/2),2)+COS(PI()/180*lat1)*COS(PI()/180*lat2)*POWER(SIN(PI()/180*(lng2–lng1)/2),2)))

Trong đó:

6371 là bán kính trái đất (đơn vị: Km)

lat1, lat2: Vĩ độ tuần tự của tọa độ 1 (lattitude 1) và tọa độ hai (lattitude 2)

lng1, lng2: độ kinh tuần tự của tọa độ một (longitude 1) và tọa độ 2 (longitude 2)

ví dụ, ta tính khoảng bí quyết có 2 điểm mang tọa độ 1 (20.98705, 105.8138) và tọa độ 2 (21.00433, 105.8155)
vận dụng công thức ta có:

=6371*2*ASIN(SQRT(POWER(SIN(PI()/180*(21.00433-20.98705)/2),2)+COS(PI()/180*20.98705)*COS(PI()/180*21.00433)*POWER(SIN(PI()/180*(105.8155-105.8138)/2),2)))

=1.929536024

Anh chị em mang thể thử tra trên google map để kiểm tra lại kết quả.

ngoài ra, ta sở hữu thể tạo một UDF bằng VBA có tên Haversine cho mục đích trên:

Public Function Haversine(Lat1 As Variant, Lon1 As Variant, Lat2 As Variant, Lon2 As Variant) Dim R As Integer, dlon As Variant, dlat As Variant, Rad1 As Variant Dim a As Variant, c As Variant, d As Variant, Rad2 As VariantR = 6371 dlon = Excel.WorksheetFunction.Radians(Lon2 - Lon1) dlat = Excel.WorksheetFunction.Radians(Lat2 - Lat1) Rad1 = Excel.WorksheetFunction.Radians(Lat1) Rad2 = Excel.WorksheetFunction.Radians(Lat2) a = Sin(dlat / 2) * Sin(dlat / 2) + Cos(Rad1) * Cos(Rad2) * Sin(dlon / 2) * Sin(dlon / 2) c = 2 * Excel.WorksheetFunction.Asin(Sqr(a)) d = R * c Haversine = d End Function

Ta sử dụng công thức như sau:

=Haversine(20.98705, 105.8138, 21.00433, 105.8155)

Đối với Google sheets, ta cũng có thể viết một công thức như vậy trên chính trang tính, hoặc bằng Google Apps Script để làm việc này:

function haversine(lat1, lng1, lat2, lng2)  const R = 6371 var dlon = Math.PI/180*(lng2 - lng1) var dlat = Math.PI/180*(lat2 - lat1) var ra1 = Math.PI/180*lat1 var ra2 = Math.PI/180*lat2 var a = Math.sin(dlat / 2) * Math.sin(dlat / 2) + Math.cos(ra1) * Math.cos(ra2) * Math.sin(dlon / 2) * Math.sin(dlon / 2) var c = 2 * Math.asin(Math.sqrt(a)) var d = R * c return d

mang công thức trên, ta với thể tính được khoảng cách thức giữa 2 tọa độ. Lưu ý, tọa độ ở đây là tọa độ thập phân – Decimal degrees. Trong trường hợp ta sử dụng tọa độ đi kèm phút giây – Degrees Minutes Seconds, ta cần thêm 1 bước chuyển tọa độ về dạng thập phân. Ví dụ: 72*17'55 sẽ cần được chuyển về 72.29861.

Ngoài công thức Haversine, bạn với thể tham khảo tiêu dùng 1 công thức khác có phần phức tạp hơn là công thức Vincenty.

Tính toán quãng đường chuyển di thực tại giữa 2 tọa độ tiêu dùng API

"Đi từ nhà lên bờ phần đông bao lăm km?" – lúc hỏi nghi vấn này, chắc hẳn chúng ta không đợi mong một đáp án kiểu như "3km theo con đường chim bay" phải không. Trong trường hợp này, đa số mọi người sẽ dùng các phương tiện bản đồ, chẳng hạn như Google Maps, để tra cứu quãng đường và kể ra quãng đường rẻ nhất được gợi ý.

Khoảng 7.5km

như vậy cảnh huống trên, ví thử chúng ta có hai liên hệ (hoặc đa dạng hơn) lưu trong Excel/Google Sheets và cần biết quãng tuyến đường giữa 2 địa chỉ đó thì sao?

Trong trường hợp này, có phổ biến bên đã cung ứng những API để ta dễ dàng tra cứu dữ liệu can dự tới quãng tuyến phố mà ta sở hữu thể tìm kiếm chuẩn y từ khóa "distance matrix". Ở đây Học Excel Online sẽ tiêu dùng Bing Maps API để lấy dữ liệu về quãng trục đường trong khoảng tọa độ 20.98705, 105.8138 tới 21.00433, 105.8155. những bước tiến hành sẽ được đề cập dưới đây.

Lấy Bing Maps Key

Bước trước tiên ấy là ta cần có 1 "mã cá nhân". Để khiến cho điều này, ta truy hỏi cập địa chỉ: https://www.bingmapsportal.com/ và đăng nhập bằng trương mục Microsoft (hoặc đăng kí nếu chưa có).

Sau khi đã đăng nhập thành công, tróc nã cập vào My tài khoản và chọn My keys:

Bước tiếp theo, ta chọn "Click here lớn create a new key" sau đấy chọn "Show key" để hiển thị mã. Lưu mã đấy lại.

lúc đã với mã, bước tiếp theo ta sẽ tiến hành tiêu dùng Bing Maps API để lấy dữ liệu về quãng đường.

bây giờ, ta sẽ ngó qua một tẹo về API, cụ thể ở đây trong trường hợp này ta sẽ tiêu dùng Distance Matrix tại link này.

Url ta tiêu dùng sẽ với dạng:

https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=lat1,lng1&destinations=lat2,lng2&travelMode=driving&key=BingMapsAPIKey

Trong đó:

lat1, lng1, lat2, lng2: tuần tự là vĩ độ và độ kinh của tọa độ một và tọa độ hai

travelMode=driving: chiếc hình di chuyển, tương tự như chọn lái xe hoặc đi bộ khi bạn thao tác trên Google Map. Mục này có thể bỏ qua.

BingMapsAPIKey: key bạn vừa lấy được ở portal trên

Sau khi điền tất cả thông tin vào Url trên mang tọa độ 20.98705, 105.8138 và 21.00433, 105.8155 ta sẽ nhận được thông báo hiển thị như sau:

Ta quan tâm tới chiếc "TravelDistance". Đây là quãng đường (đơn vị km) giữa 2 tọa độ. Lúc kiểm tra lại bằng Bing Maps, ta với kết quả như vậy.

công tác tiếp theo ta cần phải làm ấy là lấy được dữ liệu trong khoảng TravelDistance về.

tiêu dùng Google Apps Script để thu thập dữ liệu về quãng các con phố

Bạn cần sở hữu tri thức cơ bản của Google Apps Script để hiểu bí quyết hoạt động. Tham dự ngay khóa học GAS101: Tự động hóa công việc với Google Apps Script

Đoạn code sau sẽ giúp bạn khiến điều này:

function getlocation(lat1,lng1,lat2,lng2) var link = 'https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?' var key = 'điền key của bạn vào đây' var response = UrlFetchApp.fetch(link + 'origins=' + lat1 + ',' + lng1 + '&destinations=' + lat2 + ',' + lng2 + '&travelMode=driving' + '&key=' + key); var json = response.getContentText(); var parse = JSON.parse(json); var result = parse.resourceSets[0].resources[0].results[0].travelDistance; return result

Sau khi đã lưu và cấp quyền, ta mở lại Google Sheet và viết cú pháp: =getlocation(20.98705, 105.8138, 21.00433, 105.8155) để lấy ra quãng tuyến đường giữa 2 tọa độ

Ta cũng hoàn toàn có thể làm được sở hữu VBA trong Excel. Chỉ dẫn chi tiết cho phần này đã được giới thiệu trong khóa học

VBA201: Lập trình VBA tăng trong Excel,

bài số 1004. Hoặc đơn thuần hơn bạn với thể truy hỏi cập Youtube và xem video này: