求一个数据分配的公式

1786024734

1. =LOOKUP(F2,$B$4:$C$8)/SUM(NETWORKDAYS.INTL(EOMONTH(F2,-1)+1,EOMONTH(F2,0),{"1111000","0000111"})*{1.2,1})*1.2^(WEEKDAY(F2,2)>4)

复制代码

或

1. =LOOKUP(F2,$B$4:$C$8)/SUM(IFERROR(1.2^(WEEKDAY(TEXT(F2,"e-m-")&ROW($1:$31),2)>4),))*1.2^(WEEKDAY(F2,2)>4)

复制代码

ericxzhou

Python+Pandas:
import pandas as pd

df = pd.DataFrame({'date': pd.date_range('2024-1-1', '2024-5-31')})
df['month'] = df['date'].dt.month
df['weekday'] = df['date'].dt.weekday
df1 = pd.DataFrame({'month': range(1, 6), '月总数': [340, 600, 620, 3000, 930]})
df = df.merge(df1)
df['factor'] = df['weekday'].map(lambda x: 1 if x <=3 else 1.2)
df['factorSum'] = df['factor'].groupby(df.month).transform('sum')
df['数量'] = df['月总数'] / df['factorSum'] * df['factor']
df[['date', '数量']]

minchar

=LET(s,--TEXT(F2,"yyyy-m"),e,EOMONTH(F2,0),a,SUM(NETWORKDAYS.INTL(s,e,{"1111000","0000111"})*{1.2,1}),LOOKUP(s,B$3:C$8)/a)*(1+(WEEKDAY(F2,2)>4)*0.2)