Mike theo dõi các giá trị vĩ độ và kinh độ trong một trang tính Excel. Vì đây về cơ bản là các điểm trên lưới, Mike muốn tính khoảng cách giữa hai điểm vĩ độ / kinh độ nhất định.

Nếu các cặp vĩ độ và kinh độ thực sự chỉ là các điểm trên một lưới, thì việc tính toán khoảng cách giữa chúng sẽ rất dễ dàng. Vấn đề là chúng thực sự là các điểm trên một hình cầu, có nghĩa là bạn không thể sử dụng các phép tính lưới phẳng để xác định khoảng cách. Ngoài ra, có nhiều cách mà bạn có thể tính toán khoảng cách: khoảng cách bề mặt ngắn nhất, đường bay tối ưu (“như con quạ bay”), khoảng cách qua trái đất, khoảng cách lái xe, v.v.

Rõ ràng đây có thể là một câu hỏi phức tạp. Trong không gian có sẵn, tôi sẽ xem xét một số cách để xác định khoảng cách vòng tròn lớn (“khi quạ bay”), và sau đó cung cấp một số tài liệu tham khảo để biết thêm thông tin về các loại tính toán khác.

Điều đầu tiên bạn cần tìm ra là vĩ độ và kinh độ của mỗi điểm sẽ được thể hiện như thế nào trong Excel. Có một số cách nó có thể được biểu diễn. Ví dụ: bạn có thể nhập độ, phút và giây vào các ô riêng lẻ. Hoặc, bạn có thể đặt chúng trong một ô đơn dưới dạng DD: MM: SS. Cách nào cũng có thể chấp nhận được, nhưng chúng sẽ cần được xử lý theo cách khác với công thức của bạn. Tại sao? Bởi vì nếu bạn nhập vĩ độ và kinh độ dưới dạng DD: MM: SS, thì Excel sẽ chuyển đổi chúng trong nội bộ thành giá trị thời gian và bạn chỉ cần tính đến chuyển đổi đó.

Bất kể điều gì bạn cần làm là chuyển đổi vĩ độ và kinh độ của bạn thành một giá trị thập phân tính bằng radian. Nếu bạn có tọa độ trong ba ô riêng biệt (độ, phút và giây), thì bạn có thể sử dụng công thức sau để thực hiện chuyển đổi thành giá trị thập phân tính bằng radian:

=RADIANS((Degrees3600+Minutes60+Seconds)/3600)

Công thức sử dụng các phạm vi được đặt tên cho độ, phút và giây của bạn. Nó chuyển đổi ba giá trị đó thành một giá trị duy nhất đại diện cho tổng độ và sau đó sử dụng hàm RADIANS để chuyển đổi giá trị này thành radian.

Nếu bạn bắt đầu với giá trị 32 độ, 48 phút và 0 giây, công thức sẽ kết thúc như sau:

=RADIANS((323600+4860+0)/3600)

=RADIANS((115200+2880+0)/3600)

=RADIANS(118080/3600)

=RADIANS(32.8)

=0.572467995

Nếu bạn đang lưu trữ tọa độ của mình ở định dạng DD: MM: SS trong một ô (trong ví dụ này là ô E12), thì bạn có thể sử dụng công thức sau để chuyển đổi thành giá trị thập phân tính bằng radian:

=RADIANS((DAY(E12)86400+HOUR(E12)3600+MINUTE(E12)*60+SECOND(E12))/3600)

Giả sử rằng ô E12 chứa 32:48:00, thì công thức kết thúc như thế này:

=RADIANS((186400+83600+48*60+0)/3600)

=RADIANS((86400+28800+2880+0)/3600)

=RADIANS(118080/3600)

=RADIANS(32.8)

=0.572467995

Với tọa độ của bạn tính bằng radian, bạn có thể sử dụng công thức lượng giác để tính khoảng cách dọc theo bề mặt của một hình cầu. Có nhiều công thức như vậy có thể được sử dụng; công thức sau sẽ đủ cho mục đích của chúng tôi:

=ACOS(SIN(Lat1)SIN(Lat2)+COS(Lat1)COS(Lat2)COS(Lon2-Lon1))180/PI()*60

Trong công thức này, mỗi tọa độ vĩ độ (Lat1 và Lat2) và kinh độ (Lon1 và Lon2) phải là một giá trị thập phân, tính bằng radian, như đã được thảo luận. Công thức trả về một giá trị tính bằng hải lý, sau đó bạn có thể áp dụng các công thức khác nhau để chuyển đổi sang các đơn vị đo lường khác, như mong muốn.

Bạn nên nhận ra rằng các giá trị bạn đưa ra bằng cách sử dụng bất kỳ công thức nào tính khoảng cách trên bề mặt của hình cầu sẽ cho kết quả hơi sai. Tại sao? Vì Trái đất không phải là một khối cầu hoàn hảo. Vì vậy, các khoảng cách chỉ nên được coi là gần đúng. Nếu bạn muốn chính xác hơn một chút, thì bạn có thể sử dụng công thức sau để xác định hải lý của bạn:

=ACOS(SIN(Lat1)SIN(Lat2)+COS(Lat1)COS(Lat2)COS(Lon2-Lon1))3443.89849

Công thức này thay thế bán kính trái đất (3443,89849 hải lý) cho bán kính của một hình cầu (180 / PI () * 60, hoặc 3437,746771). Dù bằng cách nào, câu trả lời vẫn nên được coi là gần đúng.

Như bạn có thể nói, công thức tính khoảng cách khá dài. Bạn có thể thấy dễ dàng hơn khi phát triển hàm do người dùng xác định của riêng bạn sẽ thực hiện việc tính toán cho bạn. Hàm sau nhận bốn giá trị (hai cặp vĩ độ và kinh độ, tính bằng độ), sau đó trả về kết quả là hải lý:

Function CrowFlies(dlat1, dlon1, dlat2, dlon2)

Pi = Application.Pi()

earthradius = 3443.89849  'nautical miles

lat1 = dlat1  Pi / 180     lat2 = dlat2  Pi / 180     lon1 = dlon1  Pi / 180     lon2 = dlon2  Pi / 180

cosX = Sin(lat1)  Sin(lat2) + Cos(lat1) _        Cos(lat2) * Cos(lon1 - lon2)

CrowFlies = earthradius * Application.Acos(cosX)

End Function

Nếu bạn muốn xem thảo luận chuyên sâu hơn về vĩ độ và kinh độ, và toán học liên quan, bạn có thể tìm thấy tuyển tập các bài viết hay tại trang web này:

http://mathforum.org/library/drmath/sets/select/dm_lat_long.html

Với bài toán dưới đây, bạn có thể bắt đầu xem xét các công thức khác nhau mà bạn có thể sử dụng. Có một điều thú vị trong VBA tại trang Web này:

http://www.freevbcode.com/ShowCode.asp?ID=5532

Bạn cũng có thể tìm thấy một cuộc thảo luận có mục đích chung tại trang của Chip Pearson, tại đây:

http://www.cpearson.com/excel/LatLong.aspx

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (3275) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: