将多个查询输出堆叠在一张纸中 [英] Stacking multiple query output in one sheet

问题描述

对于我的系，我为每位老师制作了一个动态而灵活的概览表，向其展示了她的老师辅导学生.

For my department I made a dynamic and flexible overview sheet for every teacher, showing his of hers tutored students.

在一个概览表中，我进行了几个查询，这些查询从不同的来源(不同年份的学生和教师)收集数据.该查询位于一列中，因为我想为每个老师生成一个列表.

In one overview sheet I have made several query's, that gather data form different sources (students and teachers in different years). The query's are in one column, because I want to generate a list per teacher.

但是查询公式生成的输出的长度可以不同(=需要的行数=学生数)，这就是为什么我必须在概览表中允许固定的行数.这会导致我的列表中的空白行很丑.

But the output the query formula generate, can differ in length (= number of rows it takes = number of students), that's is why I have to allow for a fixed number of rows in the overview sheet. And that can lead to ugly blank rows in my list.

是否有办法保持灵活性(每个老师的补习学生数量不同)，但要删除空白行?

Is there a way to keep the flexibility (for every teacher has a different number of tutored students), but remove the blank rows?

推荐答案

如果每位教师的工作表中有相同数量的相同列，则您的数据结构良好，您可以对其进行单个查询.

If any sheet for every teacher has the same number of same columns, then your data is well-structured and you could make single query on them.

=query({Sheet1!A1:C;Sheet2!A2:C},"select * where Col1 <> ''")

请注意一些细节:

1. 查询应仅包含带标题的一行，使用Sheet1!A1:C带标题的行，而其他SheetName!A2:C
2. 当查询具有包含多个表的源时，请使用Col1, Col2, Col3...而不是列字母A, B, C...可能更加方便.当从另一个文件导入数据时，该规则也适用.

1. query should take only one row with headers, use Sheet1!A1:C with headers and the others SheetName!A2:C
2. when query have source, that contains of multiple tables, use Col1, Col2, Col3... instead of column's letters A, B, C... It may be even more convenient. This rule also works, when data is imported from another file.

这篇关于将多个查询输出堆叠在一张纸中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！