根据列值将工作表 1 的行转换为工作表 2 [英] Transpose rows of sheet 1 to sheet2 based on a column value

问题描述

我下面有两张纸.我还在这里添加了示例数据和所需的输出/a>.

I have two sheets below. I have also added sample data and desired output in the sheet here.

帖子表:

 id |   title   |  image   
 1  |  title 1  |  1.jpg   
 2  |  title 2  |  2.jpg   
 3  |  title 3  |  3.jpg   

元数据表:

  meta_id |  meta_key      |  value   |  post_id
     1    |  meta_title    |  1       |     1
     2    |  meta_keyword  |  1       |     1
     3    |  meta_likes    |  1       |     1
     4    |  meta_title    |  2       |     2
     5    |  meta_keyword  |  2       |     2
     7    |  meta_title    |  3       |     3
     8    |  meta_keyword  |  3       |     3
     9    |  meta_likes    |  3       |     3

如您所见，不能保证每个帖子都包含所有元数据.

As you can see, there is no guarantee every post will have all the metadata.

每张纸上的说明:

帖子表 -

帖子表由帖子列表、标题和图片组成.id其中的列是独一无二的，永远不会重复.所以基本上一个帖子将有一行.

Posts sheet consist of a list of posts, its title and image. id column in it is unique and will never repeat. So basically one post will have one row.

元数据表 -

元数据表包含与个人相关的各种信息邮政.它包含键值数据，并映射到主帖子表通过 post_id 列.

Metadata sheet consists of various information related to an individual post. It contains key-value data with a mapping to the main post sheet via the post_id column.

两者需要如何结合-

元数据表中的每个元数据都应该添加到相关行在基于元数据中 post_id 列的帖子表中.所以之后合并帖子表中的一行将包含名为 meta_title 的列，meta_keyword &meta_likes.

Each metadata in the metadata sheet should be added to relevant rows in the posts sheet based on the post_id column in metadata. So after merging a row in the posts sheet will have columns named meta_title, meta_keyword & meta_likes.

合并后的列标题示例如下所示:

meta_id | meta_key  |  value  |  meta_title | meta_keyword | meta_likes

我试过这个 但是，它仅在 meta_table 只有一个键时才有效.就我而言，元表可以包含多个键和值.

I tried this, however, it only works if meta_table has just one key. In my case, the metatable can consist of multiple keys and values.

样本数据 &在本表中添加了所需的输出.

Sample data & desired output added in this sheet.

推荐答案

on 此共享表 来自原始帖子中的评论，我创建了一个名为 MK.Help 的新标签，其中我留下了两个选项.第一个由两个公式组成，第二个只有一个.

on this shared sheet from the comments in the original post, I made a new tab called MK.Help where I left two options. The first consists of two formulas, the second, just one.

单元格 A1 中的这个公式，只是带来了帖子:

This formula in cell A1, just brings over the posts:

=QUERY(Posts!A:C,"where B<>''")

此公式填充每个帖子的元数据.

This formula populates the meta data for each post.

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A&D1:F1,{Metadata!D:D&Metadata!B:B,Metadata!C:C},2,0)))

还有另一个可能更简单的选项，它只是以固定方式 QUERY() s 元数据.也就是说，您无法控制列的顺序，它们按字母顺序发生.

There is also another potentially simpler option which just QUERY()s the meta data in a fixed way. That is, you cannot control the order of the columns, they happen alphabetically.

=QUERY(Metadata!A:D,"select D,MAX(C) where D is not null group by D pivot B")

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