使用行号的数组公式中的连接

Question

更新-修改答案

在@player0 0的帮助下，找到了处理行分隔器的解决方案：

=ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(SPLIT(TRANSPOSE(SPLIT(QUERY( IF(D1:D="[task]", "♦"&D1:D, "♣♀"&D1:D),,999^99), "♦", )), "♀")),,999^99)),"\s*♣\s*",char(10)))

这会将每个任务部分移动到自己的单元格中，保留所有其他格式，并停止在每个单元格内容周围添加额外的空格，这些单元格内容被连接/连接在一起。

原邮政

似乎找不到其他地方这样做了，所以也很高兴在评论中的任何链接:)

我有一列文本，包含超过100个文件的内容，每个文件顶部都有[task]。

超过50,000个字符，因此它们粘贴到每个单元格的换行符中(“粘贴为值”)。

我可以很容易地告诉要使用的行号，并且有一个可拖的工作公式，只是不能正确地获得支持ARRAYFORMULA的，因为输入可能会很容易地更改一千行，因此需要优雅地处理。

公式只请，没有代码

示例电子表格 https://docs.google.com/spreadsheets/d/1yaETa3Pvq47DpcAyojSlf6p5N1Tt_hpvoqp7WEtC6pI/edit?usp=sharing

示例输入(50,000+字符) https://drive.google.com/file/d/1sMl0ZQfbXZUjpB0fic2VkX5Ze5H_IEGL/view?usp=sharing

注:如果在我清理驱动器时删除了输入文件，只需复制粘贴下面的示例直到50,000+字符，或查看链接电子表格。

输入

[task]
SpriteNE = floor_metal_01.dds
SpriteSE = floor_metal_01.dds
SpriteSW = floor_metal_01.dds
SpriteNW = floor_metal_01.dds
guilevel = 1
guiindex = 10
type = COMPONENT
locked = 0
name = category_advanced_manufacturing
parent

[task]
SpriteNE = floor_metal_01.dds
SpriteSE = floor_metal_01.dds
SpriteSW = floor_metal_01.dds
SpriteNW = floor_metal_01.dds
guilevel = 1
guiindex = 9
type = COMPONENT
locked = 0
name = category_facilities
parent

[task]
tilelable
SpriteNE = conveyor_NE.dds
SpriteSE = conveyor_SE.dds
SpriteSW = conveyor_SW.dds
SpriteNW = conveyor_NW.dds
exitdirectionoffsets = 0
inputdirectionoffsets = 2
guilevel = 1
parent
guiindex = 0.5
name = task_conveyor
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_SE.dds

[task]
tilelable
SpriteNE = conveyor_converge3_NE.dds
SpriteSE = conveyor_converge3_SE.dds
SpriteSW = conveyor_converge3_SW.dds
SpriteNW = conveyor_converge3_NW.dds
exitdirectionoffsets = 0,
inputdirectionoffsets = 1,2,3
guilevel = -1
parent
guiindex = 1
name = task_conveyor_converge3
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_converge3_SE.dds

[task]
tilelable
SpriteNE = conveyor_crossover_NE.dds
SpriteSE = conveyor_crossover_SE.dds
SpriteSW = conveyor_crossover_SW.dds
SpriteNW = conveyor_crossover_NW.dds
exitdirectionoffsets = 0,3
inputdirectionoffsets = 1,2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_crossover
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_crossover_SE.dds

[task]
tilelable
SpriteNE = conveyor_left_NE.dds
SpriteSE = conveyor_left_SE.dds
SpriteSW = conveyor_left_SW.dds
SpriteNW = conveyor_left_NW.dds
exitdirectionoffsets = 3
inputdirectionoffsets = 2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_left
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_left_SE.dds

[task]
tilelable
SpriteNE = conveyor_right_NE.dds
SpriteSE = conveyor_right_SE.dds
SpriteSW = conveyor_right_SW.dds
SpriteNW = conveyor_right_NW.dds
exitdirectionoffsets = 1
inputdirectionoffsets = 2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_right
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_right_SE.dds

[task]
tilelable
SpriteNE = conveyor_split3_NE.dds
SpriteSE = conveyor_split3_SE.dds
SpriteSW = conveyor_split3_SW.dds
SpriteNW = conveyor_split3_NW.dds
exitdirectionoffsets = 0,1,3
inputdirectionoffsets = 2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_split3
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_split3_SE.dds

[task]
tilelable
SpriteNE = conveyor_t_NE.dds
SpriteSE = conveyor_t_SE.dds
SpriteSW = conveyor_t_SW.dds
SpriteNW = conveyor_t_NW.dds
exitdirectionoffsets = 3,1
inputdirectionoffsets = 2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_t
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_t_SE.dds

[task]
tilelable
SpriteNE = conveyor_t_join_NE.dds
SpriteSE = conveyor_t_join_SE.dds
SpriteSW = conveyor_t_join_SW.dds
SpriteNW = conveyor_t_join_NW.dds
exitdirectionoffsets = 0
inputdirectionoffsets = 1,3
guilevel = -1
parent
guiindex = 1
name = task_conveyor_t_join
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_t_join_SE.dds

