У меня есть несколько сотен ячеек в Excel, которые я хотел бы объединить в одну строку. Есть ли более простой способ сделать это, чем проходить через них один за другим вручную, чтобы ввести их в CONCATENATE(A1, A2, ....)?
CONCATENATE(A1:A255) не работает.
* На новой вкладке введите A1 в ячейке A1,
* Тип A2 в ячейке A2
* Используйте серию заполнения, чтобы заполнить значения в столбце A
* Тип A1 в ячейке B1
Используйте этот форум в ячейке B2
= В1 & "" & А2
Скопируйте формулу вниз.
Скопируйте и вставьте значения, чтобы собрать строку созданных вами значений.
A1 A1
A2 A1,A2
A3 A1,A2,A3
A4 A1,A2,A3,A4
A5 A1,A2,A3,A4,A5
A6 A1,A2,A3,A4,A5,A6
A7 A1,A2,A3,A4,A5,A6,A7
A8 A1,A2,A3,A4,A5,A6,A7,A8
A9 A1,A2,A3,A4,A5,A6,A7,A8,A9
A10 A1,A2,A3,A4,A5,A6,A7,A8,A9,A10
Нажмите Alt-F11, вставьте новый модуль, вставьте код ниже.
Public Function concatRange(data As Range, Optional sep As String = "") As String
Dim ret As String
Dim sep2 As String
ret = ""
sep2 = ""
For Each cell In data
ret = ret & sep2 & cell.Value
sep2 = sep
Next cell
concatRange = ret
End Function
Использование:
=concatRange(A8:D11;", ") 'OS with ; list separator
=concatRange(A8:D11,", ") 'OS with , list separator or in a macro code
или
=concatRange(A8:D11)
Смотрите этот пост в блоге: http://www.dullsharpness.com/2011/11/14/excel-vba-range-to-csv-range2csv-function/
Вы можете использовать его так, например, с разделителем труб:
=Range2Csv(A1:A255,"|")
Получите доступ к редактору VBA с помощью Alt+F11 и переместите его в модуль.
Выдержка кода здесь:
Option Explicit
'**********************************************
'* PURPOSE: Concatenates range contents into a
'* delimited text string
'*
'* FUNCTION SIGNATURE: Range2Csv(Range, String)
'*
'* PARAMETERS:
'* Range - the range of cells whose contents
'* will be included in the CSV result
'* String - delimiter used to separate values
'* (Optional, defaults to a comma)
'*
'* AUTHOR: www.dullsharpness.com
'*
'* NOTES: [add'l notes removed for brevity]
'*
'**********************************************
Public Function Range2Csv(inputRange As Range, Optional delimiter As String)
Dim concattedList As String 'holder for the concatted CSVs
Dim rangeCell As Range 'holder cell used in For-Each loop
Dim rangeText As String 'holder for rangeCell text
'default to a comma delimiter if none is provided
If delimiter = "" Then delimiter = ","
concattedList = "" 'start with an empty string
'Loop through each cell in the range to append valid contents
For Each rangeCell In inputRange.Cells
rangeText = rangeCell.Value 'capture the working value
'Only operate on non-blank cells (i.e. Length > 0)
If Len(rangeText) > 0 Then
'Strip any delimiters contained w/in the value itself
rangeText = WorksheetFunction.Substitute(rangeText, delimiter, "")
If (Len(concattedList) > 0) Then
'prepend a delimiter to the new value if we
'already have some list items
concattedList = concattedList + delimiter + rangeText
Else
'else if the list is blank so far,
'just set the first value
concattedList = rangeText
End If
End If
Next rangeCell
'Set the return value
Range2Csv = concattedList
End Function
конкатенат (a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, a13, a14, a15, a16, a17, a18, a19, a20, a21, a22, a23, a24, a25, a26, a27, a28, a29, a30, a31, a32, a33, a34, a35, a36, a37, a38, a39, a40, a41, a42, a43, a44, a45, a46, a47, a48, a49, a50, a51, a52, a53, a54, a55, a56, a57, a58, a59, a60, a61, a62, a63, a64, a65, a66, a67, a68, a69, a70, a71, a72, a73, a74, a75, a76, a77, a78, a79, a80, a81, a82, a83, a84, a85, a86, a87, a88, a89, a90, a91, a92, a93, a94, a95, a96, a97, a98, a99, a100, a101, a102, a103, a104, a105, a106, a107, a108, a109, a110, a111, a112, a113, a114, a115, a116, a117, a118, a119, a120, a121, a122, a123, a124, a125, a126, a127, a128, a129, a130, a131, a132, a133, a134, a135, a136, a137, a138, a139, a140, a141, a142, a143, a144, a145, a146, a147, a148, a149, a150, a151, a152, a153, a154, a155, a156, a157, a158, a159, a160, a161, a162, a163, a164, a165, a166, a167, a168, a169, a170, a171, a172, a173, a174, a175, a176, a177, a178, a179, a180, a181, a182, a 183, a184, a185, a186, a187, a188, a189, a190, a191, a192, a193, a194, a195, a196, a197, a198, a199, a200, a201, a202, a203, a204, a205, a206, a207, a208, a209, a210, a211, a212, a213, a214, a215, a216, a217, a218, a219, a220, a221, a222, a223, a224, a225, a226, a227, a228, a229, a230, a231, a232, a233, a234, a235, a236, a237, a238, a239, a240, a241, a242, a243, a244, a245, a246, a247, a248, a249, a250, a251, a252, a253, a254, a255)
PowerShell it!
"concatenate(a$((1..255) -join ', a'))" | clip
Откройте копию текстового файла и вставьте
Эта функция VBA будет конкатенировать содержимое ячеек с необязательным разделителем, если это необходимо. Скопируйте его в стандартный модуль:
Option Explicit
Function Concat(CellRange As Range, Optional Delimiter As String) As String
' this function will concatenate a range of cells and return the result as a single string
' useful when you have a large range of cells that you need to concatenate
' source: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
Dim retVal As String, dlm As String, cell As Range
retVal = ""
If Delimiter = Null Then
dlm = ""
Else
dlm = Delimiter
End If
For Each cell In CellRange
If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
retVal = retVal & CStr(cell.Value) & dlm
End If
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
Concat = retVal
End Function
Если вы ищете чистый подход к Excel (т.е. нет VBA), то метод, предложенный Джеймсом Дженкинсом, лучше всего. Если вы счастливы с помощью VBA, тогда откройте редактор VBA, добавьте новый модуль и добавьте этот код:
Option Explicit
Public Function JoinText(cells As Variant,Optional delim_str As String) As String
If cells.Columns.count < cells.Rows.count Then
JoinText = Join(WorksheetFunction.Transpose(cells), delim_str)
Else
JoinText = Join(WorksheetFunction.Transpose(WorksheetFunction.Transpose(cells)), delim_str)
End If
End Function
Чтобы открыть редактор VBA, просто нажмите Alt-F11. Чтобы вставить модуль, щелкните правой кнопкой мыши на книге, указанной в окне "Проект".
Функция вызывается из excel следующим образом:
=JoinText(A1:C1)
Если вы хотите добавить разделитель (например, запятую):
=JoinText(A1:C1,",")
Цель использования функции транспонирования состоит в том, чтобы превратить 2d-массив, 'cells', в 1d-массив. Резонансом для этого является то, что функция VBA Join принимает только 1d-массив. Причиной использования двух из них является то, что если JoinText смотрит на строку ячеек (которая все еще является только 2-мерным массивом), то первый вызов транспонировать переносит этот массив 2-й строки в массив 2d-столбцов, второй вызов превращает его в 1-й массив.
Мой предпочтительный метод - вырезать и вставлять значения в редактор, который позволяет регулярные выражения, а затем просто удалять вкладки (или пробелы) с помощью find и replace в моем текущем выборе.
Вы также можете использовать это для вставки запятых, пробелов или чего хотите.
Это тон быстрее, чем набирать =concatenate(A1,",","A2",",",......)
Это не чисто Excel, но есть простой способ сделать это с помощью Word.
Если у вас есть Excel 2016, вы можете использовать формулу массива:
Введите
= CONCAT (a1: A255)
в ячейку, затем нажмите
[Ctrl] + [Shift] + [Enter]
где значения, которые вы хотели бы конкретизировать, начинаются в столбце 3 строки 2 вашего листа.
Sub GOWN()
roww = 2
Do While cells(roww, 2) <> ""
aa = cells(roww, 3)
dd = dd & aa & ","
roww = roww + 1
Loop
cells(roww + 1, 3) = dd
End Sub
Просто добавьте разделитель в одну конкатенацию:
=concatenate(A1, ",")
Затем скопируйте все конкатенации, вставьте их как значения. Затем скопируйте эти значения, вставьте их в транспозицию. Затем скопируйте значения Transposed и вставьте их в текстовый редактор. Найдите find для разделителя и пробел, предшествующий значениям, и замените для JUST разделителя. Это должно дать вам конкатенированную строку всех значений с разделителем. Это намного проще, чем другие варианты.
Бесстыдно скопировано из этот сайт:
- Выберите ячейку, в которой вам нужен результат.
- Перейдите в панель формул и введите... "= A1: A5"
- Выберите всю формулу и нажмите F9 (это преобразует формулу в значения).
- Удалите фигурные скобки с обоих концов.
- Добавить = CONCATENATE (в начало текста и завершить его с помощью круглой скобки).
- Нажмите Enter.
Что особенно очевидно, так это то, что при редактировании формулы нажатие F9 заменяет формулу результатом этой формулы. Где этот диапазон, он заменяет его списком содержимого этого диапазона.