{"id":289664,"date":"2023-11-29T18:55:18","date_gmt":"2023-11-29T11:55:18","guid":{"rendered":"https:\/\/hoanghamobile.com\/tin-tuc\/?p=289664"},"modified":"2023-11-29T18:55:18","modified_gmt":"2023-11-29T11:55:18","slug":"ham-concatenate","status":"publish","type":"post","link":"https:\/\/hoanghamobile.com\/tin-tuc\/ham-concatenate\/","title":{"rendered":"C\u00e1ch d\u00f9ng h\u00e0m CONCATENATE trong Excel c\u00f3 v\u00ed d\u1ee5 chi ti\u1ebft"},"content":{"rendered":"

H\u00e0m CONCATENATE <\/b>trong Excel \u0111\u01b0\u1ee3c d\u00f9ng \u0111\u1ec3 n\u1ed1i chu\u1ed7i k\u00fd t\u1ef1 v\u1edbi nhau nhanh ch\u00f3ng. \u0110i\u1ec1u n\u00e0y gi\u00fap ng\u01b0\u1eddi d\u00f9ng t\u1ed5ng h\u1ee3p c\u0169ng nh\u01b0 hi\u1ec3n th\u1ecb d\u1eef li\u1ec7u trong b\u1ea3ng t\u00ednh m\u1ed9t c\u00e1ch t\u1ed1i \u01b0u. V\u1eady c\u00e1ch s\u1eed d\u1ee5ng CONCATENATE trong Excel nh\u01b0 th\u1ebf n\u00e0o? M\u1eddi c\u00e1c b\u1ea1n theo d\u00f5i b\u00e0i vi\u1ebft d\u01b0\u1edbi \u0111\u00e2y c\u1ee7a Ho\u00e0ng H\u00e0 Mobile \u0111\u1ec3 bi\u1ebft th\u00f4ng tin chi ti\u1ebft nh\u00e9!\u00a0<\/span><\/p>\n

H\u00e0m CONCATENATE l\u00e0 g\u00ec?<\/span><\/h2>\n

Trong Excel, CONCATENATE l\u00e0 h\u00e0m \u0111\u01b0\u1ee3c d\u00f9ng n\u1ed1i chu\u1ed7i c\u00e1c k\u00fd t\u1ef1 v\u1edbi nhau. C\u00e1c k\u00fd t\u1ef1 \u0111\u01b0\u1ee3c n\u1ed1i l\u00e0 v\u0103n b\u1ea3n, d\u1ea5u c\u00e2u, kho\u1ea3ng tr\u1ed1ng,… C\u00fa ph\u00e1p CONCATENATE r\u1ea5t d\u1ec5 nh\u1edb:\u00a0<\/span><\/p>\n

=CONCATENATE(text1, text2,\u2026),<\/strong> trong \u0111\u00f3 text 1, text 2 l\u00e0 d\u1eef li\u1ec7u c\u1ea7n n\u1ed1i chu\u1ed7i v\u1edbi nhau.\u00a0<\/span><\/p>\n

L\u01b0u \u00fd, m\u1ecdi ng\u01b0\u1eddi h\u00e3y c\u00e1ch c\u00e1c chu\u1ed7i b\u1eb1ng d\u1ea5u (,) v\u00e0 c\u00f3 th\u1ec3 n\u1ed1i t\u1ed1i \u0111a 255 d\u00e3y t\u01b0\u01a1ng \u0111\u01b0\u01a1ng kho\u1ea3ng 8192 k\u00fd t\u1ef1. Tr\u01b0\u1eddng h\u1ee3p \u0111\u1ed1i s\u1ed1 \u0111\u1ea7u v\u00e0o l\u00e0 v\u0103n b\u1ea3n th\u00ec ng\u01b0\u1eddi d\u00f9ng c\u1ea7n th\u00eam d\u1ea5u \u201c \u201d ch\u1eb3ng h\u1ea1n nh\u01b0 \u201cHo\u00e0ng H\u00e0 Mobile\u201d. B\u00ean c\u1ea1nh \u0111\u00f3, \u0111\u00e1p \u00e1n c\u1ee7a c\u00f4ng th\u1ee9c CONCATENATE l\u00e0 d\u1ea1ng v\u0103n b\u1ea3n n\u00ean m\u1ecdi ng\u01b0\u1eddi kh\u00f4ng s\u1eed d\u1ee5ng h\u00e0m n\u00e0y \u0111\u1ec3 n\u1ed1i d\u1eef li\u1ec7u c\u00f3 ph\u00e9p t\u00ednh.\u00a0<\/span><\/p>\n

H\u01b0\u1edbng d\u1eabn s\u1eed d\u1ee5ng h\u00e0m CONCATENATE d\u1ec5 hi\u1ec3u nh\u1ea5t<\/span><\/h2>\n

Nh\u01b0 ch\u00fang t\u00f4i \u0111\u00e3 chia s\u1ebb \u1edf tr\u00ean, CONCATENATE l\u00e0 h\u00e0m d\u00f9ng n\u1ed1i chu\u1ed7i d\u1eef li\u1ec7u trong Excel. H\u00e0m c\u00f4ng th\u1ee9c n\u00e0y \u0111\u01b0\u1ee3c d\u00f9ng trong nhi\u1ec1u tr\u01b0\u1eddng h\u1ee3p v\u00e0 d\u1ef1a v\u00e0o m\u1ee5c \u0111\u00edch c\u1ee7a ng\u01b0\u1eddi d\u00f9ng. Sau \u0111\u00e2y, ch\u00fang t\u00f4i s\u1ebd h\u01b0\u1edbng d\u1eabn s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c CONCATENATE trong c\u00e1c tr\u01b0\u1eddng h\u1ee3p:\u00a0<\/span><\/p>\n

D\u00f9ng h\u00e0m CONCATENATE \u0111\u1ec3 n\u1ed1i k\u00fd t\u1ef1<\/span><\/h3>\n

N\u1ebfu b\u1ea1n c\u1ea7n n\u1ed1i c\u00e1c k\u00fd t\u1ef1, kho\u1ea3ng tr\u1eafng, text trong Excel th\u00ec s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c CONCATENATE. Ch\u00fang t\u00f4i s\u1ebd h\u01b0\u1edbng d\u1eabn c\u00e1ch n\u1ed1i chu\u1ed7i b\u1eb1ng CONCATENATE trong v\u00ed d\u1ee5 d\u01b0\u1edbi \u0111\u00e2y:\u00a0<\/span><\/p>\n

B\u01b0\u1edbc 1: T\u1ea1i v\u1ecb tr\u00ed \u00f4 mu\u1ed1n hi\u1ec3n th\u1ecb k\u1ebft qu\u1ea3 trong Excel, m\u1ecdi ng\u01b0\u1eddi h\u00e3y nh\u1eadp c\u00fa ph\u00e1p\u00a0 =CONCATENATE(A2,B2).\u00a0<\/span><\/p>\n

\"ham-Concatenate-2\"<\/picture><\/p>\n

B\u01b0\u1edbc 2: Ti\u1ebfp theo, m\u1ecdi ng\u01b0\u1eddi h\u00e3y nh\u1ea5n Enter tr\u00ean b\u00e0n ph\u00edm \u0111\u1ec3 hi\u1ec3n th\u1ecb k\u1ebft qu\u1ea3. Ch\u1ec9 v\u1edbi hai b\u01b0\u1edbc \u0111\u01a1n gi\u1ea3n, m\u1ecdi ng\u01b0\u1eddi \u0111\u00e3 n\u1ed1i chu\u1ed7i k\u00fd t\u1ef1 th\u00e0nh c\u00f4ng m\u00e0 kh\u00f4ng t\u1ed1n nhi\u1ec1u th\u1eddi gian thao t\u00e1c th\u1ee7 c\u00f4ng.\u00a0<\/span><\/p>\n

\"ham-Concatenate-3\"<\/picture><\/p>\n

D\u00f9ng CONCATENATE \u0111\u1ec3 n\u1ed1i chu\u1ed7i xu\u1ed1ng d\u00f2ng\u00a0<\/span><\/h3>\n

H\u00e0m CONCATENATE c\u00f2n \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng n\u1ed1i d\u00e3y k\u00fd t\u1ef1 xu\u1ed1ng d\u00f2ng. Tuy nhi\u00ean, ng\u01b0\u1eddi d\u00f9ng c\u1ea7n k\u1ebft h\u1ee3p th\u00eam v\u1edbi h\u00e0m CHAR (10) d\u00e0nh m\u00e1y t\u00ednh Windows v\u00e0 CHAR (13) \u0111\u1ed1i v\u1edbi m\u00e1y t\u00ednh Macbook. \u0110\u1ec3 n\u1ed1i chu\u1ed7i k\u00fd t\u1ef1 c\u00f3 xu\u1ed1ng d\u00f2ng, m\u1ecdi ng\u01b0\u1eddi h\u00e3y \u00e1p d\u1ee5ng c\u00e1c b\u01b0\u1edbc nh\u01b0 sau:\u00a0<\/span><\/p>\n

B\u01b0\u1edbc 1: \u0110\u1ea7u ti\u00ean, m\u1ecdi ng\u01b0\u1eddi h\u00e3y x\u00e1c \u0111\u1ecbnh v\u1ecb tr\u00ed mu\u1ed1n hi\u1ec3n th\u1ecb k\u1ebft qu\u1ea3 tr\u00ean b\u1ea3ng t\u00ednh. Ti\u1ebfp theo, ng\u01b0\u1eddi d\u00f9ng h\u00e3y ch\u1ecdn tab Home -> t\u1ea1i Alignment, ng\u01b0\u1eddi d\u00f9ng h\u00e3y ch\u1ecdn t\u00ednh n\u0103ng Wrap Text \u0111\u1ec3 \u0111i\u1ec1u ch\u1ec9nh \u0111\u1ecbnh d\u1ea1ng v\u0103n b\u1ea3n xu\u1ed1ng d\u00f2ng.\u00a0<\/span><\/p>\n

\"ham-Concatenate-4\"<\/picture><\/p>\n

B\u01b0\u1edbc 2: L\u00fac n\u00e0y, ng\u01b0\u1eddi d\u00f9ng h\u00e3y k\u1ebft h\u1ee3p CONCATENATE v\u1edbi CHAR (10). Ch\u1eb3ng h\u1ea1n nh\u01b0 t\u1ea1i v\u1ecb tr\u00ed H2, b\u1ea1n h\u00e3y nh\u1eadp c\u00f4ng th\u1ee9c:\u00a0<\/span><\/p>\n

=CONCATENATE(A2,CHAR(10),B2,CHAR(10),C2),<\/strong> sau \u0111\u00f3 nh\u1ea5n Enter tr\u00ean b\u00e0n ph\u00edm. Nh\u01b0 v\u1eady, k\u1ebft qu\u1ea3 n\u1ed1i chu\u1ed7i k\u00fd t\u1ef1 hi\u1ec3n th\u1ecb nhanh ch\u00f3ng, ch\u00ednh x\u00e1c gi\u00fap ng\u01b0\u1eddi d\u00f9ng t\u1ed1i \u01b0u th\u1eddi gian.\u00a0<\/span><\/p>\n

\"ham-Concatenate-5\"<\/picture><\/p>\n

D\u00f9ng h\u00e0m CONCATENATE \u0111\u1ec3 n\u1ed1i d\u1eef li\u1ec7u trong m\u1ed9t m\u1ea3ng\u00a0<\/span><\/h3>\n

Trong b\u1ea3ng t\u00ednh Excel, \u0111\u00f4i khi c\u00e1c gi\u00e1 tr\u1ecb kh\u00f4ng \u0111\u01b0\u1ee3c \u0111\u1ed3ng nh\u1ea5t m\u00e0 chia th\u00e0nh t\u1eebng m\u1ea3ng. Tuy nhi\u00ean, ng\u01b0\u1eddi d\u00f9ng c\u00f3 th\u1ec3 n\u1ed1i d\u1eef li\u1ec7u trong m\u1ed9t v\u00f9ng b\u1eb1ng c\u00fa ph\u00e1p CONCATENATE nhanh ch\u00f3ng. Trong th\u1ef1c t\u1ebf, \u0111\u1ec3 n\u1ed1i gi\u00e1 tr\u1ecb trong m\u1ed9t m\u1ea3ng, ng\u01b0\u1eddi d\u00f9ng c\u00f3 th\u1ec3 \u00e1p d\u1ee5ng m\u1ed9t trong hai c\u00e1ch sau:<\/span><\/p>\n

C\u00e1ch 1: \u0110\u1ed1i v\u1edbi m\u1ea3ng c\u00f3 \u00edt gi\u00e1 tr\u1ecb th\u00ec ng\u01b0\u1eddi d\u00f9ng li\u1ec7t k\u00ea \u0111\u1ed1i s\u1ed1 sau \u0111\u00f3 th\u00eam v\u00e0o c\u00f4ng th\u1ee9c CONCATENATE.\u00a0<\/span><\/p>\n

T\u1ea1i v\u1ecb tr\u00ed \u00f4 c\u1ea7n hi\u1ec3n th\u1ecb k\u1ebft qu\u1ea3<\/span> h\u00e0m CONCATENATE <\/b>trong b\u1ea3ng t\u00ednh, ng\u01b0\u1eddi d\u00f9ng h\u00e3y nh\u1eadp\u00a0 =CONCATENATE sau \u0111\u00f3 nh\u1ea5n v\u00e0 gi\u1eef Ctrl \u0111\u1ed3ng th\u1eddi ch\u1ecdn t\u1eebng gi\u00e1 tr\u1ecb \u0111\u1ec3 g\u00e1n c\u00f4ng th\u1ee9c theo \u00fd \u0111\u1ecbnh n\u1ed1i c\u1ee7a ng\u01b0\u1eddi d\u00f9ng r\u1ed3i d\u00f9ng d\u1ea5u ngo\u1eb7c ) \u0111\u1ec3 \u0111\u00f3ng h\u00e0m. Cu\u1ed1i c\u00f9ng, m\u1ecdi ng\u01b0\u1eddi h\u00e3y b\u1ea5m ph\u00edm Enter \u0111\u1ec3 hi\u1ec3n th\u1ecb k\u1ebft qu\u1ea3 n\u1ed1i d\u1eef li\u1ec7u trong m\u1ea3ng.\u00a0<\/span><\/p>\n

\"ham-Concatenate-6\"<\/picture><\/p>\n

C\u00e1ch 2: \u0110\u1ed1i v\u1edbi m\u1ea3ng c\u00f3 nhi\u1ec1u d\u1eef li\u1ec7u, ng\u01b0\u1eddi d\u00f9ng n\u00ean k\u1ebft h\u1ee3p TRANSPOSE tr\u01b0\u1edbc \u0111\u1ec3 l\u1ea5y chu\u1ed7i gi\u00e1 tr\u1ecb ti\u1ebfp theo d\u00f9ng CONCATENATE \u0111\u1ec3 n\u1ed1i gi\u00e1 tr\u1ecb.\u00a0<\/span><\/p>\n

\"ham-Concatenate-7\"<\/picture><\/p>\n

Ch\u1eb3ng h\u1ea1n nh\u01b0 t\u1ea1i \u00f4 c\u1ea7n hi\u1ec3n th\u1ecb k\u1ebft qu\u1ea3, ng\u01b0\u1eddi d\u00f9ng h\u00e3y nh\u1eadp =TRANSPOSE(F4:F6) sau \u0111\u00f3 nh\u1ea5n F9. L\u00fac n\u00e0y, b\u1ea3ng t\u00ednh s\u1ebd hi\u1ec3n th\u1ecb chu\u1ed7i gi\u00e1 tr\u1ecb m\u1ea3ng \u0111\u01b0\u1ee3c n\u1eb1m trong d\u1ea5u {}. Cu\u1ed1i c\u00f9ng, m\u1ecdi ng\u01b0\u1eddi s\u1ebd nh\u1eadp c\u00fa ph\u00e1p =CONCATENATE ph\u00eda tr\u01b0\u1edbc \u0111\u1ed3ng th\u1eddi b\u1ecf d\u1ea5u {} v\u00e0 th\u00eam d\u1ea5u ). L\u01b0u \u00fd, ng\u01b0\u1eddi d\u00f9ng h\u00e3y nh\u1eadp gi\u00e1 tr\u1ecb tham chi\u1ebfu v\u00e0 d\u1ea5u c\u1ea9n th\u1eadn \u0111\u1ec3 tr\u00e1nh x\u1ea3y ra sai s\u00f3t nh\u00e9!\u00a0<\/span><\/p>\n

