{"id":2726,"date":"2021-09-10T10:19:21","date_gmt":"2021-09-10T03:19:21","guid":{"rendered":"http:\/\/htttdn.com\/?post_type=ht_kb&p=2726"},"modified":"2021-09-11T15:08:11","modified_gmt":"2021-09-11T08:08:11","slug":"cach-loc-trung-gia-tri-trung-du-lieu-tren-2-sheet-trong-excel-don-gian","status":"publish","type":"ht_kb","link":"https:\/\/htttdn.com\/index.php\/tin-tuc\/cach-loc-trung-gia-tri-trung-du-lieu-tren-2-sheet-trong-excel-don-gian\/","title":{"rendered":"C\u00e1ch \u0111\u01a1n gi\u1ea3n \u0111\u1ec3 l\u1ecdc tr\u00f9ng gi\u00e1 tr\u1ecb, tr\u00f9ng d\u1eef li\u1ec7u trong Excel"},"content":{"rendered":"

Ngu\u1ed3n: T\u01b0\u01a1iNT, Cty FAST<\/em><\/p>\n

Vi\u1ec7c tr\u00f9ng l\u1eb7p d\u1eef li\u1ec7u \u0111ang g\u00e2y kh\u00f3 kh\u0103n trong vi\u1ec7c th\u1ed1ng k\u00ea c\u1ee7a b\u1ea1n. \u0110\u1eebng lo l\u1eafng! H\u00e3y theo d\u00f5i b\u00e0i vi\u1ebft \u0111\u1ec3 bi\u1ebft c\u00e1ch l\u1ecdc d\u1eef li\u1ec7u tr\u00f9ng tr\u00ean 1 sheet v\u00e0 2 sheet trong Excel si\u00eau \u0111\u01a1n gi\u1ea3n.<\/p>\n

1. C\u00e1ch l\u1ecdc tr\u00f9ng d\u1eef li\u1ec7u trong Excel<\/h2>\n

H\u01b0\u1edbng d\u1eabn nhanh<\/h3>\n

M\u1edf t\u1ec7p excel c\u1ee7a b\u1ea1n, b\u00f4i \u0111en v\u00f9ng d\u1eef li\u1ec7u c\u1ea7n l\u1ecdc > Ch\u1ecdn Data<\/strong> tr\u00ean thanh c\u00f4ng c\u1ee5 > Ch\u1ecdn Remove Duplicates<\/strong> > D\u00f9ng chu\u1ed9t t\u00edch v\u00e0o c\u1ea1nh tr\u01b0\u1eddng (Column) c\u1ee7a b\u1ea3ng t\u00ednh b\u1ea1n mu\u1ed1n l\u1ecdc > Ch\u1ecdn OK<\/strong> l\u00e0 ph\u1ea7n m\u1ec1m s\u1ebd hi\u1ec3n th\u1ecb \u0111\u00e3 lo\u1ea1i b\u1ecf \u0111\u01b0\u1ee3c bao nhi\u00eau d\u1eef li\u1ec7u tr\u00f9ng v\u00e0 bao nhi\u00eau d\u1eef li\u1ec7u c\u00f2n l\u1ea1i.<\/p>\n

H\u01b0\u1edbng d\u1eabn chi ti\u1ebft<\/h3>\n

\"\"B\u1ea3ng t\u00ednh c\u1ea7n l\u1ecdc d\u1eef li\u1ec7u<\/em><\/p>\n

B\u01b0\u1edbc 1<\/strong>: M\u1edf t\u1ec7p excel c\u1ee7a b\u1ea1n, b\u00f4i \u0111en v\u00f9ng d\u1eef li\u1ec7u c\u1ea7n l\u1ecdc > Ch\u1ecdn Data<\/strong> tr\u00ean thanh c\u00f4ng c\u1ee5 > Ch\u1ecdn Remove Duplicates.<\/strong><\/p>\n

\"\"<\/p>\n

Ch\u1ecdn Data -> Remove Duplicates<\/em><\/p>\n

B\u01b0\u1edbc 2<\/strong>: Sau \u0111\u00f3, b\u1ea1n s\u1ebd th\u1ea5y hi\u1ec7n ra h\u1ed9p tho\u1ea1i Remove Duplicates<\/strong>. B\u1ea1n c\u00f3 th\u1ec3 l\u1ef1a ch\u1ecdn l\u1ea1i c\u00e1c tr\u01b0\u1eddng (Column<\/strong>) c\u1ea7n x\u1eed l\u00fd t\u00f9y theo y\u00eau c\u1ea7u. S\u1ebd c\u00f3 c\u00e1c tr\u01b0\u1eddng h\u1ee3p nh\u01b0 sau:<\/p>\n

Tr\u01b0\u1eddng h\u1ee3p 1<\/strong><\/span>: Ch\u1ecdn Select All<\/strong><\/p>\n

Khi c\u1ea7n l\u1ecdc d\u1eef li\u1ec7u b\u1ecb tr\u00f9ng nhau \u1edf t\u1ea5t c\u1ea3 c\u00e1c tr\u01b0\u1eddng (Column) c\u1ee7a b\u1ea3ng t\u00ednh th\u00ec ch\u1ecdn Select All<\/strong>. Sau khi l\u1ef1a ch\u1ecdn b\u1ea1n nh\u1ea5n OK<\/strong> \u0111\u1ec3 xu\u1ea5t k\u1ebft qu\u1ea3 l\u1ecdc d\u1eef li\u1ec7u.<\/p>\n

\"\"
\nTheo b\u1ea3ng t\u00ednh tr\u00ean, khi c\u1ea7n l\u1ecdc d\u1eef li\u1ec7u b\u1ecb tr\u00f9ng nhau \u1edf c\u1ea3 4 c\u1ed9t M\u00e3 KH (C\u1ed9t A), T\u00ean kh\u00e1ch h\u00e0ng (C\u1ed9t B), Ng\u01b0\u1eddi \u0111\u1eb7t mua (C\u1ed9t C), T\u1ec9nh th\u00e0nh (C\u1ed9t D) th\u00ec b\u1ea5m ch\u1ecdn Select All> Ch\u1ecdn OK. C\u00f3 th\u1ec3 th\u1ea5y, d\u1eef li\u1ec7u t\u1ea1i c\u1ea3 4 c\u1ed9t c\u1ee7a d\u00f2ng 15 \u0111ang tr\u00f9ng v\u1edbi d\u00f2ng 6.<\/em><\/p>\n

Ti\u1ebfp theo, b\u1ea1n s\u1ebd th\u1ea5y h\u1ed9p tho\u1ea1i th\u00f4ng b\u00e1o Microsoft Excel<\/strong> xu\u1ea5t hi\u1ec7n. Tr\u00ean \u0111\u00f3 hi\u1ec3n th\u1ecb s\u1ed1 l\u01b0\u1ee3ng gi\u00e1 tr\u1ecb tr\u00f9ng, s\u1ed1 m\u1eabu b\u1ecb Removed v\u00e0 t\u1ed5ng s\u1ed1 gi\u00e1 tr\u1ecb \u0111\u1ed9c l\u1eadp c\u00f2n l\u1ea1i sau khi Removed \u0111\u1ec3 b\u1ea1n n\u1eafm b\u1eaft d\u1ec5 d\u00e0ng. Sau khi l\u1ef1a ch\u1ecdn b\u1ea1n nh\u1ea5n OK<\/strong> \u0111\u1ec3 ho\u00e0n th\u00e0nh qu\u00e1 tr\u00ecnh th\u1ef1c hi\u1ec7n.<\/p>\n

\"\"<\/p>\n

Theo b\u1ea3ng t\u00ednh tr\u00ean, d\u00f2ng d\u1eef li\u1ec7u b\u1ecb tr\u00f9ng v\u1edbi d\u00f2ng 6 \u0111\u00e3 \u0111\u01b0\u1ee3c Removed, c\u00f2n 15 d\u00f2ng ch\u1ee9a gi\u00e1 tr\u1ecb duy nh\u1ea5t (Unique value) \u0111\u01b0\u1ee3c gi\u1eef l\u1ea1i.<\/em><\/p>\n

\"\"K\u1ebft qu\u1ea3 d\u1eef li\u1ec7u \u0111\u00e3 \u0111\u01b0\u1ee3c l\u1ecdc ho\u00e0n ch\u1ec9nh<\/em><\/p>\n

Tr\u01b0\u1eddng h\u1ee3p 2<\/strong>: T\u00f9y ch\u1ecdn c\u00e1c tr\u01b0\u1eddng (Column<\/strong>) c\u1ea7n l\u1ecdc tr\u00f9ng d\u1eef li\u1ec7u.<\/p>\n

Th\u1ee9 nh\u1ea5t<\/strong><\/em><\/span>: Trong tr\u01b0\u1eddng h\u1ee3p ch\u1ec9 c\u1ea7n l\u1ecdc tr\u00f9ng d\u1eef li\u1ec7u \u1edf m\u1ed9t s\u1ed1 tr\u01b0\u1eddng (Column) nh\u1ea5t \u0111\u1ecbnh<\/strong>, b\u1ea1n c\u00f3 th\u1ec3 tick ch\u1ecdn v\u00e0o t\u1eebng \u00f4 vu\u00f4ng c\u1ee7a c\u00e1c Column<\/strong> m\u00e0 kh\u00f4ng c\u1ea7n ch\u1ecdn to\u00e0n b\u1ed9.<\/p>\n

\"\"<\/p>\n

Theo b\u1ea3ng t\u00ednh tr\u00ean, khi c\u1ea7n l\u1ecdc tr\u00f9ng d\u1eef li\u1ec7u c\u1ee7a Column T\u00ean kh\u00e1ch h\u00e0ng b\u1ea1n tick ch\u1ecdn \u00f4 vu\u00f4ng T\u00ean kh\u00e1ch h\u00e0ng t\u1ea1i h\u1ed9p tho\u1ea1i Remove Duplicates sau \u0111\u00f3 nh\u1ea5n OK. C\u00f3 th\u1ec3 th\u1ea5y, d\u1eef li\u1ec7u t\u1ea1i Column T\u00ean kh\u00e1ch h\u00e0ng c\u1ee7a d\u00f2ng 15, 16 \u0111ang tr\u00f9ng v\u1edbi d\u00f2ng 6, c\u00e1c Column c\u00f2n l\u1ea1i kh\u00f4ng \u0111\u01b0\u1ee3c x\u00e9t t\u1edbi.<\/em><\/p>\n

\"\"<\/p>\n

Khi \u0111\u00f3, d\u00f2ng 15 v\u00e0 d\u00f2ng 16 c\u00f3 T\u00ean kh\u00e1ch h\u00e0ng tr\u00f9ng v\u1edbi d\u00f2ng 6 s\u1ebd \u0111\u01b0\u1ee3c l\u1ecdc \u0111i, c\u00f2n 14 d\u00f2ng ch\u1ee9a gi\u00e1 tr\u1ecb duy nh\u1ea5t (Unique value) \u0111\u01b0\u1ee3c gi\u1eef l\u1ea1i.<\/em><\/p>\n

\"\"<\/p>\n

S\u1ed1 l\u01b0\u1ee3ng d\u1eef li\u1ec7u b\u1ecb tr\u00f9ng sau khi l\u1ecdc<\/em><\/p>\n

Th\u1ee9 hai<\/strong><\/em><\/span>: T\u01b0\u01a1ng t\u1ef1, \u0111\u1ed1i v\u1edbi tr\u01b0\u1eddng h\u1ee3p ch\u1ecdn nhi\u1ec1u h\u01a1n 1 Column<\/strong> trong b\u1ea3ng Remove Duplicates th\u00ec c\u00e1c d\u00f2ng c\u00f3 d\u1eef li\u1ec7u tr\u00f9ng \u1edf t\u1ea5t c\u1ea3 c\u00e1c Column \u0111\u01b0\u1ee3c ch\u1ecdn s\u1ebd b\u1ecb Removed.<\/p>\n

\"\"<\/p>\n

Theo b\u1ea3ng t\u00ednh tr\u00ean, d\u00f2ng n\u00e0o c\u00f3 d\u1eef li\u1ec7u b\u1ecb tr\u00f9ng \u1edf 2 Column M\u00e3 KH v\u00e0 T\u00ean kh\u00e1ch h\u00e0ng s\u1ebd b\u1ecb Removed<\/em><\/p>\n

\"\"<\/p>\n

K\u1ebft qu\u1ea3 l\u1ecdc ta c\u00f3 b\u1ea3ng t\u00ednh nh\u01b0 tr\u00ean<\/em><\/p>\n

2. C\u00e1ch l\u1ecdc d\u1eef li\u1ec7u tr\u00f9ng nhau tr\u00ean 2 sheet trong Excel<\/strong><\/h2>\n

H\u01b0\u1edbng d\u1eabn nhanh<\/h3>\n

M\u1edf t\u1ec7p excel c\u1ee7a b\u1ea1n, trong sheet 1 t\u1ea1o th\u00eam 1 c\u1ed9t \u0111\u1ec3 ki\u1ec3m tra > Nh\u1eadp h\u00e0m =IF(ISNA(VLOOKUP(V\u00f9ng l\u1ecdc d\u1eef li\u1ec7u sheet 1,V\u00f9ng l\u1ecdc d\u1eef li\u1ec7u sheet 2 ,1,0)),”Kh\u00f4ng”,”Tr\u00f9ng”)<\/strong> t\u1ea1i \u00f4 b\u00ean c\u1ea1nh d\u1eef li\u1ec7u sheet 1 => M\u00e0n h\u00ecnh s\u1ebd hi\u1ec3n th\u1ecb k\u1ebft qu\u1ea3 xem d\u1eef li\u1ec7u c\u00f3 tr\u00f9ng kh\u00f4ng b\u00ean c\u1ea1nh, k\u00e9o xu\u1ed1ng nh\u1eefng \u00f4 b\u00ean d\u01b0\u1edbi \u0111\u1ec3 hi\u1ec3n th\u1ecb th\u00eam k\u1ebft qu\u1ea3.<\/p>\n

H\u01b0\u1edbng d\u1eabn chi ti\u1ebft<\/h3>\n

\"\"<\/p>\n

D\u1eef li\u1ec7u c\u1ea7n l\u1ecdc c\u1ee7a sheet 1<\/em><\/p>\n

\"\"<\/p>\n

D\u1eef li\u1ec7u c\u1ea7n l\u1ecdc c\u1ee7a sheet 2<\/em><\/p>\n

Nh\u00ecn v\u00e0o 2 h\u00ecnh ph\u00eda tr\u00ean, ch\u00fang ta c\u00f3 th\u1ec3 th\u1ea5y d\u1eef li\u1ec7u b\u1ecb tr\u00f9ng \u1edf sheet 1 v\u00e0 sheet 2 \u0111\u00e3 \u0111\u01b0\u1ee3c b\u00f4i ch\u1eef m\u00e0u \u0111\u1ecf. C\u00e1c thao t\u00e1c l\u1ecdc d\u1eef li\u1ec7u gi\u1eefa 2 sheet b\u1ecb tr\u00f9ng th\u1ef1c hi\u1ec7n nh\u01b0 sau:<\/p>\n

B\u01b0\u1edbc 1<\/strong>: M\u1edf t\u1ec7p excel c\u1ee7a b\u1ea1n, trong sheet 1 t\u1ea1o th\u00eam 1 c\u1ed9t \u0111\u1ec3 Ki\u1ec3m tra => Nh\u1eadp h\u00e0m =IF(ISNA(VLOOKUP(V\u00f9ng l\u1ecdc d\u1eef li\u1ec7u sheet 1,V\u00f9ng l\u1ecdc d\u1eef li\u1ec7u sheet 2 ,1,0)),”Kh\u00f4ng”,”Tr\u00f9ng”)<\/strong> t\u1ea1i \u00f4 b\u00ean c\u1ea1nh d\u1eef li\u1ec7u sheet 1.<\/p>\n

\"\"<\/p>\n

Ki\u1ec3m tra d\u1eef li\u1ec7u tr\u00f9ng c\u1ee7a 2 sheet<\/em><\/p>\n

B\u01b0\u1edbc 2<\/strong>: M\u00e0n h\u00ecnh s\u1ebd hi\u1ec3n th\u1ecb k\u1ebft qu\u1ea3 xem d\u1eef li\u1ec7u c\u00f3 tr\u00f9ng hay kh\u00f4ng \u1edf b\u00ean c\u1ea1nh, k\u00e9o xu\u1ed1ng nh\u1eefng \u00f4 b\u00ean d\u01b0\u1edbi \u0111\u1ec3 hi\u1ec3n th\u1ecb th\u00eam k\u1ebft qu\u1ea3.<\/p>\n

\"\"<\/p>\n

Nh\u01b0 v\u1eady, k\u1ebft qu\u1ea3 b\u1ea3ng d\u1eef li\u1ec7u sau khi l\u1ecdc s\u1ebd cho th\u1ea5y nh\u1eefng d\u1eef li\u1ec7u tr\u00f9ng hay kh\u00f4ng tr\u00f9ng t\u1ea1i c\u1ed9t Ki\u1ec3m tra.<\/p>\n

\"\"K\u1ebft qu\u1ea3 th\u00f4ng b\u00e1o Tr\u00f9ng v\u00e0 Kh\u00f4ng cho b\u1ea3ng ki\u1ec3m tra<\/em><\/p>\n

Tr\u00ean \u0111\u00e2y l\u00e0 h\u01b0\u1edbng d\u1eabn c\u00e1ch l\u1ecdc d\u1eef li\u1ec7u tr\u00f9ng tr\u00ean 1 sheet 2 sheet trong Excel si\u00eau \u0111\u01a1n gi\u1ea3n gi\u00fap b\u1ea1n x\u1eed l\u00fd nhanh, hi\u1ec7u qu\u1ea3 s\u1ed1 li\u1ec7u.<\/p>\n","protected":false},"excerpt":{"rendered":"

<\/div>
<\/div>