Google工作表数组公式+联接+过滤器 [英] Google sheet array formula + Join + Filter

问题描述

有人知道我如何使用arrayformula扩展join + filter吗?我需要它自动展开，而不是将公式滑动到新行中.

试图搜索此问题，但找不到适合自己的解决方案.

Does anyone know how I could expand the join+filter with arrayformula? I need it to automatically expand instead of sliding the formulae into new rows.

Tried to search this problem but couldn't find a solution for myself.

Copy to sheet itself

Problem in question highlighted in orange.

Thanks a lot!

解决方案

JOIN is not supported under ARRAYFORMULA

use in row 2:

=ARRAYFORMULA(IFNA(REGEXREPLACE(VLOOKUP(A2:A, 
 SPLIT(TRIM(FLATTEN(QUERY(TRANSPOSE(QUERY(FILTER(
 {A2:A&"×", TEXT(B2:B, "dd.mm.yyyy hh:mm:ss")&" ;", B2:B}, J2:J<>""), 
 "select Col1,max(Col2) group by Col1 pivot Col3")),,
 9^9))), "×"), 2, 0), "^ |;$", )))

这篇关于Google工作表数组公式+联接+过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！