将一对多数据转换为列 [英] 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:乙},"选择 Col1,max(Col3)其中 Col1 不为空按 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屋！