함수로 범위를 지정하는 방법 2가지(offset, index)

지식인 질문에 대한 답이다.

위의 표에서 사람이름 옆에 있는 날짜가

아래 표에서 각 사람 이름 아래에 있는 날짜 목록에 없으면 "O", 있으면 "X"를 표시하는 방법이다.

처음에는 다음과 같이 했다.

=IF(COUNTIF(OFFSET($H$1,0,MATCH(B2,$A$8:$C$8,0),100,1),A2),"X","O")

offset 함수의 구문은 다음과 같다.

OFFSET(reference, rows, cols, [height], [width])

이 중, 뒤에 height와 width를 이용한 방법이다. 평소에 이 옵션을 빈칸으로 주면 offset함수는 reference위치에서 rows만큼 아래로, cols만큼 오른쪽으로 간 위치의 셀값을 보여준다.

하지만 height와 width를 표시한 offset함수는 reference위치에서 rows만큼 아래로, cols만큼 오른쪽으로 간 위치에서 행의 크기는 height만큼, 열의 크기는 width만큼 확장한 범위를 나타낸다.

이 기능은 offset함수 단독으로 사용하면 에러가 나고, 이 범위를 이용해서 다른 함수와 함께 사용된다.

답을 작성하고 올리려고 하던 찰나, 다른 사람이 답변을 먼저 올린 것을 발견했다.

해당 수식은 다음과 같다.

=IF(COUNTIF(INDEX($A$9:$C$13,,MATCH(B2,$A$8:$C$8,0)),A2),"X","O")

이 사람은 index함수의 성질을 이용했다. index함수는 배열형과 참조형이 있는데, 배열형을 이용한 것이다.

 Index함수(배열형)의 구문은 다음과 같다.

INDEX(array, row_num, [column_num])

여기서 array는 배열이고, 그 뒤의 row_num, column_num 중 하나만 사용하면 배열의 전체 행이나 열이 하나의 배열로 반환된다.

참고로, row_num과 column_num 인수를 모두 사용하면 row_num과 column_num이 교차하는 셀의 값이 반환된다.

위의 수식에서

INDEX($A$9:$C$13,,MATCH(B2,$A$8:$C$8,0)

이 부분을 보면, array 부분은 $A$9:$C$13이고,  row_num는 주지 않았으므로,

MATCH(B2,$A$8:$C$8,0)의 결과에 의해 나오는 숫자에해당하는 열이 범위가 된다.

예를 들어 MATCH(B2,$A$8:$C$8,0)의 값이 2가 되었다면 

$A$9:$C$13 중에서 두 번째 열에 해당하는   $B$9:$C$13 의 범위를 반환한다.

이제 이 범위안에서 countif를 이용해 해당 날짜를 찾으면 된다.