algorithm - 在 MS EXCEL 中交替混合数据

Question

我有一个包含 10.000 行的 csv 文件。2.000 行的值为“EXPL_1”。3.000 行的值为“EXPL_2”。2.500 行的值为“EXPL_3”。1.500 行的值为“EXPL_4”。2.000 行的值为“EXPL_5”。

我正在搜索一个函数，它将交替混合(重新排序)值并将继续混合它们直到完成。

所以最终的结果会是这样的:

EXPL_1,
EXPL_2,
EXPL_3,
EXPL_4,
EXPL_5,
EXPL_1,
EXPL_2,
EXPL_3,
EXPL_4,
EXPL_5,
.......... (x times repeat)
EXPL_1,
EXPL_2,
EXPL_3,
EXPL_5,    (*EXPL_4 values finished but continue to alternately mix the rest)  

*值按名称排序(第一个所有 EXPL_1，第二个所有 EXPL_2 等)*也许 future 会出现更多的值(value)。*我知道列表中有多少个值。

Answer 1

此代码根据值的数量“手动”将值添加到工作表。因此，如果某种类型的值较少，则会留下空格。我使用了 speardsheet 上的单元格，但您可以使用相同的逻辑对数组进行操作，而不是创建一个不连续的范围，您可以使用 For loop Step 将值添加到数组索引中

Dim ws As Worksheet
Dim one_rng As Range
Dim a1(), a2(), i As Long, ub As Long

Set ws = ThisWorkbook.Worksheets(1)
'Insert the number of values
For n = 1 To 5
    If n = 1 Then
    n_array = 20 'insert number of valuer for EXPL_1
    ElseIf n = 2 Then
    n_array = 30 'insert number of valuer for EXPL_2
    ElseIf n = 3 Then
    n_array = 25 'insert number of valuer for EXPL_3
    ElseIf n = 4 Then
    n_array = 15 'insert number of valuer for EXPL_4
    ElseIf n = 5 Then
    n_array = 20 'insert number of valuer for EXPL_5
    End If


    ReDim a1(1 To 1, 1 To n_array) As Variant
    For i = 1 To n_array
     a1(1, i) = CStr("EXPL_" & n)
    Next i
    ub = UBound(a1, 2)
    ReDim a2(1 To ub, 1 To 1) 'resize a2 ("right" shape) to match a1
        ' "flip" the a1 array into a2
        For i = 1 To ub
            a2(i, 1) = a1(1, i)
        Next i

    For i = 5 + n To (5 + n) * (n_array - 1) Step 5
        If i = (5 + n) Then Set one_rng = ws.Range("B" & n)
        Set new_rng = ws.Range("B" & i)
        Set one_rng = Union(one_rng, new_rng)
    Next i
    Debug.Print one_rng.Address 'Verify the Range
    one_rng = a2
Next n

如果需要delete the blank spaces , 可以做一些改变。

您可以对所用范围(第一行到最后一行)的空白值进行 .Autofilter，然后将其删除。

Sub DeleteBlankRows()
    Range("B:B").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

And after create an array and add the range to the array.

代码说明

5种EXPL_循环5次

For n = 1 To 5
Next n

插入值的数量以创建每个类型的数组

    If n = 1 Then
    n_array = 20 'insert number of valuer for EXPL_1
    ElseIf n = 2 Then
    n_array = 30 'insert number of valuer for EXPL_2
    ElseIf n = 3 Then
    n_array = 25 'insert number of valuer for EXPL_3
    ElseIf n = 4 Then
    n_array = 15 'insert number of valuer for EXPL_4
    ElseIf n = 5 Then
    n_array = 20 'insert number of valuer for EXPL_5
    End If

创建数组

ReDim a1(1 To 1, 1 To n_array) As Variant
For i = 1 To n_array
 a1(1, i) = CStr("EXPL_" & n)
Next i
ub = UBound(a1, 2)
ReDim a2(1 To ub, 1 To 1) 'resize a2 ("right" shape) to match a1
    ' "flip" the a1 array into a2
    For i = 1 To ub
        a2(i, 1) = a1(1, i)
    Next i

创建不连续的范围，跳过 5 行，行数与数组元素的行数相同

  For i = 5 + n To (5 + n) * (n_array - 1) Step 5
        If i = (5 + n) Then Set one_rng = ws.Range("B" & n)
        Set new_rng = ws.Range("B" & i)
        Set one_rng = Union(one_rng, new_rng)
    Next i

将数组插入范围

one_rng = a2