{"id":8171,"date":"2024-12-22T04:08:32","date_gmt":"2024-12-22T04:08:32","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/22\/huong-dan-loc-du-lieu-theo-quy-trong-excel\/"},"modified":"2024-12-22T04:08:32","modified_gmt":"2024-12-22T04:08:32","slug":"huong-dan-loc-du-lieu-theo-quy-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/22\/huong-dan-loc-du-lieu-theo-quy-trong-excel\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn l\u1ecdc d\u1eef li\u1ec7u theo qu\u00fd trong Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>L\u1ecdc l\u00e0 m\u1ed9t trong nh\u1eefng c\u00f4ng vi\u1ec7c v\u00f4 c\u00f9ng ph\u1ed5 bi\u1ebfn trong Excel. V\u1edbi b\u00e0i vi\u1ebft n\u00e0y, <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong><\/a> s\u1ebd h\u01b0\u1edbng d\u1eabn b\u1ea1n c\u00e1ch l\u1ecdc d\u1eef li\u1ec7u theo qu\u00fd th\u00f4ng qua nhi\u1ec1u c\u00e1ch kh\u00e1c nhau.<\/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-676790c0063fb\" 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\/loc-du-lieu-theo-quy-trong-excel.html#Loc_du_lieu_theo_quy_bang_thao_tac_Filter_thong_thuong\" title=\"L\u1ecdc d\u1eef li\u1ec7u theo qu\u00fd b\u1eb1ng thao t\u00e1c Filter th\u00f4ng th\u01b0\u1eddng\">L\u1ecdc d\u1eef li\u1ec7u theo qu\u00fd b\u1eb1ng thao t\u00e1c Filter th\u00f4ng th\u01b0\u1eddng<\/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\/loc-du-lieu-theo-quy-trong-excel.html#Loc_du_lieu_theo_quy_voi_cot_phu\" title=\"L\u1ecdc d\u1eef li\u1ec7u theo qu\u00fd v\u1edbi c\u1ed9t ph\u1ee5\">L\u1ecdc d\u1eef li\u1ec7u theo qu\u00fd v\u1edbi c\u1ed9t ph\u1ee5<\/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\/loc-du-lieu-theo-quy-trong-excel.html#Advanced_Filter_%E2%80%93_loc_du_lieu_nang_cao\" title=\"Advanced Filter \u2013 l\u1ecdc d\u1eef li\u1ec7u n\u00e2ng cao\">Advanced Filter \u2013 l\u1ecdc d\u1eef li\u1ec7u n\u00e2ng cao<\/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\/loc-du-lieu-theo-quy-trong-excel.html#Ham_FILTER_trong_Excel_365\" title=\"H\u00e0m FILTER trong Excel 365\">H\u00e0m FILTER trong Excel 365<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Loc_du_lieu_theo_quy_bang_thao_tac_Filter_thong_thuong\"><\/span>L\u1ecdc d\u1eef li\u1ec7u theo qu\u00fd b\u1eb1ng thao t\u00e1c Filter th\u00f4ng th\u01b0\u1eddng<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Ta c\u00f3 b\u1ea3ng d\u1eef li\u1ec7u m\u1eabu sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-29121\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/loc-theo-quy-1-1024x409-1.png\" width=\"1024\" height=\"409\" title=\"loc-theo-quy-1-1024x409-1\"><\/p>\n<p>\u0110\u1ec3 l\u1ecdc theo qu\u00fd, tr\u01b0\u1edbc ti\u00ean ta c\u1ea7n ch\u1ecdn t\u00ednh n\u0103ng Filter cho to\u00e0n b\u1ea3ng b\u1eb1ng c\u00e1ch ch\u1ecdn to\u00e0n b\u1ed9 b\u1ea3ng sau \u0111\u00f3 v\u00e0o th\u1ebb\u00a0<strong>Data<\/strong>, click v\u00e0o\u00a0<strong>Filter<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-29122\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/loc-theo-quy-2.png\" width=\"458\" height=\"137\" title=\"loc-theo-quy-2\"><\/p>\n<p>T\u1ea1i n\u00fat l\u1ec7nh filter c\u1ee7a c\u1ed9t\u00a0<strong>Ng\u00e0y b\u00e1n<\/strong>, ch\u1ecdn\u00a0<strong>Date Filters<\/strong>. Cu\u1ed9n xu\u1ed1ng m\u1ee5c\u00a0<strong>All Dates in the Period\u00a0<\/strong>v\u00e0 ch\u1ecdn Qu\u00fd mu\u1ed1n l\u1ecdc.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-29123\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/loc-theo-quy-3.png\" width=\"559\" height=\"648\" title=\"loc-theo-quy-3\"><\/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-739.gif\" title=\"ex101_92019-nho-739\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>\u00a0<\/p>\n<p>K\u1ebft qu\u1ea3 sau khi l\u1ecdc theo\u00a0<strong>Qu\u00fd 1:<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-29124\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/loc-theo-quy-4.png\" width=\"418\" height=\"387\" title=\"loc-theo-quy-4\"><\/p>\n<p>\u00a0<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Loc_du_lieu_theo_quy_voi_cot_phu\"><\/span>L\u1ecdc d\u1eef li\u1ec7u theo qu\u00fd v\u1edbi c\u1ed9t ph\u1ee5<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Trong tr\u01b0\u1eddng h\u1ee3p b\u1ea1n kh\u00f4ng th\u1ec3 l\u1ecdc d\u1eef li\u1ec7u b\u1eb1ng thao t\u00e1c Filter, ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u1ed9t ph\u1ee5 \u0111\u1ec3 ti\u1ebfn h\u00e0nh l\u1ecdc theo qu\u00fd. C\u00f3 nhi\u1ec1u c\u00f4ng th\u1ee9c \u0111\u1ec3 chuy\u1ec3n t\u1eeb th\u00e1ng sang qu\u00fd v\u00e0 d\u01b0\u1edbi \u0111\u00e2y H\u1ecdc Excel Online s\u1ebd gi\u1edbi thi\u1ec7u cho b\u1ea1n m\u1ed9t trong s\u1ed1 nh\u1eefng c\u00f4ng th\u1ee9c \u0111\u00f3:<\/p>\n<blockquote>\n<p>=<em>ROUNDUP(MONTH(Ng\u00e0y_b\u00e1n)\/3,0)<\/em><\/p>\n<\/blockquote>\n<p>V\u1edbi\u00a0<strong>Ng\u00e0y_b\u00e1n<\/strong> l\u00e0 \u00f4 thu\u1ed9c c\u1ed9t\u00a0<strong>Ng\u00e0y b\u00e1n<\/strong>. Filldown c\u00f4ng th\u1ee9c v\u00e0 ta s\u1ebd c\u00f3 d\u1eef li\u1ec7u nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-29127\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/loc-theo-quy-5-1024x387-1.png\" width=\"1024\" height=\"387\" title=\"loc-theo-quy-5-1024x387-1\"><\/p>\n<p>\u00a0<\/p>\n<p>Sau \u0111\u00f3, th\u1ef1c hi\u1ec7n thao t\u00e1c Filter cho c\u1ed9t\u00a0<strong>Qu\u00fd<\/strong>. Ta s\u1ebd l\u1ea5y d\u1eef li\u1ec7u qu\u00fd 1:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-29129\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/loc-theo-quy-6.png\" width=\"484\" height=\"434\" title=\"loc-theo-quy-6\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Advanced_Filter_%E2%80%93_loc_du_lieu_nang_cao\"><\/span>Advanced Filter \u2013 l\u1ecdc d\u1eef li\u1ec7u n\u00e2ng cao<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Thay v\u00ec s\u1eed d\u1ee5ng Filter, ta c\u00f3 th\u1ec3 d\u00f9ng\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/cach-su-dung-advanced-filter-trong-excel-kem-cac-vi-du-cu-the.html\"><strong>Advanced Filter<\/strong><\/a> \u0111\u1ec3 l\u1ecdc v\u00e0 ngo\u00e0i ra c\u00f2n c\u00f3 th\u1ec3 tr\u00edch d\u1eef li\u1ec7u sang v\u00f9ng kh\u00e1c. \u0110\u1ec3 c\u00f3 th\u1ec3 l\u1ecdc d\u1eef li\u1ec7u theo qu\u00fd s\u1eed d\u1ee5ng Advanced Filter ta l\u00e0m nh\u01b0 sau:<\/p>\n<p>1. Thi\u1ebft l\u1eadp v\u00f9ng \u0111i\u1ec1u ki\u1ec7n: \u0110i\u1ec1u ki\u1ec7n \u1edf \u0111\u00e2y l\u00e0 th\u1ecfa m\u00e3n \u0111\u1ed3ng th\u1eddi\u00a0<strong>l\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng ng\u00e0y \u0111\u1ea7u qu\u00fd\u00a0<\/strong>v\u00e0\u00a0<strong>nh\u1ecf h\u01a1n ho\u1eb7c b\u1eb1ng ng\u00e0y cu\u1ed1i qu\u00fd<\/strong>. Ta l\u1ea5y v\u00ed d\u1ee5 l\u00e0\u00a0<strong>Qu\u00fd 1<\/strong>.<\/p>\n<p><em>(Chi ti\u1ebft <strong><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-loc-du-lieu-sang-sheet-khac-bang-advanced-filter-trong-excel.html\">c\u00e1ch \u0111\u1eb7t \u0111i\u1ec1u ki\u1ec7n Advanced Filter<\/a> <\/strong>b\u1ea1n c\u00f3 th\u1ec3 xem t\u1ea1i \u0111\u00e2y<\/em><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-29132\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/loc-theo-quy-7.png\" width=\"672\" height=\"163\" title=\"loc-theo-quy-7\"><\/p>\n<p>Sau \u0111\u00f3 ti\u1ebfn h\u00e0nh s\u1eed d\u1ee5ng\u00a0<strong>Advanced Filter:<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-29133\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/loc-theo-quy-8-1024x437-1.png\" width=\"1024\" height=\"437\" title=\"loc-theo-quy-8-1024x437-1\"><\/p>\n<p>\u1ea4n OK v\u00e0 ta c\u00f3 k\u1ebft qu\u1ea3:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-29134\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/loc-theo-quy-9.png\" width=\"467\" height=\"427\" title=\"loc-theo-quy-9\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Ham_FILTER_trong_Excel_365\"><\/span>H\u00e0m FILTER trong Excel 365<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>\u0110\u1ed1i v\u1edbi Excel 365, ta c\u00f3 h\u00e0m\u00a0<strong>FILTER<\/strong>.\u00a0H\u00e0m n\u00e0y c\u00f3 t\u00e1c d\u1ee5ng l\u1ecdc d\u1eef li\u1ec7u\u00a0\u0111\u1ed3ng th\u1eddi k\u1ebft xu\u1ea5t d\u1eef li\u1ec7u l\u1ecdc\u00a0\u0111\u01b0\u1ee3c sang 1 v\u1ecb tr\u00ed kh\u00e1c (\u00f4 kh\u00e1c, sheet kh\u00e1c,\u00a0workbook kh\u00e1c). \u0110\u1ec3 t\u00ecm hi\u1ec3u v\u1ec1 h\u00e0m\u00a0<strong>FILTER<\/strong>, \u0111\u1ecdc b\u00e0i vi\u1ebft sau:\u00a0<em><strong><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-loc-du-lieu-sang-sheet-khac-bang-advanced-filter-trong-excel.html\">https:\/\/blog.hocexcel.online\/huong-dan-cach-loc-du-lieu-sang-sheet-khac-bang-advanced-filter-trong-excel.html<\/a>.<\/strong><\/em><\/p>\n<p>C\u1ee5 th\u1ec3 trong b\u00e0i vi\u1ebft n\u00e0y, \u0111\u1ec3 l\u1ecdc d\u1eef li\u1ec7u t\u1eeb qu\u00fd 1 ta l\u00e0m t\u01b0\u01a1ng t\u1ef1 nh\u01b0 \u0111i\u1ec1u ki\u1ec7n v\u1edbi\u00a0<strong>Advanced Filter,\u00a0<\/strong>thay b\u1eb1ng h\u00e0m\u00a0<strong>Filter<\/strong> nh\u01b0 sau:<\/p>\n<blockquote>\n<p><strong><em>=FILTER(A2:D85,(B2:B85&gt;=DATE(2018,1,1))*(B2:B85&lt;=DATE(2018,3,31)),\u201dkh\u00f4ng c\u00f3 k\u1ebft qu\u1ea3\u201d)<\/em><\/strong><\/p>\n<\/blockquote>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-29136\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/loc-theo-quy-10-1024x429-1.png\" width=\"1024\" height=\"429\" title=\"loc-theo-quy-10-1024x429-1\"><\/p>\n<p>V\u00e0 k\u1ebft qu\u1ea3:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-29137\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/loc-theo-quy-11.png\" width=\"569\" height=\"466\" title=\"loc-theo-quy-11\"><\/p>\n<p>T\u00f9y thu\u1ed9c v\u00e0o y\u00eau c\u1ea7u t\u1eebng tr\u01b0\u1eddng h\u1ee3p m\u00e0 b\u1ea1n h\u00e3y \u00e1p d\u1ee5ng c\u00e1c ph\u01b0\u01a1ng ph\u00e1p kh\u00e1c nhau trong vi\u1ec7c l\u1ecdc d\u1eef li\u1ec7u. C\u00f3 nh\u1eefng tr\u01b0\u1eddng h\u1ee3p ta c\u00f3 th\u1ec3 l\u1ecdc th\u00f4ng th\u01b0\u1eddng, c\u00f3 tr\u01b0\u1eddng h\u1ee3p ph\u1ea3i s\u1eed d\u1ee5ng Advanced Filter hay c\u00f3 nh\u1eefng tr\u01b0\u1eddng h\u1ee3p ta ph\u1ea3i k\u1ebft h\u1ee3p c\u00f9ng\u00a0<strong><a href=\"http:\/\/blog.ezworkapp.com\/tu-dong-loc-danh-sach-khong-trung-voi-advanced-filter-vba-cuc-ky-don-gian.html\">VBA<\/a>.<\/strong><\/p>\n<hr>\n<p>Xem th\u00eam nh\u1eefng b\u00e0i vi\u1ebft t\u01b0\u01a1ng t\u1ef1 sau:<\/p>\n<p><strong><a href=\"http:\/\/blog.ezworkapp.com\/su-dung-autofilter-hieu-qua-hon-voi-vba.html\">S\u1eed d\u1ee5ng AutoFilter hi\u1ec7u qu\u1ea3 v\u1edbi VBA<\/a><\/strong><\/p>\n<p><strong><a href=\"http:\/\/blog.ezworkapp.com\/tu-dong-hoa-advanced-filter-voi-recod-macro.html\">T\u1ef1 \u0111\u1ed9ng Advanced Filter v\u1edbi macro<\/a><\/strong><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-lap-bao-cao-chi-tiet-tu-dong-voi-ham-filter-trong-excel.html\"><strong>L\u1eadp b\u00e1o c\u00e1o chi ti\u1ebft v\u1edbi h\u00e0m Filter<\/strong><\/a><\/p>\n<h3><\/h3>\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-739.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":8172,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-8171","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\/8171","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=8171"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/8171\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/8172"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=8171"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=8171"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=8171"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}