병합 셀의 첫번째 셀 값 찾기(1) - if와 Offset 함수

병합셀의 첫 셀 값 찾기1.xlsx

0.01MB

 
 아래 데이터를 이용하여

 

연도별, 지점별로 판매량과 판매액을 집계하는 것을 피벗 테이블과 SumIfs함수를 이용해 만들어 보겠습니다.

 

1.  피벗 테이블 이용

피벗 테이블 기능을 이용하면 쉽게 아래와 같이 만들 수 있는데,

 

열 레이블은 연도별로, 행 레이블은 지점별로 수정할 수는 있지만,

연도별까지 없앨 수는 없고, '합계 : 판매량'과 '합계 : 판매액'을 판매량과 판매액으로 수정하고 싶어도 '이미 사용 중인 피벗 테이블 필드의 이름입니다'라고 하면서 수정이 안되는 한계가 있습니다.

 

확인 버튼을 누른 후 Esc키를 눌러 원래 필드명으로 돌아옵니다.

 

2.  SumIfs 함수 이용

위와 같은 한계점을 극복하기 위해 SumIfs함수를 이용할 수 있으며 이 방법은 피벗 테이블에 비해 어려운 단점이 있지만, 원하는 형태로 만들 수 있는 장점이 있습니다.

 

[ 수식 붙여 넣기 시 문제점 ]

SumIfs 함수를 이용 해 수식을 만든 후 다른 셀에 복사할 때 연도가 병합되어 있다면 빈 셀을 참고하게 되고, 판매량과 판매액의 범위는 고정인데 위치에 따라 범위가 변해서 집계가 제대로 되지 않는 문제점이 있습니다.
 
① 병합셀의 빈셀을 참조해서 값이 구해지지 않음
연도별 판매량과 판매금액의 집계표에서 연도가 판매량과 판매액에 공통되므로 병합셀로 만들게 되는데

 
이럴 경우 SumIfs함수를 B3셀에 작성하고 C3셀에 수식을 복사하게 되면 B1셀을 참조하던 것이

 
C1셀을 참조하게 되는 문제점이 있습니다.

 
B열을 고정하기 위해 $B1로 고정하게 되면 C열까지는 문제가 없는데,
D열에서도 B1셀을 참고하게 되는 문제점이 있습니다.

 
② 더할 범위가 변해서 값이 구해지지 않음
또한 더할 범위도 B열에서 열로 이동함에 따라 원시 데이터의 D$2:D$13이 E$2:E$13으로 바뀌는 것은 좋은데,

 
D열과 E열의 경우는 원시 데이터의 F와 G열로 바뀌는 것도 문제입니다.

 
원시 데이터 시트를 보면 판매량은 D열, 판매액은 E열에 있으므로 이것이 고정이어야 합니다.

 
 

2. 병합 셀의 첫 번째 셀 참조하는 방법

 

가. 방법 1

B3셀의 수식에서 $B1을 아래로 복사할 때 행이 변하지 않도록 B$1로 수정합니다.
그리고, C3셀 복사하면 B$1이 C$1이 돼서 빈 셀을 참조하게 되므로 If함수를 이용해 값이 0일 때는 왼쪽 셀을 참고하도록 해야 합니다.

변경된 수식은 아래와 같습니다.
=SUMIFS('원시 데이터'!E$2:E$13,'원시 데이터'!$A$2:$A$13,IF(집계표!C$1=0,B1,C1),'원시 데이터'!$C$2:$C$13,집계표!$A3)

 
이때 집계표!는 현재 시트가 집계표이므로 지워도 됩니다. 지우는 방법은 Ctrl+H키를 눌러 찾을 내용에 집계표!라고 입력하고, 바꿀 내용은 공백으로 둔 다음 모두 바꾸기 버튼을 누르는 것입니다. 일괄적으로 바뀌므로 편리합니다.
모두 바꾸기 버튼을 누르면

바꾸기로 일괄 대체

 
8개가 바뀌었다는 메시지가 나오는데, B열부터 E열까지 2개씩 바뀌어서 8개입니다.

닫기 버튼을 눌러 창을 닫습니다.
 
 

나. 방법 2

두 개 셀이 병합됐을 경우는 위와 같이 빈 셀일 때 왼쪽 셀을 참조하도록 하면 되지만
3개일 경우는 값이 0이 경우를 조건으로 하면  3번째 셀이 빈 셀이면 무조건 이 조건이 충족돼서 왼쪽 셀인  공백이 되므로 0을 조건으로 하면 안 되고, 0보다 클 때로 바꿔서 0보다 클 때 그 셀을 참조하도록 해야 합니다.
 
또한 if 함수를 중첩해서 value_if_false에 if 함수를 다시 넣어야 합니다.

따라서, 수식은
IF(C$1>0,C$1,IF(B$1>0,B$1,A$1))이 됩니다.
C1셀의 값이 0보다 크다면 C1셀, B1셀의 값이 0보다 크다면 B1셀, B1셀의 값이 0이라면 A1셀을 참조하게 됩니다.
 
그런데 4개 셀이라면 다시 value_if_false안에 넣어야 하는데, A1셀의 왼쪽 셀은 없으므로 값을 지정할 수 없습니다. 이럴 때는 Offset함수를 이용해서 지정하면 됩니다. 또한 A1셀도 C3셀의 수식을  B2셀로  복사하면 에러가 발생하므로 마찬가지로 Offset함수를 이용해서 수정해야 합니다.
 
