Excel根据另一列中的值将一些行转置为列 [英] Excel transpose some rows into columns depending on values in another column
问题描述
时间012345 2345678 5647890 7891234
6:00 1 0 0 1
6:30 1 1 0 1
7:00 1 1 0 0
7:30 0 0 0 0
8:00 0 0 1 0
8:30 0 0 1 0
9:00 0 0 1 0
9:30 0 0 1 0
10:00 0 0 0 0
10:30 1 0 0 1
11:00 1 0 0 1
11:30 1 0 0 1
12:00 1 0 0 1
12:30 0 0 0 0
13:00 0 0 0 0
13:30 0 0 1 0
14:00 0 0 1 0
14:30 0 0 1 0
15 ：00 0 0 1 0
15:30 1 1 0 0
等。
目标是获得这些转置，但要花费时间值，而不是列中的实际1和0。理想的输出如下所示：
userID startTime1 stopTime1 startTime2 stopTime2 startTime3 stopTime3
012345 6:00 7:30 10:00 12:30 15:00
2345678 6:00 7:30 15:00
5647890 7:30 10:00 13:00 15:30
7891234 6:00 7： 00 10:00 12:30
我已经尝试设置数据透视表并进行过滤，没有得到我的任何地方我不知道还有什么可以尝试的。 V和HLOOKUPS还没有为我做任何的伎俩。
部分问题是，开始时间需要是时间戳在1上方的一行和停止时间需要成为一组中最后1位的时间戳1行。做简单的事情，如if语句没有帮助我，因为我需要从列中的每个组中的第一个和最后一次。所以我很沮丧。
对此的任何帮助将不胜感激。
根据您的理想格式，我可以为您提供正确的结果。假设您的数据在A1：E21范围内，并且您已经将范围G2：G5中的userID复制/转置。
首次开始时间公式为：
= IF（HLOOKUP（$ G2，$ B $ 1：$ E $ 2,2,1 ）= 1，$ A $ 2，INDEX（$ A $ 1：$ A $ 21 MATCH（1，OFFSET（$ A $ 1：$ A $ 21,0，MATCH（$ G2，$ 1：$ 1,0）-1 ,,） ，0）-1））
第一个停留时间公式（可以复制到所有停止时间列）是：
$ code> = IFERROR（INDEX（OFFSET（$ A $ 1：$ A $ 21，MATCH（H2，$ A $ 1：$ A $ 21,0）,,,），1 + MATCH（0，OFFSET A $ 1：$ A $ 21 MATCH（H 2，$ A $ 1：$ A $ 21,0），MATCH（$ G2，$ 1：$ 1,0）-1 ,,），0）-1）， ）
第二个开始时间公式（可以复制到所有后续开始时间列：
= IFERROR（INDEX （OFFSET（$ A $ 1：$ A $ 21 MATCH（I2，$ A $ 1：$ A $ 21,0）,,,），MATCH（1，OFFSET（$ A $ 1：$ A $ 21 MATCH（I2，$ A $ 1：$ A $ 21,0），MATCH（$ G2，$ 1：$ 1,0）-1 ,,），0）-1），）
对于工作文件示例，请查看此处。
I have an Excel file I'm getting that looks something like this:
Time 012345 2345678 5647890 7891234
6:00 1 0 0 1
6:30 1 1 0 1
7:00 1 1 0 0
7:30 0 0 0 0
8:00 0 0 1 0
8:30 0 0 1 0
9:00 0 0 1 0
9:30 0 0 1 0
10:00 0 0 0 0
10:30 1 0 0 1
11:00 1 0 0 1
11:30 1 0 0 1
12:00 1 0 0 1
12:30 0 0 0 0
13:00 0 0 0 0
13:30 0 0 1 0
14:00 0 0 1 0
14:30 0 0 1 0
15:00 0 0 1 0
15:30 1 1 0 0
etc.
The goal is to get these transposed but to take the time values instead of the actual 1s and 0s in the columns. The ideal output would look like this:
userID startTime1 stopTime1 startTime2 stopTime2 startTime3 stopTime3
012345 6:00 7:30 10:00 12:30 15:00
2345678 6:00 7:30 15:00
5647890 7:30 10:00 13:00 15:30
7891234 6:00 7:00 10:00 12:30
I've tried setting up a pivot table and filtering, and that hasn't gotten me anywhere. And I'm not sure what else to try. V and HLOOKUPS haven't done the trick for me either.
Part of the problem is that the start time needs to be the timestamp one row above the 1 and the stop time needs to be the timestamp 1 row below the final 1 in a group. Doing simple things like if statements hasn't helped me because I need the first and last time in each group from the column. So I'm pretty stumped.
Any help on this would be appreciated.
Based on your ideal format, I have formulas that will give you the right results. Assuming your data is in range A1:E21, and that you have copied / transposed your userIDs in range G2:G5.
First start time formula is:
=IF(HLOOKUP($G2,$B$1:$E$2,2,1)=1,$A$2,INDEX($A$1:$A$21,MATCH(1,OFFSET($A$1:$A$21,0,MATCH($G2,$1:$1,0)-1,,),0)-1))
First stop time formula (and this can be copied to all stop time columns) is:
=IFERROR(INDEX(OFFSET($A$1:$A$21,MATCH(H2,$A$1:$A$21,0),,,),1+MATCH(0,OFFSET($A$1:$A$21,MATCH(H2,$A$1:$A$21,0),MATCH($G2,$1:$1,0)-1,,),0)-1),"")
Second start time formula (which can be copied to all subsequent start time columns:
=IFERROR(INDEX(OFFSET($A$1:$A$21,MATCH(I2,$A$1:$A$21,0),,,),MATCH(1,OFFSET($A$1:$A$21,MATCH(I2,$A$1:$A$21,0),MATCH($G2,$1:$1,0)-1,,),0)-1),"")
For a working file sample, check here.
这篇关于Excel根据另一列中的值将一些行转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
