javascript - 在给出相应单元格值的列中选择单元格

Question

假设您在 Google 表格中有这样一个电子表格

你想对 Q:Q 中单元格的所有值求和 WHERE CORRESPONDING 单元格(即 在同一行)在列 D:D 中具有某些给定值..

现在，如果我们使用像 MySql 这样的数据库语言，我想这会很容易，您可能会指示类似的东西

SELECT cells in "Tot. / Anno" (Q:Q) WHERE "level" (D:D) is B or MID

就是这样，但我想在 Javascript 中不可能有这么直接的事情；所以恐怕需要一些解决方法。

那么，首先，表格背后的逻辑是什么？
在 level 下的 D:D 列中，您有四个选项可供选择，它们是:b、mid、pro 和顶部。 B代表basic，这里的逻辑是试图建立一个gradual budget。
有些费用是基本的，你无法避免，比如医疗、银行账户或电话；休闲、旅游等是次要的，可以省下。所以它们不是基本的，但很可能是 mid pro 甚至 top :)这就是背后的逻辑和值(value)尺度。

当您在 D2 中选择给定条件时，您应该根据相同的渐进逻辑在 G2 中获得匹配的 SUM:

b   in D2  -->  only sum cells in Q:Q where value is b
mid in D2  -->  only sum cells in Q:Q where value is b OR mid
pro in D2  -->  only sum cells in Q:Q where value is b OR mid OR pro
top in D2  -->       sum cells in Q:Q where value is b OR mid OR pro OR top

现在，关于编码，这是我找到的解决方法。 确实有效，但不够优雅。
我想听听一些更有效和正确的方法来处理这件事:我是初级水平，我相信有更有效的解决方案可用

// onEdit is meant to make sure that a real-time recalculation is triggered  
// at any change within the specified columns
function onEdit(e){

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var cell = sheet.getRange("Q2");
  var range = e.range;
  var columnOfCellEdited = range.getColumn();

  if (columnOfCellEdited === 4 || 17) {


// creating 2 different arrays from the two different comlumns (ranges)  
var range1 = sheet.getRange("D3:D100").getValues();
var range2 = sheet.getRange("Q3:Q100").getValues();  

// merging the two single arrays into one 2d array (you can't have one single 
// 2d array from the start because the two columns are not adjacent I guess)
var range=[],i=-1;
while ( range1[++i] ) { 
  range.push( [ range1[i][0], range2[i][0] ] );
}

// I tried to come up with a global SUM but I don't know why it does not work
// var valore_glob = range2.reduce(function(acc, val) { return acc + val; }, 0);

// as much filters as given values in the validation drop-down list: each  
// filter returns only the correspoding values

// FILTRO BASE - keeps only B values
var filt_bas = range.filter(function (dataRow) {  return dataRow[0] ===  'b';});
// splitting the 2d array into two singles arrays, we keep only the second  
// one for the follwing SUM
var yArray_bas = filt_bas.map(function(tuple) {    return tuple[1];});  
// summing up all the values within the resulting array
var valore_bas = yArray_bas.reduce(function(acc, val) { return acc + val; }, 0);

// FILTRO MID - keeps only MID values 
var filt_mid = range.filter(function (dataRow) {  return dataRow[0] ===  'mid';});
// splitting the 2d array into two singles arrays, we keep only the second  
// one for the follwing SUM
var yArray_mid = filt_mid.map(function(tuple) {    return tuple[1];});  
// summing up all the values within the resulting array
var valore_mid = yArray_mid.reduce(function(acc, val) { return acc + val; }, 0);

// FILTRO PRO - keeps only PRO  values  
var filt_pro = range.filter(function (dataRow) {  return dataRow[0] ===  'pro';});
// splitting the 2d array into two singles arrays, we keep only the second  
// one for the follwing SUM
var yArray_pro = filt_pro.map(function(tuple) {    return tuple[1];});  
// summing up all the values within the resulting array
var valore_pro = yArray_pro.reduce(function(acc, val) { return acc + val; }, 0);  

// FILTRO TOP - keeps only TOP values 
var filt_top = range.filter(function (dataRow) {  return dataRow[0] ===  'top';});
// splitting the 2d array into two singles arrays, we keep only the second  
// one for the follwing SUM
var yArray_top = filt_top.map(function(tuple) {    return tuple[1];});  
// summing up all the values within the resulting array
var valore_top = yArray_top.reduce(function(acc, val) { return acc + val; }, 0);   

var selector = sheet.getRange("D2").getValue();

switch (selector) {
  default:
    cell.setValue(valore_bas);
    break;
  case 'mid':
    cell.setValue(valore_bas + valore_mid);
    break;
  case 'pro':
    cell.setValue(valore_bas + valore_mid + valore_pro);
    break;
  case 'top':
    cell.setValue(valore_bas + valore_mid + valore_pro + valore_top);
}

  }
}

欢迎提出建议，谢谢。

学分:
Writing google Javascript similar to vlookup
Sum elements of an array with specific attribute
Spreadsheet Non-Adjacent Column data
How to find the sum of an array of numbers
How to filter an array of arrays?
Split a 2D array into single arrays

code.gs 中的最新工作实际代码: https://jsfiddle.net/John_Galassi/5ahrLmg9/

Answer 1

可以尝试使用内置函数

=iferror(sumproduct(regexmatch(D3:D, "^("&vlookup(D2, {"b", "b"; "mid", "b|mid"; "pro", "b|mid|pro"; "top", "b|mid|pro|top"}, 2, 0)&")$"), Q3:Q))

或者，根据您的语言环境

=iferror(sumproduct(regexmatch(D3:D; "^("&vlookup(D2; {"b"\ "b"; "mid"\ "b|mid"; "pro"\"b|mid|pro"; "top"\ "b|mid|pro|top"}; 2; 0)&")$"); Q3:Q))

或者，您可以在谷歌脚本中创建自定义函数:

function sumOnCondition(filter, checkrange, sumrange) {
var arr, ind;
arr = ["b", "mid", "pro", "top"];
ind = arr.indexOf(filter);
arr = (filter === "top") ? arr : arr.slice(0, ind + 1);
return checkrange.map(function(r, i) {
    return arr.indexOf(r[0]) > -1 ? Number(sumrange[i]) : 0;
}).reduce(function(a, b) {
    return a + b;
})
}

然后在电子表格中输入 G2

=sumOnCondition(D2, D3:D, Q3:Q)

或者，根据您的语言环境

=sumOnCondition(D2; D3:D; Q3:Q)

看看有没有帮助？