如何根据Google中的数字和特定文本将多行数据组合成一行？

Question

我正在为交易目的创建一本日记。在交易中，假设我以1美元买下了迪士尼的+1股，以2美元卖出了迪士尼的1股。现在这种交易将显示在两行中，一行代表1%的迪斯尼股票是以1美元的价格购买的，而下一行则是交易退出的方式--迪士尼的1股以2美元的价格出售。现在，我想把贸易进入行和贸易退出行的数据合并成一行，这样它就可以同时显示进入价格和退出价格，以及它们各自在一排中的次数。

我需要所需的行来显示进入时间、入口价格和退出时间以及不同行的退出价格。此外，时间和价格需要在多个入口或出口的情况下平均。

这对我来说有点复杂，如果你们能帮忙，我会很感激的。

第一个表是原始数据。

​

​

第二个表是所需的输出。

​

​

Answer 1

首先，我们来修理一下.在数据示例中，您提供了一个单元格A11中的错误，其中年份读取222而不是2022。

​

​

下一个..。理论上，下面的公式可以工作，但不建议使用它，因为数据集越大，它的性能就会影响到您的电子表格。尽管如此，对于您提供的数据示例来说，它是有效的：

={"Date","Entry Time","Exit Time","Company","Entry Price","Exit Price","Size";
 ARRAYFORMULA(QUERY(QUERY({A2:E, IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0))), 
 (IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), 
 IF(NOT(REGEXMATCH(TO_TEXT(D2:D), "-"))*COUNTIFS(A2:A&C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), A2:A&C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), ROW(X2:X), "<="&ROW(X2:X)), {B2:B, E2:E}, ),  
 (IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))<1)*REGEXMATCH(TO_TEXT(D2:D), "-"), 
 IF(REGEXMATCH(TO_TEXT(D2:D), "-")*COUNTIFS(A2:A&C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))<1)*REGEXMATCH(TO_TEXT(D2:D), "-"), A2:A&C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))<1)*REGEXMATCH(TO_TEXT(D2:D), "-"), ROW(X2:X), "<="&ROW(X2:X)), {B2:B, E2:E}, ), 
 A2:A&C2:C&VLOOKUP(ROW(X2:X), IF(
 IF(A2:A="",,IF(VLOOKUP(C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X), "<="&ROW(X2:X))-1, 
 {SPLIT(UNIQUE(C2:C)&"×0♦0", "♦"); C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X),"<="&ROW(X2:X)), IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))}, 2, )<>0,,
 IF(NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), COUNTIFS(C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), ROW(X2:X), "<="&ROW(X2:X)), )))<>"", {ROW(X2:X), 
 IF(A2:A="",,IF(VLOOKUP(C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X), "<="&ROW(X2:X))-1, 
 {SPLIT(UNIQUE(C2:C)&"×0♦0", "♦"); C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X), "<="&ROW(X2:X)), (IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-"))}, 2, )<>0,,
 IF(NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), COUNTIFS(C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), C2:C&(IFERROR(1*(IFERROR(ROW(A2:A)/0)&MMULT(1*TRANSPOSE(IF((TRANSPOSE(ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))>=ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>"")))))*(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>"")))=TRANSPOSE(  INDIRECT("C2:C"&MAX(ROW(A2:A)*(A2:A<>"")))&  INDIRECT("A2:A"&MAX(ROW(A2:A)*(A2:A<>""))))),   INDIRECT("D2:D"&MAX(ROW(A2:A)*(A2:A<>""))), 0)), ROW(  INDIRECT("X2:X"&MAX(ROW(A2:A)*(A2:A<>""))))^0)))>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), ROW(X2:X), "<="&ROW(X2:X)), )))}), 2)}, 
 "select Col1,avg(Col8),avg(Col11),Col3,avg(Col9),avg(Col12),count(Col11) 
  where Col1 is not null 
  group by Col1,Col3,Col13"), "offset 1", ))}

​

​

相反，最好使用一个助手列(G列)和这个公式：

