反透视 Excel 电子表格

Question 1

我将使用 Index 函数：

假设您的数据源位于 Sheet1 中的 A1:D20。然后在 Sheet2 中使用以下公式：

Column A: "Row": =1, =1, =1, =A1+1, drag down
Column B: "Column": =1, =2, =3, =A1, drag down
Column C: "User": =INDEX(Sheet1!$A$2:$A$20,A2)
Column D: "Perms": =INDEX(Sheet1!$B$1:$D$1,B2)
Column E: "Value": INDEX(Sheet1!$B$2:$D$20,A2,B2)

最终看起来应该是这样的：

Row Column User  Perm  Value 
1   1      User1 Perm1 R     
1   2      User1 Perm2 W     
1   3      User1 Perm3 X     
2   1      User2 Perm1 X     
2   2      User2 Perm2 W     
2   3      User2 Perm3 X     
3   1      User3 Perm1 R     
3   2      User3 Perm2 R     
3   3      User3 Perm3 X

希望这可以帮助。

编辑：

前两列只是为了循环显示行列组合的所有可能性。假设您有 40 种不同的权限类型和 500 个用户：

如果您想使用公式，您可以将其一直拖到最下面，您可以这样做：

Column A: "Count": =1, =A1+1
Column B: "Row": =INT((A1-1)/5)+1
Column C: "Column": =MOD(A1-1,40)+1  (where you change 40 to the number of permissions you have)
Column D: "User": =INDEX(Sheet1!$A$2:$A$20,B2)
Column E: "Perms": =INDEX(Sheet1!$B$1:$D$1,C2)
Column F: "Value": INDEX(Sheet1!$B$2:$D$20,B2,C2)

Answer

我将使用 Index 函数：

假设您的数据源位于 Sheet1 中的 A1:D20。然后在 Sheet2 中使用以下公式：

Column A: "Row": =1, =1, =1, =A1+1, drag down
Column B: "Column": =1, =2, =3, =A1, drag down
Column C: "User": =INDEX(Sheet1!$A$2:$A$20,A2)
Column D: "Perms": =INDEX(Sheet1!$B$1:$D$1,B2)
Column E: "Value": INDEX(Sheet1!$B$2:$D$20,A2,B2)

最终看起来应该是这样的：

Row Column User  Perm  Value 
1   1      User1 Perm1 R     
1   2      User1 Perm2 W     
1   3      User1 Perm3 X     
2   1      User2 Perm1 X     
2   2      User2 Perm2 W     
2   3      User2 Perm3 X     
3   1      User3 Perm1 R     
3   2      User3 Perm2 R     
3   3      User3 Perm3 X

希望这可以帮助。

编辑：

前两列只是为了循环显示行列组合的所有可能性。假设您有 40 种不同的权限类型和 500 个用户：

如果您想使用公式，您可以将其一直拖到最下面，您可以这样做：

Column A: "Count": =1, =A1+1
Column B: "Row": =INT((A1-1)/5)+1
Column C: "Column": =MOD(A1-1,40)+1  (where you change 40 to the number of permissions you have)
Column D: "User": =INDEX(Sheet1!$A$2:$A$20,B2)
Column E: "Perms": =INDEX(Sheet1!$B$1:$D$1,C2)
Column F: "Value": INDEX(Sheet1!$B$2:$D$20,B2,C2)

Question 2

好吧，我在这个问题被问到 6 年后回答了这个问题...我现在正在使用 Excel 2013。据我所知，此方法应该适用于 Excel 2007、2010、2013。但不确定它是否适用于 Excel 2016。

首先，通过打开数据透视表和数据透视图向导AltDP。从 Excel 2007 开始，它被隐藏，只能通过快捷键打开。

选择“多个合并范围”，然后单击“下一步”。

选择“为我创建单页字段”，然后单击下一步。

单击“范围”并选择要“反透视”的范围（例如 Sheet1!$A$1:$D$4）。单击“完成”。

关闭向导后，您将看到如下所示创建的数据透视表：

双击“总计”单元格（即我的示例中的 E8），您将在新工作表上获得一个“反透视”表，如下所示：

这不是一个完美的解决方案，但绝对方便，无需编写任何公式，对我来说是迄今为止最好的解决方案。

Answer