수식 계산 도구를 이용해 수식의 논리 오류 찾아 수정하기

안녕하세요.

 

구독자가 질문을 하셨는데, 여러분과 함께 보면 좋은 내용이라 소개합니다.

 

실습 파일 다운로드

sumproduct함수.xlsx

0.01MB

완성 파일 다운로드

sumproduct함수_완성.xlsx

0.01MB

 

보내신 메일 내용입니다.

선생님,
 
안녕하세요. 선생님의 책으로 열심히 엑셀공부하고 있는 홍**입니다.
 
오늘은 sumproduct라는 함수에 부딪쳤습니다.
선생님 포스트의 영상을 보고 이해는 했는데, 
첨부파일 보시면, 시트 두 개에서 항목 조건이 일치할 경우, 2개의 열에 있는 합을 시트1으로 가져오는 겁니다.
어찌어찌하여 결과는 얻어냈는데 왜 이렇게 되는건지 이해가 되지 않습니다.
혹시 설명을 좀 부타드려도 될까요?
 
매번 도움을 요청드려 송구합니다.
 
홍** 드림

실습 파일은 보내신 파일에 내용을 조금 추가했습니다.

 

Sheet1 시트 [B2] 셀에 수식이 작성되어 있습니다.

=SUMPRODUCT((Sheet1!$A$2:$A$5=A2)*(Sheet2!$B$2:$C$5))

SUMPRODUCT 함수를 이용해 Sheet1 시트의 항목과 일치하는 Sheet2 시트의 기증 및 폐기의 합계를 구하는 수식으로 보입니다.

 

Sheet2 시트는 이렇게 구성되어 있습니다.

이상한 점은 작성된 수식의 첫번째 조건 부분이 Sheet2 시트의 A2 셀과 비교하는 것이 아니라 Sheet1 시트 A2 셀과 비교하는 것으로 작성되어 있다는 것입니다.

(Sheet1!$A$2:$A$5=A2)

 

배열 수식이라 수식이 어떻게 실행되어 결과값이 구해지는지 머리속에서 떠올려보는 것에 한계가 있습니다.

이럴 때 수식을 단계별로 진행시켜 결과를 구하는 과정을 보여주는 기능이 있습니다.

 

바로 [수식 계산] 기능입니다.

 

커서를 Sheet1 시트 [B2] 셀에 두고 [수식]-[수식 분석]-[수식 계산]을 선택합니다.

[수식 계산] 대화상자가 나타납니다.

아래쪽 [계산] 단추를 누를 때마다 한 단계씩 수식을 진행합니다.

참조한 셀의 값을 가져와 값을 보여 줍니다.

첫번째 괄호 안 수식이 계산되어 논리 결과값이 표시됩니다.

이제 두번째 괄호 안의 수식이 처리됩니다.

역시 참조한 셀 값을 가져와 표시합니다.

앞 괄호의 논리식은 4개이고, 뒷 괄호의 숫자는 8개입니다.

논리식 1개에 숫자 2개를 곱하기 연산하여 결과를 구합니다.

아래에 [다시 시작] 단추가 표시되었다면 모든 수식을 처리되어 최종 결과가 표시되었다는 의미입니다.

 

[수식 계산] 도구를 이용해 단계별로 수식을 진행했는데, 별다른 문제점을 찾을 수 없습니다.

[닫기]를 눌러 [수식 계산] 대화상자를 닫습니다.

 

[C2] 셀에 커서를 두고 Sheet2 시트의 값과 비교하는 형식으로 수식을 작성합니다.

=SUMPRODUCT(($A$2:$A$5=Sheet2!A2)*(Sheet2!$B$2:$C$5))

채우기 해 보니 결과가 똑같습니다.

Sheet1 시트 [A2] 셀과 비교하는 수식이나 Sheet2 시트 [A2] 셀과 비교하는 수식의 결과가 똑같습니다.

 

[B2] 셀 수식은 문제 없는 수식일까요?

결과적으로 이야기하자면 [B2] 셀 수식은 잘못된 수식입니다.

특정한 상태에서만 제대로 된 결과를 나타내는 것 뿐입니다.

 

[B2] 셀 수식의 의미는 항목의 '국어책', '영어책', '수학책', '미술책'에 해당하는 Sheet2 시트의 합계값을 구하는 것이 아니라 Sheet2 시트의 행별 합계를 구하는 수식입니다.

 

Sheet2 시트를 복사해 Sheet3 시트를 만들고, [A2] 셀을 '영어책', [A3] 셀을 '국어책'으로 바꿨습니다.

 

Sheet1 시트 [B8] 셀 수식을 작성했습니다.

조건식에 Sheet3 시트 [A2] 셀을 비교하도록 작성했습니다.

=SUMPRODUCT(($A$8:$A$11=Sheet3!A2)*(Sheet3!$B$2:$C$5))

[C8] 셀에 [A8] 셀을 조건식에 사용하는 수식을 작성했습니다.

[B8] 셀 결과와 [C8] 셀 결과가 다르게 표시됩니다.

[B8] 셀 수식은 항목이 '국어책'인 합계값을 구하는 수식이고, [C8] 셀 수식은 참조 셀 범위 첫번째 행의 합계값을 구하는 수식입니다.

 

수식을 작성할 때 문법적인 오류가 발행하거나 실행될 때 오류가 발생하면 엑셀이 오류가 발생했다는 것을 표시해 줍니다.

수식에 논리적인 오류가 있는 경우는 엑셀이 알 수 없습니다.

첫번째 행의 합계를 구하는 것인지, 항목별 합계를 구하는 것인지, 사용자의 의도를 알 수는 없기 때문입니다.

 

우연히 Sheet1 시트의 항목 나열 순서와 Sheet2 시트의 항목 나열 순서가 같았기 때문에 [B2] 셀 수식이 제대로 된 결과를 나타냈지만, 다른 경우였다면 잘못된 값을 구했을 겁니다.

 

수식을 작성할 때 결과값이 나왔다는 것만 보고 제대로 만들어졌다고 생각하지 말고, 올바른 결과인지 확인해야 합니다.

 

필요하시는 분께 도움이 되길 바랍니다.