[IT] 엑셀 대시보드, 초보자를 위한 90분 총정리 가이드 - 보고서의 > JAY Data Center

영상 요약 · 오빠두엑셀 · 1시간 37분 27초

은행에서 받은 통장 거래내역을 붙여넣기만 하면 집계가 자동으로 갱신되고, 모두 새로고침 한 번으로 PC 대시보드까지 완성되는 엑셀 가계부를 처음부터 끝까지 만드는 통합 강의다. 표 기능, 기초 함수, 데이터 유효성 검사, 피벗 테이블, 슬라이서, 자동화 서식을 한 흐름에 엮는다.

사전 주의사항
· 은행·공공 홈페이지에서 받은 파일은 대개 .xls 형식이다. 반드시 다른 이름으로 저장 → .xlsx로 바꾼 뒤 작업해야 한다. .xls로 저장하면 피벗 테이블과 피벗 차트가 날아가는 경우가 있다

데이터 구조 잡기
· 누적되는 데이터는 범위가 아니라 표로 관리한다. 아무 셀 선택 → Ctrl+A → 삽입 → 표 → 머리글 포함 체크
· 표 변환 후 줄무늬가 거슬리면 테이블 디자인 → 표 스타일 → 없음
· 표 오른쪽에 계정과목, 대분류, 거래일, 시간대 네 개 머리글을 추가
· 분류 기준은 두 개의 표로 분리한다. 계정과목 목록을 담은 분류표, 그리고 거래처 문자열과 분류를 짝지은 단어표. 표 이름은 테이블 디자인 탭 왼쪽에서 각각 분류표, 단어표로 지정

계정과목 목록 상자
· 계정과목 열 전체 선택 → 데이터 → 데이터 유효성 검사 → 제한 대상 목록
· 원본에 범위를 직접 넣으면 절대 참조라 데이터가 늘어날 때 확장되지 않는다. INDIRECT 함수로 분류표의 분류 머리글 범위를 받아오게 작성해야 자동 확장된다
· 표 구조적 참조 팁: =분류표 입력 후 대괄호를 넣으면 머리글 목록이 떠서 모두/머리글/특정 열을 골라 받을 수 있다
· 목록 상자 호출 단축키는 Alt + 아래 방향키

계정과목 자동 분류
· 440여 건을 손으로 분류하는 것이 가계부 실패의 1순위 원인이므로, 단어표의 포함 단어를 검색해 분류를 끌어오는 공식을 쓴다
· 인수 구성: 출력 범위는 단어표의 분류 열, 포함 단어 범위는 단어표의 포함 단어 열, 검색 대상은 왼쪽 내용 셀, 단어 범위 시작 셀은 F4로 절대 참조 고정
· 단어표에서 GS25를 기타 식료품에서 생활용품으로 바꾸면 결과가 전부 자동 갱신된다
· 자동 갱신이 안 되면 파일 → 옵션 → 언어 교정 → 자동 고침 옵션 → 입력할 때 자동 서식 탭에서 작업할 때 적용과 작업할 때 자동 서식 설정 두 개를 체크
· 분류 안 된 빈 값은 필터로 모아 오름차순 정렬하면 반복 거래처가 보인다. 예금, 특별적금, 오빠두엑셀, 주식배당금, 쿠팡 등을 단어표에 미리 추가해 두면 다음 회차부터 자동 분류된다

날짜가 문자로 들어오는 문제
· 은행 데이터의 거래일자는 날짜처럼 보이지만 문자라서 피벗에서 그룹화가 거부된다
· 날짜 열 전체 선택 → Alt, A, E, F 순서로 텍스트 나누기를 실행하면 숫자(날짜)로 변환된다
· 변환 후 피벗 테이블 우클릭 → 새로고침 → 거래일자를 다시 행에 올리면 연/월 인덱싱이 정상 작동한다

피벗과 차트 구성
· 피벗 테이블은 용도별로 복사해 쓰고, 피벗 테이블 분석 탭에서 피벗테이블_상위10개지출처럼 이름을 붙여 관리
· 상위 10개는 피벗의 상위 10 필터로 만들면 안 된다. 슬라이서 필터를 해제할 때 그 필터까지 같이 풀려 버리므로, 피벗 값을 별도 셀로 참조해 10행을 따로 구성한다
· 항목이 8개 이상이면 세로 막대는 머리글이 세로/대각선으로 눕고 가독성이 떨어지므로 묶은 가로 막대형으로 만든다
· 피벗 차트의 필드 단추는 피벗 차트 분석 → 필드 단추 → 모두 숨기기
· 가로 막대는 항상 역순으로 그려지므로 세로축 우클릭 → 축 서식 → 항목을 거꾸로 체크
· 서식: 도형 채우기·윤곽선 없음, 눈금선 색을 배경에 맞춰 짙게, 세로축 글꼴 흰색 8pt, 막대는 표준 색 주황, 데이터 레이블 추가 후 흰색 7pt 굵게, 데이터 계열 서식에서 간격 너비를 좁혀 꽉 찬 느낌

대시보드 마무리 디테일
· 시간대 피벗이 데이터 있는 시간만 나와 추세가 끊기는 문제: 해당 필드 설정 → 레이아웃 및 인쇄 탭 → 데이터가 없는 항목도 표시 체크하면 0시부터 23시까지 항상 표시된다
· 그래도 선이 중간에 끊기면 차트 우클릭 → 데이터 선택 → 숨겨진 셀/빈 셀 → 빈 셀을 0으로 처리
· 슬라이서 필터 시 합계가 따라 붙는 문제: 피벗 테이블 선택 → 디자인 탭 → 총합계 해제
· 상위 30개 거래내역 표는 차트가 아니라 선택하여 붙여넣기 → 연결된 그림으로 올려 실시간 반영시킨다
· 병합 셀 때문에 열 전체가 선택되는 문제: 병합하고 가운데 맞춤을 해제한 뒤, 셀 서식 → 맞춤 → 가로를 선택 영역의 가운데로 바꾸면 겉보기는 유지하면서 범위 선택이 정상화된다
· 마지막 업데이트 날짜: MAX로 최댓값을 구하고 Ctrl+Shift+3으로 날짜 서식. 문자열과 연결하면 서식이 깨지므로 TEXT 함수로 yyyy-mm-dd 형태로 감싼다
· 통장 잔액은 특정 열의 마지막 입력값을 가져오는 공식을 쓴다. 엑셀 2021 이후·365는 그냥 Enter, 그 이전 버전은 Ctrl+Shift+Enter 배열 수식
· 운용 방식: 통장내역 시트에 새 거래내역을 붙여넣고 대시보드 시트에서 데이터 → 모두 새로고침