James có một số dữ liệu trong một trang tính được chứa trong một chuỗi các hàng. Một trong các cột trong dữ liệu bao gồm các ô có nhiều dòng trên mỗi ô. (Dữ liệu trong ô được tách thành các dòng bằng cách nhấn Alt + Enter giữa các mục.) James muốn chia dữ liệu này thành nhiều hàng. Ví dụ: nếu có ba dòng dữ liệu trong một ô trong hàng, thì dữ liệu trong ô đó phải được chia thành ba hàng.

Excel cung cấp một cách tiện dụng để chia dữ liệu thành các cột riêng biệt bằng công cụ Văn bản thành Cột. Điều này có thể được sử dụng để chia nhỏ dữ liệu dựa trên sự hiện diện của ký tự ASCII 10, là ký tự Excel sẽ chèn khi bạn nhấn Alt + Enter. Vấn đề là trong khi điều này chia thành công dữ liệu thành các cột riêng biệt, nó không đưa nó vào các hàng riêng biệt, như James đã yêu cầu.

Điều đó có nghĩa là giải pháp cho vấn đề này phải bao gồm việc sử dụng macro. Một cách tiếp cận được hiển thị trong đoạn mã sau. Trong ví dụ này, macro giả định rằng bạn muốn “mở rộng” mọi thứ trong trang tính và dữ liệu trong trang tính bắt đầu ở hàng 1.

Sub CellSplitter1()

Dim Temp As Variant     Dim CText As String     Dim J As Integer     Dim K As Integer     Dim L As Integer     Dim iColumn As Integer     Dim lNumCols As Long     Dim lNumRows As Long

iColumn = 4

Set wksSource = ActiveSheet     Set wksNew = Worksheets.Add

iTargetRow = 0     With wksSource         lNumCols = .Range("IV1").End(xlToLeft).Column         lNumRows = .Range("A65536").End(xlUp).Row         For J = 1 To lNumRows             CText = .Cells(J, iColumn).Value             Temp = Split(CText, Chr(10))

For K = 0 To UBound(Temp)

iTargetRow = iTargetRow + 1                 For L = 1 to lNumCols                     If L <> iColumn Then                         wksNew.Cells(iTargetRow, L) _                           = .Cells(J, L)

Else                         wksNew.Cells(iTargetRow, L) _                           = Temp(K)

End If                 Next L             Next K         Next J     End With End Sub

Lưu ý rằng để chạy macro, bạn sẽ cần chỉ định, bằng cách sử dụng biến iColumn, cột chứa các ô được tách ra.

Như đã viết ở đây, macro chia nhỏ thông tin trong cột thứ tư. Ngoài ra, các phiên bản tách rời của các ô được lưu trữ trong một trang tính mới, do đó, trang tính ban đầu hoàn toàn không bị ảnh hưởng.

Macro dựa vào việc sử dụng chức năng Tách để chia nhỏ các ô nhiều dòng. Chức năng này chỉ có sẵn bắt đầu trong Excel 2000 và hoàn toàn không khả dụng trong Excel cho Mac. Ngoài ra, bạn có thể chỉ muốn chạy macro trên một vùng chọn ô cụ thể. Để khắc phục tất cả những vấn đề tiềm ẩn này, bạn sẽ muốn xem xét macro sau, thay vào đó:

Sub CellSplitter2()

Dim iSplitCol As Integer     Dim iEnd As Integer     Dim sTemp As String     Dim iCount As Integer     Dim i As Integer     Dim wksNew As Worksheet     Dim wksSource As Worksheet     Dim lRow As Long     Dim lRowNew As Long     Dim lRows As Long     Dim lRowOffset As Long     Dim iTargetRows As Integer     Dim iCol As Integer     Dim iCols As Integer     Dim iColOffset As Integer     Dim AWF As WorksheetFunction

On Error GoTo ErrRoutine     Application.ScreenUpdating = False

'Set Column to split     iSplitCol = 4

iCols = Selection.Columns.Count     lRows = Selection.Rows.Count

iColOffset = Selection.Column - 1     lRowOffset = Selection.Row - 1     lRowNew = lRowOffset          Set wksSource = ActiveSheet     Set wksNew = Worksheets.Add

Set AWF = Application.WorksheetFunction     With wksSource         For lRow = (lRowOffset + 1) To (lRowOffset + lRows)

sTemp = .Cells(lRow, iSplitCol)

If Right(sTemp, 1) <> vbLf Then                 sTemp = sTemp & vbLf             End If             iCount = (Len(sTemp) - _               Len(AWF.Substitute(sTemp, vbLf, "")))



For iTargetRows = 1 To iCount                 lRowNew = lRowNew + 1                 For i = (iColOffset + 1) To (iColOffset + iCols)

If i <> iSplitCol Then                         wksNew.Cells(lRowNew, i) _                           = .Cells(lRow, i)

Else                         iEnd = InStr(sTemp, vbLf)

wksNew.Cells(lRowNew, i) _                           = Left(sTemp, iEnd - 1)

sTemp = Mid(sTemp, iEnd + 1)

End If                 Next i             Next iTargetRows         Next lRow     End With

ExitRoutine:

Set wksSource = Nothing     Set wksNew = Nothing     Set AWF = Nothing     Application.ScreenUpdating = True     Exit Sub

ErrRoutine:

MsgBox Err.Description, vbExclamation     Resume ExitRoutine End Sub

Macro vẫn dựa vào việc sử dụng một biến để chỉ ra cột được tách ra. Trong trường hợp này, biến là iSplitCol và nó được đặt thành cột 4. Macro chỉ hoạt động trên các ô được chọn khi nó được chạy lần đầu tiên và các ô tách rời được chuyển sang một trang tính mới. Địa chỉ của ô phía trên bên trái trong trang tính mới giống với ô phía trên bên trái được chọn khi chạy macro.

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (3263) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: