들어가며
Excel에는 수많은 함수가 있습니다. 해도해도 끝이 없을 것 같던 Excel 함수도 이번 시간으로 마무리됩니다. 우리는 함수를 이용하여 합계를 구할 수도 있고 조건 분기 처리도 할 수 있습니다. 다들 기억하시죠? (...잠잠...^^) 혹시라도 긴가민가하는 분들은 이전 강의를 살펴보고 오시기 바랍니다.
함수를 응용하는 능력은 함수의 가짓수를 많이 아는 데서 오지 않습니다. 하나를 알더라도 그 함수의 특징과 성격을 속속들이 알고 있는 것이 중요합니다.
이번 시간에는 조건 함수와 집계를 구하는 함수를 결합시키면 얼마나 파워풀한 일을 할 수 있는 지 알아봅니다. 자, 준비되셨나요? 출발합니다.
여러 시트에 있는 데이터 집계하기
하나의 통합 문서에는 여러 개의 시트가 들어갈 수 있습니다. 다음과 같은 데이터가 있습니다. 1월부터 3월까지의 데이터가 있고, 이것을 '1분기' 시트에 집계하고자 합니다.
시트 수가 많더라도 상관 없습니다. 참고로 다른 시트나 파일에 있는 내용을 참조하는 것을 '3차원 참조'라고 부르며, 매우 중요한 개념입니다.
(1) 집계를 구할 셀(여기서는 '1분기' 시트의 D3 셀)을 선택합니다.
(2) [수식] 탭 - [함수 라이브러리] 그룹 - [자동 합계]를 선택합니다.
(3) '1월' 시트를 클릭하고 Shift를 누른 채 '3월' 시트를 선택합니다.
(4) 이 상태에서 '김지연'의 '수량'이 들어있는 D4 셀을 클릭합니다. 수식 입력줄을 보면 '=SUM('1월:3월'!D3)'이라는 수식이 작성되어 있습니다. 이것은 '1월'부터 '3월' 시트의 D4셀을 뜻합니다.
(5) Enter를 누르면 결과가 구해집니다. 채우기 핸들을 이용하여 나머지 셀에도 수식을 채워넣기 합니다.
(6) 같은 방법으로 금액 합계를 구하여 완성합니다.
[주의] 3차원 참조 수식을 적용하려면 각 시트에 입력된 항목의 순서가 모두 동일해야 합니다. 만약 순서가 다르다면 데이터 통합이나 피벗 테이블 등 다른 방법을 사용해야 합니다. 이와 관련해서는 다른 강의에서 소개합니다.
하나의 조건을 만족하는 합계와 평균 구하기
우리는 SUM이나 AVERAGE 함수에 대해서도 알고 있고, IF 함수에 대해서도 배웠습니다. 이 두 가지를 조합하면 어떤 일이 생길까요? 특정한 조건에 맞는 자료들의 합계(또는 평균)를 구할 수 있습니다. 이런 역할을 하는 함수가 SUMIF와 AVERAGEIF입니다. 이름만으로도 그 역할을 미루어 짐작할 수 있습니다.
SUMIF와 AVERAGEIF 함수 기본
SUMIF, AVERAGEIF는 가까운 친척입니다. 합계를 구하느냐 평균을 구하느냐에 따라 함수명만 다를 뿐 사용하는 방법은 같습니다.
=SUMIF(range, criteria, sum_range)
=AVERAGEIF(range, criteria, sum_range)
' range: 조건을 비교할 범위
' criteria: 조건
' sum_range: 합계/평균을 구할 범위
조건에 맞는 합계/평균 구하기
부서명이 '동부'에 해당하는 '수량' 합계를 구해보겠습니다.
(1) 수량 합계를 구할 I3 셀을 선택합니다.
(2) =sumif()를 입력하고 수식 입력줄 왼쪽에 있는 [함수 삽입] 아이콘을 클릭합니다.
(3) [함수 인수] 대화상자에서 인수를 지정합니다. Range 인수는 조건(여기서는 '부서')을 비교할 범위인 C3:C17을 지정합니다. Criteria는 부서 중에서 '동부'에 해당하는 자료에 접근할 것이므로 '동부'라고 입력하거나 셀 참조 방식으로 지정합니다. 후자를 사용하면 부서명을 변경했을 때, 셀 값만 바꾸면 자동으로 수식에 반영되므로 편리합니다. Sum_range는 해당 조건을 충족하는 경우 합계를 구할 범위인 D3:D17 영역을 범위로 지정합니다.
이 경우에는 작성된 수식을 다른 셀에 복사할 필요가 없으므로 모두 상대 참조로 처리했습니다.
=SUMIF(C3:C17,G3,D3:D17)
(4) [확인]을 누르면 결과가 구해집니다(결과: 2496).
(5) AVERAGEIF 함수를 사용하여 '동부'의 수량 '평균'을 구합니다(결과: 832). 수식을 복사한 다음 SUM을 AVERAGE로 바꾸거나 앞의 과정을 반복합니다.
여러 가지 조건을 만족하는 합계와 평균 구하기
영어에서 복수형을 표현할 때 단어 뒤에 's'를 붙입니다. 한 가지가 아닌 여러 가지 조건을 충족하는 합계나 평균을 구하고자 할 때에도 마찬가지입니다. SUMIFS나 AVERAGEIFS 함수를 사용하면 둘 이상의 조건에 맞는 합계와 평균을 구할 수 있습니다.
SUMIFS와 AVERAGEIFS 함수 기본
이 두 가지 함수도 사용법은 동일합니다. 합계나 평균을 구할 범위(sum_range)가 맨 앞에 나오고, 이어서 조건과 범위가 쌍으로 나열됩니다.
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,...)
=AVERAGEIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,...)
' sum_range: 합계/평균을 구할 범위
' criteria_range1: 조건1에 맞는지 검사할 범위1
' criteria1: 조건1
참고로, SUMIFS와 AVERAGEIFS 함수는 엑셀 2010 버전부터 사용할 수 있으며, 조건은 127개까지 지정할 수 있습니다.
SUMIF와 SUMIFS 함수는 사용 방법이 조금 달라서 헷갈리는 경우가 있습니다. 두 함수를 비교해 보면 이렇습니다.
=SUMIF(range, criteria, sum_range)
=SUMIFS(sum_range, criteria_range1, criteria1,...)
' range: 조건을 비교할 범위
' criteria: 조건
' sum_range: 합계/평균을 구할 범위
차이점이 명확히 드러나죠? sum_range의 위치만 잘 기억해 두시면 되겠습니다.
두 가지 이상의 조건을 충족하는 합계/평균 구하기
이번에는 '중부'이면서 '수량'이 1000 이상인 데이터의 '금액' 합계를 구해보겠습니다.
(1) 결과를 표시할 I6 셀을 선택합니다.
(2) =sumifs()를 입력하고 수식 입력줄 왼쪽에 있는 [함수 삽입] 아이콘을 클릭합니다.
(3) Sum_range 인수에는 합계를 구할 범위(금액)이 있는 E3:E17 셀을 지정합니다. 첫 번째 조건과 범위는 C3:C17, G6을 각각 지정합니다.
(4) 대화상자에서 두 번째 조건을 추가합니다. '수량'이 1000 이상이므로 D3:D17과 G7 셀을 각각 지정합니다. G7 대신 직접 ">=1000"과 같이 해도 됩니다.
(4) [확인]을 누르면 결과가 구해집니다(결과: 4,054,700).
(5) AVERAGEIFS 함수를 사용하여 '동부'의 수량 '평균'을 구합니다(결과: 2,027,350). 수식을 복사한 다음 SUM을 AVERAGE로 바꾸거나 앞의 과정을 반복합니다.
그 밖에 알아두면 좋은 몇 가지 함수
이들 함수 외에 COUNT 계열 함수들도 반드시 알아두는 것이 좋습니다. COUNT 계열 함수에는 COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS 등이 있습니다. 이들 함수에 대해서는 다음 영상을 통해 정리해 두시기 바랍니다.
나가며
축하합니다. 이제 여러분은 엑셀의 기본적이지만 중요한 함수에 대해 두루 섭렵하게 되었습니다. 함수 응용력은 함수를 몇 개나 아느냐가 아니라 디테일에서 옵니다. 6개의 "수식과 함수" 시리즈에서 다룬 함수는 Excel의 가장 중요한 함수들이므로 반드시 숙지해 두시기 바랍니다. 수고하셨습니다.
'Excel' 카테고리의 다른 글
| Excel 뺄셈 마스터가 되는 방법 (7) | 2024.04.09 |
|---|---|
| Excel에서 선행 0이 제거되지 않도록 하는 5가지 방법 (6) | 2024.04.08 |
| Excel에 숨겨진 카메라 기능 사용하는 방법 (6) | 2024.04.06 |
| 더 일찍 알았으면 좋았을 Excel 단축키 30+ (6) | 2024.04.05 |
| Excel에서 방향키가 작동하지 않을 때 대처 방법 3가지 (11) | 2024.04.04 |
