{"id":5644,"date":"2024-12-16T16:50:14","date_gmt":"2024-12-16T16:50:14","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/16\/huong-dan-cach-xac-dinh-du-lieu-trung-theo-nhieu-tieu-chi-bang-ham-countifs\/"},"modified":"2024-12-16T16:50:14","modified_gmt":"2024-12-16T16:50:14","slug":"huong-dan-cach-xac-dinh-du-lieu-trung-theo-nhieu-tieu-chi-bang-ham-countifs","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/16\/huong-dan-cach-xac-dinh-du-lieu-trung-theo-nhieu-tieu-chi-bang-ham-countifs\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn c\u00e1ch x\u00e1c \u0111\u1ecbnh d\u1eef li\u1ec7u tr\u00f9ng theo nhi\u1ec1u ti\u00eau ch\u00ed b\u1eb1ng h\u00e0m COUNTIFS"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Khi l\u00e0m vi\u1ec7c v\u1edbi d\u1eef li\u1ec7u l\u1edbn, ch\u00fang ta th\u01b0\u1eddng g\u1eb7p ph\u1ea3i m\u1ed9t v\u1ea5n\u00a0\u0111\u1ec1 l\u00e0 c\u00f3 nh\u1eefng d\u1eef li\u1ec7u b\u1ecb tr\u00f9ng, l\u1eb7p do nhi\u1ec1u nguy\u00ean nh\u00e2n kh\u00f4ng x\u00e1c\u00a0\u0111\u1ecbnh\u00a0\u0111\u01b0\u1ee3c.<\/p>\n<p>Y\u00eau c\u1ea7u c\u1ee7a ng\u01b0\u1eddi l\u00e0m vi\u1ec7c v\u1edbi d\u1eef li\u1ec7u l\u00e0 ph\u1ea3i th\u1ef1c hi\u1ec7n vi\u1ec7c l\u1ecdc c\u00e1c gi\u00e1 tr\u1ecb tr\u00f9ng, l\u1eb7p\u00a0\u0111\u00f3\u00a0\u0111\u1ec3 tr\u00e1nh vi\u1ec7c t\u00ednh to\u00e1n 2 l\u1ea7n tr\u00ean 1 n\u1ed9i dung. Tuy nhi\u00ean kh\u00f4ng ph\u1ea3i l\u00fac n\u00e0o vi\u1ec7c l\u1ecdc d\u1eef li\u1ec7u tr\u00f9ng c\u0169ng d\u1ec5 d\u00e0ng, b\u1edfi c\u0103n c\u1ee9 x\u00e1c\u00a0\u0111\u1ecbnh th\u1ebf n\u00e0o l\u00e0 tr\u00f9ng\u00a0\u0111\u00f4i khi l\u1ea1i ph\u1ee9c t\u1ea1p h\u01a1n ch\u00fang ta v\u1eabn ngh\u0129.<\/p>\n<p><strong>V\u00ed d\u1ee5<\/strong>: X\u00e9t b\u1ea3ng d\u1eef li\u1ec7u sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12842 aligncenter\" src=\"https:\/\/blog.hocexcel.online\/wp-content\/uploads\/2017\/12\/pic01.png\" alt=\"\" width=\"760\" height=\"612\"><\/p>\n<p><em><strong>Y\u00eau c\u1ea7u<\/strong><\/em>: X\u00e1c\u00a0\u0111\u1ecbnh xem trong b\u1ea3ng d\u1eef li\u1ec7u tr\u00ean n\u1ed9i dung\u00a0\u1edf d\u00f2ng n\u00e0o\u00a0\u0111ang b\u1ecb tr\u00f9ng<\/p>\n<p><strong>H\u01b0\u1edbng d\u1eabn c\u00e1ch l\u00e0m<\/strong>:<\/p>\n<p><strong><em>* Ph\u00e2n t\u00edch y\u00eau c\u1ea7u\u00a0\u0111\u1ec1 b\u00e0i<\/em><\/strong><\/p>\n<p>Kh\u1ea3o s\u00e1t b\u1ea3ng d\u1eef li\u1ec7u tr\u00ean, ch\u00fang ta th\u1ea5y r\u1eb1ng: Vi\u1ec7c x\u00e1c\u00a0\u0111\u1ecbnh th\u1ebf n\u00e0o l\u00e0 tr\u00f9ng\u00a0\u1edf\u00a0\u0111\u00e2y ph\u1ea3i c\u0103n c\u1ee9 theo nhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n.<\/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-206.gif\" title=\"vba101_92019-nho-206\"><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-493.gif\" title=\"ex101_92019-nho-493\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<ul>\n<li>Ng\u00e0y: N\u1ebfu c\u00f9ng 1 ng\u00e0y m\u00e0 c\u00e1c m\u1eb7t h\u00e0ng kh\u00e1c nhau, ho\u1eb7c s\u1ed1 l\u01b0\u1ee3ng h\u00e0ng b\u00e1n kh\u00e1c nhau th\u00ec kh\u00f4ng b\u1ecb tr\u00f9ng<\/li>\n<li>T\u00ean h\u00e0ng: N\u1ebfu c\u00f9ng 1 t\u00ean h\u00e0ng m\u00e0 b\u00e1n\u00a0\u1edf nhi\u1ec1u ng\u00e0y kh\u00e1c nhau, ho\u1eb7c trong c\u00f9ng 1 ng\u00e0y nh\u01b0ng s\u1ed1 l\u01b0\u1ee3ng b\u00e1n kh\u00e1c nhau s\u1ebd kh\u00f4ng ph\u1ea3i l\u00e0 tr\u00f9ng<\/li>\n<li>S\u1ed1 l\u01b0\u1ee3ng: N\u1ebfu s\u1ed1 l\u01b0\u1ee3ng b\u1eb1ng nhau nh\u01b0ng kh\u00e1c ng\u00e0y b\u00e1n, ho\u1eb7c kh\u00e1c m\u1eb7t h\u00e0ng th\u00ec kh\u00f4ng ph\u1ea3i tr\u00f9ng.<\/li>\n<\/ul>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 x\u00e1c\u00a0\u0111\u1ecbnh tr\u00f9ng, ch\u00fang ta bu\u1ed9c ph\u1ea3i x\u00e9t c\u00f9ng 1 l\u00fac tr\u00ean 3\u00a0\u0111i\u1ec1u ki\u1ec7n tr\u00ean: C\u00f9ng ng\u00e0y, c\u00f9ng m\u1eb7t h\u00e0ng, c\u00f9ng s\u1ed1 l\u01b0\u1ee3ng b\u00e1n th\u00ec l\u00e0 tr\u00f9ng<\/p>\n<p>(\u1ede\u00a0\u0111\u00e2y b\u00e0i to\u00e1n ch\u1ec9 c\u00f3 nh\u1eefng d\u1eef li\u1ec7u cho s\u1eb5n n\u00ean ch\u00fang ta ch\u1ec9 x\u00e9t tr\u00ean nh\u1eefng n\u1ed9i dung cho s\u1eb5n, c\u00f2n kh\u00f4ng gi\u1ea3\u00a0\u0111\u1ecbnh ph\u1ee9c t\u1ea1p h\u01a1n)<\/p>\n<p><strong>* H\u00e0m s\u1eed d\u1ee5ng<\/strong><\/p>\n<p>Y\u00eau c\u1ea7u x\u00e1c\u00a0\u0111\u1ecbnh d\u1eef li\u1ec7u tr\u00f9ng -&gt; Ch\u00fang ta ngh\u0129 t\u1edbi h\u00e0m\u00a0\u0110\u1ebfm<\/p>\n<p>Y\u00eau c\u1ea7u bao g\u1ed3m nhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n c\u00f9ng l\u00fac -&gt;\u00a0\u0110\u1ebfm theo nhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n<\/p>\n<p>=&gt; Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y s\u1eed d\u1ee5ng <a href=\"http:\/\/blog.ezworkapp.com\/cach-dung-ham-countifs-va-countif-voi-nhieu-dieu-kien.html\">h\u00e0m COUNTIFS<\/a> l\u00e0 ph\u00f9 h\u1ee3p.<\/p>\n<p><strong>* C\u00e1ch vi\u1ebft h\u00e0m<\/strong><\/p>\n<p>Sau khi\u00a0\u0111\u00e3 x\u00e1c\u00a0\u0111\u1ecbnh\u00a0\u0111\u01b0\u1ee3c h\u00e0m ph\u00f9 h\u1ee3p, ch\u00fang ta ph\u1ea3i t\u00ecm hi\u1ec3u ti\u1ebfp 1 b\u01b0\u1edbc n\u1eefa,\u00a0\u0111\u00f3 l\u00e0 xem d\u00f9ng h\u00e0m nh\u01b0 th\u1ebf n\u00e0o.<\/p>\n<p>D\u1ef1a tr\u00ean vi\u1ec7c kh\u1ea3o s\u00e1t y\u00eau c\u1ea7u, ch\u00fang ta g\u1eafn v\u1edbi c\u1ea5u tr\u00fac h\u00e0m\u00a0\u0111\u1ec3 bi\u1ec7n lu\u1eadn t\u1eebng n\u1ed9i dung nh\u01b0 sau:<\/p>\n<p>H\u00e0m COUNTIFS s\u1ebd vi\u1ebft theo 3\u00a0\u0111i\u1ec1u ki\u1ec7n:<\/p>\n<ul>\n<li>Ng\u00e0y: Criteria_Range1 l\u00e0 c\u1ed9t Ng\u00e0y, Criteria1\u00a0l\u00e0\u00a0ng\u00e0y t\u1ea1i d\u00f2ng ki\u1ec3m tra<\/li>\n<li>T\u00ean h\u00e0ng: Criteria_Range2 l\u00e0 c\u1ed9t T\u00ean h\u00e0ng, Criteria2 l\u00e0 t\u00ean m\u1eb7t h\u00e0ng t\u1ea1i d\u00f2ng ki\u1ec3m tra<\/li>\n<li>S\u1ed1 l\u01b0\u1ee3ng: Criteria_Range3 l\u00e0 c\u1ed9t S\u1ed1 l\u01b0\u1ee3ng, Criteria3 l\u00e0 s\u1ed1 l\u01b0\u1ee3ng t\u1ea1i d\u00f2ng ki\u1ec3m tra<\/li>\n<\/ul>\n<p>Ch\u00fang ta vi\u1ebft h\u00e0m nh\u01b0 sau:<\/p>\n<blockquote>\n<p>G2 =\u00a0COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$D$2:D2,D2)<\/p>\n<\/blockquote>\n<p>T\u1eeb\u00a0\u00f4 G2 ch\u00fang ta s\u1ebd filldown c\u00f4ng th\u1ee9c t\u1edbi\u00a0\u00f4 G25<\/p>\n<p><em>* Gi\u1ea3i th\u00edch<\/em>: t\u1ea1i sao l\u1ea1i d\u00f9ng <em>$A$2:A2 <\/em>ch\u1ee9 kh\u00f4ng ph\u1ea3i<em> $A$2:$A$25<\/em><\/p>\n<p>Khi d\u00f9ng\u00a0 <em>$A$2:$A$25<\/em>\u00a0th\u00ec khi filldown c\u00f4ng th\u1ee9c \u1edf c\u1ed9t G, v\u00f9ng tham chi\u1ebfu lu\u00f4n c\u1ed1 \u0111\u1ecbnh. V\u00ec v\u1eady c\u00e1c gi\u00e1 tr\u1ecb Tr\u00f9ng \/ l\u1eb7p s\u1ebd t\u00ednh = t\u1ed5ng s\u1ed1 c\u00e1c gi\u00e1 tr\u1ecb tr\u00f9ng<\/p>\n<p>Khi d\u00f9ng\u00a0<em>$A$2:A2<\/em>\u00a0 th\u00ec khi filldown c\u00f4ng th\u1ee9c\u00a0\u1edf c\u1ed9t G, v\u00f9ng tham chi\u1ebfu t\u0103ng d\u1ea7n t\u1eeb A2 t\u1edbi A25. V\u00ec v\u1eady gi\u00e1 tr\u1ecb Tr\u00f9ng \/ l\u1eb7p n\u1ebfu c\u00f3 xu\u1ea5t hi\u1ec7n s\u1ebd t\u0103ng d\u1ea7n theo th\u1ee9 t\u1ef1 xu\u1ea5t hi\u1ec7n so v\u1edbi v\u00f9ng d\u1eef li\u1ec7u tr\u01b0\u1edbc\u00a0\u0111\u00f3 =&gt; Ch\u00fang ta c\u00f3 th\u1ec3 nh\u1eadn bi\u1ebft\u00a0\u0111\u01b0\u1ee3c khi n\u00e0o gi\u00e1 tr\u1ecb\u00a0\u0111\u00f3 ch\u01b0a b\u1ecb tr\u00f9ng, khi n\u00e0o n\u00f3 b\u1eaft\u00a0\u0111\u1ea7u b\u1ecb tr\u00f9ng (N\u1ebfu k\u1ebft qu\u1ea3 h\u00e0m &gt;1 th\u00ec t\u1ee9c l\u00e0 b\u1eaft\u00a0\u0111\u1ea7u b\u1ecb tr\u00f9ng)<\/p>\n<blockquote>\n<p>T\u00ecm hi\u1ec3u th\u00eam:\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/excel-co-ban\">T\u00e0i li\u1ec7u Excel c\u01a1 b\u1ea3n<\/a><\/p>\n<\/blockquote>\n<p>Ch\u00fang ta c\u00f9ng xem b\u1ea3ng k\u1ebft qu\u1ea3:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-12843 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/pic02.png\" width=\"840\" height=\"680\" title=\"pic02\"><\/p>\n<p>Trong b\u1ea3ng k\u1ebft qu\u1ea3 tr\u00ean, m\u00ecnh\u00a0\u0111\u00e3 t\u00f4 m\u00e0u\u00a0\u0111\u1ec3 ch\u00fang ta c\u00f3 th\u1ec3 nh\u1eadn ra\u00a0\u0111\u01b0\u1ee3c gi\u00e1 tr\u1ecb tr\u00f9ng.<\/p>\n<p>K\u1ebft h\u1ee3p v\u1edbi ph\u01b0\u01a1ng ph\u00e1p l\u1ecdc (Filter) ch\u00fang ta ho\u00e0n to\u00e0n c\u00f3 th\u1ec3 l\u1ecdc t\u00e1ch c\u00e1c k\u1ebft qu\u1ea3 c\u00f3 gi\u00e1 tr\u1ecb kh\u00f4ng tr\u00f9ng (gi\u00e1 tr\u1ecb b\u1eb1ng 1\u00a0\u1edf c\u1ed9t G) sang m\u1ed9t b\u1ea3ng t\u00ednh kh\u00e1c\u00a0\u0111\u1ec3 l\u1ea5y ri\u00eang ra nh\u1eefng gi\u00e1 tr\u1ecb kh\u00f4ng b\u1ecb tr\u00f9ng.<\/p>\n<p><strong>K\u1ebft lu\u1eadn<\/strong><\/p>\n<p>H\u00e0m COUNTIFS l\u00e0 m\u1ed9t h\u00e0m r\u1ea5t m\u1ea1nh trong\u00a0excel,\u00a0\u1ee9ng d\u1ee5ng trong nhi\u1ec1u tr\u01b0\u1eddng h\u1ee3p.<\/p>\n<p>Trong th\u1ef1c t\u1ebf, ch\u00fang ta h\u1ea7u h\u1ebft lu\u00f4n c\u1ea7n ki\u1ec3m tra d\u1eef li\u1ec7u tr\u01b0\u1edbc khi b\u1eaft\u00a0\u0111\u1ea7u l\u00e0m vi\u1ec7c v\u1edbi d\u1eef li\u1ec7u nh\u1eadn\u00a0\u0111\u01b0\u1ee3c. Do\u00a0\u0111\u00f3 thao t\u00e1c ki\u1ec3m tra d\u1eef li\u1ec7u tr\u00f9ng th\u01b0\u1eddng l\u00e0 thao t\u00e1c\u00a0\u0111\u1ea7u ti\u00ean, gi\u00fap ch\u00fang ta ki\u1ec3m tra d\u1eef li\u1ec7u\u00a0\u0111\u1ec3\u00a0\u0111\u1ea3m b\u1ea3o t\u00ednh hi\u1ec7u qu\u1ea3 khi l\u00e0m vi\u1ec7c\u00a0\u1edf c\u00e1c b\u01b0\u1edbc ti\u1ebfp theo. C\u00e1c b\u1ea1n h\u00e3y t\u1eadp luy\u1ec7n thao t\u00e1c n\u00e0y nhi\u1ec1u\u00a0\u0111\u1ec3 r\u00e8n luy\u1ec7n t\u01b0 duy x\u1eed l\u00fd d\u1eef li\u1ec7u tr\u01b0\u1edbc khi d\u00f9ng nh\u00e9 (Gi\u1ed1ng vi\u1ec7c ch\u00fang ta ph\u1ea3i r\u1eeda s\u1ea1ch hoa qu\u1ea3 tr\u01b0\u1edbc khi\u00a0\u0103n v\u1eady, n\u1ebfu kh\u00f4ng r\u1ea5t d\u1ec5 b\u1ecb\u00a0\u0111au b\u1ee5ng\u00a0\u0111\u1ea5y).<\/p>\n<p>Ch\u00fac c\u00e1c b\u1ea1n h\u1ecdc t\u1ed1t c\u00f9ng <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong><\/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\/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-206.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":5645,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-5644","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\/5644","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=5644"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5644\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/5645"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=5644"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=5644"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=5644"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}