A1셀은 C1셀을 기준으로 하면 왼쪽으로 두 칸 이동해야 하므로 OFFSET(C$1,0,-2)이 되고, A1셀의 왼쪽 셀은 없지만 왼쪽으로 3칸 이동이므로 OFFSET(C$1,0,-3)이 됩니다.
 
따라서 완성된 수식은 복잡하지만 아래와 같습니다.
=SUMIFS('원시 데이터'!E$2:E$13,'원시 데이터'!$A$2:$A$13,IF(C$1>0,C$1,IF(B$1>0,B$1,IF(OFFSET(C$1,0,-2)>0,OFFSET(C$1,0,-2),OFFSET(C$1,0,-3)))),'원시 데이터'!$C$2:$C$13,$A3)

 
 
병합 셀이 다섯 개일 때는 더 복잡해집니다.
=SUMIFS('원시 데이터'!E$2:E$13,'원시 데이터'!$A$2:$A$13,IF(C$1>0,C$1,IF(B$1>0,B$1,IF(OFFSET(C$1,0,-2)>0,OFFSET(C$1,0,-2),IF(OFFSET(C$1,0,-3)>0,OFFSET(C$1,0,-3),OFFSET(C$1,0,-4))))),'원시 데이터'!$C$2:$C$13,$A3)
 
C3셀의 수식을 복사해서 B3셀에 붙여 넣습니다. 이때 C3셀의 채우기 핸들을 B3셀까지 끌어도 됩니다.

 
 ※ 위 수식에서 B$1도 A1셀에 복사할 경우 A1 왼쪽 셀이 없어서 #REF! 에러가 발생할 수 있으므로 병합 셀 중 첫번째 셀을 구하는 수식을 아래와 같이 수정하는 것이 바람직합니다.
IF(C$1>0,C$1,IF(OFFSET(C$1,0,-1)>0,OFFSET(C$1,0,-1),IF(OFFSET(C$1,0,-2)>0,OFFSET(C$1,0,-2),IF(OFFSET(C$1,0,-3)>0,OFFSET(C$1,0,-3),OFFSET(C$1,0,-4)))))
 

 

3. 더할 범위인 판매량과 판매액 범위 고정하기

 
가. 이름 지정하기
이름을 지정하면 오른쪽 또는 아래로 복사하더라도 셀 주소가 변경되지 않고 고정되게 됩니다.
 
원시 데이터 시트를 연 다음 판매량에 해당하는 데이터 범위인 D2셀부터 D13셀까지 선택하는데 D2셀부터 D13셀까지 마우스로 끌던가, D2셀을 클릭한 다음 Shift키를 누른 상태에서 Ctrl + ↓키를 눌러 선택합니다.

 
그리고, 왼쪽 위 D2라고 쓰인 셀 주소 표시란에 판매량이라고 이름을 입력합니다.

셀 주소에 간단하게 이름을 입력할 수 있습니다.

 
그리고, E2셀부터 E13셀까지는 판매액이라고 이름을 부여합니다.
 
그리고, 수식 탭의 이름 관리자 명령을 누르면

 
이름에 판매량과 판매액이 표시되고, 오른쪽에 참조대상이 표시되는데 좁으므로 폭을 넓혀도 되지만, 아래쪽의 참조 대상을 보면 ='원시 데이터'!$D$2:$D$13으로 되어 있습니다.

 
 
나. 수식 수정하기
B3셀의 수식
=SUMIFS('원시 데이터'!D$2:D$13,'원시 데이터'!$A$2:$A$13,IF(B$1>0,B$1,IF(A$1>0,A$1,IF(OFFSET(B$1,0,-2)>0,OFFSET(B$1,0,-2),IF(OFFSET(B$1,0,-3)>0,OFFSET(B$1,0,-3),OFFSET(B$1,0,-4))))),'원시 데이터'!$C$2:$C$13,$A3)
에서 '원시 데이터'!D$2:D$13를 판매량으로 수정하고,
 
C3셀의 수식
=SUMIFS('원시 데이터'!E$2:E$13,'원시 데이터'!$A$2:$A$13,IF(C$1>0,C$1,IF(B$1>0,B$1,IF(OFFSET(C$1,0,-2)>0,OFFSET(C$1,0,-2),IF(OFFSET(C$1,0,-3)>0,OFFSET(C$1,0,-3),OFFSET(C$1,0,-4))))),'원시 데이터'!$C$2:$C$13,$A3)
에서 원시 데이터'!E$2:E$13을 판매액으로 수정합니다.
 
 
값이 그대로입니다.

 
이번에는 B3셀과 C3셀의 수식을 복사해서 D3셀에 붙여 넣고,

 
B3셀부터 E3셀까지 마우스로 선택한 다음 E3셀의 채우기 핸들을 4행까지 끌면 모든 셀의 수식이 완성됩니다.

 
오른쪽 피벗 테이블과 비교하니 값이 맞습니다.
이제 홈 탭에서 ,(쉼표)를 눌러 1000 단위 구분 기호 ,를 삽입합니다. 수식이므로 피벗테이블과 달리 값이 변경되더라도 쉼표가 없어지지 않습니다.

 
아래와 같이 연도가 2개 이상일 경우에도 B3에서 C4셀을 복사해서 D3셀에서 G4셀까지 붙여 넣으면 되므로 문제없습니다.

 
또한 B3:C4의 범위를 선택한 후 C4셀의 채우기 핸들을 G4셀까지 끌어도 결과는 같습니다.
 
E3셀에 열을 삽입해서 병합 셀을 3개로 만들어도 판매액의 값은 동일합니다.

 

병합셀의 첫 셀 값 찾기2.xlsx

0.02MB