求大佬指点！根据一组排班时段数据求班次总得分

zuiai331

求大佬指点！根据一组排班时段数据求班次总得分
感谢先

quqiyuan

1. =SUM(ISNUMBER(FIND(SUBSTITUTE(SUBSTITUTE(FILTER(数据源!B:B,数据源!A:A=A2),"~","-")," ",),B2))*FILTER(数据源!C:C,数据源!A:A=A2))

复制代码

魚。。

1. =SUM(SUMIFS(数据源!C:C,数据源!A:A,A2,数据源!B:B,TRIM(SUBSTITUTE(TEXTSPLIT(B2,","),"-"," ~ "))))

复制代码

没有效验

ericxzhou

Python+Pandas:

import pandas as pd

df = pd.read_excel('Sample242.xlsx', '目标区域')
df1 = pd.read_excel('Sample242.xlsx', '数据源')
df1['起止时间'] = df1['起止时间'].str.replace(' ~ ', '-')
df2 = df.copy()
df2['排班应出勤时段'] = df2['排班应出勤时段'].str.split(', ')
df2 = df2.explode('排班应出勤时段')
for i, v in df2.iterrows():
  df3 = df1[(df1['车间'] == v['车间'])&(df1['起止时间'] == v['排班应出勤时段'])]
  if len(df3) > 0:
    df2.iloc[i, 2] = df3.iloc[0, 2]
df.iloc[:, -1] = df2.groupby(level=0).sum().iloc[:, -1]
df.to_excel('Sample242_results.xlsx', index=False)