Excel:用于在列/行/矩阵之间转换数据的公式

Question

是否有将列中的数据转换为矩阵或行的公式？
并从/转换为其他组合？

更复杂的情况如何:将宽度为 W 的矩阵 reshape 为宽度 N*W？

有几个类似或相关的问题。
我已经回答了其中一些，标有*。
我不断更新此列表，因为添加了新的类似(或相等)问题:

Formatting Data: Columns to Rows *

Move content from 1 column to 3 columns *

how to split one column into two columns base on conditions in EXCEL *

writing a macro to transpose 3 columns into 1 row

Excel VBA transpose with characters

Mathematical transpose in excel

How do transform a "matrix"-table to one line for each entry in excel

Convert columns with multiple rows of data to rows with multiple columns in Excel.

How to use VBA to reshape data in excel *

Sorting three columns into six, sorted horizontally by surname using excel *

divide data in one column into more column in excel

Move data from multiple columns into single row *

一些答案似乎可以“升级”到更包容的东西。
那可能吗？

要转换的示例格式是:

柱子

1
2
3
4
5
6
7
...

排

1   2   3   4   5   6   7   ...

矩阵(此处跨度为 4 列)

1   2   3   4
5   6   7   8
...

Answer 1

我们的想法是在这里给出一些可以对上面列出的问题稍作修改的东西，这也可以作为 future 相关问题的引用。

要使用的基本功能是INDEX或 OFFSET .每种方法的优缺点都会在具体例子之后，结合附图给出。它显示了几个范围及其定义的名称(以下用斜体字表示)。
所有定义的名称都可以由对相应单元格的直接绝对引用替换。



1. 列转矩阵

跨度(在 C1 中)给出了列数。然后matrix_data_top_left(这里是D1)包含

=INDEX(col_data,(ROW()-ROW(matrix_data_top_left))*span+(COLUMN()-COLUMN(matrix_data_top_left)+1),1)

然后将其复制到 matrix_data 的其余部分。
请注意，复制到 D5 也会出错，因为生成的公式引用了 col_data (A1:A16) 之外的单元格。
在 matrix_data2_top_left (I1) 中获得相同的结果

=OFFSET(col_data_top,(ROW()-ROW(matrix_data2_top_left))*span+(COLUMN()-COLUMN(matrix_data2_top_left)),0)

并类似地复制到matrix_data2中。
请注意，复制到 I5 也会返回 0 ，不是错误。
OFFSET优点是只需要一个单元格作为基础引用(col_data_top)，因此用更多数据扩展源数据范围不需要在公式中重新定义源数据范围，只需复制粘贴到扩展目标范围。
另一方面，使用 INDEX 扩展源数据范围需要首先在公式中更新它(如果明确使用则更改范围)，然后复制粘贴到扩展的目标范围中。为此目的，使用定义的名称更加通用，因为在这里重新定义 col_data 就足够了(并且可以在扩展目标范围后完成)。
由于相同的属性， INDEX提供一种对源范围的自动边界检查， OFFSET才不是。

2.矩阵到列

col_data2_top 包含

=INDEX(matrix_data2,INT((ROW()-ROW(col_data2_top))/span)+1,MOD(ROW()-ROW(col_data2_top),span)+1)

和 col_data3_top

=OFFSET(matrix_data2_top_left,INT((ROW()-ROW(col_data3_top))/span),MOD(ROW()-ROW(col_data3_top),span))

两个公式都向下复制。 INDEX 之间的相同差异和 OFFSET存在。

3.矩阵到行

由于 OFFSET不给出错误，其余公式将使用它。适应 INDEX沿着上面显示的路线很容易。
row_data_left 包含

=OFFSET(matrix_data_top_left,INT((COLUMN()-COLUMN(row_data_left))/span),MOD(COLUMN()-COLUMN(row_data_left),span))

然后复制到右边。

4. 列到行

row_data2_left 包含

=OFFSET(col_data_top,COLUMN()-COLUMN(row_data2_left),0)

再次复制到右边。

PS:公式 =TRANSPOSE(...适用于这种情况，它应该作为数组公式输入(使用 ctrl+shift+enter)。尽管如此， it might be desirable to avoid array formulas .

5/6。行到列/矩阵

沿着这些路线很容易获得。
例如，col_data_top 包含

=OFFSET(row_data_left,0,ROW()-ROW(col_data_top))

并抄下来。

7.矩阵转置

要在matrix_data3(图中未显示)中得到matrix_data2的转置，只需使用matrix_data3_top_left，公式如下

=OFFSET(matrix_data2_top_left,COLUMN()-COLUMN(matrix_data3_top_left),ROW()-ROW(matrix_data3_top_left))

并复制到合适的目标范围。

8.矩阵 reshape

我们想将一个矩阵 reshape 为一个更宽的矩阵:
matrix_data4，具有 N4 行和 M4 列(宽度 4)，进入
matrix_data5，N5=N4/R 行，M5=M4xR 列(width5)，R(rep5)是重复次数
(图中未显示的矩阵。)然后使用

=OFFSET(matrix_data4_top_left,(ROW()-ROW(matrix_data5_top_left))*rep5+INT((COLUMN()-COLUMN(matrix_data5_top_left))/width4),MOD((COLUMN()-COLUMN(matrix_data5_top_left)),width4))

现在我们想将一个矩阵 reshape 为一个更窄的矩阵:
matrix_data4，具有 N4 行和 M4 列(宽度 4)，进入
matrix_data6，其中 N6=N4xS 行和 M6=M4/S 列 (width6)，其中 S (split6) 是拆分数
(图中未显示的矩阵。)然后使用

=OFFSET(matrix_data4_top_left,INT((ROW()-ROW(matrix_data6_top_left))/split6),MOD((ROW()-ROW(matrix_data6_top_left)),split6)*width4+(COLUMN()-COLUMN(matrix_data6_top_left)))