[스프레드시트] 색상별 셀값 합계 구하기 (Script 활용 - Sumcolor)

 

 

스프레드시트 색상별 셀값 합계 구하기

간편하게 스크립트를 활용해서 셀 색상별 셀 값을 계산할 수 있습니다.

 

스크립트를 이용해서 Google 시트 셀 색상별 셀 값 합계 구하기

 

1. 도구 > 스크립트 편집기 를 클릭 하여 프로젝트 창으로 이동

 

셀 색상별 셀 값 합계 구하기

 

2. 파일 > 새로 만들기 > 스크립트 파일 을 클릭하여 새로운 스크립트를 작성합니다.

 

새로운 스크립트 생성하기

 

3. 아래 코드를 입력하고 복사한 후 저장해주세요.

 

728x90

 

function SumColor(sumRange,colorRef) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var sumValues = activeSht.getRange(countRangeAddress).getValues(); 
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  var totalValue = 0;
  for (var i = 0; i < backGrounds.length; i++)
    for (var k = 0; k < backGrounds[i].length; k++)
      if ( backGrounds[i][k] == BackGround )
        if ((typeof sumValues[i][k]) == 'number')
          totalValue = totalValue + (sumValues[i][k]);
  return totalValue;
};

* 여기에서 SumColor 값은 불러오는 함수 네이밍이라 변경하시면 함수 불러올 때도 동일한 값으로 적어주세요

 

 

4. 이제 계산할 범위와 참조하는 색상값을 지정하여 계산해주시면 됩니다.

 

=sumcolor("계산할 범위값: 데이터 범위","계산하려는 특정 배경색이 있는 셀 지정")

 

 

구글스프레드 시트의 색상별 개수를 구하려면 아래 링크를 참고해주세요.

 

2020/12/19 - [공부한날/엑셀] - [스프레드시트] 색상별 개수 구하기 (Script 활용 - SumColor)

[스프레드시트] 섹상별 개수 구하기 (Script 활용 - SumColor)

 

 

 

---

 

22.03.04 추가

TypeError: Cannot read property 'pop' of null 발생할 경우 원인은 정확하지 않지만 스프레드시트의 동적 특성에 적합하지 않아 문자열로 하드 코딩이 되어서 오류가 발생하는거 같은데, 정확한 원인은 문제되는 시트를 봐야알 거 같네요. 우선 임시방편으로는 아래와 같이 진행을 해보세요. 반드시 신규 시트를 생성하여 테스트를 해보는 것을 권장합니다. 혹시 안될 수도 있어서요.

방법 1

쉼표 [ , ] 와 세미콜론 [ ; ] 를 서로 바꿔주세요. 

(/\((.*)\,/) -> (/\((.*)\;/)
(/\,(.*)\)/) -> (/\;(.*)\)/)

 

방법2

아예 다른 코드로 생성으로 다시 시도를 합니다. 호출 함수는 =sumColoredCells(범위,색상참조셀) 입니다. 제가 우선 임시로 했을때는 문제는 없네요. 

function sumColoredCells(sumRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula().toString();
  formula = formula.replace(new RegExp(';','g'),',');
  
  
  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 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;
};