match 함수의 -1 인수의 의미 (셀병합상태에서 수식)

역시 네이버 지식인에서 누군가가 올려주신 해답에서 연구해볼만한 것입니다.

이렇게 데이타가 있을 때

이런 식으로 합계를 내야합니다.

문제는 G,H,I열이 셀병합이 되어있다는 점입니다. 셀병합이 안되어있다면 SUMIFS함수로 간단하게 해결됩니다만....

셀병합을 하면 제일 첫셀(여기서는 G3과 G12)에만 데이타가 들어있고, 나머지 병합된 부분에는 아무 데이터도 들어있지 않습니다. 

그러니 평소처럼 SUMIFS함수를 쓰게 되면 G3,G12외에는 아무 데이터도 없으므로 오류가 나게 됩니다. 즉, 이 상태에서는 SUMIFS함수를 쓸 수 없다는 뜻입니다.

(그래서 엑셀에서 수식을 단순하게 만드려면 셀병합은 가급적 피하는 것이 좋습니다.)

여기서 어떤 분이 답을 달았습니다.

이렇게하면 셀병합을 해제하지 않아도 답을 구할 수 있습니다.

=SUMIFS(D:D,A:A,INDEX($G$3:G13,MATCH("",$G$3:G13,-1)),B:B,INDEX($H$3:H13,MATCH("",$H$3:H13,-1)))

이 수식의 핵심은 

INDEX($G$3:G13,MATCH("",$G$3:G13,-1))

이 부분입니다. 일단 INDEX함수는 주어진 범위안에서 두번째 인수값 만큼의 위치에 있는 값을 보여줍니다.

그리고 두번째 인수는 MATCH함수가 반환하는 값인데, 해당 행의 G열에 있는 셀값(여기서는 G13)이 빈칸이면 그 위쪽에서 빈칸이 아닌 셀(여기서는 G12)에 있는 텍스트를 가져오는 역할을 합니다.

아무튼, 여기서 

INDEX($G$3:G13,MATCH("",$G$3:G13,-1))

이 수식은 G3:G13의 범위 안에서 

MATCH("",$G$3:G13,-1)) 의 값만큼의 위치에 있는 값을 구합니다.

그리고

MATCH("",$G$3:G13,-1)

이 수식이 핵심중의 핵심인데요, 그 중에서 -1이라는 마지막 인수입니다.

1 또는 생략: MATCH는 lookup_value보다 작거나 같은 값 중에서 최대값을 찾습니다. lookup_array 인수 값은 오름차순(...-2, -1, 0, 1, 2,...A-Z, FALSE, TRUE)으로 지정해야 합니다.

0 : MATCH는 lookup_value와 같은 첫째 값을 찾습니다. lookup_array 인수 값은 임의의 순서로 지정해도 됩니다.

-1 : MATCH는 lookup_value보다 크거나 같은 값 중 최소값을 찾습니다. lookup_array 인수 값은 내림차순(TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., )으로 지정해야 합니다.

match 함수의 도움말을 찾아보면 마지막 인수에 대한 설명이 위와 같습니다.

즉, -1은 찾는 값(여기서는 빈칸)보다 크거나 같은 값 중 최소값을 찾으라는 의미입니다.

그러면, G3:G13의 범위 중에서 ""(빈칸)보다 크거나 같은 값 중 최소값은 무엇인가요?

보시면 G3에는 "주사위"가 들어가있고 G12에는 "구슬"이 들어가 있습니다.

상식적으로 생각하면 빈칸보다는 텍스트가 더 클 것 같습니다

아무 셀에나 

=""<"주사위"

이런 수식을 넣어보면 TRUE라고 나옵니다. 즉, 엑셀에서는 아무 텍스트라도 들어있는 것이 빈칸보다는 크다고 인식하고 있는 것입니다.

그러므로, -1이라고 인수를 주면 

MATCH("",$G$3:G13,-1)

위 수식에서 찾는 "G3:G13 범위에서 '빈칸'보다 크거나 같은 값"은 G3:G13범위 전체가 됩니다. (빈칸은 같은 값이니까 해당되고, 텍스트가 있는 셀은 빈칸보다 크다고 엑셀에서 인식하므로)

그러므로 그 중에서 최소값은 11이 되어야할 것인데(G13에 있는 빈칸은 G3,G12에 있는 텍스트보다 작은 값일테므로)

하지만 결과값은 10으로 나옵니다.

이 이유가 무엇인지를 곰곰히 생각해봤는데, 아직까지는 이런 추측을 할 수 밖에 없습니다.

일단, 빈칸은 크기 비교에서 제외하는 것 같습니다.

 

그리고 위에서 인수를 설명하는 부분에서 언급된 부분을 다시 보면, 

-1 : MATCH는 lookup_value보다 크거나 같은 값 중 최소값을 찾습니다. lookup_array 인수 값은 내림차순(TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., )으로 지정해야 합니다.

밑줄 친 부분에 주목합시다.

사실 이 상태에서 G,H,I열에 텍스트가 아니라 숫자를 넣으면 에러가 뜹니다.

그러므로, 여기서처럼 텍스트가 있는 상태에서는 엑셀에서는 내림차순으로 지정되어있다고 간주하는 것 같습니다. 

즉, 엑셀에서는  MATCH함수에서는 

빈칸은 크기 비교에서 제외하고, 

텍스트 중에서는 크기 비교를 하지 않고 내림차순으로 되어있다고 가정하여 계산하면

최소값은 맨 아래에 나오는 텍스트값이 됩니다. 

이 가정은 G3과 G12에 있는 텍스트를 서로 바꾸어보아도 결과값이 바뀌지 않는 것을 보면 아마 옳은 추정이 아닌가 합니다. (즉, MATCH함수에서는 텍스트사이의 크기를 실제 비교하지는 않는다는 의미)

셀병합을 유지한 상태에서 수식을 작성해야할 때 응용가능할 듯합니다.