병합된 셀의 개수 세기

1. 문제

병합셀 기준 countif.xlsx

0.01MB

 

 

아래와 같이 셀 병합이 됐을 때 셀의 길이(높이)를 알려면 어떻게 해야 할까요?

 

 

2. 해법

병합셀 다루기 정말 어렵죠?

 

가. 진행 주의 셀 수 알아내기

=OFFSET(A6,1,0)이라고 A6셀을 기준으로 한 행 아래 값을 구하려고 하면

"미 진행 주"가 아니라 A7셀이 돼서 0이 반환됩니다.

 

그러면 어떻게 해야 할까요?

 

Match 함수를 이용해 "미 진행 주"의 위치를 찾은 다음 빼기 1을 하면 됩니다.

 

먼저 '미 진행 주'의 위치를 찾기 위해

=MATCH("미 진행 주",A6:A25,0)라고 하면

11이 구해지는데, 이것은 '미 진행 주'의 처음을 검색해서 그런 것이므로 1을 빼면

진행 주의 개수는 10이 됩니다.

 

나. 미 진행 주의 셀 수 알아내기

미진행 주의 개수를 알아내려면

A열에서 빈칸("")이 시작되는 위치를 알아낸 다음, 미 진행 주의 위치를 빼면 됩니다.

 

A열의 빈칸 위치를 구하는 수식은

=MATCH("",A6:A26,0)라고 해서

A6셀에서 맨 아래 다음 셀인 A26셀까지 범위에서 빈칸을 찾으면 될 듯한데,

#N/A! 에러가 발생합니다.

 

따라서, =MATCH(TRUE,A6:A26="",0)라고

A6:A26중 빈칸인지 여부를 판단한 후 True의 위치를 찾으면 되는데 2가 나옵니다.

A7셀이 빈 셀이라 그렇습니다.

 

그러면 A6:A26을 B6:B26으로 수정하면 됩니다.

그러면 21이라고 맞는 값이 구해졌습니다.

 

이제 21에서 11을 빼면 10개이므로

=MATCH(TRUE,B6:B26="",0)-MATCH("미 진행 주",A6:A25,0)라고 하면 됩니다.

 

다. 진행 주에서 판매 개수가 1 이상인 비율 구하기

진행 주의 범위에서 판매 개수가 1 이상인 것의 개수를 구할 때

먼저 진행 주에 해당하는 판매 개수의 범위를 알아야 하는데,

이것은 Offset함수를 이용해 구할 수 있습니다.

 

Offset 함수의 인수는 참조셀, 이동할 행 수, 이동할 열 수, 높이, 너비의 4가지이며,

이 중 앞의 2가지 인수는 필수입니다.

 

그런데 위에서 구한 진행 주의 길이(높이) 10은 위 인수에서 높이에 해당합니다.

 

따라서, 수식은

=offset(c6,,, MATCH("미 진행 주",A6:A25,0)-1)이 됩니다.

이동할 행 수와 열 수는 꼭 필요하지만 입력하지 않으면 0이 됩니다.

 

위 수식은 c6에서 10개의 셀을 가져오므로

C6:C15셀이 됩니다.

 

이렇게 구하는 것은 병합 셀의 크기(높이)가 달라질 수 있기 때문입니다.

 

이제 범위 내에서 1개 이상인 것은 개수는

=countif(offset(c6,,,MATCH("미 진행 주",A6:A25,0)-1),">=1")

로 구할 수 있습니다.

 

700%로 표시되는데, 이것은 백분율 표시 때문에 그런 것이고 7입니다. 

1 이상을 세어 보니 7 맞습니다.

 

이제 전체 개수로 나누면 되는데,

전체 개수는 (MATCH("미 진행 주",A6:A25,0)-1)입니다.

 

따라서, 비율은

=countif(offset(c6,,,MATCH("미 진행 주",A6:A25,0)-1),">=1")/(MATCH("미 진행 주",A6:A25,0)-1)

입니다.

 

7/10 = 70% 맞습니다.

 

 

라. 진행 주에서 판매 개수가 3 이상인 비율 구하기

위 수식에서 >=1을 >=3으로만 수정하면 됩니다.

=countif(offset(c6,,,MATCH("미 진행 주",A6:A25,0)-1),">=3")/(MATCH("미 진행 주",A6:A25,0)-1)

 

3 이상이 4건이므로 40% 맞습니다.

 

병합셀 기준 countif(완성).xlsx

0.01MB