在合并数据之后，如何获得连接文本的查询？

Question

在这张表上，我一直在处理我需要的信息，我需要调整到查询结果中

=QUERY(Investors!A4:D, 
 "select B,C,sum(D) 
  where B is not null
  group by B,C
  label sum(D)''")

怎样才能把当天到期的股票编号列在D栏中？如下所示：

 1. 10/7/2019   Grady Johnson   $100.12     PT-1013
 2. 11/15/2020  Bill Jones      $553.80     PT-1020, PT-1019
 3. 11/15/2020  Grady Johnson   $45.00      PT-1011
 4. 11/15/2020  Steve Robinson  $320.00     PT-1018, PT-1016
 5. 11/17/2020  Jim Luke        $1,057.20   PT-1009, PT-1008, PT-1007, PT-1006
 6. 11/22/2020  Jim Luke        $300.43     PT-1010

下面是链接：谷歌单张

任何帮助都是非常感谢的！

Answer 1

或将其粘贴到第3行并向下拖放：

=JOIN(", ", IFNA(FILTER(Investors!A$4:A, Investors!B$4:B&" "&Investors!C$4:C=A3&" "&B3)))

​

​

Answer 2

尝试：

=ARRAYFORMULA(REGEXREPLACE(IFNA(VLOOKUP(TO_TEXT(A3:A)&" "&B3:B, 
 TRIM({TRANSPOSE(QUERY(TRANSPOSE(QUERY(QUERY({Investors!A4:C}, 
 "select Col2,Col3,count(Col2) where Col2 is not null group by Col2,Col3 pivot Col1"), 
 "select Col1,Col2 offset 1", 0)),,9^9)), 
 TRANSPOSE(QUERY(TRANSPOSE(IF(QUERY(QUERY({Investors!A4:C}, 
 "select count(Col2) where Col2 is not null group by Col2,Col3 pivot Col1"), 
 "offset 1", 0)="",,
    QUERY(QUERY({Investors!A4:A&",", Investors!B4:C},
 "select count(Col2) where Col2 is not null group by Col2,Col3 pivot Col1"), 
 "limit 0", 1))),,9^9))}), 2, 0)), ",$", ))

​

​

Answer 3

下面是我建议的公式，放在示例电子表格的组合！D3中：

=ArrayFormula(IF(A3:A="",,SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(VLOOKUP(TRANSPOSE(FILTER(Investors!A4:A,Investors!A4:A<>"")),{Investors!A:A,Investors!C:C&Investors!B:B},2,FALSE)=B3:B&A3:A,TRANSPOSE(FILTER(Investors!A4:A,Investors!A4:A<>"")),))," ",COUNTA(Investors!A4:A))))," ",", ")))

至于它是如何工作的，这将是费时的解释。(对于这样的免费站点来说，时间比合理的时间要多。)我建议，如果这对你很重要，花点时间去研究它。把它拆开，看看每一块都能做什么--分开，然后和其他部分结合--直到你把它们重新组合在一起。