将两列数据表示到表中(Representing two columns of data into a table)

将两列数据表示到表中(Representing two columns of data into a table)

我有两列数据，如下所示。

A B 1 John red 2 John yellow 3 John yellow 4 Albert blue 5 Albert orange 6 Chad blue 7 Tim yellow

我想以网格/表格格式表示它们，如下所示：

A B C D E 1 red yellow blue orange 2 John 1 2 0 0 3 Albert 0 0 1 1 4 Chad 0 0 1 0 5 Tim 0 1 0 0

我试图表示一个表中的两列，它保持每个人分配的颜色数量。 我已经通过使用countif的数组公式并使用填充处理填充表来成功完成此操作，但是我想知道是否有更强大的方法来完成此任务，因为我的数据最终会发生变化。 即在一个单元格中使用一个公式来填充表格。

I have two columns of data as shown below.

A B 1 John red 2 John yellow 3 John yellow 4 Albert blue 5 Albert orange 6 Chad blue 7 Tim yellow

I would like to represent them in a grid/table format as shown:

A B C D E 1 red yellow blue orange 2 John 1 2 0 0 3 Albert 0 0 1 1 4 Chad 0 0 1 0 5 Tim 0 1 0 0

I am trying to represent the two columns in a table that keeps a count of the number of colours that each person is assigned to. I have successfully completed this by using array formula with countif and filling up the table using fill handling however was wondering if there was a more robust way of completing this task as my data can end up changing. i.e. using one formula in one cell to fill up the table.

最满意答案

在谷歌表

这个公式：

=QUERY(A1:C,"select A, Count(C) where A <>'' group by A pivot B")

或仅使用范围A1:B ，此公式：

=QUERY({A1:B,B1:B},"select Col1, Count(Col2) where Col1 <>'' group by Col1 pivot Col3")

---

要使公式显示0而不是空白，请使用以下公式：

={{"";UNIQUE(A1:A8)},{TRANSPOSE(UNIQUE(B1:B8));ArrayFormula(MMULT(--(UNIQUE(A1:A8=transpose(A1:A8))),TRANSPOSE(--(UNIQUE(B1:B8)=TRANSPOSE(B1:B8)))))}}

如果你想在开放范围（ A1:A ， B1:B ）中使用它，那么使用：

={{"";UNIQUE(OFFSET(A1,,,COUNTA(A1:A)))},{TRANSPOSE(UNIQUE(OFFSET(B1,,,COUNTA(B1:B))));ArrayFormula(MMULT(--(UNIQUE(OFFSET(A1,,,COUNTA(A1:A))=transpose(OFFSET(A1,,,COUNTA(A1:A))))),TRANSPOSE(--(UNIQUE(OFFSET(B1,,,COUNTA(B1:B)))=TRANSPOSE(OFFSET(B1,,,COUNTA(B1:B)))))))}}

这个公式更难，而且效果更慢，但你可以更好地控制结果。

in google sheets

This formula:

=QUERY(A1:C,"select A, Count(C) where A <>'' group by A pivot B")

or use only range A1:B, this formula:

=QUERY({A1:B,B1:B},"select Col1, Count(Col2) where Col1 <>'' group by Col1 pivot Col3")

---

To make formula show 0s instead of blanks, use this formula:

={{"";UNIQUE(A1:A8)},{TRANSPOSE(UNIQUE(B1:B8));ArrayFormula(MMULT(--(UNIQUE(A1:A8=transpose(A1:A8))),TRANSPOSE(--(UNIQUE(B1:B8)=TRANSPOSE(B1:B8)))))}}

if you want to use it with open ranges (A1:A, B1:B), then use this:

={{"";UNIQUE(OFFSET(A1,,,COUNTA(A1:A)))},{TRANSPOSE(UNIQUE(OFFSET(B1,,,COUNTA(B1:B))));ArrayFormula(MMULT(--(UNIQUE(OFFSET(A1,,,COUNTA(A1:A))=transpose(OFFSET(A1,,,COUNTA(A1:A))))),TRANSPOSE(--(UNIQUE(OFFSET(B1,,,COUNTA(B1:B)))=TRANSPOSE(OFFSET(B1,,,COUNTA(B1:B)))))))}}

This formula is harder and it works slower, but you have more control on the result.