{"id":900,"date":"2024-12-02T03:02:32","date_gmt":"2024-12-02T03:02:32","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/tim-chuoi-gan-giong-trong-excel-voi-levenshtein-distance\/"},"modified":"2024-12-02T03:02:32","modified_gmt":"2024-12-02T03:02:32","slug":"tim-chuoi-gan-giong-trong-excel-voi-levenshtein-distance","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/tim-chuoi-gan-giong-trong-excel-voi-levenshtein-distance\/","title":{"rendered":"T\u00ecm chu\u1ed7i g\u1ea7n gi\u1ed1ng trong Excel v\u1edbi Levenshtein distance"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Trong c\u00f4ng vi\u1ec7c h\u00e0ng ng\u00e0y, \u0111\u00f4i khi b\u1ea1n s\u1ebd g\u1eb7p ph\u1ea3i y\u00eau c\u1ea7u ch\u1ec9nh s\u1eeda l\u1ea1i c\u00e1c t\u1eeb, c\u1ee5m t\u1eeb g\u1ea7n gi\u1ed1ng nhau b\u1edfi nhi\u1ec1u l\u00fd do: c\u00f3 th\u1ec3 l\u00e0 ng\u01b0\u1eddi kh\u00e1c l\u1ee1 nh\u1eadp li\u1ec7u sai, ho\u1eb7c c\u00f3 th\u1ec3 do nhi\u1ec1u b\u00ean nh\u1eadp li\u1ec7u kh\u00f4ng th\u1ed1ng nh\u1ea5t, ho\u1eb7c s\u1eed d\u1ee5ng c\u00e1ch b\u1ecf d\u1ea5u kh\u00e1c nhau khi\u1ebfn cho c\u00e1c t\u1eeb t\u01b0\u1edfng gi\u1ed1ng nhau h\u00f3a ra l\u1ea1i kh\u00e1c (nh\u01b0 tr\u01b0\u1eddng h\u1ee3p \u0111\u01b0\u1ee3c n\u00eau <a href=\"http:\/\/blog.ezworkapp.com\/group-doi-tuong-fuzzy-matching-trong-power-query.html\"><strong>t\u1ea1i \u0111\u00e2y<\/strong><\/a>). Hay b\u1ea1n c\u00f3 bao gi\u1edd t\u00f2 m\u00f2 r\u1eb1ng t\u00ednh n\u0103ng t\u1ef1 \u0111\u1ed9ng g\u1ee3i \u00fd t\u1eeb ho\u1ea1t \u0111\u1ed9ng ra sao? T\u1ea5t nhi\u00ean, c\u00f3 r\u1ea5t nhi\u1ec1u ph\u01b0\u01a1ng ph\u00e1p \u0111\u1ec3 l\u00e0m \u0111i\u1ec1u \u0111\u00f3, v\u00e0 trong b\u00e0i vi\u1ebft n\u00e0y, H\u1ecdc Excel Online s\u1ebd gi\u1edbi thi\u1ec7u t\u1edbi c\u00e1c b\u1ea1n Levenshtein distance (kho\u1ea3ng c\u00e1ch Levenshtein) c\u0169ng nh\u01b0 \u1ee9ng d\u1ee5ng c\u1ee7a n\u00f3 \u0111\u1ec3 t\u00ecm chu\u1ed7i g\u1ea7n gi\u1ed1ng nhau.<\/p>\n<p>N\u1ebfu b\u1ea1n \u0111\u00e3 hi\u1ec3u v\u1ec1 Levenshtein Distance v\u00e0 mu\u1ed1n t\u00ecm c\u00e1ch s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p VBA \u0111\u1ec3 so s\u00e1nh \u0111\u1ed3ng th\u1eddi nhi\u1ec1u chu\u1ed7i c\u00f9ng l\u00fac, h\u00e3y \u0111\u1ecdc\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/sanh-chuoi-tim-chuoi-gan-dung-trong-excel.html\"><strong>b\u00e0i vi\u1ebft n\u00e0y<\/strong><\/a><\/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-674d234725c90\" 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\/tim-chuoi-gan-giong-trong-excel.html#Levenshtein_Distance\" title=\"Levenshtein Distance?\">Levenshtein Distance?<\/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\/tim-chuoi-gan-giong-trong-excel.html#Cong_thuc\" title=\"C\u00f4ng th\u1ee9c\">C\u00f4ng th\u1ee9c<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/tim-chuoi-gan-giong-trong-excel.html#Tim_chuoi_gan_giong_nhau_trong_Excel_voi_Levenshtein_distance\" title=\"T\u00ecm chu\u1ed7i g\u1ea7n gi\u1ed1ng nhau trong Excel v\u1edbi Levenshtein distance\">T\u00ecm chu\u1ed7i g\u1ea7n gi\u1ed1ng nhau trong Excel v\u1edbi Levenshtein distance<\/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-4\" href=\"https:\/\/blog.hocexcel.online\/tim-chuoi-gan-giong-trong-excel.html#Thiet_lap_vi_tri_nhap_chuoi\" title=\"Thi\u1ebft l\u1eadp v\u1ecb tr\u00ed nh\u1eadp chu\u1ed7i\">Thi\u1ebft l\u1eadp v\u1ecb tr\u00ed nh\u1eadp chu\u1ed7i<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/tim-chuoi-gan-giong-trong-excel.html#Thiet_lap_khung_cho_mo_hinh\" title=\"Thi\u1ebft l\u1eadp khung cho m\u00f4 h\u00ecnh\">Thi\u1ebft l\u1eadp khung cho m\u00f4 h\u00ecnh<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/tim-chuoi-gan-giong-trong-excel.html#Cat_chuoi\" title=\"C\u1eaft chu\u1ed7i\">C\u1eaft chu\u1ed7i<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blog.hocexcel.online\/tim-chuoi-gan-giong-trong-excel.html#Dien_so\" title=\"\u0110i\u1ec1n s\u1ed1\">\u0110i\u1ec1n s\u1ed1<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/blog.hocexcel.online\/tim-chuoi-gan-giong-trong-excel.html#Tinh_ty_le_trung_khop\" title=\"T\u00ednh t\u1ef7 l\u1ec7 tr\u00f9ng kh\u1edbp\">T\u00ednh t\u1ef7 l\u1ec7 tr\u00f9ng kh\u1edbp<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Levenshtein_Distance\"><\/span>Levenshtein Distance?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>N\u00f3i m\u1ed9t c\u00e1ch \u0111\u01a1n gi\u1ea3n, Levenshtein Distance l\u00e0\u00a0<em>s\u1ef1 kh\u00e1c bi\u1ec7t gi\u1eefa 2 chu\u1ed7i k\u00ed t\u1ef1. C\u1ee5 th\u1ec3 h\u01a1n, n\u00f3 l\u00e0 s\u1ed1 k\u00ed t\u1ef1\u00a0<strong>c\u1ea7n x\u00f3a \u0111i, th\u00eam v\u00e0o ho\u1eb7c thay th\u1ebf \u0111\u1ec3 bi\u1ebfn \u0111\u1ed5i m\u1ed9t chu\u1ed7i k\u00ed t\u1ef1 th\u00e0nh chu\u1ed7i kh\u00e1c<\/strong>.<\/em><\/p>\n<blockquote>\n<p>\u201cThe Levenshtein distance between two strings is <b>the number of single character deletions, insertions, or substitutions required to transform one string into the other<\/b>.\u201d<\/p>\n<\/blockquote>\n<p>V\u00ed d\u1ee5, ta c\u00f3 2 chu\u1ed7i: 1.\u00a0<strong>cat<\/strong>; 2.\u00a0<strong>cap<\/strong><\/p>\n<p>Theo \u0111\u1ecbnh ngh\u0129a Levenshtein Distance, s\u1ef1 kh\u00e1c bi\u1ec7t c\u1ee7a 2 chu\u1ed7i tr\u00ean l\u00e0 <strong>1<\/strong> b\u1edfi ch\u1ec9 c\u1ea7n <strong>1 ph\u00e9p bi\u1ebfn \u0111\u1ed5i<\/strong> t\u1eeb\u00a0<strong>cat\u00a0<\/strong>sang\u00a0<strong>cap<\/strong> (thay\u00a0<strong>t<\/strong> b\u1eb1ng\u00a0<strong>p<\/strong>).<\/p>\n<p>T\u01b0\u01a1ng t\u1ef1, v\u1edbi 2 chu\u1ed7i: 1.\u00a0<strong>KPMG<\/strong> v\u00e0 2.\u00a0<strong>KPMGLLp<\/strong>, s\u1ef1 kh\u00e1c bi\u1ec7t l\u00e0 <strong>3<\/strong> b\u1edfi c\u1ea7n <strong>3\u00a0ph\u00e9p bi\u1ebfn \u0111\u1ed5i th\u00eam v\u00e0o<\/strong> t\u1eeb <strong>KPMG<\/strong> sang\u00a0<strong>KPMGLLp<\/strong> ho\u1eb7c ng\u01b0\u1ee3c l\u1ea1i.<\/p>\n<h4><span class=\"ez-toc-section\" id=\"Cong_thuc\"><\/span>C\u00f4ng th\u1ee9c<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>C\u00e1ch t\u00ednh s\u1ed1 kh\u00e1c bi\u1ec7t \u0111\u01b0\u1ee3c vi\u1ebft nh\u01b0 sau:<\/p>\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-56.gif\" title=\"ex101_92019-nho-56\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/pjd6f-1.png\" alt=\"PHP - Is this Levenshtein distance recursive algorithm so slow or am I wrong? - Stack Overflow\" title=\"pjd6f-1\"><\/p>\n<p>\u00a0\n<\/p>\n<p>Trong \u0111\u00f3:<\/p>\n<p>a l\u00e0 chu\u1ed7i th\u1ee9 1, b l\u00e0 chu\u1ed7i th\u1ee9 2, i,j l\u00e0 v\u1ecb tr\u00ed \u0111\u1ea7u cu\u1ed1i (t\u00ednh t\u1eeb k\u00ed t\u1ef1 \u0111\u1ea7u \u0111\u1ebfn k\u00ed t\u1ef1 i, j c\u1ee7a 2 chu\u1ed7i).\n<\/p>\n<p><strong>Hi\u1ec3u m\u1ed9t c\u00e1ch \u0111\u01a1n gi\u1ea3n:\u00a0<\/strong>\n<\/p>\n<p>-N\u1ebfu i ho\u1eb7c j = 0 th\u00ec s\u1ed1 kh\u00e1c bi\u1ec7t l\u00e0 s\u1ed1 l\u1edbn nh\u1ea5t trong i v\u00e0 j.\n<\/p>\n<p>-Tr\u01b0\u1eddng h\u1ee3p c\u00f2n l\u1ea1i, s\u1ed1 kh\u00e1c bi\u1ec7t l\u00e0 s\u1ed1 nh\u1ecf nh\u1ea5t trong 3 c\u00f4ng th\u1ee9c:\n<\/p>\n<pre> <ol><li>lev(i-1,j)+1<\/li> <li>lev(i,j-1)+1<\/li> <li>lev(i-1,j-1)<strong>+1*<\/strong><\/li><\/ol><\/pre>\n<p>.\n<\/p>\n<p><strong>*<\/strong><em>Tr\u01b0\u1eddng h\u1ee3p +1 ch\u1ec9 \u00e1p d\u1ee5ng khi ai &lt;&gt; bj (k\u00ed t\u1ef1 \u1edf v\u1ecb tr\u00ed i, chu\u1ed7i a kh\u00e1c v\u1edbi k\u00ed t\u1ef1 \u1edf v\u1ecb tr\u00ed j, chu\u1ed7i b). <\/em><\/p>\n<p>V\u00ed d\u1ee5:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-38136 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-chuoi-gan-giong-nhau-1-300x206-2.png\" width=\"300\" height=\"206\" title=\"tim-chuoi-gan-giong-nhau-1-300x206-2\">\n<\/p>\n<p>Ta b\u1eaft \u0111\u1ea7u t\u1eeb\u00a0<strong>lev(a,b)(1,1)\u00a0<\/strong>v\u00e0 k\u1ebft th\u00fac \u1edf\u00a0<strong>lev(a,b)(3,3)<\/strong><\/p>\n<pre>\r\n<br>\r\nlev(a,b)(1,1) = min[lev(a,b)(0,1)+1, lev(a,b)(1,0)+1, lev(a,b)(0,0)] = 0 (kh\u00f4ng <strong>+1<\/strong> v\u00ec ai = a1 = \"c\" = bj = b1 = \"c\")\r\n<br>\r\nlev(a,b)(1,2) = min[lev(a,b)(0,2)+1, lev(a,b)(1,1)+1, lev(a,b)(0,1)<strong>+1<\/strong>] = 1 (+1 v\u00ec ai &lt;&gt; bj)\r\n<br>\r\nlev(a,b)(1,3) = min[lev(a,b)(0,3)+1, lev(a,b)(1,2)+1, lev(a,b)(0,2)<strong>+1<\/strong>] = 2 (+1 v\u00ec ai &lt;&gt; bj)\r\n<br>\r\nlev(a,b)(2,1) = min[lev(a,b)(1,1)+1, lev(a,b)(2,0)+1, lev(a,b)(1,0)<strong>+1<\/strong>] = 1 (+1 v\u00ec ai &lt;&gt; bj)\r\n<br>\r\n...\r\n<br>\r\n<strong>lev(a,b)(3,3)= min[lev(a,b)(2,3)+1, lev(a,b)(3,2)+1, lev(a,b)(2,2)+1] = 1 (+1 v\u00ec ai &lt;&gt; bj)<\/strong>\r\n<\/pre>\n<p>\nK\u1ebft qu\u1ea3 ta c\u00f3 \u0111\u01b0\u1ee3c l\u00e0 1.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Tim_chuoi_gan_giong_nhau_trong_Excel_voi_Levenshtein_distance\"><\/span>T\u00ecm chu\u1ed7i g\u1ea7n gi\u1ed1ng nhau trong Excel v\u1edbi Levenshtein distance<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>T\u1eeb b\u00e0i to\u00e1n tr\u00ean, ta c\u00f3 th\u1ec3 \u00e1p d\u1ee5ng v\u00e0o trong Excel \u0111\u1ec3 x\u00e2y d\u1ef1ng m\u1ed9t m\u00f4 h\u00ecnh so s\u00e1nh 2 chu\u1ed7i k\u00ed t\u1ef1 (v\u00e0 c\u00f2n c\u00f3 th\u1ec3 m\u1edf r\u1ed9ng th\u00eam cho nhi\u1ec1u chu\u1ed7i k\u00ed t\u1ef1). B\u00e0i to\u00e1n gi\u1ea3 \u0111\u1ecbnh \u0111\u01a1n gi\u1ea3n \u1edf \u0111\u00e2y l\u00e0 ta c\u00f3 2 chu\u1ed7i c\u1ea7n so s\u00e1nh \u0111\u1ed9 gi\u1ed1ng nhau. C\u00e1c b\u01b0\u1edbc ti\u1ebfn h\u00e0nh c\u1ee5 th\u1ec3:<\/p>\n<h4><span class=\"ez-toc-section\" id=\"Thiet_lap_vi_tri_nhap_chuoi\"><\/span>Thi\u1ebft l\u1eadp v\u1ecb tr\u00ed nh\u1eadp chu\u1ed7i<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Trong v\u00ed d\u1ee5 n\u00e0y, ta \u0111\u1eb7t chu\u1ed7i a v\u00e0o \u00f4 A1, chu\u1ed7i b v\u00e0o \u00f4 A2 trong trang t\u00ednh, ta quy \u0111\u1ecbnh \u0111\u00e2y l\u00e0 n\u01a1i c\u1ed1 \u0111\u1ecbnh ch\u1ee9a 2 chu\u1ed7i:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-38141 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-chuoi-gan-giong-nhau-2-1.png\" width=\"111\" height=\"107\" title=\"tim-chuoi-gan-giong-nhau-2-1\"><\/p>\n<h4><span class=\"ez-toc-section\" id=\"Thiet_lap_khung_cho_mo_hinh\"><\/span>Thi\u1ebft l\u1eadp khung cho m\u00f4 h\u00ecnh<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Ta s\u1ebd b\u1eaft \u0111\u1ea7u \u1edf v\u1ecb tr\u00ed 0 cho c\u1ea3 2 chu\u1ed7i. \u0110\u1ec3 \u0111\u00e1nh d\u1ea5u v\u1ecb tr\u00ed 0, \u0111\u1eb7t m\u1ed9t k\u00ed hi\u1ec7u b\u1ea5t k\u00ec (VD: #) v\u00e0o chi\u1ec1u ngang v\u00e0 chi\u1ec1u d\u1ecdc t\u1ea1i 2 \u00f4 D2 v\u00e0 C3:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-38142 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-chuoi-gan-giong-nhau-3-1.png\" width=\"233\" height=\"177\" title=\"tim-chuoi-gan-giong-nhau-3-1\"><\/p>\n<h4><span class=\"ez-toc-section\" id=\"Cat_chuoi\"><\/span>C\u1eaft chu\u1ed7i<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Ta s\u1ebd ti\u1ebfn h\u00e0nh c\u1eaft nh\u1ecf chu\u1ed7i th\u00e0nh c\u00e1c k\u00ed t\u1ef1 \u0111\u01a1n l\u1ebb v\u00e0 \u0111\u01b0a v\u00e0o t\u1eebng \u00f4. C\u00f3 r\u1ea5t nhi\u1ec1u c\u00f4ng th\u1ee9c c\u00f3 th\u1ec3 \u00e1p d\u1ee5ng \u0111\u1ec3 l\u00e0m \u0111i\u1ec1u n\u00e0y.<\/p>\n<p><em>V\u1edbi phi\u00ean b\u1ea3n Office 365, m\u00ecnh ch\u1ecdn c\u00f4ng th\u1ee9c <\/em><\/p>\n<pre><strong>=MID(A1,ROW(INDIRECT(\"1:\"&amp;LEN(A1))),1)<\/strong><\/pre>\n<p>\u00a0<em>cho chu\u1ed7i th\u1ee9 nh\u1ea5t t\u1ea1i \u00f4\u00a0<strong>C4<\/strong>, v\u00e0 <\/em><\/p>\n<pre><strong>=TRANSPOSE(MID(A2,ROW(INDIRECT(\"1:\"&amp;LEN(A2))),1))<\/strong><\/pre>\n<p>\ncho chu\u1ed7i th\u1ee9 2 t\u1ea1i \u00f4\u00a0<strong>E2<\/strong>.\u00a0Trong c\u00e1c phi\u00ean b\u1ea3n kh\u00e1c, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng <strong>c\u00f4ng th\u1ee9c m\u1ea3ng\u00a0<\/strong>l\u00e0m t\u01b0\u01a1ng t\u1ef1.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-38150 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-chuoi-gan-giong-nhau-4-1.png\" width=\"494\" height=\"206\" title=\"tim-chuoi-gan-giong-nhau-4-1\"><\/p>\n<h4><span class=\"ez-toc-section\" id=\"Dien_so\"><\/span>\u0110i\u1ec1n s\u1ed1<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>T\u1eeb \u0111\u00e2y, ta c\u00f3 th\u1ec3 b\u1eaft \u0111\u1ea7u \u0111i\u1ec1n s\u1ed1 v\u00e0o b\u1ea3ng.<\/p>\n<ul>\n<li>T\u1ea1i v\u1ecb tr\u00ed (0,0), b\u1edfi min(0,0) = 0 n\u00ean lev(a,b)(0,0) = max(0,0) = 0 =&gt; \u0111i\u1ec1n\u00a0<strong>0<\/strong> v\u00e0o \u00f4 D3<\/li>\n<li>T\u1ea1i c\u00e1c v\u1ecb tr\u00ed (1,0), (2,0), (3,0), (n,0)\u2026 v\u00e0 (0,1), (0,2), (0,3), (0,n)\u2026 kho\u1ea3ng c\u00e1ch Levenshtein ch\u00ednh b\u1eb1ng c\u00e1c s\u1ed1 1, 2, 3,\u2026 n, hay ch\u00ednh b\u1eb1ng <em>v\u1ecb tr\u00ed c\u1ee7a k\u00ed t\u1ef1 t\u1ea1i \u0111i\u1ec3m \u0111\u00f3.<\/em> Ta s\u1ebd s\u1eed d\u1ee5ng h\u00e0m \u0111\u1ec3 t\u1ea1o ra d\u00e3y s\u1ed1 n\u00e0y v\u1edbi b\u1eaft \u0111\u1ea7u l\u00e0 1 v\u00e0 k\u1ebft th\u00fac \u1edf \u0111\u1ed9 d\u00e0i c\u1ee7a chu\u1ed7i. C\u00f4ng th\u1ee9c c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng t\u1ea1i \u00f4 D4:\n<pre><em><strong>=ROW(INDIRECT(\"1:\"&amp;LEN(A1))),<\/strong><\/em><\/pre>\n<p>\nT\u1ea1i \u00f4 E3:\u00a0\n<\/p>\n<pre><em><strong>=TRANSPOSE(MID(A2,ROW(INDIRECT(\"1:\"&amp;LEN(A2))),1)).\u00a0<\/strong><\/em><\/pre>\n<p>\nS\u1eed d\u1ee5ng <strong>c\u00f4ng th\u1ee9c m\u1ea3ng\u00a0<\/strong>n\u1ebfu phi\u00ean b\u1ea3n Excel \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng kh\u00f4ng ph\u1ea3i l\u00e0 365.<\/p>\n<\/li>\n<li><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-38151 size-full aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-chuoi-gan-giong-nhau-5-1.png\" width=\"529\" height=\"216\" title=\"tim-chuoi-gan-giong-nhau-5-1\"><\/li>\n<li>V\u1edbi nh\u1eefng v\u1ecb tr\u00ed c\u00f2n l\u1ea1i, ta c\u00f3 th\u1ec3 nh\u00ecn ra quy t\u1eafc:<\/li>\n<\/ul>\n<pre><em>lev(a,b)(1,2) = min[lev(a,b)(0,2)+1, lev(a,b)(1,1)+1, lev(a,b)(0,1)<strong>+1<\/strong>] = 1 (+1 v\u00ec ai &lt;&gt; bj)<\/em>\r\n<br>\r\n<em>lev(a,b)(1,3) = min[lev(a,b)(0,3)+1, lev(a,b)(1,2)+1, lev(a,b)(0,2)<strong>+1<\/strong>] = 2 (+1 v\u00ec ai &lt;&gt; bj)<\/em>\r\n<br>\r\n<em>lev(a,b)(2,1) = min[lev(a,b)(1,1)+1, lev(a,b)(2,0)+1, lev(a,b)(1,0)<strong>+1<\/strong>] = 1 (+1 v\u00ec ai &lt;&gt; bj)<\/em><\/pre>\n<p>Trong ph\u1ea1m vi\u00a0<strong>4<\/strong> \u00f4, k\u1ebft qu\u1ea3 c\u1ea7n t\u00ecm \u1edf <strong>\u00f4 b\u00ean d\u01b0\u1edbi, g\u00f3c ph\u1ea3i\u00a0<\/strong>s\u1ebd l\u00e0 gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t d\u1ef1a theo gi\u00e1 tr\u1ecb c\u1ee7a 3 \u00f4 c\u00f2n l\u1ea1i theo quy t\u1eafc: gi\u00e1 tr\u1ecb <strong>\u00f4 th\u1ee9 2<\/strong> +1, gi\u00e1 tr\u1ecb <strong>\u00f4 th\u1ee9 3<\/strong> +1; gi\u00e1 tr\u1ecb <strong>\u00f4 th\u1ee9 1<\/strong> + 1 n\u1ebfu k\u00ed t\u1ef1 chu\u1ed7i a \u1edf v\u1ecb tr\u00ed i kh\u00e1c v\u1edbi k\u00ed t\u1ef1 chu\u1ed7i b \u1edf v\u1ecb tr\u00ed j v\u00e0 gi\u1eef nguy\u00ean trong tr\u01b0\u1eddng h\u1ee3p ng\u01b0\u1ee3c l\u1ea1i. N\u00f3i m\u1ed9t c\u00e1ch tr\u1ef1c quan, trong h\u00ecnh d\u01b0\u1edbi, \u0111\u1ec3 \u0111i\u1ec1n v\u00e0o \u00f4 E4, v\u00ec ai == bj = \u201cc\u201d, ta x\u00e9t 3 gi\u00e1 tr\u1ecb: D4+1,E3+1,D3. V\u00ec D3 = 0 nh\u1ecf nh\u1ea5t n\u00ean k\u1ebft qu\u1ea3 E4 =0.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-38152 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-chuoi-gan-giong-nhau-6-1.png\" width=\"920\" height=\"262\" title=\"tim-chuoi-gan-giong-nhau-6-1\"><\/p>\n<p>C\u00f4ng th\u1ee9c \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y nh\u01b0 sau (\u0111\u1eb7t v\u00e0o \u00f4 E4):<\/p>\n<pre><strong>=IF(ISTEXT($C4)*ISTEXT(E$2),IF(MIN($D4,E$3)=0,MAX($D4,E$3),MIN(D4+1,E3+1,IF(EXACT(LOWER($C4),LOWER(E$2)),D3,D3+1))),\"\")<\/strong><\/pre>\n<p>Nh\u1eefng v\u1ecb tr\u00ed c\u00f2n l\u1ea1i ta \u00e1p d\u1ee5ng t\u01b0\u01a1ng t\u1ef1 b\u1eb1ng c\u00e1ch k\u00e9o th\u1ea3 \u0111\u1ec3 \u0111i\u1ec1n d\u1eef li\u1ec7u v\u00e0o. B\u1ea1n c\u00f3 th\u1ec3 t\u1ea1o ra m\u1ed9t b\u1ea3ng v\u1edbi k\u00edch c\u1ee1 10\u00d710 ho\u1eb7c 20\u00d720 \u0111\u1ec3 x\u1eed l\u00fd chu\u1ed7i l\u1edbn h\u01a1n.<\/p>\n<p><em>*H\u00e0m LOWER \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y nh\u1eb1m x\u1eed l\u00fd k\u00fd t\u1ef1 hoa \u2013 th\u01b0\u1eddng. Ch\u1eb3ng h\u1ea1n:\u00a0<strong>KPMG\u00a0<\/strong>v\u00e0 kpmg n\u1ebfu d\u00f9ng thu\u1ea7n Levenshtein distance s\u1ebd c\u00f3 \u0111\u1ed9 kh\u00e1c bi\u1ec7t l\u00e0 4. Trong khi k\u1ebft h\u1ee3p v\u1edbi LOWER, \u0111\u1ed9 kh\u00e1c bi\u1ec7t l\u00e0 0.<\/em><\/p>\n<p>K\u1ebft qu\u1ea3:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-38153 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-chuoi-gan-giong-nhau-7-1.png\" width=\"1018\" height=\"248\" title=\"tim-chuoi-gan-giong-nhau-7-1\"><\/p>\n<h4><span class=\"ez-toc-section\" id=\"Tinh_ty_le_trung_khop\"><\/span>T\u00ednh t\u1ef7 l\u1ec7 tr\u00f9ng kh\u1edbp<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>V\u1eady kho\u1ea3ng c\u00e1ch Levenshtein c\u00f3 li\u00ean quan g\u00ec t\u1edbi vi\u1ec7c so s\u00e1nh 2 chu\u1ed7i c\u00f3 gi\u1ed1ng nhau kh\u00f4ng? Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, ta s\u1ebd t\u00ednh t\u1ef7 l\u1ec7 tr\u00f9ng kh\u1edbp b\u1eb1ng c\u00f4ng th\u1ee9c:<\/p>\n<p><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-3-7.png\" alt=\"Fuzzy Joins Tutorial | Python-bloggers\" title=\"image-3-7\"><\/p>\n<p>Di\u1ec5n gi\u1ea3i:<\/p>\n<p style=\"padding-left: 40px\"><strong>(\u0111\u1ed9 d\u00e0i chu\u1ed7i a + \u0111\u1ed9 d\u00e0i chu\u1ed7i b \u2013 kho\u1ea3ng c\u00e1ch Levenshtein 2 chu\u1ed7i)\/(\u0111\u1ed9 d\u00e0i chu\u1ed7i a + \u0111\u1ed9 d\u00e0i chu\u1ed7i b)<\/strong><\/p>\n<p>Ho\u1eb7c:<\/p>\n<p style=\"padding-left: 40px\"><strong>1 \u2013 kho\u1ea3ng c\u00e1ch Levenshtein 2 chu\u1ed7i\/t\u1ed5ng \u0111\u1ed9 d\u00e0i 2 chu\u1ed7i.<\/strong><\/p>\n<p>C\u00f4ng th\u1ee9c \u00e1p d\u1ee5ng trong Excel:<\/p>\n<pre><strong>=(LEN(A1)+LEN(A2)-INDIRECT(\"R\"&amp;LEN(A1)+3&amp;\"C\"&amp;LEN(A2)+4,FALSE))\/(LEN(A1)+LEN(A2))<\/strong><\/pre>\n<p>Trong \u0111\u00f3:<\/p>\n<ul>\n<li><strong>Len(A1), Len(A2)<\/strong>: \u0110\u1ed9 d\u00e0i 2 chu\u1ed7i t\u1ea1i A1 v\u00e0 A2<\/li>\n<li><strong>INDIRECT(\u201cR\u201d&amp;LEN(A1)+3&amp;\u201dC\u201d&amp;LEN(A2)+4,FALSE)<\/strong>: t\u00ecm ra gi\u00e1 tr\u1ecb t\u1ea1i \u00f4 cu\u1ed1i c\u00f9ng trong b\u1ea3ng \u2013 ch\u00ednh l\u00e0 kho\u1ea3ng c\u00e1ch Levenshtein.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-38154 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-chuoi-gan-giong-nhau-8-1.png\" alt=\"T\u00ecm chu\u1ed7i g\u1ea7n gi\u1ed1ng nhau trong Excel\" width=\"780\" height=\"237\" title=\"tim-chuoi-gan-giong-nhau-8-1\"><\/p>\n<\/p>\n<p>Qua h\u01b0\u1edbng d\u1eabn \u0111\u01a1n gi\u1ea3n n\u00e0y, H\u1ecdc Excel Online hy v\u1ecdng b\u1ea1n \u0111\u00e3 bi\u1ebft c\u00e1ch t\u1ef1 setup cho m\u00ecnh m\u1ed9t khung \u0111\u01a1n gi\u1ea3n \u0111\u1ec3 t\u00ecm chu\u1ed7i g\u1ea7n gi\u1ed1ng nhau trong Excel.<\/p>\n<p>Tham kh\u1ea3o c\u00e1ch t\u1ea1o Levenshtein distance trong VBA \u0111\u1ec3 so s\u00e1nh chu\u1ed7i h\u00e0ng lo\u1ea1t, nhi\u1ec1u chu\u1ed7i v\u1edbi nhau c\u00f9ng l\u00fac: <strong><a href=\"http:\/\/blog.ezworkapp.com\/sanh-chuoi-tim-chuoi-gan-dung-trong-excel.html\">So s\u00e1nh, t\u00ecm chu\u1ed7i g\u1ea7n \u0111\u00fang v\u1edbi Levenshtein trong VBA<\/a>.<\/strong><\/p>\n<p>Tham kh\u1ea3o c\u00e1ch s\u1eed d\u1ee5ng Power Query \u0111\u1ec3 gi\u1ea3i quy\u1ebft b\u00e0i to\u00e1n match c\u00e1c gi\u00e1 tr\u1ecb g\u1ea7n gi\u1ed1ng nhau: <a href=\"http:\/\/blog.ezworkapp.com\/group-doi-tuong-fuzzy-matching-trong-power-query.html\">Group \u0111\u1ed1i t\u01b0\u1ee3ng s\u1eed d\u1ee5ng Fuzzy Matching trong Power Query<\/a><\/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\/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-56.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":901,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-900","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\/900","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=900"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/900\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/901"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=900"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=900"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=900"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}