Bộ phận kho vận hay cần biết cửa hàng nào gần kho nhất để xếp tuyến giao hàng, hay khách hàng nào nằm trong bán kính giao miễn phí. Nếu bạn có tọa độ của từng điểm, bạn tính được khoảng cách giữa 2 tọa độ trong Excel chỉ bằng một công thức, không cần phần mềm bản đồ.

Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Các ô màu vàng nhạt là nơi bạn gõ công thức. Ảnh dùng định dạng số tiếng Anh, nên máy của bạn có thể hiện 21,0285 thay vì 21.0285. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn tham số bằng dấu chấm phẩy. Nếu máy của bạn dùng dấu phẩy thập phân, bạn gõ tọa độ là 21,0285, vì gõ 21.0285 sẽ thành chữ và công thức báo lỗi.

Tọa độ gồm những gì và lấy ở đâu?

Mỗi điểm trên bản đồ có 2 con số:

  • Vĩ độ cho biết điểm nằm xa xích đạo về phía bắc hay phía nam bao nhiêu độ. Hà Nội có vĩ độ khoảng 21.

  • Kinh độ cho biết điểm nằm về phía đông hay phía tây bao nhiêu độ. Hà Nội có kinh độ khoảng 105,8.

Trên Google Maps bản máy tính, bạn bấm chuột phải vào một điểm. Dòng đầu tiên của menu là tọa độ của điểm đó, ví dụ 21.02850, 105.85420, và bạn bấm vào dòng này để sao chép. Số đứng trước là vĩ độ, số đứng sau là kinh độ.

Công thức Haversine tính khoảng cách như thế nào?

Trái Đất có dạng gần như hình cầu, nên khoảng cách ngắn nhất giữa 2 điểm là một cung tròn trên mặt cầu chứ không phải một đường thẳng. Công thức Haversine tính độ dài cung tròn này từ vĩ độ, kinh độ của 2 điểm và bán kính Trái Đất.

Trong Excel, công thức có dạng sau, với bán kính Trái Đất là 6371 km:

=6371*2*ASIN(SQRT(SIN(RADIANS(vĩ độ 2 - vĩ độ 1)/2)^2 + COS(RADIANS(vĩ độ 1))*COS(RADIANS(vĩ độ 2))*SIN(RADIANS(kinh độ 2 - kinh độ 1)/2)^2))

Các hàm lượng giác SIN, COS trong Excel tính theo radian, còn tọa độ tính theo độ. Vì vậy, hàm RADIANS đổi các số đo độ sang radian trước khi đưa vào SIN và COS, gồm hiệu vĩ độ, hiệu kinh độ và vĩ độ của từng điểm.

Bạn tính khoảng cách từ kho tới từng cửa hàng như thế nào?

Bảng ví dụ có 8 cửa hàng ở vùng A1:D9 và 1 kho ở vùng G1:I2. Tọa độ trong bài là tọa độ ví dụ quanh Hà Nội, chỉ để minh họa cách tính.

Ô

Nội dung

C2:C9

Vĩ độ của 8 cửa hàng, ví dụ ô C2 là 21.0285

D2:D9

Kinh độ của 8 cửa hàng, ví dụ ô D2 là 105.8542

H2

Vĩ độ của kho, là 21.0450

I2

Kinh độ của kho, là 105.8890

Bạn chọn ô E2, gõ công thức sau rồi nhấn Enter:

=6371*2*ASIN(SQRT(SIN(RADIANS(C2-$H$2)/2)^2+COS(RADIANS($H$2))*COS(RADIANS(C2))*SIN(RADIANS(D2-$I$2)/2)^2))

Bạn đang gõ vào ô E2 công thức Haversine, thanh công thức hiện đủ công thức
Tọa độ kho ở ô H2, I2 được khóa bằng dấu $ để khi kéo xuống vẫn giữ nguyên

Sau đó, bạn đưa chuột tới chấm vuông ở góc dưới bên phải ô E2, khi con trỏ thành dấu cộng đen mảnh thì kéo xuống dòng 9. Cột E trong ảnh đã được định dạng 2 chữ số thập phân. Nếu ô của bạn hiện 4.050975, bạn quét chọn E2:E9 rồi bấm nút Decrease Decimal trên tab Home tới khi còn 2 chữ số.

Ô E2 đang chọn, cột E có khoảng cách từ kho tới 8 cửa hàng, từ 4.05 tới 10.26 km
Cửa hàng Hoàn Kiếm cách kho 4,05 km, còn cửa hàng Cầu Giấy cách kho 10,26 km

Công thức có 4 phần:

  • SIN(RADIANS(C2-$H$2)/2)^2 tính phần chênh lệch vĩ độ giữa cửa hàng và kho.

  • COS(RADIANS($H$2))*COS(RADIANS(C2))*SIN(RADIANS(D2-$I$2)/2)^2 tính phần chênh lệch kinh độ. Phần này nhân thêm COS của 2 vĩ độ, vì càng xa xích đạo thì 1 độ kinh càng ngắn.

  • 2*ASIN(SQRT(...)) đổi tổng 2 phần trên thành góc ở tâm Trái Đất, tính bằng radian.

  • 6371* nhân góc đó với bán kính Trái Đất để ra số km. Nếu muốn ra mét, bạn đổi 6371 thành 6371000.

