EDIT 1 → 2024-03-30:
- Included parameters in the lambda functions so the table names and columns may be injected by the user
- Updated lambda functions with such paramteres
- Even though I found a solution, I tested the whole programming and it s really resource/memmory intensive.
必须设法优化/简化这一框架。
Objective:
根据《材料法案》计算一系列日期的产品成本,并购买每件材料的价格史(相对)
Process:
• 采购产品部件和数量达到购买水平,然后按每个月的购买价格乘以。
Source data
TableBOM 每个产品所需部件组合(Q = qty)
| Componente 1 | Q1 | Componente 2 | Q2 | Resultado |
|---|---|---|---|---|
| Papel 1 | 1 | Tarjeta personal | 20 | Tarjeta x20 |
| Papel 2 | 2 | Tarjetón | 40 | Tarjetón x40 |
| Caja pe | 4 | Empaque pequeño | ||
| Cinta pe | 5 | Empaque pequeño | ||
| Separador pe | 6 | Empaque pequeño | ||
| Tarjeta x20 | 1 | Empaque pequeño | 1 | Tarjeta empaque pe |
| Tarjetón x40 | 1 | Empaque grande | 1 | Tarjetón empaque gr |
| Caja gr | 7 | Empaque grande | ||
| Cinta gr | 8 | Empaque grande | ||
| Nota | 1 | Empaque pequeño | 1 | Nota empaque pe |
| Tarjeta x20 | 1 | Empaque grande | 1 | Tarjeta empaque gr |
| Tarjetón empaque gr | 1 | Nota empaque pe | 2 | Tarjetón + nota |
| Papel 3 | 3 | Tarjetón | ||
| Divi gr | 9 | Empaque grande | ||
| Sobre 4 | 1 | Nota sola | 1 | Nota |
TablePurchaseComponent,每个材料的历史价格(相对)
| Fecha | Componente | Precio unitario |
|---|---|---|
| 1/01/2023 | Caja gr | 100 |
| 1/01/2023 | Caja pe | 110 |
| 1/01/2023 | Cinta gr | 120 |
| 1/01/2023 | Cinta pe | 130 |
| 1/01/2023 | Divi gr | 140 |
| 1/01/2023 | Nota sola | 150 |
| 1/01/2023 | Papel 1 | 10 |
| 1/01/2023 | Papel 2 | 20 |
| 1/01/2023 | Papel 3 | 30 |
| 1/01/2023 | Separador pe | 190 |
| 1/01/2023 | Sobre 4 | 200 |
| 1/01/2023 | Tarjeta personal | 2 |
| 1/01/2024 | Caja gr | 200 |
| 1/01/2024 | Caja pe | 220 |
| 1/01/2024 | Cinta gr | 240 |
| 1/01/2024 | Cinta pe | 260 |
| 1/01/2024 | Divi gr | 280 |
| 1/01/2024 | Nota sola | 300 |
| 1/01/2024 | Papel 1 | 20 |
| 1/01/2024 | Papel 2 | 40 |
| 1/01/2024 | Papel 3 | 60 |
| 1/01/2024 | Separador pe | 380 |
| 1/01/2024 | Sobre 4 | 400 |
| 1/01/2024 | Tarjeta personal | 4 |
表格:
Lambda functions (as defined names):
fxProcessVal:
Parameters → lookup_val;component1_cols;component2_cols;result_col
法典:
=LET(
comp_res; VSTACK(
FILTER(component1_cols; result_col = lookup_val);
FILTER(component2_cols; result_col = lookup_val)
);
comp_res_fil; FILTER(comp_res; CHOOSECOLS(comp_res; 2) <> "");
lookup_col; IFNA(EXPAND(lookup_val; ROWS(comp_res_fil)); lookup_val);
IFERROR(HSTACK(lookup_col; comp_res_fil); "")
)
<fxProcessCompRow>:
参数:data;lookup_row;component1_cols;component2_cols;result_col
法典:
=LET(
sourceData; fxProcessVal(
INDEX(data; lookup_row; 2);
component1_cols;
component2_cols;
result_col
);
res_val; INDEX(data; 1; 1);
res_col; IFNA(EXPAND(res_val; ROWS(sourceData)); res_val);
sourceRes; HSTACK(res_col; DROP(sourceData; 0; 1));
IF(
sourceData <> "";
FILTER(sourceRes; CHOOSECOLS(sourceRes; 2) <> "");
CHOOSEROWS(data; lookup_row)
)
)
http://www.ohchr.org。
参数:source;component1_cols;component2_cols;result_col
法典:
=LET(
seq; SEQUENCE(ROWS(source));
reducer; REDUCE(
"";
seq;
LAMBDA(acc; curr;
VSTACK(
acc;
IFNA(
fxProcessCompRow(source; curr; component1_cols; component2_cols; result_col);
HSTACK(""; ""; "")
)
)
)
);
temp_res; DROP(reducer; 1);
temp_res
)
<>strong>fxCompst:
参数:component1_cols;component2_cols;result_col
法典:
=IFNA(
INDEX(
unitprice_col;
MATCH(
MAXIFS(purchasedate_col; purchasedate_col; "<=" & date; purchasecomponent_col; comp) &
comp;
purchasedate_col & purchasecomponent_col;
0
)
);
0
)
<>prox>:
参数:seed;component1_cols;component2_cols;result_col
法典:
=LET(
res; IF(
COUNTA(seed) = 1;
fxProcessVal(seed; component1_cols; component2_cols; result_col);
fxProcessComp(seed; component1_cols; component2_cols; result_col)
);
comp; CONCAT(seed) = CONCAT(res);
IF(comp; seed; prox(res; component1_cols; component2_cols; result_col))
)
参考:
Formulas
探索生物浓缩计划清单的方法(并按所需产品材料和数量分列):
=UNIQUE(DROP(REDUCE("";TableBOM[Resultado];LAMBDA(acc;curr;VSTACK(acc;prox(curr;TableBOM[[Componente 1]:[Q1]];TableBOM[[Componente 2]:[Q2]];TableBOM[Resultado]))));1))
每一产品成本的计算日期采用这一公式,在幅度内动态确定:
=DATE(YEAR(Q1);SEQUENCE(1;DATEDIF(Q1;Q2;"M")+1;MONTH(Q1);1);1)
STUCKED HERE:
我想确定Q3(时间范围作为溢出范围),计算每个月每个产品的费用。
目前,预防母婴传播中心计算了所有几个月的总数(时间范围)。
我曾接受过《圣经》的审判,并结合了《巴塞莫行动计划》和《圣经》,但已经表明了这一点。
如果有任何帮助,我将不胜感激。
=LET(
data;L4#;
date;Q3#;
res;INDEX(data;;1);
comp;INDEX(data;;2);
q;INDEX(data;;3);
lab;UNIQUE(res);
cost;q*fxCompCost(comp;date);
rt;MAP(lab;LAMBDA(a; SUMPRODUCT((res=a)*cost)));
temp;HSTACK(lab;rt);
temp
)
预期成果:
