Excel中的数据整理-重新排列列和行 [英] Data Wrangling in Excel - Rearranging Columns and Rows

问题描述

我在excel中有一个庞大的数据集.我想重新排列行和列.

I have a massive dataset in excel. I would like to rearrange the rows and columns.

这是数据的摘要，它是什么样的以及我希望它在什么样的状态下显示.

Here's a snippet of the data, what it looks like and what I'd like it to look like under.

Area, Channel, Unit,  Year1, Year2, Year3, Year4
bel,  dc,      share, 25,    36,    56,    45   
bel,  dc,      avm,   23,    45,    65,    47,
bel,  dc,      ats,   45,    2,     3,     4,
bel,  tl,      share, 25,    36,    56,    45   
bel,  tl,      avm,   23,    45,    65,    47,
bel,  tl,      ats,   45,    2,     3,     4,

我想切换单位和年份(Year1，Year2，Year3，Year4).看起来像这样

I'd like to switch Unit and the Years (Year1, Year2, Year3, Year4). To look like this

Area, Chennel, Year, Share, avm, ats
bel,  dc,      Year1, 25,    23,  45
bel,  dc,      Year2, 36,    45,  2
bel,  dc,      Year3, 56,    65,  3
bel,  dc,      Year4, 45,    47,  4
bel,  tl,      Year1, 25,    23,  45
bel,  tl,      Year2, 36,    45,  2
bel,  tl,      Year3, 56,    65,  3
bel,  tl,      Year4, 45,    47,  4 

有没有办法做到这一点?

Is there a way to achieve this?

推荐答案

有很多方法可以做这种事情-我通常使用索引匹配.

There are lots of ways to do this sort of thing - I usually use index-match.

我用索引匹配来做这种事情.

I do this sort of thing with index-match.

=INDEX($D$2:$G$4,MATCH(D$7,$C$2:$C$4,0),MATCH($C8,$D$1:$G$1,0))

首先，我将建立转置数据的位置:

First I build the spot where I am going to transpose the data:

然后我输入适当的公式:

Then I put in the proper formula:

这是它的工作方式:

在这种用法中，Index接受三个参数:一个数字范围，我想从中抓取的行和我想从中抓取的列.

In this usage Index takes three arguments: A range of numbers, the row I want to grab from and the column I want to grab from.

范围永远不会改变，因此我将其锁定在美元符号(击中f4 INDEX( $D$2:$G$4

The range never changes, so I lock that in with dollar signs (hitting f4 INDEX( $D$2:$G$4

下一个参数是我想从中获取的行.我使用match来找到它. Match(也接受三个参数:我要查找的值，我要在其中找到的范围以及一个0，以便让excel知道我的范围不正确，并且需要找到一个完全匹配.

The next argument is the row I want to grab from. I use match to find it. Match( takes three arguments also: the value I want to find, the range I am going to find it in, and a 0 to let excel know that my range is not in order, and it needs to find an exact match.

所以我的索引行是MATCH(D$7,$C$2:$C$4,0):在$D$1 -> $G$1范围内找到"Year1".我在这里使用美元符号锁定行和列，以便将公式复制到整个表格，只有需要更改的部分.

So my index row is MATCH(D$7,$C$2:$C$4,0): Find "Year1" in the range $D$1 -> $G$1. I used the dollar signs here to lock in rows and column so I can copy the formula to the whole table, and only the parts that need to change will.

这篇关于Excel中的数据整理-重新排列列和行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！