1. 문제
합계를 범위내에서 홀수인 행과 짝수인 행으로 나눠서 구하고 있는데, 중간에 소계가 있어서 여기에 숫자를 입력하게 되면 합계가 틀려지는 문제가 있으므로 소계인 경우에는 합계에서 제외하도록 해야 합니다.
(짝수인 행 합계 수식) =SUMPRODUCT((MOD(ROW(H$4:H$23),2)=0)*H$4:H$23)
2. 해결방법
가. Lookup 함수
그런데 문제는 셀인 병합되어 있어서 소계가 윗 셀에만 있고 아랫 셀은 비어 있다는 것입니다. 이럴 때 생각나는 것이 Lookup함수입니다.
Lookup 함수는 벡터형과 배열형이 있는데, 이 경우에 사용하는 것은 벡터형이며,
구문은 LOOKUP(lookup_value, lookup_vector, [result_vector])입니다.
찾을 값을 찾을 벡터에서 찾은 후 결과 벡터를 반환하는 것입니다.
예제) =lookup(4.19,A2:A6,B2:B6)
4.19를 A2셀에서 A6셀까지에서 찾아서, 일치하는 셀인 A3셀과 같은 위치의 B3셀 값이 주황색을 반환하는 것입니다. 이건 너무 단순한 경우입니다.
나. 셀이 비어 있는 경우 윗 셀값으로 채우기
N4셀에 =LOOKUP(ROW(C4:C23),ROW(C4:C23)/(C4:C23<>””),C4:C23)라고 입력하면 아래와 같이 C4:C23<>””로 빈 셀인지 체크해서 빈 셀이라면 윗셀 값으로 채웁니다. N5셀의 경우도 1, N13셀의 경우는 “소계”로 채웠습니다.
위 화면을 보면 Microsoft 365를 사용해서 결과값 영역에 파란 선이 둘러쳐져 있는데, 낮은 버전이라면 먼저 결과가 표시될 영역인 H4셀부터 H23셀까지를 선택한 다음 위 수식을 입력하고, Ctrl+Shift+Enter키를 눌러야 할 것입니다. 그러면 위 수식은 왼쪽과 오른쪽에 중괄호가 없는데, 낮은 버전의 경우는 중괄호가 표시될 것입니다.
다. SumProduct 함수를 이용한 수식 변경하기
(H24셀 수식 수정하기)
이제 H24셀의 수식에 위 Lookup함수를 이용한 수식을 추가하겠습니다.
(MOD(ROW(H$4:H$23),2)=0)라는 행이 짝수인 경우에 소계가 아닌 경우를 추가하면 됩니다.
따라서 조건은 (MOD(ROW(H$4:H$23),2)=0)*(LOOKUP(ROW($C$4:$C$23),ROW($C$4:$C$23)/($C$4:$C$23<>””),$C$4:$C$23)<>”소계”)가 됩니다.
기존 Lookup 수식에서 C4:C24 범위에 F4키를 눌러 절대 참조형식으로 수정했고, Lookup 수식 뒤에 <>”소계”를 추가해서 ‘소계’가 아닌 경우만 더하도록 했습니다.
전체 수식은
=SUMPRODUCT((MOD(ROW(H$4:H$23),2)=0)(LOOKUP(ROW($C$4:$C$23),ROW($C$4:$C$23)/($C$4:$C$23<>””),$C$4:$C$23)<>”소계”)H$4:H$23)
입니다.
이제 짝수행인 소계 H12셀에 숫자를 넣어도 합계가 달라지지 않는 것을 알 수 있습니다.
그러나 총합계는 조건이 없기때문에 달라집니다.
(H25셀 수식 수정하기)
H25셀의 수식에도 Lookup 수식을 이용한 조건인
*(LOOKUP(ROW($C$4:$C$23),ROW($C$4:$C$23)/($C$4:$C$23<>””),$C$4:$C$23)<>”소계”)
를 추가해서 수정하면
*(LOOKUP(ROW($C$4:$C$23),ROW($C$4:$C$23)/($C$4:$C$23<>””),$C$4:$C$23)<>”소계”)
=SUMPRODUCT((MOD(ROW(H$4:H$23),2)=1)(LOOKUP(ROW($C$4:$C$23),ROW($C$4:$C$23)/($C$4:$C$23<>””),$C$4:$C$23)<>”소계”)H$4:H$23)
가 됩니다.
(L열까지 수식 복사해서 붙여 넣기)
이제 H24셀과 H25셀의 수식을 복사해서 L26셀까지 붙여넣거나, H24셀과 H25셀을 선택한 다음 H25셀의 채우기 핸들을 L26셀까지 끌어도 됩니다.
라. 총합계셀 수정하기
(H27셀 수식 수정하기)
Sum을 SumProduct로 수정하고,
마찬가지로 “소계”가 아닌 조건식
(LOOKUP(ROW($C$4:$C$23),ROW($C$4:$C$23)/($C$4:$C$23<>””),$C$4:$C$23)<>”소계”)
을 추가하면 됩니다.
완성된 수식은
=SUMPRODUCT((LOOKUP(ROW($C$4:$C$23),ROW($C$4:$C$23)/($C$4:$C$23<>””),$C$4:$C$23)<>”소계”)*H4:H23)
입니다.
H27셀의 채우기 핸들을 L27셀까지 끌어서 수식을 복사합니다.