Tách dòng

[Tran Mui]

Tôi muốn tách các dòng dữ liệu họ và tên trong một ô, thành hai dòng họ và tên, như file đính kèm
nhờ GPE giúp đỡ
cám ơn trước

 

[nguyanhdien]

mình không rõ câu hỏi của bạn lắm. nhưng theo mình hiểu thì bạn muốn trong một ô có thể viết 2 dòng tên. đúng không?
nếu đúng thì khi viết xong tên thứ nhất thì bạn nhấn tổ hợp (ALT + enter). rồi viết tiếp tên thứ 2 vào.
thế là bạn được 2 tên trong cùng một ô

 

[ndu96081631]

Tôi muốn tách các dòng dữ liệu họ và tên trong một ô, thành hai dòng họ và tên, như file đính kèm
nhờ GPE giúp đỡ
cám ơn trước

Bài này đúng ra phải dùng VBA, nhưng bất chợt nghĩ ra được 1 công thức
- Tại shéet1, đặt con trỏ chuột vào cell A1 rồi vào menu Insert\Name\Define, đặt 1 name tên là Tach, với công thức tại khung Refers to:

=EVALUATE("{""" & SUBSTITUTE(OFFSET($B$7,INT((ROWS($1:1)-1)/2),COLUMNS($A:A)-1),CHAR(10),""";""") & """}")

- Tại sheet2, quét chọn 2 cell A1:A2 rồi gõ vào thanh Formula công thức = Tach
- Gõ xong công thức, bấm tổ hợp phím Ctrl + Shift + Enter
- Quét chọn cùng lúc 2 cell A1:A2 rồi kéo fill công thức xuống dưới và sang phải
Xem file

 

[khanhpur]

Chào anh Tuấn !
Ạnh có thể giải thích rõ hơn một chút về công thức này được không ạ, vì sao khi gõ công thức xong rồi nhấn tổ hợp phím ctrl+shift+enter lại báo lỗi , còn khi gõ = tách rồi nhấn ctrl+shift+enter lại Ok.

 

[Ba Tê]

Tôi muốn tách các dòng dữ liệu họ và tên trong một ô, thành hai dòng họ và tên, như file đính kèm
nhờ GPE giúp đỡ
cám ơn trước

Thử làm "thí thí" bằng công thức, đưa vào name, dài thòn thấy ghê!
Xem cho vui nhé!

 

[Tran Mui]

Rất cảm ơn các đồng nghiệp
Tôi cần kết quả như anh ndu tách ra mỗi họ và tên của một người vào trong một ô.

 

[haonlh]

Tôi muốn tách các dòng dữ liệu họ và tên trong một ô, thành hai dòng họ và tên, như file đính kèm
nhờ GPE giúp đỡ
cám ơn trước

Các trả lời là đã thỏa đáng, nhưng tôi muốn hỏi Tran Mui là:
Dữ liệu như file bạn gửi là có "xuất xứ" từ đâu (từ thao tác nào)?

 

[TrungChinhs]

Cảm ơn Ndu và Ba tê. Rất vui vì đã học được từ các bạn nhiều điều bổ ích và còn vui hơn nếu được các bạn giải thích ý nghĩa của các công thức trong Name

1. Công thức trong Name Tach (của Ndu)
=EVALUATE("{""" & SUBSTITUTE(OFFSET(Sheet1!$B$7;INT((ROWS(Sheet1!$1:1)-1)/2);COLUMNS(Sheet1!$A:A)-1);CHAR(10);""";""") & """}")

2. Công thức trong Name Dòng1 và Dòng2 của Ba Tê
=LEFT(OFFSET(Sheet1!$A$7;Sheet1!$A11-1;COLUMNS(Sheet1!$B:C));FIND(CHAR(10);OFFSET(Sheet1!$A$7;Sheet1!$A11-1;COLUMNS(Sheet1!$B:C)))-1)
=MID(OFFSET(Sheet1!$A$7;Sheet1!$A11-1;COLUMNS(Sheet1!$B:C));FIND(CHAR(10);OFFSET(Sheet1!$A$7;Sheet1!$A11-1;COLUMNS(Sheet1!$B:C)))+1;100)

Sáng nay tôi đã xem bài này và cũng đã nghĩ ra cách làm đơn giản hơn nhưng thấy thủ công quá và không còn là Giải pháp Excel nữa. "Lực bất tòng tâm" nên không Post.
đó là: Copy dữ liệu sang Word chọn Menu Data - Convert - Table to tex sau đó copy dữ liệu trở về Excel (nếu muốn các tên ở gần nhau như dữ liệu gốc thì phải chuyển dữ liệu về cùng một cột trước khi chuyển sang Word).

 

[Ba Tê]

Cảm ơn Ndu và Ba tê. Rất vui vì đã học được từ các bạn nhiều điều bổ ích và còn vui hơn nếu được các bạn giải thích ý nghĩa của các công thức trong Name

------
Chắc không cần phải giải thích cách dùng các hàm LEFT, MID, FIND, OFFSET, COLUMNS.
Ý của mình khi dùng công thức trên vào trong name là:

Dong1:
- Columns($B:B)= 1, dùng công thức này để khi kéo copy sang phải sẽ có lần lượt là columns($B:C)=2; Columns($B)=3… làm đối số cols cho hàm Offset().
=OFFSET($A$7;$A17-1;COLUMNS($B:B)) = Offset($A$17;0;1) = giá trị trong ô B7
- Char(10) = ký tự Alt+Enter (xuống hàng trong cùng một ô).
=FIND(CHAR(10);OFFSET(Sheet1!$A$7;Sheet1!$A17-1;COLUMNS(Sheet1!$B:B)))-1 = tìm vị trí ký tự xuống hàng nằm trong chuỗi ở ô B7.
=LEFT(OFFSET(Sheet1!$A$7;Sheet1!$A17-1;COLUMNS(Sheet1!$B:B));FIND(CHAR(10);OFFSET(Sheet1!$A$7;Sheet1!$A17-1;COLUMNS(Sheet1!$B:B)))-1) = Left(B7;kết quả của hàm Find() -1) . Không lấy ký tự xuống hàng.
Tương tự: Dong2:
=MID(OFFSET(Sheet1!$A$7;Sheet1!$A18-1;COLUMNS(Sheet1!$B:B));FIND(CHAR(10);OFFSET(Sheet1!$A$7;Sheet1!$A18-1;COLUMNS(Sheet1!$B:B)))+1;100) = Mid(B7; vị trí của ký tự xuống hàng +1, 100). Số 100 này là tôi tự ý đặt ra vì nghĩ rằng không ai có tên dài hơn 100 ký tự.
3. Công thức mảng của ndu… rất tuyệt, tôi tự ngẫm nghĩ có thể tự hiểu, giải thích không nổi.
Chúc Bạn vui.

 

[ndu96081631]

Chào anh Tuấn !
Ạnh có thể giải thích rõ hơn một chút về công thức này được không ạ, vì sao khi gõ công thức xong rồi nhấn tổ hợp phím ctrl+shift+enter lại báo lỗi , còn khi gõ = tách rồi nhấn ctrl+shift+enter lại Ok.

Mình xin giải thích ngắn gọn công thức trong file như sau:
- Name Tach làm nhiệm vụ biến chuổi thành MẢNG (giống hàm Split trong VBA)
- Công thức này chỉ có thể đặt trong name, không thể gõ trực tiếp vào bảng tính, vì có hàm EVALUATE là hàm macro 4
-------------
Từ công thức này, ta có thể cải tiến để tách họ tên (khác với tất cả những gì trước đây đã làm trên GPE)
Chỉ với 1 name:

Arr =EVALUATE("{"""&SUBSTITUTE(TRIM($A2)," ",""",""")&"""}")

Ta sẽ có công thức tách HỌ TÊN vô cùng gọn:
Tách TÊN

=INDEX(Arr,,COUNTA(Arr))

và tách HỌ

=IF(COUNTA(Arr)=1,"",INDEX(Arr,,1))

Xem file này (Name Arr tương đương với Spilit(Text, " ") trong VBA)

 

[Tran Mui]

Một lần nữa vẫn phải nói lời cám ơn các Bạn, đã chỉ giáo.
Trong quá trình làm còn vấn đề nữa cần mọi người giúp:
Trong bảng trước đây từ một ô chỉ có 2 tên, nếu bây giờ có nhiều tên hơn, hay xen lần ít tên hơn, thì làm thế nào?
Mong mọi người giúp ( bảng đính kèm)

 

[ndu96081631]

Một lần nữa vẫn phải nói lời cám ơn các Bạn, đã chỉ giáo.
Trong quá trình làm còn vấn đề nữa cần mọi người giúp:
Trong bảng trước đây từ một ô chỉ có 2 tên, nếu bây giờ có nhiều tên hơn, hay xen lần ít tên hơn, thì làm thế nào?
Mong mọi người giúp ( bảng đính kèm)

Nếu lộn xộn thế, tôi nghĩ nên dùng code VBA sẽ tốt hơn (công thức chắc là thua)
Nếu bạn đồng ý giải pháp này, chúng ta sẽ tiếp tục nhé!

 

[Tran Mui]

Để đơn giản Bạn có thể giúp trường hợp nhiều tên trong một ô thôi,
Tôi có thể copy những ô đó xử lí xong đưa về
cám ơn nhé

 

[ndu96081631]

Để đơn giản Bạn có thể giúp trường hợp nhiều tên trong một ô thôi,
Tôi có thể copy những ô đó xử lí xong đưa về
cám ơn nhé

Nếu tất cả các cell đều có số lượng TÊN giống nhau thì vẫn dùng công thức cũ (chỉ sửa lại đôi chút)
Vấn đề ở đây là: Cell thì 2 tên, cell thì 5, 6 tên, công thức e rằng không kham nỗi...
Bởi vậy mới đề xuất với bạn giải pháp VBA ---> Nếu bạn vẫn quyết dùng công thức cho file "lộn xộn" trên thì tôi.. làm không nỗi bạn à!

 

[Tran Mui]

Cám ơn bạn
Bạn cho mình 2 phương án:
1) sửa đôi chút cho số tên trong mỗi ô đều là 6 người chẳng hạn.
2) Dùng VBA cho trường hợp chung
Mình sẽ lựa chọn cách sử dụng sau

 

[ndu96081631]

Cám ơn bạn
Bạn cho mình 2 phương án:
1) sửa đôi chút cho số tên trong mỗi ô đều là 6 người chẳng hạn.
2) Dùng VBA cho trường hợp chung
Mình sẽ lựa chọn cách sử dụng sau

Đây gữi bạn 2 phương án: Dùng công thức và VBA
Với công thức, tôi chỉ sửa lại cái cũ 1 chút thôi
Riêng về giải pháp VBA thì cách dùng đơn giản hơn:
- Bấm nút Run code
- Một UserForm hiện ra



- Quét chọn dữ liệu nguồn tại khung Source Range
- Chọn 1 cell để đặt dữ liệu lọc tại khung Target Range
- Bấm nút OK để lọc dữ liệu
- Code làm việc như sau:

Private Sub SplitCell(SrcRng As Range, TargetCell As Range, Sep As String)
  Dim i As Long, j As Long, k  As Long, Arr
  With WorksheetFunction
    For j = 1 To SrcRng.Columns.Count
      k = 1
      For i = 1 To SrcRng.Rows.Count
        Arr = Split(.Trim(SrcRng(i, j)), Sep)
        TargetCell(k, j).Resize(UBound(Arr) + 1).Value = .Transpose(Arr)
        k = k + UBound(Arr) + 1
      Next i
    Next j
  End With
End Sub

Private Sub Com_OK_Click()
  On Error GoTo ExitSub
  SplitCell Range(SrcRef), Range(TarRef), Chr(10)
ExitSub:
End Sub

Private Sub Com_Exit_Click()
  Unload Me
End Sub

 

[Tran Mui]

Mình đang làm thử xin cám ơn
Bạn ndu ơi Mình cám ơn nhé. Trong 2 cách mình đã thử dùng được rồi
nếu chỉ dùng đơn giản
mình vẫn muốn dùng công thức, tuy thủ công , hiệu quả vẫn tốt