작성일: 2023. 3. 24.
🔥 엑셀이 쉬워지는 올바른 데이터 관리, 하나만 기억하세요! - 자동화 보고서 🔥
✍ 사전학습자료 (총 20분)
[예습] 엑셀 데이터 관리 핵심 규칙 4가지, 정말 중요합니다!
🚀 보충학습자료 (총 20분)
🎯 미션 목표
- 실무에서 올바른 데이터 관리가 중요한 이유를 이해한 후, 함수를 사용하지 않고 간단한 기능과 클릭만으로 자동화 보고서를 만드는 과정을 실습합니다.
✍ 스터디 노트 (요약)
• 아래 빈칸을 채워서 올바른 데이터 구조의 3가지 규칙을 완성하고, 각 규칙에 대해 간략히 설명합니다.
| ① ○○○은 절대 사용하지 않습니다. ② 머릿글은 반드시 ○○로 관리합니다. ③ ○○ 데이터는 원본에서 제외합니다. |
① 셀 병합은 절대 사용하지 않습니다.
- 셀 병합이 되어 있으면 셀 영역 선택이나 이동이 어려워지고 중간에 누락되는 데이터가 생깁니다.
② 머릿글은 반드시 한줄로 관리합니다.
- 기존 범위에 머릿글이 여러 줄로 작성된 경우 표로 변경하면 첫번째 행만 머릿글로 인식되기 때문입니다.
③ 집계 데이터는 원본에서 제외합니다.
- 원본 데이터에 집계(합계)가 포함된 순간 데이터가 두 배로 뻥튀기되는 오류가 발생하기 때문입니다.
• 실무에서 올바른 자료 관리를 위해 꼭 지켜야 하는 "세로방향 블록쌓기" 규칙에 대해, 아래 그림을 예제로 간략히 설명합니다.
날짜를 가로방향으로 나열하면 단순히 데이터가 막연히 쌓이기만 할 뿐 데이터 관리가 되지 않는다.
날짜를 하나의 블록으로 인식해 세로 방향으로 쌓고, 제품명과 수량 필드 역시 나란히 블록쌓기를 하면
날짜별 제품, 판매량, 판매금액 등 모든 데이터를 자유롭게 가공할 수 있게 된다.
* 데이터 바꾸기는 오빠두엑셀 유튜브 파워쿼리 API 특강이나 교재 열 피벗 해제 기능 참고
• 피벗테이블 만들기의 핵심 규칙인 '십자가 규칙'에 대해서 간략히 설명합니다.
✔️ 피벗테이블은 :+:십자가:+:만 기억하자!
- 피벗테이블의 가로열과 세로열에 가상의 십자가를 그려넣고, 기준이 되는 데이터와 추출하고자 하는 값을 십자가 행렬에 맞춰 배치하면 올바른 데이터구조를 만들어낼 수 있다.
• 피벗테이블과 슬라이서를 활용해 나만의 멋진 자동화 보고서를 만들어보세요! 보고서를 완성한 후, 스크린샷을 첨부합니다.
✍ 스터디 노트 (종합)
✏️ 지난 주 내용 복습
✔️ 빠른채우기 - 단축키 ctrl + e
✔️ 자동채우기 - 날짜 범위 더블클릭 땅땅 / 격일 설정도 가능함
✔️ 피벗테이블, VLOOKUP 함수 등 모든 기능들은 다 세로방향으로 동작한다
✔️ 자낳괴 엑셀, 셀 참조 방식은 "$", 셀 서식은 #,##0, 세미콜론(양수;음수;0;텍스트)
✔️ 조건부 서식 - 데이터 막대, 아이콘 강조, 스파크라인
🤓 오늘은 엑셀의 본질을 들여다 보는 시간!
✏️ Copilot (코파일럿) - MS에서 발표한 대박 기능
✔️ AI 기능을 오피스에 심는다 - 오피스의 반복 작업들을 AI가 대신 해 주는 시대가 오고 있다
⚠️ 하.지.만. 같은 데이터라도 어느 시각으로 보느냐에 따라 다양한 시각화가 가능함. 아직 대비할 시간은 있다
✏️ 엑셀을 잘 하려면? 엑"셀"을 잘 해야 한다!
✔️ 모든 업무 자동화는 올바른 "데이터"에서부터 시작된다
- 배운내용 소내용 소내용 중간내용
- 배운내용 내용 내용 내용
- 내용 내용
⚠️ 주의사항은 빨간색
✏️ 내가 만든 파일을 지키는 방법 - "데이터 유효성 검사"
✔️ 원하는 값(유효한 데이터)만 넣게 하고 싶을 때 (데이터 유효성 검사)
- [데이터] 탭 - 데이터 유효성 검사
- 제한 대상: 목록 / 원본: 입력을 제한하고 싶은 제품명 범위 선택
- 목록상자가 적용된 셀에서 Alt + ⬇️를 누르면 목록을 바로 입력할 수 있다
- Alt + ⬇️는 1) 일반 필터, 2) 일반 데이터에도 사용 가능
✔️ 범위에 숫자만 입력되도록 제한하기
- [데이터] 탭 - 데이터 유효성 검사
- 제한 대상: 정수 / 최소값 & 최대값 설정
- 제한에 걸리는 값을 입력했을 때 뜨는 경고 메시지도 친절하게(?) 넣을 수 있다.
✔️ 시트 보호로 원하는 영역만 잠그기
- [홈] 탭 - 서식 - 셀 잠금 해제 - 시트 보호
- 셀 전체를 잠그고, 편집 가능한 범위만 잠금 해제하는 원리
- 편집 가능한 영역만 색깔 등으로 표시 해주면 좋음
✔️ 목록 상자를 범위로 만들 경우 제한사항
- 목록 상자에 새로운 값을 추가할 때 자동으로 반영되게 하려면, 범위를 표로 바꿔주면 된다!
- 목록 상자 범위 선택 - [삽입] 탭 - 표 (또는 단축키 Ctrl + t) (머리글 포함)
✔️ 엑셀에서 표를 쓰면 좋은 이유
1. 범위가 자동으로 확장된다 (동적 범위)
2. 스타일을 자동으로 꾸며준다 (스타일이 마음에 들지 않으면? [테이블 디자인] 탭 - 표 스타일 - '없음')
3. 표 이름을 지정해 줄 수 있다 ([테이블 디자인] 탭 - 속성)
4. 표 이름으로부터 구조적 참조를 사용할 수 있다 - "표 이름["
✔️ 자동으로 확장되는 목록상자 만들기
- 데이터 유효성 검사 - 원본: =INDIRECT("표이름[머릿글]")
※ INDIRECT 함수: 문자를 바탕으로 범위를 참조하는 함수
⚠️ 누적되는 데이터 집합을 인식하고 자동화 문서를 만들기 위해서는 실무에서 표를 쓰는 것이 유리하다!
✏️ 다중조건 VLOOKUP 간단 사용법
✔️ VLOOKUP 함수란? "Vertical" 세로 방향에서 데이터를 찾는 것
- (예) 박동주 학생의 평균 점수를 찾으려면?
- VLOOKUP의 특징
1. 찾을 값은 항상 맨 왼쪽에 있어야 한다 (박동주를 찾으려면 이름이 맨 왼쪽에 있어야 함)
2. 중복데이터가 있으면 첫 번째 행값만 검색됨 (박동주 1차 점수만 검색됨)
3. 항상 고유값을 기준으로 데이터를 찾는다
✔️ 특정 학생의 n차 시험 점수를 찾는 법 (다중조건을 만족하는 VLOOKUP)
- 고유값 설정해 주기 (이름&구분)
- 고유값 열을 왼쪽으로 옮겨야함 - (복습!) 열 선택 후 Shift 키 누른 채로 마우스 드래그
- 이렇게 하면 INDEX, MATCH, 다중조건 VLOOKUP을 쓰지 않더라도 데이터 관리 가능!!
✏️ 실전 SUMIF 함수 사용법
✔️ SUMIF 함수란? 뭔가를 "집계"하는 것, 어떤 조건을 만족하는 값을 합치는 것
- 집계하는 기준을 만들어 줘야함 → 고유값만 뽑아주기!!
- [데이터] 탭 - 중복된 항목 제거 - 현재 선택 영역으로 정렬 (or 엑셀 최신 버전 UNIQUE 함수 (고유값 반환 함수))
- =SUMIF(조건범위(대분류C:C) 중 가구(H5)에 해당하는 금액(F:F)을 모아 합계를 계산
⚠️ 엑셀 미니맵으로 데이터 검산은 필수
✏️ 실무에서 종종 발생하는 셀병합 오류
⚠️ 셀 병합은 역시 안 하는 것이 좋다.
- 셀 병합 때문에 대분류 값이 입력되지 않은 셀이 있어서 합계에서 누락될 수 있기 때문!!
- 해결방법: 셀 병합 풀고, Ctrl + g - '옵션' - '빈셀' 선택 - 확인 - 그 상태에서 "=가구" 입력 후 Ctrl + 엔터
- 단, 입력된 함수값들은 일반 값으로 변환해 줘야함 (Ctrl + Alt + v (선택하여 붙여넣기) - '값')
✏️ 함수 vs. 피벗테이블, 여러분의 선택은?
✔️ 모든 자동화의 핵심은 데이터 구조!!
- 직장인의 90%가 기피하지만 이제는 꼭 알아야 하는 기능 - 표(Table)
- 솔직히 업무의 대부분은 합계/평균 함수만 거의 사용하고, 간단한 집계는 '피벗테이블'로 만들 수 있다. (십자가)
- 이 모든 기능을 잘 활용하려면 데이터구조를 잘 숙지해야 한다!!!
✔️ 효율적인 엑셀 작업을 위한 올바른 데이터 구조 규칙 3가지
1. 머릿글은 반드시 '한 줄'로 관리하기
2. 원본데이터에는 셀 병합 절대 사용 금지!!
3. 집계 데이터는 원본에서 제외하기 (피벗 테이블 만들 때 데이터 오류 발생함)
* 데이터는 무조건 "세로방향 블록쌓기"!!!
* 데이터 바꾸기는 오빠두엑셀 유튜브 파워쿼리 API 특강이나 교재 열 피벗 해제 기능 참고
✏️ 피벗테이블로 알아보는 데이터구조의 중요성
✔️ 피벗테이블은 :+:십자가:+:만 기억하자!
- 범위 선택 Ctrl + a - [삽입] 탭 - [피벗 테이블] - 기존 워크시트 - 빈셀 클릭
- 데이터구조만 잘 지키면 피벗 테이블을 잘 만들 수 있다.
✏️ 표 + 피벗테이블로 만드는 자동화 보고서
✔️ 원본 데이터가 변경/추가되었을 때는?
- 테이블 선택 - [피벗 테이블 분석] 탭 - 모두 새로고침
- 기존 원본 범위 안에서 데이터를 변경하면 피벗 테이블을 새로고침했을 때 반영됨
- 하지만 데이터를 새로 추가하려면 피벗 테이블 선택 - [피벗 테이블 분석] 탭 - 데이터 원본 변경을 해야함
⚠️ 원본 데이터를 표로 바꿔두면 (표 만들기 단축키: Ctrl + t) 데이터 누적/변경/추가시 새로고침하면 바로 반영됨!
✔️ 날짜를 연/월/분기별로 그룹화하기
- 날짜 우클릭 - 그룹 - 그룹화
- Shift + 마우스 휠 - 그룹 확장/축소
✔️ 원본 데이터에 새로운 데이터가 추가될 경우- 피벗 테이블에 계산 필드를 추가하면 원본 데이터는 유지한 채 계산된 값을 얹을 수 있다- [피벗 테이블 분석] 탭 - [필드, 항목 및 집합] - 계산 필드- 앗 조금 오류가 있는 것 같다.. 다음 시간에 다시 설명해주시기로 함
✏️ 실무에서 유용한 피벗테이블 레이아웃 설정
✔️ 각 지점의 '가전'별 판매 기록을 보려면?
- 지점 열 하나, 구분 열 하나로 분리해서 보기 위해서는 [보고서 레이아웃]을 활용하자
- 피벗 테이블 - [디자인] 탭 - 표시 형식 - 테이블 형식
⚠️ 실무에서 피벗테이블 필터는 되도록 쓰지 않는 것이 좋다
- 필터 대신 [피벗 테이블 분석] - [슬라이서 삽입]
🚀 2주차 퀴즈 풀기 (⬇️더보기)
[문제 1/5] 표를 참조하여 자동확장 목록상자를 만드는 함수는?
- 데이터 유효성 검사 - 원본: =INDIRECT("표이름[머릿글]")
※ INDIRECT 함수: 문자를 바탕으로 범위를 참조하는 함수
[문제 2/5] 선택된 여러 범위에 수식을 한 번에 입력하는 단축키는?
Ctrl + Enter
[문제 3/5] 다음 보기 중, 엑셀 표기능에 대한 설명으로 잘못된 것은 무엇인가요?
① 표는 오른쪽 끝과 아래에 새롭게 추가된 데이터를 자동으로 인식하여 범위를 확장한다.
② 범위를 표로 변경하면 표 스타일이 적용되며, 표를 구분하기 위해 적용된 스타일은 변경할 수 없다.
③ 범위를 표로 변경할 시, 기존의 셀 병합된 범위의 셀 병합은 모두 해제된다.
④ 기존 범위에 머릿글이 여러 줄로 작성된 경우, 표로 변경하면 첫번째 행만 머릿글로 인식된다.
② (표 스타일은 변경 가능하다)
[문제 4/5] 다음 보기 중, 올바른 데이터 구조에 대한 설명으로 잘못된 것은 무엇인가요?
① 집계데이터와 원본데이터는 동일한 시트에 관리한다.
② 머리글은 반드시 한 줄로 작성한다.
③ 세로방향 블록쌓기 규칙을 지켜서 데이터를 관리한다.
④ 셀 병합은 절대 사용하지 않는다
① 집계데이터는 원본에서 제외한다.
[문제 5/5] 피벗테이블에서 꼭 기억해야 하는 구조는?
십자가!!
'오피스' 카테고리의 다른 글
| 파워쿼리 기초 완성, 2주 특별 챌린지 - 1주차 (0) | 2024.01.25 |
|---|---|
| 종이접기 효과는 이제 그만! 매력적인 PPT 슬라이드 디자인하기 - 이미지, 인포그래픽, 애니메이션 (PPT 보고서 디자인 2주 챌린지) (0) | 2023.05.03 |
| 엉망진창 내 프레젠테이션, 손쉽게 고쳐보기 (PPT 보고서 디자인 2주 챌린지) (0) | 2023.04.28 |
| [오빠두엑셀] 2023년 엑셀 끝내기, 기초 완성! 3주 챌린지 (3주차) (0) | 2023.03.31 |
| [오빠두엑셀] 2023년 엑셀 끝내기, 기초 완성! 3주 챌린지 (1주차) (0) | 2023.03.17 |
댓글