☰

来自现有 Excel 文件的多个条目

tag-icon • tag-icon windows microsoft-excel
来自现有 Excel 文件的多个条目

我们的客户向我们提供了一个 excel 文件，其中列出了司机的姓名以及他们可获得的免费抽奖券数量，例如“bill smith（下一栏）17 张票

有没有办法将其变成 17 个单独的“比尔·史密斯”条目，以便我们可以为所有 286 名司机打印出总计 5000 多张罚单？有些人得到 1 张，有些人得到 5 张，有些人得到更多……

我们目前会根据需要手动重复司机姓名多次。

我们通过邮件合并将它们打印到 2x3 avery 标签模板，然后将它们剪开。

答案1

  1. 修改文件以使其具有以下属性：
    • A列：标题为“姓名”。所有单元格都包含要发放罚单的人员的姓名。
    • B 列：标题为“数字”。所有单元格均包含分配给 A 列同一行所列人员的票数。不包含其他文本。
    • 工作表上不包含除“姓名”和“编号”信息以外的其他数据。
  2. 选择包含“姓名”和“号码”信息的工作表，将文件保存为 CSV（逗号分隔）。为了便于说明，我们将使用 OrigCSV.csv 作为文件名。
  3. 打开 PowerShell 会话，并导航到包含刚刚保存的 CSV 的文件夹。
  4. 运行以下命令：
    • $x=ipcsv .\OrigCSV.csv;$x|%{for($y=1;$y-le$_.Number;$y++){$_.Name}}|Out-File NewCSV.csv
  5. 打开 NewCSV.csv 并验证名称是否以您想要的方式和数量列出。

如果您需要的不仅仅是复制名称，那么使用 PowerShell 仍然可以实现 - 只是更“有趣”一点。

以下是上面提供的命令行的扩展和注释版本：

<#
    First set $x so that it contains everything in OrigCSV.csv.
    Each line of the CSV will be an array element within $x, with "Name" and "Number" properties according to their entry in the CSV.

    ipcsv is a built-in alias for Import-Csv
#>
$x=ipcsv .\OrigCSV.csv;
<#
    Next step is to put all the objects in $x through a ForEach-Object loop.

    % is a built-in alias for ForEach-Object.
#>
$x|%{
    <#
        Within ForEach-Object, we're starting a For loop.
        The loop definition starts with setting a counter, $y, to 1.
        Then, if $y is less than or equal to the current line item's "Number" property, the script block will execute.
        After the script block executes, it will increment $y by 1 and check the loop condition again.
        Once $y becomes greater than the current line item's "Number" property, the For loop will exit.
    #>
    for($y=1;$y-le$_.Number;$y++)
    {
        # This next line simply outputs the "Name" property of the current line item.
        $_.Name
    # After the For loop exits, the script will return to the ForEach-Object loop and proceed to put the next item into the For loop.
    }
# After ForEach-Object is done with its work, we pipe all of the output to Out-File so that the list gets written to a new CSV file.
}|Out-File NewCSV.csv

答案2

这是一个 VBA 解决方案。首先，选择两列中的数据。如果存在列标题，请不要选择它们。

接下来，将此代码放入模块并执行。（有关执行此操作的说明，请参阅这个帖子。

Sub TicketList()
'Two columns of drivers and ticket counts should be selected (no headers) before running this Sub.
Dim drivers() As Variant, output() As Variant, shtOut As Worksheet
Dim i As Long, j As Long, k As Long, scount As Integer
drivers = Selection.Value
'Set size of output array to match total number of tickets
ReDim output(1 To Application.WorksheetFunction.Sum(Selection), 1 To 1) As Variant
For i = LBound(drivers, 1) To UBound(drivers, 1)
    For j = 1 To drivers(i, 2)
        k = k + 1
        output(k, 1) = drivers(i, 1)
    Next j
Next i
'Place tickets on new sheet named "Driver Tickets #"
For Each sht In ThisWorkbook.Sheets
    If InStr(sht.Name, "Driver Tickets") > 0 Then scount = scount + 1
Next sht
Set shtOut = Sheets.Add
If scount = 0 Then
    shtOut.Name = "Driver Tickets"
Else
    shtOut.Name = "Driver Tickets " & CStr(scount + 1)
End If
'Print output on the new sheet
shtOut.Range("A1").Resize(UBound(output, 1), 1).Value = output
End Sub

这将在名为“司机票”的新表上创建票证姓名列表。

相关内容