Nội Dung Bài Viết

Việc tính khoảng cách, dù là đường chim bay hay đường đi thực tế, đều quan trọng với nhiều người, từ doanh nghiệp nhỏ đến các công ty vận tải lớn. Bài viết này hướng dẫn cách tính khoảng cách giữa hai điểm tọa độ bằng Excel và Google Sheets, giúp bạn dễ dàng tự xây dựng giải pháp tính toán mà không cần kiến thức lập trình phức tạp.

Công nghệ này hữu ích cho việc theo dõi lộ trình giao hàng, quản lý đội xe, hoặc đơn giản là tra cứu khoảng cách cá nhân. Hãy cùng tìm hiểu cách thực hiện!

Tính Khoảng Cách Đường Chim Bay

Công Thức Haversine

Công thức Haversine là một phương pháp phổ biến để tính khoảng cách đường chim bay giữa hai điểm tọa độ. Công thức này được biểu diễn như sau:

Công thức Haversine

Để áp dụng trong Excel, công thức được viết lại 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: Bán kính Trái Đất (đơn vị km)
lat1, lat2: Vĩ độ của điểm thứ nhất và thứ hai.
lng1, lng2: Kinh độ của điểm thứ nhất và thứ hai.

Ví dụ Excel

Ví dụ, tính khoảng cách giữa hai điểm (20.98705, 105.8138) và (21.00433, 105.8155):

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

Kết quả là khoảng 1.93 km. Bạn có thể kiểm tra kết quả này trên Google Maps.

Xem thêm: Lập Sổ Kế Toán Chi Tiết Vật Tư, Sản Phẩm, Hàng Hóa trên Excel

Ví dụ Excel 2

Ngoài ra, bạn có thể tạo hàm người dùng (UDF) trong VBA với tên Haversine:

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

Sử dụng hàm này trong Excel: =Haversine(20.98705, 105.8138, 21.00433, 105.8155)

Google Sheets và Google Apps Script

Tương tự, bạn có thể áp dụng công thức Haversine trực tiếp trong Google Sheets hoặc sử dụng Google Apps Script:

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
}

Lưu ý: Tọa độ sử dụng là tọa độ thập phân. Nếu sử dụng tọa độ độ-phút-giây, cần chuyển đổi sang dạng thập phân.

Tính Quãng Đường Di Chuyển Thực Tế

Để tính quãng đường di chuyển thực tế, ta cần sử dụng API. Bài viết này sẽ hướng dẫn sử dụng Bing Maps API.

Lấy Bing Maps Key

Truy cập https://www.bingmapsportal.com/ và đăng nhập bằng tài khoản Microsoft. Trong mục “My Account” -> “My Keys”, tạo và lưu lại key.

Sử dụng Bing Maps API

Sử dụng Distance Matrix API của Bing Maps với URL sau:

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

Trong đó:

lat1, lng1, lat2, lng2: Vĩ độ và kinh độ của hai điểm.
travelMode=driving: Chế độ di chuyển (lái xe).
BingMapsAPIKey: Key đã lấy ở bước trước.

Xem thêm: Hiểu về Kiểu Tham Chiếu R1C1 trong Excel

Thu Thập Dữ Liệu bằng Google Apps Script

Sử dụng đoạn code Google Apps Script sau để lấy dữ liệu khoảng cách:

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' // Nhớ thay 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
}

Trong Google Sheets, sử dụng hàm =getlocation(20.98705, 105.8138, 21.00433, 105.8155) để lấy quãng đường.

Ví dụ Google Sheet

Bạn cũng có thể thực hiện tương tự với VBA trong Excel.

Kết Luận

Bài viết đã hướng dẫn cách tính khoảng cách giữa hai điểm tọa độ bằng Excel và Google Sheets, cả đường chim bay và đường đi thực tế. Từ đây, bạn có thể ứng dụng để tính toán tổng quãng đường di chuyển, thời gian di chuyển, và thậm chí là trực quan hóa dữ liệu trên bản đồ.