英文:
Is it possible to fill with values above plus filtering the table, with one formula?
问题
我有一个包含列名为 "groups"、"subgroups"、"specifics" 以及 12 个月份的值的表格。我想要 (1) 用上面的值填充前两列，然后 (2) 对此表格进行排序，只保留 "specifics"。是否有公式可以实现这个目标？
给定数据
| group | sub | spec | sum | mon1 | mon2 | mon3 |
|---|---|---|---|---|---|---|
| a | 3 | 3 | 3 | |||
| b | 2 | 3 | 3 | |||
| c | 5 | 1 | 2 | 2 | ||
| c1 | 6 | 3 | 2 | 1 | ||
| b1 | 3 | 4 | 5 | |||
| c2 | 5 | 2 | 1 | 2 | ||
| a1 | 5 | 5 | 5 | |||
| b2 | 2 | 3 | 4 | |||
| c3 | 9 | 2 | 3 | 4 | ||
| c4 | 6 | 2 | 2 | 2 | ||
| c5 | 4 | 1 | 2 | 1 |
期望输出
| group | sub | spec | sum | mon1 | mon2 | mon3 |
|---|---|---|---|---|---|---|
| a | b | c | 5 | 1 | 2 | 2 |
| a | b | c1 | 6 | 3 | 2 | 1 |
| a | b1 | c2 | 5 | 2 | 1 | 2 |
| a1 | b2 | c3 | 9 | 2 | 3 | 4 |
| a1 | b2 | c4 | 6 | 2 | 2 | 2 |
| a1 | b2 | c5 | 4 | 1 | 2 | 1 |
英文:
I have a table with columns named "groups", "subgroups", "specifics" as well as values across the 12 months. I want to (1) fill first two columns with the values above and (2) sort this table to only specifics. Is there a formula to do it?
Given Data
| group | sub | spec | sum | mon1 | mon2 | mon3 |
|---|---|---|---|---|---|---|
| a | 3 | 3 | 3 | |||
| b | 2 | 3 | 3 | |||
| c | 5 | 1 | 2 | 2 | ||
| c1 | 6 | 3 | 2 | 1 | ||
| b1 | 3 | 4 | 5 | |||
| c2 | 5 | 2 | 1 | 2 | ||
| a1 | 5 | 5 | 5 | |||
| b2 | 2 | 3 | 4 | |||
| c3 | 9 | 2 | 3 | 4 | ||
| c4 | 6 | 2 | 2 | 2 | ||
| c5 | 4 | 1 | 2 | 1 |
Desired Output
| group | sub | spec | sum | mon1 | mon2 | mon3 |
|---|---|---|---|---|---|---|
| a | b | c | 5 | 1 | 2 | 2 |
| a | b | c1 | 6 | 3 | 2 | 1 |
| a | b1 | c2 | 5 | 2 | 1 | 2 |
| a1 | b2 | c3 | 9 | 2 | 3 | 4 |
| a1 | b2 | c4 | 6 | 2 | 2 | 2 |
| a1 | b2 | c5 | 4 | 1 | 2 | 1 |
答案1
得分: 2
尝试使用<kbd>SCAN( )</kbd>函数。
• 单元格<kbd>I1</kbd>中使用的公式：
=LET(
a,A2:A12,
b,B2:B12,
c,C2:G12,
d,SCAN("",a,LAMBDA(x,y,IF(y="",x,y))),
e,SCAN("",b,LAMBDA(x,y,IF(y="",x,y))),
VSTACK(A1:G1,SORT(FILTER(HSTACK(d,e,c),INDEX(c,,1)<>0),3)))
英文:
Try using <kbd>SCAN( )</kbd> function.
• Formula used in cell <kbd>I1</kbd>
=LET(
a,A2:A12,
b,B2:B12,
c,C2:G12,
d,SCAN("",a,LAMBDA(x,y,IF(y="",x,y))),
e,SCAN("",b,LAMBDA(x,y,IF(y="",x,y))),
VSTACK(A1:G1,SORT(FILTER(HSTACK(d,e,c),INDEX(c,,1)<>0),3)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论