Excel：基于行内特定值批量填充C列区域标签

Got it, let's tackle this problem step by step—this is a super common scenario when working with grouped or sectioned data in spreadsheets like Excel or Google Sheets. Below are straightforward, actionable approaches depending on which tool you're using:

Excel Approach #

1. Formula-Based Auto-Fill (Dynamic) #

Assuming your "specified data" lives in Column A (adjust the column reference if yours is different), and your data starts at row 2 (with row 1 as headers):

• In cell C2, enter this formula:

  =IF(A2<>"", A2, C1)
  

  • How it works: The IF function checks if the current row's Column A has your specified data (we're using non-empty as a proxy here—if your specified data is a specific value like "Product Group", replace A2<>"" with A2="Product Group"). If it does, Column C takes that value; if not, it inherits the value from the row above it, automatically propagating the label until a new one is found.
• Click and drag the fill handle (the small square at the bottom-right of C2) down to the end of your dataset.

2. Convert to Static Values (Optional) #

If you don't want the formula to stay linked (e.g., to avoid breaking if you edit Column A later):

• Select the entire Column C, copy it (Ctrl+C or Cmd+C).
• Right-click the top cell of Column C, choose Paste Special > Values to replace the formulas with their calculated results.

Google Sheets Approach #

1. Basic Drag-Fill Formula #

Same logic as Excel works here:

• In cell C2, enter:

  =IF(A2<>"", A2, C1)
  

• Drag the fill handle down, or double-click it to auto-fill to the end of your data.

2. Array Formula (No Drag Needed) #

For a one-time setup that auto-fills the entire Column C without dragging, use this array formula (still assuming specified data is in Column A):

=ARRAYFORMULA(IF(A2:A="", "", VLOOKUP(ROW(A2:A), FILTER({ROW(A2:A), A2:A}, A2:A<>""), 2, TRUE)))

• How it works:
  • FILTER({ROW(A2:A), A2:A}, A2:A<>"") creates a list of row numbers where Column A has your specified data, paired with the data itself.
  • VLOOKUP then matches each row's number to the closest earlier row with a label, pulling that label into Column C.

Customizing for Specific "Specified Data" #

If your trigger isn't just any non-empty cell, tweak the condition:

• For a specific keyword (e.g., "Section Header"): Replace A2<>"" with A2="Section Header"
• For cells containing a substring (e.g., starts with "Group"): Use LEFT(A2,5)="Group"

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