Вам нужно захватить из регулярного выражения. search
для шаблона, если он найден, извлекает строку, используя group(index)
. Предполагая, что действительные проверки выполняются:
>>> p = re.compile("name (.*) is valid")
>>> p.search(s) # The result of this is referenced by variable name '_'
<_sre.SRE_Match object at 0x10555e738>
>>> _.group(1) # group(1) will return the 1st capture.
'my_user_name'
Мой первый подход к этой проблеме был похож на тот, который был отправлен @Jeeped:
Используя MicroTimer Я вычислил среднее время, затраченное каждой частью алгоритма. Часть 3. заняла 90% -93% от общего времени выполнения для больших входных данных.
Ниже я попытался улучшить скорость записи данных на рабочий лист. Я определил константу iMinRSize=17
. После того, как возможно заполнить более iMinRSize
последовательные строки с тем же значением, код прекратит подавать массив и будет записываться непосредственно в диапазон рабочих листов.
Sub CrossJoin(rSrc As Range, rTrg As Range)
Dim vSrc() As Variant, vTrgPart() As Variant
Dim iLengths() As Long
Dim iCCnt As Integer, iRTrgCnt As Long, iRSrcCnt As Long
Dim i As Integer, j As Long, k As Long, l As Long
Dim iStep As Long
Const iMinRSize As Long = 17
Dim iArrLastC As Integer
On Error GoTo CleanUp
Application.ScreenUpdating = False
Application.EnableEvents = False
vSrc = rSrc.Value2
iCCnt = UBound(vSrc, 2)
iRSrcCnt = UBound(vSrc, 1)
iRTrgCnt = 1
iArrLastC = 1
ReDim iLengths(1 To iCCnt)
For i = 1 To iCCnt
j = iRSrcCnt
While (j > 0) And IsEmpty(vSrc(j, i))
j = j - 1
Wend
iLengths(i) = j
iRTrgCnt = iRTrgCnt * iLengths(i)
If (iRTrgCnt < iMinRSize) And (iArrLastC < iCCnt) Then iArrLastC = iArrLastC + 1
Next i
If (iRTrgCnt > 0) And (rTrg.row + iRTrgCnt - 1 <= rTrg.Parent.Rows.Count) Then
ReDim vTrgPart(1 To iRTrgCnt, 1 To iArrLastC)
iStep = 1
For i = 1 To iArrLastC
k = 0
For j = 1 To iRTrgCnt Step iStep
k = k + 1
If k > iLengths(i) Then k = 1
For l = j To j + iStep - 1
vTrgPart(l, i) = vSrc(k, i)
Next l
Next j
iStep = iStep * iLengths(i)
Next i
rTrg.Resize(iRTrgCnt, iArrLastC) = vTrgPart
For i = iArrLastC + 1 To iCCnt
k = 0
For j = 1 To iRTrgCnt Step iStep
k = k + 1
If k > iLengths(i) Then k = 1
rTrg.Resize(iStep).Offset(j - 1, i - 1).Value2 = vSrc(k, i)
Next j
iStep = iStep * iLengths(i)
Next i
End If
CleanUp:
Application.ScreenUpdating = True
Application.EnableEvents = False
End Sub
Sub test()
CrossJoin Range("a2:f10"), Range("k2")
End Sub
Если мы установим iMinRSize
в Rows.Count
, все данные записываются в массив. Ниже приведены мои тестовые результаты:
Код работает лучше всего, если первые столбцы с наибольшим количеством строк на первом месте, но это не будет большая проблема для изменения кода для ранжирования столбцов и обработки в правильном порядке.
Я собираю universal , вы хотите, чтобы это вмещало любое количество столбцов и любое количество записей в каждом. Несколько вариантов массивов должны содержать размеры, необходимые для вычисления циклов повторения для каждого значения.
Option Explicit
Sub main()
Call for_each_in_others(rDATA:=Worksheets("Sheet3").Range("A3"), bHDR:=True)
End Sub
Sub for_each_in_others(rDATA As Range, Optional bHDR As Boolean = False)
Dim v As Long, w As Long
Dim iINCROWS As Long, iMAXROWS As Long, sErrorRng As String
Dim vVALs As Variant, vTMPs As Variant, vCOLs As Variant
On Error GoTo bm_Safe_Exit
appTGGL bTGGL:=False
With rDATA.Parent
With rDATA(1).CurrentRegion
'Debug.Print rDATA(1).Row - .Cells(1).Row
With .Resize(.Rows.Count - (rDATA(1).Row - .Cells(1).Row), .Columns.Count).Offset(2, 0)
sErrorRng = .Address(0, 0)
vTMPs = .Value2
ReDim vCOLs(LBound(vTMPs, 2) To UBound(vTMPs, 2))
iMAXROWS = 1
'On Error GoTo bm_Output_Exceeded
For w = LBound(vTMPs, 2) To UBound(vTMPs, 2)
vCOLs(w) = Application.CountA(.Columns(w))
iMAXROWS = iMAXROWS * vCOLs(w)
Next w
'control excessive or no rows of output
If iMAXROWS > Rows.Count Then
GoTo bm_Output_Exceeded
ElseIf .Columns.Count = 1 Or iMAXROWS = 0 Then
GoTo bm_Nothing_To_Do
End If
On Error GoTo bm_Safe_Exit
ReDim vVALs(LBound(vTMPs, 1) To iMAXROWS, LBound(vTMPs, 2) To UBound(vTMPs, 2))
iINCROWS = 1
For w = LBound(vVALs, 2) To UBound(vVALs, 2)
iINCROWS = iINCROWS * vCOLs(w)
For v = LBound(vVALs, 1) To UBound(vVALs, 1)
vVALs(v, w) = vTMPs((Int(iINCROWS * ((v - 1) / UBound(vVALs, 1))) Mod vCOLs(w)) + 1, w)
Next v
Next w
End With
End With
.Cells(2, UBound(vVALs, 2) + 2).Resize(1, UBound(vVALs, 2) + 2).EntireColumn.Delete
If bHDR Then
rDATA.Cells(1, 1).Offset(-1, 0).Resize(1, UBound(vVALs, 2)).Copy _
Destination:=rDATA.Cells(1, UBound(vVALs, 2) + 2).Offset(-1, 0)
End If
rDATA.Cells(1, UBound(vVALs, 2) + 2).Resize(UBound(vVALs, 1), UBound(vVALs, 2)) = vVALs
End With
GoTo bm_Safe_Exit
bm_Nothing_To_Do:
MsgBox "There is not enough data in " & sErrorRng & " to perform expansion." & Chr(10) & _
"This could be due to a single column of values or one or more blank column(s) of values." & _
Chr(10) & Chr(10) & "There is nothing to expand.", vbInformation, _
"Single or No Column of Raw Data"
GoTo bm_Safe_Exit
bm_Output_Exceeded:
MsgBox "The number of expanded values created from " & sErrorRng & _
" (" & Format(iMAXROWS, "\> #, ##0") & " rows × " & UBound(vTMPs, 2) & _
" columns) exceeds the rows available (" & Format(Rows.Count, "#, ##0") & ") on this worksheet.", vbCritical, _
"Too Many Entries"
bm_Safe_Exit:
appTGGL
End Sub
Sub appTGGL(Optional bTGGL As Boolean = True)
Application.EnableEvents = bTGGL
Application.ScreenUpdating = bTGGL
End Sub
Поместите метки заголовка столбца в строку 2, начиная с столбца A, и данные непосредственно ниже этого.
Я добавил некоторое управление ошибкой, чтобы предупредить о превышении количества строк на листе. Обычно это не то, что может быть рассмотрено, но умножение количества значений в неопределенном количестве столбцов друг на друга может быстро привести к большому количеству результатов. Не предвидится, что вы превысите 1 048 576 строк.
Рекурсивная версия. Предполагается, что данные не содержат внутренних вкладок, так как основная функция возвращает строки продукта , которые разделены табуляцией. Основному подпункту необходимо передать диапазон, состоящий из данных вместе с верхней левой угловой ячейкой выходного диапазона. Вероятно, это может быть немного изменено, но подходит для целей тестирования.
ColumnProducts Range("A:C"), Range("E1")
Является вызовом, который решает проблему OP. Вот код:
'the following function takes a collection of arrays of strings
'and returns a variant array of tab-delimited strings which
'comprise the (tab-delimited) cartesian products of
'the arrays in the collection
Function CartesianProduct(ByVal Arrays As Collection) As Variant
Dim i As Long, j As Long, k As Long, m As Long, n As Long
Dim head As Variant
Dim tail As Variant
Dim product As Variant
If Arrays.Count = 1 Then
CartesianProduct = Arrays.Item(1)
Exit Function
Else
head = Arrays.Item(1)
Arrays.Remove 1
tail = CartesianProduct(Arrays)
m = UBound(head)
n = UBound(tail)
ReDim product(1 To m * n)
k = 1
For i = 1 To m
For j = 1 To n
product(k) = head(i) & vbTab & tail(j)
k = k + 1
Next j
Next i
CartesianProduct = product
End If
End Function
Sub ColumnProducts(data As Range, output As Range)
Dim Arrays As New Collection
Dim strings As Variant, product As Variant
Dim i As Long, j As Long, n As Long, numRows As Long
Dim col As Range, cell As Range
Dim outRange As Range
numRows = Range("A:A").Rows.Count
For Each col In data.Columns
n = col.EntireColumn.Cells(numRows).End(xlUp).Row
i = col.Cells(1).Row
ReDim strings(1 To n - i + 1)
For j = 1 To n - i + 1
strings(j) = col.Cells(i + j - 1)
Next j
Arrays.Add strings
Next col
product = CartesianProduct(Arrays)
n = UBound(product)
Set outRange = Range(output, output.Offset(n - 1))
outRange.Value = Application.WorksheetFunction.Transpose(product)
outRange.TextToColumns Destination:=output, DataType:=xlDelimited, Tab:=True
End Sub
.Transpose
, если добавлено только несколько значений данных. Transpose имеет ограничения, которые не соответствуют современным листам рабочего листа (см. здесь ).
– Jeeped
26 July 2015 в 20:42
Хорошо, так что вам просто нужен список всех возможных комбинаций. Вот что я хотел бы сделать:
Классический пример выражения для выбора без соединения, которое возвращает декартово произведение всех результатов комбинирования перечисленных таблиц.
SQL Database Solution
Просто импортируйте Animals, Fruit, Country в виде отдельных таблиц в любую базу данных SQL, такую как MS Access, SQLite, MySQL и т. д. и таблицы таблиц без объединений, включая неявные (WHERE
) и явные (JOIN
) объединяются:
SELECT Animals.Animal, Fruits.Fruit, Countries.Country
FROM Animals, Countries, Fruits;
Excel Solution
То же понятие с запуском non-join SQL statement в VBA с использованием ODBC-соединения с книгой, содержащей диапазоны «Животные, страны и фрукты». Например, каждая группа данных находится в своем собственном листе с таким же именем.
Sub CrossJoinQuery()
Dim conn As Object
Dim rst As Object
Dim sConn As String, strSQL As String
Set conn = CreateObject("ADODB.Connection")
Set rst = CreateObject("ADODB.Recordset")
sConn = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
& "DBQ=C:\Path To\Excel\Workbook.xlsx;"
conn.Open sConn
strSQL = "SELECT * FROM [Animals$A1:A3], [Fruits$A1:A3], [Countries$A1:A3] "
rst.Open strSQL, conn
Range("A1").CopyFromRecordset rst
rst.Close
conn.Close
Set rst = Nothing
Set conn = Nothing
End Sub
Здесь мой подход к вашей проблеме.
Public Sub matchingCell()
Dim animalRow, fruitRow, countryRow, checkRow, resultRow As Long
Dim isExist As Boolean
'Set the start row
animalRow = 2
resultRow = 2
'Work with data sheet
With Sheets("sheetname")
'Loop until animals column is blank
Do While .Range("A" & animalRow) <> ""
'Set the start row
fruitRow = 2
'Loop until fruits column is blank
Do While .Range("B" & fruitRow) <> ""
'Set the start row
countryRow = 2
'Loop until country column is blank
Do While .Range("C" & countryRow) <> ""
'Set the start row
checkRow = 2
'Reset flag
isExist = False
'Checking for duplicate row
'Loop all result row until D is blank
Do While .Range("D" & checkRow) <> ""
'If duplicate row found
If .Range("D" & checkRow) = .Range("A" & animalRow) And _
.Range("E" & checkRow) = .Range("B" & fruitRow) And _
.Range("F" & checkRow) = .Range("C" & countryRow) Then
'Set true for exist flag
isExist = True
End If
checkRow = checkRow + 1
Loop
'If duplicate row not found
If Not isExist Then
.Range("D" & resultRow) = .Range("A" & animalRow)
.Range("E" & resultRow) = .Range("B" & fruitRow)
.Range("F" & resultRow) = .Range("C" & countryRow)
'Increase resultRow
resultRow = resultRow + 1
End If
'Increase countryRow
countryRow = countryRow + 1
Loop
'Increase fruitRow
fruitRow = fruitRow + 1
Loop
'Increase fruitRow
animalRow = animalRow + 1
Loop
End With
End Sub
Я уже тестировал его. Он работает хорошо. Хороший день.
Option Explicit
, поэтому мне пришлось объявить Dim duplicateCount As Long, countIndex As Long
. Я также получал 2 столбца, но это могли быть мои данные образца; Я расскажу об этом позже.
– Jeeped
28 July 2015 в 13:04
Option Explicit
. Поэтому я забываю объявить. Очень хорошая привычка использовать Option Explicit
. Я добавил некоторую декларацию. И я изменил начальную точку цикла, получив общее количество строк, потому что обнаружил ошибку, когда я тестировал ее снова и снова. Спасибо за ваши предложения.
– R.Katnaan
29 July 2015 в 02:18
Вы можете сделать это с помощью формул рабочего листа. Если у вас есть диапазоны NAME - «Животные», «Фрукты» и «Страны», «трюк» состоит в том, чтобы генерировать индексы в этом массиве для предоставления всех различных комбинаций.
Например:
=CEILING(ROWS($1:1)/(ROWS(Fruits)*ROWS(Countries)),1)
будет генерировать последовательность чисел на основе 1, которая повторяется для записей чисел в Fruits * Countries - что дает вам, сколько строк вам нужно для каждого животного.
=MOD(CEILING(ROWS($1:1)/ROWS(Countries),1)-1,ROWS(Fruits))+1
будет генерировать 1 (f5)
Создает повторяющуюся последовательность 1..n, где n - количество стран.
Вводя их в формулы (с некоторой проверкой ошибок)
D3: =IFERROR(INDEX(Animals,CEILING(ROWS($1:1)/(ROWS(Fruits)*ROWS(Countries)),1)),"")
E3: =IF(E3="","",INDEX(Fruits,MOD(CEILING(ROWS($1:1)/ROWS(Countries),1)-1,ROWS(Fruits))+1))
F3: =IF(E3="","",INDEX(Countries,MOD(ROWS($1:1)-1,ROWS(Countries))+1))
[/g0]