📔 오늘의 공부 목록
올바른 데이터 관리 규칙
세로방향 블록 쌓기
셀 병합 풀고 데이터로 채워넣기
내 파일 지키기_데이터 유효성 검사 / 셀 잠금
VLOOKUP_2가지 이상 조건 검색 팁
SUMIF
피벗테이블_필드 / 그룹화 / 슬라이서 / 피벗 차트
올바른 데이터 관리 규칙
엑셀에서 가장 중요한 것은 올바른 구조의 원본데이터를 관리하는 것이다.
올바른 데이터 관리 규칙
✅ 셀 병합은 절대 사용하지 않기
✅ 머릿글은 반드시 한 줄로 관리하기
✅ 집계 데이터는 원본에서 제외하기
매우 💥중요중요중요💥하다.
원본데이터가 해당 규칙을 따르지 않는 경우, 다양한 함수와 피벗테이블로 가공하기 어려워 자동화 보고서를 만들 수 없다.
((자세한 내용은 저번 포스팅 참조🔽🔽🔽))
2023.01.20 - [📚배우기] - [1주차] 엑셀 API 기초 3주 완성_데이터 관리 규칙/열 피벗 해제
[1주차] 엑셀 API 기초 3주 완성_데이터 관리 규칙/열 피벗 해제
📔 오늘의 공부 목록 올바른 데이터 관리 규칙 파워쿼리를 이용한 열 병합 해제 그 밖의 유용한 엑셀 팁_피벗 테이블 필드 구성방법 / 슬라이서 / TEXT 함수 엑셀에선 원본데이터를 기본으로 하여
travel2mars.tistory.com
세로방향 블록쌓기
한 가지 더 중요한 규칙은 데이터를 쌓을 때 세로방향으로 데이터를 쌓아야 한다는 점이다. 가로방향의 데이터의 나열은 위의 규칙과 마찬가지로 데이터 가공의 한계가 있다.
하단의 첫번 째 이미지는 [올바른 데이터 관리 규칙]을 모두 지켰지만 올바른 원본 데이터라고 볼 수 없다.
그 이유는 ① 올바르지 않은 머릿글과 ② 데이터를 쌓는 방향이 틀렸기 때문이다.
[일자]를 머릿글이 아닌, [날짜]라는 머릿글과 [일자]는 데이터로 만들어 세로방향으로 정리한다면
위에서 언급했듯이 다양한 함수 / 피벗테이블 등을 적용할 수 있는 알잘딱깔센한 원본데이터가 완성된다.
셀 병합 풀고 데이터로 채워넣기
셀 병합을 풀고 난 후 빈 칸에 일일히 데이터를 채우는 엑셀 노가다 탈출하는 방법
셀 병합 해제 > Ctrl + G or F5 > 옵션 > 빈 셀 > 확인
=위의 셀 입력 후 Ctrl + Enter
※ 추후에 값 붙여넣기로 바꾸기
내 파일을 지키는 여러가지 설정_데이터 유효성 검사 / 셀 잠금
실무에서 한 파일을 여러 사람이 작성하는 경우가 많은데, 오타를 방지하거나 숫자만 입력해야하는 셀에 문자가 포함되지 않게 하여 내 파일을 지킬 수 있다.
목록상자 만들기
목록상자가 적용될 영역을 선택 > [데이터] 탭 > [데이터 도구]의 [데이터 유효성 검사] > 제한대상을 [목록] > [원본]영역은 목록상자로 나타낼 영역을 선택 > 확인
※ 목록 열기 : Alt + ↓
해당 키를 누르면 ① 마우스를 사용하지 않고 목록을 열 수 있다. 이 단축키는 ② 필터가 적용된 셀에서도 사용 가능하고, ③ 일반 셀에서 입력 시 윗쪽에 있는 데이터를 선택해서 입력할 수 있도록 목록창이 열린다.
목록상자에 자동 업데이트 되는 원본 만들기 : 표 만들기
원본에 데이터가 추가될 때마다 목록상자에 업데이트가 되도록 하려면, ① 원본 데이터를 표로 만들어준다.
※ 표 만들기 : Ctrl + T or [삽입] 탭 > 표
※ 표로 데이터를 관리하면 좋은 점
1. 범위가 자동으로 확장 됨, 하단에 데이터를 추가할 때마다 자동으로 표로 변환된다.
2. 스타일이 자동으로 적용된다.
3. 함수로 표와 표의 항목을 불러올 수 있다.(표에 데이터가 늘어와도 불러온 데이터에 자동으로 적용됨)
=표이름[원하는 머릿글 선택 후 Tab]
(표 이름은 [테이블 디자인]탭의 속성에서 입력할 수 있다.)
② 데이터 유효성 검사의 [원본] 영역에 INDIRECT 함수로 표를 입력한다.
((INDIRECT함수 : 문자를 바탕으로 범위를 참조함, 브이룩업이나 이프 등에도 활용 가능하며, 열이 표로 설정해야 함))
=INDIRECT("표이름"[머릿글]")
숫자만 입력하게 만들기
데이터 유효성 검사에서 정수로 설정
오류 메시지 창 내용 수정하기
데이터 유효성 검사에서 [오류 메시지] 탭에서 내용이 수정 가능하다.
셀 잠금 / 시트 보호
기본적으로 모든 셀은 잠금되어 있다.
데이터를 수정 가능한 영역을 선택 후 [홈] 탭 > 서식 > 셀 잠금
[홈] 탭 > 서식 > 시트 보호 > 비밀번호 입력 후 확인을 누르면 선택영역 외에 입력할 수 없다.
VLOOKUP 고유값 만들기
VLOOKUP 함수 사용 시 검색되는 값은 ① 왼쪽에 있어야 하며, ②중복된 값이 있을 경우 맨 위의 값만 데려온다.
따라서 두 가지 조건을 만족하는 값을 데려오기 위해 복잡한 함수를 사용하지 않고 '고유값'을 설정하여 쉽게 데려올 수 있다.
고유값은 원하는 데이터 두개를 "&"로 합쳐 만들면 손쉽게 VLOOKUP으로 데이터를 가져올 수 있다.
SUMIF 함수
중복된 값 제거하기
① [데이터] 탭 > 데이터 도구 > 중복된 항목 제거 > 현재 선택 영역으로 정렬 > 확인
② UNIQUE 함수 사용 (2013년 이후 버전부터 사용 가능)
= SUMIF(검색할 범위, 검색 조건, 더할 값)
※ 함수를 사용할 때는 데이터 마지막에 꼭 검산을 해야한다.
피벗 테이블 : [삽입] 탭 - 피벗 테이블
피벗 테이블 필드 : ➕
피벗테이블을 만들 때 행/열에 무엇을 넣을 지 헷갈린다면 십자가를 그려준다.
십자가 기준으로 같은 위치에 필드를 끌어넣으면 된다.
원본 데이터를 표로 관리할 경우
데이터가 추가될 시 새로고침만 해도 자동으로 추가된 데이터가 피벗테이블에 적용된다.
① 피벗 테이블 분석 탭 > 데이터 > 새로고침
② 피벗 테이블 우클릭 > 새로고침
피벗테이블 : 그룹화
날짜영역 우클릭 > 그룹 > 일/월/년 기준으로 그룹화 가능, 그룹화 시 +/- 단추가 생성된다.
※Shift 누른채로 휠 돌리면 단추가 한꺼번에 열리거나 닫힌다.
계산 필드 삽입 : 피벗 테이블 분석 탭 > 필드, 항목 및 집합 > 계산 필드
이름 : 머릿글
수식 : 원하는 필드를 더블클릭 하여 사칙연산을 넣는다
주의할 점은 곱셈/나눗셈을 사용할 때 총 합계 금액을 곱하거나/나누기 때문에 오류가 발생
예를들면 수량*단가를 각 항목별로 곱한 후 합해야 하는데, 총 합계에서 곱해버려 값이 달라진다.
따라서 덧셈/뺄셈에만 이용하거나 가중평균을 구할 때 활용하면 된다.
피벗테이블 보기 형식 변경
압축 형식으로 보여지는 피벗 테이블은 함수를 걸기 어렵다 (특히 VLOOKUP)
피벗 테이블을 누르면 생성되는 [디자인] 탭에서 보고서 레이아웃 > 테이블 형식으료 표시하면 해결된다.
자동화 보고서 만들기 : 슬라이서 / 피벗 차트
필터 보다는 슬라이서 삽입으로 있어보이는 보고서를 만들 수 있다.
[피벗 테이블 분석] 탭 > 슬라이서 삽입 > 원하는 머릿글 선택 후 확인
필터처럼 보고 싶은 값을 Ctrl키를 사용하여 중복으로 선택할 수 있다.
피벗 차트를 삽입하면 슬라이서 값에 따라 차트가 바로바로 반영되는 자동화 보고서가 완성된다!
[피벗 테이블 분석] 탭 > 피벗 차트
완성된 자동화 보고서💁♀️
슬라이서에 선택된 데이터에 따라 차트가 바로바로 반영된다.
손쉽게 보고서 5장 뚝딱 가능⭕
강의를 마치며...🌞
오늘 강의도 유익! 유익!!
저번 주 배웠던 것들을 바로 실무에 적용해서 전 보다 빠른 속도로 작업하는 나를 발견할 때의 쾌감이란(❁´◡`❁)
기존 점주 제공용 매출분석표를 수정을 해봐야겠다. 표를 사용하거나 피벗 테이블을 적용하여 시트별로 나눠져있던 월별 데이터를 한 페이지 내에서 편집이 가능하게 바꿀 수 있을 것 같다. (이번 분기 끝나고....1분기 넘나 빡셌다...🤦♀️)
'📚배우기' 카테고리의 다른 글
| [1주차] PPT 보고서 디자인 챌린지 (1) | 2023.04.28 |
|---|---|
| [3주차] 엑셀 기초완성 챌린지 (0) | 2023.03.26 |
| [1주차] 엑셀 기초완성 챌린지 (0) | 2023.03.11 |
| [3주차] API실전 : 실시간 주소 검색툴 만들기 (0) | 2023.02.03 |
| [2주차] 엑셀 API 기초 3주 완성_API 기초 사용법/QR코드 만들기 (0) | 2023.01.27 |
