请注意，由于缺少数据源文件，以下代码仅为示例，需要根据实际数据文件进行修改和调整。

import pandas as pd

读取数据源sheet

data_source = pd.read_excel('data.xlsx', sheet_name='数据源')

新建指标明细sheet

index_detail = pd.DataFrame(columns=['序号', '对应省份', '类型', '覆盖类']) index_detail.loc[0] = ['1', '北京', '省份', ''] index_detail.loc[1] = ['', '', '类型', '5G驻留时长占比'] index_detail.loc[2] = ['', '', '', '5G全网弱覆盖小区占比'] index_detail.loc[3] = ['', '', '', '5G无覆盖问题场景占比'] index_detail.loc[4] = ['', '', '', '4G无覆盖问题场景占比'] index_detail.loc[5] = ['', '', '', '4GMDT弱覆盖占比'] index_detail.loc[6] = ['', '', '', '覆盖类指标均值'] index_detail.loc[1:6, '序号':'对应省份'] = index_detail.loc[1:6, '序号':'对应省份'].fillna('') index_detail.loc[1:4, 'C'] = '指标值' index_detail.loc[1:4, 'D':'T'] = '得分' index_detail.loc[5, 'C'] = '基准值' index_detail.loc[6, 'C'] = '挑战值' index_detail.loc[5:6, 'D':'T'] = '-' index_detail.iloc[0, 0] = '1月省端画像指标计算及排名明细' index_detail.iloc[0, 0] = index_detail.iloc[0, 0].title() index_detail.iloc[0, 0] = '微软雅黑' index_detail.iloc[0, 0] = 22

使用VLOOKUP函数填充对应省份

index_detail.loc[1:, '对应省份'] = index_detail.loc[1:, '对应省份'].apply( lambda x: '=VLOOKUP($B{}，数据源!$B:$C，2，FALSE)'.format(index_detail.index.get_loc(x)+1))

使用IF函数和ROUND函数填充得分和排名

index_detail.loc[2, 'D':'F'] = ['=ROUND((IF(D7<D$5,D7/D$560,IF(AND(D7<D$6,D7>=D$5),60+(100-60)(D7-D$5)/(D$6-D$5),100))),0)', '=RANK.EQ(D7,D$7:D$37,0)'] index_detail.loc[2, 'G':'H'] = ['=ROUND((IF(G7>G$5,60-60*(G7-G$5)/(MAX(G$7:G$37)-G$5),IF(AND(G7>G$6,G7<=G$5),100+(60-100)(G7-G$6)/(G$5-G$6),100))),0)', '=RANK.EQ(G7,G$7:G$37,1)'] index_detail.loc[2, 'J':'L'] = ['=ROUND((IF(J7>J$5,60-60(J7-J$5)/(MAX(J$7:J$37)-J$5),IF(AND(J7>J$6,J7<=J$5),100+(60-100)(J7-J$6)/(J$5-J$6),100))),0)', '=RANK.EQ(J7,J$7:J$37,1)'] index_detail.loc[2, 'M':'O'] = ['=ROUND((IF(M7>M$5,60-60(M7-M$5)/(MAX(M$7:M$37)-M$5),IF(AND(M7>M$6,M7<=M$5),100+(60-100)(M7-M$6)/(M$5-M$6),100))),0)', '=RANK.EQ(M7,M$7:M$37,1)'] index_detail.loc[2, 'P':'R'] = ['=ROUND((IF(P7>P$5,60-60(P7-P$5)/(MAX(P$7:P$37)-P$5),IF(AND(P7>P$6,P7<=P$5),100+(60-100)*(P7-P$6)/(P$5-P$6),100))),0)', '=RANK.EQ(P7,P$7:P$37,1)'] index_detail.loc[2, 'S':'T'] = ['=ROUND((AVERAGE(E7,H7,K7,N7,Q7)),0)', '=RANK.EQ(S7,S$7:S$37,0)']

保存结果

with pd.ExcelWriter('result.xlsx') as writer: index_detail.to_excel(writer, sheet_name='指标明细', index=False)