={"Date","Entry Time","Exit Time","Company","Entry Price","Exit Price","Size";
 ARRAYFORMULA(QUERY(QUERY({A2:E, G2:G, 
 (G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), 
 IF(NOT(REGEXMATCH(TO_TEXT(D2:D), "-"))*COUNTIFS(A2:A&C2:C&(G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), A2:A&C2:C&(G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), ROW(X2:X), "<="&ROW(X2:X)), {B2:B, E2:E}, ),  
 (G2:G<1)*REGEXMATCH(TO_TEXT(D2:D), "-"), 
 IF(REGEXMATCH(TO_TEXT(D2:D), "-")*COUNTIFS(A2:A&C2:C&(G2:G<1)*REGEXMATCH(TO_TEXT(D2:D), "-"), A2:A&C2:C&(G2:G<1)*REGEXMATCH(TO_TEXT(D2:D), "-"), ROW(X2:X), "<="&ROW(X2:X)), {B2:B, E2:E}, ), 
 A2:A&C2:C&VLOOKUP(ROW(X2:X), IF(
 IF(A2:A="",,IF(VLOOKUP(C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X), "<="&ROW(X2:X))-1, 
 {SPLIT(UNIQUE(C2:C)&"×0♦0", "♦"); C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X),"<="&ROW(X2:X)), G2:G}, 2, )<>0,,
 IF(NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), COUNTIFS(C2:C&(G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), C2:C&(G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), ROW(X2:X), "<="&ROW(X2:X)), )))<>"", {ROW(X2:X), 
 IF(A2:A="",,IF(VLOOKUP(C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X), "<="&ROW(X2:X))-1, 
 {SPLIT(UNIQUE(C2:C)&"×0♦0", "♦"); C2:C&"×"&COUNTIFS(C2:C, C2:C, ROW(X2:X), "<="&ROW(X2:X)), (G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-"))}, 2, )<>0,,
 IF(NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), COUNTIFS(C2:C&(G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), C2:C&(G2:G>0)*NOT(REGEXMATCH(TO_TEXT(D2:D), "-")), ROW(X2:X), "<="&ROW(X2:X)), )))}), 2)}, 
 "select Col1,avg(Col8),avg(Col11),Col3,avg(Col9),avg(Col12),count(Col11) 
  where Col1 is not null 
  group by Col1,Col3,Col13"), "offset 1", ))}

​

​

重置分组功能(列N- 埃里克·泰勒帮助我完成了这里)花了一段时间，但简单地说，如果输入正确，它是一个平庸的解决方案：

=QUERY(A2:N, "select A,avg(I),avg(L),C,avg(J),avg(M),count(L) 
              where A is not null 
              group by A,C,N")

​

​

演示电子表格

Answer 2

我不知道我是否理解你需要什么，但你不能直接使用=average(C10:C11)吗？如果这是您的意思，那么您可以使用相同的公式，所有你需要的平均值。

下面是一个例子：https://docs.google.com/spreadsheets/d/1-koCscNdEBUoBo4fcB9BoDy0RrfY3r4NpZwHoFSLpN8/edit#gid=0

Answer 3

在回顾和使用查询/枢轴之后，我必须输出以下内容：

我必须准备原始数据

• 通过将时间格式化为一个数字
• 还有一个数字的位置
• 通过添加列来标识某种类型的OrderID，将买卖活动相互链接起来，这样就可以在枢轴中进行分组。我这样做，并不是完全正确的，因为当你购买额外的之前，你出售所有，这将是一个问题。
• 此外，我还添加了一个列来标识，不管它是买入还是卖出，作为一个支点来源。它可能也可以在查询中完成。

还有一个额外的事情要记住，当你卖出超过1次，这可能会在平均重量更多。因此，实际上应该有一个加权平均值，而不仅仅是平均值。

我是我的样本单

这是查询

=query(DATA!A2:G, "select F, A, C, avg(B), avg(E), max(D)
  where A is not null
  group by F, A, C
  pivot G
  order by A
  label F 'OrderID',
        A 'Date',
        C 'Company',
        avg(B) 'Time',
        avg(E) 'Price',
        max(D)  'Size'
  format avg(B) 'HH:MM:SS'
")

初步答案:计算每天的平均出口时间：

=average(query(A2:E6,"Select A where year(A) = "&year(A11)&" and month(A) = "&month(A11)-1&" and day(A) = "&day(A11)&" and C starts with '-' and B matches '"&D11&"'"))

若要计算每天的平均出口价格，请使用：

=average(query(A2:E6,"Select D where year(A) = "&year(A11)&" and month(A) = "&month(A11)-1&" and day(A) = "&day(A11)&" and C starts with '-' and B matches '"&D11&"'"))

其中“A11”是对总结日期的引用，而“D11”是指公司/股票名称。