Excel中基于上方空白单元格查找单元格对应列标题的方法问询

Got it, let's solve this problem exactly how you described it—finding each name's corresponding column header by looking upward until we hit a blank cell. I'll cover two practical methods: a formula-based approach (great for Excel 365/2021 or Google Sheets) and a VBA macro for more automation.

Formula Method (Excel 365/Google Sheets) #

First, let's assume your data is structured like this (a common setup matching your description):

Step 1: Extract All Names to a Single Column #

Use the TOCOL function (Excel 365) or FLATTEN (Google Sheets) to pull all non-blank names into a new column (let's say column G):

=TOCOL(FILTER(A:F, NOT(ISBLANK(A:F))),1)

This grabs every non-blank cell, and we'll filter out headers in the next step.

Step 2: Match Each Name to Its Header #

In the adjacent column (column H), use this formula to find the header by looking upward from the name's original cell until we hit a blank:
For a name in cell G2 (which originated from, say, A3), the formula would be:

=INDEX(A:A, MAX(IF(ISBLANK(A$1:A2), ROW(A$1:A2), 0)) - 1)

(For older Excel versions, press Ctrl+Shift+Enter to run this array formula; 365 users can just hit Enter.)

This formula works by:

1. Checking all cells above the name for blanks
2. Finding the last blank cell above the name
3. Grabbing the cell immediately above that blank (which is your header)

VBA Macro Method (For Any Excel Version) #

If you have a large dataset and want full automation, this macro will loop through all cells, find names, fetch their headers, and output the results to a new sheet:

Sub MapNamesToHeaders()
    Dim ws As Worksheet
    Dim newWs As Worksheet
    Dim lastRow As Long, lastCol As Long
    Dim i As Long, j As Long, headerRow As Long
    Dim outputRow As Long
    
    ' Set your source worksheet (update "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' Create a new sheet for results
    Set newWs = ThisWorkbook.Worksheets.Add
    newWs.Name = "Name-Header Map"
    ' Add output headers
    newWs.Range("A1").Value = "Name"
    newWs.Range("B1").Value = "Corresponding Header"
    outputRow = 2
    
    ' Get the last used row and column in your source sheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    
    ' Loop through every column and row
    For j = 1 To lastCol
        For i = 1 To lastRow
            ' Only process non-blank cells (names)
            If Not IsEmpty(ws.Cells(i, j)) Then
                ' Look upward until we hit a blank cell
                headerRow = i - 1
                Do While headerRow >= 1 And Not IsEmpty(ws.Cells(headerRow, j))
                    headerRow = headerRow - 1
                Loop
                ' The header is the cell right above that blank
                headerRow = headerRow - 1
                
                ' Write the name and header to the new sheet (if we found a valid header)
                If headerRow >= 1 Then
                    newWs.Cells(outputRow, 1).Value = ws.Cells(i, j).Value
                    newWs.Cells(outputRow, 2).Value = ws.Cells(headerRow, j).Value
                    outputRow = outputRow + 1
                End If
            End If
        Next i
    Next j
    
    ' Clean up the output sheet
    newWs.Columns("A:B").AutoFit
    MsgBox "Done! Check the 'Name-Header Map' sheet."
End Sub

How to Use the Macro: #

1. Press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project pane → Insert → Module.
3. Paste the code above, and update "Sheet1" to match your source sheet name.
4. Press F5 to run the macro, or assign it to a button for one-click access.

Both methods follow your exact logic: find each name, look up the column until a blank cell is found, and use the cell above that blank as the header. Pick the one that fits your Excel version and workflow best!

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