我想知道是否有办法使用单个 Excel 公式通过最后的非空白元素填充数组中的空元素。
例如，给定一个 excel 数组 =TRANSPOSE({"A","","","B","","","C","D"}) 我想应用一个 excel 公式以便它返回 =TRANSPOSE( {"A","A","A","B","B","B","C","D"})
我尝试使用如下匹配方法 =TRANSPOSE(IF({"A","","","B","","","C","D"}>"",{"A","","","B","","","C","D"},INDEX({"A","","","B","","","C","D"},MATCH(TRUE,{"A","","","B","","","C","D"}}"",0)))) 并且结果 =TRANSPOSE( {"A","A","A","B","A","A","C","D"}) 因为 MATCH(TRUE,{"A","","","B","","","C","D"}) 从数组的开头搜索每个元素。
我也知道可以使用 =TRANSPOSE(INDEX({"A","","","B","","","C","D"},{1,1,1,4,4,4,7,8})) 来完成。
但是，我不知道如何使用 Excel 公式创建数组 {1,1,1,4,4,4,7,8}。
任何想法都值得感激。谢谢。
答案1
在 Excel 2021 中，您可以使用 LET 和 SEQUENCE：
=LET(Arr,{"A";"";"";"B";"";"";"C";"D"}, Seq,SEQUENCE(ROWS(Arr)), LOOKUP(Seq,IF(LEN(Arr),Seq),Arr))
