#excel #vba #concatenation
#excel #vba #объединение
Вопрос:
У меня есть 2 столбца в Excel, как показано ниже:
CutNo Data
1 A
1 B
1 C
2 A
2 B
3 A
Я хочу объединить данные столбца data, если номер разреза совпадает, и поместить его в другой столбец с именем Concatenate и подсчитать количество вхождений и поместить его в другой столбец, как показано ниже
CutNo Data Concatenate Occurrences
1 A A amp; B amp; C 1
1 B
1 C
2 A A amp; B 1
2 B
3 A A 1
Я использую следующий код
Sub Unique()
Dim Rng, Cel As Range
Dim lr As Long
Dim x As Integer
Dim str As String
lr = Sheets("Report").Cells(Rows.count, 1).End(xlUp).Row
Set Rng = Sheets("Report").Range("A2:A" amp; lr)
For x = 1 To Rng.count
For Each Cel In Rng.Cells
If Cel.Value = x Then
str = str amp; Rng.Cells(x, 1).Offset(0, 7) amp; ","
End If
Next Cel
Rng.Cells(x, 1).Offset(0, 10).Value = str
Next x
End Sub
Я не получил нужного мне результата,
Ценю вашу поддержку
Спасибо, с уважением
Moheb Labib
Комментарии:
1. Какую версию вы используете?
2. Что должны означать «события», если все они таковы
1
? Разве это не должно быть 2, 2, 1 для вашего примера? Не могли бы вы лучше определить этот аспект, пожалуйста? Если, скажем,1
появляется снова после3
, следует ли считать его разделенным, а не внутри первого1
появления?
Ответ №1:
Если у вас есть Excel O365 с FILTER
функцией, вам не нужен VBA:
(Примечание: Я предположил, что Occurrences
это можно вычислить, просто подсчитав количество строк CutNo
. Если вы имеете в виду что-то другое, пожалуйста, уточните)
C2: =IF(AND(A2<>A1,A2<>""),TEXTJOIN(" amp; ",TRUE,FILTER($B:$B,$A:$A=A2)),"")
D2: =IF(AND(A2<>A1,A2<>""),COUNTIF($A:$A,A2),"")
и заполните вниз.
Вы также можете сделать это с помощью Power Query
, доступной в Excel 2010
- Выберите весь диапазон для включения
- * не удается выполнить автоматический выбор, поскольку имеются пустые строки
- В Excel 2016 :
Data --> Get amp; Transform --> From Table/Range
- Я не уверен насчет более ранних версий, где вы загружали бесплатную надстройку MS для этой функциональности.
- Когда откроется редактор PQ, выберите
Home --> Advanced Editor
и вставьтеM Code
приведенное ниже в открывшееся окно.- Измените имя таблицы в строке 2 на имя таблицы, сгенерированной при открытии PQ.
Для получения пояснений изучите элементы в окне Примененные шаги. Если вы наведете курсор на любой из i
значков, вы увидите соответствующий комментарий; если вы дважды щелкните на шестеренке, откроется диалоговое окно, в котором вы сможете просмотреть, что было сделано.
- Закрыть и загрузить в: я выбираю столбец рядом с исходными данными, но есть другие способы сделать это.
M Код
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CutNo", Int64.Type}, {"Data", type text}}),
//make the grouping easier, else we'd have a group with the blank rows
#"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
//Group by CutNo -- hence no need to sort
#"Grouped Rows" = Table.Group(#"Removed Blank Rows", {"CutNo"}, {{"Grouped", each _, type table [CutNo=nullable number, Data=nullable text]}}),
//add a blank row at the bottom of each grouped table (each CutNo group)
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "addBlankRow", each Table.InsertRows([Grouped],
Table.RowCount([Grouped]),
{[CutNo=null, Data=null]})),
//remove unneded columns
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"CutNo", "Grouped"}),
#"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each Table.Column([addBlankRow],"Data")),
//Concatenate the "Data"
#"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), " amp; "), type text}),
//Count the rows (subtract one since last row will be blank
#"Added Custom2" = Table.AddColumn(#"Extracted Values", "Custom.1", each Table.RowCount([addBlankRow])-1),
//Expand the Table column to put a blank row between each group of CutNo
#"Expanded addBlankRow" = Table.ExpandTableColumn(#"Added Custom2", "addBlankRow", {"CutNo"}, {"addBlankRow.CutNo"}),
//Add Index column so we can null out where there should be empty cells in the Concatenate Column
#"Added Index" = Table.AddIndexColumn(#"Expanded addBlankRow", "Index", 0, 1, Int64.Type),
#"Added Custom3" = Table.AddColumn(#"Added Index", "Concatenate", each
if [Index] = 0
then [Custom]
else if [addBlankRow.CutNo] = null
then null
else if [addBlankRow.CutNo] = #"Expanded addBlankRow"[addBlankRow.CutNo]{[Index]-1}
then null
else [Custom]),
//Blank cells in the Occurrence column if blank in the CutNo column
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Occurrences", each
if [Concatenate] = null then null
else [Custom.1]),
//Remove unneeded columns
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom4",{"addBlankRow.CutNo", "Custom", "Custom.1", "Index"}),
//Remove bottom row which will be blank
#"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Columns1",1)
in
#"Removed Bottom Rows"
Ответ №2:
Сначала ваши данные в форме VBA:
Cells.Clear
Cells(2, 1) = "1"
Cells(2, 2) = "A"
Cells(3, 1) = "1"
Cells(3, 2) = "B"
Cells(4, 1) = "1"
Cells(4, 2) = "C"
Cells(6, 1) = "2"
Cells(6, 2) = "A"
Cells(7, 1) = "2"
Cells(7, 2) = "B"
Cells(9, 1) = "3"
Cells(9, 2) = "A"
Во-вторых, ваш код переработан:
Dim rng As Range, Cel As Range
Dim lr As Long
Dim x As Integer, y As Integer
Dim str As String
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:A" amp; lr)
For x = 1 To rng.Count
str = ""
y = 0
For Each Cel In rng
If Cel.Value = x Then
str = str amp; rng.Cells(Cel.Row - 1, 2) amp; ","
If y = 0 Then y = Cel.Row - 1
End If
Next Cel
If y>0 Then rng.Cells(y, 4) = Left(str, Len(str) - 1)
Next x
Вывод:
Примечания:
Я не включил «вхождения», поскольку это кажется расплывчатым.
Dim rng, Cel As Range
должно быть Dim rng As Range, Cel As Range
, в противном случае rng
a объявлено как вариант.
Кроме этого, я просто обрезал биты и добавил процедуру для правильного вычисления и форматирования данных.
Ранее вы использовали Rng.Cells(x, 1)
, но значение x
не меняется на протяжении Cel
цикла, поэтому вам нужно получить доступ к Cel.Row
свойству, чтобы узнать, где находится рассматриваемая строка.
y
Переменная сохраняет первое вхождение x
для целей отображения.
Комментарии:
1. @ JMP, спасибо за решение моего кода, вхождение должно, должно для каждой группы конкатенации, Для каждого номера разреза. Спасибо
2. @ JMP, Можем ли мы удалить 1: , 2:, из объединения? Спасибо
Ответ №3:
Попробуйте следующий код, пожалуйста. Поскольку вы не ответили на мой уточняющий вопрос, код работает с предположением, что вхождения означают подсчет каждого объединенного элемента:
Sub testConcatenateOnCriteria()
Dim sh As Worksheet, lastRow As Long, dict As New Scripting.Dictionary
Dim i As Long, count As Long, strVal As String, arr As Variant
Set sh = ActiveSheet 'use here your sheet
lastRow = sh.Range("A" amp; Rows.count).End(xlUp).Row
For i = 2 To lastRow
strVal = sh.Range("A" amp; i).Value
If sh.Range("B" amp; i).Value <> "" Then
If Not dict.Exists(strVal) Then
dict.Add strVal, Array(sh.Range("B" amp; i).Value, 1, i)
Else
dict(strVal) = Array(dict(strVal)(0) amp; sh.Range("B" amp; i).Value, dict(strVal)(1) 1, dict(strVal)(2))
End If
End If
Next i
ReDim arr(1 To lastRow, 1 To 2)
arr(1, 1) = "Concatenate": arr(1, 2) = "Occurrences"
For i = 0 To dict.count - 1
arr(dict(dict.Keys(i))(2), 1) = dict(dict.Keys(i))(0): arr(dict(dict.Keys(i))(2), 2) = dict(dict.Keys(i))(1)
Next i
sh.Range("C1").Resize(UBound(arr), 2).Value = arr
End Sub
Комментарии:
1. @ FaneDuru, большое спасибо, и мне жаль, что я не ответил на ваш комментарий, поскольку я был занят работой в other shores, вхождение должно быть для каждой группы конкатенации, Для каждого разреза no. Спасибо
2. @Meho2016: Боюсь, я не понимаю вашего объяснения о вхождениях, ни сейчас. Я вижу, вам понравился ответ, вообще не возвращающий вхождения, что по меньшей мере странно. Возможно ли, что «группа» из 1 также повторяется после того, что мы видим в вашем вопросе? Если да, вам следует отредактировать его и разместить соответствующий пример, пожалуйста…
3. @ FaneDuru, Вы правы, извините, вхождения указаны для всех записей с одинаковыми критериями, спасибо
4. @Meho2016: И «по тем же критериям» разве это не было бы ‘CutNo’? Если да, приведенный выше код возвращает правильный результат. Если нет, то я все еще не понимаю этой концепции. Но, если вы довольны полученным ответом, я тоже могу жить, не понимая этого … 🙂
5. @ FaneDuru, я хочу подсчитать дубликаты не только для каждого вырезанного файла, но и для всего файла, если вы меня поняли, количество должно быть 10, 7 и так далее