将具有原始工作表名称的列添加到合并工作表 [英] 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)

该公式的工作原理如下:抓取范围为 Class 1 和 Class 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屋！