将单列转置为多行 [英] Transpose a single column to multiple rows
问题描述
我有一列数据，大约有800条记录.每18个单元格是一个记录.我需要一个脚本，该脚本需要每18个单元格动态地将它们转置为一行，甚至需要一种编写excel公式以将每18条记录从列转置为一行的方法.
I have one column of data with approximately 800 records. Each 18 cells is one record. I need a script that takes every 18 cells and transposes them to a row dynamically, or even a way to write excel formula to transpose every 18 records from the column to a row.
推荐答案
您可能会被问到您尝试了什么"，这是因为您最好对它进行重击并询问何时卡住，而不是要求完整的解决方案.话虽如此，我可以为您提供一些入门的提示.
You'll probably get asked "what have you tried" since it's preferred that you have a bash at it and ask when you get stuck rather than asking for a complete solution. That having been said, I can offer you some tips to get you started.
要在VBA中执行此操作，您需要执行一个循环，该循环从第一个单元格开始，获取由该单元格和随后的18行组成的范围，然后将其粘贴到(可能是)工作簿的另一张工作表中，从一列到一行.然后循环将偏移19个像元并重新开始.
To do this in VBA you need to perform a loop which starts at the first cell, gets a range consisting of that cell and the following 18 rows, then pastes that into (probably) another sheet in the workbook, transposing it from a column to a row. The loop will then offset 19 cells and start again.
Take a look at the answer that I gave in this thread: Unable to write a Macro for Excel VBA to copy a specific cell value to the next blank rows
这将为您提供执行与您的循环有点类似的循环的语法.
That will give you the syntax for doing a loop which is somewhat similar to yours.
要获取进行粘贴/转置的语法，请手动执行并使用宏记录器记录您的操作.可以在开发人员"选项卡上找到.(如果看不到开发人员"选项卡，请转到文件"->选项"->自定义功能区"，然后在此处打开开发人员"选项卡.)
To get the syntax for doing a paste / transpose, do it manually and record your actions using the macro recorder. That can be found on the Developer tab. (If you don't have the Developer tab visible, go to File -> Options -> Customise Ribbon and turn on the Developer tab there.)
尝试一下，如果发现不能完全发挥作用，只需再次张贴您遇到困难的位置的详细信息即可.
Give it a try, and if you find that you can't quite make it work just post again with the details of where you're getting stuck.
这篇关于将单列转置为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
