将一对多数据转换为列 [英] Transform one to many data to columns

问题描述

给出此数据:

如何将其转换为如下形式:

数据源中有两列，分别是 key ( title )和 value ( responsibility ).我需要对其进行转换，以便我们有 key 列( title )，然后是 n 列，其中 n 是键具有最多 value 个数字，例如上图中的3.因此，列应为:

标题，1、2、3 .

每列 1、2、3 中的值应与原始数据中的值相对应.

欢迎使用任何公式组合-我相信 Transpose 和/或 Query ( pivot )的组合是合适的，但是我不能放在一起.

如果这太复杂了，我们可以直接在数据源中放入一个枚举，但是如果没有它就可以使公式起作用将是很好的.例如:

示例表:

---

或:

  = ARRAYFORMULA(QUERY({A:A，COUNTIFS(A:A，A:A，ROW(A:A)，< ="& ROW(A:A))，B:B}，选择Col1，max(Col3)其中Col1不为null按Col1分组枢轴Col2，1)) 

Given this data:

How do I transform it to look like this:

There are two columns in the data source, key (title) and value (responsibility). I need to transform it such that we have the key column (title) and then n columns where n is the highest number of value a key has, eg 3 in the picture above. Hence the columns should be:

Title, 1, 2, 3.

The values in each column 1, 2, 3 should be corresponding to values in the original data.

Any combination of formula is welcomed - I believe a combination of Transpose and/or Query (pivot) is appropriate but I cannot put it together.

In case this is too complex we can put an enumeration directly in the data source, but it would be nice to be able to have the formula work without it. Eg:

Example sheet: https://docs.google.com/spreadsheets/d/1InYZ12VuuaSg0s3fiFTCx8BnwEan5JsqpsNBF973lWc/edit?usp=sharing

解决方案

try:

=QUERY({A:C}, 
 "select Col1,max(Col3) 
  where Col1 is not null 
  group by Col1 
  pivot Col2", 1)

---

or:

=ARRAYFORMULA(QUERY({A:A, COUNTIFS(A:A, A:A, ROW(A:A), "<="&ROW(A:A)), B:B}, 
 "select Col1,max(Col3) 
  where Col1 is not null 
  group by Col1 
  pivot Col2", 1))

这篇关于将一对多数据转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！