D\u00f9ng h\u00e0m CONCATENATE k\u1ebft h\u1ee3p v\u1edbi h\u00e0m IF<\/span><\/h3>\n

Trong b\u1ea3ng t\u00ednh Excel, h\u00e0m CONCATENATE c\u00f2n \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng v\u1edbi h\u00e0m IF ph\u1ed5 bi\u1ebfn \u0111\u1ec3 n\u1ed1i chu\u1ed7i k\u00fd t\u1ef1 v\u00e0 k\u00e8m theo \u0111i\u1ec1u ki\u1ec7n. Khi k\u1ebft h\u1ee3p h\u00e0m th\u00ec ng\u01b0\u1eddi d\u00f9ng c\u1ea7n ch\u00fa \u00fd \u0111\u1ebfn c\u00e1c gi\u00e1 tr\u1ecb, tr\u00e1nh nh\u1ea7m l\u1eabn d\u1eabn \u0111\u1ebfn k\u1ebft qu\u1ea3 sai l\u1ec7ch. \u0110\u1ec3 gi\u00fap m\u1ecdi ng\u01b0\u1eddi hi\u1ec3u h\u01a1n c\u00e1ch k\u1ebft h\u1ee3p CONCATENATE v\u1edbi h\u00e0m IF trong Excel nh\u01b0 sau:\u00a0<\/span><\/p>\n

