googlesheets = indirect()关于单元格范围 [英] google sheets =indirect() on range of cells

问题描述

我想知道是否有一个标准函数可以在一系列单元格上循环并在其上应用间接函数. 我正在尝试构建一张表格来按商店，月份和类别来支付我的费用，但是到目前为止，由于我的功能类似于

，因此很难扩展

=INDIRECT(""&$B$3&"!"&concat(A12,$C$1))+INDIRECT(""&$B$4&"!"&concat(A12,$C$1))+INDIRECT(""&$B$5&"!"&concat(A12,$C$1))+INDIRECT(""&$B$6&"!"&concat(A12,$C$1))

其中B3:B7是我的商店，并且读起来很容易:

=forEach(B3:B7, INDIRECT(""& B3:B7 &"!"&concat(A12,$C$1)))

之类的东西.

我尝试使用arrayFormula，但是在这种情况下不起作用，因为我没有尝试在2个相同大小的范围内应用函数，而是在单个值范围内进行查找

简洁的问题: 是否有现有功能，还是我必须尝试自己编写代码?

解决方案

我为我的特定问题找到了一个非常适合用例的解决方案. 发布是为了保持完整性，但仍然想知道是否还有其他更好的方法.

function indirectLoop(range, cellReference) {
  var currentCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var numberOfStores = range.length;

  var total = 0;

  var debugString = "";
  var currentStore;
  var storeSheet;
  var storeCell;
  for (var i = 0; i < numberOfStores; i++) {
    currentStore = range[i];
    storeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(currentStore);
    storeCell = storeSheet.getRange(cellReference);

    total = total + storeCell.getValue();
    //debugString += currentStore + ":" + storeCell.getValue() + "- ";

  }
  return(total);
}

I'm wondering if there is a standard function to loop over a range of cells and apply the indirect function on it. I'm trying to build a sheet to cover my expenses by store, month, and category, but as of yet it is hard to expand because I have a function that looks like

=INDIRECT(""&$B$3&"!"&concat(A12,$C$1))+INDIRECT(""&$B$4&"!"&concat(A12,$C$1))+INDIRECT(""&$B$5&"!"&concat(A12,$C$1))+INDIRECT(""&$B$6&"!"&concat(A12,$C$1))

where B3:B7 are my stores, and it would be a lot easier to read as:

=forEach(B3:B7, INDIRECT(""& B3:B7 &"!"&concat(A12,$C$1)))

or something of the like.

I've tried with arrayFormula, but that does not work in this case, as I'm not trying to apply a function on 2 same-sized ranges, but on a single range of values to find

concise question: is there an existing function, or will I have to try to code my own?

解决方案

I have found a very use-case specific solution for my specific problem. Posting for completeness, but still wondering if some else has something better.

function indirectLoop(range, cellReference) {
  var currentCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var numberOfStores = range.length;

  var total = 0;

  var debugString = "";
  var currentStore;
  var storeSheet;
  var storeCell;
  for (var i = 0; i < numberOfStores; i++) {
    currentStore = range[i];
    storeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(currentStore);
    storeCell = storeSheet.getRange(cellReference);

    total = total + storeCell.getValue();
    //debugString += currentStore + ":" + storeCell.getValue() + "- ";

  }
  return(total);
}

这篇关于googlesheets = indirect()关于单元格范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！