{"id":797,"date":"2024-12-02T02:50:34","date_gmt":"2024-12-02T02:50:34","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/hieu-ve-tim-kiem-xap-xi-trong-excel-mot-cach-de-toi-uu-hoa-toc-do\/"},"modified":"2024-12-02T02:50:34","modified_gmt":"2024-12-02T02:50:34","slug":"hieu-ve-tim-kiem-xap-xi-trong-excel-mot-cach-de-toi-uu-hoa-toc-do","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/hieu-ve-tim-kiem-xap-xi-trong-excel-mot-cach-de-toi-uu-hoa-toc-do\/","title":{"rendered":"Hi\u1ec3u v\u1ec1 t\u00ecm ki\u1ebfm x\u1ea5p x\u1ec9 trong Excel \u2013 m\u1ed9t c\u00e1ch \u0111\u1ec3 t\u1ed1i \u01b0u h\u00f3a t\u1ed1c \u0111\u1ed9"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p class=\"uk-text-meta tm-page-break tm-page-break-first-page\">Page 1 of 4<\/p>\n<p>Khi s\u1eed d\u1ee5ng Excel, ta \u0111\u00e3 qu\u00e1 quen thu\u1ed9c v\u1edbi nh\u1eefng h\u00e0m t\u00ecm ki\u1ebfm nh\u01b0 VLOOKUP, HLOOKUP, INDEX\/MATCH. M\u1edbi h\u01a1n n\u1eefa ta c\u00f3 XLOOKUP, XMATCH. L\u00e2u \u0111\u1eddi h\u01a1n c\u1ea3 l\u00e0 LOOKUP. C\u00e1c b\u1ea1n c\u00f3 \u0111\u1ec3 \u00fd r\u1eb1ng trong nh\u1eefng h\u00e0m n\u00e0y \u0111\u1ec1u c\u00f3 m\u1ed9t tham s\u1ed1 t\u00f9y ch\u1ecdn gi\u1eefa \u201cExact\u201d \u2013 t\u00ecm ch\u00ednh x\u00e1c v\u00e0 \u201cApproximate\u201d \u2013 t\u00ecm x\u1ea5p x\u1ec9 kh\u00f4ng? Trong b\u00e0i vi\u1ebft n\u00e0y, H\u1ecdc Excel Online s\u1ebd n\u00f3i v\u1ec1 t\u00ecm ki\u1ebfm x\u1ea5p x\u1ec9. Hi\u1ec3u \u0111\u01b0\u1ee3c c\u00e1ch Excel th\u1ef1c hi\u1ec7n vi\u1ec7c t\u00ecm ki\u1ebfm n\u00e0y, c\u00e1c b\u1ea1n s\u1ebd bi\u1ebft th\u00eam c\u00e1ch \u0111\u1ec3 t\u1ed1i \u01b0u h\u00f3a t\u1ed1c \u0111\u1ed9 file.<\/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-674d2079d69e6\" 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-1\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/mot-cach-de-toi-uu-hoa-toc-do.html#Gioi_thieu_ve_tim_kiem_voi_ham_MATCH\" title=\"Gi\u1edbi thi\u1ec7u v\u1ec1 t\u00ecm ki\u1ebfm v\u1edbi h\u00e0m MATCH\">Gi\u1edbi thi\u1ec7u v\u1ec1 t\u00ecm ki\u1ebfm v\u1edbi h\u00e0m MATCH<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-1\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/mot-cach-de-toi-uu-hoa-toc-do.html#Vi_sao_khi_tim_kiem_xap_xi_ta_can_phai_sap_xep_du_lieu_tu_nho_toi_lon_hoac_tu_lon_toi_nho\" title=\"V\u00ec sao khi t\u00ecm ki\u1ebfm x\u1ea5p x\u1ec9, ta c\u1ea7n ph\u1ea3i s\u1eafp x\u1ebfp d\u1eef li\u1ec7u t\u1eeb nh\u1ecf t\u1edbi l\u1edbn ho\u1eb7c t\u1eeb l\u1edbn t\u1edbi nh\u1ecf?\">V\u00ec sao khi t\u00ecm ki\u1ebfm x\u1ea5p x\u1ec9, ta c\u1ea7n ph\u1ea3i s\u1eafp x\u1ebfp d\u1eef li\u1ec7u t\u1eeb nh\u1ecf t\u1edbi l\u1edbn ho\u1eb7c t\u1eeb l\u1edbn t\u1edbi nh\u1ecf?<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-1\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"http:\/\/blog.ezworkapp.com\/mot-cach-de-toi-uu-hoa-toc-do.html\/4\/#Cach_xu_ly_neu_ket_qua_tim_kiem_trong_mang_khop_voi_nhieu_gia_tri_trung_lap\" title=\"C\u00e1ch x\u1eed l\u00fd n\u1ebfu k\u1ebft qu\u1ea3 t\u00ecm ki\u1ebfm trong m\u1ea3ng kh\u1edbp v\u1edbi nhi\u1ec1u gi\u00e1 tr\u1ecb tr\u00f9ng l\u1eb7p?\">C\u00e1ch x\u1eed l\u00fd n\u1ebfu k\u1ebft qu\u1ea3 t\u00ecm ki\u1ebfm trong m\u1ea3ng kh\u1edbp v\u1edbi nhi\u1ec1u gi\u00e1 tr\u1ecb tr\u00f9ng l\u1eb7p?<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-1\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"http:\/\/blog.ezworkapp.com\/mot-cach-de-toi-uu-hoa-toc-do.html\/4\/#Toi_uu_hoa_toc_do\" title=\"T\u1ed1i \u01b0u h\u00f3a t\u1ed1c \u0111\u1ed9\">T\u1ed1i \u01b0u h\u00f3a t\u1ed1c \u0111\u1ed9<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h1 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Gioi_thieu_ve_tim_kiem_voi_ham_MATCH\"><\/span>Gi\u1edbi thi\u1ec7u v\u1ec1 t\u00ecm ki\u1ebfm v\u1edbi h\u00e0m MATCH<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>Ch\u00fang ta s\u1ebd \u0111\u1ebfn v\u1edbi m\u1ed9t h\u00e0m r\u1ea5t quen thu\u1ed9c: MATCH. <\/p>\n<pre class=\"wp-block-code\"><code>=MATCH(lookup_value,lookup_array,[match_type])<\/code><\/pre>\n<p>MATCH s\u1ebd tr\u1ea3 v\u1ec1 v\u1ecb tr\u00ed c\u1ee7a m\u1ed9t gi\u00e1 tr\u1ecb trong m\u1ed9t m\u1ea3ng. Ch\u1eb3ng h\u1ea1n ta c\u00f3 m\u1ea3ng:<\/p>\n<pre class=\"wp-block-code\"><code>[\"a\",\"c\",\"b\",\"z\",\"e\",\"f\",\"g\"]<\/code><\/pre>\n<p>\u1ede \u0111\u00e2y, khi nh\u1eadp h\u00e0m MATCH \u0111\u1ec3 t\u00ecm ki\u1ebfm \u201cb\u201d, ta s\u1ebd vi\u1ebft:<\/p>\n<pre class=\"wp-block-code\"><code>=MATCH(\"b\",m\u1ea3ng_tr\u00ean,0)<\/code><\/pre>\n<p>K\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 l\u00e0 2, v\u00ec ph\u1ea7n t\u1eed \u201cb\u201d \u1edf v\u1ecb tr\u00ed th\u1ee9 2 t\u1eeb tr\u00e1i sang.<\/p>\n<p>T\u1ea1i sao <strong>match_type<\/strong> b\u1eaft bu\u1ed9c l\u1ea1i l\u00e0 0? B\u1edfi v\u00ec theo nh\u01b0 t\u00e0i li\u1ec7u c\u1ee7a Microsoft c\u0169ng nh\u01b0 theo \u201ckinh nghi\u1ec7m d\u00e2n gian\u201d, n\u1ebfu b\u1ecf tr\u1ed1ng ho\u1eb7c \u0111i\u1ec1n 1\/-1 th\u00ec k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 s\u1ebd sai ho\u1eb7c l\u1ed7i. <\/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-45.gif\" title=\"ex101_92019-nho-45\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>Gi\u1edd h\u00e3y \u0111\u1ebfn v\u1edbi v\u00ed d\u1ee5 ti\u1ebfp theo.<\/p>\n<p>M\u1ed9t v\u00ed d\u1ee5 kh\u00e1c, ta c\u00f3 m\u1ea3ng:<\/p>\n<pre class=\"wp-block-code\"><code>[5,10,15,20,25,30]<\/code><\/pre>\n<p>Khi th\u1ef1c hi\u1ec7n t\u00ecm ki\u1ebfm s\u1ed1 <strong>15<\/strong> v\u1edbi MATCH, ta ho\u00e0n to\u00e0n c\u00f3 th\u1ec3 vi\u1ebft:<\/p>\n<pre class=\"wp-block-code\"><code>=MATCH(15,m\u1ea3ng_tr\u00ean,0)\nho\u1eb7c\n=MATCH(15,m\u1ea3ng_tr\u00ean)\nho\u1eb7c\n=MATCH(15,m\u1ea3ng_tr\u00ean,1)<\/code><\/pre>\n<p>C\u1ea3 3 c\u00f4ng th\u1ee9c tr\u00ean \u0111\u1ec1u tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 <strong>3<\/strong>, ch\u00ednh l\u00e0 v\u1ecb tr\u00ed c\u1ee7a s\u1ed1 15 trong m\u1ea3ng. V\u1eady t\u1ea1i sao trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y l\u1ea1i c\u00f3 th\u1ec3 d\u00f9ng <strong>match_type = 1<\/strong>?<\/p>\n<p><strong>Ch\u00fang ta \u0111\u00e3 bi\u1ebft<\/strong> <strong>r\u1eb1ng n\u1ebfu \u0111\u1ec3 m\u1eb7c \u0111\u1ecbnh th\u00ec match_type s\u1ebd \u0111\u01b0\u1ee3c ng\u1ea7m hi\u1ec3u l\u00e0 1, v\u00e0 theo nh\u01b0 t\u00e0i li\u1ec7u c\u1ee7a Microsoft, n\u1ebfu ti\u1ebfn h\u00e0nh t\u00ecm ki\u1ebfm \u201cx\u1ea5p x\u1ec9\u201d v\u1edbi tham s\u1ed1 1, m\u1ea3ng c\u1ea7n ph\u1ea3i \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp theo th\u1ee9 t\u1ef1.<\/strong><\/p>\n<p>Qu\u1ea3 v\u1eady, n\u1ebfu m\u1ea3ng c\u1ee7a ta \u0111\u1ed5i th\u00e0nh <strong>[10,5,30,25,20,15]<\/strong> ho\u1eb7c s\u1eafp x\u1ebfp theo m\u1ed9t th\u1ee9 t\u1ef1 kh\u00e1c, th\u00ec t\u00ecm ki\u1ebfm x\u1ea5p x\u1ec9 s\u1ebd b\u1ecb sai.<\/p>\n<p><em><strong>Nh\u01b0ng \u0111\u00e3 bao gi\u1edd b\u1ea1n t\u1ef1 h\u1ecfi v\u00ec sao ch\u01b0a?<\/strong><\/em><\/p>\n<p>H\u00e3y \u0111\u1ecdc ti\u1ebfp nh\u00e9.<\/p>\n<h1 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Vi_sao_khi_tim_kiem_xap_xi_ta_can_phai_sap_xep_du_lieu_tu_nho_toi_lon_hoac_tu_lon_toi_nho\"><\/span>V\u00ec sao khi t\u00ecm ki\u1ebfm x\u1ea5p x\u1ec9, ta c\u1ea7n ph\u1ea3i s\u1eafp x\u1ebfp d\u1eef li\u1ec7u t\u1eeb nh\u1ecf t\u1edbi l\u1edbn ho\u1eb7c t\u1eeb l\u1edbn t\u1edbi nh\u1ecf?<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>M\u00ecnh \u0111\u00e3 \u0111i h\u1ecfi nh\u1eefng ng\u01b0\u1eddi \u201ctrong ng\u00e0nh\u201d v\u00e0 bi\u1ebft \u0111\u01b0\u1ee3c m\u1ed9t \u0111i\u1ec1u r\u1eb1ng, vi\u1ec7c ch\u1ecdn <strong>match_type <\/strong>(c\u0169ng nh\u01b0 <strong>lookup_type<\/strong>) gi\u1ed1ng nh\u01b0 trigger m\u1ed9t c\u00e1i c\u00f4ng t\u1eafc v\u1ec1 l\u1ef1a ch\u1ecdn <strong>thu\u1eadt to\u00e1n t\u00ecm ki\u1ebfm<\/strong>.<\/p>\n<p>V\u1edbi type = 0, Excel s\u1ebd s\u1eed d\u1ee5ng <strong>Linear Search.<\/strong><\/p>\n<p>V\u1edbi type = 1, Excel s\u1ebd s\u1eed d\u1ee5ng <strong>Binary Search<\/strong>.<\/p>\n<p>V\u1edbi c\u00e1c h\u00e0m nh\u01b0 MATCH, XLOOKUP, XMATCH, ta c\u00f3 th\u00eam t\u00f9y ch\u1ecdn -1, c\u0169ng v\u1eabn l\u00e0 <strong>Binary Search<\/strong> v\u1edbi m\u1ea3ng \u0111\u01b0\u1ee3c x\u1ebfp ng\u01b0\u1ee3c l\u1ea1i.<\/p>\n<p>C\u00e1c b\u1ea1n c\u00f3 th\u1ec3 google 2 t\u1eeb kh\u00f3a tr\u00ean \u0111\u1ec3 hi\u1ec3u.<\/p>\n<p>V\u00e0 ch\u00ednh v\u00ec <strong>Binary Search<\/strong> ch\u1ec9 ho\u1ea1t \u0111\u1ed9ng \u0111\u00fang khi m\u1ea3ng \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp t\u1eeb nh\u1ecf t\u1edbi l\u1edbn, n\u00ean trong c\u00e1c t\u00e0i li\u1ec7u c\u1ee7a Excel lu\u00f4n y\u00eau c\u1ea7u ta ph\u1ea3i s\u1eafp x\u1ebfp nh\u01b0 v\u1eady \u0111\u1ec3 k\u1ebft qu\u1ea3 kh\u00f4ng sai.<\/p>\n<p><strong>C\u00e1c b\u1ea1n c\u00f3 th\u1ec3 ng\u1eebng \u0111\u1ecdc<\/strong> <strong>\u1edf \u0111\u00e2y. L\u00fd do \u0111\u00e3 \u0111\u01b0\u1ee3c n\u00eau l\u00ean r\u1ed3i.<\/strong><\/p>\n<p>Nh\u01b0ng t\u1ea1i sao kh\u00f4ng \u0111\u00e0o s\u00e2u th\u00eam n\u1eefa nh\u1ec9? C\u00e1c b\u1ea1n c\u00f3 h\u1ee9ng th\u00fa \u0111\u1ecdc ti\u1ebfp ch\u1ee9?<\/p>\n<p><em>Ph\u1ea7n d\u01b0\u1edbi \u0111\u00e2y l\u00e0 m\u1ed9t \u201cnh\u1eadn \u0111\u1ecbnh\u201d \u2013 assumption c\u1ee7a m\u00ecnh. G\u1ecdi l\u00e0 nh\u1eadn \u0111\u1ecbnh v\u00ec Microsoft kh\u00f4ng chia s\u1ebb t\u00e0i s\u1ea3n s\u1edf h\u1eefu tr\u00ed tu\u1ec7 \u2013 intellectual property (c\u00e1ch c\u00e1c k\u0129 s\u01b0 \u0111\u00e3 vi\u1ebft n\u00ean c\u00e1c h\u00e0m)<\/em>.<\/p>\n<div class=\"uk-margin-medium\">\n<div class=\"uk-text-default\">\n            <a href=\"http:\/\/blog.ezworkapp.com\/mot-cach-de-toi-uu-hoa-toc-do.html\/2\" class=\"post-page-numbers uk-button uk-button-secondary\">            Next Page            <\/a>\n        <\/div>\n<div class=\"uk-grid-small uk-flex-middle uk-child-width-auto uk-text-default uk-margin-top\">\n<div>\n            Page        <\/div>\n<div>\n<ul class=\"uk-pagination\">\n<li class=\"uk-active\"><span>1<\/span><\/li>\n<li><a href=\"http:\/\/blog.ezworkapp.com\/mot-cach-de-toi-uu-hoa-toc-do.html\/2\" class=\"post-page-numbers\">2<\/a><\/li>\n<li><a href=\"http:\/\/blog.ezworkapp.com\/mot-cach-de-toi-uu-hoa-toc-do.html\/3\" class=\"post-page-numbers\">3<\/a><\/li>\n<li><a href=\"http:\/\/blog.ezworkapp.com\/mot-cach-de-toi-uu-hoa-toc-do.html\/4\" class=\"post-page-numbers\">4<\/a><\/li>\n<\/ul><\/div>\n<\/p><\/div>\n<\/div>\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-45.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":798,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-797","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\/797","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=797"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/797\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/798"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=797"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=797"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=797"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}