Nối các cell thành chuỗi (1 người xem)

[viehoai]

Xin các anh chị giúp đỡ nối các text trong nhiều cell lại thành một chuỗi như file mẫu đính kèm. Xin cảm ơn

 

[ptlong04x1]

Xem file đính kèm nhé, nhớ Enable Macro.

 

[tam8678]

Xem file đính kèm nhé, nhớ Enable Macro.

---
Trong trường hợp vùng không liên tục thì viết chắc khó hơn phải không bạn ptlong04x1?
( Hỏi thêm thôi )

 

[viehoai]

Xem file đính kèm nhé, nhớ Enable Macro.

Trường hợp công thức làm đựoc không bạn?

 

[ST-Lu!]

Trường hợp công thức làm đựoc không bạn?

[B]Function Join(Rng As Range) As String
    Dim cll As Range, Tmp As String
    For Each cll In Rng
        Tmp = Tmp & cll & " "
    Next cll
    Join = Left(Tmp, Len(Tmp) - 1)
End Function
[/B]

Mình thấy đoạn code trên ngắn mà, nếu làm công thức mình không biết có làm được ko? nhưng chắc mệt lắm
Đoạn code trên có gì bạn không hiểu thì hỏi Anh em nhé

 

[sealand]

Hàm của ptlong04x1 có thể bớt đi 1 biến và 2 phép tính:

Function Join(Rng As Range) As String
    Dim cll As Range
    For Each cll In Rng
        Join = Join & cll & " "
    Next cll
    Join = RTrim(Join)
End Function

 

[yeuthamhangxom]

Trường hợp công thức làm đựoc không bạn?

Được bạn ạ. Nếu bạn không dành về VBA bạn có thể dùng công thức củ chuối sau.
Tại ô C2 bạn gõ công thức sau =B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&" "&B9
Hơi củ chuối đúng không bạn?

 

[rollover79]

Hàm của ptlong04x1 có thể bớt đi 1 biến và 2 phép tính:

Function Join(Rng As Range) As String
    Dim cll As Range
    For Each cll In Rng
        Join = Join & cll & " "
    Next cll
    Join = RTrim(Join)
End Function

Tôi thấy cách dùng biến tạm để tính toán và trả về kết quả cho hàm hay hơn là tính toán trực tiếp vào tên hàm(Ý kiến cá nhân của tôi), còn dùng hàm RTrim để loại bỏ dấu cách thừa ở cuối như bạn trong trường hợp này có vẻ không đúng lắm, vì nó cắt tất cả các dấu cách ở cuối, trong khi đó trong quá trình tính toán ta chỉ thừa có 1 dấu cách ở cuối mà thôi, nếu ô cuối cùng có dấu cách ở cuối thì sao???

 

[ndu96081631]

Nối chuổi dùng vòng lập thì quá bình thường rồi
Xem tuyệt chiêu tại đây:
Có thể dùng hàm Join để nối chuối từ các cell lại không?
Khỏi dùng vòng lập (đương nhiên nó cũng có những nhược điểm)
-----------------------

còn dùng hàm RTrim để loại bỏ dấu cách thừa ở cuối như bạn trong trường hợp này có vẻ không đúng lắm, vì nó cắt tất cả các dấu cách ở cuối, trong khi đó trong quá trình tính toán ta chỉ thừa có 1 dấu cách ở cuối mà thôi, nếu ô cuối cùng có dấu cách ở cuối thì sao???

Xử lý dấu cách theo tôi cũng không cần thiết ---> Của người ta sao cứ để vậy, biết đâu người ta thích thế!

 

[ptlong04x1]

---
Trong trường hợp vùng không liên tục thì viết chắc khó hơn phải không bạn ptlong04x1?
( Hỏi thêm thôi )

Anh thêm cái : If Cll <> "" Then ... là được mà.

 

[ndu96081631]

Anh thêm cái : If Cll <> "" Then ... là được mà.

Có 1 cách khác mà tôi cho là tuyệt chiêu, đó là nối các từ với nhau bằng dấu phân cách ---> Ra được kết quả ta dùng TRIM để loại các dấu cách thừa (khỏi IF)
Ví dụ hàm của bạn có thể sửa như sau:

