有时，您可能希望将一列中的数据转换为Excel中的组织数据。当您将无组织的数据从文字编辑器复制粘贴到Excel 工作^(Excel)表时，尤其如此。在这种情况下，所有数据都编译在一行中。

^{(Convert Columns)}使用公式将^(Formulas)Excel中的列转换为行^(Rows)

虽然您可以使用Transpose 函数轻松地将已排列的工作簿或其中的一部分从行转换为列，但当数据无组织并分布在单个列中时，情况并非如此。

如果无组织的数据也没有模式，您可能首先需要整理出一个模式。但是，通常会以某种模式向您呈现无组织的数据。

例如。让我们假设这样一种情况，您有一个按顺序排列在单个列中的数据列表Name、DOB和Grade。

Karan

01/06/1987

A

Justin

09/08/1964

D

Bob

04/05/1996

B

Jason

08/09/1984

C

在这种情况下，您可能希望按Name、DOB和Grade跨行和列的顺序组织数据，而不仅仅是单个列。这可以使用OFFSET公式来完成。OFFSET公式的语法将变为：

OFFSET($A$1,(ROW()-f_row)*rows_in_set+INT((COLUMN()-f_col)/col_in_set), MOD(COLUMN()-f_col,col_in_set))

正如上面提到的示例中所解释的，如果信息从 A1 开始分布在 A 列中，则公式将变为：

=OFFSET($A$1,(ROW()-2)*3+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

虽然此公式将信息划分为 3 行，但列数并不固定。列数取决于数据的长度。如果数据中有任何空白列，则将其替换为 0。

假设^(Suppose)您需要从 C2 开始排列这些数据，并且知道数据要跨 3 行组织，请在单元格 C2 中输入此公式。然后将公式拉到 3 行并向下拉到列，直到您开始获得 0 作为条目。

组织好数据后，您可以在顶部提及小标题并将其剪切粘贴到正确的位置。

希望能帮助到你！

How to convert Columns to Rows in Excel with Formulas

At times, you may want to convert data in one column to organized data in Excel. This is especially the сase when уou copy-рaste unorganized data from a word editor to an Excel sheet. In such a case, all the data is compіled across а single row.

Convert Columns to Rows in Excel with Formulas

While you can easily convert an arranged workbook or part of it from rows to columns using the Transpose function, that isn’t the case when the data is unorganized and spread across a single column.

If the unorganized data doesn’t have a pattern either, you might first need to sort out a pattern. However, usually, you are presented with unorganized data in a pattern.

Eg. Let us assume a case where you have a list of data organized across a single column in the sequence Name, DOB, and Grade.

Karan

01/06/1987

A

Justin

09/08/1964

D

Bob

04/05/1996

B

Jason

08/09/1984

C

In this case, you may want to organize the data in the sequence of Name, DOB, and Grade across rows and columns and not just a single column. This could be done using the OFFSET formula. The syntax for the OFFSET formula would become:

OFFSET($A$1,(ROW()-f_row)*rows_in_set+INT((COLUMN()-f_col)/col_in_set), MOD(COLUMN()-f_col,col_in_set))

As explained in the example mentioned above, if the information is spread across column A starting A1, the formula would become:

=OFFSET($A$1,(ROW()-2)*3+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

While this formula divides the information across the 3 rows, the number of columns isn’t fixed. The number of columns depends on the length of the data. In the case of any blank column across the data, it is replaced by a 0.

Suppose you need this data arranged starting C2 and are aware that the data is to be organized across 3 rows, enter this formula in cell C2. Then pull the formula across 3 rows and down across the columns till you start getting 0 as entries.

Once the data is organized, you can mention the subheadings on the top and cut-paste it at the right location.

Hope it helps!

Related posts

• 如何申请Color在alternate Rows or Columns在Excel

• 如何在Excel中切换Rows and Columns

• 如何在Excel中交换Columns or Rows [逐步]

• 如何在 Excel 中隐藏工作表、单元格、列和公式

• 如何在 Excel 中冻结行和列

• 如何在Excel formula中锁定单元格以保护它们

• 如何在一页上打印Excel or Google Sheets中所选单元格

• 如何在Google纸上隐藏Rows and Columns

• 在 Excel 工作表中对行和列进行分组

• 如何在Excel中使用Percentile.Exc function

• 如何在Excel中停止或关闭Workbook Sharing

• Microsoft Excel正试图恢复您的信息

• 如何在Microsoft Excel中使用MID and MIDB function

• 如何在Excel工作表中Freeze and Split Panes

• 如何在Excel中使用Rept Function

• 如何在Excel中使用INT and LCM Functions

• 如何修复损坏的Excel Workbook

• 如何删除 Excel 中的重复行

• 如何在Excel键入时展示或隐藏Formulas的List

• 如何计算在Excel的Yes or No entries数