[task]
tilelable
SpriteNE = conveyor_t_join_left_NE.dds
SpriteSE = conveyor_t_join_left_SE.dds
SpriteSW = conveyor_t_join_left_SW.dds
SpriteNW = conveyor_t_join_left_NW.dds
exitdirectionoffsets = 0
inputdirectionoffsets = 2,3
guilevel = -1
parent
guiindex = 1
name = task_conveyor_t_join_left
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_t_join_left_SE.dds

[task]
tilelable
SpriteNE = conveyor_t_join_right_NE.dds
SpriteSE = conveyor_t_join_right_SE.dds
SpriteSW = conveyor_t_join_right_SW.dds
SpriteNW = conveyor_t_join_right_NW.dds
exitdirectionoffsets = 0
inputdirectionoffsets = 1,2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_t_join_right
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_t_join_right_SE.dds

[task]
tilelable
SpriteNE = conveyor_t_left_NE.dds
SpriteSE = conveyor_t_left_SE.dds
SpriteSW = conveyor_t_left_SW.dds
SpriteNW = conveyor_t_left_NW.dds
exitdirectionoffsets = 0,3
inputdirectionoffsets = 2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_t_left
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_t_left_SE.dds

[task]
tilelable
SpriteNE = conveyor_t_right_NE.dds
SpriteSE = conveyor_t_right_SE.dds
SpriteSW = conveyor_t_right_SW.dds
SpriteNW = conveyor_t_right_NW.dds
exitdirectionoffsets = 0,1
inputdirectionoffsets = 2
guilevel = -1
parent
guiindex = 1
name = task_conveyor_t_right
is_conveyor = 1

[components]

[resources]

[subtasksrequired]

[subtasksapplied]

[tile]
texture = conveyor_t_right_SE.dds

期望输出

每个任务.*都是一个新文件，因此将这些文件分隔成一个单元格，每个单元格位于一个列中，换行符(\n)作为内部分隔符。

换句话说，在下一个task之前，每个任务之后的所有内容都合并成一个单元。

只是必须是数组公式，可以避免像50,000个字符的连接这样的函数的字符限制。

电流输出

获取行号

$D$1:D是输入列

选择了$G$1并选择了"[task]"

$M$1 =ARRAYFORMULA(QUERY({$D1:$D, row($D1:$D)},"select * where Col1 = '"&$G$1&"'"))

$O$1 =ARRAYFORMULA(QUERY({$M1:$M,{$M2:$M;0}},"select *"))

然后我尝试用这些数字

工作，必须拖放，而且没有任何分隔符__。

=ARRAYFORMULA(CONCATENATE(INDIRECT("$D"&$O1:$O&":$D"&$P1:$P-1)))

不起作用，我一直在添加更多的数组公式，但仍然只是第一个结果

=ARRAYFORMULA(IF($P1:P<>"",CONCATENATE(ARRAYFORMULA(INDIRECT("$D"&ARRAYFORMULA(INDIRECT("$O"&IF($P1:$P<>"",row($P1:$P),"")&":$O"))&":$D"&ARRAYFORMULA(INDIRECT("$P"&IF($P1:$P<>"",row($P1:$P),"")&":$P"))-1))),""))

只是前一次间接使用的字符串

=ARRAYFORMULA(IF($O1:O<>"","$D"&ARRAYFORMULA(INDIRECT("$O"&IF($P1:$P<>"",row($P1:$P),"")&":$O"))&":$D"&ARRAYFORMULA(INDIRECT("$P"&IF($P1:$P<>"",row($P1:$P),"")&":$P"))-1,""))

不工作，有分隔符，但仍然只循环第一个结果。

$U1:U是用于间接定向的字符串列表(见上文)

=ARRAYFORMULA(IF($U1:U<>"",TEXTJOIN(";",FALSE,INDIRECT($U1:U)),""))

在示例表中还有一些其他的公式，我在继续的过程中基本上去掉了哑弹。

我在那里发现了很多有趣的资源，但似乎没有什么是针对使用行号的，甚至是只允许使用ARRAYFORMULA (并通过单次连接或类似调用避免了50,000个字符限制的问题)的拆分+连接类型方法。

Answer 1

尝试：

=ARRAYFORMULA(SPLIT(TRANSPOSE(SPLIT(QUERY(
 IF(A2:A="[task]", "♦"&A2:A, "♀"&A2:A),,999^99), "♦", )), "♀"))

​

​

Answer 2

这是我在注释中说过的示例，您只需在单元格中调用函数"=GroupMacro“不幸的是，这不能是自动刷新：

var rtrn=[];
var rslt=[];
var rslt2='';

function GroupMacro() {
  var spreadsheet = SpreadsheetApp.getActive();
  var bomo=spreadsheet.getRange('D1:D' + spreadsheet.getLastRow()).getValues();
  for (a=0; a<bomo.length; a++)
  {
    if(bomo[a]=='test') //this is separator, this can be applied as parameter in function
    {
      if(a>0)
      {
         rslt2=rslt.join();
         rtrn.push(rslt2);
         rslt=[];
      }
      rslt.push(bomo[a]);
    }
    else
    {
      rslt.push(bomo[a]);
    }
  }
  rslt2=rslt.join();
  rtrn.push(rslt2);
  return rtrn;
};