Function JoinText(Range As Range, Sep As String) As String
  Dim Clls As Range, Tmp As String
  For Each Clls In Range
    Tmp = Tmp & " " & Clls.Value
  Next Clls
  JoinText = Replace(WorksheetFunction.Trim(Tmp), " ", Sep)
End Function

Cú pháp sử dụng:
=JoinText(B2:B15," ")
Trong đó:
- Sep là ký tự phân cách do người dùng tùy chọn
- Vùng B2:B15 chứa dử liệu không liên tục
Lưu ý:
- Bạn không nên đặt tên hàm là Join (trùng với tên hàm của VBA) vì sẽ gây hậu quả nghiêm trọng nếu có lúc nào đó bạn cần dùng đến hàm này (của VBA)
- Đây chỉ là gợi ý về việc có thể thay thế IF chứ chưa hẳn là giải pháp hoàn hảo

 

[jubinell]

Chào các bạn,

Xem ra mình đến với chủ đề này hơi trễ. Mình cũng đã từng gặp phải vấn đề này trong quá khứ và cũng giải quyết bằng một UDF giống như UDF ở trên. Ưu điểm thì như các bạn đã nêu là có thể nối các ô không liền nhau và người sử dụng có thể tùy chọn nhóm ký tự ngăn cách.

Trong trường hợp những cái bạn muốn nối không chỉ đơn thuần là các ô, mà là các thành phần của mảng (array) thì sao? Mình xin giới thiệu một UDF khác khá tương tự có thể giải quyết vấn đề này.

Các bạn có thể thắc mắc tại sao tui lại muốn nối thành phần mảng với nhau, chỉ cần nối các cell trong một range tự chọn là được rồi? Mình xin phép được trả lời nó sẽ hữu dụng trong trường hợp bạn muốn nối một số cell trong một range được chọn (chứ không phải nối hết), và một số này là bao nhiêu thì tùy vào điều kiện được đặt ở một cột/hàng tương ứng khác.

Ví dụ:
Cột A: loại máy ("new" hoặc "second-hand")
Cột B: tên máy

Bạn cần một công thức có thể nối tên các máy mới lại với nhau, được ngăn cách bởi dấu phẩy. Đương nhiên bạn sẽ không coi nguyên cột B rồi đánh dấu lại cell nào là cell máy mới rồi. Giải quyết như sau:

1) Trước hết, đặt tên (name range) cho các cột của bảng: loai_may và ten_may.
2) Viết công thức mảng tạo một mảng (chứ không phải range) bao gồm các cell máy mới:
If(loai_may="new";"ten_may";"") bạn lưu ý công thức mảng này chưa sử dụng được-->xem bước 3
3) Chuyển mảng này qua cho UDF xử lý. UDF của mình được gọi là ConcSep_Array (viết tắt của Concatenate with separators for arrays).
ConcSep_Array(If(loai_may="new";"ten_may";"");", ") rồi nhấn Ctrl-Shift-Enter


Dưới đây là 2 UDF mình đã giới thiệu ở trên, một dùng cho array, một dùng cho range:

Option Explicit

Public Function ConcSep_Array(InArray() As Variant, Optional Sep As String) As String
On Error Resume Next
Dim OutStr As String
Dim i, j As Integer
For i = LBound(InArray, 1) To UBound(InArray, 1)
    For j = LBound(InArray, 2) To UBound(InArray, 2)
        If InArray(i, j) <> "" Then OutStr = OutStr & InArray(i, j) & Sep
    Next j
Next i
ConcSep_Array = Left(OutStr, Len(OutStr) - Len(Sep))
End Function

Public Function ConcSep_Range(InCells As Range, Optional Sep As String) As String
On Error Resume Next
Dim OutStr As String, Cell As Range
For Each Cell In InCells
    If Cell <> "" Then OutStr = OutStr & Cell & Sep
Next
ConSep = Left(OutStr, Len(OutStr) - Len(Sep))
End Function

 

[ndu96081631]

Chào các bạn,

