Excel从编号单元格返回多值：按数量生成重复员工角色列表需求

Hey there, let's tackle this problem step by step. Here are a few solid methods to generate your repeated employee role list in Sheet2 based on the counts from Sheet1:

This is the simplest approach if you're using a modern Excel version with dynamic array support.

In cell A2 of Sheet2, enter this formula:

=TOCOL(BYROW(Sheet1!A2:B4, LAMBDA(row_data, REPT(INDEX(row_data, 1)&"|", INDEX(row_data, 2)))), , TRUE)

How it works: #

• BYROW loops through each row of your Sheet1 data (A2:B4)
• REPT repeats the employee role (plus a pipe separator) the number of times specified in the "员工数量" column
• TOCOL splits all the repeated values into a single column and automatically spills down to fill all rows
• The final TRUE ignores any empty values from the split

If you're using Excel 2019 or earlier, use this array formula. Enter it in cell A2 of Sheet2, then press Ctrl+Shift+Enter (not just Enter), and drag the fill handle down until you see #N/A (which means you've covered all repetitions):

=INDEX(Sheet1!$A$2:$A$4, MATCH(TRUE, SUBTOTAL(9, OFFSET(Sheet1!$B$2, 0, 0, ROW(INDIRECT("1:"&ROWS(Sheet1!$A$2:$A$4)))))>=ROW(A1), 0))

How it works: #

• SUBTOTAL calculates cumulative counts of employee numbers
• MATCH finds which employee group the current row falls into
• INDEX pulls the corresponding employee role

Power Query is perfect if you expect your data to grow, as it's easy to refresh without adjusting formulas.

1. Go to Sheet1, select your data range (including headers: "员工角色" and "员工数量")
2. Click Data > From Table/Range (make sure "My table has headers" is checked)
3. In the Power Query Editor:
   • Select the "员工数量" column
   • Click Add Column > Custom Column
   • Enter this formula in the custom column dialog:

     =List.Repeat({[员工角色]}, [员工数量])
     

   • Click OK – you'll see a new column with lists of repeated roles
   • Click the expand icon (two arrows) in the header of the custom column, then click Expand to New Rows
4. Delete the original "员工数量" and custom column if you don't need them
5. Click Home > Close & Load To... and choose to load the data to Sheet2

If you want to automate this process with a single click, use this VBA code:

1. Press Alt+F11 to open the VBA Editor
2. Right-click your workbook in the Project Explorer > Insert > Module
3. Paste this code into the module:

Sub GenerateRepeatedEmployeeList()
    Dim sourceSheet As Worksheet, destSheet As Worksheet
    Dim lastSourceRow As Long, currentRow As Long, repeatCount As Long
    Dim destRow As Long
    
    ' Set references to your sheets
    Set sourceSheet = ThisWorkbook.Sheets("Sheet1")
    Set destSheet = ThisWorkbook.Sheets("Sheet2")
    destRow = 1 ' Start writing from row 1 in Sheet2
    
    ' Clear existing data in Sheet2 (optional)
    destSheet.Cells.Clear
    
    ' Get the last row with data in Sheet1
    lastSourceRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each employee in Sheet1
    For currentRow = 2 To lastSourceRow ' Assumes headers are in row 1
        repeatCount = sourceSheet.Cells(currentRow, "B").Value
        ' Repeat the employee role the specified number of times
        For i = 1 To repeatCount
            destSheet.Cells(destRow, "A").Value = sourceSheet.Cells(currentRow, "A").Value
            destRow = destRow + 1
        Next i
    Next currentRow
    
    MsgBox "Repeated employee list generated successfully!", vbInformation
End Sub

4. Press F5 to run the macro, or assign it to a button in Excel for easy access.

内容的提问来源于stack exchange，提问作者Batman