DJMC
Platinum Member | Редактировать | Профиль | Сообщение | Цитировать | Сообщить модератору
Код:
Sub aaa()
lLastRow1 = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For k = 1 To lLastRow1 Step 2
lLastRow2 = Sheets("Лист2").Cells(Rows.Count, 1).End(xlUp).Row
For n = 1 To 15
Sheets("Лист2").Cells(lLastRow2 + 1, n) = n
marks = ActiveSheet.Range(Cells(k, 1), Cells(k, 15)).Find(Sheets("Лист2").Cells(lLastRow2 + 1, n), LookAt:=xlWhole).Offset(1, 0).Value
Sheets("Лист2").Cells(lLastRow2 + 2, n) = marks
Sheets("Лист2").Cells(lLastRow2 + 3, n) = ActiveSheet.Cells(k + 1, n)
Sheets("Лист2").Cells(lLastRow2 + 4, n) = ActiveSheet.Cells(k, n)
Sheets("Лист2").Cells(lLastRow2 + 5, n) = "хз"
Sheets("Лист2").Cells(lLastRow2 + 6, n) = n
Sheets("Лист2").Cells(lLastRow2 + 7, n) = "хз2"
Next n
Next k
End Sub
|
чтобы вытащить данные в скобках:
Код:| Mid(Range("A6"), InStr(Range("A6"), "(") + 1, Len(Range("A6")) - InStr(Range("A6"), "(") - 1) |
Код:
marks = ActiveSheet.Range(Cells(k, 1), Cells(k, 15)).Find(Sheets("Лист2").Cells(lLastRow2 + 1, n), LookAt:=xlWhole).Offset(1, 0).Value
|
Код: let
data = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
names = List.Buffer ( Table.ColumnNames ( data ) ),
sortNames = List.Buffer ( List.Sort ( names, ( x ) => Number.From ( x ) ) ),
transform = Table.FromRecords (
Table.TransformRows (
data,
( r ) =>
[
vals = List.Transform ( Record.FieldValues ( r ), ( x ) => Text.Split ( Text.BetweenDelimiters ( x, "(", ")" ), "," ) ),
sortVals = List.Transform (
Record.FieldValues ( Record.ReorderFields ( r, sortNames ) ),
( x ) => Text.Split ( Text.BetweenDelimiters ( x, "(", ")" ), "," )
),
transform = List.Transform (
List.Zip ( { vals, sortVals } ),
( x ) => Text.Combine ( { "(", Text.Combine ( List.Intersect ( x ), "," ), ")" } )
),
firstVals = List.Transform ( Record.FieldValues ( r ), ( x ) => Text.BeforeDelimiter ( x, "-" ) ),
resultLst = List.Transform ( List.Zip ( { firstVals, transform } ), ( x ) => Text.Combine ( x, "-" ) ),
result = Record.ReorderFields ( Record.FromList ( resultLst, names ), sortNames )
][result]
)
)
in
transform |
|
