거래내역 시트를 기준으로 실적요약 시트 만들기

안녕하세요.

 

거래내역 시트에 거래내역을 일자 기준으로 나열하면 입력된 데이터를 기준으로 월별 거래처별 품명별 판매실적을 나타내는 작업을 해 보도록 하겠습니다.

 

거래내역 시트에 어떤 필드를 넣는가에 따라 분석할 수 있는 내용도 결정됩니다.

그래서 구분할 수 있는 최대한 필드를 나눠 거래내역 시트를 구성하는 것이 좋습니다.

물론 필드를 너무 많이 만들면 입력할 때 불편하기 때문에 적당한 개수로 결정해야 합니다.

 

관련있는 필드를 입력할 때 자동으로 처리될 수 있는게 있다면 가능한한 자동으로 처리되도록 구성하는 것도 해야 할 작업 중 하나입니다.

 

실습 파일 다운로드

거래내역 시트를 기준으로 실적요약 시트 만들기.xlsx

0.01MB

완성 파일 다운로드

거래내역 시트를 기준으로 실적요약 시트 만들기_완성.xlsx

0.03MB

 

 

실습 파일에는 이미 1월부터 7월까지 거래내역이 입력되어 있습니다.

 

거래처와 품명, 단가를 입력할 때 현재 실습 파일은 직접 입력하게 되어 있는데, [데이터 유효성 검사]를 이용하여 거래처와 품명은 선택할 수 있도록 바꾸고, VLOOKUP 함수를 이용해 품명을 선택하면 단가를 자동으로 입력되도록 수정하겠습니다.

단가와 수량이 입력되면 금액이 계산되도록 수식이 작성되어 있고, 금액과 입금액이 입력하면 미수금액도 구해지도록 수식이 만들어져 있습니다.

 

B, C, D열 머리글을 선택해 복사하고, 새 시트를 추가해 [A1] 셀에 붙여넣기 합니다.

새로 추가된 시트 이름을 참고라고 수정합니다.

참고 시트의 내용은 거래내역 시트에서 사용될 추가 정보를 기록해 두는 시트입니다.

현재는 거래처 목록과 품명/단가 목록을 만들 것입니다.

 

시트 탭의 시트 이름을 더블클릭한 뒤 새 시트 이름 참고를 입력하고 <Enter>를 누릅니다.

A열의 거래처와 B, C열의 품명/단가를 구분하기 위해 B열 머리글을 선택한 뒤 단축키 <Ctrl + 플러스(+)>를 눌러 새 열을 추가합니다.

[A1] 셀에 커서를 두고 [데이터]-[데이터 도구]-[중복된 항목 제거]를 선택합니다.

[중복 값 제거] 대화상자에서 [확인]을 눌러 중복 거래처를 제거합니다.

거래 목록은 56개 행이 있었지만 실제 거래처는 5개입니다.

[확인]을 눌러 대화상자를 닫습니다.

 

[C1] 셀에 커서를 두고 똑같은 방법으로 [데이터]-[데이터 도구]-[중복된 항목 제거]를 누릅니다.

거래처 목록과 품명/단가 목록을 만들었습니다.

[데이터 유효성 검사]에서 사용하기 쉽도록 각각 이름 정의 하겠습니다.

 

참고시트 [A2:A6] 셀 범위를 선택하고 수식 입력줄 이름상자에 커서를 두고 거래처라고 입력한 뒤 <Enter>를 누릅니다.

[C2:D6] 셀 범위를 선택하고 이름상자에 단가표를 입력한 뒤 <Enter>를 누릅니다.

[C2:C6] 셀 범위를 선택하고 이름상자에 품명을 입력한 뒤 <Enter>를 누릅니다.

 

작성된 이름을 확인해 보려면 [수식]-[정의된 이름]-[이름 관리자]를 선택합니다.

거래처나 품명이 추가되는 경우 여기에서 범위를 변경하면 됩니다.

 

[닫기]를 누르고 거래내역 시트로 가기 위해 시트 탭에서 거래내역 시트를 선택합니다.

 

거래내역 시트에 추가될 거래내역은 최대 1000개라고 가정하고 1001행까지 데이터 유효성 검사 설정을 하겠습니다.

데이터가 추가로 더 입력된다면 데이터 유효성 검사 범위를 늘리면 됩니다.

 

[B2] 셀을 선택한 뒤 수식 입력줄 이름상자에 커서를 두고 B2:B1001을 입력한 뒤 <Enter>를 누릅니다.

[데이터]-[데이터 도구]-[데이터 유효성 검사]를 선택합니다.

[데이터 유효성] 대화상자에서 [제한 대상]을 목록, 원본은 =거래처를 입력한 뒤 [확인]을 누릅니다.

[C2] 셀에 커서를 두고 수식 입력줄 이름상자에서 C2:C1001을 입력한 뒤 <Enter>를 누릅니다.

 

[데이터 유효성] 대화상자에서 [제한 대상]을 목록, 원본은 =품명을 입력한 뒤 [확인]을 누릅니다.

