아래 데이터를 대상으로 작업을 해보겠습니다.
데이터를 보면 구분이 같은 것이 있고, 같은 중도금 및 잔금에 대해 여러 차례에 걸쳐 나눠 낸 경우가 있습니다.
1. 구분별 약정금액 구하기
중복된 항목 제거를 이용할 수도 있고, 파워 피벗을 이용할 수도 있습니다.
가. 중복된 항목 제거
표 안에 커서를 두고 데이터 탭 > 중복된 항목 제거를 누른 다음
중복 값 제거 창에 열이 모두 선택된 상태인데, 모두 선택 취소를 누른 다음 동호수와 약정일만 체크합니다.
그러면 아래와 같이 종전과 달리 정확하지 않게 "중복된 값을 제거하지 5. 8 고유 값이 남아 있습니다."라고 메시지가 표시됩니다. 제대로 하면 중복된 값 5개를 제거하고 8개의 고유한 값이 남아 있습니다."가 됩니다. 확인 버튼을 누릅니다.
이제 데이터 탭 > 부분합을 누르면 그룹화할 항목은 동호수, 사용할 함수는 합계 맞습니다. 부부합 계산 항목에 처음에는 납부금액에 체크되어 있는데 해제하고, 약정금액을 체크하고 확인 버튼을 누릅니다.
그러면 동호수별 요약(약정금액 합계)과 총합계가 표시됩니다.
나. 피벗 테이블
원래대로 돌리기 안되므로 파일 닫기를 한 다음 저장 안함을 클릭하고, 다시 위 파일을 엽니다.
삽입 탭 > 피벗 테이블 윗부분을 클릭합니다. 그러면 범위가 자동으로 선택되고, 위치가 새 워크시트가 기본값인데,
① 새 워크시트에 만들기 위해 확인 버튼을 눌러도 되고
② 기존 워크시트를 클릭한 후 위치에 커서를 넣고 i3셀을 클릭한 다음 확인 버튼을 눌러도 됩니다.
i3셀에 만들어 보겠습니다.
동호수, 구분과 약정금액에 체크하면 아래와 같이 피벗 테이블이 만들어지는데,
행은 동호수와 구분이 맞는데, 값이 약정금액의 합계로 표시되는데, 약정금액이 여러 번 표시되므로 합계로 하면 안 되고, 금액이 같으므로 평균, 최댓값 또는 최솟값으로 바꿔야 합니다.
값 영역의 합계 : 약정금액을 클릭한 다음 값 필드 설정을 클릭하고,
아래와 같이 값 필드 요약 기준이 합계로 되어 있는 것을 평균, 최대, 최소 중 하나로 바꾸는데 최대를 선택하겠습니다. 확인 버튼을 누르면
구분별 약정금액의 최댓값이 표시되는데, 합계 금액도 동호수 기준 약정금액의 최댓값이 표시됩니다.
그렇다고 호수 오른쪽 j4셀을 클릭한 다음 값 필드 설정을 누르고, 다시 합계로 바꾸면
구분(중도금 및 잔금)까지 모두 합계로 바뀌므로 원하는 값이 아닙니다.
따라서, 다시 최댓값으로 바꾸고,
동호수에 커서를 놓고, 마우스 오른쪽 버튼을 누른 후 "동호수" 부분합을 클릭해서 왼쪽 체크를 지웁니다.
그러면 동호수 오른쪽의 합계 금액이 제거됩니다.
이제 동별 합계를 표시해야 하므로 피벗 테이블의 디자인을 변경한 후 복사한 후 오른쪽에 값으로 붙여넣기를 하고, 다시 피벗 테이블을 만들어야 합니다.
(1) 피벗 테이블 디자인 변경
피벗 테이블 분석 옆의 디자인 탭을 클릭하고, 보고서 레이아웃에서 '테이블 형식으로 표시'를 클릭합니다.
그러면 피벗 테이블이 세로에서 가로 형식으로 바뀝니다.
그런데 동호수 별로 집계를 해야 하므로 아래에 동호수가 반복되어야 합니다.
이번에는 보고서 레이아웃을 클릭하고 '모든 항목 레이블 반복'을 클릭합니다.
그러면 동호수가 아래로 채워집니다.
(2) 값으로 붙여 넣기
피벗 테이블을 복사하는데 총합계는 필요 없으므로 제외하고 i3셀에서 K11셀까지만 복사 후 M3셀에서 마우스 오른쪽 버튼을 누르고 값으로 붙여넣기를 합니다.
그러면 아래와 같이 오른쪽에 데이터가 붙여 넣어집니다.
(3) 피벗 테이블 만들기
M3셀부터 시작하는 표 범위에 커서를 넣은 후 삽입 > 피벗 테이블을 누르고, 현재 워크시트의 Q3셀을 위치로 선택한 다음 동호수, 구분과 약정금액을 클릭하면
아래와 같이 동호수별 구분별 약정금액이 표시됩니다.
중복된 항목을 제거한 후 피벗 테이블을 만드는 것에 비해 엄청 복잡합니다.
2. 구분별 약정금액과 납부금액 구하기
중복된 항목 제거를 하면 납부금액이 한 개만 남으므로 안되고, 파워 피벗을 이용해야 합니다.
A3셀에 커서를 넣은 후 삽입 > 피벗 테이블을 한 후 이번에는 새 워크시트에 만들어보겠습니다.
동호수, 구분, 약정금액, 납부금액에 체크하면 약정금액과 납부금액이 모두 합계로 표시되는데, 합계 : 약정금액을 클릭한 후 값 필드설정에서 합계를 최대로 변경합니다.
그러면 아래와 같이 표시되는데, 동호수에 표시되는 약정금액도 최댓값으로 표시되므로
동호수 합계를 제거하고, 보고서 레이아웃을 테이블 형식으로 바꾸고 모든 항목 레이블 반복에 체크합니다.
그러면 아래와 같이 가로로 형식이 바뀌고, 동호수가 반복됩니다.
총합계를 제외하고, 범위를 선택한 후 복사해서 값으로 붙여 넣습니다.
값으로 붙여 넣은 데이터를 기준으로 피벗 테이블을 만듭니다.
F3셀에 커서를 두고, 삽입 > 피벗 테이블을 누른 후 기존 워크시트, K3셀을 클릭합니다.
그리고, 동호수, 구분, 최대 : 약정금액, 합계 : 납부금액을 체크하면 약정금액과 납부금액의 합계가 표시됩니다.
피벗 테이블을 만든 후 다시 값으로 붙여 넣은 후 피벗 테이블을 만드는 방법 괜찮은 방법입니다.
[계약금 위치 변경하기]
위 피벗 테이블을 보면 계약금이 맨 위에 있으면 좋겠는데 정렬이 돼서 세 번째에 위치합니다.
이 때는 계약금을 클릭한 후 마우스 커서를 경계선에 댔을 때 십자 화살표키가 나오면 마우스 왼쪽 버튼을 누른 채로 1차 중도금 위로 올리면 됩니다.
그러면 다른 동 호수의 계약금도 위치가 바뀝니다.
'Excel' 카테고리의 다른 글
| 색 기준 필터, 고급 필터, sum+if 배열 함수 (0) | 2023.02.23 |
|---|---|
| 두 가지이상 조건을 만족하는 값을 구해주는 ~Ifs 함수 (0) | 2023.02.22 |
| 피벗 테이블 필터에 숫자가 보이지 않을 때 (0) | 2023.02.20 |
| Lambda 함수의 매개변수를 옵션으로 지정 및 설명 입력 방법 (0) | 2023.01.20 |
| Lambda 함수 매개변수 2개 이상인 경우(2) (0) | 2023.01.19 |