{"id":7133,"date":"2024-12-21T16:55:51","date_gmt":"2024-12-21T16:55:51","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/ham-filter-ham-su-dung-de-loc-du-lieu\/"},"modified":"2024-12-21T16:55:51","modified_gmt":"2024-12-21T16:55:51","slug":"ham-filter-ham-su-dung-de-loc-du-lieu","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/ham-filter-ham-su-dung-de-loc-du-lieu\/","title":{"rendered":"H\u00e0m FILTER, h\u00e0m s\u1eed d\u1ee5ng \u0111\u1ec3 l\u1ecdc d\u1eef li\u1ec7u"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><em><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-su-dung-ham-filter-trong-excel-office-365.html\"><strong>H\u00e0m FILTER<\/strong><\/a> l\u00e0 m\u1ed9t h\u00e0m m\u1edbi c\u00f3 m\u1eb7t trong phi\u00ean b\u1ea3n Excel 365 s\u1ebd gi\u00fap ch\u00fang ta l\u1ecdc d\u1eef li\u1ec7u theo \u0111i\u1ec1u ki\u1ec7n m\u1ed9t c\u00e1ch d\u1ec5 d\u00e0ng. B\u00e0i vi\u1ebft n\u00e0y s\u1ebd h\u01b0\u1edbng d\u1eabn b\u1ea1n <strong>c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m FILTER<\/strong> m\u1ed9t c\u00e1ch chi ti\u1ebft k\u00e8m theo v\u00ed d\u1ee5.<\/em><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2020\/03\/ham-filter-loc-du-lieu-trong-excel.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-31634\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ham-filter-loc-du-lieu-trong-excel.png\" alt=\"H\u00e0m Filter l\u1ecdc d\u1eef li\u1ec7u trong Excel\" width=\"1786\" height=\"954\" title=\"ham-filter-loc-du-lieu-trong-excel\"><\/a><\/p>\n<p>Xem th\u00eam: <strong><a href=\"http:\/\/blog.ezworkapp.com\/cach-loc-du-lieu-trong-excel.html\">AutoFilter<\/a> l\u1ecdc d\u1eef li\u1ec7u trong Excel<\/strong><\/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-6766f31632bab\" 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-2\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/ham-filter-cach-su-dung-de-loc-du-lieu.html#Ham_FILTER_%E2%80%93_cu_phap_va_cach_su_dung\" title=\"H\u00e0m FILTER \u2013 c\u00fa ph\u00e1p v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng\">H\u00e0m FILTER \u2013 c\u00fa ph\u00e1p v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/ham-filter-cach-su-dung-de-loc-du-lieu.html#Ham_FILTER_%E2%80%93_cac_vi_du\" title=\"H\u00e0m FILTER \u2013 c\u00e1c v\u00ed d\u1ee5\">H\u00e0m FILTER \u2013 c\u00e1c v\u00ed d\u1ee5<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/ham-filter-cach-su-dung-de-loc-du-lieu.html#Loc_theo_1_dieu_kien\" title=\"L\u1ecdc theo 1 \u0111i\u1ec1u ki\u1ec7n\">L\u1ecdc theo 1 \u0111i\u1ec1u ki\u1ec7n<\/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\/ham-filter-cach-su-dung-de-loc-du-lieu.html#Loc_theo_nhieu_dieu_kien\" title=\"L\u1ecdc theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n\">L\u1ecdc theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Ham_FILTER_%E2%80%93_cu_phap_va_cach_su_dung\"><\/span><span style=\"font-size: 14pt\">H\u00e0m FILTER \u2013 c\u00fa ph\u00e1p v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>C\u00fa ph\u00e1p c\u1ee7a h\u00e0m FILTER nh\u01b0 sau:<\/p>\n<p><code class=\"excel-formula\">=FILTER (array, include, [if_empty])<\/code><\/p>\n<p>Trong \u0111\u00f3:<\/p>\n<ul style=\"list-style-type: square\">\n<li><code>array<\/code> l\u00e0 v\u00f9ng d\u1eef li\u1ec7u ngu\u1ed3n<\/li>\n<li><code>inclue<\/code> l\u00e0 1 m\u1ea3ng gi\u00e1 tr\u1ecb \u0111\u00fang sai. D\u1ef1a tr\u00ean m\u1ea3ng n\u00e0y, h\u00e0m s\u1ebd quy\u1ebft \u0111\u1ecbnh d\u00f2ng d\u1eef li\u1ec7u n\u00e0o c\u00f3 m\u1eb7t trong k\u1ebft qu\u1ea3 l\u1ecdc<\/li>\n<li><code>[if_empty]<\/code> gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1 trong tr\u01b0\u1eddng h\u1ee3p h\u00e0m kh\u00f4ng t\u00ecm \u0111\u01b0\u1ee3c d\u1eef li\u1ec7u tho\u1ea3 m\u00e3n \u0111i\u1ec1u ki\u1ec7n l\u1ecdc (tham s\u1ed1 kh\u00f4ng b\u1eaft bu\u1ed9c)<\/li>\n<\/ul>\n<p>M\u1ed9t s\u1ed1 l\u01b0u \u00fd:<\/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-642.gif\" title=\"ex101_92019-nho-642\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<ul style=\"list-style-type: square\">\n<li>H\u00e0m FILTER c\u00f3 t\u00ednh \u0111\u1ed9ng, ngh\u0129a l\u00e0 khi d\u1eef li\u1ec7u ngu\u1ed3n thay \u0111\u1ed5i, th\u00ec k\u1ebft qu\u1ea3 c\u1ee7a h\u00e0m c\u0169ng thay \u0111\u1ed5i theo ngay l\u1eadp t\u1ee9c m\u1ed9t c\u00e1ch t\u1ef1 \u0111\u1ed9ng<\/li>\n<li>K\u00edch th\u01b0\u1edbc c\u1ee7a m\u1ea3ng d\u1eef li\u1ec7u ngu\u1ed3n (s\u1ed1 d\u00f2ng) v\u00e0 s\u1ed1 d\u00f2ng c\u1ee7a v\u00f9ng \u0111i\u1ec1u ki\u1ec7n include ph\u1ea3i t\u01b0\u01a1ng \u0111\u1ed3ng v\u1edbi nhau \u0111\u1ec3 c\u00f4ng th\u1ee9c kh\u00f4ng b\u1ecb l\u1ed7i<\/li>\n<li>N\u1ebfu b\u1ea1n s\u1eed d\u1ee5ng h\u00e0m Filter v\u1edbi d\u1eef li\u1ec7u \u1edf 1 Workbook kh\u00e1c, th\u00ec workbook n\u00e0y c\u1ea7n \u0111\u01b0\u1ee3c m\u1edf, n\u1ebfu kh\u00f4ng, s\u1ebd c\u00f3 l\u1ed7i #REF!<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Ham_FILTER_%E2%80%93_cac_vi_du\"><\/span><span style=\"font-size: 14pt\">H\u00e0m FILTER \u2013 c\u00e1c v\u00ed d\u1ee5<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"Loc_theo_1_dieu_kien\"><\/span><span style=\"font-size: 12pt\">L\u1ecdc theo 1 \u0111i\u1ec1u ki\u1ec7n<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Trong \u1ea3nh v\u00ed d\u1ee5 \u1edf \u0111\u1ea7u b\u00e0i vi\u1ebft, c\u00f4ng th\u1ee9c trong \u00f4 G5 l\u00e0<\/p>\n<p><code class=\"excel-formula\">=FILTER(B5:D13,D5:D13=H2,\"Kh\u00f4ng t\u00ecm th\u1ea5y\")<\/code><\/p>\n<p>Ch\u00fang ta c\u00f3 th\u1ec3 di\u1ec5n gi\u1ea3i c\u00f4ng th\u1ee9c n\u00e0y ra m\u1ed9t c\u00e1ch d\u1ec5 hi\u1ec3u l\u00e0: l\u1ecdc trong v\u00f9ng B5:D13 nh\u1eefng d\u00f2ng m\u00e0 c\u1ed9t D c\u00f3 gi\u00e1 tr\u1ecb l\u00e0 Red.<\/p>\n<p>Ch\u00fang ta c\u00f3 th\u1ec3 vi\u1ebft c\u00f4ng th\u1ee9c tr\u00ean nh\u01b0 sau (vi\u1ebft \u0111i\u1ec1u ki\u1ec7n tr\u1ef1c ti\u1ebfp v\u00e0o c\u00f4ng th\u1ee9c)<\/p>\n<p><code class=\"excel-formula\">=FILTER(B5:D13,D5:D13=\"Red\",\"Kh\u00f4ng t\u00ecm th\u1ea5y\")<\/code><\/p>\n<p>Tuy nhi\u00ean vi\u1ec7c vi\u1ebft \u0111i\u1ec1u ki\u1ec7n tr\u1ef1c ti\u1ebfp v\u00e0o c\u00f4ng th\u1ee9c n\u00e0y l\u00e0 kh\u00f4ng n\u00ean, b\u1edfi v\u00ec khi \u0111i\u1ec1u ki\u1ec7n thay \u0111\u1ed5i, ch\u00fang ta ph\u1ea3i v\u00e0o trong c\u00f4ng th\u1ee9c \u0111\u1ec3 ch\u1ec9nh s\u1eeda. Do v\u1eady, vi\u1ec7c \u0111\u1ec3 \u0111i\u1ec1u ki\u1ec7n \u1edf 1 \u00f4 b\u00ean ngo\u00e0i c\u00f4ng th\u1ee9c, sau \u0111\u00f3 tham chi\u1ebfu t\u1edbi \u00f4 \u0111\u00f3 s\u1ebd gi\u00fap ch\u00fang ta c\u00f3 c\u00f4ng th\u1ee9c linh ho\u1ea1t h\u01a1n.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Loc_theo_nhieu_dieu_kien\"><\/span><span style=\"font-size: 12pt\">L\u1ecdc theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n ph\u1ee9c t\u1ea1p \u0111\u1ec3 l\u1ecdc d\u1eef li\u1ec7u v\u1edbi h\u00e0m FILTER. Gi\u1ea3 s\u1eed, trong v\u00f9ng d\u1eef li\u1ec7u B5:D13, ch\u00fang ta mu\u1ed1n gi\u1eef l\u1ea1i nh\u1eefng d\u00f2ng d\u1eef li\u1ec7u tho\u1ea3 m\u00e3n 2 ti\u00eau ch\u00ed sau \u0111\u00e2y c\u00f9ng l\u00fac:<\/p>\n<ul style=\"list-style-type: square\">\n<li>C\u1ed9t D c\u00f3 gi\u00e1 tr\u1ecb l\u00e0 Red<\/li>\n<li>C\u1ed9t C c\u00f3 gi\u00e1 tr\u1ecb l\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng 80<\/li>\n<\/ul>\n<p>C\u00f4ng th\u1ee9c ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y l\u00e0:<\/p>\n<p><code class=\"excel-formula\">=FILTER(B5:D13,(D5:D13=H2)*(C5:C13&gt;=80),\"Kh\u00f4ng t\u00ecm th\u1ea5y\")<\/code><\/p>\n<p>C\u00e1ch vi\u1ebft v\u00e0 hi\u1ec3u \u0111o\u1ea1n\u00a0<code class=\"excel-formula\">(D5:D13=H2)*(C5:C13&gt;=80)<\/code> , b\u1ea1n c\u00f3 th\u1ec3 t\u00ecm hi\u1ec3u th\u00eam trong b\u00e0i vi\u1ebft v\u1ec1 SUMPRODUCT sau \u0111\u00e2y:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/bi-kip-vo-lam-sumproduct-than-chuong.html\" target=\"_blank\" rel=\"noopener noreferrer\">B\u00ed k\u00edp v\u00f5 l\u00e2m 2: SUMPRODUCT th\u1ea7n ch\u01b0\u1edfng<\/a><\/p>\n<p>N\u1ed9i dung c\u1ee7a b\u00e0i vi\u1ebft n\u00e0y c\u00f2n \u0111\u01b0\u1ee3c tr\u00ecnh b\u00e0y chi ti\u1ebft h\u01a1n v\u00e0 gi\u1ea3i th\u00edch r\u00f5 h\u01a1n trong video sau \u0111\u00e2y. \u0110\u1eebng qu\u00ean theo d\u00f5i <a href=\"http:\/\/blog.ezworkapp.com\"><strong>H\u1ecdc Excel Online<\/strong> <\/a>cho nh\u1eefng b\u00e0i h\u1ecdc k\u1ebf ti\u1ebfp nh\u00e9 c\u00e1c b\u1ea1n!<\/p>\n<p><a href=\"https:\/\/youtu.be\/dEDmfBinFTU\" target=\"_blank\" rel=\"noopener noreferrer\">\u0110\u01b0\u1eddng d\u1eabn t\u1edbi Video<\/a><\/p>\n<iframe loading=\"lazy\" src=\"https:\/\/www.youtube.com\/embed\/dEDmfBinFTU\" width=\"560\" height=\"314\" allowfullscreen=\"allowfullscreen\"><\/iframe>\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-642.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":7134,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-7133","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\/7133","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=7133"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/7133\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/7134"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=7133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=7133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=7133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}