Google Sheet - 使用 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(第 2 列 +1)
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 Sheet - 使用 arrayformula 将两列转换为一列(超过 50,000 个字符)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！