짝수행 홀수행 따로 더하기 (SUMPRODUCT 응용)

설계변경 내역서를 작성해 보았다면 각 품목의 합을 구할때 일반내역서와 차이가 있음을 알 수 있습니다. 일반내역서의 경우 SUM 함수를 사용해서 범위만 지정하면 합을 구할 수 있습니다.

 

그런데 설계변경 내역서의 경우 위 아래의 값을 각각 따로 합을 구해야 하기 때문에 많은 분들이 + 를 사용하여 하나하나 셀을 선택해서 수식을 만들곤 합니다. 그나마 1개의 공종이 1페이지에 끝나는 경우는 작업하기가 좀 쉽죠!! 창호공사나 설비, 전기 공사인 경우에는 이야기가 달라집니다. 

하나하나 클릭하며 선택하다 보면 눈이 팽팽 돌아가십니다. 

수식이 맞나 확인 할땐 더욱 난감합니다. 

품목이 추가되는 경우에도 다시 + 해 줘야 합니다.

 

그래서 SUMPRODUCT함수를 사용합니다.

 

SUMPRODUCT함수는 배열수식함수입니다. 배열이라는 단어를 어디서 많이 들어는 봤는데 가물가물합니다. 그래서 도움말을 봤는데도 좀 어려운것 같습니다.

 

배열수식에 대한 설명은 SUMPRODUCT함수에서 다시한번 확인 하시구요. 설계변경 내역서에서 SUMPRODUCT함수를 어떻게 활용할지를 고민해 보도록 하겠습니다.

 

 

 

 

위 그림에서 변경전과 변경후의 합을 하기위해 몇가지 방법을 생각해보면 검정 글자색의 합과 빨간글자색의 합을 하거나, 짝수행 또는 홀수행의 합을 구하면 될것 같습니다. 저는 짝수행과 홀수행의 합으로 각각의 합을 구하겠습니다.

 

위 그림에서 보듯이 [합계] 에 검정색 부분과 빨간색 부분의 수식을 살펴보면

=SUMPRODUCT((MOD(ROW(F34:F57),2)=0)*(F34:F57))

=SUMPRODUCT((MOD(ROW(F34:F57),2)=1)*(F34:F57))

이렇게 적혀 있습니다.

 

짝수행의 합 : =SUMPRODUCT((MOD(ROW(F34:F57),2)=0)*(F34:F57))

이것을 글로 풀어 보면 

1) ROW() : 34에서 57까지 행번호를

2) (MOD(ROW(F34:F57),2)=0) : 2로 나눈 나머지가 0이면(짝수행이면) TRUE, 아니면 FALSE 값을 반환하고

3) (MOD(ROW(F34:F57),2)=0)*(F34:F57) : 짝수행은 TRUE, 홀수행은 FALSE 값을 금액과 곱하시고

4) SUMPRODUCT((MOD(ROW(F34:F57),2)=0)*(F34:F57)) : 3의 결과값을 모두 합합니다.

이렇게 하면 짝수행을 합하는 수식이 됩니다. 

홀수행의 합을 구하는 수식에는 나머지 값이 0이 아닌 1을 적습니다.

 

SUMPRODUCT01.xls

 

기타 사용된 함수에 대한 간략히 설명을 드리자면

MOD 함수는 나눗셈에서 나머지를 구합니다. =MOD(9,2) 는 9를 2로 나눈 나머지를 구합니다. 결과는 1입니다.

ROW 함수는 행번호를 나타냅니다. =ROW()라고 하면 수식이 있는 셀의 행번호가 반환됩니다.

 

사용함수 : SUMPRODUCT, MOD, ROW