在Excel 2007中将具有数据列的行转换为具有多行的列 [英] Convert row with columns of data into column with multiple rows in Excel 2007

问题描述

我有一行数据如下：

            header1      header2      header3      header4      header5
row key     datavalue1   datavalue2   datavalue3   datavalue4   datavalue5....

所以基本上，我有一个非规范化数据集，其中数据库可能或可能不是逐行为空。我需要对它们进行规范化。

so basically, I have a denormalized data set where the datavalues may or may not be empty on a row-by-row basis. I need to normalize them.

ie

12345678    NULL         10           3            NULL         14

将成为：

12345678   header2   10
12345678   header3   3
12345678   header5   14

$ b $我可以通过使用粘贴特殊的变换来做到这一点，但是我有数千行，我需要确保我为每个行获取正确的行键。此外，每一行都有一堆与之相关联的描述，我需要与每个数据库复制。

I could do this by using a paste special transform, but I have thousands of rows and I'd need to make sure that I get the right row key for each. furthermore, each row has a bunch of descriptives associated with it that I need copied over with each datavalue.

什么是最简单的方法来转换每一列的列，使我有多行单列具有所有非空数据空间加上相关联的数据库参考？我需要能够转动数据集。

What is the easiest way to convert each row of columns such that I have multiple rows of a single column with all non-empty datavalues plus the associated datavalue reference? I need to be able to pivot the dataset.

推荐答案

如果您有五个标题列，请输入这些公式

If you have five "header" columns, enter these formulas

H1: =OFFSET($A$1,INT((ROW()-1)/5)+1,0)
I1: =OFFSET($A$1,0,IF(MOD(ROW(),5)=0,5,MOD(ROW(),5)))
J1: =INDEX($A$1:$F$9,MATCH(H1,$A$1:$A$9,FALSE),MATCH(I1,$A$1:$F$1,FALSE))

复制H1：J ??并在顶部粘贴特殊值。对列J进行排序，并删除零值。如果您在数据中有合法的零，那么您首先需要用一些唯一的字符串替换空白单元格，然后再删除。

Copy H1:J?? and paste special values over the top. Sort on column J and delete anything that's a zero. If you have legitmate zeros in the data, then you first need to replace blank cells with some unique string that you can then delete later.

如果您有更多的列，那么将所有上述公式中的'5'替换成您拥有的任何数字。

If you have more columns, then replace the '5' in all the above formulas with whatever number you have.

这篇关于在Excel 2007中将具有数据列的行转换为具有多行的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！