이미 단가가 구해져 있지만 앞으로 추가될 데이터도 자동으로 단가가 나타나도록 하기 위해 단가 열에 수식을 입력합니다.

 

[D2] 셀에 커서를 두고 수식을 입력합니다.

=IFERROR(VLOOKUP(C2,단가표,2,0),"")

[D2] 셀을 선택하고 채우기 핸들에 커서를 둔 다음 더블 클릭해 나머지 셀에도 수식을 채웁니다.

 

여기까지 작업을 해서 기존 데이터를 정리 했습니다.

이 내용을 기준으로 실적요약 시트를 만들려면 피벗 테이블 기능을 이용하면 됩니다.

 

그런데 이 내용 뿐만 아니라 앞으로도 새로운 데이터를 입력하면 자동으로 피벗 테이블의 원본 데이터가 되도록 하려면 추가 작업이 필요합니다.

 

현재 데이터를 표로 전환하겠습니다

표로 바꾸면 새 데이터를 입력하면 자동으로 표 범위가 확장되고 수식으로 입력된 필드는 자동으로 수식도 채워지게 됩니다.

 

[A1] 셀에 커서를 두고 [삽입]-[표]-[표]를 선택합니다.

표가 만들어졌습니다.

 

너무 간단하죠?

복잡해서 어려운 것보다는 휠씬 낫죠. ^^

 

A열 데이터가 입력된 마지막 행 다음 행에 커서를 두고 새 데이터를 입력하면 자동으로 표 범위가 확장되며 수식으로 처리된 셀은 위 행에서 복사가 되어 값을 나타냅니다.

 

[A58] 셀에 8-13을 입력하고 <Tab>키를 눌러 다음 필드로 커서를 옮깁니다.

[F58] 셀과 [H58] 셀에 나타난 오류메시지는 무시해도 됩니다.

품명을 입력하면 오류메시지가 사라집니다.

 

거래처는 화명점, 품명은 노트북, 수량은 10, 입금액은 0, 처리담당은 1을 입력합니다.

처리담당까지 입력한 다음에는 <Enter>를 누릅니다.

<Tab>을 누르면 다음 행으로 입력이 넘어가고, 표 범위에 포함되게 됩니다.

계속 입력을 하려고 한다면 <Tab>을 누르는 것이 더 나은 선택입니다.

 

 

새로운 데이터를 입력하면 표 범위가 자동으로 확장되므로 이 표를 기준으로 피벗 테이블을 만들겠습니다.

 

커서를 [A1] 셀에 두고 [삽입]-[표]-[피벗 테이블]을 선택합니다.

[확인]을 눌러 피벗 테이블을 만듭니다.

새로 만들어진 시트 이름을 실적요약으로 바꿉니다.

시트 탭에서 바꿀 시트 이름을 더블 클릭한 뒤 실적요약을 입력하고 <Enter>를 누릅니다.

피벗 테이블 기능을 이용해 원하는 요약결과를 나타냅니다.

 

피벗 테이블 기본 사용법은 아래 강좌를 참고하세요.

 

 

엑셀 79강] 복잡한 데이터를 요약하는 피벗테이블

 

여기에선 월별, 거래처별, 품명별 금액, 입금액, 미수금액 합계를 나타내겠습니다.

 

피벗 테이블 필드 목록에서 일자, 거래처, 품명을 순서대로 필드명 앞 사각형을 클릭해 체크 표시가 나타나도록 합니다.

금액, 입금액, 미수금액을 순서대로 체크 표시가 나타나도록 합니다.

거래일자를 행 항목으로 넣게 되면 자동으로 그룹 설정이 되어 월, 일별로 그룹이 됩니다.

월별 그룹만 나타나도록 수정합니다.

 

[A4] 셀에 커서를 두고 마우스 오른쪽 단추를 눌러 [그룹]을 선택합니다.

[그룹화] 대화상자에서 일을 클릭해 선택해제하고 [확인]을 누릅니다.

[디자인]-[레이아웃]-[보고서 레이아웃]을 눌러 [테이블 형식으로 표시]를 선택합니다.

1000단위 구분 기호를 나타내겠습니다.

 

[D4] 셀에 커서를 두고 [피벗 테이블 분석]-[활성 필드]-[필드 설정]를 누릅니다.

아래 [표시 형식] 단추를 눌러 [셀 서식] 대화상자를 표시하고, 범주에서 일반, 1000 단위 구분 기호 사용에 체크하고 [확인]을 누릅니다.

[E4], [F4] 셀에서도 똑같이 해서 1000 단위 구분 기호를 나타냅니다.

 

거래내역 시트에서 새로운 데이터를 입력한 경우 피벗 테이블에 자동으로 반영되지 않으므로 [피벗 테이블 분석]-[데이터]-[새로 고침]을 눌러줘야 합니다.

 

 

이상으로 거래내역을 입력해 실적요약을 만드는 방법을 알아봤습니다.

 

입력데이터에 따라 조금씩 응용이 필요합니다.

 

도움되길 바라며 강좌 마치겠습니다.

수고하셨습니다.