월별 사용금액 종합표 만드는 방법을 소개해본다. 다소 복잡해 보일 수 있지만, 차근차근 따라 하다보면 충분히 할 수 있다. (엑셀 고수들은 더 좋은 방법을 사용하겠지만, 나만의 방법을 소개한다.)
우선, 상황을 설명해본다.
아래 그림처럼 2개의 엑셀 문서가 있다고 해보자. [download.xlsx] 파일은 사용금액 집계의 바탕이되는 '원 자료(raw data)'가 수록된 파일이다. [사용금액 종합표.xlsx] 파일은 제목 그대로 '사용금액' 현황을 한 눈에 볼 수 있도록 표로 종합한 파일이다.
[download.xlsx] 파일은 회사의 ERP 시스템에서 매달 다운받아 최신화 하는 자료다. 예를들어, 아래 그림과 같은 형식이라고 해보자. 이 파일 내 자료의 양식은 항상 동일하고, 값만 계속 추가되는 형식이다.
[사용금액 종합표.xlsx] 파일안에는 월별 총 사용금액이나, 담당자별 월간 사용금액의 현황이 종합되어있다. 이번 글에서는 아래그림의 좌측에 있는 "1. 월별 총 사용금액"을 만들어보려고 한다.
목표는 이렇다. 회사의 ERP 시스템에서 비용지출 자료를 [download.xlsx] 파일로 추출 & 다운로드 하면, 이 자료가 자동으로 [사용금액 종합표.xlsx] 파일에 반영되어, 월별 총 사용금액이 집계되도록 만들 것이다.
1. 종합표 만들어두기
먼저, [사용금액 종합표.xlsx] 파일 안에 종합표를 만들어두자. 아래 그림처럼 표를 만들어 주고, 금액이 들어갈 셀들은 모두 선택한 뒤 "표시형식 > 쉼표 스타일 (=회계)" 를 선택해준다. 그리고, C16 셀에는 1월 ~ 12월의 합계를 나타내도록 수식을 기입해준다.
단, 여기서 B열의 1월 ~ 12월은 「숫자 형식」 이어야 한다. 아래 그림과 같이 설정해주자.
2. SUMIFS 함수 사용하기
SUMIFS 함수를 사용해서 "월별 총 사용금액" 을 계산해보자.
SUMIFS 함수는 여러가지 조건에 맞는 셀들의 합계를 계산해주는 함수다. SUMIFS 함수의 구조는 아래 그림과 같다. 첫번째 필드에는 '실제 합계를 구해줄 셀 범위'를 지정해야한다. 그리고 그뒤로는 '조건을 지정할 셀 범위', '조건의 내용' 순으로 계속 이어진다.
여기서 '조건의 내용' 을 입력할 때는 숫자, 식, 셀, 텍스트 등의 형식으로 입력해줘야 한다. 특히 '텍스트' 형식으로 입력해줘야한 다는 점에 주목해야한다. 이 부분은, 뒤에 한번 더 설명하겠다.
3. SUMIFS 의 첫번째 필드 입력하기
[사용금액 종합표.xlsx] 파일의 C4 셀에 SUMIFS 수식을 입력해보자. 우선, C4 셀에 =SUMIFS( 까지만 입력해준다.
이어서, [download.xlsx] 파일을 열고, 합계를 구할 D열 전체를 선택해준다.
이 상태에서 콤마(,)를 입력해서 첫번째 필드의 기입을 마무리한다.
4. SUMIFS의 조건① 필드 입력하기
다음으로 SUMIFS 함수의 조건 필드를 입력해보자. 첫번째 조건을 지정할 셀 범위를 선택해야 한다. [download.xlsx] 파일의 A열 전체를 선택해준다. 그리고 콤마(,)를 입력해서 필드의 입력을 마무리한다.
이제, 다음 필드에 "조건 내용" 을 입력해줘야한다. 콤마(,) 뒤에 이어서 ">="&EOMONTH(DATE(2023,1,1),-1)+1 라고 조건 을 기입해준다. 이 조건은 "2023년 1월 1일 보다 크거나 같은 날짜" 를 찾으라는 뜻이다.
참고로 SUMIFS 함수를 사용할 때, 부등호 등의 조건 기호를 입력할 땐 「텍스트 형식」으로 입력해야 인식이 된다. 그래서 부등호를 「큰 따옴표(" ")」 안에 입력해줬다. 그리고, 그 뒤에 따라붙는 함수나 셀을 정상적으로 연결시켜주기 위해서 「&」 기호를 사용해야 한다.
이 상태에서 콤마(,)를 입력해서 필드의 입력을 마무리한다.
5. SUMIFS의 조건② 필드 입력하기
두번째 조건 필드를 입력해보자. 콤마(,) 뒤에 이어서 [download.xlsx] 파일의 A열 전체를 다시한번 선택해준다. 그리고 콤마(,)를 입력해서 필드의 입력을 마무리한다.
다음 필드에 "두번째 조건 내용" 을 입력해줘야한다. 콤마(,) 뒤에 이어서 "<"&EOMONTH(DATE(2023,1,1),0)+1 라고 조건 을 기입해준다. EOMONTH 함수의 두번째 필드를 0으로 입력했다는 차이점이 있다. 이 조건은 "2023년 2월 1일 보다 작은 날짜" 를 찾으라는 뜻이다.
이 상태에서 「닫힘괄호 " ) "」 를 입력하여 전체 수식 입력을 마무리한다. [사용금액 종합표.xlsx] 파일에 1월 사용금액이 집계된 것을 확인할 수 있다.
6. 월별 자동계산을 위한 부분수정
이 상태에서 C4 셀(1월)에 작성한 수식을 C15 셀(12월)까지 자동채우기 또는 복사/붙여넣기 하면, 아래 그림처럼 1월의 값이 그대로 12월까지 적용되어버린다.
이 문제를 해결하고, 월별 자동계산되게 만들기 위해서는 수식의 일부분을 수정해야 한다. 아래에 빨간색 네모로 표시한 두 부분이다.
아래 그림처럼, 「-1」 을 선택하여 「B4-2」 라고 수정해준다.
같은 방식으로 「0」 을 선택하여 「B4-1」 이라고 수정해준다.
이 상태에서 C4 셀(1월)에 작성한 수식을 C15 셀(12월)까지 자동채우기 또는 복사/붙여넣기 하면, 아래 그림처럼 월별 사용금액이 자동 집계된 것을 볼 수 있다.
7. 자동 업데이트 확인
이제, [download.xlsx] 파일만 최신화 시키면, [사용금액 종합표.xlsx] 파일의 "월별 사용금액 집계표" 는 자동 업데이트 된다.
예를들어, [download.xlsx] 파일의 22행에 12월의 사용금액 실적을 최신화 했다고 해보자.
그러면, [사용금액 종합표.xlsx] 파일의 집계표 12월 항목 및 합계 항목이 자동 업데이트 된 것을 볼 수 있다.
이렇게 두 엑셀간 데이터를 연결하여 자동업데이트 할 때에는 몇가지 주의사항이 있는데, 자세한 내용은 아래 링크된 글에서 확인할 수 있다.
2023.09.09 - [Office Tools/Excel] - [엑셀] 연결된 데이터의 업데이트, 관리, 문제해결 팁
끝.
다소 복잡한 방법이었지만, SUMIFS 함수를 사용하면 내가 원하는 조건에 맞추어 종합표, 현황표, 집계표, 실적표 등을 작성할 수 있다.
'Office Tools > Excel' 카테고리의 다른 글
| [엑셀] 연결된 데이터의 업데이트, 관리, 문제해결 팁 (0) | 2023.09.09 |
|---|---|
| [엑셀] 방금 전 적용한 서식을 복사하기 (F4) (0) | 2023.02.21 |
| [엑셀] 단축키로 탭(Tab) 전환하는 방법 (0) | 2023.02.21 |
| [엑셀] 처음 두 문자 대문자인 경우 (자동 고침 옵션) (0) | 2023.02.21 |
| [엑셀] 수학 계산 연산자 적용 우선순위 (0) | 2023.02.20 |