Trong Excel ta có thể dễ dàng chọn nhiều vùng không liền nhau bằng cách giữ phím Ctrl sau đó thực hiện thao tác Merge dễ dàng. Tuy vậy thì việc Merge hàng loạt trong Google Sheets lại không thể thực hiện được theo cách thông thường. Với bài viết này Học Excel Online sẽ hướng dẫn bạn gộp ô hàng loạt một cách dễ dàng trong Google Sheets.
Merge hàng loạt trong Google Sheets với Google Apps Script
Để thực hiện việc tạo ra một “công cụ” merge hàng loạt, bước đầu tiên ta cần làm là truy cập Apps Script bằng cách chọn Extensions -> Apps Script trên thanh công cụ.
Sau khi đã mở cửa sổ Apps Script, copy đoạn code sau và dán vào:
function mergecells() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
var select = ss.getSelection().getActiveRangeList().getRanges()
for (var i = 0; i < select.length; i++){
select[i].merge()
}
}
Sau đó lưu lại và quay về trang tính.
Sau đó, trên thanh công cụ, chọn Insert -> Drawing và vẽ 1 hình bất kỳ:
Chọn Save and Close sau khi đã vẽ xong để hoàn tất.
Bước tiếp theo, ta ấn vào hình và chọn Assign Script. Điền tên function vừa tạo trong Apps Script “mergecells” vào và ấn OK.
Cuối cùng, ta chọn nhiều vùng bằng cách giữ Ctrl, sau đó ấn vào nút (hình) vừa tạo để tiến hành gộp ô.
Để hiểu hơn về bài viết này, các bạn có thể truy cập ngay khóa học GAS101: Tự động hóa công việc với Google Apps Script để có thêm các kiến thức cơ bản về Google Apps Script, giúp nâng cao hiệu suất công việc và tiết kiệm thời gian.
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:
. 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
Đố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.
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 Excel, bà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é.