如何使用Python获取Excel中指定值对应的单元格/行号？

Hey there! Let's fix this problem efficiently—first, let's break down why your initial code wasn't working, then jump into clean, reliable solutions.

Issues with Your Existing Code #

• Nested Loop Approach: The double loop over huge ranges (1-99999 rows, 1-2999999 columns) is extremely slow, and the else: row = row + 1 breaks loop logic—you’ll skip rows unnecessarily since the outer loop already increments row.
• Range to DataFrame Line: That line only reads a specific range into a DataFrame, but includes no logic to match your target values to their positions.

Efficient Solution Using Pandas #

This method uses pandas' vectorized operations to quickly find matches, and converts positions to Excel’s real row/column numbering (Excel starts at row 1, column A, unlike pandas’ 0-based index).

Step-by-Step Code #

import pandas as pd

# 1. Load your target values from clipboard
target_df = pd.read_clipboard()
target_values = target_df['val'].tolist()

# 2. Load the Excel sheet into a DataFrame (use header=None if your Excel has no column headers)
# Replace 'your_excel_file.xlsx' with your actual file path
excel_df = pd.read_excel('your_excel_file.xlsx', sheet_name=1, header=None)

# 3. Find positions for each target value
for value in target_values:
    # Get all (row_index, column_index) pairs where the value matches
    matches = excel_df[excel_df == value].stack().index.tolist()
    
    if matches:
        print(f"Value *{value}* found at these locations:")
        for row_idx, col_idx in matches:
            # Convert to Excel's 1-based row number
            excel_row = row_idx + 1
            # Convert column index to Excel's letter notation (e.g., 0 → A, 1 → B)
            excel_col_letter = pd.io.excel.excel_column_name(col_idx)
            excel_col_num = col_idx + 1
            
            print(f"  - Row: {excel_row}, Column: {excel_col_letter} (Column number: {excel_col_num})")
    else:
        print(f"Value *{value}* was not found in the Excel sheet.")

Alternative with Xlwings (Direct Excel Interaction) #

If you’re already using xlwings (from your wb.sheets reference), this approach lets you work directly with the Excel object model:

import xlwings as xw
import pandas as pd

# Load target values
target_values = pd.read_clipboard()['val'].tolist()

# Open the Excel file (use mode='r' to avoid a visible window if needed)
with xw.Book('your_excel_file.xlsx') as wb:
    sheet = wb.sheets[1]
    # Load the used range into a DataFrame
    excel_df = sheet.used_range.options(pd.DataFrame, header=None, index=False).value
    
    for value in target_values:
        matches = excel_df[excel_df == value].stack().index.tolist()
        if matches:
            print(f"Value *{value}* found at:")
            for row_idx, col_idx in matches:
                # Get the actual Excel cell object
                cell = sheet.range(row_idx + 1, col_idx + 1)
                print(f"  - Cell Address: `{cell.address}`, Row: {cell.row}, Column Letter: {cell.column_letter}")
        else:
            print(f"Value *{value}* not found.")

Key Notes #

• Both methods handle multiple matches for a single value (if the same number appears multiple times in Excel).
• The pandas approach is faster for large datasets since it avoids looping through every cell manually.
• Replace 'your_excel_file.xlsx' with your actual file path, and adjust sheet_name=1 if your target sheet is at a different index (matches your original code’s sheet reference).

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