英文:
Combining 2 google sheet formulas
问题
我有以下两个公式：
公式1:
=ARRAYFORMULA(SPLIT(FLATTEN(G4:G1000&"💡"&H3:K3&"💡"&H4:K1000);"💡"))
公式2:
=QUERY(M4:O1000, "select M, N, O WHERE O is not null")
我有一些水平数据，需要将其转换为垂直数据：
ID Compensation Fee Reimbursement Interests
12345 1863 750
第一个公式可以实现这个目标：
ID Category Amount
12345 Compensation 1863
12345 Fee
12345 Reimbursement 750
12345 Interests
但是，正如您所见，它还包含了没有金额的行，我不希望包括它们。这时候第二个公式派上用场，它根据第一个公式的输出来进行查询，以去除这些行。
理想情况下，我希望能够用一个公式实现这一目标，但每次我尝试合并它们时，都会出现问题。我相信对于许多人来说，这似乎很明显，但我似乎无法做到。
非常感谢您的任何帮助。
英文:
I have:
- this formula
=ARRAYFORMULA(SPLIT(FLATTEN(G4:G1000&"🐕"&H3:K3&"🐕"&H4:K1000);"🐕")) - this formula
=QUERY(M4:O1000; "select M,N,O WHERE O is not null")
I have some horizontal data that I need for it be vertical
ID Compensation Fee Reimbursement Interests
12345 1863 750
And the 1st formula does that
ID Category Amount
12345 Compensation 1863
12345 Fee
12345 Reimbursement 750
12345 Interests
But, as you can see, it also has rows without amounts and I don't want that.
That's when the second formula is used, to remove those, and it does a query based on the output of the 1st formula.
Ideally, I would achieve this in one formula but every time I try to combine them, I break it. I'm sure this is quite obvious for many, but I can't seem to do it.
Any help would be much appreciated.
答案1
得分: 2
以下是翻译好的代码部分：
=let(Σ,reduce(wraprows(,3,),sequence(counta(G4:G)),lambda(a,c,{a;{tocol(wraprows(index(G4:G,c),4,index(G4:G,c))),tocol(H3:K3),tocol(index(H4:K,c))}})),
filter(Σ,index(Σ,,3)<>""))
英文:
Here's another approach:
=let(Σ,reduce(wraprows(,3,),sequence(counta(G4:G)),lambda(a,c,{a;{tocol(wraprows(index(G4:G,c),4,index(G4:G,c))),tocol(H3:K3),tocol(index(H4:K,c))}})),
filter(Σ,index(Σ,,3)<>""))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论