{"id":7745,"date":"2021-08-15T08:51:19","date_gmt":"2021-08-15T01:51:19","guid":{"rendered":"https:\/\/tct.vn\/blog\/?p=7745"},"modified":"2021-08-18T11:08:26","modified_gmt":"2021-08-18T04:08:26","slug":"huong-dan-cach-dem-ky-tu-ky-tu-dac-biet-trong-mot-o-hoac-mot-dai-o-trong-excel","status":"publish","type":"post","link":"https:\/\/tct.vn\/blog\/huong-dan-cach-dem-ky-tu-ky-tu-dac-biet-trong-mot-o-hoac-mot-dai-o-trong-excel\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn c\u00e1ch \u0111\u1ebfm k\u00fd t\u1ef1, k\u00fd t\u1ef1 \u0111\u1eb7c bi\u1ec7t trong m\u1ed9t \u00f4 ho\u1eb7c m\u1ed9t d\u1ea3i \u00f4 trong Excel"},"content":{"rendered":"

C\u00c1CH \u0110\u1ebeM T\u1ea4T C\u1ea2 K\u00dd T\u1ef0 TRONG M\u1ed8T D\u1ea2I \u00d4<\/h2>\n

Khi n\u00f3i \u0111\u1ebfn vi\u1ec7c \u0111\u1ebfm t\u1ea5t c\u1ea3 k\u00fd t\u1ef1 trong m\u1ed9t v\u00e0i \u00f4 th\u00ec gi\u1ea3i ph\u00e1p m\u00e0 b\u1ea1n ngh\u0129 ngay \u0111\u1ebfn \u0111\u00f3 ch\u00ednh l\u00e0 \u0111\u1ebfm s\u1ed1 k\u00fd t\u1ef1 trong m\u1ed7i \u00f4, r\u1ed3i c\u1ed9ng c\u00e1c con s\u1ed1 \u0111\u00f3 l\u1ea1i:<\/p>\n

=LEN(A2)+(A3)+(A4)<\/em><\/strong><\/p>\n

Hay<\/p>\n

=SUM(LEN(A2),LEN(A3),LEN(A4))<\/strong><\/em><\/p>\n

\n
<\/div>\n<\/div>\n

C\u00e1c c\u00f4ng th\u1ee9c tr\u00ean c\u00f3 l\u1ebd s\u1ebd hi\u1ec7u qu\u1ea3 trong m\u1ed9t d\u1ea3i \u00f4 nh\u1ecf. \u0110\u1ec3 \u0111\u1ebfm t\u1ea5t c\u1ea3 k\u00fd t\u1ef1 trong m\u1ed9t d\u1ea3i \u00f4 l\u1edbn h\u01a1n, ch\u00fang ta n\u00ean ngh\u0129 ra m\u1ed9t c\u00f4ng th\u1ee9c kh\u00e1c g\u1ecdn h\u01a1n, v\u00ed d\u1ee5: h\u00e0m SUMPRODUCT, h\u00e0m n\u00e0y s\u1ebd c\u1ed9ng c\u00e1c m\u1ea3ng l\u1ea1i v\u1edbi nhau r\u1ed3i tr\u1ea3 v\u1ec1 t\u1ed5ng c\u1ee7a c\u00e1c k\u1ebft qu\u1ea3 \u0111\u00f3.<\/p>\n

\u0110\u00e2y l\u00e0 c\u00f4ng th\u1ee9c chung trong Excel \u0111\u1ec3 \u0111\u1ebfm t\u1ea5t c\u1ea3 k\u00fd t\u1ef1 trong m\u1ed9t d\u1ea3i \u00f4:<\/p>\n

=SUMPRODUCT(LEN(d\u1ea3i \u00f4))<\/em><\/strong><\/p>\n

V\u00e0 khi \u00e1p d\u1ee5ng n\u00f3 s\u1ebd tr\u00f4ng nh\u01b0 th\u1ebf n\u00e0y:<\/p>\n

=SUMPRODUCT(LEN(A1:A7))\"\"<\/em><\/strong><\/p>\n

C\u00e1ch kh\u00e1c \u0111\u1ec3 \u0111\u1ebfm s\u1ed1 k\u00fd t\u1ef1 trong m\u1ed9t d\u1ea3i \u00f4 \u0111\u00f3 l\u00e0 s\u1eed d\u1ee5ng h\u00e0m LEN k\u1ebft h\u1ee3p v\u1edbi h\u00e0m SUM:<\/p>\n

=SUM(LEN(A1:A7))<\/em><\/strong><\/p>\n

Kh\u00f4ng gi\u1ed1ng h\u00e0m SUMPRODUCT, h\u00e0m SUM kh\u00f4ng t\u00ednh c\u00e1c m\u1ea3ng m\u1ed9t c\u00e1ch m\u1eb7c \u0111\u1ecbnh, v\u00e0 b\u1ea1n c\u1ea7n ph\u1ea3i nh\u1ea5n\u00a0Ctrl+Shift+Enter<\/em><\/strong>\u00a0\u0111\u1ec3 \u0111\u01b0a n\u00f3 v\u1ec1 c\u00f4ng th\u1ee9c m\u1ea3ng.<\/p>\n

Khi \u0111\u01b0\u1ee3c di\u1ec5n gi\u1ea3i trong \u1ea3nh ch\u1ee5p m\u00e0n h\u00ecnh d\u01b0\u1edbi \u0111\u00e2y, h\u00e0m SUM tr\u1ea3 v\u1ec1 c\u00f9ng m\u1ed9t k\u1ebft qu\u1ea3 s\u1ed1 k\u00fd t\u1ef1:\"\"<\/p>\n

C\u00d4NG TH\u1ee8C \u0110\u1ebeM S\u1ed0 K\u00dd T\u1ef0 TRONG M\u1ed8T D\u1ea2I \u00d4 HO\u1ea0T \u0110\u1ed8NG NH\u01af TH\u1ebe N\u00c0O<\/p>\n

\u0110\u00e2y l\u00e0 m\u1ed9t trong nh\u1eefng c\u00f4ng th\u1ee9c d\u1ec5 nh\u1ea5t \u0111\u1ec3 \u0111\u1ebfm s\u1ed1 k\u00fd t\u1ef1 trong Excel. H\u00e0m LEN t\u00ednh to\u00e1n chi\u1ec1u d\u00e0i chu\u1ed7i cho m\u1ed7i \u00f4 trong m\u1ed9t d\u1ea3i \u00f4 x\u00e1c \u0111\u1ecbnh r\u1ed3i tr\u1ea3 v\u1ec1 m\u1ed9t d\u00e3y s\u1ed1. Sau \u0111\u00f3, h\u00e0m SUMPRODUCT hay SUM c\u1ed9ng c\u00e1c con s\u1ed1 n\u00e0y v\u1edbi nhau v\u00e0 hi\u1ec7n k\u1ebft qu\u1ea3 l\u00e0 t\u1ed5ng s\u1ed1 c\u00e1c k\u00fd t\u1ef1.<\/p>\n

Trong c\u00e1c v\u00ed d\u1ee5 tr\u00ean, m\u1ed9t d\u00e3y g\u1ed3m b\u1ea3y con s\u1ed1ch\u00ednh l\u00e0 chi\u1ec1u d\u00e0i chu\u1ed7i trong c\u00e1c \u00f4 t\u1eeb A1 \u0111\u1ebfn A7 khi \u0111\u01b0\u1ee3c c\u1ed9ng:<\/p>\n

=SUMPRODUCT({28;29;27;23;27;29;30})<\/em><\/strong><\/p>\n

    \n
  • L\u01b0u \u00fd.<\/strong>\u00a0H\u00e3y ch\u00fa \u00fd r\u1eb1ng h\u00e0m LEN trong Excel c\u1ed9ng\u00a0t\u1ea5t c\u1ea3 c\u00e1c k\u00fd t\u1ef1 trong m\u1ed7i \u00f4<\/strong>\u00a0m\u1ed9t c\u00e1ch tuy\u1ec7t \u0111\u1ed1i, k\u1ec3 c\u1ea3 ch\u1eef c\u00e1i, ch\u1eef s\u1ed1, d\u1ea5u c\u00e2u, k\u00fd t\u1ef1 \u0111\u1eb7c bi\u1ec7t, v\u00e0 t\u1ea5t c\u1ea3 c\u00e1c kho\u1ea3ng tr\u1ed1ng (th\u1ee5t \u0111\u1ea7u d\u00f2ng, th\u1ee5t cu\u1ed1i d\u00f2ng v\u00e0 kho\u1ea3ng c\u00e1ch gi\u1eefa c\u00e1c t\u1eeb).<\/em><\/li>\n<\/ul>\n

    <\/span>L\u00c0M TH\u1ebe N\u00c0O \u0110\u1ec2 \u0110\u1ebeM C\u00c1C K\u00dd T\u1ef0 \u0110\u1eb6C BI\u1ec6T TRONG M\u1ed8T \u00d4<\/h2>\n

    Th\u1ec9nh tho\u1ea3ng, thay v\u00ec ph\u1ea3i \u0111\u1ebfm t\u1ea5t c\u1ea3 k\u00fd t\u1ef1 trong m\u1ed9t \u00f4, b\u1ea1n c\u00f3 th\u1ec3 ch\u1ec9 c\u1ea7n ph\u1ea3i \u0111\u1ebfm s\u1ef1 xu\u1ea5t hi\u1ec7n c\u1ee7a m\u1ed9t k\u00fd t\u1ef1, ch\u1eef s\u1ed1, hay bi\u1ec3u t\u01b0\u1ee3ng \u0111\u1eb7c bi\u1ec7t.<\/p>\n

    \u0110\u1ec3 \u0111\u1ebfm t\u1ea7n su\u1ea5t xu\u1ea5t hi\u1ec7n c\u1ee7a m\u1ed9t k\u00fd t\u1ef1 x\u00e1c \u0111\u1ecbnh trong m\u1ed9t \u00f4, h\u00e3y s\u1eed d\u1ee5ng h\u00e0m LEN k\u1ebft h\u1ee3p v\u1edbi h\u00e0m SUBSTITUTE:<\/p>\n

    =LEN(\u00f4)-LEN(SUBSTITUTE(\u00f4, k\u00fd t\u1ef1,\u201d\u201d))<\/em><\/strong><\/p>\n

    \u0110\u1ec3 hi\u1ec3u r\u00f5 v\u00ed d\u1ee5 h\u01a1n, h\u00e3y xem qua v\u00ed d\u1ee5 d\u01b0\u1edbi \u0111\u00e2y nh\u00e9.<\/p>\n

    G\u1ec9\u1ea3 s\u1eed, b\u1ea1n l\u01b0u gi\u1eef m\u1ed9t c\u01a1 s\u1edf d\u1eef li\u1ec7u c\u1ee7a nh\u1eefng m\u00f3n h\u00e0ng \u0111\u01b0\u1ee3c giao, n\u01a1i m\u00e0 m\u1ed7i lo\u1ea1i h\u00e0ng c\u00f3 k\u00fd hi\u1ec7u nh\u1eadn d\u1ea1ng ri\u00eang bi\u1ec7t. V\u00e0 m\u1ed7i \u00f4 c\u00f3 m\u1ed9t v\u00e0i m\u00f3n h\u00e0ng c\u00e1ch nhau b\u1edfi d\u1ea5u ph\u1ea9y, kho\u1ea3ng tr\u1ed1ng, hay b\u1ea5t c\u1ee9 d\u1ea5u ph\u00e2n c\u00e1ch n\u00e0o. Nhi\u1ec7m v\u1ee5 c\u1ee7a b\u1ea1n l\u00e0 h\u00e3y \u0111\u1ebfm s\u1ed1 l\u1ea7n xu\u1ea5t hi\u1ec7n c\u1ee7a k\u00fd hi\u1ec7u nh\u1eadn d\u1ea1ng ri\u00eang bi\u1ec7t \u0111\u01b0\u1ee3c x\u00e1c \u0111\u1ecbnh trong m\u1ed7i \u00f4.<\/p>\n

    G\u1ec9\u1ea3 s\u1eed r\u1eb1ng danh s\u00e1ch c\u00e1c m\u00f3n h\u00e0ng n\u1eb1m \u1edf c\u1ed9t B (b\u1eaft \u0111\u1ea7u t\u1eeb B2) v\u00e0 ch\u00fang ta s\u1ebd \u0111\u1ebfm s\u1ed1 l\u1ea7n xu\u1ea5t hi\u1ec7n c\u1ee7a \u201cA\u201d, d\u01b0\u1edbi \u0111\u00e2y l\u00e0 c\u00f4ng th\u1ee9c:<\/p>\n

    =LEN(B2)-LEN(SUBSTITUTE(B2,\u201dA\u201d,\u201d\u201d))<\/em><\/strong>
    \n\"\"<\/p>\n

    C\u00d4NG TH\u1ee8C \u0110\u1ebeM K\u00dd T\u1ef0 N\u00c0Y TRONG EXCEL HO\u1ea0T \u0110\u1ed8NG NH\u01af TH\u1ebe N\u00c0O<\/p>\n

    \u0110\u1ec3 hi\u1ec3u quy lu\u1eadt c\u1ee7a c\u00f4ng th\u1ee9c n\u00e0y, h\u00e3y chia n\u00f3 ra th\u00e0nh t\u1eebng ph\u1ea7n nh\u1ecf:<\/p>\n

    \u0110\u1ea7u ti\u00ean, b\u1ea1n \u0111\u1ebfm t\u1ed5ng chi\u1ec1u d\u00e0i chu\u1ed7i c\u1ee7a \u00f4 B2:<\/p>\n

    LEN(B2)<\/em><\/strong><\/p>\n

    Sau \u0111\u00f3, b\u1ea1n d\u00f9ng h\u00e0m SUBSTITUTE \u0111\u1ec3 lo\u1ea1i b\u1ecf s\u1ed1 l\u1ea7n xu\u1ea5t hi\u1ec7n c\u1ee7a \u201cA\u201d trong B2 b\u1eb1ng c\u00e1ch thay th\u1ebf n\u00f3 b\u1eb1ng m\u1ed9t chu\u1ed7i r\u1ed7ng(\u201c\u201d):<\/p>\n

    SUBSTITUTE(B2,\u201dA\u201d,\u201d\u201d)<\/em><\/strong><\/p>\n

    Sau \u0111\u00f3, b\u1ea1n \u0111\u1ebfm chi\u1ec1u d\u00e0i chu\u1ed7i m\u00e0 kh\u00f4ng c\u00f3 k\u00fd t\u1ef1 \u201cA\u201d:<\/p>\n

    LEN(SUBSTITUTE(B2,\u201dA\u201d,\u201d\u201d))<\/em><\/strong><\/p>\n

    Cu\u1ed1i c\u00f9ng, b\u1ea1n l\u1ea5y t\u1ed5ng chi\u1ec1u d\u00e0i chu\u1ed7i tr\u1eeb cho chi\u1ec1u d\u00e0i c\u1ee7a chu\u1ed7i kh\u00f4ng c\u00f3 k\u00fd t\u1ef1 \u201cA\u201d.<\/p>\n

    Do \u0111\u00f3, b\u1ea1n t\u00ednh \u0111\u01b0\u1ee3c s\u1ed1 k\u00fd t\u1ef1 \u201c\u0111\u01b0\u1ee3c lo\u1ea1i b\u1ecf\u201d, k\u1ebft qu\u1ea3 n\u00e0y c\u0169ng l\u00e0 s\u1ed1 l\u1ea7n xu\u1ea5t hi\u1ec7n c\u1ee7a k\u00fd t\u1ef1 \u0111\u00f3 trong m\u1ed9t \u00f4.<\/p>\n

    Thay v\u00ec x\u00e1c \u0111\u1ecbnh k\u00fd t\u1ef1 b\u1ea1n mu\u1ed1n \u0111\u1ebfm trong c\u00f4ng th\u1ee9c, b\u1ea1n c\u00f3 th\u1ec3 g\u00f5 n\u00f3 v\u00e0o m\u1ed9t v\u00e0i \u00f4, r\u1ed3i tham chi\u1ebfu \u00f4 \u0111\u00f3 trong c\u00f4ng th\u1ee9c. B\u1eb1ng c\u00e1ch n\u00e0y, ng\u01b0\u1eddi d\u00f9ng c\u1ee7a b\u1ea1n s\u1ebd c\u00f3 kh\u1ea3 n\u0103ng \u0111\u1ebfm s\u1ed1 l\u1ea7n xu\u1ea5t hi\u1ec7n c\u1ee7a b\u1ea5t c\u1ee9 k\u00fd t\u1ef1 n\u00e0o h\u1ecd nh\u1eadp v\u00e0o \u00f4 \u0111\u00f3 m\u00e0 kh\u00f4ng ph\u00e1 c\u00f4ng th\u1ee9c c\u1ee7a b\u1ea1n:<\/p>\n

    \"\"<\/p>\n

      \n
    • L\u01b0u \u00fd.<\/strong> H\u00e0m SUBSTITUTE c\u1ee7a Excel l\u00e0 m\u1ed9t h\u00e0m ph\u00e2n bi\u1ec7t d\u1ea1ng ch\u1eef, v\u00e0 v\u00ec th\u1ebf c\u00e1c c\u00f4ng th\u1ee9c tr\u00ean \u0111\u1ec1u ph\u00e2n bi\u1ec7t d\u1ea1ng ch\u1eef theo. V\u00ed d\u1ee5, trong \u1ea3nh ch\u1ee5p m\u00e0n h\u00ecnh \u1edf tr\u00ean, \u00f4 B3 c\u00f3 3 l\u1ea7n xu\u1ea5t hi\u1ec7n c\u1ee7a \u201cA\u201d-hai l\u1ea7n vi\u1ebft hoa, v\u00e0 m\u1ed9t l\u1ea7n vi\u1ebft th\u01b0\u1eddng. C\u00f4ng th\u1ee9 c ch\u1ec9 \u0111\u1ebfm c\u00e1c k\u00fd t\u1ef1 vi\u1ebft hoa b\u1edfi v\u00ec ch\u00fang ta cung c\u1ea5p \u201cA\u201d cho h\u00e0m SUBSTITUTE.<\/li>\n<\/ul>\n

      Ch\u00fac b\u1ea1n th\u00e0nh c\u00f4ng.<\/p>\n","protected":false},"excerpt":{"rendered":"

      C\u00c1CH \u0110\u1ebeM T\u1ea4T C\u1ea2 K\u00dd T\u1ef0 TRONG M\u1ed8T D\u1ea2I \u00d4 Khi n\u00f3i \u0111\u1ebfn vi\u1ec7c \u0111\u1ebfm t\u1ea5t c\u1ea3 k\u00fd t\u1ef1 trong m\u1ed9t v\u00e0i \u00f4 th\u00ec gi\u1ea3i ph\u00e1p m\u00e0 b\u1ea1n ngh\u0129 ngay \u0111\u1ebfn \u0111\u00f3 ch\u00ednh l\u00e0 \u0111\u1ebfm s\u1ed1 k\u00fd t\u1ef1 trong m\u1ed7i \u00f4, r\u1ed3i c\u1ed9ng c\u00e1c con s\u1ed1 \u0111\u00f3 l\u1ea1i: =LEN(A2)+(A3)+(A4) Hay =SUM(LEN(A2),LEN(A3),LEN(A4)) C\u00e1c c\u00f4ng th\u1ee9c tr\u00ean […]<\/p>\n","protected":false},"author":3,"featured_media":7747,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[508],"tags":[],"class_list":["post-7745","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-office"],"_links":{"self":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/posts\/7745","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/comments?post=7745"}],"version-history":[{"count":0,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/posts\/7745\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/media\/7747"}],"wp:attachment":[{"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/media?parent=7745"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/categories?post=7745"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/tct.vn\/blog\/wp-json\/wp\/v2\/tags?post=7745"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}