英文:
Transpose data list
问题
我有一个表格，看起来像这样：
| Item# | Name | Value | Quantity |
|---|---|---|---|
| 21 | John | $345 | 3 |
| 22 | John | $21 | 1 |
| 23 | John | $323 | 4 |
| 21 | Bob | $254 | 1 |
| 22 | Bob | $34 | 2 |
| 23 | Bob | $400 | 4 |
| 21 | Suzy | $254 | 9 |
| 22 | Suzy | $25 | 1 |
| 23 | Suzy | $202 | 15 |
我希望将其转换为如下形式：
| Item | John | Bob | Suzy |
|---|---|---|---|
| Item# | Value | Quantity | value |
| ---------- | ------ | -------- | ------ |
| 21 | $345 | 3 | $254 |
| 22 | $21 | 1 | $34 |
| 23 | $323 | 4 | $400 |
如何使用VBA转置表格？
英文:
I have a table which looks like this:
| Item# | Name | Value | Quantity |
|---|---|---|---|
| 21 | John | $345 | 3 |
| 22 | John | $21 | 1 |
| 23 | John | $323 | 4 |
| 21 | Bob | $254 | 1 |
| 22 | Bob | $34 | 2 |
| 23 | Bob | $400 | 4 |
| 21 | Suzy | $254 | 9 |
| 22 | Suzy | $25 | 1 |
| 23 | Suzy | $202 | 15 |
and I wish to convert into something like this:
| Item | John | Bob | Suzy |
|---|---|---|---|
| Item# | Value | Quantity | value |
| ---------- | ------ | -------- | ------ |
| 21 | $345 | 3 | $254 |
| 22 | $21 | 1 | $34 |
| 23 | $323 | 4 | $400 |
How to transpose the table using VBA?
答案1
得分: 1
将光标放在数据表内的任何位置，转到“插入” > “数据透视表” > “来自表/区域” > “插入新工作表” 就可以实现你想要的效果。
右侧会打开“数据透视表字段”面板，这部分不太直观。尝试将项目拖到行，名称拖到列，值拖到值，这几乎会立刻复制你上面的示例。
这是一个功能强大的工具，具有许多秘密和隐藏的功能。你可能想在YouTube上搜索“excel数据透视表基础”或类似的内容。
英文:
I think a Pivot Table will get you exactly where you want to go. Place your cursor anywhere within your data table, go to Insert > PivotTable > From Table/Range > Insert New Sheet.
The "PivotTable Fields" panel that opens on the right is not super-intuitive. Try dragging Item onto Rows and name onto Columns, and Value onto Values. This will almost replicate your sample above, instantly.
This is an incredibly powerful tool with many secret and hidden capabilities. You might want to search youTube for "excel pivot table essentials" or similar.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论