\"ham-Concatenate-8\"<\/picture><\/p>\n

\u0110\u1ed1i v\u1edbi b\u1ea3ng t\u00ednh g\u1ed3m c\u1ed9t \u0111i\u1ec3m TBM v\u00e0 x\u1ebfp h\u1ea1ng h\u1ecdc l\u1ef1c d\u1ef1a tr\u00ean \u0111i\u1ec3m TB m\u00f4n h\u1ecdc. \u0110\u1ec3 hi\u1ec3n th\u1ecb chu\u1ed7i k\u00fd t\u1ef1 t\u00ean h\u1ecdc sinh sau \u0111\u00f3 l\u00e0 x\u1ebfp h\u1ea1ng h\u1ecdc l\u1ef1c, m\u1ecdi ng\u01b0\u1eddi h\u00e3y nh\u1eadp h\u00e0m theo c\u00fa ph\u00e1p:\u00a0<\/span><\/p>\n

=IF(C3>=$F$3,CONCATENATE(B3,” – “,$G$3),IF(AND(C3>=$F$4, C3< $F$3),CONCATENATE(B3,” – “,$G$4), CONCATENATE(B3,” – “,$G$5)))<\/span><\/p>\n

\"ham-Concatenate-9\"<\/picture><\/p>\n

Sau \u0111\u00f3 nh\u1ea5n ph\u00edm Enter tr\u00ean b\u00e0n ph\u00edm, k\u1ebft qu\u1ea3 s\u1ebd tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u00ean h\u1ecdc sinh v\u00e0 \u0111i\u1ec3m trung b\u00ecnh v\u1edbi \u0111i\u1ec1u ki\u1ec7n >= 8 (h\u1ecdc sinh gi\u1ecfi), n\u1ebfu 7 <= \u0111\u1ea1t h\u1ecdc l\u1ef1c kh\u00e1 v\u00e0 tr\u01b0\u1eddng h\u1ee3p c\u00f2n l\u1ea1i l\u00e0 \u0111i\u1ec3m trung b\u00ecnh.\u00a0<\/span><\/p>\n

D\u00f9ng h\u00e0m TEXTJOIN \u0111\u1ec3 n\u1ed1i k\u00fd t\u1ef1\u00a0<\/span><\/h3>\n

Trong Excel, ngo\u00e0i h\u00e0m CONCATENATE, ng\u01b0\u1eddi d\u00f9ng c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m TEXTJOIN \u0111\u1ec3 n\u1ed1i chu\u1ed7i v\u0103n b\u1ea3n trong m\u1ea3ng v\u00e0 c\u00f3 th\u1ec3 k\u1ebft h\u1ee3p th\u00eam d\u1ea5u c\u00e1ch, k\u00fd t\u1ef1 kh\u00e1c. H\u00e0m TEXTJOIN c\u00f3 c\u00fa ph\u00e1p nh\u01b0 sau:\u00a0<\/span><\/p>\n

=TEXTJOIN(d\u1ea5u t\u00e1ch; ignore_empty; text 1; [text 2; \u2026])<\/span><\/p>\n

Trong \u0111\u00f3:\u00a0<\/span><\/p>\n