Kết quả thường lệch dưới 0,3%, nhiều nhất khoảng 0,5% so với khoảng cách đo trên bản đồ, vì Trái Đất không phải hình cầu hoàn hảo. Mức lệch này không đáng kể khi bạn so sánh các điểm trong cùng thành phố.

Bạn tìm cửa hàng gần kho nhất như thế nào?

Bạn chọn ô G5, gõ công thức sau rồi nhấn Enter:

=INDEX(B2:B9,MATCH(MIN(E2:E9),E2:E9,0))

Bạn đang gõ vào ô G5 công thức tìm cửa hàng gần kho nhất
Hàm MIN tìm khoảng cách nhỏ nhất, MATCH tìm vị trí của số đó và INDEX lấy tên cửa hàng ở cùng vị trí

Tiếp theo, bạn gõ =MIN(E2:E9) vào ô H5 để hiện khoảng cách đó, rồi định dạng ô này 2 chữ số thập phân như cột E.

Ô G5 đang chọn, cửa hàng gần kho nhất là Cửa hàng Hoàn Kiếm, cách 4.05 km
Cửa hàng Hoàn Kiếm gần kho nhất, cách 4,05 km

Nếu bạn muốn lọc các cửa hàng trong bán kính 6 km, bạn bấm vào một ô trong bảng, chọn Data, Filter. Sau đó, bạn bấm mũi tên ở ô E1, chọn Number Filters, Less Than Or Equal To, gõ 6 rồi bấm OK. Với bảng ví dụ, có 4 cửa hàng thỏa điều kiện là Hoàn Kiếm, Ba Đình, Hai Bà Trưng và Gia Lâm.

Bạn đổi tọa độ độ, phút, giây sang số thập phân như thế nào?

Một số tài liệu ghi tọa độ theo kiểu độ, phút, giây, ví dụ 21°01'42.6". Công thức Haversine cần tọa độ dạng số thập phân, nên bạn đổi trước khi tính. Mỗi độ có 60 phút, mỗi phút có 60 giây.

Bảng ví dụ có độ ở cột B, phút ở cột C và giây ở cột D. Bạn chọn ô E2, gõ công thức sau rồi nhấn Enter, sau đó kéo xuống dòng 3:

=B2+C2/60+D2/3600

Bạn đang gõ vào ô E2 công thức =B2+C2/60+D2/3600
Phút chia 60 và giây chia 3600 để đổi ra phần lẻ của độ
Ô E3 đang chọn, vĩ độ 21°01'42.6" thành 21.028500 và kinh độ 105°51'15.1" thành 105.854194
Vĩ độ 21°01'42.6" bằng 21,0285 độ, cột E trong ảnh được định dạng 6 chữ số thập phân

Tọa độ ở bán cầu nam hoặc bán cầu tây mang dấu âm. Các điểm ở Việt Nam đều nằm ở bán cầu bắc và bán cầu đông, nên bạn giữ số dương.

Bạn hay gặp lỗi gì khi tính khoảng cách trong Excel?

Lỗi

Nguyên nhân

Cách sửa

Các dòng dưới ra hàng nghìn km, ví dụ 11.648 km, hoặc báo #VALUE!

Công thức thiếu dấu $ ở ô tọa độ kho, nên khi kéo xuống, dòng 3 trỏ tới ô trống H3, I3, còn dòng 4 trỏ tới ô tiêu đề H4.

Bạn khóa ô kho bằng $H$2 và $I$2.

Khoảng cách sai lớn dù không báo lỗi

Bạn để kinh độ ở cột vĩ độ, hoặc ngược lại.

Bạn kiểm tra lại: ở đất liền và các đảo gần bờ của Việt Nam, vĩ độ nằm trong khoảng 8 tới 24, còn kinh độ nằm trong khoảng 102 tới 110.

Ô kết quả báo #VALUE!

Tọa độ đang ở dạng chữ, thường do dán cả cặp số "21.0285, 105.8542" vào một ô.

Bạn tách vĩ độ và kinh độ ra 2 cột bằng Data, Text to Columns, chọn dấu phẩy làm ký tự phân cách. Nếu máy dùng dấu phẩy thập phân, ở bước cuối bạn bấm Advanced và chọn dấu chấm làm Decimal separator.

Khoảng cách đường chim bay khác quãng đường thực tế thế nào?

Đường chim bay là khoảng cách ngắn nhất giữa 2 điểm, nên quãng đường đi thực tế theo đường phố luôn dài hơn hoặc bằng con số này. Bạn dùng khoảng cách đường chim bay để so sánh, xếp thứ tự và lọc sơ bộ các điểm. Khi cần quãng đường thực tế để tính phí giao hàng, bạn tra chỉ đường trên Google Maps.

Nếu bạn dùng Excel 365 hoặc Excel 2021 trở lên, bạn có thể thay công thức ở ô G5 bằng =XLOOKUP(MIN(E2:E9),E2:E9,B2:B9). Bạn xem thêm cách dùng hàm này trong bài hàm XLOOKUP trong Excel.

Bạn muốn học Excel bài bản hơn?

Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.

Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.