如何通过另一文件替换Excel的v1、v2列值为对应名称？支持多工具实现

Got it, let's break down how to solve this value-mapping problem with three common tools—Excel, Python, and Matlab. Each approach has its own strengths, so pick the one that aligns best with your daily workflow!

Excel Method #

This is great if you prefer a no-code, point-and-click solution:

1. Open both your source Excel file (let's call it data.xlsx, with columns v1, v2, commID) and the mapping file (mapping.xlsx, with Index, Name).
2. In data.xlsx, insert two new helper columns next to v1 and v2 respectively.
3. For the helper column next to v1, use the VLOOKUP function to pull the matching Name:

   =VLOOKUP(A2, [mapping.xlsx]Sheet1!$A:$B, 2, FALSE)
   

   • A2 is the first cell in your v1 column
   • [mapping.xlsx]Sheet1!$A:$B refers to the full range of your mapping file's Index and Name columns
   • 2 tells Excel to return the value from the second column (Name)
   • FALSE ensures we only get exact matches
4. Repeat the same logic for the helper column next to v2, replacing A2 with the first cell in your v2 column.
5. If you want to handle missing matches gracefully, wrap the formula in IFERROR:

   =IFERROR(VLOOKUP(A2, [mapping.xlsx]Sheet1!$A:$B, 2, FALSE), "No Match")
   

6. Copy the results from both helper columns, right-click, and choose Paste Values to replace the formulas with static text.
7. Delete the original v1 and v2 columns, rename the helper columns to v1 and v2, then save the file as your new output (e.g., final_output.xlsx).

Python Method (Using Pandas) #

Perfect if you need to automate this process or work with large datasets:
First, make sure you have the required libraries installed:

pip install pandas openpyxl

Then use this script:

import pandas as pd

# Load the two Excel files into DataFrames
df_data = pd.read_excel("data.xlsx")
df_mapping = pd.read_excel("mapping.xlsx")

# Create a dictionary to map Index values to their corresponding Names
name_mapping = df_mapping.set_index("Index")["Name"].to_dict()

# Replace the values in v1 and v2 with the mapped Names
df_data["v1"] = df_data["v1"].map(name_mapping)
df_data["v2"] = df_data["v2"].map(name_mapping)

# Optional: Replace any NaN values (from unmatched Indexes) with a custom message
df_data = df_data.fillna("No Match")

# Save the result to a new Excel file
df_data.to_excel("final_output.xlsx", index=False)

Matlab Method #

Ideal if you're already working in a Matlab environment:

% Read the input files into tables
data_table = readtable('data.xlsx');
mapping_table = readtable('mapping.xlsx');

% Create a container map to link Index values to Names
name_map = containers.Map(mapping_table.Index, mapping_table.Name);

% Define a helper function to handle missing matches
function matched_name = get_mapped_name(index_val, map)
    if isKey(map, index_val)
        matched_name = map(index_val);
    else
        matched_name = 'No Match';
    end
end

% Apply the mapping to v1 and v2 columns
data_table.v1 = cellfun(@(x) get_mapped_name(x, name_map), data_table.v1, 'UniformOutput', false);
data_table.v2 = cellfun(@(x) get_mapped_name(x, name_map), data_table.v2, 'UniformOutput', false);

% Write the final table to a new Excel file
writetable(data_table, 'final_output.xlsx');

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