将多列中特定列的数据转换为另一张“摘要"表中的行 [英] Transpose data from a specific column from multiple sheets to rows on another 'summary' sheet

问题描述

我有79个数据表，它们都是相同的格式.

I have 79 sheets of data which are all the same format.

我想将数据从列 C6:C34复制到摘要表"中的行中.

I would like to copy data from a Column C6:C34 in to a Row in a 'Summary Sheet'.

我已成功使用此

I have been successful in using the formulas mentioned in this Stackoverflow question here, which suggests

=TRANSPOSE('worksheet A'!A1:A10)

和

=INDEX(A!$A$1:$A$10, COLUMN())

但据我所知，我必须在2013-14年度执行79次此功能，然后在2014-15年度再执行79次，依此类推.

But as far as I can work out, I have to perform the function 79 times for the 2013-14 year, then another 79 times for the 2014-15 year, and so on.

有没有一种方法可以将Sheet1中C6:C34中的数据提取到Sheet79中并将其转置到D2:AF2行至D80:AF80行中?我非常懂计算机，但是没有太多使用宏的经验(阅读:无).

Is there a way that I can take the data from C6:C34 in Sheet1 to Sheet79 and transpose it in to rows D2:AF2 to D80:AF80? I'm pretty computer literate, but haven't got much experience with using macros (read:none).

任何帮助将不胜感激！

推荐答案

经过数小时的尝试找到解决方法之后，我弄清楚了如何将其作为公式来进行(这非常简单).

After several hours of trying to find a workaround, I worked out how to do this as a formula (and it was stupidly easy).

我仔细阅读了以下内容，并拖动了该行，使 = TRANSPOSE('worksheet A'！A1:A10)公式分别向下复制到79行中的每行中，我选择的行与我要从其转置的工作表中原始列中的单元格数量匹配，将相关公式复制到公式框中，然后按 ctrl + shift +输入.

I went through and copied the =TRANSPOSE('worksheet A'!A1:A10) formula down each of the 79 rows separately, by dragging across the row so that the number of cells in the row I selected matched the number of cells in the original column in the sheet I was transposing from, copying the relevant formula in to the formula box, then pressing ctrl + shift+ enter.

在我完成了2014/15年度摘要电子表格的全部内容后，我将其复制到2015/16年度摘要的标题相同的地方.这样做时，我意识到在复制数据时已经更新了数据，因此我将C6:C34的所有内容替换为" D6:D34(在这种情况下，这是每个文件的2015/16列中的数据)79个电子表格)，并且更新后无需我做任何其他事情！

After I had completed the entire summary spreadsheet for 2014/15, I copied it over so that I had the same headings for the 2015/16 summary. In doing so, I realised that the data had updated when I copied it over, so I did a "replace all" of C6:C34 to D6:D34 (which in this case is the data in the 2015/16 column of each of the 79 spreadsheets) and it updated without me having to do anything else!

虽然我仍然需要花一些时间将数据转移到第一张摘要表中，但是对于像我这样对宏不是很有经验的人来说，这可能是一个很好的选择.

While I still had to spend some time transposing the data to the first summary sheet, this may be a good alternative for those who, like me, aren't very experienced at macros.

如果您确实知道如何使用宏，上面还有一个很好的替代答案.

There is a great alternative answer above though if you do know how to use macros.

这篇关于将多列中特定列的数据转换为另一张“摘要"表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！