Как посчитать ячейки по цвету с определенным условием?

Question

[twdr]

Допустим мы ведем отчет выполнения заданий, в конце месяца нужно посчитать сколько раз определенный человек не уложился в дедлайн.


До этого просто считал количество ячеек по цвету:
/**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* return {number}
* @customfunction
*/

function countColoredCells(countRange,colorRef) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formula = activeRange.getFormula();

var rangeA1Notation = formula.match(/\((.*)\;/).pop().replace(/\s/g, "");
var range = activeSheet.getRange(rangeA1Notation);
var bg = range.getBackgrounds();
var values = range.getValues();

var colorCellA1Notation = formula.match(/\;(.*)\)/).pop().replace(/\s/g, "");
var colorCell = activeSheet.getRange(colorCellA1Notation);
var color = colorCell.getBackground();

var count = 0;

for(var i=0;i for(var j=0;j if( bg[i][j] == color )
count=count+1;
return count;
};

Как сделать countif по цвету?

Answer 1

[SpiceCrafter]

Делал так:

Скрипт
/**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* return {number}
* @customfunction
*/
function countColoredCells(countRange,colorRef) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formula = activeRange.getFormula();

var rangeA1Notation = formula.match(/\((.*)\;/).pop();
var range = activeSheet.getRange(rangeA1Notation);
var bg = range.getBackgrounds();
var values = range.getValues();

var colorCellA1Notation = formula.match(/\;(.*)\)/).pop();
var colorCell = activeSheet.getRange(colorCellA1Notation);
var color = colorCell.getBackground();

var count = 0;

for(var i=0;i for(var j=0;j if( bg[i][j] == color )
count=count+1;
return count;
};

Функция
=countColoredCells(B1:B9;A1)

B1:B9 - диапазон в котором считаем
A1 - ячейка с цветом, который считаем

У меня работала, но пришлось отказаться от подсчета цветных ячеек, т. к. считал большие диапазоны и всё зависало при каждом пересчете.

PS. Работает только с залитыми ячейками. Если применяете условное форматирование, не получится.

PPS. Будет лучше использовать подсчет чекбоксов
=COUNTIF(B1:B9; TRUE)