如何基于Excel多条件匹配创建字典条目

Got it, let's break down how to solve this Excel data matching task. I'll show you two reliable methods—one that's lightning-fast with pandas, and another that keeps your original workbook formatting intact using openpyxl.

This approach is perfect if you just need to process the data quickly without worrying about preserving workbook formatting.

• First, we'll load both sheets into pandas DataFrames for easy filtering and lookup
• Build a lookup dictionary from SheetB to avoid repeated scans
• Filter SheetA to only rows where the CK column is empty, then validate matches and build our result dictionary

import pandas as pd

# Replace with your actual file path
file_path = "your_excel_file.xlsx"

# Load the Excel file and parse both sheets
wb = pd.ExcelFile(file_path)
sheet_a = wb.parse("SheetA")
sheet_b = wb.parse("SheetB")

# Create a lookup dict from SheetB: key = unique ID (H column), value = (N column value, AG column value)
# Drop rows with empty IDs to avoid invalid keys
b_lookup = sheet_b.dropna(subset=["H"]).set_index("H")[["N", "AG"]].to_dict("index")

# Initialize the final result dictionary
result_dict = {}

# Iterate through SheetA rows where CK column is empty
for idx, row in sheet_a[sheet_a["CK"].isna()].iterrows():
    unique_id = row["H"]
    # Check if the ID exists in our SheetB lookup
    if unique_id in b_lookup:
        n_column_val = b_lookup[unique_id]["N"]
        ag_column_val = b_lookup[unique_id]["AG"]
        # Only add to the dict if N column equals "ABC"
        if n_column_val == "ABC":
            result_dict[unique_id] = ag_column_val

print("Final matching dictionary:", result_dict)

Use this method if you need to keep your original workbook's formatting (like cell colors, formulas, or merged cells) intact.

• Load the workbook and access both sheets
• Pre-build a lookup dictionary from SheetB for fast matching
• Scan SheetA for empty CK cells, validate the corresponding SheetB entries, and build the result dict

from openpyxl import load_workbook

# Replace with your actual file path
file_path = "your_excel_file.xlsx"

# Load workbook (set read_only=False if you need to write changes back later)
wb = load_workbook(file_path, read_only=True)
sheet_a = wb["SheetA"]
sheet_b = wb["SheetB"]

# Build lookup dict from SheetB: key = unique ID (H column), value = (N column value, AG column value)
# Note: Columns are 0-indexed—adjust these numbers if your sheet structure differs
b_lookup = {}
# Skip header row if your data has one (adjust min_row as needed)
for row in sheet_b.iter_rows(min_row=2, values_only=True):
    unique_id = row[7]  # H column is the 8th column (index 7)
    n_val = row[13]     # N column is the 14th column (index 13)
    ag_val = row[32]    # AG column is the 33rd column (index 32)
    if unique_id is not None:
        b_lookup[unique_id] = (n_val, ag_val)

# Initialize result dictionary
result_dict = {}

# Iterate through SheetA rows, check for empty CK column
for row in sheet_a.iter_rows(min_row=2, values_only=True):
    unique_id = row[7]
    ck_val = row[90]  # CK column is the 91st column (index 90)
    # Check if CK cell is empty
    if ck_val is None or ck_val == "":
        if unique_id in b_lookup:
            n_col_val, ag_col_val = b_lookup[unique_id]
            if n_col_val == "ABC":
                result_dict[unique_id] = ag_col_val

print("Final matching dictionary:", result_dict)
wb.close()

Quick Notes: #

• Replace "your_excel_file.xlsx" with your actual file path
• Adjust column indexes (like row[7] for H column) if your sheet has a different structure (e.g., no header row, shifted columns)
• The code skips IDs that don't exist in SheetB—you can add a print statement or log if you need to track these cases

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