{"id":5341,"date":"2024-12-14T06:59:43","date_gmt":"2024-12-14T06:59:43","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/14\/tim-gia-tri-gan-dung-nhat-bang-vba\/"},"modified":"2024-12-14T06:59:43","modified_gmt":"2024-12-14T06:59:43","slug":"tim-gia-tri-gan-dung-nhat-bang-vba","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/14\/tim-gia-tri-gan-dung-nhat-bang-vba\/","title":{"rendered":"T\u00ecm gi\u00e1 tr\u1ecb g\u1ea7n \u0111\u00fang nh\u1ea5t b\u1eb1ng VBA"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-dQf5b79p#1\">\n<p>B\u1ea1n s\u1ebd l\u00e0m th\u1ebf n\u00e0o n\u1ebfu g\u1eb7p ph\u1ea3i y\u00eau c\u1ea7u \u201cT\u00ecm gi\u00e1 tr\u1ecb g\u1ea7n\u00a0\u0111\u00fang nh\u1ea5t c\u1ee7a 1 s\u1ed1 trong 1 d\u00e3y s\u1ed1 b\u1ea5t k\u1ef3 trong Excel?\u201d\u00a0\u0110\u00e2y ch\u1eafc ch\u1eafn l\u00e0 1 y\u00eau c\u1ea7u r\u1ea5t kh\u00f3, b\u1edfi ch\u1eb3ng c\u00f3 h\u00e0m\u00a0Excel n\u00e0o c\u00f3 th\u1ec3 gi\u00fap b\u1ea1n tr\u1ea3 l\u1eddi\u00a0\u0111\u01b0\u1ee3c c\u00e2u h\u1ecfi n\u00e0y.<\/p>\n<p>Tuy nhi\u00ean h\u00f4m nay H\u1ecdc\u00a0Excel Online s\u1ebd gi\u00fap b\u1ea1n tr\u1ea3 l\u1eddi c\u00e2u h\u1ecfi\u00a0\u0111\u00f3\u00a0m\u1ed9t\u00a0c\u00e1ch d\u1ec5 d\u00e0ng b\u1eb1ng vi\u1ec7c s\u1eed d\u1ee5ng 1 c\u00e2u l\u1ec7nh trong VBA. C\u00e2u l\u1ec7nh\u00a0\u0111\u00f3 c\u00f3 kh\u00f3 kh\u00f4ng? H\u00e3y c\u00f9ng t\u00ecm hi\u1ec3u th\u00f4ng qua v\u00ed d\u1ee5 sau:<\/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-675d2cde8e872\" 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-gia-tri-gan-dung-nhat-bang-vba.html#Yeu_cau\" title=\"Y\u00eau c\u1ea7u\">Y\u00eau c\u1ea7u<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/tim-gia-tri-gan-dung-nhat-bang-vba.html#Cach_thuc_hien\" title=\"C\u00e1ch th\u1ef1c hi\u1ec7n\">C\u00e1ch th\u1ef1c hi\u1ec7n<\/a><\/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-gia-tri-gan-dung-nhat-bang-vba.html#2_Xay_dung_lenh_tu_dong_trong_VBA\" title=\"2. X\u00e2y d\u1ef1ng l\u1ec7nh t\u1ef1\u00a0\u0111\u1ed9ng trong VBA\">2. X\u00e2y d\u1ef1ng l\u1ec7nh t\u1ef1\u00a0\u0111\u1ed9ng trong VBA<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/tim-gia-tri-gan-dung-nhat-bang-vba.html#3_Gan_lenh_VBA_trong_su_kien_Thay_doi_gia_tri_tai_E2\" title=\"3. G\u00e1n l\u1ec7nh VBA trong s\u1ef1 ki\u1ec7n Thay\u00a0\u0111\u1ed5i gi\u00e1 tr\u1ecb t\u1ea1i E2\">3. G\u00e1n l\u1ec7nh VBA trong s\u1ef1 ki\u1ec7n Thay\u00a0\u0111\u1ed5i gi\u00e1 tr\u1ecb t\u1ea1i E2<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Yeu_cau\"><\/span>Y\u00eau c\u1ea7u<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>T\u00ecm s\u1ed1 g\u1ea7n\u00a0\u0111\u00fang nh\u1ea5t trong v\u00f9ng A3:A23 so v\u1edbi gi\u00e1 tr\u1ecb t\u1ea1i\u00a0\u00f4\u00a0E2<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14514\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-closestmatch-01.png\" width=\"438\" height=\"604\" title=\"heo-closestmatch-01\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Cach_thuc_hien\"><\/span>C\u00e1ch th\u1ef1c hi\u1ec7n<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><strong>1. T\u00ecm hi\u1ec3u v\u1ec1 thu\u1eadt to\u00e1n (t\u00ednh logic)<\/strong><\/p>\n<p>S\u1ed1 g\u1ea7n\u00a0\u0111\u00fang nh\u1ea5t c\u00f3\u00a0\u0111\u1eb7c\u00a0\u0111i\u1ec3m l\u00e0 c\u00f3\u00a0<strong>\u0111\u1ed9 l\u1ec7ch so v\u1edbi m\u1eabu l\u00e0\u00a0\u00edt nh\u1ea5t\u00a0 (hay nh\u1ecf nh\u1ea5t)<\/strong><\/p>\n<p>C\u00e1c b\u01b0\u1edbc th\u1ef1c\u00a0 hi\u1ec7n thu\u1eadt to\u00e1n n\u00e0y nh\u01b0 sau:<\/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-185.gif\" title=\"vba101_92019-nho-185\"><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-459.gif\" title=\"ex101_92019-nho-459\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14530\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-closestmatch-05.png\" width=\"1114\" height=\"601\" title=\"heo-closestmatch-05\"><\/p>\n<p>\u00a0<\/p>\n<p><strong>B\u01b0\u1edbc 1<\/strong>: X\u00e9t\u00a0\u0111\u1ed9 l\u1ec7ch v\u1edbi t\u1eebng gi\u00e1 tr\u1ecb c\u1ee7a d\u00e3y<\/p>\n<p>T\u1ea1i c\u1ed9t B\u00a0\u0111\u1eb7t c\u00f4ng th\u1ee9c : l\u1ea5y s\u1ed1 l\u00e0m m\u1eabu tr\u1eeb m\u1ed7i gi\u00e1 tr\u1ecb trong d\u00e3y\u00a0\u1edf c\u1ed9t A<\/p>\n<blockquote>\n<p>B3=$E$2-A3<\/p>\n<\/blockquote>\n<p>V\u00ec v\u1edbi nh\u1eefng s\u1ed1 l\u1edbn h\u01a1n s\u1ed1 m\u1eabu, n\u00ean s\u1eed d\u1ee5ng h\u00e0m ABS\u00a0\u0111\u1ec3 quy h\u1ebft\u00a0\u0111\u1ed9 l\u1ec7ch v\u1ec1 gi\u00e1 tr\u1ecb tuy\u1ec7t\u00a0\u0111\u1ed1i\u00a0\u0111\u1ec3 c\u00f3 th\u1ec3 x\u00e9t c\u1ea3 c\u1eadn tr\u00ean v\u00e0 c\u1eadn d\u01b0\u1edbi.<\/p>\n<blockquote>\n<p>B3=ABS($E$2-A3)<\/p>\n<\/blockquote>\n<p>\u00c1p d\u1ee5ng c\u00f4ng th\u1ee9c cho v\u00f9ng t\u1eeb B3 t\u1edbi B23 ta\u00a0\u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3\u00a0\u1edf c\u1ed9t B<\/p>\n<p>\u00a0<\/p>\n<p><strong>B\u01b0\u1edbc 2<\/strong>: X\u00e9t\u00a0\u0111\u1ed9 l\u1ec7ch nh\u1ecf nh\u1ea5t (v\u1edbi c\u1eadn d\u01b0\u1edbi \u2014 N\u1ebfu l\u1ea5y c\u1eadn tr\u00ean th\u00ec x\u00e9t\u00a0\u0111\u1ed9 l\u1ec7ch l\u1edbn nh\u1ea5t)<\/p>\n<p>S\u1eed d\u1ee5ng h\u00e0m MIN\u00a0\u0111\u1ec3 t\u00ecm gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t (\u00f4 J6)<\/p>\n<p><strong>B\u01b0\u1edbc 3<\/strong>: Tham chi\u1ebfu t\u1edbi s\u1ed1 c\u00f3\u00a0\u0111\u1ed9 l\u1ec7ch nh\u1ecf nh\u1ea5t b\u1eb1ng h\u00e0m Index+Match (\u00f4 J7)<\/p>\n<p>Nh\u01b0 v\u1eady ch\u00fang ta\u00a0\u0111\u00e3 x\u00e1c\u00a0\u0111\u1ecbnh\u00a0\u0111\u01b0\u1ee3c thu\u1eadt to\u00e1n v\u1edbi t\u1eebng b\u01b0\u1edbc th\u1ef1c\u00a0 hi\u1ec7n m\u1ed9t c\u00e1ch th\u1ee7 c\u00f4ng.\u00a0\u0110\u00e2y l\u00e0 b\u01b0\u1edbc r\u1ea5t quan tr\u1ecdng gi\u00fap ch\u00fang ta ph\u00e1t tri\u1ec3n t\u01b0 duy l\u1eadp tr\u00ecnh trong VBA<\/p>\n<h3><span class=\"ez-toc-section\" id=\"2_Xay_dung_lenh_tu_dong_trong_VBA\"><\/span>2. X\u00e2y d\u1ef1ng l\u1ec7nh t\u1ef1\u00a0\u0111\u1ed9ng trong VBA<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>C\u00e2u l\u1ec7nh VBA v\u00e0 logic\u00a0\u0111\u01b0\u1ee3c di\u1ec5n gi\u1ea3i trong c\u00e2u l\u1ec7nh n\u00e0y nh\u01b0 sau:<\/p>\n<\/p>\n<div class=\"oembed-gist\">View the code on <a href=\"https:\/\/gist.github.com\/DuongAQ\/73b71820314206df2d8bc309f0943666\">Gist<\/a>.<\/div>\n<p>Ch\u1ea1y th\u1eed c\u00e2u l\u1ec7nh tr\u00ean ch\u00fang ta thu\u00a0\u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14522\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-closestmatch-03.png\" width=\"1000\" height=\"597\" title=\"heo-closestmatch-03\"><\/p>\n<p>Ch\u00fa\u00a0\u00fd: Trong v\u00f9ng A3:B23\u00a0 c\u00f3\u00a0\u00e1p d\u1ee5ng ch\u1ee9c n\u0103ng Conditional formatting\u00a0\u0111\u1ec3 l\u00e0m n\u1ed5i b\u1eadt v\u1ecb tr\u00ed d\u00f2ng c\u00f3 ch\u1eef Match\u00a0\u1edf c\u1ed9t B, gi\u00fap ch\u00fang ta d\u1ec5 d\u00e0ng nh\u1eadn ra gi\u00e1 tr\u1ecb k\u1ebft qu\u1ea3 c\u1ea7n t\u00ecm.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"3_Gan_lenh_VBA_trong_su_kien_Thay_doi_gia_tri_tai_E2\"><\/span>3. G\u00e1n l\u1ec7nh VBA trong s\u1ef1 ki\u1ec7n Thay\u00a0\u0111\u1ed5i gi\u00e1 tr\u1ecb t\u1ea1i E2<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>\u0110\u1ec3 m\u1ed7i l\u1ea7n thay\u00a0\u0111\u1ed5i gi\u00e1 tr\u1ecb t\u1ea1i\u00a0\u00f4 E2 th\u00ec\u00a0Excel s\u1ebd t\u1ef1\u00a0\u0111\u1ed9ng ch\u1ea1y c\u00e2u l\u1ec7nh Macro tr\u00ean\u00a0\u0111\u1ec3 t\u00ecm k\u1ebft qu\u1ea3 m\u1ed9t c\u00e1ch t\u1ef1\u00a0\u0111\u1ed9ng, ch\u00fang ta t\u1ea1o s\u1ef1 ki\u1ec7n\u00a0WorkSheet_Change cho Sheet nh\u01b0 sau:<\/p>\n<p><strong>B\u01b0\u1edbc 1<\/strong>: m\u1edf c\u1eeda s\u1ed5 VBA, trong m\u1ee5c Properties b\u1ea5m chu\u1ed9t ch\u1ecdn Sheet ch\u1ee9a n\u1ed9i dung c\u1ea7n th\u1ef1c\u00a0 hi\u1ec7n v\u00e0 ch\u1ecdn s\u1ef1 ki\u1ec7n Change<\/p>\n<p><strong>B\u01b0\u1edbc 2<\/strong>: Ch\u1ec9 th\u1ef1c hi\u1ec7n l\u1ec7nh macro khi c\u00f3 s\u1ef1 thay\u00a0\u0111\u1ed5i t\u1ea1i\u00a0\u00f4 E2. Do\u00a0\u0111\u00f3 th\u1ef1c hi\u1ec7n c\u00e2u l\u1ec7nh sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14527\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-closestmatch-04.png\" width=\"1204\" height=\"230\" title=\"heo-closestmatch-04\"><\/p>\n<p>C\u00e2u l\u1ec7nh tr\u00ean\u00a0\u0111\u01b0\u1ee3c hi\u1ec3u l\u00e0:<\/p>\n<p>N\u1ebfu c\u00f3 s\u1ef1 thay\u00a0\u0111\u1ed5i\u00a0\u1edf v\u00f9ng E2 th\u00ec th\u1ef1c hi\u1ec7n l\u1ec7nh macro T\u00ecm s\u1ed1 g\u1ea7n\u00a0\u0111\u00fang<\/p>\n<hr>\n<p>T\u1ea3i v\u1ec1 file m\u1eabu: <a href=\"https:\/\/drive.google.com\/open?id=1oYRg9_MataD_SU-AoA434la9W2SW18sI\">T\u00ecm gi\u00e1 tr\u1ecb g\u1ea7n \u0111\u00fang nh\u1ea5t<\/a><\/p>\n<p>Ch\u00fac c\u00e1c b\u1ea1n h\u1ecdc t\u1ed1t c\u00f9ng H\u1ecdc\u00a0Excel Online!<\/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-185.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":5342,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-5341","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-nang-cao"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5341","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=5341"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5341\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/5342"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=5341"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=5341"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=5341"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}