목적
예제 1 - 구글 시트에서의 Match 함수란?
구글 스프레드시트 또는 엑셀에서 사용하는 Match 함수는 지정한 범위 내에서 지정한 값의 위치를 숫자로 반환하는 함수입니다.
다음과 같이 매출표 1~3 시트와 관리자 시트를 포함한 총 4개의 시트가 있다고 가정하겠습니다.
"매출표1" 시트 내용.
"매출표2" 시트 내용.
"매출표3" 시트 내용.
"관리자" 시트 내용.
구글 시트에서 사용하는 match 함수의 문법은 다음과 같습니다.
=match("검색할 값","검색할 범위","검색 유형")
JavaScript
복사
즉 관리자 시트의 B2 셀에서 다음과 같이 입력할 경우 결과는 각각 다르게 나옵니다.
=MATCH($A$2,'매출표1'!A1:F1,0) // 결과값 5
JavaScript
복사
=MATCH($A$2,'매출표2'!A1:G1,0) // 결과값 6
JavaScript
복사
=MATCH($A$2,'매출표3'!A1:C1,0) // 결과값 3
JavaScript
복사
관리자 시트의 A2 셀에 다른 값을 입력하여 검색할 수도 있습니다.
그런데, "매출원가" 와 같이 매출표1, 매출표2 시트에는 있지만 매출표3 시트에는 없는 값을 입력할 경우 다음 함수는 "매출원가" 값을 찾을 수 없다는 이유로 오류를 출력하게 됩니다.
=MATCH($A$2,'매출표3'!A1:C1,0) // A2 셀의 입력값이 "매출원가"인 경우 오류가 반환됩니다.
JavaScript
복사
예제 2 - 스크립트에서 indexOf 함수 사용하기
예제 1과 같은 기능 구글 스크립트에서도 사용할 수 있는데, 이와 같은 기능을 사용할 수 있는 메소드가 바로 javascript 에서 사용하는 메소드 indexOf() 입니다.
indexOf() 메소드로 match 함수를 구현하는 과정을 알아 보겠습니다.
function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lc = ss.getLastColumn();
var lookupRangeValues = ss.getRange(1, 1, 1, lc).getValues();
Logger.log(lookupRangeValues);
}
JavaScript
복사
스크립트
활성화한 시트에 따라 결과가 달라지기 때문에, 아래와 같이 각 매출표 시트를 활성화할 때마다 실행 로그에는 서로 다른 값이 출력됩니다.
"매출표 1" 시트를 활성화한 상태에서 스크립트 실행 결과.
"매출표 2" 시트를 활성화한 상태에서 스크립트 실행 결과.
"매출표 3" 시트를 활성화한 상태에서 스크립트 실행 결과.
이제 indexOf() 메소드를 사용하여 스프레드시트의 match 함수를 구현해 보겠습니다.
function myFunction() {
var lookupValue = "매출액";
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lc = ss.getLastColumn();
var lookupRangeValues = ss.getRange(1, 1, 1, lc).getValues();
var index = lookupRangeValues.indexOf(lookupValue);
Logger.log(index);
}
JavaScript
복사
스크립트
변수 lookupValue 으로 지정한 "매출액"은 각 매출표 시트의 1행을 기준으로 이 범위에 포함된 값을 지정했기 때문에, 범위에 포함된 값만을 입력한다는 가정 하에 검색하기 원하는 값을 임의로 입력해도 됩니다.
스크립트 실행 결과.
그런데 스크립트를 실행하면 예상과는 달리 엉뚱한 값이 나오게 됩니다.
스크립트 문법에서는 오류가 없지만, getValues() 메소드로 얻은 값이 배열의 형태로 구성되므로 코드를 완성한 것이 아니기 때문에 출력에 문제가 생기는 것입니다.
배열의 형태에서 값을 추출해보기 위해 다음과 같이 코드를 재수정하여 실행해 보겠습니다.
function myFunction2() {
var lookupValue = "매출액";
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lc = ss.getLastColumn();
var lookupRangeValues = ss.getRange(1, 1, 1, lc).getValues()[0];
var index = lookupRangeValues.indexOf(lookupValue) + 1;
// 배열의 순번이 0부터 시작하기 때문에 값 1을 더합니다.
Logger.log(index);
}
JavaScript
복사
스크립트
"매출표1" 시트를 활성화한 상태에서 스크립트 실행 결과.
"매출표2" 시트를 활성화한 상태에서 스크립트 실행 결과.
"매출표3" 시트를 활성화한 상태에서 스크립트 실행 결과.
예제 3 - 스크립트에서 작성한 함수를 시트에서 활용하기
이제 예제 2에서 사용한 스크립트를 개선하는 과정은 물론, 개선하면서 실제로 스프레드시트에서 사용해 보는 방법을 알아보겠습니다.
function myFunction() {
var index = getColumnIndex("지역","매출표1");
Logger.log(index);
}
function getColumnIndex(label,sheetName) {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
var lc = ss.getLastColumn();
var lookupRangeValues = ss.getRange(1, 1, 1, lc).getValues()[0];
var index = lookupRangeValues.indexOf(label) + 1;
return index;
}
JavaScript
복사
스크립트.
함수를 추가하여 두 개의 입력값을 받는 함수 getColumnIndex() 는 index 변수를 리턴하고, 함수 myFunction() 에서 검색 값과 1행을 검색할 시트만 입력하여 실행 로그에 출력해 보겠습니다.
getSheetByName() 메소드가 적용되었기 때문에 더 이상 활성화한 시트에 따라 결과값이 달라지는 경우가 발생하지 않으며, 함수 myFunction() 에서 검색할 시트를 입력하면 됩니다.
스크립트 결과. 매출표 1 시트에서 "지역" 값이 1행 2열에 위치해 있기 때문에 2가 출력된다.
이제 작성한 스크립트에 의해 실제로 다음과 같이 시트에 입력할 경우 함수를 구현할 수 있게 됩니다.
= getColumnIndex("지역","매출표1")
JavaScript
복사
함수 getColumnIndex() 를 입력할 때는 알 수 없는 함수라고 하지만, 실제로는 구현됩니다.
