如何提取Excel中指定描述旁的所有值并整理成表格？

Hey there! I get that you're stuck trying to pull together those scattered 4x4 module values into a clean summary sheet—since you're just starting out with Excel, let's break this down into easy, actionable solutions, no fancy VBA experience required (though I'll throw in a simple VBA option if you want to level up later).

First, let's assume your source sheet (with all the 4x4 modules) is named SourceData, and your summary sheet is named Summary.

Solution 1: Dynamic Array Formulas (Excel 365/2021) #

This is the simplest method if you have a modern Excel version—formulas will automatically spill results into columns without manual dragging.

Pull all ID values into Summary!A2 #

Paste this formula into cell A2 of your Summary sheet:

=LET(
    // Pick all columns where descriptions (like ID) appear (adjust column numbers if needed)
    descCols, CHOOSECOLS(SourceData!A2:U125, 1, 6, 11, 16),
    // Pick the corresponding value columns (right next to each description column)
    valCols, CHOOSECOLS(SourceData!A2:U125, 2, 7, 12, 17),
    // Stack all description columns into a single list
    allDescs, TOCOL(descCols, TRUE),
    // Stack all value columns into a single list
    allVals, TOCOL(valCols, TRUE),
    // Filter to get only values where the description is "ID"
    FILTER(allVals, allDescs="ID")
)

Pull Price values into Summary!B2 #

Use the same logic, just change "ID" to "Price" at the end:

=LET(
    descCols, CHOOSECOLS(SourceData!A2:U125, 1, 6, 11, 16),
    valCols, CHOOSECOLS(SourceData!A2:U125, 2, 7, 12, 17),
    allDescs, TOCOL(descCols, TRUE),
    allVals, TOCOL(valCols, TRUE),
    FILTER(allVals, allDescs="Price")
)

Quick notes:

• Adjust the column numbers in CHOOSECOLS to match your actual description/value columns (e.g., if descriptions are in columns A, F, K, add 1,6,11 to the list).
• TOCOL(..., TRUE) ignores empty cells, so you won't get blank entries in your summary.

Solution 2: Compatible with Older Excel Versions (No Dynamic Arrays) #

If you're using Excel 2019 or earlier, you'll need array formulas (enter with Ctrl+Shift+Enter instead of just Enter) and drag them down until you see #N/A.

Pull ID values into Summary!A2 #

Enter this formula, press Ctrl+Shift+Enter, then drag down:

=INDEX(SourceData!$B:$L, SMALL(IF((SourceData!$A:$A="ID")+(SourceData!$F:$F="ID")+(SourceData!$K:$K="ID"), ROW(SourceData!$A:$A)), ROW(A1)), IF(SMALL(IF((SourceData!$A:$A="ID")+(SourceData!$F:$F="ID")+(SourceData!$K:$K="ID"), COLUMN(SourceData!$A:$A)), ROW(A1))=1,2,IF(SMALL(IF((SourceData!$A:$A="ID")+(SourceData!$F:$F="ID")+(SourceData!$K:$K="ID"), COLUMN(SourceData!$A:$A)), ROW(A1))=6,7,12)))

To pull Price values, just replace all instances of "ID" with "Price" in the formula.

This works by scanning multiple columns for your target description, then pulling the value from the cell immediately to the right.

Solution 3: Simple VBA Macro (Set It and Forget It) #

If you want to automate this entirely (no formulas needed), try this basic VBA macro. It will scan your entire source range and dump values into the summary sheet automatically.

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

Sub SummarizeModuleData()
    Dim sourceSheet As Worksheet
    Dim summarySheet As Worksheet
    Dim sourceRange As Range
    Dim cell As Range
    Dim idRow As Integer, priceRow As Integer, rentRow As Integer
    
    ' Update these sheet names to match your actual sheets
    Set sourceSheet = ThisWorkbook.Worksheets("SourceData")
    Set summarySheet = ThisWorkbook.Worksheets("Summary")
    
    ' Start writing values at row 2 (skip header row)
    idRow = 2
    priceRow = 2
    rentRow = 2
    
    ' Scan the entire source range you mentioned
    Set sourceRange = sourceSheet.Range("A2:U125")
    For Each cell In sourceRange
        ' Check what description the cell has
        Select Case UCase(cell.Value)
            Case "ID"
                summarySheet.Cells(idRow, "A").Value = cell.Offset(0, 1).Value
                idRow = idRow + 1
            Case "PRICE"
                summarySheet.Cells(priceRow, "B").Value = cell.Offset(0, 1).Value
                priceRow = priceRow + 1
            Case "RENT"
                summarySheet.Cells(rentRow, "C").Value = cell.Offset(0, 1).Value
                rentRow = rentRow + 1
            ' Add more cases here if you need to pull other values like "PROS/CONS"
            ' Case "PROS/CONS"
            '     summarySheet.Cells(prosRow, "D").Value = cell.Offset(0, 1).Value
            '     prosRow = prosRow + 1
        End Select
    Next cell
    
    MsgBox "Summary complete! Check your Summary sheet."
End Sub

4. Press F5 to run the macro, or go back to Excel → Developer tab → Macros → Select SummarizeModuleData → Run.

This macro will automatically find every instance of ID, Price, Rent, and write their corresponding values to columns A, B, C of your summary sheet. No need to worry about column positions—it just looks for the description text anywhere in the source range.

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