구글시트에서 셀에 배경 색상을 입혀서 보기 편하게 하는 경우들이 있습니다. 이럴때 구글시트 셀 색상의 숫자와 합계를 내고 싶을 때는 어떻게 해야 할까요?
구글시트에서 구현 가능한 기능입니다.
구글 앱스크립트 기능을 이용하거나 확장 프로그램을 사용하는 방법이 있습니다.
여기서는 구글시트 앱스크립트 기능으로 색상별 합계와 갯수를 구하는 방법을 알아보겠습니다.
저는 적금풍차 내용을 기입할 때 필요했습니다.
주요기능
앱스크립트, 조건부서식, 체크박스
준비물
구글시트를 하나 새로 만듭니다. 아래와 같이 데이터를 입력합니다.
100000원과 50000원 금액을 입력하고 각 배경색을 달리해줍니다.
이때 조건부서식을 사용하여 해당 금액을 입력하면 해당 배경색이 나오게 해줍니다.
셀 색상별 합계, 갯수 구하는 함수 만들기
셀 색상별 합계와 갯수를 구하는 나만의 함수를 앱스크립트를 사용하여 만드는 과정입니다.
코딩하는 것 아니야? 라고 어려워하실 필요없습니다. 아래 내용대로 한번 따라해보세요.
앱 스크립트 메뉴로 이동합니다. 상단 주 메뉴에서 확장 프로그램>앱 스크립트를 선택합니다.
앱 스크립트 메뉴로 이동하면 아래 화면 처럼 나옵니다.
1.색상별 합계 구하는 스크립트
오른쪽 코드 입력창에 있던 내용을 지운 후 색상별 합계를 구하는 코드를 붙여 넣습니다.
function sumColor에서 sumcolor는 이 함수 스크립트를 호출하는 이름입니다. 기억해두시고요, 다른 이름으로 변경해도 됩니다. 이후 구글시트에서 같은 이름으로만 적으면 됩니다.
저장 버튼을 누릅니다.
그러면 디버그 옆에 sumcolor라는 것으로 이름이 변경되었습니다.
앱스크립트 프로젝트 이름도 원하는 것으로 변경합니다. 알아보기 쉽게만 정하면 됩니다. 이 함수를 불러올때 사용하는 이름은 아닙니다.
2.색상별 갯수 구하는 스크립트
이제 갯수를 구하는 스크립트도 이 메뉴에 들어온 김에 같이 만들어보겠습니다.
파일>+버튼을 누르면 메뉴가 나옵니다. 여기서 스크립트를 선택합니다.
그러면 새롭게 스크립트를 입력하는 창이 오른쪽 화면에 뜹니다.
오른쪽 창에 갯수를 구하는 함수를 입력합니다. 함수 호출 이름은 countcolor로 했습니다.
입력 후 저장 버튼을 누릅니다.
이번에도 저장버튼을 누르니 coountcolor라고 함수명이 변경되었습니다.
이제 앱스크립트 창은 닫고 구글시트 화면으로 이동합니다.
색상별 합계, 갯수 구하기
이제 입력한 데이터가 있는 구글시트에서 아래화면처럼 함수를 입력하면 됩니다.
갯수를 구하는 함수 호출명은 위에서 countcolor로 정한것 기억하시죠?
다른 이름으로 변경했으면 여기서 그 이름을 적어야합니다.
countcolor(범위,색상)이렇게 입력하면 됩니다.
7개 갯수를 잘 찾았지요?
합계는 sumcolor(범위,색상)을 하면 됩니다. sumcolor(A1:B20,D2)이렇게 하면 됩니다. 합계도 잘 구했네요.
노란 색상도 각각 똑같은 방법으로 함수를 입력하면 됩니다.
이번에는 새로운 데이터로 50000원을 입력 후 엔터를 치니 새로운 데이타가 들어와서 아래화면처럼 데이터를 새로 가져옵니다.
그리고나서 아래 화면처럼 노란색 갯수와 합계를 잘 가져옵니다.
여기서 해당금액을 입력하면 해당 배경색이 나오게 미리 조건부 서식에서 지정한 경우입니다.
데이터 리프레쉬 갱신하기
위의 데이터는 해당 금액에 대한 배경색을 지정 한 후 입력하는 방법이었습니다.
하지만 때로는 데이터를 입력 후 해당 데이터에 색상을 입힐수도 있습니다. 완료의 의미로 말이죠.
아래와 같이 먼저 데이터를 입력 후 완료 되었을 때 배경색을 선택한다고 가정하겠습니다.
색상을 변경후 합계와 갯수 함수를 입력하니 처음에는 마찬가지로 잘 계산해줍니다.
그런데 이번에 50000원을 노란색으로 변경했는데 아무런 변화가 없습니다. 데이터의 변화가 아니라서 함수가 새롭게 자동 갱신이 되지 않습니다.
어떻게 해야 할까요?
데이터를 자동갱신하는 방법을 찾느라고 시간을 많이 보냈는데요, 해결책을 찾았습니다.
바로 체크박스 기능을 사용하는 것입니다. 체크박스 기능은 어떤 기능을 선택하거나 취소하거나 해서 해당 데이터를 다시 갱신, 리프레시 해서 가져올 수가 있습니다.
노란 색상에 체크박스를 삽입하니 화면처럼 로드중이라고 나오죠?
우리가 갯수나 함수를 구할때 색상란을 참고하라고 해서 거기의 데이터가(true,false)로 변경되기에 해당 함수가 갱신되는 것입니다.
노란색 데이터가 2개, 10만원으로 합계가 잘 내주었습니다.
마찬가리로 초록색 데이터도 해봅니다.
한번 더 100000원 데이터에 녹색을 칠해봅니다. 그후 체크박스를 클릭하니 화면처럼 새로 데이터를 받아옵니다.
체크박스를 선택, 해제 때마다 데이터를 가져옵니다.
이제 색상을 변경후 체크박스만 클릭해주면 데이터가 갱신됩니다.
색상이 많다면 각각 해줘야 하는 번거로움이 있지만 현재로서는 이 방법이 제일 간편하지 않나 싶습니다.
앱스크립트와 주의점
색상별 합계와 갯수를 구하는 스크립트를 아래에 적습니다. 그대로 복사해서 붙이면 됩니다.
주의할점은 function 옆에 함수 호출 이름을 제외하고는 다른것은 수정하면 안됩니다.
이점 참고하시고요.
1.합계 내기
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;
};
2.갯수 구하기
function countColor(countRange,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 colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};