英文:
Using Office365 Excel array formulas, how to convert this standard formula?
问题
这两个公式是相同的，只是第一个不是数组公式，而第二个是。如何将第一个公式转换为数组公式？在使用数组公式时出现循环逻辑。
标准公式（正常工作，没有循环逻辑）：
=LET(a, A2, b, B2, c, C2, d, D1, e, E1,
dd, IF(a = 1, 0, d),
ee, IF(a = 1, 0, e),
(b * c + dd * ee ) / (b + dd) )
数组公式（循环逻辑错误）：
=LET(a, A9:A12, b, B9:B12, c, C9:C12, d, D8:D11, e, E8:E11,
dd, IF(a = 1, 0, d),
ee, IF(a = 1, 0, e),
(b * c + dd * ee ) / (b + dd) )
这个公式是两组数字的相对简单的加权平均值。试图将其转换为数组公式。先前的结果在下一行中使用，只有在第一行没有先前的结果。
困难之处在于如何在计算当前单元格时引用先前的结果单元格。
英文:
These two formulas are the same, except the first one is not an array formula and the second one is. How can the first formula be converted to an array formula? Getting circular logic when using the array formula.
Standard Formula (works fine, no circular logic):
=LET(a, A2, b, B2, c, C2, d, D1, e, E1,
dd, IF(a = 1, 0, d),
ee, IF(a = 1, 0, e),
(b * c + dd * ee ) / (b + dd) )
Array Formula (circular logic error):
=LET(a, A9:A12, b, B9:B12, c, C9:C12, d, D8:D11, e,E8:E11,
dd, IF(a = 1, 0, d),
ee, IF(a = 1, 0, e),
(b * c + dd * ee ) / (b + dd) )
The formula is a fairly simple weighted average of two sets of numbers. Trying to convert it to an array formula. The previous result is used on the following row, except on the first row where there is no previous result.
The difficulty is in how to reference the previous result cell when calculating the current cell.
Data:
| Seq | B | C | D |
|---|---|---|---|
| 1 | 100 | 1.00 | - |
| 2 | 100 | 3.00 | 800 |
| 3 | 250 | 2.00 | 200 |
| 4 | 400 | 5.00 | 300 |
答案1
得分: 1
INDEX每个数组并使用SCAN返回值：
=LET(
a, A9:A12,
b, B9:B12,
c, C9:C12,
d, D8:D11,
dd, IF(a = 1, 0, d),
SCAN(0,a,LAMBDA(z,y,(INDEX(b,y)*INDEX(c,y)+INDEX(dd,y)*z)/(INDEX(b,y)+INDEX(dd,y)))))
英文:
INDEX each array and use SCAN to return the values:
=LET(
a, A9:A12,
b, B9:B12,
c, C9:C12,
d, D8:D11,
dd, IF(a = 1, 0, d),
SCAN(0,a,LAMBDA(z,y,(INDEX(b,y)*INDEX(c,y)+INDEX(dd,y)*z)/(INDEX(b,y)+INDEX(dd,y)))))
答案2
得分: 1
- 使用SCAN和位移进行累积
- 丢弃最后一行（
DROP）的最后一列（TAKE），并在其上堆叠一个零（VSTACK）：
=LET(data,B2:D5,
dt,HSTACK(DROP(data,,-1),VSTACK(0,DROP(TAKE(data,,-1),-1)))),
SCAN(0,SEQUENCE(ROWS(dt)),LAMBDA(sr,r,
LET(b,INDEX(dt,r,1),c,INDEX(dt,r,2),d,INDEX(dt,r,3),
(b*c+d*sr)/(b+d)))))
英文:
Accumulate Using SCAN With a Shift
-
Lose the last row (
DROP) of the last column (TAKE) and stack a zero on top of it (VSTACK):VSTACK(0,DROP(TAKE(data,,-1),-1))
=LET(data,B2:D5,
dt,HSTACK(DROP(data,,-1),VSTACK(0,DROP(TAKE(data,,-1),-1))),
SCAN(0,SEQUENCE(ROWS(dt)),LAMBDA(sr,r,
LET(b,INDEX(dt,r,1),c,INDEX(dt,r,2),d,INDEX(dt,r,3),
(b*c+d*sr)/(b+d)))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论