我正在尝试设置条件多列连接,已经接近成功。
其原理类似于SUMIF:您沿着一行/列运行,如果值与条件匹配,则从求和范围中取相应的值并将它们加在一起。不同之处在于这一次我们是在连接它们(即字面意义上的相加)!
下图显示了预期结果和实际结果,比我所能描述的更能说明问题。
我现在遇到的问题是,无论我输入什么作为最后一个参数,它都会给出一个没有任何空格的逗号(即使输入“#”也会给出逗号等)...
以下是代码:
/**
* Concatenates a range of cells where the condition is met.
*
* @param {A4:A6} cRange The dynamic cell range to test.
* @param {"Condition"} cCondition The string that the cell should match.
* @param {A4:A6} pRange The dynamic cell range to concatenate.
* @param {", "} interspace A string to insert between each entry.
* @return The a concatenated range of cells
* @customfunction
*/
function conditionalMultiConcat(cRange, cCondition, pRange, interspace){
var ss = SpreadsheetApp.getActiveSpreadsheet(); //get the active workbook
var sheet = ss.getSheets()[0]; //get the active sheet
//var values = sheet.getRange(pRange).getValues(); //debug line - uncomment to see the values
var values = "" //set the return value to blank
for(var cc = 0; cc < pRange.length; ++cc) { //For each value in the array
if ((cc == 0 || cc == pRange.length - 1) && (cRange[cc] = cCondition)) { //if it's the first or last value AND it matches our condition
values = values + pRange[cc].toString();// concatenate without interspace
}
else if (cRange[cc] = cCondition){ //else if it matches our condition then concatenate with the interspace
values = values + interspace + pRange[cc].toString();// concatenate
}
}
return values;
}