将垂直表中的数据更改为单个行 [英] Change data in vertical table to individual rows

问题描述

我有一个Excel工作簿，格式如下：

I have an Excel workbook with the following format:

 | A  | B     | C     | D     | E     |   
 |----|-------|-------|-------|-------|  
1|    | po#1  | po#2  | po#3  | po#1  |
2|    | date1 | date2 | date3 | date4 |
3|sku1| qty1  | qty4  | qty7  | qty10 | 
4|sku2| qty2  | qty5  | qty8  | qty11 |
5|sku3| qty3  | qty6  | qty9  | qty12 |

我需要转换为以下格式：

that I need to convert to the following format:

  | A      | B     | C     | D     |
  |--------|-------|-------|-------|
 1|  po#1  | date1 | sku1  | qty1  |
 2|  po#1  | date1 | sku2  | qty2  |
 3|  po#1  | date1 | sku3  | qty3  |
 4|  po#1  | date4 | sku1  | qty10 |
 5|  po#1  | date4 | sku2  | qty11 |
 6|  po#1  | date4 | sku3  | qty12 |
 7|  po#2  | date2 | sku1  | qty4  |
 8|  po#2  | date2 | sku2  | qty5  |
 9|  po#2  | date2 | sku3  | qty6  |
10|  po#3  | date3 | sku1  | qty7  |
11|  po#3  | date3 | sku2  | qty8  |
12|  po#3  | date3 | sku3  | qty9  |

不使用VBA。

推荐答案

在副本上，删除Row2并应用详细的反向枢轴技术（带图片！）此处。当您到达表格时，请切换行和列列的顺序，并在两者之间插入一个新列。使用HLOOKUP从您的原始数据填充（仍然有Row2）。在值上升序排序。删除标签，如果需要，将表转换为范围。

On a copy, delete Row2 and apply the reverse pivot technique detailed (with pictures!) here. When you get to the Table, switch the order of the Row and Column columns and insert a new column between the two. Populate this with an HLOOKUP from your original data (that still has Row2). Sort ascending on Column Value. Delete the labels and, if desired, convert Table to Range.

编辑以上地址是早期版本的OP。为了解决当前版本，可以使用诸如 = A1&|​​& A2 的公式的结果来替换Row1和2（基于A1和A2的内容在替换之前）复制，然后通过搜索 | 通过文本操作在表中再次拆分它们。

Edit Above addresses an earlier version of the OP. To address the current version Rows1 and 2 could be replaced with the result of a formula such as =A1&"|"&A2 (based on the contents of A1 and A2 before replacement) with copying across to suit, then splitting these apart again in the Table by text manipulation with searching for |.

这篇关于将垂直表中的数据更改为单个行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！