{"id":24378,"date":"2021-05-11T15:41:31","date_gmt":"2021-05-11T08:41:31","guid":{"rendered":"https:\/\/ben.com.vn\/tin-tuc\/?p=24378"},"modified":"2021-11-23T17:00:46","modified_gmt":"2021-11-23T10:00:46","slug":"ham-chuyen-so-thanh-chu-trong-excel","status":"publish","type":"post","link":"https:\/\/ben.com.vn\/tin-tuc\/ham-chuyen-so-thanh-chu-trong-excel\/","title":{"rendered":"H\u00e0m chuy\u1ec3n s\u1ed1 th\u00e0nh ch\u1eef trong Excel v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng"},"content":{"rendered":"

Trong Excel c\u00f3 nhi\u1ec1u h\u00e0m v\u00e0 c\u00e1ch chuy\u1ec3n s\u1ed1 th\u00e0nh ch\u1eef kh\u00e1c nhau. Trong b\u00e0i vi\u1ebft d\u01b0\u1edbi \u0111\u00e2y Ben Computer s\u1ebd gi\u1edbi thi\u1ec7u cho b\u1ea1n m\u1ed9t s\u1ed1 h\u00e0m chuy\u1ec3n s\u1ed1 th\u00e0nh ch\u1eef<\/strong> v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng c\u00e1c h\u00e0m Excel n\u00e0y nh\u01b0 th\u1ebf n\u00e0o. Tham kh\u1ea3o ti\u1ebfp b\u00e0i vi\u1ebft d\u01b0\u1edbi \u0111\u00e2y.<\/p>\n

H\u00e0m chuy\u1ec3n s\u1ed1 th\u00e0nh ch\u1eef trong Excel v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng<\/strong><\/h2>\n

\u0110\u1ec3 chuy\u1ec3n s\u1ed1 th\u00e0nh ch\u1eef trong Excel, ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c k\u1ebft h\u1ee3p c\u00e1c h\u00e0m ADDRESS v\u00e0 h\u00e0m SUBSTITUTE.<\/p>\n

C\u00fa ph\u00e1p h\u00e0m Excel c\u00f3 d\u1ea1ng nh\u01b0 d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n

=SUBSTITUTE(ADDRESS(1,col_number,4),\u201d1\u2033,\u201d\u201d)<\/strong><\/p><\/blockquote>\n

\u0110\u1ec3 d\u1ec5 d\u00e0ng h\u00ecnh dung c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m Excel n\u00e0y, b\u1ea1n \u0111\u1ecdc tham kh\u1ea3o v\u00ed d\u1ee5 d\u01b0\u1edbi \u0111\u00e2y. Cho b\u1ea3ng t\u00ednh c\u00f3 ch\u1ee9a d\u1eef li\u1ec7u nh\u01b0 sau:<\/p>\n

\"1<\/p>\n

Trong b\u1ea3ng t\u00ednh tr\u00ean, ch\u00fang ta s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c trong \u00f4 C5 c\u00f3 d\u1ea1ng:<\/p>\n

=SUBSTITUTE(ADDRESS(1,B5,4),\u201d1\u2033,\u201d\u201d)<\/strong><\/p>\n

Trong c\u00f4ng th\u1ee9c tr\u00ean, ch\u00fang ta s\u1eed d\u1ee5ng tham s\u1ed1 column number l\u00e0 1, c\u1ed9t l\u00e0 B4 v\u00e0 4 l\u00e0m tham s\u1ed1 cho tham s\u1ed1 abs_num h\u00e0m ADDRESS (\u0111\u1ec3 l\u1ea5y tham chi\u1ebfu t\u01b0\u01a1ng \u0111\u1ed1i):<\/p>\n

ADDRESS(1,B5,4)<\/strong><\/p>\n

H\u00e0m ADDRESS s\u1ebd tr\u1ea3 v\u1ec1 v\u0103n b\u1ea3n l\u00e0 A1.<\/p>\n

Ti\u1ebfp theo ch\u00fang ta s\u1eed d\u1ee5ng h\u00e0m SUBSTITUTE \u0111\u1ec3 lo\u1ea1i b\u1ecf s\u1ed1 1 nh\u01b0 sau:<\/p>\n

=SUBSTITUTE(\u201cA1\u2033,\u201d1\u2033,\u201d\u201d)<\/strong><\/p>\n

C\u00e1ch chuy\u1ec3n s\u1ed1 th\u00e0nh ch\u1eef trong Excel kh\u00e1c<\/strong><\/h2>\n

Ngo\u00e0i c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m chuy\u1ec3n s\u1ed1 th\u00e0nh ch\u1eef trong Excel<\/a><\/strong> tr\u00ean, b\u1ea1n c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n theo c\u00e1c b\u01b0\u1edbc d\u01b0\u1edbi \u0111\u00e2y \u0111\u1ec3 thao t\u00e1c tr\u00ean Excel 2016 v\u00e0 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 t\u01b0\u01a1ng t\u1ef1:<\/p>\n

B\u01b0\u1edbc 1<\/em>:<\/strong> Tr\u00ean c\u1eeda s\u1ed5 b\u1ea3ng t\u00ednh Excel, t\u00ecm v\u00e0 click ch\u1ecdn\u00a0tab File<\/strong>\u00a0n\u1eb1m g\u00f3c tr\u00ean c\u00f9ng thanh c\u00f4ng c\u1ee5, ch\u1ecdn\u00a0Options<\/strong>.<\/p>\n

B\u01b0\u1edbc 2:<\/em><\/strong>\u00a0Tr\u00ean c\u1eeda s\u1ed5 Excel Options hi\u1ec3n th\u1ecb tr\u00ean m\u00e0n h\u00ecnh, click ch\u1ecdn\u00a0Formulas<\/strong>\u00a0\u1edf khung b\u00ean tr\u00e1i. Ti\u1ebfp theo t\u00ecm v\u00e0 b\u1ecf t\u00edch t\u00f9y ch\u1ecdn\u00a0R1C1 reference style<\/strong>\u00a0r\u1ed3i click ch\u1ecdn\u00a0OK.<\/strong><\/p>\n

\"2<\/p>\n

B\u01b0\u1edbc 3:<\/em>\u00a0<\/strong>L\u00fac n\u00e0y tr\u00ean b\u1ea3ng t\u00ednh c\u1ee7a b\u1ea1n, ti\u00eau \u0111\u1ec1 c\u00e1c c\u1ed9t s\u1ebd hi\u1ec3n th\u1ecb l\u00e0 c\u00e1c ch\u1eef c\u00e1i A, B, C thay v\u00ec c\u00e1c s\u1ed1 1, 2, 3.<\/p>\n

S\u1eed d\u1ee5ng h\u00e0m CHAR chuy\u1ec3n s\u1ed1 th\u00e0nh ch\u1eef trong Excel<\/strong><\/em><\/p>\n

B\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m Char c\u0169ng l\u00e0 m\u1ed9t c\u00e1ch \u0111\u1ec3 chuy\u1ec3n s\u1ed1 th\u00e0nh ch\u1eef trong Excel nhanh ch\u00f3ng v\u00e0 \u0111\u01a1n gi\u1ea3n.
\nC\u00f4ng th\u1ee9c h\u00e0m Char trong Excel c\u00f3 d\u1ea1ng nh\u01b0 d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n

=CHAR(64+A1)<\/strong><\/p>\n

Trong \u0111\u00f3: A1<\/strong>\u00a0l\u00e0 \u00f4 c\u00f3 ch\u1ee9a gi\u00e1 tr\u1ecb s\u1ed1 m\u00e0 b\u1ea1n mu\u1ed1n chuy\u1ec3n sang k\u00fd t\u1ef1 v\u0103n b\u1ea3n, ch\u1eef.<\/p>\n

Cho v\u00ed d\u1ee5, trong v\u00ed d\u1ee5 d\u01b0\u1edbi \u0111\u00e2y ch\u00fang ta s\u1eed d\u1ee5ng h\u00e0m Char \u0111\u1ec3 chuy\u1ec3n s\u1ed1 th\u00e0nh ch\u1eef trong \u00f4 A1.<\/p>\n

C\u00f4ng th\u1ee9c h\u00e0m Excel c\u00f3 d\u1ea1ng nh\u01b0 d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n

\"3<\/p>\n

K\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 c\u00f3 d\u1ea1ng:<\/p>\n

\"4<\/p>\n

S\u1eed d\u1ee5ng ph\u1ea7n m\u1ec1m vnTools<\/strong><\/h2>\n

B\u01b0\u1edbc 1:<\/em><\/strong> \u0110\u1ea7u ti\u00ean t\u1ea3i ph\u1ea7n m\u1ec1m vnTools v\u1ec1 m\u00e1y v\u00e0i c\u00e0i \u0111\u1eb7t.<\/p>\n

\"Ph\u1ea7n<\/p>\n

T\u1ea3i vnTools v\u1ec1 m\u00e1y v\u00e0 c\u00e0i \u0111\u1eb7t t\u1ea1i \u0111\u00e2y.<\/p>\n

DOWNLOAD<\/a><\/p>\n

B\u01b0\u1edbc 2:<\/em><\/strong>\u00a0Ti\u1ebfp theo ti\u1ebfn h\u00e0nh c\u00e0i Add-in v\u00e0o Excel. \u0110\u1ec3 l\u00e0m \u0111\u01b0\u1ee3c \u0111i\u1ec1u n\u00e0y, tr\u00ean thanh c\u00f4ng c\u1ee5 Excel t\u00ecm v\u00e0 click ch\u1ecdn\u00a0File<\/strong>\u00a0=>\u00a0Options.<\/strong><\/p>\n

B\u01b0\u1edbc 3<\/em>:<\/strong> Tr\u00ean c\u1eeda s\u1ed5 Excel Options, \u1edf khung b\u00ean tr\u00e1i b\u1ea1n t\u00ecm v\u00e0 click ch\u1ecdn\u00a0Add-ins.<\/strong><\/p>\n

B\u01b0\u1edbc 4:<\/em>\u00a0<\/strong>Trong m\u1ee5c\u00a0Add-ins<\/strong>\u00a0\u1edf khung b\u00ean ph\u1ea3i, cu\u1ed9n xu\u1ed1ng t\u00ecm menu Manage v\u00e0 click ch\u1ecdn t\u00f9y ch\u1ecdn\u00a0Excel Add-ins<\/strong>\u00a0r\u1ed3i click ch\u1ecdn Go.<\/p>\n

B\u01b0\u1edbc 5:<\/em><\/strong>\u00a0Click ch\u1ecdn\u00a0Browse<\/strong>\u00a0v\u00e0 duy\u1ec7t \u0111\u1ebfn v\u1ecb tr\u00ed, th\u01b0 m\u1ee5c ch\u1ee9a file vnTools m\u00e0 b\u1ea1n \u0111\u00e3 t\u1ea3i v\u1ec1 r\u1ed3i click ch\u1ecdn OK.<\/p>\n

B\u01b0\u1edbc 6<\/em>:<\/strong> \u0110\u00e1nh t\u00edch ch\u1ecdn \u00f4 n\u1eb1m k\u1ebf b\u00ean\u00a0NTD\u2019s Tools<\/strong>\u00a0r\u1ed3i click ch\u1ecdn\u00a0OK.<\/strong><\/p>\n

B\u01b0\u1edbc 7<\/em>:<\/strong> Tr\u00ean c\u1eeda s\u1ed5 Excel b\u1ea1n click ch\u1ecdn\u00a0tab vnTools v<\/strong>\u00e0 ch\u1ecdn t\u00f9y ch\u1ecdn\u00a0\u0110\u1ed5i s\u1ed1 th\u00e0nh ch\u1eef (VN).<\/strong><\/p>\n

B\u01b0\u1edbc 8:<\/em>\u00a0<\/strong>Tr\u00ean h\u1ed9p tho\u1ea1i Number to Text (VN), ch\u1ecdn \u0111\u01a1n v\u1ecb trong menu\u00a0\u0110\u01a1n v\u1ecb ch\u00ednh, ph\u1ee5<\/strong>\u00a0r\u1ed3i click ch\u1ecdn bi\u1ec3u t\u01b0\u1ee3ng d\u1ea5u tr\u1eeb n\u1eb1m trong menu\u00a0\u00d4 ch\u1ee9a k\u00e9t qu\u1ea3.<\/strong><\/p>\n

B\u01b0\u1edbc 9<\/em>:<\/strong> Ch\u1ecdn \u00f4 m\u00e0 b\u1ea1n mu\u1ed1n ch\u1ee9a k\u1ebft qu\u1ea3 r\u1ed3i click ch\u1ecdn bi\u1ec3u t\u01b0\u1ee3ng l\u00e0 xong.<\/p>\n

B\u00e0i vi\u1ebft tr\u00ean \u0111\u00e2y Ben Computer v\u1eeba gi\u1edbi thi\u1ec7u cho b\u1ea1n m\u1ed9t s\u1ed1 h\u00e0m chuy\u1ec3n s\u1ed1 th\u00e0nh ch\u1eef trong Excel<\/strong> v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng. Ngo\u00e0i ra n\u1ebfu c\u00f2n th\u1eafc m\u1eafc ho\u1eb7c c\u00e2u h\u1ecfi n\u00e0o c\u1ea7n gi\u1ea3i \u0111\u00e1p, b\u1ea1n \u0111\u1ecdc c\u00f3 th\u1ec3 \u0111\u1ec3 l\u1ea1i \u00fd ki\u1ebfn c\u1ee7a m\u00ecnh trong ph\u1ea7n b\u00ecnh lu\u1eadn b\u00ean d\u01b0\u1edbi b\u00e0i vi\u1ebft nh\u00e9.<\/p>\n

B\u00ean c\u1ea1nh \u0111\u00f3 n\u1ebfu ch\u01b0a bi\u1ebft c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m Countif trong Excel<\/a> nh\u01b0 th\u1ebf n\u00e0o, b\u1ea1n \u0111\u1ecdc c\u00f3 th\u1ec3 tham kh\u1ea3o th\u00eam m\u1ed9t s\u1ed1 b\u00e0i vi\u1ebft \u0111\u00e3 c\u00f3 tr\u00ean Ben Computer \u0111\u1ec3 t\u00ecm hi\u1ec3u th\u00eam nh\u00e9.<\/p>\n

C\u00f3 th\u1ec3 b\u1ea1n quan t\u00e2m:<\/p>\n