Xác định khoảng cách giữa hai điểm bằng đường bay thẳng và trên thực tế trong Excel, Google Sheets

Nhu cầu xác định khoảng cách theo đường thẳng và đường đi thực tế là điều cần thiết cho nhiều đối tượng từ doanh nghiệp nhỏ lẻ đến các công ty vận chuyển quy mô lớn cũng như hệ thống bưu điện, hoặc chỉ đơn giản cho mục đích cá nhân. Tuy không phải mọi người đều nắm rõ cách thực hiện và không phải lúc nào cũng có đội ngũ IT hỗ trợ, nhưng qua sự trợ giúp của Google Sheets hay Excel, chúng ta hoàn toàn có khả năng tự tạo lời giải đơn giản. Bài viết này từ Siêu Marketing sẽ chỉ dẫn bạn cách làm.

Khoảng cách theo đường bay thẳng và trên thực tế

Áp dụng công thức Haversine để tính toán khoảng cách đường chim bay giữa hai điểm

Công thức không quá rắc rối để tính khoảng cách đường chim bay giữa hai vị trí là công thức Haversine. Nó được biểu diễn như sau:

Công thức Haversine

Công thức này có thể được biểu diễn 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)))

Mô tả từng phần của công thức:

6371: Bán kính Trái Đất xấp xỉ bằng km

lat1, lat2: Vĩ độ của điểm thứ nhất (latitude 1) và điểm thứ hai (latitude 2)

lng1, lng2: Kinh độ của điểm thứ nhất (longitude 1) và điểm thứ hai (longitude 2)

Lấy ví dụ, để tính khoảng cách giữa hai điểm có vĩ độ và kinh độ như sau: điểm 1 (20.98705, 105.8138) và điểm 2 (21.00433, 105.8155), có thệ áp dụng công thứ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

Kết quả có thể được kiểm tra lại thông qua Google Maps.

Bạn cũng có thể tạo ra một UDF (User Defined Function) trong VBA mang tên Haversine nhằm mục đích tương tự sau:

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 Variant

R = 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

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

=Haversine(20.98705, 105.8138, 21.00433, 105.8155)

Trong trường hợp dùng Google Sheets, bạn cũng có thể lập trình một công thức tương tự hoặc sử dụng Google Apps Script như sau:


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
}

Bằng công thức này, chúng ta có khả năng tiến hành tính toán khoảng cách giữa hai điểm.Xin lưu ý rằng, nếu như bạn đang làm việc với tọa độ kiểu số thập phân, tức là Decimal degrees, câu chuyện đơn giản hơn. Thế nhưng, khi bạn gặp phải tọa độ theo kiểu phút và giây, tức là Degrees Minutes Seconds, bạn cần thực hiện một bước chuyển đổi thêm để đưa chúng về dạng số thập phân. Chẳng hạn, 72*17’55″ cần được chuyển hóa thành 72.29861.

Không chỉ có công thức Haversine, bạn cũng có thể tham khảo một phép tính khác tên Vincenty, mặc dù nó hơi phức tạp hơn một chút.

Tính toán khoảng cách trên bản đồ thực tế giữa hai điểm địa lý bằng cách sử dụng API

“Khoảng cách từ nhà đến hồ là bao xa?” – Bạn sẽ không thích nhận một câu trả lời như là “3km nếu như là đường chim bay”, đúng không? Trong hoàn cảnh như vậy, đa số chúng ta sẽ tìm đến các ứng dụng bản đồ, như Google Maps chẳng hạn, để xác định chính xác đường đi và đề xuất lộ trình tốt nhất.

Khoảng cách là 7.5km

Giả sử bạn có một số địa chỉ được lưu trong Excel/Google Sheets và bạn muốn biết khoảng cách giữa chúng, bạn sẽ làm sao?

Có nhiều dịch vụ đã cung cấp API để giúp bạn dễ dàng tìm kiếm thông tin về khoảng cách – có thể tìm thấy bằng cách dùng từ khóa “distance matrix”. Siêu Marketing nỗ lực sử dụng Bing Maps API để tìm ra khoảng cách từ tọa độ 20.98705, 105.8138 đến 21.00433, 105.8155. Dưới đây là cách thực hiện.

Lấy chìa khóa Bing Maps API

Để bắt đầu, bạn cần một mã định danh cá nhân. Bạn có thể lấy mã này bằng cách truy cập https://www.bingmapsportal.com/ và đăng nhập hoặc đăng ký tài khoản Microsoft của bạn.

Đăng nhập thành công, hãy vào phần My account và chọn My keys:

Sau đó bạn chọn “Click here to create a new key” và nhấn “Show key” để lấy mã. Hãy lưu lại mã này.

Khi đã có mã, bước kế tiếp là sử dụng Bing Maps API để thu thông tin về khoảng cách.

Chúng ta hãy xem qua một tý về API, ở đây có Distance Matrix API tại đường link này.

Url chúng ta sẽ sử dụng có dạng như sau:

Trong đó:

lat1, lng1, lat2, lng2: là tọa độ vĩ độ và kinh độ của điểm thứ nhất và thứ hai.

travelMode=driving: loại hình di chuyển bạn chọn, tương tự như khi bạn chọn đi ô tô hay đi bộ trên Google Map. Mục này không bắt buộc phải nhập.

BingMapsAPIKey: chìa khóa bạn có được từ portal trước đó.

Điền đầy đủ các thông tin vào Url với tọa độ 20.98705, 105.813821.00433, 105.8155, bạn sẽ nhận được kết quả như sau:

Lưu ý đến số liệu hiển thị ở dòng “TravelDistance”, đây là đoạn đường khoảng cách giữa 2 vị trí tính theo km. Kết quả này kiểm tra lại sẽ phù hợp với thông tin từ Bing Maps.

Bước tiếp theo cần thực hiện là thu thập dữ liệu từ TravelDistance.

Cách dùng Google Apps Script để lấy thông tin khoảng cách

Code dưới đây sẽ hỗ trợ bạn làm việc này:


function getlocation(lat1,lng1,lat2,lng2){
var link = 'https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?'
var key = 'nhập 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
}

=getlocation(20.98705, 105.8138, 21.00433, 105.8155) để xem quãng đường giữa hai địa điểm.VBA201: Lập trình VBA nâng cao trong Excel, trong bài số 1004. Hoặc bạn cũng có thể xem qua video trên Youtube tại đây:


Cấu trúc bài hướng dẫn này cung cấp cách tính khoảng cách giữa hai điểm một cách đơn giản. Từ đó bạn có thể áp dụng cho nhiều trường hợp khác như xác định tổng đường đi của shipper trong một ngày hay tính thời gian di chuyển, thậm chí hiển thị dữ liệu qua bản đồ di chuyển. Đừng quên truy cập sieumarketing.com để cập nhật những khóa học mới nhất.

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop