我正在尝试从不确定数目的工作表的A列中生成唯一项的列表。想法是随着时间的流逝,将添加更多的工作表,并且我不想更新公式来说明新工作表。
测试/示例电子表格https://docs.google.com/spreadsheets/d/193ZTAbn8R-HjwDn2LE2eDDVJk_LbY4V5P9J6ynES6wo/edit#gid=0
在示例中,工作表List1,List2和List3是我要从中创建一个组合列表(在工作表“ CombinedList”中)的列表,其中每个工作表的列A中都出现了这些项目。如“期望的结果”表所示,我希望得到一个表,该表从每个列表中提取与每个项目相关的值。我可以在创建新工作表时将它们手动添加到行1中。只是我困住了A列中项目列表的生成。
我本来是在做以下事情,
=ARRAYFORMULA(UNIQUE(QUERY({'List1'!A2:A;'List2'!A2:A},
"select Col1 where Col1 is not null")))
但是我不必写出所有要查询的列表的名称,而是想使用INDIRECT()之类的东西来引用在第1行中列出的工作表。但是,我已经阅读了INDIRECT和ARRAYFORMULA的几个地方彼此之间不要玩得很好。
您可以使用以下脚本:
function SNAME(option) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet()
var thisSheet = sheet.getName();
if(option === 0){ // ACTIVE SHEET NAME =SNAME(0)
return thisSheet;
}else if(option === 1){ // ALL SHEET NAMES =SNAME(1)
var sheetList = [];
ss.getSheets().forEach(function(val){
sheetList.push(val.getName())
});
return sheetList;
}else if(option === 2){ // SPREADSHEET NAME =SNAME(2)
return ss.getName();
}else{
return "#N/A"; // ERROR MESSAGE
};
};
function onEdit() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Master Sheet');
var src = sheet.getRange("A1");
var str = src.getValue();
var cell = sheet.getRange("A2");
cell.setFormula(str);
}
然后将其粘贴到主表的A1中:
=ARRAYFORMULA("=ARRAYFORMULA(QUERY({"&TEXTJOIN("; ", 1,
FILTER(SNAME(1), SNAME(1)<>SNAME(0))&"!A2:B, "&
FILTER(SNAME(1), SNAME(1)<>SNAME(0))&"!C2:C&"&
FILTER(SNAME(1), SNAME(1)<>SNAME(0))&"!A1")&
"}, ""select Col1,max(Col2) where Col1 is not null group by Col1 pivot Col3""))")
如果添加了新工作表,这将满足您所有不需要通过添加引用来编辑公式的需求。唯一的缺点是要重新计算工作表名称脚本...为此,您需要拆除A1公式,例如,'
在前导=
Enter之前添加,然后将其删除以修复公式
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句