Google Sheets脚本需求：基于多单元格值修改相邻单元格内容

Hey there, let's get this sorted for you! It sounds like you're trying to automate updating column B based on conditions in columns A and D, and your previous script attempts haven't panned out. I'll cover solutions for both Excel (using formulas or VBA) and Google Sheets since those are the most common tools for this task—pick the one that fits your workflow.

Excel Solutions #

Option 1: Formula (No Script Needed) #

If you just need a dynamic update that refreshes automatically, drop this formula into cell B2 and drag it down to cover your range:

=IF(AND(ISNUMBER(A2),LEN(TEXT(A2,"0"))=6,D2="MATCH"),"ANN",B2)

• ISNUMBER(A2) ensures the value in A is a number
• LEN(TEXT(A2,"0"))=6 guarantees it's exactly 6 digits (fixes issues with numbers stored as text with leading zeros)
• D2="MATCH" checks the D column condition
• If all conditions are met, it sets B2 to "ANN"; otherwise, it keeps the existing value in B2

Option 2: VBA Script (Batch Update) #

For a one-time batch update or manual trigger, this VBA macro will do the trick:

Sub SetANNForMatchingRows()
    Dim targetSheet As Worksheet
    Dim lastRow As Long
    Dim currentRow As Long
    
    ' Replace "YourSheetName" with your actual worksheet name
    Set targetSheet = ThisWorkbook.Worksheets("YourSheetName")
    lastRow = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row starting from row 2
    For currentRow = 2 To lastRow
        With targetSheet
            ' Check if A is a 6-digit number and D equals "MATCH"
            If IsNumeric(.Cells(currentRow, "A").Value) _
                And Len(Trim(.Cells(currentRow, "A").Value)) = 6 _
                And UCase(.Cells(currentRow, "D").Value) = "MATCH" Then
                
                .Cells(currentRow, "B").Value = "ANN"
            End If
        End With
    Next currentRow
End Sub

To use this:

1. Press Alt + F11 to open the VBA editor
2. Right-click your workbook in the Project Explorer > Insert > Module
3. Paste the code, update the sheet name
4. Press F5 to run the macro, or assign it to a button for easy access

Google Sheets Solutions #

Option 1: Array Formula #

For a dynamic, auto-updating solution in Google Sheets, use this array formula in cell B2:

=ARRAYFORMULA(IF(ISBLANK(A2:A),"",IF(AND(ISNUMBER(A2:A),LEN(A2:A)=6,D2:D="MATCH"),"ANN",B2:B)))

Option 2: Apps Script (Batch Update) #

If you prefer a script-based approach for Google Sheets:

function updateBColumnToANN() {
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("YourSheetName");
    const dataRange = sheet.getDataRange();
    const values = dataRange.getValues();
    
    // Skip header row, start at index 1 (which maps to row 2)
    for (let i = 1; i < values.length; i++) {
        const aCellValue = values[i][0];
        const dCellValue = values[i][3];
        
        // Check conditions: A is 6-digit number, D is "MATCH"
        if (!isNaN(aCellValue) && aCellValue.toString().length === 6 && dCellValue === "MATCH") {
            sheet.getRange(i + 1, 2).setValue("ANN");
        }
    }
}

To use this:

1. Open your Google Sheet
2. Go to Extensions > Apps Script
3. Delete existing code, paste the script above, update the sheet name
4. Save the project and click the run button

All these solutions should handle your core requirement. Once you get this working, you can easily add more rules by extending the condition checks in the IF statements or loop logic.

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