Excel：用于在列/行/矩阵之间转换数据的公式 [英] Excel: Formulas for converting data among column / row / matrix

问题描述

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

更复杂的情况如何：重新形成宽度为W的矩阵宽度N * W？ p>

有一些类似或相关的问题。
我回答了一些，标有*。
我不断更新此列表，因为添加了新的类似（或相同）问题：

。

5/6。行到列/矩阵

很容易获得这些行
例如， col_data_top 包含

  = OFFSET（row_data_left，0，ROW（） -  ROW（col_data_top））
  

并复制。

7。矩阵转置

要获取 matrix_data3 （图中未显示）矩阵数据2 的转置，只需使用 matrix_data3_top_left ，公式为

  = OFFSET（matrix_data2_top_left，COLUMN（） -  COLUMN（matrix_data3_top_left），ROW -ROW（matrix_data3_top_left））
  

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

8。矩阵重塑

我们要将矩阵重新形成一个更广泛的矩阵：
matrix_data4 行和 M4 列（ width4 ）转换为
matrix_data5 ，其中 N5 = 行和 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）），宽度4））
  

现在我们要将一个矩阵重新形成一个较窄的矩阵：
< > matrix_data4 ，将 N4 行和 M4 列（ width4 ）转换为
matrix_data6 ， N6 N6xS 行和 M6 = M4 / S 列（ width6 ），与 S （ split6 ）分割数
（图中未显示的矩阵）然后我们e

  = 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）））
  

Are there formulas to convert data in a column to a matrix or to a row? And to convert from/to other combinations?

What about an even more complex case: reshape a matrix of width W to width N*W?

There are a few similar or related questions. I have answered some of them, marked with *. I keep updating this list, as new similar (or equal) questions are added:

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 *

Some of the answers appear to be "upgradeable" to something more encompassing. Is that possible?

Sample formats to convert from/to are:

Column

1
2
3
4
5
6
7
...

Row

1   2   3   4   5   6   7   ...

Matrix (with a span of 4 columns here)

1   2   3   4
5   6   7   8
...

解决方案

The idea is to give here something that can likely be used with minor adaptations to the questions listed above, which may also serve as a reference for future related questions.

The essential functions to be used are INDEX or OFFSET. The pros and cons of each one will be given after explicit examples, with reference to the figure. It shows several ranges with their defined names (in italics in the following). All defined names can be replaced by direct absolute references to the corresponding cells.

1. Column to matrix

The span (in C1) gives the number of columns. Then matrix_data_top_left (D1 here) contains

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

which is then copied into the rest of matrix_data. Note that copying also into D5 gives an error, since the resulting formula refers to a cell outside col_data (A1:A16). The same result is obtained in matrix_data2_top_left (I1) with

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

and copying similarly into matrix_data2. Note that copying also into I5 returns 0, not an error.

OFFSET has the advantage of requiring only one cell to be used as a base reference (col_data_top), so extending the source data range with further data does not need redefining the source data range in the formula, one has only to copy-paste into an extended target range. On the other hand, extending the source data range using INDEX requires first updating it in the formula (changing the range if used explicitly), and then copy-paste into an extended target range. Using a defined name is more versatile for this purpose, as redefining col_data suffices here (and it can be done after extending the target range). Due to this same property, INDEX provides a kind of automatic bounds checking on the source range, which OFFSET does not.

2. Matrix to column

col_data2_top contains

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

and col_data3_top

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

Both formulas are copied downwards. The same differences between INDEX and OFFSET exist.

3. Matrix to row

Since OFFSET does not give errors, the remaining formulas will use it. Adapting for INDEX along the lines shown above is easy. row_data_left contains

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

then copied to the right.

4. Column to row

row_data2_left contains

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

again copied to the right.

PS: The formula =TRANSPOSE(... works for this case, and it should be entered as an array formula (with ctrl+shift+enter). Nevertheles, it might be desirable to avoid array formulas.

5/6. Row to column/matrix

It is very easy to obtain along these lines. E.g., col_data_top contains

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

and copy down.

7. Matrix transpose

To get in matrix_data3 (not shown in the fig.) the transpose of matrix_data2, one only needs to use matrix_data3_top_left, with the formula

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

and copied to a suitable target range.

8. Matrix reshape

We want to reshape a matrix into a wider one: matrix_data4, with N4 rows and M4 columns (width4), into matrix_data5, with N5=N4/R rows and M5=M4xR columns (width5), with R (rep5) the number of repeats (matrices not shown in the fig.) Then use

=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))

Now we want to reshape a matrix into a narrower one: matrix_data4, with N4 rows and M4 columns (width4), into matrix_data6, with N6=N4xS rows and M6=M4/S columns (width6), with S (split6) the number of splits (matrices not shown in the fig.) Then use

=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)))

这篇关于Excel：用于在列/行/矩阵之间转换数据的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！