Google表格 - 使用arrayformula将两列转换为一列（超过50,000个字符） [英] Google Sheet - Transform two columns into one column using arrayformula (more than 50,000 characters)

问题描述

我使用Google表格并寻找能够列出两​​列的数组表单并将其排列在一列中。该表包含大约5,000行，每行有超过35个字符。

我试过这个：

  = transpose（split（join（，query（transpose（B5 ：C）,, 50000）），））
  

然后我得到这个消息：

请看这里的工作表：

解释，解开公式从内部：

1. 每个值根据其行数乘以2（第二列+1） li>
   
2. 所有内容均根据此编号排序


3. 仅为结果提取第二列

I'm using Google Sheets and looking for an arrayformula that able to take a list in two columns and arrange it alternately in one column. The sheet contains about 5,000 rows, each row has more than 35 characters.

I tried this:

=transpose(split(join(" ", query(transpose(B5:C),,50000)), " "))

But then I got this message:

Please take a look at the sheet here:

https://docs.google.com/spreadsheets/d/11T1Roj1trviOSiiTZS292-4l3oODid7KLi9oGz3Z66o/edit#gid=0

解决方案

Assuming your 2 columns are A and B, this formula will "interlace" them:

=query(
  sort(
    {arrayformula({row(A1:A3)*2, A1:A3});
     arrayformula({row(B1:B3)*2+1, B1:B3})}
  ),
  "select Col2")

Explanation, unwrapping the formula from the inside:

1. Each value gets a unique number, based on its row number times 2 (+1 for the 2nd column)
2. Everything is sorted based on this number
3. Only the 2nd column is extracted for the result.

这篇关于Google表格 - 使用arrayformula将两列转换为一列（超过50,000个字符）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！