Thẻ: app script

Tính khoảng cách theo đường chim bay và thực tế trong Excel, Google Sheets

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

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

Tính toán khoảng cách theo đường chim bay giữa 2 điểm bằng công thức Haversine

Một công thức không quá phức tạp được sử dụng để tính khoảng cách theo đường chim bay giữa 2 tọa độ mà ta có thể sử dụng là công thức Haversine. Công thức được mô tả:
Công thức Haversine
Một cách 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ĩ độ lần lượt của tọa độ 1 (lattitude 1) và tọa độ 2 (lattitude 2) lng1, lng2: Kinh độ lần lượt của tọa độ 1 (longitude 1) và tọa độ 2 (longitude 2) Ví dụ, ta tính khoảng cách với 2 điểm có tọa độ 1 (20.98705, 105.8138) và tọa độ 2 (21.00433, 105.8155) Áp 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 . Các bạn có thể thử tra trên google map để kiểm tra lại kết quả.
Ngoài ra, ta có thể tạo 1 UDF bằng VBA với 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 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

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 tương tự 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
}
Với công thức trên, ta có thể tính được khoảng cách 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 một 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 có thể tham khảo sử dụng một 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 di chuyển thực tế giữa 2 tọa độ sử dụng API

“Đi từ nhà lên bờ hồ hết bao nhiêu km?” – khi hỏi câu hỏi này, chắc hẳn chúng ta không mong chờ một đáp án kiểu như “3km theo đường chim bay” phải không. Trong trường hợp này, hầu hết mọi người sẽ sử dụng các công cụ bản đồ, chẳng hạn như Google Maps, để tra cứu quãng đường và nói ra quãng đường tốt nhất được gợi ý.
Khoảng 7.5km
Tương tự tình huống trên, giả sử chúng ta có 2 địa chỉ (hoặc nhiều hơn) lưu trong Excel/Google Sheets và cần biết quãng đường giữa 2 địa chỉ đó thì sao? Trong trường hợp này, có nhiều bên đã cung cấp các API để ta dễ dàng tra cứu dữ liệu liên quan tới quãng đường mà ta có thể tìm kiếm thông qua từ khóa “distance matrix”. Ở đây Học Excel Online sẽ sử dụng Bing Maps API để lấy dữ liệu về quãng đường từ tọa độ 20.98705, 105.8138 tới 21.00433, 105.8155. Các bước tiến hành sẽ được nói dưới đây.
Lấy Bing Maps Key
Bước đầu tiên đó là ta cần có một “mã cá nhân”. Để làm điều này, ta truy cập địa chỉ: https://www.bingmapsportal.com/ và đăng nhập bằng tài khoản Microsoft (hoặc đăng kí nếu chưa có). Sau khi đã đăng nhập thành công, truy cập vào My account và chọn My keys: Bước tiếp theo, ta chọn “Click here to create a new key” sau đó chọn “Show key” để hiển thị mã. Lưu mã đó lại. Khi đã có mã, bước tiếp theo ta sẽ tiến hành sử dụng Bing Maps API để lấy dữ liệu về quãng đường. Bây giờ, ta sẽ ngó qua một chút về API, cụ thể ở đây trong trường hợp này ta sẽ sử dụng Distance Matrix tại link này. Url ta sử dụng sẽ có 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: lần lượt là vĩ độ và kinh độ của tọa độ 1 và tọa độ 2 travelMode=driving: loại 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 đầy đủ thông tin vào Url trên với tọa độ 20.98705, 105.813821.00433, 105.8155 ta sẽ nhận được thông tin hiển thị như sau: Ta để ý đến dòng “TravelDistance”. Đây là quãng đường (đơn vị km) giữa 2 tọa độ. Khi kiểm tra lại bằng Bing Maps, ta có kết quả tương tự. Công việc tiếp theo ta cần phải làm đó là lấy được dữ liệu từ TravelDistance về.
Sử dụng Google Apps Script để thu thập dữ liệu về quãng đường
Bạn cần có kiến thức cơ bản của Google Apps Script để hiểu cách thức hoạt động. Tham gia 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 làm đ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 đường giữa 2 tọa độ
Ta cũng hoàn toàn có thể làm được với VBA trong Excel. Hướng 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 nâng cao trong Excelbài số 1004. Hoặc đơn giản hơn bạn có thể truy cập Youtube và xem video này:

