Google Apps 스크립트를 처음 사용하고 셀이 배경색과 같은 특정 기준을 충족 할 때 셀 값을 합산하는 스프레드 시트를위한 함수를 만들고 싶습니다. 또한 다음과 같은 이유로 범위를 배열로 전달하고 A1 표기법 을 사용하고 싶지 않습니다 .
여기서 A1 표기법을 사용하는 함수를 찾았 습니다 . 문제는 주어진 셀에있을 때입니다.
=sumWhereBackgroundColorIs("white", "A1:A10")
오른쪽 셀에 값을 복사하면 결과가 다시 나타납니다.
= sumWhereBackgroundColorIs ( “white”, “A1 : A10” )
내가 갖고 싶은 동안
= sumWhereBackgroundColorIs ( “white”, “B1 : B10” )
그렇지 않으면 항상 입력 인수를 수동으로 수정해야 하며이 기능을 광범위하게 사용하기 때문에 이것을 피하고 싶습니다.
따라서 범위를 값의 배열로 전달하여 시도했습니다.
=sumIfBgColor(#ffffff, A1:A10)
function sumIfBgColor(color, range){
var x = 0;
for(var i = 0; i < range.length; i++){
for(var j = 0; j < range[i].length; j++){
var cell = getCell();
if(cell.getBackgroundColor() == color)
x += parseFloat(range[i][j]);
}
}
return x;
}
그러나 나는 내가 가진 것에서 시작하여 셀 (즉, Range 유형의 객체)을 얻는 방법을 모른다.
답변
@Jacob의 불가능에 대한 주장에 따르면, 나는 그것을 반박한다. (그러나 향상된 속도 덕분에)
사용하여 :
=sumIfBgColor("#ffffff", A1:A10, COLUMN(A1), ROW(A1))
다음 기능으로 원하는 것을 수행합니다.
/**
* Sums cell values in a range if they have the given background color
*
* @param {String} color Hex string of color eg ("#ffffff")
* @param {Array.Array} range Values of the desired range
* @param {int} startcol The column of the range
* @param {int} startrow The first row of the range
*
* @return {int} Sum of all cell values matching the condition
*/
function sumIfBgColor(color, range, startcol, startrow){
// convert from int to ALPHANUMERIC - thanks to
// Daniel at http://stackoverflow.com/a/3145054/2828136
var col_id = String.fromCharCode(64 + startcol);
var endrow = startrow + range.length - 1
// build the range string, then get the background colours
var range_string = col_id + startrow + ":" + col_id + endrow
var ss = SpreadsheetApp.getActiveSpreadsheet();
var getColors = ss.getRange(range_string).getBackgrounds();
var x = 0;
for(var i = 0; i < range.length; i++) {
for(var j = 0; j < range[0].length; j++) {
// Sometimes the cell background is eg 'white' rather than '#ffffff'.
// I don't know why - I think it's a bug.
// so we remove that inconsistency with colourNameToHex
// courtesy of Greg at http://stackoverflow.com/a/1573141/2828136
if(colourNameToHex(getColors[i][j].toString()) == color) {
x += range[i][j];
}
}
}
return x;
}
/**
* Takes a colour string and returns it to a hex string. If a non-matching string is
* passed, it will return the argument as is - for this situation it means that a
* hex string can be passed to it and be returned as is. This is not for production.
*
* @param {string} color Must be either a colour name or hex string of color eg ("#ffffff")
*
* @return {object|string} hex string of color eg ("#ffffff") or the argument given.
*/
function colourNameToHex(colour)
{
var colours = {"aliceblue":"#f0f8ff","antiquewhite":"#faebd7","aqua":"#00ffff","aquamarine":"#7fffd4","azure":"#f0ffff",
"beige":"#f5f5dc","bisque":"#ffe4c4","black":"#000000","blanchedalmond":"#ffebcd","blue":"#0000ff","blueviolet":"#8a2be2","brown":"#a52a2a","burlywood":"#deb887",
"cadetblue":"#5f9ea0","chartreuse":"#7fff00","chocolate":"#d2691e","coral":"#ff7f50","cornflowerblue":"#6495ed","cornsilk":"#fff8dc","crimson":"#dc143c","cyan":"#00ffff",
"darkblue":"#00008b","darkcyan":"#008b8b","darkgoldenrod":"#b8860b","darkgray":"#a9a9a9","darkgreen":"#006400","darkkhaki":"#bdb76b","darkmagenta":"#8b008b","darkolivegreen":"#556b2f",
"darkorange":"#ff8c00","darkorchid":"#9932cc","darkred":"#8b0000","darksalmon":"#e9967a","darkseagreen":"#8fbc8f","darkslateblue":"#483d8b","darkslategray":"#2f4f4f","darkturquoise":"#00ced1",
"darkviolet":"#9400d3","deeppink":"#ff1493","deepskyblue":"#00bfff","dimgray":"#696969","dodgerblue":"#1e90ff",
"firebrick":"#b22222","floralwhite":"#fffaf0","forestgreen":"#228b22","fuchsia":"#ff00ff",
"gainsboro":"#dcdcdc","ghostwhite":"#f8f8ff","gold":"#ffd700","goldenrod":"#daa520","gray":"#808080","green":"#008000","greenyellow":"#adff2f",
"honeydew":"#f0fff0","hotpink":"#ff69b4",
"indianred ":"#cd5c5c","indigo ":"#4b0082","ivory":"#fffff0","khaki":"#f0e68c",
"lavender":"#e6e6fa","lavenderblush":"#fff0f5","lawngreen":"#7cfc00","lemonchiffon":"#fffacd","lightblue":"#add8e6","lightcoral":"#f08080","lightcyan":"#e0ffff","lightgoldenrodyellow":"#fafad2",
"lightgrey":"#d3d3d3","lightgreen":"#90ee90","lightpink":"#ffb6c1","lightsalmon":"#ffa07a","lightseagreen":"#20b2aa","lightskyblue":"#87cefa","lightslategray":"#778899","lightsteelblue":"#b0c4de",
"lightyellow":"#ffffe0","lime":"#00ff00","limegreen":"#32cd32","linen":"#faf0e6",
"magenta":"#ff00ff","maroon":"#800000","mediumaquamarine":"#66cdaa","mediumblue":"#0000cd","mediumorchid":"#ba55d3","mediumpurple":"#9370d8","mediumseagreen":"#3cb371","mediumslateblue":"#7b68ee",
"mediumspringgreen":"#00fa9a","mediumturquoise":"#48d1cc","mediumvioletred":"#c71585","midnightblue":"#191970","mintcream":"#f5fffa","mistyrose":"#ffe4e1","moccasin":"#ffe4b5",
"navajowhite":"#ffdead","navy":"#000080",
"oldlace":"#fdf5e6","olive":"#808000","olivedrab":"#6b8e23","orange":"#ffa500","orangered":"#ff4500","orchid":"#da70d6",
"palegoldenrod":"#eee8aa","palegreen":"#98fb98","paleturquoise":"#afeeee","palevioletred":"#d87093","papayawhip":"#ffefd5","peachpuff":"#ffdab9","peru":"#cd853f","pink":"#ffc0cb","plum":"#dda0dd","powderblue":"#b0e0e6","purple":"#800080",
"red":"#ff0000","rosybrown":"#bc8f8f","royalblue":"#4169e1",
"saddlebrown":"#8b4513","salmon":"#fa8072","sandybrown":"#f4a460","seagreen":"#2e8b57","seashell":"#fff5ee","sienna":"#a0522d","silver":"#c0c0c0","skyblue":"#87ceeb","slateblue":"#6a5acd","slategray":"#708090","snow":"#fffafa","springgreen":"#00ff7f","steelblue":"#4682b4",
"tan":"#d2b48c","teal":"#008080","thistle":"#d8bfd8","tomato":"#ff6347","turquoise":"#40e0d0",
"violet":"#ee82ee",
"wheat":"#f5deb3","white":"#ffffff","whitesmoke":"#f5f5f5",
"yellow":"#ffff00","yellowgreen":"#9acd32"};
if (typeof colours[colour.toLowerCase()] != 'undefined')
return colours[colour.toLowerCase()];
return colour;
}
답변
참조 : http://igoogledrive.blogspot.com/2015/11/google-spreadsheet-sum-of-colored-cells.html
맞춤 함수에 매개 변수를 문자열로 전달하는 대신 다음 스크립트는 범위로 입력을받습니다.
/**
* @param {string} color String as background color to be searched for in sumRange
* @param {range} sumRange Range to be evaluated
* @return {number}
* @customfunction
*/
function sumColoredCells(color,sumRange) {
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 total = 0;
for(var i=0;i<bg.length;i++)
for(var j=0;j<bg[0].length;j++)
if( bg[i][j] == color )
total=total+(values[i][j]*1);
return total;
};
다음 스크린 샷을보십시오.
답변
다음의 작은 스크립트가 트릭을 수행합니다.
암호
function sumIfBgColor(color, range){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var getColors = ss.getRange(range).getBackgrounds();
var getValues = ss.getRange(range).getValues(), x = 0;
for(var i = 0; i < getValues.length; i++) {
for(var j = 0; j < getValues[0].length; j++) {
if(getColors[i][j].toString() == color) {
x += getValues[i][j];
}
}
}
return x;
}
설명
먼저 활성 스프레드 시트가 결정됩니다. 그런 다음 범위를 기준으로 값과 색상을 모두 가져옵니다. 값은 색상과 궁극적으로 합계를 반복하는 데 사용됩니다.
용법
예
나는 당신을 위해 예제 파일을 만들었습니다 : 배경을 기반으로 합