Как сгенерировать изменения буквенно-цифрового дерева с критериями?

Я работаю над шаблоном листа Excel, используемым для системы SAP, и у меня есть 2 столбца, как показано ниже:

Column C               Column E
Level                  Element Code
3                      ABCD.01.01.01               
4                      ABCD.01.01.01.01
4                      ABCD.01.01.01.02
4                      ABCD.01.01.01.03
3                      ABCD.01.01.02
4                      ABCD.01.01.02.01 'I Want to Restart Numbering Here
4                      ABCD.01.01.02.02
4                      ABCD.01.01.02.03

Мне удалось на уровне 3 автоматизировать весь лист с помощью макроса, как показано ниже.

Sub AutoNumber3()

Dim Rng, C As Range
Dim Lrow As Long
Dim i As Integer
Lrow = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Worksheets("Union").Range("C2:C" & Lrow)
i = 1
For Each C In Rng.Cells
If C.Value = 3 Then
For i = 1 To i Step 1
C.Offset(0, 2).Value = "ABCD.01.01." & i
Next i
End If
Next C
End Sub

и я использовал то же самое для уровня 4, как показано ниже

Sub AutoNumber4()
Dim Rng, C As Range
Dim Lrow As Long
Dim i As Integer
Lrow = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Worksheets("Union").Range("C2:C" & Lrow)
i = 1
For Each C In Rng.Cells
If C.Value = 4 Then
For i = 1 To i Step 1
C.Offset(0, 2).Value = "ABCD.01.01.01" & i
Next i
End If
Next C
End sub

Я хочу перезапустить нумерацию уровня 4 с 1 каждый раз, когда значения ячеек в столбце уровня = 3, используя Do До следующего C.Value = 3, I = 1, но я не могу правильно поставить его в процедуре Autonumber4

Ваша помощь очень ценится, так как этот лист может достигать 50000 или 100000 строк, которые невозможно заполнить вручную.

С уважением

Мохеб Лабиб


excel vba generate tree alphanumeric
person Meho2016    schedule 07.08.2020    source источник
comment
Всегда объявляйте переменные, прямо сейчас rng это Variant. Кроме того, вы объявляете i=1, а затем делаете For i=1 to i step 1. Это не имеет смысла, если вы собираетесь сделать это только один раз, вам не нужен For...Next. Кроме того, вы пытались сделать это с помощью обычных формул Excel? Кроме того, будут ли все числа иметь 2 цифры? Вы говорите, что существует более 100 000 строк. Как будет представлена ​​строка 95.000?   -  person Foxfire And Burns And Burns    schedule 07.08.2020

Ответы (2)


arrow_upward
2
arrow_downward

Попробуй это

Sub AutoNumber()
    Dim rngLevels As Range, cl As Range
    Dim lLastRow As Long, i As Long
    Dim sElemCode As String
    Dim vLevelsCounter() As Long
    
    With ThisWorkbook.Sheets("Union")
        lLastRow = Evaluate("=COUNTA(" & .Name & "!C:C)")
        lLastRow = WorksheetFunction.Max(lLastRow, .Cells(Rows.count, "C").End(xlUp).Row)
        Set rngLevels = .Range("C2:C" & lLastRow)
    End With
    
    For Each cl In rngLevels.Cells
        ' Uncomment "If" to use it on filtered data only
        'If Not cl.EntireRow.Hidden Then
            UpdateLevelsCounters vLevelsCounter, cl.Value
            
            sElemCode = "ABCD"
            For i = 1 To cl.Value
                sElemCode = sElemCode & "." & Format(vLevelsCounter(i), "00")
            Next i
            cl.Offset(0, 2).Value = sElemCode
        'End If
    Next cl
End Sub

Function UpdateLevelsCounters(ByRef arr() As Long, lLevel As Long)
    If lLevel < 1 Then Exit Function
    
    Dim i As Long
    ReDim Preserve arr(1 To lLevel)
    
    For i = LBound(arr) To lLevel - 1
        If arr(i) = 0 Then arr(i) = 1
    Next i
    arr(lLevel) = arr(lLevel) + 1
End Function

Это должно работать и для уровней, отличных от 3 и 4 (надеюсь)

введите здесь описание изображения

person Super Symmetry    schedule 07.08.2020
comment
ВАУ, ты мужик, большое спасибо, все работает отлично - person Meho2016; 07.08.2020

arrow_upward
1
arrow_downward

