多表取数

wtowccctv

从前面三张表中取数，其中范例1表，A列是父物料，成本由后面的C列子物料合计组成。

博爱风雨同舟

=SUM(SUMIF(INDIRECT("范例"&{1;2;3}&"!A2:A20"),$A2,OFFSET(INDIRECT("范例"&{1;2;3}&"!A2:A20"),,COLUMN($A:$E)))*COUNTIF(OFFSET(INDIRECT("范例"&{1;2;3}&"!A1"),,{1,2,3,4,5}),C$1))

博爱风雨同舟

= REDUCE(0,"范例"&{1,2,3},LAMBDA(x,y,x+SUM(IFERROR((INDIRECT(y&"!A2:A20")=$A2)*(INDIRECT(y&"!b1:F1")=C$1)*(INDIRECT(y&"!b2:F20")),0))))

ericxzhou

Python+Pandas:
import pandas as pd

f = pd.ExcelFile('pandas exercise/sample407.xlsx')
lst = [f.parse(ws, index_col=[0, 1]) for ws in f.sheet_names]
lst[0] = lst[0].groupby(level=[0, 1])[['材料', '人工']].sum()
df = pd.concat(lst[:-1])
df1 = lst[-1].join(df, lsuffix='l_').assign(合计=lambda x: x['材料'] + x['人工'])
df1.iloc[:, -3:]