1일1함수 (12) sumproduct함수 - 동점일때 다른 기준으로 순위매기기
오늘은 SUMPRODUCT함수를 다룹니다. SUMPRODUCT함수는 매우 다양한 문제의 해결을 위해 광범위하게 응용될 수 있는 함수입니다.
오늘은 석차를 낼때 사용하는 예를 들어 설명해 보겠습니다.
SUMPRODUCT함수는 다음에 이야기하게 될 "배열함수"와 깊은 관련이 있습니다. 영어로는 ARRAY인데, 학교 다닐때 "행렬"이라는 이름으로 배운 기억이 있는데 엑셀에서는 "배열"이라는 표현을 씁니다. 배열로 수식을 작성하는 것은 저도 아직 정확하게 이해는 하지 못했지만, 대충 감은 잡고 있는데, 그 감을 잡는데 이 SUMPROCUT함수가 도움이 되었습니다.
이 함수는 위에 있는 설명에도 있듯이, 주어진 배열을 모두 곱한 뒤에 그 합계를 반환하는 함수입니다.
(2,4) 라는 배열과 (3,5) 라는 배열이 있을때,
SUMPRODUCT({2,4},{3,5})라고 하면
2*3 + 4*5 를 계산하여 26을 반환합니다.
같은 식을 다음과 같이 표현할 수도 있습니다.
A1:A2 의 배열과 B1:B2의 배열이 * (논리곱)으로 연결되어있습니다. 이때의 계산식은 위에서 설명한 것과 같이,
입니다. 그런데, 다음처럼 + (논리합)으로 연결되면
를 계산하여 14를 반환합니다.
여기까지 SUMPRODUCT함수의 기본적인 설명이고, 이제 RANK함수를 이용해 순위를 구할때 유용하게 쓸 수 있는 방법을 알아봅시다.
가장 기본적인 방식입니다. 지난번 글에서 예로 든 방식이죠. 그런데 이 방식에서는 동점자일때 무조건 같은 순위로 표시합니다.
다음과 같이 수식을 써 봅시다.
그러면 보시는 것처럼 동점자수를 그 뒤에 괄호 속에 표시하게 됩니다. 그런데 모양이 약간 어색합니다. 동점자가 없을때는 아예 괄호를 쓰지 않는게 보통이지요. 그래서 다음과 같이 수식을 수정합니다.
그 결과는 위의 그림과 같습니다. 이제 제대로 된 모양 같네요 ^^ 하지만 명심할 것은, 모양을 보기 좋게 만들수록 수식은 점점 더 어려워진다는 것입니다. 실무자 입장에서는 편하게 수식을 만들고 싶고, 관리자입장에서는 보기 편했으면 좋겠고..... 갈등이죠 ^^
그런데, 두번째 기준으로 영어점수를 추가하여, 동점자 중에서도 국어점수가 높은 학생을 더 높은 순위로 주려면 어떻게 해야할까요? 이제 SUMPRODUCT함수가 등장합니다.
설명을 하기위해 학생들의 성적을 약간 바꾸었습니다. 학생3은 총점으로는 공동1위이지만 국어점수를 두번째 기준으로 주면 3위가 됩니다. 다음 수식을 씁니다.
잘 보시면 앞의 RANK(E4,$E$2:$E$5) 부분은 총점으로 순위를 구하는 수식인데, 그 뒤에 더하기기호(+)가 붙어있습니다. 그 뒤의 수식에서 동점자중에서 자신보다 국어점수가 높은 학생수를 구한 다음 순위에 더한다는 의미입니다. 즉, 이전의 RANK함수에서는 공동1위였던 학생3은 자신의 순위 1에다가, 총점이 자기와 같으면서 국어점수가 자기보다 높은 학생의 수 2(학생2,학생4)를 더하여 3위가 되는 것입니다.
SUMPRODUCT함수는 각 배열의 인수끼리 곱해서 더한다고 했죠?
위의 수식에서 인수는 ($E$2:$E$5=E2)와 ($B$2:$B$5>B2)) 의 배열에 붙은 수식 두 개입니다. 숫자가 들어있으면 글그대로 곱하고 더하면 되지만, 지금처럼 수식이 들어있는 경우는 배열의 각 셀마다 주어진 수식에 대한 논리값(참,거짓)을 구하여, 곱하기대신에 논리곱을, 더하기 대신에 논리합을 구합니다. 논리합과 논리곱에 대해서는 이전 글을 참고하세요.
여기서는 총점이 E2의 값과 같다는 조건과 국어점수가 E2보다 크다는 두 조건을 제시하고 있습니다. 각 배열에서 두 조건의 참,거짓을 따져봅시다.
총점이 있는 E열부터 보면,
총점이 275와 같다는 조건에 참인 것은 위와 같습니다.
두번째 조건인 국어점수가 B4보다 큰가는 조건에 대해서는
위와 같은 결과값이 나올것입니다.
앞의 수식
SUMPRODUCT(($E$2:$E$5=E4)*($B$2:$B$5>B4))
에서 두 조건 사이가 논리곱(*)으로 연결되어 있습니다. 이전의 글에서 알아본 것처럼, 논리곱일때는 두 조건이 모두 참일때만 참이 된다고 했습니다. 그러므로 최종적인 결과값은 다음과 같습니다.
결국 두 가지 조건을 다 만족하는 것은 학생2와 학생4의 두 사람 뿐임을 알 수 있습니다. 따라서,
의 수식에서는 2가 반환됩니다.
SUMPRODUCT함수는 제대로 이해하고 있으면 매우 유용합니다. 수식이 좀 길어져서 복잡해 보인다는 것이 단점이지만, 그것도 남들에게는 뭔가 엄청나게 대단한 것을 하는 능력자인것처럼 보이게 하는 효과를 거두기도 합니다.^^
p.s. 총점과 국어점수까지 같다면, 그 다음 조건으로 수학점수를 주고 싶다면 어떻게 할까요? 이 글을 쓰기 전에 연구를 해봤지만, 아직 답을 얻지 못했습니다. 뒤에 IF함수를 이용해 조건을 더 길게 주어야 할 것 같은데...... 아무튼, 실제 사용하기위해 함수식을 만들어야하는 상황은 아니므로, SUMPRODUCT함수를 이해하는데 도움이 되는 정도에서 동점자순위 구하기는 마치겠습니다.
2009/03/19 - [컴퓨터/엑셀] - 1일1함수(3) : 순위 매기기 rank 함수
2009/03/28 - [컴퓨터/엑셀] - 1일1함수(7) : AND 함수 OR 함수 (논리곱과 논리합)
2009/04/05 - [컴퓨터/엑셀] - 1일1함수 (11) COUNTIF 함수
주어진 배열에서 해당 요소를 모두 곱하고 그 곱의 합계를 반환합니다.
구문
SUMPRODUCT(array1,array2,array3, ...)
array1, array2, array3, ... 곱한 후 더할 값이 들어 있는 배열을 지정합니다. 2개에서 255개 사이의 배열을 사용할 수 있습니다.
주의
구문
SUMPRODUCT(array1,array2,array3, ...)
array1, array2, array3, ... 곱한 후 더할 값이 들어 있는 배열을 지정합니다. 2개에서 255개 사이의 배열을 사용할 수 있습니다.
주의
- 인수로 사용하는 배열의 차원은 모두 같아야 합니다. 차원이 같지 않으면 #VALUE! 오류 값이 반환됩니다.
- 숫자가 아닌 배열 항목은 0으로 처리됩니다.
SUMPRODUCT함수는 다음에 이야기하게 될 "배열함수"와 깊은 관련이 있습니다. 영어로는 ARRAY인데, 학교 다닐때 "행렬"이라는 이름으로 배운 기억이 있는데 엑셀에서는 "배열"이라는 표현을 씁니다. 배열로 수식을 작성하는 것은 저도 아직 정확하게 이해는 하지 못했지만, 대충 감은 잡고 있는데, 그 감을 잡는데 이 SUMPROCUT함수가 도움이 되었습니다.
이 함수는 위에 있는 설명에도 있듯이, 주어진 배열을 모두 곱한 뒤에 그 합계를 반환하는 함수입니다.
(2,4) 라는 배열과 (3,5) 라는 배열이 있을때,
SUMPRODUCT({2,4},{3,5})라고 하면
2*3 + 4*5 를 계산하여 26을 반환합니다.
같은 식을 다음과 같이 표현할 수도 있습니다.
A1:A2 의 배열과 B1:B2의 배열이 * (논리곱)으로 연결되어있습니다. 이때의 계산식은 위에서 설명한 것과 같이,
2*3 + 4*5
입니다. 그런데, 다음처럼 + (논리합)으로 연결되면
(2+3) * (4+5)
를 계산하여 14를 반환합니다.
여기까지 SUMPRODUCT함수의 기본적인 설명이고, 이제 RANK함수를 이용해 순위를 구할때 유용하게 쓸 수 있는 방법을 알아봅시다.
다음과 같이 수식을 써 봅시다.
=RANK(E2,$E$2:$E$5)&"("&COUNTIF($E$2:$E$5,E2)&")"
| =RANK(E2,$E$2:$E$5) | E2:E5의 범위에서 E2의 순위를 구하고 | ||
| &"(" | 그 뒤에 괄호 여는 기호 "("를 삽입하고 | ||
| &COUNTIF($E$2:$E$5,E2) | 그 뒤에 E2:E5의 범위에서 E2와 점수가 같은 사람수를 구해서 넣고 | ||
| &")" | 괄호 닫는 기호 ")"를 삽입하라 |
=RANK(E2,$E$2:$E$5)&IF(COUNTIF($E$2:$E$5,E2)=1,"","("&COUNTIF($E$2:$E$5,E2)&")")
| =RANK(E2,$E$2:$E$5) | E2:E5의 범위에서 E2의 순위를 구하고, |
| & | 그 뒤에 다음의 결과값을 덧붙인다 |
| IF(COUNTIF($E$2:$E$5,E2)=1, | 만약 E2:E5범위에서 E2와 같은 값이 1이라면, (즉 자기자신 이외에는 같은 값을 가진 이가 없음) |
| "", | 공백을 출력하고 |
| "("& | 1보다 크다면 (동점자가 있다면) 괄호여는 기호를 삽입하고 |
| COUNTIF($E$2:$E$5,E2)& | E2:E5범위에서 E2와 같은 값의 개수를 출력하고 |
| ")") | 괄호 닫는 기호를 삽입하라 |
그 결과는 위의 그림과 같습니다. 이제 제대로 된 모양 같네요 ^^ 하지만 명심할 것은, 모양을 보기 좋게 만들수록 수식은 점점 더 어려워진다는 것입니다. 실무자 입장에서는 편하게 수식을 만들고 싶고, 관리자입장에서는 보기 편했으면 좋겠고..... 갈등이죠 ^^
그런데, 두번째 기준으로 영어점수를 추가하여, 동점자 중에서도 국어점수가 높은 학생을 더 높은 순위로 주려면 어떻게 해야할까요? 이제 SUMPRODUCT함수가 등장합니다.
=RANK(E4,$E$2:$E$5)+SUMPRODUCT(($E$2:$E$5=E4)*($B$2:$B$5>B4))
잘 보시면 앞의 RANK(E4,$E$2:$E$5) 부분은 총점으로 순위를 구하는 수식인데, 그 뒤에 더하기기호(+)가 붙어있습니다. 그 뒤의 수식에서 동점자중에서 자신보다 국어점수가 높은 학생수를 구한 다음 순위에 더한다는 의미입니다. 즉, 이전의 RANK함수에서는 공동1위였던 학생3은 자신의 순위 1에다가, 총점이 자기와 같으면서 국어점수가 자기보다 높은 학생의 수 2(학생2,학생4)를 더하여 3위가 되는 것입니다.
SUMPRODUCT함수는 각 배열의 인수끼리 곱해서 더한다고 했죠?
위의 수식에서 인수는 ($E$2:$E$5=E2)와 ($B$2:$B$5>B2)) 의 배열에 붙은 수식 두 개입니다. 숫자가 들어있으면 글그대로 곱하고 더하면 되지만, 지금처럼 수식이 들어있는 경우는 배열의 각 셀마다 주어진 수식에 대한 논리값(참,거짓)을 구하여, 곱하기대신에 논리곱을, 더하기 대신에 논리합을 구합니다. 논리합과 논리곱에 대해서는 이전 글을 참고하세요.
여기서는 총점이 E2의 값과 같다는 조건과 국어점수가 E2보다 크다는 두 조건을 제시하고 있습니다. 각 배열에서 두 조건의 참,거짓을 따져봅시다.
총점이 있는 E열부터 보면,
두번째 조건인 국어점수가 B4보다 큰가는 조건에 대해서는
위와 같은 결과값이 나올것입니다.
앞의 수식
SUMPRODUCT(($E$2:$E$5=E4)*($B$2:$B$5>B4))
에서 두 조건 사이가 논리곱(*)으로 연결되어 있습니다. 이전의 글에서 알아본 것처럼, 논리곱일때는 두 조건이 모두 참일때만 참이 된다고 했습니다. 그러므로 최종적인 결과값은 다음과 같습니다.
결국 두 가지 조건을 다 만족하는 것은 학생2와 학생4의 두 사람 뿐임을 알 수 있습니다. 따라서,
=SUMPRODUCT(($E$2:$E$5=E4)*($B$2:$B$5>B4))
의 수식에서는 2가 반환됩니다.
SUMPRODUCT함수는 제대로 이해하고 있으면 매우 유용합니다. 수식이 좀 길어져서 복잡해 보인다는 것이 단점이지만, 그것도 남들에게는 뭔가 엄청나게 대단한 것을 하는 능력자인것처럼 보이게 하는 효과를 거두기도 합니다.^^
p.s. 총점과 국어점수까지 같다면, 그 다음 조건으로 수학점수를 주고 싶다면 어떻게 할까요? 이 글을 쓰기 전에 연구를 해봤지만, 아직 답을 얻지 못했습니다. 뒤에 IF함수를 이용해 조건을 더 길게 주어야 할 것 같은데...... 아무튼, 실제 사용하기위해 함수식을 만들어야하는 상황은 아니므로, SUMPRODUCT함수를 이해하는데 도움이 되는 정도에서 동점자순위 구하기는 마치겠습니다.
2009/03/19 - [컴퓨터/엑셀] - 1일1함수(3) : 순위 매기기 rank 함수
2009/03/28 - [컴퓨터/엑셀] - 1일1함수(7) : AND 함수 OR 함수 (논리곱과 논리합)
2009/04/05 - [컴퓨터/엑셀] - 1일1함수 (11) COUNTIF 함수
댓글
댓글 쓰기