Question

I have a Google Sheet where each column represents a different topic, and every column contains 5 groups of 5 rows outlining the topic details along with an assigned name and status.

感谢。现在,我正在使用以下公式,从上至下搜索每一栏,并显示专题及其细节,如果情况没有完成,则第一个五栏对应特定名称:

=INDEX(SPLIT(ARRAY_CONSTRAIN(TOCOL(IF(($C5:$I21=B32)*($C6:$I22<>"Complete"),$C2:$I2&"|"&$C3:$I19,),1,1),5,1),"|"))

What I am trying to figure out now, however, is how to sort this data by the priority levels in rows 4, 8, 12, 16, and 20. (If there is no priority level, the data should appear in the order of the columns from top to bottom, left to right, after the data with priority levels.) This is the order in which I want the data to be output:

我曾尝试使用SORT,但我不敢肯定如何将其放在优先数据上,因为这似乎是由阵列第一行的数据分类的:

=INDEX(SORT(SPLIT(ARRAY_CONSTRAIN(TOCOL(IF(($C5:$I21=B32)*($C6:$I22<>"Complete"),$C2:$I2&"|"&$C3:$I19,),1,1),5,1),"|"),3,1))

我怎么能最好地完成我所期待的东西?

Answer 1

创建一种惯例<代码>filter_功能,该功能提供所希望的<代码>resultRange in a one (TO)COLumn,t>/code> 输入<代码>Range等于特定<代码>condition, 说Bob。
通过提供必要的<条码>OFFSET,使用<条码>>,获取<条码>、<条码>、>优先权/代码>、<条码>尾和<条码>。
FILTER the combinedData based on status
根据<代码>优先顺序填写。

=ARRAYFORMULA(LET(
    dataRng, C3:E22,
    cond, B24,
    filter_,LAMBDA(resRng,TOCOL(IF(dataRng=cond,resRng,))),
    topics, filter_(C2:E2),
    priority, filter_(OFFSET(dataRng,-1,0)),
    details, filter_(OFFSET(dataRng, -2,0)),
    status, filter_(OFFSET(dataRng, 1,0)),
    combinedData, FILTER({topics,details, priority},status<>"Complete"),
    SORTN(combinedData,5,2,3,1) 
))

Topics	Details	Priority
TOPIC B	4th details for Topic B	1
TOPIC A	Details for Topic A	2
TOPIC A	More details for Topic A	3
TOPIC C	4th details for Topic C	4
TOPIC B	Third details for Topic B	5

友情链接