Trên đây là toàn bộ hướng dẫn về cách tính khoảng cách giữa 2 điểm đơn giản mà từ đó chúng ta có thể mở rộng ra cho nhiều trường hợp, chẳng hạn như để tính toán tổng quãng đường 1 shipper đi trong ngày, thời gian đi hay thậm chí có thể trực quan hóa dữ liệu thành một bản đồ đường đi nữa. Các bạn cũng đừng quên ghé thăm trang web https://dtnguyen.business/ để tham khảo các khóa học mới nhất nhé.
lấy tên toàn bộ file trong folder Google Drive

Lấy tên toàn bộ file trong folder Google Drive vào Google Sheets

Để tổng hợp nhanh các file trong 1 thư mục vào trong Google Sheets, ta có thể viết một đoạn script nho nhỏ sử dụng Google App Script. Trong bài viết này, Học Excel Online sẽ hướng dẫn bạn cách lấy tên toàn bộ file từ folder trên Google Drive và đưa vào trong Google Sheets. Ví dụ ta có một folder mẫu đã được chia sẻ quyền truy cập với đường dẫn sau:
https://drive.google.com/drive/folders/1f6umsjEz6s2erp6SCnL2TZ8AdTAIyDjF
Trong folder đã bao gồm 3 file với tên Testfile1, Testfile2, Testfile3. Công việc ở đây là lấy toàn bộ tên 3 file và tổng hợp vào Google Sheets. Để thực hiện ta sẽ chia thành 2 bước:
  1. Truy cập Google Drive và lấy tên file trong folder
  2. Ghi các tên file vào trong Google Sheets, mỗi file là một dòng

Truy cập Google Drive và lấy tên toàn bộ file trong folder Google Drive

Đầu tiên ta mở App Script bằng cách chọn Extensions -> App Script. Tại đây, bạn có thể bắt đầu gõ các dòng lệnh bên dưới vào. Đoạn code để truy cập vào folder và lấy tập hợp các file thuộc folder đó trong Google Drive như sau:
var folders=DriveApp.getFolderById('1f6umsjEz6s2erp6SCnL2TZ8AdTAIyDjF')
var files = folders.getFiles()
Trong đó 1f6umsjEz6s2erp6SCnL2TZ8AdTAIyDjF là ID của folder (phần in đậm trên link đầu bài). Khi đã lấy được tập hợp các file, ta sẽ cần đi qua từng file và lấy tên tương ứng. Và ngay trong tài liệu hướng dẫn của Google đã đề cập đến ví dụ này nên ta sẽ sử dụng luôn:
while(files.hasNext()) {
	var file = files.next()
	var filename = file.getName()
}
với getName() là phương thức để lấy tên của file. Hiểu đơn giản, đoạn trên sẽ giúp lặp qua từng file và lấy tên của file đó. Tới đây, ta có thể ghi tên các file vào Google Sheets.

Ghi các tên file vào trong Google Sheets, mỗi file là một dòng

Tạm để filename sang một bên. Giờ ta sẽ truy cập vào Sheet đang mở bằng lệnh:
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
Trong đó Sheet1 là tên sheet nơi muốn đặt kết quả. 
Khi đã xác định nơi đặt kết quả, ta sẽ sử dụng phương thức appendRow để điền dữ liệu vào dòng cuối của vùng dữ liệu trong Sheet. Cụ thể, ngay tại vòng lặp ở trên, mỗi khi có được file name, ta sẽ tiến hành ghi ngay:
while (files.hasNext()) {
  var file = files.next() 
  var filename = file.getName() 
  ss.appendRow([filename])
}
Tổng kết lại, những gì ta cần ghi vào trong Google App Script như sau (với phần Folder ID và Sheet name tùy chỉnh):

function getfilename(){

  var folders = DriveApp.getFolderById('1f6umsjEz6s2erp6SCnL2TZ8AdTAIyDjF') 

  var files = folders.getFiles()

  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')

  while (files.hasNext()) {

     var file = files.next() 

     var filename = file.getName() 

     ss.appendRow([filename]) 
  }
}
Sau đó ta bấm Save project và ấn Run the selected function để chạy. Lưu ý: Trong trường hợp lần đầu tiên khởi chạy Function, ta sẽ nhận được 1 thông báo Review Permission. Lý do vì Google sẽ yêu cầu quyền truy cập tới Folder trên. Ta tiến hành theo các bước:
Chọn Review permissions
Chọn tài khoản
Chọn Advanced
Trong trường hợp gặp lỗi “Exception: No item with the given ID could be found. Possibly because you have not edited this item or you do not have permission to access it.“, bạn cần kiểm tra lại tài khoản Google của mình đã được cấp quyền truy cập vào folder đó hay chưa. Tham khảo thêm các bài viết về Google Apps Script tại: https://blog.hocexcel.online/google-apps-script

© Học Excel Online. All rights reserved.