可以使用查询将同一个工作簿中的多个工作表组合起来吗？
可以使用查询将同一个工作簿中的多个工作表组合起来吗？
提问于 2019-11-06 08:21:49
我有一个工作簿，用于跟踪生产输出。有一些主要的标签页(例如主列表，PDLine1，PDLine2，每日摘要)。
主列表用于列出所有生产订单，而PDLine工作表用于跟踪各自生产线上的生产订单。“每日摘要”将列出每个PDLine的一天总输出量。
从PDLine表中，我实际上生成了一个新的表来跟踪每个生产订单来测量他们的每日产量(一个订单大约需要3到5天才能完成)。
因此，使用查询函数和应用程序脚本，每次生成一个新的生产订单表，该工作表会被添加到“每日摘要表”中的查询函数中吗？
我不知道我上面提到的对你是否有意义，但是如果你对如何做这件事有一个想法，并且愿意帮忙，请让我详细说明。
链接到示例电子表格：示例电子表格
回答 1
Stack Overflow用户
回答已采纳
发布于 2019-11-07 05:30:24
我认为您不需要为工作表PDLine导入范围，您可以对每个PDLine表使用这个范围，只需用您的工作表更改筛选器：
={QUERY(JobList!A:P,"Select A,B,C,D,E,F,G where H='PDLine1'"),ArrayFormula( QUERY(JobList!H:O,"Select O where H='PDLine1'") ),ArrayFormula(QUERY(JobList!D:H,"Select D where H='PDLine1'")- QUERY(JobList!H:O,"Select O where H='PDLine1'") )}我在我的测试中：
在单元格A4中，我将：
=query({query(JobList!A10:O,"Select A,O where H<>'' ",-1),ARRAYFORMULA(if(query(JobList!H10:H,"Select H where H<>'' ",-1)="PDLine1",query(JobList!H10:O,"Select O where H<>'' ",-1),0)),ARRAYFORMULA(if(query(JobList!H10:H,"Select H where H<>'' ",-1)="PDLine2",query(JobList!H10:O,"Select O where H<>'' ",-1),0))},"Select Col1,sum(Col3),sum(Col4),Sum(Col2) where Not Col1='' group By Col1 LABEL Sum(Col3) '',Sum(Col4) '',Sum(Col2) ''",0)在E4中，我指出：
=query({query(JobList!A10:I,"Select A,I where H<>'' "),ARRAYFORMULA(if(query(JobList!H10:I,"Select H where H<>'' ")="PDLine1",query(JobList!H10:I,"Select I where H<>'' "),0)),ARRAYFORMULA(if(query(JobList!H10:I,"Select H where H<>'' ")="PDLine2",query(JobList!H10:I,"Select I where H<>'' "),0))},"Select sum(Col3),sum(Col4),Sum(Col2) where not Col1='' group By Col1 label sum(Col3) '',sum(Col4) '',sum(Col2) ''",0)可能这可以在脚本中使用
function createQuery() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var myJ = ss.getSheets();
var ShList=[];
for (a=1; a< myJ.length; a++)
{
if (myJ[a-1].getSheetName().substring(0, 3)=='myJ')
ShList.push (myJ[a-1].getSheetName());
}
var myQry='={';
for (a=0; a<ShList.length; a++)
{
if (a>0) myQry = myQry + ",";
myQry= myQry + "QUERY(" + ShList[a] + "!B8:C10,\"Select B,C\")";
}
myQry= myQry + '}';
Logger.log(myQry); //put this to cell
ss.getSheetByName('myRslt').getRange('A4').setFormula(myQry);
}你的可能会是这样的：
=query({query(JobList!A10:O,"Select A,O where H<>'' ",-1),ARRAYFORMULA(if(query(JobList!H10:H,"Select H where H<>'' ",-1)="PDLine1",query(JobList!H10:O,"Select O where H<>'' ",-1),0)),ARRAYFORMULA(if(query(JobList!H10:H,"Select H where H<>'' ",-1)="PDLine2",query(JobList!H10:O,"Select O where H<>'' ",-1),0))},"Select Col1,sum(Col3),sum(Col4),Sum(Col2) where Not Col1='' group By Col1 LABEL Sum(Col3) '',Sum(Col4) '',Sum(Col2) ''",0)在E4中，我指出：
=query({query(JobList!A10:I,"Select A,I where H<>'' "),ARRAYFORMULA(if(query(JobList!H10:I,"Select H where H<>'' ")="PDLine1",query(JobList!H10:I,"Select I where H<>'' "),0)),ARRAYFORMULA(if(query(JobList!H10:I,"Select H where H<>'' ")="PDLine2",query(JobList!H10:I,"Select I where H<>'' "),0))},"Select sum(Col3),sum(Col4),Sum(Col2) where not Col1='' group By Col1 label sum(Col3) '',sum(Col4) '',sum(Col2) ''",0)也许这个可以用在你的
function createQuery() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var myJ = ss.getSheets();
//Below you will use the ss as your spreadsheet and myJ as your Sheets
//But may be different in yours,
//ShList is stringlist to store your sheetname that matchs to your criteria
var ShList=[];
for (a=1; a< myJ.length; a++)
{
//If your sheet prefix is 'JO' use below, if others you can change it
//substring(0, 2) is depend on your sheet name that will be included
//in myQuery later
if (myJ[a-1].getSheetName().substring(0, 2)=='JO')
ShList.push (myJ[a-1].getSheetName());
}
var myQry='={';
for (a=0; a<ShList.length; a++)
{
if (a>0) myQry = myQry + ",";
//You must change A8:G10 to your actual condition, may be you can use getLastRow() first, you can use select * if all column define will be included
myQry= myQry + "QUERY(" + ShList[a] + "!A8:G10,\"Select A,B,C,D,E,F,G\")";
}
myQry= myQry + '}';
//Please check your logger.log content, myQry must be success first
Logger.log(myQry); //put this to cell
//Change myRslt to your destination sheet that you will store your query
//Change A4 to your destination cell
ss.getSheetByName('myRslt').getRange('A4').setFormula(myQry);
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58725780
复制相关文章
相似问题
腾讯云开发者
