基于关键短语实现Excel行隐藏/显示的自动化代码开发需求

Got it, let's break this down. You're dealing with daily Excel sheets each holding ~10k rows, and you need to scan a target column for 11 specific key phrases using a loop-based approach. Below are two solid solutions tailored to different tools you might be using:

If you prefer working directly in Excel, this VBA script aligns perfectly with your loop-based idea. It integrates seamlessly with your worksheets and lets you visualize matches right in the spreadsheet:

Sub CheckKeyPhrases()
    ' Define your 11 key phrases here (replace with your actual phrases)
    Dim keyPhrases As Variant
    keyPhrases = Array("Urgent", "Priority", "Review", "Approved", "Rejected", _
                       "Pending", "Completed", "Draft", "Final", "Escalate", "Follow-up")
    
    ' Set target worksheets and column (adjust sheet names/column as needed)
    Dim ws As Worksheet
    Dim targetColumn As Range
    Dim cell As Range
    Dim phraseIndex As Integer
    
    ' Loop through both daily worksheets
    For Each ws In ThisWorkbook.Worksheets(Array("DailySheet1", "DailySheet2"))
        ' Dynamically get the full target column (avoids empty rows at the bottom)
        Set targetColumn = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
        
        ' Loop through every cell in the target column
        For Each cell In targetColumn
            ' Check each key phrase against the cell content
            For phraseIndex = LBound(keyPhrases) To UBound(keyPhrases)
                ' Case-insensitive match (use vbBinaryCompare for case-sensitive)
                If InStr(1, cell.Value, keyPhrases(phraseIndex), vbTextCompare) > 0 Then
                    ' Customize what happens when a match is found:
                    cell.Interior.Color = RGB(255, 255, 0) ' Highlight cell yellow
                    cell.Offset(0, 1).Value = cell.Offset(0, 1).Value & ", " & keyPhrases(phraseIndex) ' Log phrase in adjacent column
                    Exit For ' Stop checking other phrases for this cell (remove if you want all matches)
                End If
            Next phraseIndex
        Next cell
    Next ws
    
    MsgBox "Phrase check finished!", vbInformation
End Sub

Key Notes: #

• Replace the keyPhrases array with your actual 11 phrases
• Adjust worksheet names (DailySheet1, DailySheet2) to match your files
• Modify the target column (A1:A...) if your data isn't in Column A
• The Exit For line stops checking further phrases once a match is found—remove it if you want to capture all matching phrases for a single cell

If you're comfortable with Python and want faster handling of large datasets, pandas is ideal. It's way more efficient for 10k+ rows and lets you automate saving results with formatting:

import pandas as pd
from openpyxl.styles import PatternFill
from openpyxl import load_workbook

# Define your 11 key phrases
key_phrases = [
    "Urgent", "Priority", "Review", "Approved", "Rejected",
    "Pending", "Completed", "Draft", "Final", "Escalate", "Follow-up"
]

# Load your daily Excel file
excel_path = "your_daily_excel_file.xlsx"
excel_file = pd.ExcelFile(excel_path)

# Process each worksheet
for sheet_name in excel_file.sheet_names:
    # Load only the target column (adjust "A" to your column name/index)
    df = excel_file.parse(sheet_name, usecols=["A"])
    df.rename(columns={df.columns[0]: "Target Data"}, inplace=True)
    
    # Add a column to track matched phrases
    df["Matched Phrases"] = ""
    
    # Check each key phrase against the target column
    for phrase in key_phrases:
        # Find rows containing the phrase (case-insensitive)
        matches = df["Target Data"].str.contains(phrase, case=False, na=False)
        # Append the phrase to the matched column for relevant rows
        df.loc[matches, "Matched Phrases"] = df.loc[matches, "Matched Phrases"].apply(
            lambda x: f"{x}, {phrase}" if x else phrase
        )
    
    # Save results to a new Excel file with highlights
    output_path = "matched_results.xlsx"
    df.to_excel(output_path, sheet_name=sheet_name, index=False)
    
    # Apply yellow highlight to rows with matches
    wb = load_workbook(output_path)
    ws = wb[sheet_name]
    yellow_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
    
    for row in range(2, ws.max_row + 1):  # Skip header row
        if ws.cell(row=row, column=2).value != "":
            for col in range(1, ws.max_column + 1):
                ws.cell(row=row, column=col).fill = yellow_fill
    
    wb.save(output_path)

print("Phrase check and formatting completed successfully!")

Key Notes: #

• Update excel_path to point to your daily Excel file
• Adjust the target column (usecols=["A"]) if your data is in a different column
• The script saves results to a new file to avoid overwriting your original data

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