我的数据如下:
Parameter Location_A Location_B Location_C Location_D
A 1 0.3 0.2 0.1
B 0.9 0.3 0.1 0.1
C 1.1 0.2 0.3 0.2
我有 365 个参数和 768 个位置。
我想为每个参数和位置组合创建一行,并在第三列显示结果(即 365*768 = 280,320):
Location Parameter Result
Location_A A 1
Location_A B 0.9
Location_A c 1.1
Location_B A 0.3
Location_B B 0.3
等等。有没有简单的方法可以做到这一点?我有一个标题行,然后每个参数有 365 行,B 列到 ACO 是位置。
我查看了一些内容但似乎找不到答案: 如何使用 Excel 将一行拆分为多行?
答案1
开始了。
步骤1:
为方便起见,命名范围。PARAMETERS 是从 A2 向下的参数列表;LOCATIONS 是从 B1 向内的位置列表;DATA 是从 B2 到末尾的大方块。看我的例子:
第2步:
在另一张表中,设置新表。第一列打印出所有位置,并列出每个位置的次数与参数的次数相同:
该公式:
=INDEX(LOCATIONS,ROUNDUP((ROW()-1)/COUNTA(PARAMETERS),0))
那个公式复制下来。
步骤3:
第二列打印出所有参数,并列出每个参数一次,直到没有更多参数可列出(请注意,此计数对应于步骤 2 中列出每个位置的次数)。现在您已经获得了每个位置/参数组合的完整列表,每个组合一次:
该公式:
=INDEX(PARAMETERS,MOD(ROW()-2,COUNTA(PARAMETERS))+1)
那个公式复制下来。
步骤4:
从这里开始,前进的道路应该很清晰——我们现在使用一个简单的 INDEX MATCH 来查找给定位置和参数交叉处的数据。
该公式:
=INDEX(DATA,MATCH(B2,PARAMETERS,0),MATCH(A2,LOCATIONS,0))
那个公式复制下来。
结论:
您使用三个公式创建了连接表。请考虑选择此答案,以便将此问题从未回答队列中删除。
笔记:
- 无论您的数据中有多少列/行,这都可以动态工作(只要您根据需要调整命名范围,如果您添加的记录超过该问题规范中的 365 * 768 条记录)。
- 但是,它不会对缺失或空数据执行任何特殊操作;您可以轻松地将步骤 4 中的最终 INDEX MATCH 与 IF(ISBLANK()) 包装在一起,以返回比“0”更有用的内容。
- 这并非是为了跳过这些记录而设计的,这增加了超出了该问题范围的复杂性。
答案2
在结果列中尝试这个:
=OFFSET($A$1,MATCH(B7,$A$2:$A$4),MATCH(A7,$B$1:$E$1))