动态重组Google表格数据 [英] Reorganizing Google Sheets data dynamically

问题描述

我目前正在使用Google表格从Wordpress中的Contact Form 7导入数据.所有数据都可以正常使用，但是我想了解有关以更用户友好的方式对其进行格式化的信息.我已经稍微简化了示例，但是我创建的表单的要点允许用户根据需要请求带有不同措辞的图形文件的多个版本，最多5个(为简单起见，我的示例只有2个).

I'm currently working with Google Sheets to import data from Contact Form 7 in Wordpress. All the data is coming over fine, but I wanted to see about formatting it in more user friendly fashion. I've simplified the example a bit, but the gist of the form I have created allows the user to request multiple versions of a graphic file with different wording as needed, up to 5(my example has just 2 for simplicity sake).

所有数据都是使用CF7变量导入的，理想情况下，我想对此进行一些清理.我想到的解决方案是创建第二张纸，将第一张纸中提交的数据提取为更用户友好的格式，因为我打算将其用作工作表，以便设计人员在设计完成后创建所需的图形.数据已接收.对于每个请求，名称/部门/电子邮件/日期都保持不变，但我想在另一行显示版本以及行1和行2的数据.是否可以即时重组数据，所以当提交新表单并将数据添加到工作表1中时，工作表2随后将使用格式正确的信息进行更新?

All the data is imported using the CF7 variables and ideally I wanted to clean this up a bit. What I had thought of as a solution was creating a second sheet that pulls in this data submitted in the first sheet into a more user friendly format, as I intended to use this as a work form for a designer to create the requested graphic once the data is received. With each request the name/department/email/date all stay the same, but I'd like to display the version and line 1 and 2 data on another line. Is it possible to reorganize data like this on the fly, so when a new form is submitted and adds data to sheet 1, sheet 2 would then update with the properly formatted info?

这甚至有可能做到吗?我在网上做了一些查找，但与这种类型的数据处理没有任何真正的联系.

Is this even possible to do? I did some looking online, but didn't anything that really related to this type of data manipulation.

解决方案: 这就是我的示例的最终结果

Solution: Here's what ended up working for my example

=ArrayFormula(QUERY({
Sheet1!A2:D,Sheet1!E2:G,ROW(Sheet1!A2:A);
IFERROR(LEN(Sheet1!A2:D)/0),Sheet1!H2:J,ROW(Sheet1!A2:A);
IFERROR(LEN(Sheet1!A2:D)/0),Sheet1!K2:M,ROW(Sheet1!A2:A);
IFERROR(LEN(Sheet1!A2:D)/0),Sheet1!N2:P,ROW(Sheet1!A2:A);
IFERROR(LEN(Sheet1!A2:D)/0),Sheet1!Q2:S,ROW(Sheet1!A2:A)
},"select Col1,Col2,Col3,Col4,Col5,Col6,Col7 where Col5<>'' order by Col8",1))

推荐答案

是的，有可能.

一种方法是使用数组和QUERY函数.

One way is to use arrays and the QUERY function.

为简单起见，请说

• A列和B列具有订单的一般信息
• C和D列包含版本1的数据
• E和F列包含版本2的数据
• G和H列包含版本3的数据

在输出页上，添加标题. 在它们的下面添加一个类似于以下内容的公式:

On the output sheet, add the headers. Below of them add a formula like the following:

=ArrayFormula(QUERY({A2:B,C2:D,ROW(A2:A);IFERROR(LEN(A2:B)/0),E2:F,ROW(A2:A);IFERROR(LEN(A2:B)/0),G2:H,ROW(A2:A)},"select Col1,Col2,Col3,Col4 where Col3<>'' order by Col5"))

• 参考文献从第2行开始，以跳过标题以避免将其包含在输出表中.
• ROW(A2:A)用于保持顺序
• IFERROR(LEN(A2:B)/0)是一个技巧"，用于隐藏"相同顺序的第二行和后续行的顺序(常规信息)数据.在QUERY函数的select参数上，在order by子句中将其引用为Col5.
• 假定lookup-choice-1永远不会为空.

• References start on row 2 to skip the headers to avoid to include them on the output sheet.
• ROW(A2:A) is used to keep the order
• IFERROR(LEN(A2:B)/0) is a "trick" used to "hide" the order (general information) data for the second and following rows for the same order. On the select parameter of the QUERY function, it's referrey as Col5 on the order by clause.
• It's assumed that lookup-choice-1 will never be empty.

注意:

1. 如果添加了更多列，则列号应相应更新
2. 不要使用order by子句按常规信息列对结果进行排序，因为技巧"隐藏了标签".如果需要应用排序，请在应用上述公式之前执行此操作，可以通过数据">排序范围..."功能对源范围进行排序，以便在通过上述公式转换数据之前对数据进行排序.

1. If more columns were added, the column numbers should be updated accordingly
2. Don't use the order by clause to sort the result by the general information columns because the "trick" to hide the "labels". If you need to apply a sort, do it' before applying the above formula, you could do this by sorting the source range through the Data > Sort range... feature, so the data is sorted before it's transformed by the above formula.

另请参见

• 对数据进行排序和过滤，这是一篇描述帮助的官方帮助文章数据>排序范围...

• Sort and filter your data, an official help article describing Data > Sort range...

这篇关于动态重组Google表格数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！