I do understand the title may be slightly confusing since I don't exactly know how to describe what I am attempting to do.
我知道这个标题可能有点让人困惑，因为我不知道如何描述我正在尝试做的事情。
This is the problem: I have an .xls file that looks like this:
这就是问题所在:我有一个.xls文件，它看起来是这样的:
This is my current excel file:
这是我目前的excel文件:
It's a long, long list with song numbers and the biblebooks they are referring to. So, for instance, in verse 1, the reference is Gen. 12:1-9. In verse 2, two biblebooks are referenced, namely 1 Kor. 2:9-15, and Ef. 4:1-6. If the abbreviations don't make sense to you it's because they're in Dutch.
这是一长串有歌曲编号和他们所指的生物书籍的长名单。例如，在第1节中，提到的是gen。12:1-9。在第2节，两本圣经被引用，即1 Kor. 2:9-15和Ef. 4:1-6。如果这些缩写对你没有意义，那是因为它们是荷兰语。
I want it to look like this:
我想让它看起来像这样:
I want the biblebooks to be organised according to what verses they are references in instead of the other way around. I want to see which verses are referenced to in what biblebooks (see the picture).
我希望圣经按照它们所引用的诗句来组织，而不是反过来。我想看看哪几首诗被引用到哪本圣经中(见图)。
Some of the biblebooks are referenced to in multiple songs (as you can see in the picture): Psalm 90 is referenced to in verse 19 ánd 20.
一些生物书籍在多首歌中被引用(正如你在图片中看到的):诗篇90是在第19和20节提到的。
I thought this to be simple but as it turns out it's trickier than I'd hoped it would be. I did promise someone that I would do this (and I thought it'd be a simple matter of transposing the columns and rows) but I've been going at it for about an hour now, with the help of Google, and I'm still getting nowhere.
我认为这很简单，但结果却比我希望的要复杂。我的确向别人保证过我会这么做(我还以为这只是简单地改变列和行)，但在谷歌的帮助下，我已经做了大约一个小时了，但我还是一无所获。
So any help would be appreciated! :)
因此，任何帮助都将受到感激!:)
1 个解决方案
#1
0
From your original worksheet, select the range you want and copy it: 
从你的原始工作表中，选择你想要的范围并复制它:
Then transpose it into a new sheet:
然后将其转置到一个新表中:
Hit Alt + D + P to bring up the Pivot Table Menu and select "Multiple Consolidation Ranges" then click "Next":
按Alt + D + P打开Pivot表菜单，选择“多重整合范围”，点击“Next”:
Select "I will create the page fields" and click "Next":
选择“我将创建页面字段”，点击“Next”:
Select the entire transposed range and click "Add" then click "Next":
选择整个转置范围，点击“添加”，点击“下一步”:
Click "Finish" to create the Pivot Table in a new worksheet:
单击“Finish”，在新的工作表中创建Pivot表:
Double-click on the grand total cell of the Pivot Table to un-pivot the data:
双击数据透视表的总单元格，使数据反透视:
Your un-pivoted table will now appear on a new tab:
您的未旋转的表现在将出现在一个新的选项卡上:
Filter out the blanks and your data is now converted:
过滤掉空格，你的数据现在被转换为:
#1
0
From your original worksheet, select the range you want and copy it:
从你的原始工作表中，选择你想要的范围并复制它:
Then transpose it into a new sheet:
然后将其转置到一个新表中:
Hit Alt + D + P to bring up the Pivot Table Menu and select "Multiple Consolidation Ranges" then click "Next":
按Alt + D + P打开Pivot表菜单，选择“多重整合范围”，点击“Next”:
Select "I will create the page fields" and click "Next":
选择“我将创建页面字段”，点击“Next”:
Select the entire transposed range and click "Add" then click "Next":
选择整个转置范围，点击“添加”，点击“下一步”:
Click "Finish" to create the Pivot Table in a new worksheet:
单击“Finish”，在新的工作表中创建Pivot表:
Double-click on the grand total cell of the Pivot Table to un-pivot the data:
双击数据透视表的总单元格，使数据反透视:
Your un-pivoted table will now appear on a new tab:
您的未旋转的表现在将出现在一个新的选项卡上:
Filter out the blanks and your data is now converted:
过滤掉空格，你的数据现在被转换为: