{"id":7328,"date":"2024-12-21T17:20:38","date_gmt":"2024-12-21T17:20:38","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/textjoin-concat-thay-the-concatenate\/"},"modified":"2024-12-21T17:20:38","modified_gmt":"2024-12-21T17:20:38","slug":"textjoin-concat-thay-the-concatenate","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/textjoin-concat-thay-the-concatenate\/","title":{"rendered":"TEXTJOIN CONCAT thay th\u1ebf CONCATENATE"},"content":{"rendered":"
\n

B\u00e0i vi\u1ebft tr\u00ean \u0111\u01b0\u1ee3c H\u1ecdc Excel Online<\/strong> <\/a>d\u1ecbch v\u00e0 bi\u00ean t\u1eadp l\u1ea1i t\u1eeb 1 b\u00e0i chia s\u1ebb tr\u00ean c\u1ed9ng \u0111\u1ed3ng reddit: H\u00e0m TEXTJOIN v\u00e0 CONCAT \u2013 phi\u00ean b\u1ea3n cao c\u1ea5p h\u01a1n c\u1ee7a CONCATENATE<\/strong>.<\/a><\/em><\/p>\n

H\u00e0m CONCAT v\u00e0 TEXTJOIN thay th\u1ebf CONCATENATE<\/h3>\n

Nh\u1eefng ng\u00e0y huy ho\u00e0ng c\u1ee7a =CONCATENATE(A1, A2, A3, A4\u2026 An) \u0111\u00e3 kh\u00f4ng c\u00f2n n\u1eefa.<\/p>\n

H\u00e3y thay th\u1ebf c\u00f4ng th\u1ee9c tr\u00ean b\u1eb1ng m\u1ed9t th\u1ee9 \u0111\u01a1n gi\u1ea3n h\u01a1n:
=CONCAT(A1:An)<\/p>\n

\"concatenate-1\"<\/p>\n

Vi\u1ec7c n\u00e0y s\u1ebd khi\u1ebfn c\u00f4ng th\u1ee9c c\u1ee7a b\u1ea1n tr\u1edf n\u00ean g\u1ecdn g\u00e0ng h\u01a1n.<\/p>\n

V\u00e0 ta c\u0169ng c\u1ea7n ph\u1ea3i th\u00eam 1 \u0111\u1ed1ng d\u1ea5u ph\u00e2n c\u00e1ch nh\u01b0 d\u1ea5u c\u00e1ch, d\u1ea5u ph\u1ea9y\u2026 nh\u01b0 n\u00e0y: =CONCATENATE(A1, \u201d \u201c, A2, \u201c, \u201cA3, \u201c; \u201c, A4\u2026.. An), b\u1edfi \u0111\u00e3 c\u00f3 m\u1ed9t c\u00f4ng th\u1ee9c kh\u00e1c \u0111\u01a1n gi\u1ea3n h\u01a1n l\u00e0m h\u1ed9 vi\u1ec7c \u0111\u00f3:
=TEXTJOIN(\u201d \u201c, 1, A1:An)<\/p>\n

S\u1ed1 1 \u1edf tr\u00ean l\u00e0 g\u00ec?
Trong tr\u01b0\u1eddng h\u1ee3p b\u1ea1n c\u00f3 nh\u1eefng \u00f4 tr\u1ed1ng trong v\u00f9ng d\u1eef li\u1ec7u, n\u00f3 s\u1ebd b\u1ecf qua v\u00e0 ch\u1ec9 n\u1ed1i nh\u1eefng k\u00ed t\u1ef1 \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y. Kh\u00f4ng mu\u1ed1n b\u1ecf qua \u00f4 tr\u1ed1ng \u00e1? Thay 1 b\u1eb1ng 0 \u0111i (ho\u1eb7c t\u01b0\u01a1ng t\u1ef1 d\u00f9ng TRUE\/FALSE) v\u00e0 h\u00e0m s\u1ebd ch\u00e8n ph\u00e2n c\u00e1ch v\u00e0o nh\u1eefng v\u00f9ng tr\u1ed1ng n\u1eefa.<\/p>\n

H\u00e3y v\u1eadn d\u1ee5ng ki\u1ebfn th\u1ee9c n\u00e0y m\u1ed9t c\u00e1ch kh\u00f4n ngoan. \u0110\u1ed3ng th\u1eddi hai h\u00e0m tr\u00ean ch\u1ec9 x\u00e0i \u0111\u01b0\u1ee3c khi b\u1ea1n d\u00f9ng Office 365 ho\u1eb7c Office 2019. Trong c\u00e1c phi\u00ean b\u1ea3n kh\u00e1c, ta s\u1ebd v\u1eabn t\u1eadn d\u1ee5ng CONCATENATE ho\u1eb7c s\u1eed d\u1ee5ng c\u00e1c h\u00e0m UDF \u0111\u1ec3 t\u1ea1o ra m\u1ed9t h\u00e0m thay th\u1ebf, ch\u1eb3ng h\u1ea1n:<\/p>\n

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

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
Dim d As Long
Dim c As Long
Dim arr2()
Dim t As Long, y As Long
t = -1
y = -1
If TypeName(arr) = \u201cRange\u201d Then
arr2 = arr.Value
Else
arr2 = arr
End If
On Error Resume Next
t = UBound(arr2, 2)
y = UBound(arr2, 1)
On Error GoTo 0<\/p>\n

If t >= 0 And y >= 0 Then
For c = LBound(arr2, 1) To UBound(arr2, 1)
For d = LBound(arr2, 1) To UBound(arr2, 2)
If arr2(c, d) <> \u201c\u201d Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
End If
Next d
Next c
Else
For c = LBound(arr2) To UBound(arr2)
If arr2(c) <> \u201c\u201d Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c) & delim
End If
Next c
End If
TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) \u2013 Len(delim))
End Function<\/p>\n<\/blockquote>\n

\n

Xem th\u00eam nh\u1eefng b\u00e0i vi\u1ebft t\u01b0\u01a1ng t\u1ef1 sau:<\/p>\n

T\u1ed5ng quan v\u1ec1 h\u00e0m concatenate<\/a><\/p>\n

Gh\u00e9p n\u1ed1i k\u00fd t\u1ef1 v\u1edbi h\u00e0m CONCAT v\u00e0 TEXTJOIN<\/a><\/p>\n

H\u00e0m TEXTJOIN gh\u00e9p chu\u1ed7i th\u1ecfa m\u00e3n \u0111i\u1ec1u ki\u1ec7n<\/a><\/p>\n

\u0110\u1ea3o ng\u01b0\u1ee3c chu\u1ed7i trong Excel<\/a><\/p>\n<\/div>\n

\n

Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n

T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0\ud83d\udcd6<\/span><\/a><\/p>\n

H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n

HKD H\u1ecdc Excel Online
S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n

\n

\u00a9 document.currentScript.insertAdjacentHTML(‘afterend’, ” + new Intl.DateTimeFormat(document.documentElement.lang, {year: ‘numeric’}).format() + ”); H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

\n
\n
\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":7329,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-7328","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/7328","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=7328"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/7328\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/7329"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=7328"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=7328"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=7328"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}