将多行转移到多个列 [英] Transpose multiple rows to multiple columns
问题描述
我在Excel电子表格中有很多数据,目前有多行。我想将其转换为具有多个列的行。
当前数据集:
名称ID图片
产品1 111 image111A.jpg
产品1 111 image111B.jpg
product 2 222 image222A.jpg
product 2 222 image222B.jpg
product 2 222 image222C.jpg
product 3 333 image333A.jpg
product 3 333 image333B.jpg
所需结果:
名称ID image1 image2 image3
产品1 111 image111A.jpg image111B.jpg
product 2 222 image222A.jpg image222B.jpg image222C.jpg
product 3 333 image333A.jpg image333B.jpg
假设您的资料从A1开始,按产品(或ID)排序
在D2中输入并填写
= IF(A2&A1; A1,1,D1 + 1)
在下面的截图中,这是num列
下表中的公式如下:
($ D2 = 1,A2,)
B12:= IF($ D2 = 1,B2,)
C12:= IF(OFFSET($ D2,C $ 11-1,0)= C $ 11,OFFSET($ C2,C $ 11-1,0),)
pre>
将C12中的公式拖到右边,拥有尽可能多的num值。然后选择整个第一行,并填写原始数据集中的行数。
复制/粘贴值/按产品排序以删除空行。
I have a lot of data in an Excel spreadsheet that is currently in multiple rows. I'd like to transform this into rows with multiple columns.
Current data set: Name ID Image product 1 111 image111A.jpg product 1 111 image111B.jpg product 2 222 image222A.jpg product 2 222 image222B.jpg product 2 222 image222C.jpg product 3 333 image333A.jpg product 3 333 image333B.jpg Desired outcome: Name ID image1 image2 image3 product 1 111 image111A.jpg image111B.jpg product 2 222 image222A.jpg image222B.jpg image222C.jpg product 3 333 image333A.jpg image333B.jpg
Assuming your data begins in A1 and is sorted by Product (or ID)
Enter this in D2 and fill down
=IF(A2<>A1,1,D1+1)
In the screenshot below, this is the "num" column
Formulas in the second table below are:
A12: =IF($D2=1,A2,"")
B12: =IF($D2=1,B2,"")
C12: =IF(OFFSET($D2,C$11-1,0)=C$11,OFFSET($C2,C$11-1,0),"")
Drag the formula in C12 over to the right for as many "num" values as you have. Then select the whole first row and fill down for as many rows as there are in your original dataset. Copy/paste values/sort by Product to remove blank rows.
这篇关于将多行转移到多个列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!