如何根据单元格内容扩展或减少公式

Question

我有一个由几张纸组成的表格。该表应每天由多个用户同时处理。主要编辑在工作表 1 中完成。Sheet2 仅包含查询和公式。

示例表 1：

• 表 1 由 2500 行和 17 列组成。

从第 12 行开始(第 11 行是标题行。上面的行包含公式引用)，A列，B列由用户填写。列 C-H 包含各种公式(例如 GoogleFinance 查询)。用户必须再次填写 G-M 列。最后，N-Q 列中再次出现公式。

目标： 自动展开/删除 C-H 和 N-Q 列中的公式，具体取决于是否已在 A 列和 B 列中添加/删除内容。删除过程应逐行执行，以同时删除 G-M 列中的剩余用户内容。

床单也是可排序的。

示例表 2：

• 表 2 由 1500 行和 11 列组成。

从第 9 行开始(第 9 行也是标题行。上面的行包含公式引用)，列 A-I 填充了查询结果(位于单元格 A9 中)。 查询从工作表 1 中获取某些内容，并且是动态的。J-K 列包含公式。

目标： 自动扩展/删除 J-K 列中的公式，具体取决于是否在 A-I 列中添加/删除了内容(通过查询)。

为了扩展、删除和排序，我使用了下面的代码示例。

不幸的是，显示的代码不符合星座中描述的要求。因此，如果能有更好的解决办法，我将不胜感激。

function fillDownFormulaTD(){
Sheet = "sheet1";
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(Sheet);
var lr = ss.getLastRow();
var Rng1 = ss.getRange(1, 2, lr-1);
ss.getRange("").setFormula('');
ss.getRange("").copyTo(Rng1);         
}

---

function removeEmptyRows(){    
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Ticker-Datenbank');
var maxRows = sheet.getMaxRows(); 
var lastRow = sheet.getLastRow();
sheet.deleteRows(lastRow+1, maxRows-lastRow-20);            
}

---

function Sortieren(){
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A11:Q11')
var currentCell = spreadsheet.getCurrentCell();
spreadsheet.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
spreadsheet.getActiveRange().sort({column: 3, ascending: true});
spreadsheet.getRange('A11').activate();
}

Answer 1

这是 Sheet2 的快速解决方案(Sheet#1 的答案正在开发中)。

对于 Sheet2，与其使用公式手动填充 J 列和 I 的每一行，不如使用数组公式，该公式将根据包含数据的行"自动"扩展/收缩。

1. 删除J列和I列中每行>9的所有公式(空白和非空白单元格)。
2. 在单元格J10中，将此公式
   插入=arrayformula(IF($C10:$C="";"";HYPERLINK($J$1&$A10:$A&$J$2&$B10:$B;$J$3)))
3. 在单元格 I10 中，将此公式
   插入=arrayformula(IFERROR(VLOOKUP($C10:$C;'Geprüfte Ticker'!$C:$P;14;FALSE);""))

---

此代码解决Sheet1方案。

该代码在技术上是准确的，并且可以工作，但OP可能希望考虑另外两个问题。

1. 电子表格"每天由多个用户同时处理"。在 A 列或 B 列中检测到的空白单元格(作为删除 while 行的指示器)存在一种风险，该单元格只是临时的 - 用户可能打算更正拼写或输入新数据。删除行的"理想"基础是检测 A 列 ** 和 ** B 列中的空白单元格，但这并不总是可行的。

2. 即使只有测试数据，电子表格的重新计算时间也很长 - @around 10+ 秒 - 实时电子表格的时间是未知的。目前尚不清楚此代码对重新计算有何影响。

---

function onEdit(e) {
// setup spreadsheet and sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var tsName = "onedittest";
var targetsheet = ss.getSheetByName(tsName);
// list event data
// Logger.log(JSON.stringify(e)); //DEBUG
//get the formukla ranges and formulas
// set 1 = Column C-H
// set 2 = Column N-P
// Column Q = arrayformula - leave as is
var rR1 = targetsheet.getRange("C12:H12");
var fR1 = rR1.getFormulasR1C1();
var rR2 = targetsheet.getRange("N12:P12");
var fR2 = rR2.getFormulasR1C1();
// collect event data
var editR = e.range.getRow();
var editRS = e.range.rowStart;
var editRE = e.range.rowEnd;
var editC = e.range.getColumn();
var editCS = e.range.columnStart;
var editCE = e.range.columnEnd;
//Logger.log("DEBUG: edited rowstart = "+editRS+", rowend = "+editRE+", columnstart = "+editCS+", columnend = "+editCE);
//Logger.log("DEBUG: edited row = "+editR+", edited column = "+editC);
var editedsheet = e.range.getSheet().getSheetName();
// Logger.log("DEBUG: sheet name = "+editedsheet);
if (editedsheet === tsName  && editC>= 1 && editC<= 2 && editR>12) {
// this range/cell is a trigger
// Logger.log("DEBUG: match");
var editCell = targetsheet.getRange(editRS, editCS);
// Logger.log("DEBUG: the edited cell is "+editCell.getA1Notation());
//Logger.log("DEBUG: the old value = "+e.oldValue+", and the new value = "+e.value);
//now split Logic#1 and Logic#2
// Logic#1 = AND there is data in the edited cell (that is, the edit did not make the cell blank), 
// then copy (or recopy) the formulas from row 12 Columns C-H and N-Q onto the edited row.
if (editCell.length !=0){
// copy formulas
// Logger.log("DEBUG: copy the formulas");
var formulaset1 = targetsheet.getRange(editRS,3,1,6);
formulaset1.setFormulasR1C1(fR1);  
// Logger.log("DEBUG: set formulas for range 1")
var formulaset2 = targetsheet.getRange(editRS,14,1,3);
formulaset2.setFormulasR1C1(fR2);  
// Logger.log("DEBUG: set formulas for range 2")
// Sort the data
// get the number of rows of data
var lastheaderRow = 11;
var Avals = ss.getRange("A12:A").getValues();
var Alast = Avals.filter(String).length;
//Logger.log("DEBUG: Number of rows of data = "+Alast+", so last row = "+(Alast+lastheaderRow));
// define the sort range
var sortRange = targetsheet.getRange(lastheaderRow, 1, Alast+1, 17);
// Logger.log("DEBUG: the range = "+range.getA1Notation());
// sort by Column C, ascending
sortRange.sort({column: 3, ascending: true});
}
else{
// Logic#2 - delete the row
// Logger.log("DEBUG: delete the row");
targetsheet.deleteRow(editRS);
}      
}
else
{
//not the right sheet, not the column, not the right row
// Logger.log("DEBUG: do nothing - not matched");    
}
}