1일1함수 (30) SUM 함수 : 여러 시트의 합계내기, 여러 시트의 서식 한꺼번에 만들기

오늘은 SUM함수를 다루겠습니다. 너무나 유명한 함수이기 때문에 다루지 않을까도 생각했습니다만, 얼마전에 한 선생님이 (차마 우리말을 다루는 과목 담당이시라고는 말씀드리지 못하겠습니다만 ^^;) 1반 학생이면서 A과목을 선택한 학생의 수를 세는 수식을 어떻게 만들어야 할까 하고 한 시간을 고민하셨다는 말씀을 듣고서, 1일1함수를 진행하면서 신기하고 재미있는 함수만 다룰 것이 아니라, 기본이 되고 많이 쓰이는 함수를 철저하게 다루는 것이 좋지 않을까하는 생각이 들었습니다.
그래서 오늘 부터는 기본 함수를 다루려합니다. 앞으로 다룰 함수의 목록은 다음과 같습니다.
SUM
SUMIF
SUMIFS
AVERAGE
AVERAGEIF
AVERAGEIFS

COUNT/ COUNTA/ COUNTIF/ COUNTIFS 함수는 이전에 다루었습니다만, 기회가 된다면 실전 응용문제를 풀어보는 기회를 가져볼까 합니다.

참고로, 엑셀 함수를 정리해 놓은 문서를 첨부합니다.

Excel Function.xls

제가 만든건 아니고, 검색하다가 얻은 것입니다. 출처는 여기입니다.

보시면 엑셀의 함수를 항목별로 묶어 놓았습니다. 왼쪽에 + 기호를 클릭하시면

해당 항목에 속하는 함수와 간단한 설명이 펼쳐집니다.
예제 시트도 있으니 한번 살펴보세요^^



자, 오늘은 SUM함수입니다. 너무도 유명한 함수이니만큼, 함수의 구문이나 주의사항은 생략하도록 하겠습니다.

설문조사를 해서, 그 결과를 한 시트에 정리하였습니다. 그런데, 이와 같은 시트가 sheet1부터 sheet10 까지 있다면, 그래서 그 시트의 각 항목 (1번문항의 1에 응답한 사람의 수, 1번문항의 2에 답한 사람의 수등등)을 모두 더한 total시트를 만들고 싶다면 어떻게 해야할까요?

sheet1부터 sheet10까지의 모든 시트에 있는 b2셀의 값을 모두 더하는 수식으로 제일 먼저 떠오르는 방법은 아래와 같을 것입니다.

=sum(sheet1!b2,sheet2!b2,sheet3!b2,sheet4!b2,sheet5!b2,sheet6!b2,sheet7!b2,sheet8!b2,sheet9!b2,sheet10!b2)

그런데, 너무 심한 느낌이 있습니다. 시트가 100개라면 어떻게 해야할까요?

이 글을 다 읽으시고 난 다음에는 아무도 위와 같은 수식은 쓰지 않으실 겁니다.

답을 알기 전에, 작은 팁을 먼저 소개할까 합니다. 여러 시트에 동일한 서식을 만들어야할때가 있습니다. sheet1에서 sheet10까지의 10개의 시트에 위의 그림과 같은 서식을 각각 만들어야한다면 어떤 방법을 쓰시겠습니까?
sheet1에 위의 그림과 같은 서식을 먼저 만들고, 그 서식을 복사해서 sheet10까지 아홉번을 붙여넣기 하는 방법이 있을수 있습니다. 그것도 물론 좋은 방법입니다. 그런데 시트가 100개라도 그렇게 해야할까요?

먼저, 시트를 선택하였을때 색깔의 구분이 잘 되도록 하기위해 시트(탭)의 색을 변경해 줍시다. (위의 그림에서 Sheet1, Sheet2, ..와 같은 문자가 써 있는 부분을 "탭"이라고 부릅니다.)  sheet1을 선택하고, SHIFT키를 누른 상태에서 sheet10을 선택합니다. 그러면 sheet1부터 sheet10까지의 모든 시트가 선택이 됩니다.
아무 시트나 탭을 오른쪽 클릭하고 탭색에서 적당한 색을 선택해 줍니다. (전 "바다색 강조5"를 선택했습니다.)

그러면 이제 선택한 시트는 sheet1처럼 흰색이 되고 나머지 시트는 바다색으로 보이므로, 구분이 잘 됩니다.

sheet1을 선택하고, SHIFT키를 누른 상태에서 sheet10을 선택합니다. 그러면 sheet1부터 sheet10까지의 모든 시트가 선택이 됩니다.

이제 sheet1에서 서식을 작성합니다. 선도 그리고, 글자도 쓰고, 색도 입히고....

완성되고 나면 sheet6을 선택해 봅시다. sheet1과 똑같은 서식이 만들어져 있습니다! 놀랍지 않습니까? ^^* 이런 식으로 여러개의 시트에 똑같은 서식을 한번에 만들 수 있습니다.

자, 그러면 위에서 낸 문제의 답을 알아봐야하겠죠?
total이라는 시트의 b2셀에 들어가는 수식은 위에서 보시다시피

=SUM(Sheet1:Sheet10!B2)

입니다.
"sheet1에서 sheet10 까지의 시트에 있는 b2셀의 값을 모두 더하라"는 의미입니다. 참 쉽죠? ^^*

그런데, 만약에 sheet1, sheet2,...sheet10처럼 규칙적인 숫자로 시트이름이 주어져있지 않고, 중간에 불규칙적인 시트이름이 들어가 있다면, 그래도 저 수식이 제대로 작동할까요?

예, 보시다시피, 시트의 이름을 불규칙적으로 바꾸어도 수식은 제대로 작동합니다.  sheet1과 sheet10사이에 들어가 있는 모든 시트를 지정하는 것이지, 숫자가 차례대로 증가하는 것과 같은 규칙적인 시트이름이 주어져야하는 것은 아닙니다.

대신에, "2월"이라는 시트의 위치를 sheet1~sheet10의 범위 밖으로 옮기게되면 "2월"이라는 시트는 더이상 sheet1~sheet10의 범위속에 있지 않게되므로, 계산에서 빠지게 되어, 결과값이 달라집니다.

p.s. 응용 들어갑니다.^^
다들 아시겠지만, SUM(B2:B11)이라고 하면 b2부터 b11까지의 범위에 있는 모든 셀의 값을 더하게 됩니다.
그럼, 만약, sheet1부터 sheet10까지에 있는 모든 b2:b11 범위의 값을 모두 더하는 수식은 어떻게 될까요?
답을 아시는 분은 댓글을 달아주세요.^^