从 Excel 中的交换机 VLAN 配置输出生成完整的端口列表

Question 1

如果这些地址类似于单元格地址，则使用 UDF 的解决方案似乎要简单得多。考虑这样的代码：

    Function ListA(data As String)
       Dim rng As Range, i As Long, res() As String   ' declare variables
       data = Replace(data, Chr(160), vbNullString) ' remove non-breaking spaces
       data = Replace(data, "-", ":")   ' convert e.g. A1-A10 to A1:A10
       ReDim res(1 To Range(data).Count, 1 To 1)    ' define structure of
   '      the array: 1st dimension from 1 to number of cell addresses, 
   '      2nd dimension is necessary to get a column, not a row
       For Each rng In Range(data)     ' loop for each cell
          i = i + 1                    ' increase counter
          res(i, 1) = rng.Address(0, 0)   ' populate resulting array
    '         element with a cell address without $
       Next rng
       ListA = res     ' assign the resulting array to the function result 
    End Function

在工作表中输入
=ListA(A3)
即可获得垂直的地址列表。

Answer

如果这些地址类似于单元格地址，则使用 UDF 的解决方案似乎要简单得多。考虑这样的代码：

    Function ListA(data As String)
       Dim rng As Range, i As Long, res() As String   ' declare variables
       data = Replace(data, Chr(160), vbNullString) ' remove non-breaking spaces
       data = Replace(data, "-", ":")   ' convert e.g. A1-A10 to A1:A10
       ReDim res(1 To Range(data).Count, 1 To 1)    ' define structure of
   '      the array: 1st dimension from 1 to number of cell addresses, 
   '      2nd dimension is necessary to get a column, not a row
       For Each rng In Range(data)     ' loop for each cell
          i = i + 1                    ' increase counter
          res(i, 1) = rng.Address(0, 0)   ' populate resulting array
    '         element with a cell address without $
       Next rng
       ListA = res     ' assign the resulting array to the function result 
    End Function

在工作表中输入
=ListA(A3)
即可获得垂直的地址列表。

Question 2

这是我去年针对此类问题开发的通用 lambda 模块。

这是要点的链接，您可以将其导入 Excel Labs 插件中的高级公式环境工具中。或者，您可以在名称管理器中将 LAMBDA 函数创建为名称（尽管这是一个繁琐的过程）。

https://gist.github.com/ncalm/ef7ed953571eec1475c291948aa2dbc3

根据 SU 规则，以下是代码：

/*
array is a column of stuff to which we want to apply element function
row_function is some function that produces an array with a fixed number of columns
  the column count produced by row_function must be identical regardless of input
stack_function is one of V or H

If you're unsure how these work or why we would use them, please review these videos:
https://youtu.be/04jOeiMypXw
https://youtu.be/wEBLT9QfQRw

*/

V = LAMBDA(one, two, VSTACK(one, two));

H = LAMBDA(one, two, HSTACK(one, two));

STACKER = LAMBDA([stack_function],
    LAMBDA(row_function,
        LAMBDA(array,
            LET(
                _stack_function, IF(ISOMITTED(stack_function), V, stack_function),
                seq, SEQUENCE(ROWS(array)),
                firstrow, row_function(INDEX(array,1,)),
                reducer, REDUCE(
                    firstrow,
                    DROP(seq, 1),
                    LAMBDA(acc, curr,
                        LET(thisrow, INDEX(array,curr,), _stack_function(acc, row_function(thisrow)))
                    )
                ),
                reducer
            )
        )
    )
);

VSTACKER = STACKER(V);

HSTACKER = STACKER(H);

使用方法如下：

创建一个处理形式为{from}-{to}的单行的函数，例如：

CreateVLANRange=LAMBDA(x,
    LET(
        parts, TEXTSPLIT(x, "-"), 
        fromText, INDEX(parts,1,1), 
        toText, INDEX(parts,1,COLUMNS(parts)),
        getNum, LAMBDA(x, MID(x,2,LEN(x))),
        fromNum, getNum(fromText),
        LEFT(fromText)&SEQUENCE(getNum(toText)-fromNum+1,,fromNum)
    )
);

然后，假设您已将堆栈器代码导入到名为 STACKER 的模块中：

您可以使用此公式将输入字符串处理为单列端口：

=LET(input, A3,
arrayToProcess, TRIM(TEXTSPLIT(SUBSTITUTE(input,CHAR(160),),,",")),
STACKER.VSTACKER(CreateVLANRange)(arrayToProcess))

这可能看起来非常冗长，但定义行函数并将其传递给 VSTACKER 的过程是我见过的一个常见问题。

欢迎您观看要点中链接的视频，我会回答您对此提出的任何问题。

Answer