{"id":882,"date":"2024-12-02T02:59:56","date_gmt":"2024-12-02T02:59:56","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/tinh-khoang-cach-theo-duong-chim-bay-va-thuc-te-trong-excel-google-sheets\/"},"modified":"2024-12-02T02:59:56","modified_gmt":"2024-12-02T02:59:56","slug":"tinh-khoang-cach-theo-duong-chim-bay-va-thuc-te-trong-excel-google-sheets","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/tinh-khoang-cach-theo-duong-chim-bay-va-thuc-te-trong-excel-google-sheets\/","title":{"rendered":"T\u00ednh kho\u1ea3ng c\u00e1ch theo \u0111\u01b0\u1eddng chim bay v\u00e0 th\u1ef1c t\u1ebf trong Excel, Google Sheets"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-dQf5b79p#1\">\n<p>C\u00f3 r\u1ea5t nhi\u1ec1u y\u00eau c\u1ea7u v\u1ec1 t\u00ednh kho\u1ea3ng c\u00e1ch theo \u0111\u01b0\u1eddng chim bay v\u00e0 th\u1ef1c t\u1ebf, t\u1eeb c\u00e1c h\u1ed9 kinh doanh nh\u1ecf cho \u0111\u1ebfn nh\u1eefng c\u00f4ng ty giao v\u1eadn l\u1edbn, h\u1ec7 th\u1ed1ng b\u01b0u c\u1ee5c, hay \u0111\u01a1n gi\u1ea3n l\u00e0 nhu c\u1ea7u c\u00e1 nh\u00e2n \u0111\u1ec3 tra c\u1ee9u\u2026 Tuy nhi\u00ean, kh\u00f4ng ph\u1ea3i ai c\u0169ng bi\u1ebft c\u00e1ch c\u0169ng nh\u01b0 kh\u00f4ng c\u00f3 \u0111\u01b0\u1ee3c \u0111\u1ed9i ng\u0169 l\u1eadp tr\u00ecnh \u0111\u1ec3 x\u00e2y d\u1ef1ng n\u00ean h\u1ec7 th\u1ed1ng t\u00ednh to\u00e1n. D\u00f9 v\u1eady, ta ho\u00e0n c\u00f3 th\u1ec3 t\u1ef1 l\u00e0m m\u1ed9t gi\u1ea3i ph\u00e1p \u0111\u01a1n gi\u1ea3n s\u1eed d\u1ee5ng Google Sheets ho\u1eb7c Excel. H\u1ecdc Excel Online s\u1ebd h\u01b0\u1edbng d\u1eabn b\u1ea1n l\u00e0m \u0111i\u1ec1u \u0111\u00f3 qua b\u00e0i vi\u1ebft n\u00e0y.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_61 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Xem nhanh<\/p>\n<p><label for=\"ez-toc-cssicon-toggle-item-674d22ab5eef0\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><\/span><\/span><\/label><\/p>\n<nav>\n<ul class=\"ez-toc-list ez-toc-list-level-1 \">\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/tinh-khoang-cach-theo-duong-chim-bay-va-thuc-te.html#Tinh_khoang_cach_theo_duong_chim_bay_va_thuc_te\" title=\"T\u00ednh kho\u1ea3ng c\u00e1ch theo \u0111\u01b0\u1eddng chim bay v\u00e0 th\u1ef1c t\u1ebf\">T\u00ednh kho\u1ea3ng c\u00e1ch theo \u0111\u01b0\u1eddng chim bay v\u00e0 th\u1ef1c t\u1ebf<\/a>\n<ul class=\"ez-toc-list-level-4\">\n<li class=\"ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/tinh-khoang-cach-theo-duong-chim-bay-va-thuc-te.html#Tinh_toan_khoang_cach_theo_duong_chim_bay_giua_2_diem_bang_cong_thuc_Haversine\" title=\"T\u00ednh to\u00e1n kho\u1ea3ng c\u00e1ch theo \u0111\u01b0\u1eddng chim bay gi\u1eefa 2 \u0111i\u1ec3m b\u1eb1ng c\u00f4ng th\u1ee9c Haversine\">T\u00ednh to\u00e1n kho\u1ea3ng c\u00e1ch theo \u0111\u01b0\u1eddng chim bay gi\u1eefa 2 \u0111i\u1ec3m b\u1eb1ng c\u00f4ng th\u1ee9c Haversine<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/tinh-khoang-cach-theo-duong-chim-bay-va-thuc-te.html#Tinh_toan_quang_duong_di_chuyen_thuc_te_giua_2_toa_do_su_dung_API\" title=\"T\u00ednh to\u00e1n qu\u00e3ng \u0111\u01b0\u1eddng di chuy\u1ec3n th\u1ef1c t\u1ebf gi\u1eefa 2 t\u1ecda \u0111\u1ed9 s\u1eed d\u1ee5ng API\">T\u00ednh to\u00e1n qu\u00e3ng \u0111\u01b0\u1eddng di chuy\u1ec3n th\u1ef1c t\u1ebf gi\u1eefa 2 t\u1ecda \u0111\u1ed9 s\u1eed d\u1ee5ng API<\/a>\n<ul class=\"ez-toc-list-level-5\">\n<li class=\"ez-toc-heading-level-5\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/tinh-khoang-cach-theo-duong-chim-bay-va-thuc-te.html#Lay_Bing_Maps_Key\" title=\"L\u1ea5y Bing Maps Key\">L\u1ea5y Bing Maps Key<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-5\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/tinh-khoang-cach-theo-duong-chim-bay-va-thuc-te.html#Su_dung_Google_Apps_Script_de_thu_thap_du_lieu_ve_quang_duong\" title=\"S\u1eed d\u1ee5ng Google Apps Script \u0111\u1ec3 thu th\u1eadp d\u1eef li\u1ec7u v\u1ec1 qu\u00e3ng \u0111\u01b0\u1eddng\">S\u1eed d\u1ee5ng Google Apps Script \u0111\u1ec3 thu th\u1eadp d\u1eef li\u1ec7u v\u1ec1 qu\u00e3ng \u0111\u01b0\u1eddng<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Tinh_khoang_cach_theo_duong_chim_bay_va_thuc_te\"><\/span>T\u00ednh kho\u1ea3ng c\u00e1ch theo \u0111\u01b0\u1eddng chim bay v\u00e0 th\u1ef1c t\u1ebf<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h4><span class=\"ez-toc-section\" id=\"Tinh_toan_khoang_cach_theo_duong_chim_bay_giua_2_diem_bang_cong_thuc_Haversine\"><\/span>T\u00ednh to\u00e1n kho\u1ea3ng c\u00e1ch theo \u0111\u01b0\u1eddng chim bay gi\u1eefa 2 \u0111i\u1ec3m b\u1eb1ng c\u00f4ng th\u1ee9c Haversine<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>M\u1ed9t c\u00f4ng th\u1ee9c kh\u00f4ng qu\u00e1 ph\u1ee9c t\u1ea1p \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u0111\u1ec3 t\u00ednh kho\u1ea3ng c\u00e1ch theo \u0111\u01b0\u1eddng chim bay gi\u1eefa 2 t\u1ecda \u0111\u1ed9 m\u00e0 ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng l\u00e0\u00a0<a href=\"https:\/\/en.wikipedia.org\/wiki\/Haversine_formula\"><strong>c\u00f4ng th\u1ee9c Haversine<\/strong><\/a>. C\u00f4ng th\u1ee9c \u0111\u01b0\u1ee3c m\u00f4 t\u1ea3:<\/p>\n<figure id=\"attachment_39122\" aria-describedby=\"caption-attachment-39122\" style=\"width: 1138px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-39122\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/2022-08-07-14_21_11-haversine-formula-wikipedia-1.png\" width=\"1138\" height=\"755\" title=\"2022-08-07-14_21_11-haversine-formula-wikipedia-1\"><figcaption id=\"caption-attachment-39122\" class=\"wp-caption-text\">C\u00f4ng th\u1ee9c Haversine<\/figcaption><\/figure>\n<p>M\u1ed9t c\u00e1ch t\u1ed5ng qu\u00e1t, ta vi\u1ebft l\u1ea1i c\u00f4ng th\u1ee9c n\u00e0y trong Excel nh\u01b0 sau:<\/p>\n<pre>=<strong>6371<\/strong>*2*ASIN(SQRT(POWER(SIN(PI()\/180*(<span style=\"color: #ff0000\"><strong>lat2<\/strong><\/span>-<span style=\"color: #003366\"><strong>lat1<\/strong><\/span>)\/2),2)+COS(PI()\/180*<span style=\"color: #003366\"><strong>lat1<\/strong><\/span>)*COS(PI()\/180*<span style=\"color: #ff0000\"><strong>lat2<\/strong><\/span>)*POWER(SIN(PI()\/180*(<span style=\"color: #808000\"><strong>lng2<\/strong><\/span>-<span style=\"color: #008000\"><strong>lng1<\/strong><\/span>)\/2),2)))<\/pre>\n<p>Trong \u0111\u00f3:<\/p>\n<p><strong>6371\u00a0<\/strong>l\u00e0 b\u00e1n k\u00ednh tr\u00e1i \u0111\u1ea5t (\u0111\u01a1n v\u1ecb: Km)<\/p>\n<p><strong>lat1, lat2<\/strong>: V\u0129 \u0111\u1ed9 l\u1ea7n l\u01b0\u1ee3t c\u1ee7a t\u1ecda \u0111\u1ed9 1 (lattitude 1) v\u00e0 t\u1ecda \u0111\u1ed9 2 (lattitude 2)<\/p>\n<p><strong>lng1, lng2<\/strong>: Kinh \u0111\u1ed9 l\u1ea7n l\u01b0\u1ee3t c\u1ee7a t\u1ecda \u0111\u1ed9 1 (longitude 1) v\u00e0 t\u1ecda \u0111\u1ed9 2 (longitude 2)<\/p>\n<div class=\"code-block code-block-2\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-17.gif\" title=\"vba101_92019-nho-17\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-53.gif\" title=\"ex101_92019-nho-53\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>V\u00ed d\u1ee5, ta t\u00ednh kho\u1ea3ng c\u00e1ch v\u1edbi 2 \u0111i\u1ec3m c\u00f3 t\u1ecda \u0111\u1ed9 1 <strong>(20.98705, 105.8138)<\/strong> v\u00e0 t\u1ecda \u0111\u1ed9 2 <strong>(21.00433, 105.8155)<\/strong><br \/>\n\u00c1p d\u1ee5ng c\u00f4ng th\u1ee9c ta c\u00f3:<\/p>\n<pre><em>=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)))<\/em><\/pre>\n<p>=1.929536024<\/p>\n<p>. C\u00e1c b\u1ea1n c\u00f3 th\u1ec3 th\u1eed tra tr\u00ean google map \u0111\u1ec3 ki\u1ec3m tra l\u1ea1i k\u1ebft qu\u1ea3.<\/p>\n<\/p>\n<p>Ngo\u00e0i ra, ta c\u00f3 th\u1ec3 t\u1ea1o 1 UDF b\u1eb1ng VBA v\u1edbi t\u00ean Haversine cho m\u1ee5c \u0111\u00edch tr\u00ean:<\/p>\n<pre><code class=\"language-vbnet\">Public Function Haversine(Lat1 As Variant, Lon1 As Variant, Lat2 As Variant, Lon2 As Variant)\r\nDim R As Integer, dlon As Variant, dlat As Variant, Rad1 As Variant\r\nDim a As Variant, c As Variant, d As Variant, Rad2 As Variant\r\n\r\nR = 6371\r\ndlon = Excel.WorksheetFunction.Radians(Lon2 - Lon1)\r\ndlat = Excel.WorksheetFunction.Radians(Lat2 - Lat1)\r\nRad1 = Excel.WorksheetFunction.Radians(Lat1)\r\nRad2 = Excel.WorksheetFunction.Radians(Lat2)\r\na = Sin(dlat \/ 2) * Sin(dlat \/ 2) + Cos(Rad1) * Cos(Rad2) * Sin(dlon \/ 2) * Sin(dlon \/ 2)\r\nc = 2 * Excel.WorksheetFunction.Asin(Sqr(a))\r\nd = R * c\r\nHaversine = d\r\nEnd Function\r\n<\/code><\/pre>\n<p>Ta s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c nh\u01b0 sau:<\/p>\n<p>=Haversine(<strong>20.98705, 105.8138, 21.00433, 105.8155)<\/strong><\/p>\n<\/p>\n<p>\u0110\u1ed1i v\u1edbi Google sheets, ta c\u0169ng c\u00f3 th\u1ec3 vi\u1ebft m\u1ed9t c\u00f4ng th\u1ee9c t\u01b0\u01a1ng t\u1ef1 tr\u00ean ch\u00ednh trang t\u00ednh, ho\u1eb7c b\u1eb1ng Google Apps Script \u0111\u1ec3 l\u00e0m vi\u1ec7c n\u00e0y:<\/p>\n<pre><code class=\"language-js\">\r\nfunction\u00a0haversine(lat1,\u00a0lng1,\u00a0lat2,\u00a0lng2)\u00a0{\r\nconst\u00a0R\u00a0=\u00a06371\r\nvar\u00a0dlon\u00a0=\u00a0Math.PI\/180*(lng2\u00a0-\u00a0lng1)\r\nvar\u00a0dlat\u00a0=\u00a0Math.PI\/180*(lat2\u00a0-\u00a0lat1)\r\nvar\u00a0ra1\u00a0=\u00a0Math.PI\/180*lat1\r\nvar\u00a0ra2\u00a0=\u00a0Math.PI\/180*lat2\r\nvar\u00a0a\u00a0=\u00a0Math.sin(dlat\u00a0\/\u00a02)\u00a0*\u00a0Math.sin(dlat\u00a0\/\u00a02)\u00a0+\u00a0Math.cos(ra1)\u00a0*\u00a0Math.cos(ra2)\u00a0*\u00a0Math.sin(dlon\u00a0\/\u00a02)\u00a0*\u00a0Math.sin(dlon\u00a0\/\u00a02)\r\nvar\u00a0c\u00a0=\u00a02\u00a0*\u00a0Math.asin(Math.sqrt(a))\r\nvar\u00a0d\u00a0=\u00a0R\u00a0*\u00a0c\r\nreturn\u00a0d\r\n}\r\n<\/code><\/pre>\n<p>V\u1edbi c\u00f4ng th\u1ee9c tr\u00ean, ta c\u00f3 th\u1ec3 t\u00ednh \u0111\u01b0\u1ee3c kho\u1ea3ng c\u00e1ch gi\u1eefa 2 t\u1ecda \u0111\u1ed9. L\u01b0u \u00fd, t\u1ecda \u0111\u1ed9 \u1edf \u0111\u00e2y l\u00e0 t\u1ecda \u0111\u1ed9 th\u1eadp ph\u00e2n \u2013 Decimal degrees. Trong tr\u01b0\u1eddng h\u1ee3p ta s\u1eed d\u1ee5ng t\u1ecda \u0111\u1ed9 \u0111i k\u00e8m ph\u00fat gi\u00e2y \u2013 Degrees Minutes Seconds, ta c\u1ea7n th\u00eam m\u1ed9t b\u01b0\u1edbc chuy\u1ec3n t\u1ecda \u0111\u1ed9 v\u1ec1 d\u1ea1ng th\u1eadp ph\u00e2n. V\u00ed d\u1ee5: 72*17\u201955 s\u1ebd c\u1ea7n \u0111\u01b0\u1ee3c chuy\u1ec3n v\u1ec1 72.29861.<\/p>\n<p><em>Ngo\u00e0i c\u00f4ng th\u1ee9c Haversine, b\u1ea1n c\u00f3 th\u1ec3 tham kh\u1ea3o s\u1eed d\u1ee5ng m\u1ed9t c\u00f4ng th\u1ee9c kh\u00e1c c\u00f3 ph\u1ea7n ph\u1ee9c t\u1ea1p h\u01a1n l\u00e0 <a href=\"https:\/\/en.wikipedia.org\/wiki\/Vincenty%27s_formulae\">c\u00f4ng th\u1ee9c Vincenty<\/a>.<\/em><\/p>\n<h4><span class=\"ez-toc-section\" id=\"Tinh_toan_quang_duong_di_chuyen_thuc_te_giua_2_toa_do_su_dung_API\"><\/span>T\u00ednh to\u00e1n qu\u00e3ng \u0111\u01b0\u1eddng di chuy\u1ec3n th\u1ef1c t\u1ebf gi\u1eefa 2 t\u1ecda \u0111\u1ed9 s\u1eed d\u1ee5ng API<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><strong>\u201c\u0110i t\u1eeb nh\u00e0 l\u00ean b\u1edd h\u1ed3 h\u1ebft bao nhi\u00eau km?\u201d \u2013 <\/strong>khi h\u1ecfi c\u00e2u h\u1ecfi n\u00e0y, ch\u1eafc h\u1eb3n ch\u00fang ta kh\u00f4ng mong ch\u1edd m\u1ed9t \u0111\u00e1p \u00e1n ki\u1ec3u nh\u01b0 \u201c3km theo \u0111\u01b0\u1eddng chim bay\u201d ph\u1ea3i kh\u00f4ng. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, h\u1ea7u h\u1ebft m\u1ecdi ng\u01b0\u1eddi s\u1ebd s\u1eed d\u1ee5ng c\u00e1c c\u00f4ng c\u1ee5 b\u1ea3n \u0111\u1ed3, ch\u1eb3ng h\u1ea1n nh\u01b0 Google Maps, \u0111\u1ec3 tra c\u1ee9u qu\u00e3ng \u0111\u01b0\u1eddng v\u00e0 n\u00f3i ra qu\u00e3ng \u0111\u01b0\u1eddng t\u1ed1t nh\u1ea5t \u0111\u01b0\u1ee3c g\u1ee3i \u00fd.<\/p>\n<figure id=\"attachment_39124\" aria-describedby=\"caption-attachment-39124\" style=\"width: 1366px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-39124\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/2022-08-07-16_34_39-book1-excel-1.png\" width=\"1366\" height=\"623\" title=\"2022-08-07-16_34_39-book1-excel-1\"><figcaption id=\"caption-attachment-39124\" class=\"wp-caption-text\">Kho\u1ea3ng 7.5km<\/figcaption><\/figure>\n<p>T\u01b0\u01a1ng t\u1ef1 t\u00ecnh hu\u1ed1ng tr\u00ean, gi\u1ea3 s\u1eed ch\u00fang ta c\u00f3 2 \u0111\u1ecba ch\u1ec9 (ho\u1eb7c nhi\u1ec1u h\u01a1n) l\u01b0u trong Excel\/Google Sheets v\u00e0 c\u1ea7n bi\u1ebft qu\u00e3ng \u0111\u01b0\u1eddng gi\u1eefa 2 \u0111\u1ecba ch\u1ec9 \u0111\u00f3 th\u00ec sao?<\/p>\n<p>Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, c\u00f3 nhi\u1ec1u b\u00ean \u0111\u00e3 cung c\u1ea5p c\u00e1c API \u0111\u1ec3 ta d\u1ec5 d\u00e0ng tra c\u1ee9u d\u1eef li\u1ec7u li\u00ean quan t\u1edbi qu\u00e3ng \u0111\u01b0\u1eddng m\u00e0 ta c\u00f3 th\u1ec3 t\u00ecm ki\u1ebfm th\u00f4ng qua t\u1eeb kh\u00f3a \u201cdistance matrix\u201d. \u1ede \u0111\u00e2y H\u1ecdc Excel Online s\u1ebd s\u1eed d\u1ee5ng Bing Maps API \u0111\u1ec3 l\u1ea5y d\u1eef li\u1ec7u v\u1ec1 qu\u00e3ng \u0111\u01b0\u1eddng t\u1eeb t\u1ecda \u0111\u1ed9 <strong>20.98705, 105.8138<\/strong> t\u1edbi<strong> 21.00433, 105.8155.\u00a0<\/strong>C\u00e1c b\u01b0\u1edbc ti\u1ebfn h\u00e0nh s\u1ebd \u0111\u01b0\u1ee3c n\u00f3i d\u01b0\u1edbi \u0111\u00e2y.<\/p>\n<h5><span class=\"ez-toc-section\" id=\"Lay_Bing_Maps_Key\"><\/span>L\u1ea5y Bing Maps Key<span class=\"ez-toc-section-end\"><\/span><\/h5>\n<p>B\u01b0\u1edbc \u0111\u1ea7u ti\u00ean \u0111\u00f3 l\u00e0 ta c\u1ea7n c\u00f3 m\u1ed9t \u201cm\u00e3 c\u00e1 nh\u00e2n\u201d. \u0110\u1ec3 l\u00e0m \u0111i\u1ec1u n\u00e0y, ta truy c\u1eadp \u0111\u1ecba ch\u1ec9: <a href=\"https:\/\/www.bingmapsportal.com\/\">https:\/\/www.bingmapsportal.com\/<\/a> v\u00e0 \u0111\u0103ng nh\u1eadp b\u1eb1ng t\u00e0i kho\u1ea3n Microsoft (ho\u1eb7c \u0111\u0103ng k\u00ed n\u1ebfu ch\u01b0a c\u00f3).<\/p>\n<p>Sau khi \u0111\u00e3 \u0111\u0103ng nh\u1eadp th\u00e0nh c\u00f4ng, truy c\u1eadp v\u00e0o My account v\u00e0 ch\u1ecdn My keys:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-39128\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/2022-08-07-17_10_03-book1-excel-1.png\" width=\"1366\" height=\"522\" title=\"2022-08-07-17_10_03-book1-excel-1\"><\/p>\n<p>B\u01b0\u1edbc ti\u1ebfp theo, ta ch\u1ecdn \u201cClick here to create a new key\u201d sau \u0111\u00f3 ch\u1ecdn \u201cShow key\u201d \u0111\u1ec3 hi\u1ec3n th\u1ecb m\u00e3. L\u01b0u m\u00e3 \u0111\u00f3 l\u1ea1i.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-39129\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/2022-08-07-17_12_52-create-key-bing-maps-dev-center-1.png\" width=\"948\" height=\"170\" title=\"2022-08-07-17_12_52-create-key-bing-maps-dev-center-1\"><\/p>\n<p>Khi \u0111\u00e3 c\u00f3 m\u00e3, b\u01b0\u1edbc ti\u1ebfp theo ta s\u1ebd ti\u1ebfn h\u00e0nh s\u1eed d\u1ee5ng Bing Maps API \u0111\u1ec3 l\u1ea5y d\u1eef li\u1ec7u v\u1ec1 qu\u00e3ng \u0111\u01b0\u1eddng.<\/p>\n<p>B\u00e2y gi\u1edd, ta s\u1ebd ng\u00f3 qua m\u1ed9t ch\u00fat v\u1ec1 API, c\u1ee5 th\u1ec3 \u1edf \u0111\u00e2y trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y ta s\u1ebd s\u1eed d\u1ee5ng <em>Distance Matrix\u00a0<\/em>t\u1ea1i\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/bingmaps\/rest-services\/routes\/calculate-a-distance-matrix?redirectedfrom=MSDN\"><strong>link n\u00e0y<\/strong><\/a>.<\/p>\n<p>Url ta s\u1eed d\u1ee5ng s\u1ebd c\u00f3 d\u1ea1ng:<\/p>\n<blockquote>\n<pre>https:\/\/dev.virtualearth.net\/REST\/v1\/Routes\/DistanceMatrix?origins=<strong>lat1<\/strong>,<strong>lng1<\/strong>&amp;destinations=<strong>lat2<\/strong>,<strong>lng2<\/strong>&amp;<em><strong>travelMode=driving<\/strong><\/em>&amp;key=<strong>BingMapsAPIKey\r\n<\/strong><\/pre>\n<\/blockquote>\n<p>Trong \u0111\u00f3:<\/p>\n<p><strong>lat1, lng1, lat2, lng2:\u00a0<\/strong>l\u1ea7n l\u01b0\u1ee3t l\u00e0 v\u0129 \u0111\u1ed9 v\u00e0 kinh \u0111\u1ed9 c\u1ee7a t\u1ecda \u0111\u1ed9 1 v\u00e0 t\u1ecda \u0111\u1ed9 2<\/p>\n<p><em><strong>travelMode=driving:\u00a0<\/strong><\/em>lo\u1ea1i h\u00ecnh di chuy\u1ec3n, t\u01b0\u01a1ng t\u1ef1 nh\u01b0 ch\u1ecdn l\u00e1i xe ho\u1eb7c \u0111i b\u1ed9 khi b\u1ea1n thao t\u00e1c tr\u00ean Google Map. M\u1ee5c n\u00e0y c\u00f3 th\u1ec3 b\u1ecf qua.<\/p>\n<p><strong>BingMapsAPIKey: <\/strong>key b\u1ea1n v\u1eeba l\u1ea5y \u0111\u01b0\u1ee3c \u1edf portal tr\u00ean<\/p>\n<p>Sau khi \u0111i\u1ec1n \u0111\u1ea7y \u0111\u1ee7 th\u00f4ng tin v\u00e0o Url tr\u00ean v\u1edbi t\u1ecda \u0111\u1ed9 <strong>20.98705, 105.8138<\/strong> v\u00e0 <strong>21.00433, 105.8155<\/strong> ta s\u1ebd nh\u1eadn \u0111\u01b0\u1ee3c th\u00f4ng tin hi\u1ec3n th\u1ecb nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-39131\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/2022-08-07-17_25_57-1.png\" width=\"1364\" height=\"693\" title=\"2022-08-07-17_25_57-1\"><\/p>\n<p>Ta \u0111\u1ec3 \u00fd \u0111\u1ebfn d\u00f2ng \u201cTravelDistance\u201d. \u0110\u00e2y l\u00e0 qu\u00e3ng \u0111\u01b0\u1eddng (\u0111\u01a1n v\u1ecb km) gi\u1eefa 2 t\u1ecda \u0111\u1ed9. Khi ki\u1ec3m tra l\u1ea1i b\u1eb1ng Bing Maps, ta c\u00f3 k\u1ebft qu\u1ea3 t\u01b0\u01a1ng t\u1ef1.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-39132\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/2022-08-07-17_27_47-book1-excel-1.png\" width=\"1366\" height=\"693\" title=\"2022-08-07-17_27_47-book1-excel-1\"><\/p>\n<p>C\u00f4ng vi\u1ec7c ti\u1ebfp theo ta c\u1ea7n ph\u1ea3i l\u00e0m \u0111\u00f3 l\u00e0 l\u1ea5y \u0111\u01b0\u1ee3c d\u1eef li\u1ec7u t\u1eeb TravelDistance v\u1ec1.<\/p>\n<h5><span class=\"ez-toc-section\" id=\"Su_dung_Google_Apps_Script_de_thu_thap_du_lieu_ve_quang_duong\"><\/span>S\u1eed d\u1ee5ng Google Apps Script \u0111\u1ec3 thu th\u1eadp d\u1eef li\u1ec7u v\u1ec1 qu\u00e3ng \u0111\u01b0\u1eddng<span class=\"ez-toc-section-end\"><\/span><\/h5>\n<blockquote>\n<p>B\u1ea1n c\u1ea7n c\u00f3 ki\u1ebfn th\u1ee9c c\u01a1 b\u1ea3n c\u1ee7a Google Apps Script \u0111\u1ec3 hi\u1ec3u c\u00e1ch th\u1ee9c ho\u1ea1t \u0111\u1ed9ng. Tham gia ngay kh\u00f3a h\u1ecdc <strong><a href=\"https:\/\/dtnguyen.business\/elearning\/gas101-lam-viec-nang-suat-hon-tu-dong-hoa-cong-viec-voi-google-apps-script\">GAS101: T\u1ef1 \u0111\u1ed9ng h\u00f3a c\u00f4ng vi\u1ec7c v\u1edbi Google Apps Script<\/a><\/strong><\/p>\n<\/blockquote>\n<p>\u0110o\u1ea1n code sau s\u1ebd gi\u00fap b\u1ea1n l\u00e0m \u0111i\u1ec1u n\u00e0y:<\/p>\n<pre><code class=\"language-js\">\r\nfunction\u00a0getlocation(lat1,lng1,lat2,lng2){\r\nvar\u00a0link\u00a0=\u00a0'https:\/\/dev.virtualearth.net\/REST\/v1\/Routes\/DistanceMatrix?'\r\nvar key = '\u0111i\u1ec1n key c\u1ee7a b\u1ea1n v\u00e0o \u0111\u00e2y'\r\nvar\u00a0response\u00a0=\u00a0UrlFetchApp.fetch(link\u00a0+\u00a0'origins='\u00a0+\u00a0lat1\u00a0+\u00a0','\u00a0+\u00a0lng1\u00a0+\u00a0'&amp;destinations='\u00a0+\u00a0lat2\u00a0+\u00a0','\u00a0+\u00a0lng2\u00a0+\u00a0'&amp;travelMode=driving'\u00a0+\u00a0'&amp;key='\u00a0+\u00a0key);\r\nvar\u00a0json\u00a0=\u00a0response.getContentText();\r\nvar\u00a0parse\u00a0=\u00a0JSON.parse(json);\r\nvar\u00a0result\u00a0=\u00a0parse.resourceSets[0].resources[0].results[0].travelDistance;\r\nreturn\u00a0result\r\n}<\/code><\/pre>\n<div>Sau khi \u0111\u00e3 l\u01b0u v\u00e0 c\u1ea5p quy\u1ec1n, ta m\u1edf l\u1ea1i Google Sheet v\u00e0 vi\u1ebft c\u00fa ph\u00e1p: <strong>=getlocation(20.98705, 105.8138,<\/strong>\u00a0<strong>21.00433, 105.8155) <\/strong>\u0111\u1ec3 l\u1ea5y ra qu\u00e3ng \u0111\u01b0\u1eddng gi\u1eefa 2 t\u1ecda \u0111\u1ed9<\/div>\n<div><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-39134\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/2022-08-07-17_44_51-untitled-spreadsheet-google-sheets-1.png\" width=\"499\" height=\"188\" title=\"2022-08-07-17_44_51-untitled-spreadsheet-google-sheets-1\"><\/div>\n<div><\/div>\n<div>Ta c\u0169ng ho\u00e0n to\u00e0n c\u00f3 th\u1ec3 l\u00e0m \u0111\u01b0\u1ee3c v\u1edbi VBA trong Excel. H\u01b0\u1edbng d\u1eabn chi ti\u1ebft cho ph\u1ea7n n\u00e0y \u0111\u00e3 \u0111\u01b0\u1ee3c gi\u1edbi thi\u1ec7u trong kh\u00f3a h\u1ecdc <strong><a href=\"https:\/\/dtnguyen.business\/elearning\/vba201-lap-trinh-vba-nang-cao-trong-excel\">VBA201: L\u1eadp tr\u00ecnh VBA n\u00e2ng cao trong Excel<\/a>,\u00a0<\/strong>b\u00e0i s\u1ed1 1004. Ho\u1eb7c \u0111\u01a1n gi\u1ea3n h\u01a1n b\u1ea1n c\u00f3 th\u1ec3 truy c\u1eadp Youtube v\u00e0 xem video n\u00e0y:<\/div>\n<iframe loading=\"lazy\" title=\"Su\u031b\u0309 du\u0323ng API trong la\u0323\u0302p tri\u0300nh VBA du\u031b\u0323 ba\u0301o tho\u031b\u0300i tie\u0302\u0301t theo \u0111i\u0323a \u0111ie\u0302\u0309m\" width=\"500\" height=\"281\" src=\"https:\/\/www.youtube.com\/embed\/5t2QSeDzsu0?feature=oembed&amp;enablejsapi=1&amp;origin=https:\/\/blog.hocexcel.online\" frameborder allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" allowfullscreen><\/iframe>\n<hr>\n<p>Tr\u00ean \u0111\u00e2y l\u00e0 to\u00e0n b\u1ed9 h\u01b0\u1edbng d\u1eabn v\u1ec1 c\u00e1ch t\u00ednh kho\u1ea3ng c\u00e1ch gi\u1eefa 2 \u0111i\u1ec3m \u0111\u01a1n gi\u1ea3n m\u00e0 t\u1eeb \u0111\u00f3 ch\u00fang ta c\u00f3 th\u1ec3 m\u1edf r\u1ed9ng ra cho nhi\u1ec1u tr\u01b0\u1eddng h\u1ee3p, ch\u1eb3ng h\u1ea1n nh\u01b0 \u0111\u1ec3 t\u00ednh to\u00e1n t\u1ed5ng qu\u00e3ng \u0111\u01b0\u1eddng 1 shipper \u0111i trong ng\u00e0y, th\u1eddi gian \u0111i hay th\u1eadm ch\u00ed c\u00f3 th\u1ec3 tr\u1ef1c quan h\u00f3a d\u1eef li\u1ec7u th\u00e0nh m\u1ed9t b\u1ea3n \u0111\u1ed3 \u0111\u01b0\u1eddng \u0111i n\u1eefa. C\u00e1c b\u1ea1n c\u0169ng \u0111\u1eebng qu\u00ean gh\u00e9 th\u0103m trang web <a href=\"https:\/\/dtnguyen.business\/\">https:\/\/dtnguyen.business\/<\/a> \u0111\u1ec3 tham kh\u1ea3o c\u00e1c kh\u00f3a h\u1ecdc m\u1edbi nh\u1ea5t nh\u00e9.<\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-17.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":883,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-882","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/882","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/comments?post=882"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/882\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/883"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=882"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=882"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=882"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}