У Джеймса есть данные на листе, который состоит из ряда строк. Один из столбцов данных включает ячейки, в каждой из которых есть несколько строк. (Данные в ячейке были разделены на строки нажатием Alt + Enter между элементами.) Джеймс хотел бы разделить эти данные на несколько строк. Например, если в одной ячейке в строке было три строки данных, то данные в этой ячейке должны быть разделены на три строки.

Excel предоставляет удобный способ разбить данные на отдельные столбцы с помощью инструмента «Текст в столбцы». Это можно использовать для разделения данных на основе наличия символа ASCII 10, который Excel вставляет при нажатии Alt + Enter. Проблема в том, что при успешном разбиении данных на отдельные столбцы они не попадают в отдельные строки, как просил Джеймс.

Это означает, что решение этой проблемы должно включать использование макроса. Один из подходов показан в следующем коде. В этом примере макрос предполагает, что вы хотите «развернуть» все на листе, и что данные на листе начинаются в строке 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

Обратите внимание, что для запуска макроса вам нужно будет указать с помощью переменной iColumn столбец, содержащий ячейки, которые нужно разделить.

Как здесь написано, макрос разделяет информацию в четвертом столбце. Кроме того, разделенные версии ячеек хранятся на новом листе, так что исходный лист не изменяется.

Макрос основан на использовании функции Split для разрыва многострочных ячеек. Эта функция доступна только начиная с Excel 2000 и вообще недоступна в Excel для Mac. Кроме того, вы можете захотеть запустить макрос только для определенного набора ячеек. Чтобы преодолеть все эти потенциальные проблемы, вы захотите вместо этого рассмотреть следующий макрос:

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

Макрос по-прежнему полагается на использование переменной, чтобы указать столбец, который нужно разделить. В этом случае переменной является iSplitCol, и она установлена ​​в столбец 4. Макрос работает только с ячейками, выбранными при первом запуске, а разделенные ячейки переносятся на новый рабочий лист. Адрес левой верхней ячейки на новом листе совпадает с адресом левой верхней ячейки, выбранной при запуске макроса.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (3263) относится к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Splitting_Information_into_Rows [Разделение информации на строки].