Xem ra mình đến với chủ đề này hơi trễ. Mình cũng đã từng gặp phải vấn đề này trong quá khứ và cũng giải quyết bằng một UDF giống như UDF ở trên. Ưu điểm thì như các bạn đã nêu là có thể nối các ô không liền nhau và người sử dụng có thể tùy chọn nhóm ký tự ngăn cách.

Trong trường hợp những cái bạn muốn nối không chỉ đơn thuần là các ô, mà là các thành phần của mảng (array) thì sao? Mình xin giới thiệu một UDF khác khá tương tự có thể giải quyết vấn đề này.

Các bạn có thể thắc mắc tại sao tui lại muốn nối thành phần mảng với nhau, chỉ cần nối các cell trong một range tự chọn là được rồi? Mình xin phép được trả lời nó sẽ hữu dụng trong trường hợp bạn muốn nối một số cell trong một range được chọn (chứ không phải nối hết), và một số này là bao nhiêu thì tùy vào điều kiện được đặt ở một cột/hàng tương ứng khác.

Ví dụ:
Cột A: loại máy ("new" hoặc "second-hand")
Cột B: tên máy

Bạn cần một công thức có thể nối tên các máy mới lại với nhau, được ngăn cách bởi dấu phẩy. Đương nhiên bạn sẽ không coi nguyên cột B rồi đánh dấu lại cell nào là cell máy mới rồi. Giải quyết như sau:

1) Trước hết, đặt tên (name range) cho các cột của bảng: loai_may và ten_may.
2) Viết công thức mảng tạo một mảng (chứ không phải range) bao gồm các cell máy mới:
If(loai_may="new";"ten_may";"") bạn lưu ý công thức mảng này chưa sử dụng được-->xem bước 3
3) Chuyển mảng này qua cho UDF xử lý. UDF của mình được gọi là ConcSep_Array (viết tắt của Concatenate with separators for arrays).
ConcSep_Array(If(loai_may="new";"ten_may";"");", ") rồi nhấn Ctrl-Shift-Enter


Dưới đây là 2 UDF mình đã giới thiệu ở trên, một dùng cho array, một dùng cho range:

Option Explicit

Public Function ConcSep_Array(InArray() As Variant, Optional Sep As String) As String
On Error Resume Next
Dim OutStr As String
Dim i, j As Integer
For i = LBound(InArray, 1) To UBound(InArray, 1)
    For j = LBound(InArray, 2) To UBound(InArray, 2)
        If InArray(i, j) <> "" Then OutStr = OutStr & InArray(i, j) & Sep
    Next j
Next i
ConcSep_Array = Left(OutStr, Len(OutStr) - Len(Sep))
End Function

Public Function ConcSep_Range(InCells As Range, Optional Sep As String) As String
On Error Resume Next
Dim OutStr As String, Cell As Range
For Each Cell In InCells
    If Cell <> "" Then OutStr = OutStr & Cell & Sep
Next
ConSep = Left(OutStr, Len(OutStr) - Len(Sep))
End Function

Bạn mô tả thế nghe giống nối chuổi có điều kiện ---> Mời bạn tham khảo bài này:
http://www.giaiphapexcel.com/forum/showthread.php?t=30885
Dùng Dictionary Object, ngắn gọn hơn nhiều (Dictionary Object là công cụ xử lý mãng cực mạnh)

 

[johnnylinhanh]

Bạn thử xem file bên dưới xem. Nó có hạn chế là số dòng mình chỉ để 51 dòng thôi! nhiều hơn nữa mình nghĩ bạn nền dùng VBA!

 

[ndu96081631]

Bạn thử xem file bên dưới xem. Nó có hạn chế là số dòng mình chỉ để 51 dòng thôi! nhiều hơn nữa mình nghĩ bạn nền dùng VBA!

Topic này người ta hỏi từ năm 2009 đấy bạn! Giờ bạn mới trả lời?

 

[johnnylinhanh]

Topic này người ta hỏi từ năm 2009 đấy bạn! Giờ bạn mới trả lời?

Hic mở nhiều tab nên reply nhầm
lẽ ra nó phải ở bài này mới đúng Link