英文:
Google sheets, trailing sum of last 4 quarters and year over year percentage change
问题
1Q08 1
2Q08 2
3Q08 3
4Q08 4 10
1Q09 5 14 500%
2Q09 6 18 300%
3Q09 7 22 233.33%
4Q09 8 26 200%
1Q10 9 30 180%
在C1中使用一个公式，在D1中使用另一个公式，以生成列B中滚动的最近4个季度总和的数组，并在列D中进行年度同比比较。
我一直在尝试使用扫描函数这里，但不确定是否应该使用它。
目前，D5的一般公式是B5/B1，然后复制下来。
我需要rockinfreakshow的答案之外的另一种答案。我需要将其包装在另一个公式中，使用rockinfreakshow的答案后，它给我空白，即使在尝试调整偏移量后也是如此。这太奇怪了。
这是我迄今为止得到的，将rockinfreakshow的答案转换为一个命名函数后，用于横向（不是纵向），我需要最终的公式横向工作：
=map(arr,lambda(Σ,
if(or(Σ="",counta(index(arr,1,1):Σ)<4),,
sum(offset(Σ,,-3,,4))
)
))
如果我首先将另一个公式的数组输出到工作表上，然后使用rockinfreakshow的答案，它可以工作。但是，如果我将rockinfreakshow的答案包装在另一个公式中，它会给我一排空白。我认为问题在于偏移，但我认为我只需尝试不同的方法，直到它可以工作。
我还尝试在数组上使用ARRAY_CONSTRAIN：
ARRAY_CONSTRAIN(array)
,1,
COLUMNS(array)
)
我有一个不同的公式用于季度对季度百分比，这作为一个命名函数QOQ_DOWN：
=let(
New_ar,{0;ARRAY_CONSTRAIN(arr,ROWS(arr)-1,1)},
map(arr,new_ar,LAMBDA(t,t_lag,
iferror(t/t_lag-1
)))))
我可以在横向上使用它：transpose(QOQ_DOWN(transpose(B1:B9)))
英文:
1Q08 1
2Q08 2
3Q08 3
4Q08 4 10
1Q09 5 14 500%
2Q09 6 18 300%
3Q09 7 22 233.33%
4Q09 8 26 200%
1Q10 9 30 180%
How do I use one formula in C1 and another in D1 to produce an array in column C of trailing 4 quarters sum in column B, and a yoy comparison in column D?
I have been playing around with the scan function here, but not sure if it is the right one to use.
general formula for D5 right now is B5/B1 then pasted down
I need an alternative answer to rockinfreakshow's answer. I need to wrap it into another formula, and it's giving me blanks after I do it using rockinfreakshow's answer, even after playing around with the offset. It's so weird.
This is what i got so far after, turning rockinfreakshow's answer into a named function, this is for GOING ACROSS, NOT DOWN, I need the final formula to go across:
=map(arr,lambda(Σ,
if(or(Σ="",counta(index(arr,1,1):Σ)<4),,
sum(offset(Σ,,-3,,4))
)
))
If i let the other formula output the array first onto the sheet then use rockinfreakshow's answer, it works. However, if I wrap rockinfreakshow's answer around the other formula. It gives me a row of blanks. I think it is in the offset, but I think i just need to try different ways until it works.
I also tried to use ARRAY_CONSTRAIN on the array first:
ARRAY_CONSTRAIN(array)
,1,
COLUMNS(array)
)
I have a different and formula for quarter over quarter percentage, this works as a named function QOQ_DOWN:
=let(
New_ar,{0;ARRAY_CONSTRAIN(arr,ROWS(arr)-1,1)},
map(arr,new_ar,LAMBDA(t,t_lag,
iferror(t/t_lag-1
))))
I can use it on an array across: transpose(QOQ_DOWN(transpose(B1:B9)))
答案1
得分: 1
Cell_C1:
=map(B:B,lambda(Σ,if(or(Σ="",counta(B1:Σ)<4),,sum(offset(Σ,-3,,4)))))
Cell_D1:
=map(B:B,lambda(Σ,if(or(Σ="",counta(B1:Σ)<5),,to_percent(Σ/offset(Σ,-4,)))))
英文:
You may try:
Cell_C1:
=map(B:B,lambda(Σ,if(or(Σ="",counta(B1:Σ)<4),,sum(offset(Σ,-3,,4)))))
Cell_D1:
=map(B:B,lambda(Σ,if(or(Σ="",counta(B1:Σ)<5),,to_percent(Σ/offset(Σ,-4,)))))
答案2
得分: 0
=LET(
arr, G1:P1,
counter, SEQUENCE(1, COLUMNS(G1:P1), 1, 1),
table, TRANSPOSE({counter; arr}),
MAP(counter, LAMBDA(c,
IF(c < 4, ,
SUM(QUERY(table, "select Col2 where Col1 >= " & c-3 & " and Col1 <= " & c))
))))
这个代码可以正常运行。
英文:
=LET(
arr,G1:P1,
counter, SEQUENCE(1,COLUMNS(G1:P1),1,1),
table, transpose({counter;arr}),
MAP(counter,LAMBDA(c,
if(c<4,,
sum( QUERY(table,"select Col2 where Col1 >= "&c-3&" and Col1<="&c))
))))
this works.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论