如何使用变量避免为工作表中的所有列创建一组变量? [英] How do I use variables to avoid having to create a set of these for all of the columns in my sheet?

问题描述

我正在尝试让我的工作表在单元格更改时自动在两个方向上重新计算时间表中的一组日期.

I'm trying to get my sheet to automatically recalculate a set of dates within a schedule, in both directions, when a cell is changed.

代码工作正常，但是我需要添加更多列，并且我真的不希望复制/粘贴/查找/替换加载次数更多.我相当确定我可以使用变量来做到这一点(只需查找列标识符并将其以某种方式输入到代码中即可)，但是我不知道如何做.

The code works fine, but I need to add a bunch more columns and I'd really rather not copy/paste/find/replace a load more times. I'm fairly certain I can do this with variables (just looking up the column identifier and feeding that into the code somehow), but I don't know-how.

functJon onEdJt(e) {

var sh = e.source.getActJveSheet();
Jf(sh.getName() === 'Date Calculator' && e.range.getA1NotatJon() === 'C9' 
) 
{
sh.getRange("C10").setFormula("=WORKDAY(C9,+$C$3)");
sh.getRange("C11").setFormula("=WORKDAY(C10,+10)");
sh.getRange("C12").setFormula("=WORKDAY(C11,+$C$4)");
sh.getRange("C13").setFormula("=WORKDAY(C12,+$C$3)");
sh.getRange("C14").setFormula("=WORKDAY(C13,+10)");
sh.getRange("C15").setFormula("=WORKDAY(C14,+1)");
sh.getRange("C16").setFormula("=WORKDAY(C15,+$C$5)");
}
else Jf (sh.getName() === 'Date Calculator' && e.range.getA1NotatJon() 
=== 'C10' ) 
{
sh.getRange("C9").setFormula("=WORKDAY(C10,-$C$3)");
sh.getRange("C11").setFormula("=WORKDAY(C10,+10)");
sh.getRange("C12").setFormula("=WORKDAY(C11,+$C$4)");
sh.getRange("C13").setFormula("=WORKDAY(C12,+$C$3)");
sh.getRange("C14").setFormula("=WORKDAY(C13,+10)");
sh.getRange("C15").setFormula("=WORKDAY(C14,+1)");
sh.getRange("C16").setFormula("=WORKDAY(C15,+$C$5)");

理想情况下，代码应仅对工作表中的任意数量的列起作用"，因此，如果我添加更多的列，则无需添加更多的代码.

Ideally the code should then just "work" for any number of columns in the sheet, so I don't need to add more code if I add more columns.

更新 这是我正在尝试的示例(但它不起作用)-尝试在运行"set.Formula"函数之前检查活动单元格是否在特定列的第9行中:

Update Here's an example of what I'm trying (but it's not working) - attempting to check that the active cell is in row 9 of a specific column before then running the "set.Formula" functions:

function onEdit(e) {

  var sh = e.source.getActiveSheet();
  var col = e.source.getActiveSheet().getRange().getColumn();
  var row = e.source.getActiveSheet().getRange().getRow();

  if(sh.getName() === 'Date Calculator' && e.getRange('9',col)     ) 

推荐答案

事件对象
即使代码被编写为onEdit(e)，您也没有利用事件对象. 在此答案中，代码将返回已编辑单元格的新值以及范围.然后，将范围用于计算行，列和工作表的名称，并将其用于验证以及构建范围和setFormula

Event Objects
Even though the code was written as onEdit(e), you didn't take advantage of the Event Objects. In this answer, the code returns the new value of the edited cell and also the range. The range is then used to work out the row, column and sheet name and these is used for validation as well as for building the ranges and the setFormula

变量
该代码包括可用于数据输入(列C至列H)的有效列范围的变量，以及相应的输入行(行9和10).这些值表示为值，但是它们可以像假设和使用getValue在代码中获得的值一样容易地写入电子表格中. setFormula中使用的绝对单元格引用是部分可变的(列引用)，部分是硬编码的(相应的第3、4和5行).如果需要，行也可以是可变的.

Variables
The code includes variables for the valid range of columns that can be used for data entry (Column C to Column H), and respective input rows (rows 9 and 10). These are expressed as values, but they could just as easily be written into the spreadsheet as assumptions and the values obtained in the code by using getValue.
The absolute cell references used in the setFormula are partly variable (column reference) and part hard-coded (the respective rows-3,4 and 5). If desired, the rows could be variable as well.

效率
只有一个if语句包含一个用于构建setFormula的代码版本. 这可以通过设计if语句来实现:
1.如果工作表=日期计算器"且
2.如果editColumn在有效的ColumnStart和ColumnEnd值之间(列C到H)AND
3.如果editRow在有效的Row值之间(行9或10)AND
4.如果编辑的值不是空白(长度！= 0).

Efficiency
There is just one if statement containing one version of the code to build setFormula. This is achieved by designing the if statement:
1. if the sheet = "Date Calculator" AND
2. if the editColumn is between the valid ColumnStart and ColumnEnd values (Column C to H) AND
3. if the editRow is between the valid Row values (rows 9 or 10) AND
4. if the edited value isn't a blank (length != 0).

最后一个条件(已编辑的值为空白")确保删除单元格内容(和/或不具有任何值)，则代码将不会继续进行.

The last condition ("edited value is blank") ensures that if cell contents are been deleted (and/or have no value), then the code won't proceed.

将列号转换为字母
我使用了@AdamL编写的例程，该例程位于将列索引转换为相应的列字母；这会将列号转换为字母.它用于在Workdays中构建目标列"地址.对字母A-Z有效；有一个用于Z以外字母的版本.

Convert column number to letter
I used a routine written by @AdamL found at Convert column index into corresponding column letter; this converts a column number into a letter. It's used to build the "targetcolumn" address in Workdays. It's valid for the letters A-Z; there's a version for letters beyond Z.

清理
如果将数据输入给定列的第10行，则需要删除(同一列的)第9行中的任何值.该代码可以执行此操作，还可以删除下面各行中所有预先存在的公式日期，因此不会混淆数据条目得出的日期.

Cleanup
If data is entered into row 10 of a given column, then any value in row 9 (of the same column) needs to be deleted. The code does this and also deletes any pre-existing formula dates in the rows below so there is no confusion about the dates derived by the data entry.

function onEdit(e){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Date Calculator";
  var sheet = ss.getSheetByName(sheetname);

  // get the event source data
  var editedCell = e.range;
  var editRow = editedCell.getRow();
  var editCol = editedCell.getColumn();
  var eValue = e.value;
  var editedSheet = editedCell.getSheet().getName();
  //Logger.log("DEBUG: the cell = "+editedCell.getA1Notation()+", the column = "+editCol+", the row is "+editRow+", the value is "+eValue+", the edited sheet is "+editedSheet);

  // create some variables for column and row range
  var columnStart = 3; // Column C
  var columnEnd = 8; // Column H
  var rowOption1 = 9; // row 9
  var rowOption2 = 10 // row 10

  // create some variables for target cells
  var absolutecolumn = "C";
  //var absoluterow1 = 3; // not used
  //var absoluterow2 = 4; // not used
  //var absoluterow3 = 5; // not used

  // test for valid edit in row option 1 // Row 9
  if(editedSheet === sheetname && columnEnd >=editCol && editCol>=columnStart && rowOption2>=editRow && editRow>=rowOption1 && eValue.length !=0 ){

    //Logger.log("DEBUG: You got the right sheet, the edit is in the right range of columns and the edited row was =  "+rowOption1);

    if (editRow == rowOption2){
    // clear row 9
    sheet.getRange((+editRow-1),editCol).clear();
    } 

    // clear following 8 rows of data
    sheet.getRange((+editRow+1),editCol,8).clear();

    // set the targetcolumn as a letter
    var targetcolumn = columnToLetter(editCol);

    // set formula for row+1
    sheet.getRange((+editRow+1),editCol).setFormula("=WORKDAY("+targetcolumn+editRow+",$"+absolutecolumn+"$3)"); // 
    // set formula row +2
    sheet.getRange((+editRow+2),editCol).setFormula("=WORKDAY("+targetcolumn+(+editRow+1)+",+10)");
    // set formula row +3
    sheet.getRange((+editRow+3),editCol).setFormula("=WORKDAY("+targetcolumn+(+editRow+2)+",$"+absolutecolumn+"$4)");
    // set formula row +4
    sheet.getRange((+editRow+4),editCol).setFormula("=WORKDAY("+targetcolumn+(+editRow+3)+",$"+absolutecolumn+"$3)");
    // set formula row + 5
    sheet.getRange((+editRow+5),editCol).setFormula("=WORKDAY("+targetcolumn+(+editRow+4)+",+10)");
    // set formula row + 6
    sheet.getRange((+editRow+6),editCol).setFormula("=WORKDAY("+targetcolumn+(+editRow+5)+",+1)");
    // set formula row + 7
    sheet.getRange((+editRow+7),editCol).setFormula("=WORKDAY("+targetcolumn+(+editRow+6)+",$"+absolutecolumn+"$5)");

    // change the background to show entry in rowoption1
    sheet.getRange(editRow,editCol).setBackground("yellow");
    sheet.getRange((+editRow+1),editCol).setBackground("white");
  } 
}
function columnToLetter(column)
{
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

---

屏幕截图

---

Screenshot

这篇关于如何使用变量避免为工作表中的所有列创建一组变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！