Вы не указали, будет ли ваш счет всегда состоять из двух цифр или нет, и может ли он быть чем-то вроде 01.20.99.99, но эта формула может привести вас к хорошему (не проверено с 100000 записей)

введите здесь описание изображения

=IF(C2=3;"ABCD.01.01."&TEXT(COUNTIF($C$2:C2;C2);"00");INDIRECT("E"&SUMPRODUCT(MAX(--($C$2:C2=3)*ROW($C$2:C2))))&"."&TEXT(SUMPRODUCT(--($C$2:C2=4)*--(ROW($C$2:C2)>SUMPRODUCT(MAX(--($C$2:C2=3)*ROW($C$2:C2)))));"00"))

Вот как это работает:

A) Во-первых, мы проверяем, является ли ячейка в столбце C числом 3 или 4. В случае, если это число 3, мы делаем ;"ABCD.01.01."&TEXT(COUNTIF($C$2:C2;C2);"00");. Это подсчитает, сколько раз число 3 появляется в диапазоне $C$2:C2, и объединит его в строку ABCD.01.01.. Хитрость здесь заключается в использовании $C$2:C2, потому что это делает диапазон динамическим (но может перегрузить время вычислений)

Б) Если не 3, то мы делаем действительно сложную часть, которую я попытаюсь объяснить. Кроме того, мы используем трюк с динамическим диапазоном.

  1. SUMPRODUCT(MAX(--($C$2:C2=3)*ROW($C$2:C2)))) эта часть используется дважды. Он получит номер последней строки из последних 3 значений в столбце C. Пример:ROW($C$2:C6) получит массив только номеров строк, например {2;3;4;5;6}. --($C$2:C6=3) вернет массив из нуля/единицы в зависимости от того, равна ячейка или не равна 3, что-то вроде {1;0;0;0;1}. ($C$2:C6=3)*ROW($C$2:C6)) умножит оба массива, поэтому мы получим {1;0;0;0;1}*{2;3;4;5;6}={2;0;0;0;6}. И с MAX мы получаем максимальное значение из этого массива. Это 6 означает последнюю позицию значения 3.
  2. Мы используем ДВССЫЛ в сочетании с номером шага 1, чтобы получить текст внутри ячейки
  3. SUMPRODUCT(--($C$2:C2=4)*--(ROW($C$2:C2)>SUMPRODUCT(MAX(--($C$2:C2=3)*ROW($C$2:C2)))));" Все после > имеет ту же логику, что и шаг 1. Он вернет номер строки последней ячейки, содержащей 3. Часть SUMPRODUCT(--($C$2:C2=4)*--(ROW($C$2:C2) просто получит номера строк тех ячеек, содержащих значение 4, и какие номера строк выше чем значение, полученное на шаге 1. Таким образом, вы убедитесь, как подсчитывать ячейки, содержащие 4 значения, между двумя ячейками, содержащими 3 значения.
  4. Мы объединяем все, чтобы сформировать окончательную строку.
  5. Функции ТЕКСТ используются только для того, чтобы вычислить 2 цифры.

Вы можете использовать это вручную или вставить формулу с помощью VBA, перетащить ее вниз, а затем преобразовать все в значения (я бы, вероятно, так и сделал). Что-то вроде этого может сработать.

Sub Macro1()
Dim LR As Long

LR = Range("C" & Rows.Count).End(xlUp).Row 'last non blank row in column c

Range("E2").FormulaR1C1 = _
    "=IF(RC[-2]=3,""ABCD.01.01.""&TEXT(COUNTIF(R2C3:RC[-2],RC[-2]),""00""),INDIRECT(""E""&SUMPRODUCT(MAX(--(R2C3:RC[-2]=3)*ROW(R2C3:RC[-2]))))&"".""&TEXT(SUMPRODUCT(--(R2C3:RC[-2]=4)*--(ROW(R2C3:RC[-2])>SUMPRODUCT(MAX(--(R2C3:RC[-2]=3)*ROW(R2C3:RC[-2]))))),""00""))"
Range("E2").AutoFill Destination:=Range("E2:E" & LR), Type:=xlFillDefault
Range("E2:E" & LR) = Range("E2:E" & LR).Value 'paste into values
End Sub

ПРИМЕЧАНИЕ. Вероятно, вам потребуется адаптировать это в зависимости от результатов (мы не знаем, может ли число из 3 или 4 значений иметь 3 или 4 цифры и т. д.).

person Foxfire And Burns And Burns    schedule 07.08.2020