将具有原始工作表名称的列添加到合并的工作表 [英] Add column with sheet name of origin to merged sheet

问题描述

该公式可以合并，然后按日期过滤，范围从不同的工作表变为主工作表:

Having this formula to merge and then filter by date, ranges from different sheets into a master sheet :

=QUERY({'Class 1'!A3:I14;'Class 2'!A3:I14}
,"SELECT Col1, Col3, Col4, Col5, Col6, Col7, Col8, Col9 WHERE (Col1 >= date '" & TEXT(TODAY(),"yyyy-MM-dd") & "' AND Col1 <= date '" & TEXT(TODAY()+9,"yyyy-MM-dd") & "')", FALSE)

公式的工作方式如下:抓取范围从 1级和 2级工作表开始.然后，它将在今天和从现在开始的9天之间的第一列中过滤结果.对于每个过滤的结果，然后显示从第三列到第九列的所有内容.

The formula works like this: Grabs ranges from Class 1 and Class 2 sheets. Then it filters the results in the first column between today and 9 days from now. For each filtered result everything on the right from the third column till the ninth column is then displayed.

在主表中，我需要添加一列包含班级(即每个结果的原产地名称)的列，以便可以了解要测试的学生来自哪个班级.

In the Master sheet I need to add a column containing the class (aka the name of the sheet of origin of each result) so is possible to understand from which class the students to be tested are from.

示例表

推荐答案

使用IF和ARRAYFORMULA为第一张工作表添加类名，并为第二张工作表应用相同的模式.

Use IF and ARRAYFORMULA to add the class name for the first sheet and apply the same patter for the second sheet.

应用上述方法的一种非常粗糙的方法:

A very rough way to apply the above:

=ArrayFormula(QUERY({
'Class 1'!A3:I14,IF(N('Class 1'!A3:I14),"Class 1");
'Class 2'!A3:I14,IF(N('Class 2'!A3:I14),"Class 2")
},"SELECT Col1, Col10, Col3, Col4, Col5, Col6, Col7, Col8, Col9 WHERE (Col1 >= date '" & TEXT(TODAY(),"yyyy-MM-dd") & "' AND Col1 <= date '" & TEXT(TODAY()+9,"yyyy-MM-dd") & "')", FALSE))

N返回一个数字，否则不返回任何值.它可以在上述公式中使用，因为第一列包含Google表格将其作为序列号处理的日期.

N returns a number otherwise retuns nothing. It works in the above formula because the first column contains dates which Google Sheets handle them as serial numbers.

请注意，SELECT子句也已修改，它显示Col10作为第二列.

Please note that the SELECT clause was also modified, it shows Col10 as the second column.

这篇关于将具有原始工作表名称的列添加到合并的工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！