如何用Python或Excel检测Excel列中单元格内容是否为其他单元格子串并定位

Hey there! Let's work through this problem together—since you're comfortable with Python, we can build an efficient solution to handle your 40k-row Excel dataset and find all substring matches with their cell locations.

Step 1: Setup Required Libraries #

First, make sure you have these libraries installed (they're standard for Excel and data handling in Python):

pip install pandas openpyxl tqdm

• pandas: To read/write Excel files and manage the dataset
• openpyxl: Required by pandas to handle .xlsx files
• tqdm: Optional but helpful to track progress with large datasets

Step 2: Full Solution Code #

Here's a complete, commented script that does exactly what you need. Just replace "your_input_file.xlsx" with your actual file path:

import pandas as pd
from openpyxl.utils import get_column_letter
from tqdm import tqdm

# Load your Excel data (assuming product names are in column A; adjust usecols if needed)
# Use header=None if your data has no header row
df = pd.read_excel("your_input_file.xlsx", usecols="A", header=None)
df.columns = ["Product Name"]  # Name the column for clarity

# Dictionary to store results: key = current cell address, value = list of matching cells + their content
substring_matches = {}

# Iterate through each row with a progress bar
for row_idx, product_name in tqdm(df["Product Name"].items(), total=len(df), desc="Processing rows"):
    # Convert row index to Excel-style row number (since Excel starts at 1)
    excel_row = row_idx + 1
    current_cell = f"{get_column_letter(1)}{excel_row}"  # e.g., "A1"
    
    # Skip empty cells to avoid errors
    if pd.isna(product_name):
        substring_matches[current_cell] = []
        continue
    
    # Find all rows where the product name is a substring (excluding the current row)
    # Use regex=False to treat special characters (like ., *, +) as literal text
    match_mask = (
        df["Product Name"].str.contains(str(product_name), na=False, regex=False)
        & (df.index != row_idx)
    )
    
    # Format matching cells as "A14: protein B2" style strings
    matching_entries = [
        f"{get_column_letter(1)}{match_idx + 1}: {df['Product Name'].iloc[match_idx]}"
        for match_idx in df[match_mask].index
    ]
    
    substring_matches[current_cell] = matching_entries

# Convert results to a DataFrame and save to a new Excel file
result_df = pd.DataFrame.from_dict(
    substring_matches,
    orient="index",
    columns=["Matching Cells (with content)"]
)
result_df.index.name = "Current Cell"

# Save the output—you can open this in Excel to see all matches
result_df.to_excel("product_substring_matches.xlsx")

print("Done! Check 'product_substring_matches.xlsx' for results.")

Key Customization Tips #

• Case Insensitivity: If you want matches regardless of uppercase/lowercase, add case=False to the str.contains() call.
• Handle Special Characters: The regex=False flag ensures characters like ., *, or + are treated as literal text (not regex patterns)—keep this unless you intentionally want regex matching.
• Different Column: If your product names are in a different column (e.g., column C), change usecols="A" to usecols="C" and the code will adjust the cell addresses automatically.

Performance Notes #

With 40k rows, this script will take a few minutes to run (depending on your machine), but the tqdm progress bar will keep you updated. If you need to speed it up further, you could pre-process all strings into a list and use more optimized search methods, but for most use cases, this approach is straightforward and reliable.

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