☰

使用 Excel 公式从一行创建多行数据

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2019
使用 Excel 公式从一行创建多行数据

我正在寻找一个公式，该公式获取表格的名称和数量并将其逐项列出，以便数量与名称出现的行数相匹配。

以此表作为输入为例：

姓名 数量
芯片 1
苏打 2
比萨 1
曲奇饼 3

并在下一张表中输出此内容：

逐项
芯片
苏打
苏打
比萨
曲奇饼
曲奇饼
曲奇饼

答案1

这应该适用于所有版本的 Excel，但 2019 可能会有使用新函数的更简单的方法；这假设您的初始数组位于 A1：B4。

首先，您需要在 A 中添加一个辅助列来对 B 列执行 COUNTIF。将其放在 Sheet2 的 A1 中，然后将其向下拖：

=COUNTIF(B$1:B1,B1)

在 B1 中您需要输入第一个值；要么输入要么输入=Sheet1!A1。

然后在 B2 中你可以使用这个：

=IF(VLOOKUP(B1, Sheet1!$A$1:$B$11,2,0)=A1,INDEX(Sheet1!$A$1:$A$11,MATCH(B1,Sheet1!$A$1:$A$11,0)+1),B1)

该方法通过对上面的行进行 Vlookup 并返回数量来实现。如果 A 列中的计数小于数量，则重复 B1 的值（A2 将增加 1）。如果计数等于最大值，则使用 INDEX/MATCH 查找列表中的下一个名称。

这是我的输出（带有额外的名称和数量）： Excel 的屏幕截图，显示正在使用的公式和所需的输出

请注意，名称和数量范围在此处写为，Sheet1!$A1:$B11但您可以根据需要扩展它。如果您有数量为 0 的物品，或者如果您有任何物品在列表中出现多次，它就会中断。如果这些是必需的，那么可能可以编写一个略有不同的版本

答案2

如果您愿意，这里有一些代码可以为您完成此操作。

按“ALT + F11”打开代码编辑器。

右键单击 VBAProject（在打开的工作簿弹出的代码编辑器中）

选择插入/新模块

将会弹出一个新的模块面板。

将下面的代码粘贴到其中：

Sub CreateList()
    Dim StartingCell
    StartingCell = "A1"
    Dim ActSheet As Worksheet
    Dim SelRange As range
 
    Set ActSheet = ActiveSheet
    Set SelRange = Selection

    Dim cel As range
    Dim selectedRange As range

    Set selectedRange = Application.Selection
    Dim offs As Integer
    Dim rowloc
    Dim cellname
    Dim loopcount
    Dim totalcount
    Dim rowdata()
    Dim actrow
    offs = 1
    rowloc = 0
    totalcount = 0
    
    For Each cel In selectedRange.Cells
        rowloc = rowloc + 1
        If rowloc = 2 Then
            rowloc = 0
            If cel.Value > 0 Then
                totalcount = totalcount + cel.Value
            End If
        End If
    Next cel
    actrow = 0
    ReDim rowdata(totalcount)
    For Each cel In selectedRange.Cells
        rowloc = rowloc + 1
        If rowloc = 2 Then
            rowloc = 0
            loopcount = cel.Value
            If loopcount > 0 Then
                For i = 1 To loopcount
                    rowdata(actrow) = cellname
                    actrow = actrow + 1
                Next i
            End If
        Else
            cellname = cel.Value
        End If
    Next cel
    
    Dim NewSheet As Worksheet
    Set NewSheet = ThisWorkbook.Sheets().Add()
    NewSheet.Move After:=Sheets(ThisWorkbook.Sheets().Count)
    NewSheet.range(StartingCell).Select
    For i = 0 To totalcount - 1
        ActiveCell.Value = rowdata(i)
        ActiveCell.Offset(1).Select
    Next i
        
    ActSheet.Select
    SelRange.Select
    MsgBox "New sheet created"
    
End Sub

返回你的工作簿。

突出显示您所需要的内容。

按“alt + F8”调出宏列表。

