Google表格将2D数组连接到单个列中，并同时进行排序 [英] Google Sheets concatenate a 2d array into a single column and sort simultaneously

问题描述

我想知道是否有可能，以及如何将8列和300多个行的数组转换为排序的，连接的一维数组，其中每一行都是8个内容的并置列.我还想使用一个公式来实现这一点.

I want to know if it is possible and if so how I can achieve converting an array of 8 columns and 300+ rows into a sorted, concatenated, one-dimensional array where each row is the concatenation of the contents in the 8 columns. I would also like to achieve this using a single formula.

示例:

leg | dog | tom | jon | bar |     |     |     |
foo | bin | git | hub | bet | far | day | bin |
...

将转换为:

bar dog jon leg tom
bet bin bin day far foo git hub
...

我可以使用以下命令实现这一点:

I can achieve this for a single row using this:

=arrayformula(CONCATENATE(transpose(sort(transpose(F2:M2),1,1))&" "))

只要8列从F到M然后，我可以将该公式复制300多次，这很容易做到，但是我想要一个填充 n 行数的公式.

as long as the 8 columns are from F to M I can then copy this formula down 300+ times which is easy to do but I would like a single formula that populates n number of rows.

这可以实现吗，还是我必须将公式复制下来?

Can this be achieved or do I have to copy the formula down?

推荐答案

应该执行以下操作:

=transpose(split(" "&join(" ",index(sort(arrayformula({row(A1:A300),len(A1:A300)*0-9E+99
                                                      ;row(A1:A300),A1:A300
                                                      ;row(A1:A300),B1:B300
                                                      ;row(A1:A300),C1:C300
                                                      ;row(A1:A300),D1:D300
                                                      ;row(A1:A300),E1:E300
                                                      ;row(A1:A300),F1:F300
                                                      ;row(A1:A300),G1:G300
                                                      ;row(A1:A300),H1:H300
                                                      })),0,2))," "&-9E+99&" ",false))

首先，它创建一个二维数组，其中第一行的原始行号为第二行，每个单元格的第二行为值(为每行添加一个新值-9e99)，然后对该数组进行排序，舍弃第一列，所有值都使用空格连接在一起，然后拆分(由空格包围的附加值)，最后进行转置.

First it creates a two-dimensional array with original row number in the first column and value in the second for each cell (adding a new value -9e99 for each row), then the array is sorted, first column is discarded, all values are joined using a space, then split (by the added value surrounded by spaces), and finally transposed.

这篇关于Google表格将2D数组连接到单个列中，并同时进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！