{"id":3361,"date":"2024-12-03T02:16:07","date_gmt":"2024-12-03T02:16:07","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/03\/cach-tim-va-chon-dong-trung-nhau-trong-mot-day-du-lieu-trong-excel\/"},"modified":"2024-12-03T02:16:07","modified_gmt":"2024-12-03T02:16:07","slug":"cach-tim-va-chon-dong-trung-nhau-trong-mot-day-du-lieu-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/03\/cach-tim-va-chon-dong-trung-nhau-trong-mot-day-du-lieu-trong-excel\/","title":{"rendered":"C\u00e1ch t\u00ecm v\u00e0 ch\u1ecdn d\u00f2ng tr\u00f9ng nhau trong m\u1ed9t d\u00e3y d\u1eef li\u1ec7u trong Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Trong b\u00e0i vi\u1ebft n\u00e0y, <strong><a href=\"http:\/\/blog.ezworkapp.com\/\">Blog H\u1ecdc Excel Online<\/a> <\/strong>s\u1ebd h\u01b0\u1edbng d\u1eabn c\u00e1ch t\u00ecm v\u00e0 ch\u1ecdn nh\u1eefng h\u00e0ng gi\u1ed1ng nhau trong m\u1ed9t d\u00e3y \u0111\u00e3 ch\u1ecdn.<\/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-674e69e718801\" 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\/cach-tim-va-chon-dong-trung-nhau-trong-mot-day-du-lieu-trong-excel.html#Tim_nhung_hang_trung_nhau_voi_cong_thuc\" title=\"T\u00ecm nh\u1eefng h\u00e0ng tr\u00f9ng nhau v\u1edbi c\u00f4ng th\u1ee9c\">T\u00ecm nh\u1eefng h\u00e0ng tr\u00f9ng nhau v\u1edbi c\u00f4ng th\u1ee9c<\/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\/cach-tim-va-chon-dong-trung-nhau-trong-mot-day-du-lieu-trong-excel.html#Tim_hang_trung_lap_trong_day_voi_dinh_dang_co_dieu_kien\" title=\"T\u00ecm h\u00e0ng tr\u00f9ng l\u1eb7p trong d\u00e3y v\u1edbi \u0111\u1ecbnh d\u1ea1ng c\u00f3 \u0111i\u1ec1u ki\u1ec7n.\">T\u00ecm h\u00e0ng tr\u00f9ng l\u1eb7p trong d\u00e3y v\u1edbi \u0111\u1ecbnh d\u1ea1ng c\u00f3 \u0111i\u1ec1u ki\u1ec7n.<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Tim_nhung_hang_trung_nhau_voi_cong_thuc\"><\/span><strong>T\u00ecm nh\u1eefng h\u00e0ng tr\u00f9ng nhau v\u1edbi c\u00f4ng th\u1ee9c<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>C\u00f4ng th\u1ee9c sau c\u00f3 th\u1ec3 gi\u00fap b\u1ea1n t\u00ecm \u0111\u01b0\u1ee3c nh\u1eefng d\u1eef li\u1ec7u tr\u00f9ng l\u1eb7p<\/p>\n<p>1. Trong \u00f4 tr\u1ed1ng li\u1ec1n k\u1ec1, tr\u01b0\u1eddng h\u1ee3p n\u00e0y l\u00e0 D2, h\u00e3y nh\u1eadp c\u00f4ng th\u1ee9c sau:<\/p>\n<p><strong>=IF(SUMPRODUCT(($A$2:$A$10=A2)*1,($B$2:$B$10=B2)*1,($C$2:$C$10=C2)*1)&gt;1,\u201dDuplicates\u201d,\u201dNo duplicates\u201d)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7133\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-3-1.png\" width=\"600\" height=\"288\" title=\"1-3-1\"><\/p>\n<p>2. Nh\u1ea5n <strong>Enter, <\/strong>n\u1ebfu d\u00f2ng \u0111\u00f3 tr\u00f9ng l\u1eb7p trong v\u00f9ng ch\u1ecdn, th\u00ec s\u1ebd hi\u1ec3n th\u1ecb l\u00e0 <strong>Duplicates<\/strong>, n\u1ebfu kh\u00f4ng th\u00ec hi\u1ec3n th\u1ecb <strong>No duplicates<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/doc-find-duplicates-in-range4-4.png\" alt=\"doc-find-duplicates-in-range4\" width=\"478\" height=\"289\" title=\"doc-find-duplicates-in-range4-4\"><\/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-112.gif\" title=\"vba101_92019-nho-112\"><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-258.gif\" title=\"ex101_92019-nho-258\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>3. Ch\u1ecdn D2, k\u00e9o c\u00f4ng th\u1ee9c ph\u1ee7 t\u1ea5t c\u1ea3 v\u00f9ng mu\u1ed1n ki\u1ec3m tra tr\u00f9ng l\u1eb7p.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/doc-find-duplicates-in-range5-5.png\" alt=\"doc-find-duplicates-in-range5\" width=\"498\" height=\"313\" title=\"doc-find-duplicates-in-range5-5\"><\/p>\n<p><strong>Ch\u00fa \u00fd:<\/strong><\/p>\n<ol>\n<li>Trong c\u00f4ng th\u1ee9c n\u00e0y, <em><strong>$A$2:$A$10<\/strong>, <strong>$B$2:$B$10<\/strong>, <strong>$C$2:$C$10<\/strong><\/em> ch\u1ec9 v\u00f9ng b\u1ea1n mu\u1ed1n ki\u1ec3m tra tr\u00f9ng l\u1eb7p. B\u1ea1n c\u00f3 th\u1ec3 thay \u0111\u1ed5i tham s\u1ed1 theo nhu c\u1ea7u. C\u00e1c \u00f4 <strong>A2<\/strong>, <strong>B2<\/strong>, <strong>C2<\/strong> ch\u1ec9 \u00f4 \u0111\u1ea7u ti\u00ean c\u1ee7a m\u1ed7i c\u1ed9t m\u00e0\u00a0 c\u00f4ng th\u1ee9c ph\u1ea3i \u00e1p d\u1ee5ng, b\u1ea1n c\u0169ng c\u00f3 th\u1ec3 thay \u0111\u1ed5i n\u00f3.<\/li>\n<li>C\u00f4ng th\u1ee9c tr\u00ean d\u1ef1a tr\u00ean d\u1eef li\u1ec7u \u1edf c\u1ed9t th\u1ee9 3, n\u1ebfu c\u00f3 4 c\u1ed9t trong d\u00e3y c\u1ea7n ph\u1ea3i t\u00ecm tr\u00f9ng l\u1eb7p t\u1eeb h\u00e0ng \u0111\u1ea7u ti\u00ean, c\u00f4ng th\u1ee9c s\u1ebd tr\u1edf th\u00e0nh <strong>=IF(SUMPRODUCT(($A$1:$A$10=A1)*1,($B$1:$B$10=B1)*1,($C$1:$C$10=C1)*1, ($D$1:$D$10=D1)*1)&gt;1,\u201dDuplicates\u201d,\u201dNo duplicates\u201d)<\/strong>.<\/li>\n<\/ol>\n<h2><span class=\"ez-toc-section\" id=\"Tim_hang_trung_lap_trong_day_voi_dinh_dang_co_dieu_kien\"><\/span><strong>T\u00ecm h\u00e0ng tr\u00f9ng l\u1eb7p trong d\u00e3y v\u1edbi \u0111\u1ecbnh d\u1ea1ng c\u00f3 \u0111i\u1ec1u ki\u1ec7n.<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>N\u1ebfu b\u1ea1n kh\u00f4ng th\u1ec3 \u00e1p d\u1ee5ng c\u00f4ng th\u1ee9c ch\u00ednh x\u00e1c, \u0111\u1eebng lo l\u1eafng. Ch\u1ee9c n\u0103ng <strong>Conditional Formatting<\/strong> c\u0169ng c\u00f3 th\u1ec3 \u0111\u00e1nh d\u1ea5u nh\u1eefng h\u00e0ng tr\u00f9ng l\u1eb7p. H\u00e3y l\u00e0m nh\u01b0 sau:<\/p>\n<p>1. \u1ede b\u01b0\u1edbc th\u1ee9 nh\u1ea5t, b\u1ea1n n\u00ean s\u1eed d\u1ee5ng <a href=\"http:\/\/blog.ezworkapp.com\/ham-concatenate-trong-excel-ung-dung-de-noi-cac-chuoi-o-mang-va-cot.html\" target=\"_blank\" rel=\"noopener noreferrer\">h\u00e0m <strong>CONCATENATE<\/strong> <\/a>\u0111\u1ec3 k\u1ebft h\u1ee3p d\u1eef li\u1ec7u th\u00e0nh 1 \u00f4 \u1edf m\u1ed7i d\u00f2ng. Nh\u1eadp c\u00f4ng th\u1ee9c <strong>=CONCATENATE(A2,B2,C2)<\/strong> v\u00e0o \u00f4 D2, nh\u01b0 h\u00ecnh:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7134\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/2-1-2.png\" width=\"600\" height=\"313\" title=\"2-1-2\"><\/p>\n<p>2. \u00c1p d\u1ee5ng c\u00f4ng th\u1ee9c cho t\u1ea5t c\u1ea3 c\u00e1c h\u00e0ng<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/blog.hocexcel.online\/wp-content\/uploads\/imgseo\/doc-find-duplicates-in-range7-7.png\" alt=\"doc-find-duplicates-in-range7\" width=\"502\" height=\"311\"><\/p>\n<p>3. Ch\u1ecdn v\u00f9ng b\u1ea1n mu\u1ed1n ki\u1ebfm h\u00e0ng tr\u00f9ng l\u1eb7p bao g\u1ed3m c\u1ea3 c\u1ed9t D, v\u00e0o tab <strong>Home, <\/strong>nh\u1ea5p <strong>Conditional Formatting<\/strong> &gt;<strong> New Rule<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/doc-find-duplicates-in-range8-8.png\" alt=\"doc-find-duplicates-in-range8\" width=\"307\" height=\"383\" title=\"doc-find-duplicates-in-range8-8\"><\/p>\n<p>4. Trong h\u1ed9p tho\u1ea1i <strong>New Formatting Rule<\/strong>, nh\u1ea5p <strong>Use a formula to determine which cells to format<\/strong>, ta d\u00f9ng <a href=\"http:\/\/blog.ezworkapp.com\/cach-dung-ham-countifs-va-countif-voi-nhieu-dieu-kien.html\" target=\"_blank\" rel=\"noopener noreferrer\">h\u00e0m COUNTIF<\/a>, \u00a0nh\u1eadp c\u00f4ng th\u1ee9c <strong>=COUNTIF($D$2:$D$10,$D2)&gt;1<\/strong> nh\u01b0 h\u00ecnh<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-7135\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/3-3.png\" width=\"600\" height=\"278\" title=\"3-3\"><\/p>\n<p>Ch\u00fa \u00fd: <em><strong>$D$2:$D$10<\/strong><\/em>, c\u1ed9t D l\u00e0 c\u1ed9t b\u1ea1n \u0111\u00e3 k\u1ebft h\u1ee3p d\u1eef li\u1ec7u.<\/p>\n<p>5. Nh\u1ea5p <strong>Format, <\/strong>v\u00e0o tab<strong> Fill, <\/strong>ch\u1ecdn m\u00e0u b\u1ea1n c\u1ea7n \u0111\u1ec3 \u0111\u00e1nh d\u1ea5u tr\u00f9ng l\u1eb7p.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/blog.hocexcel.online\/wp-content\/uploads\/imgseo\/doc-find-duplicates-in-range10-10.png\" alt=\"doc-find-duplicates-in-range10\" width=\"542\" height=\"512\"><\/p>\n<p>6. Nh\u1ea5n OK \u0111\u1ebfn khi \u0111\u00f3ng h\u1ed9p tho\u1ea1i, nh\u1eefng h\u00e0ng tr\u00f9ng l\u1eb7p \u0111\u00e3 \u0111\u01b0\u1ee3c \u0111\u00e1nh d\u1ea5u m\u00e0u m\u1ed9t l\u01b0\u1ee3t.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/doc-find-duplicates-in-range11-11.png\" alt=\"doc-find-duplicates-in-range11\" width=\"478\" height=\"284\" title=\"doc-find-duplicates-in-range11-11\"><\/p>\n<p>R\u1ea5t nhi\u1ec1u h\u00e0m trong Excel ph\u1ea3i kh\u00f4ng n\u00e0o? M\u1ed7i h\u00e0m \u0111\u1ec1u c\u00f3 r\u1ea5t nhi\u1ec1u \u1ee9ng d\u1ee5ng n\u1ebfu \u0111\u01b0\u1ee3c hi\u1ec3u \u0111\u00fang b\u1ea3n ch\u1ea5t v\u00e0 s\u1eed d\u1ee5ng linh ho\u1ea1t. Tuy nhi\u00ean, n\u1ebfu kh\u00f4ng \u0111\u01b0\u1ee3c h\u1ec7 th\u1ed1ng th\u00ec r\u1ea5t kh\u00f3 \u0111\u1ec3 b\u1ea1n h\u1ecdc v\u00e0 s\u1eed d\u1ee5ng t\u1ed1t c\u00e1c h\u00e0m c\u0169ng nh\u01b0 c\u00e1c c\u00f4ng c\u1ee5 kh\u00e1c trong Excel.\u00a0Hi\u1ec7n t\u1ea1i,\u00a0Blog Ho\u0323c Excel Online \u0111a\u0303 m\u01a1\u0309 r\u00e2\u0301t nhi\u00ea\u0300u ca\u0301c kho\u0301a ho\u0323c \u0111a\u0300o ta\u0323o v\u00ea\u0300\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-excel-co-ban\">H\u1ecdc Excel c\u01a1 b\u1ea3n \u0111\u1ebfn n\u00e2ng cao<\/a> v\u01a1\u0301i ca\u0301c chuy\u00ean gia ha\u0300ng \u0111\u00e2\u0300u ta\u0323i Vi\u00ea\u0323t Nam. Ch\u00fang t\u00f4i se\u0303 mang \u0111\u00ea\u0301n cho ba\u0323n nh\u01b0\u0303ng bu\u00f4\u0309i ho\u0323c b\u00f4\u0309 i\u0301ch, hi\u1ec7u qua\u0309 co\u0301 th\u00ea\u0309 s\u01b0\u0309 du\u0323ng tha\u0300nh tha\u0323o Excel \u0111\u00ea\u0309 a\u0301p du\u0323ng trong c\u00f4ng vi\u00ea\u0323c. Ngoa\u0300i ra, chu\u0301ng t\u00f4i \u0111ang co\u0301 nhi\u00ea\u0300u ch\u01b0\u01a1ng tri\u0300nh khuy\u00ea\u0301n ma\u0323i l\u01a1\u0301n cho ca\u0301c ho\u0323c vi\u00ean \u0111\u0103ng ki\u0301 trong tha\u0301ng na\u0300y, v\u00e2\u0323y co\u0300n tr\u1ea7n tr\u01b0\u0300 gi\u0300 n\u01b0\u0303a ma\u0300 kh\u00f4ng \u0111\u0103ng ki\u0301 ngay th\u00f4i.<\/p>\n<p><strong>Xem th\u00eam:<a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-loc-gia-tri-trung-nhau-tren-excel-2010.html\"> C\u00e1ch t\u00ecm d\u1eef li\u1ec7u tr\u00f9ng nhau<\/a> trong excel c\u1ef1c d\u1ec5<\/strong><\/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-112.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":3362,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3361","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\/3361","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=3361"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/3361\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/3362"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=3361"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=3361"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=3361"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}