选择创建列表

点击运行按钮

创建新工作表时将显示一个弹出窗口。

它必须有两列宽（根据您的设计），第一列是文本值，下一列是数量。从技术上讲，您可以拥有任意多的行，只需突出显示所有行，然后就会创建一个新工作表（每次运行宏时）。

这应该可以正常工作，因为它们将被忽略。

您可以更改下面显示的脚本行（第 3 行）中的单元格来更改列表的开始创建位置（只需将 A1 更改为您想要的单元格）。

StartingCell = "A1"

一旦创建，可以根据需要进行复制和粘贴，而不必依赖初始列表。

VBA 不是我的第一个脚本语言，因此它可能更简洁，但它是有效的代码。

答案3

以下公式可以解决问题。它使用FILTERXML函数将文本转换为范围：

=FILTERXML("<Group><Element>"&  SUBSTITUTE(LEFT(TEXTJOIN("",TRUE,REPT((A2:A5&"|"),B2:B5)),  LEN(TEXTJOIN("",TRUE,REPT((A2:A5&"|"),B2:B5)))-1),  "|",  "</Element><Element>")  &"</Element></Group>","/*/*")

MS 表示 Excel 2019 具有 SPILL 功能。我不记得是这样，但我想一定是因为在他们向我们推出之前它已经转移到了 Excel 2021，所以我从来没有注意到我们拥有的仍然是 Excel 2019。LET显然不是在 2019 年，所以我没有将TEXTJOIN用途放入公式范围的名称中，而是使用了“根据需要重复多次”的方法。

REPT用于重复 A 列单元格中的文本，重复次数与 B 列单元格中的值相同。|放置在每个单元格之间，这样内容中的奇怪巧合不会导致以后出现错误的结果，但主要是因为有一个已知字符可以分割字符串。 该内部数组用于TEXTJOIN生成具有所有必需值且可拆分的字符串。LEFT然后用于删除末尾多余的“|”。

接下来，`FILTERXML 设置一个起始字符串，该字符串中的“|”字符被适当的 XML 标签对替换，还有一个结束字符串，外加一个简单的 XML 路径。

这显示了充分使用该想法的所有部分。但有人可能会更狡猾，用 XML 标记对替换“|”字符并跳过该SUBSTITUTE步骤：

=FILTERXML("<Group><Element>"&  LEFT(TEXTJOIN("",TRUE,REPT((A2:A5&"</Element><Element>"),B2:B5)),  LEN(TEXTJOIN("",TRUE,REPT((A2:A5&"</Element><Element>"),B2:B5)))-19)  &"</Element></Group>","/*/*")

的缺点是FILTERXML，这不是一个可以INDEX获取任意大小的范围并对其进行处理的函数。它有一个内部限制，我相信 处理的数组大约有 6,000 多个字符FILTERXML。（TEXTJOIN也有这种限制。）因此，人们不想不必要地向字符串中添加字符。事实上，虽然我为了清晰起见使用了和，但人们经常使用简单的和来代替。

但它仍然归结为无法直接处理大范围。因此，如果您有大约 250 行（示例数据集中有 700 行“有效”重复填充 700 行，这意味着它没有使用所有行（见下文）但没有因长度而失败，但每行中较长的条目和更多的重复会影响事情……所以就像他们过去常说的那样，它对某些“有效”值有效）它可以做得很好。此外，内部（对于公式计算过程，但通过使用 F9 逐步查看可见）数组将精确地截断它可以处理的长度，从而导致一些有趣的事情，因为它的最后一个条目可能会以奇怪的方式结束。例如，应该看起来像的数组{"horsey"}可能会像这样结束： {"hors伴随失败。

但如果列表足够短并且需要的重复次数足够少（平均每次 3 次，而不是 57 次），那么它可能会快速而简单。

但是，像 give 这样的 VBA 解决方案Netspud2K是此类问题的黄金标准。即使它没有完全优化，它也会很快，并且不受所需结果长度的限制。

相关内容