2张excel对应的某几列表头一致时进行复制

python实现：

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import numbers

# 定义文件路径
product_list_path = r'D:\产品列表.xlsx'
fund_pool_path = r'D:\私募基金池数据.xlsx'
output_path = r'D:\私募基金池数据-处理.xlsx'

# 读取产品列表文件
product_list_df = pd.read_excel(product_list_path)

# 加载私募基金池数据文件
wb = load_workbook(fund_pool_path)

# 遍历每个工作表
for sheet_name in wb.sheetnames:
    ws = wb[sheet_name]
    # 获取当前工作表的数据到 DataFrame
    data = []
    for row in ws.iter_rows(values_only=True):
        data.append(row)
    sub_table_df = pd.DataFrame(data[1:], columns=data[0])

    # 遍历产品列表中的每一行
    for _, row in product_list_df.iterrows():
        product_name = row['产品名称']
        # 检查产品名称是否在子表的表头中
        if product_name in sub_table_df.columns:
            # 找到产品名称列的索引
            product_col_index = sub_table_df.columns.get_loc(product_name)
            # 找到对应的日期列索引（日期列在产品名称列的前一列）
            date_col_index = product_col_index - 1

            # 找到产品名称列最后一个有值的行索引
            last_row_index = sub_table_df[product_name].last_valid_index()
            if last_row_index is None:
                # 如果该列没有有效数据，将最后一行索引设为 0
                last_row_index = 0
            else:
                # 否则将索引加 1，指向最后一行有数据的下一行
                last_row_index += 1

            # 将净值日期复制到对应的日期列的最后一行
            sub_table_df.iat[last_row_index, date_col_index] = row['净值日期']
            # 将累计净值复制到对应的产品名称列的最后一行
            sub_table_df.iat[last_row_index, product_col_index] = row['累计净值']

    # 将处理后的 DataFrame 数据写回到工作表中
    for r_idx, row in enumerate(dataframe_to_rows(sub_table_df, index=False, header=True), start=1):
        for c_idx, value in enumerate(row, start=1):
            cell = ws.cell(row=r_idx, column=c_idx, value=value)
            # 检查该列是否为日期列（通过列索引判断）
            if c_idx % 2 == 1 and isinstance(value, pd.Timestamp):
                # 设置日期格式为“年/月/日”
                cell.number_format = 'yyyy/mm/dd'

# 保存修改后的工作簿
wb.save(output_path)

VBA实现：

Sub CopyDataAndFormat()
    Dim productListWB As Workbook
    Dim fundPoolWB As Workbook
    Dim productListWS As Worksheet
    Dim fundPoolWS As Worksheet
    Dim productListLastRow As Long
    Dim fundPoolLastRow As Long
    Dim productNameCol As Long
    Dim productCodeCol As Long
    Dim netValueDateCol As Long
    Dim accumulatedNetValueCol As Long
    Dim productName As String
    Dim i As Long, j As Long, k As Long
    Dim targetCol As Long
    Dim dateCol As Long
    
    ' 定义文件路径
    Dim productListPath As String
    Dim fundPoolPath As String
    Dim outputPath As String
    productListPath = "D:\产品列表.xlsx"
    fundPoolPath = "D:\私募基金池数据.xlsx"
    outputPath = "D:\私募基金池数据-处理.xlsx"
    
    ' 打开产品列表文件
    Set productListWB = Workbooks.Open(productListPath)
    Set productListWS = productListWB.Sheets(1)
    
    ' 获取产品列表各列的索引
    productNameCol = Application.Match("产品名称", productListWS.Rows(1), 0)
    productCodeCol = Application.Match("产品代码", productListWS.Rows(1), 0)
    netValueDateCol = Application.Match("净值日期", productListWS.Rows(1), 0)
    accumulatedNetValueCol = Application.Match("累计净值", productListWS.Rows(1), 0)
    
    ' 获取产品列表的最后一行
    productListLastRow = productListWS.Cells(productListWS.Rows.Count, productNameCol).End(xlUp).Row
    
    ' 打开私募基金池数据文件
    Set fundPoolWB = Workbooks.Open(fundPoolPath)
    
    ' 遍历每个工作表
    For Each fundPoolWS In fundPoolWB.Sheets
        ' 遍历产品列表中的每一行
        For i = 2 To productListLastRow
            productName = productListWS.Cells(i, productNameCol).Value
            ' 检查产品名称是否在子表的表头中
            targetCol = 0
            For j = 1 To fundPoolWS.Cells(1, fundPoolWS.Columns.Count).End(xlToLeft).Column Step 2
                If fundPoolWS.Cells(1, j + 1).Value = productName Then
                    targetCol = j + 1
                    dateCol = j
                    Exit For
                End If
            Next j
            
            If targetCol > 0 Then
                ' 找到产品名称列最后一个有值的行索引
                fundPoolLastRow = fundPoolWS.Cells(fundPoolWS.Rows.Count, targetCol).End(xlUp).Row + 1
                ' 将净值日期复制到对应的日期列的最后一行
                fundPoolWS.Cells(fundPoolLastRow, dateCol).Value = productListWS.Cells(i, netValueDateCol).Value
                ' 设置日期格式为“年/月/日”
                fundPoolWS.Cells(fundPoolLastRow, dateCol).NumberFormat = "yyyy/mm/dd"
                ' 将累计净值复制到对应的产品名称列的最后一行
                fundPoolWS.Cells(fundPoolLastRow, targetCol).Value = productListWS.Cells(i, accumulatedNetValueCol).Value
            End If
        Next i
    Next fundPoolWS
    
    ' 保存修改后的工作簿
    fundPoolWB.SaveAs Filename:=outputPath
    ' 关闭工作簿
    productListWB.Close SaveChanges:=False
    fundPoolWB.Close SaveChanges:=False
End Sub