기초 완성 3주 챌린지 2주차 강의_128회(데이터 검증/동적 범위/데이터 관리 규칙 4개/피벗 테이블)

😲 느낀 점

2주차 수업 1 교수는 데이터를 다른 사람과 공유할 때 다른 사람이 데이터를 올바른 형식으로 만들 수 있도록 제한하는 방법을 배웠습니다.

2 교시에서는 올바르게 데이터를 관리하는 규칙을 배웠다.

그리고 올바르게 만들어진 데이터는 복잡한 함수를 쓰지 않고 쉽게 피벗 테이블만으로도 데이터를 원하는 형식으로 가공하고 슬라이서를 추가하여 자동화까지 만들 수 있다는 것을 배웠다.

형제가 강의 개시 전, 강의중에 AI 기능과 데이터의 관련에 대해서 이야기를 해 주었다.

AI 기능은 계속 발전하고 있지만, 모든 AI 기능은 백 데이터베이스입니다.

데이터를 표로 작성하지 않고 범위로 쓰면 Excel에서 데이터를 자동화할 수 없게 된다.

그래서 앞으로는 상당한 자동화 기능은 표로 하지 않으면 가능해질 것이다.

그래서 데이터를 잘 관리해야 하지만 자동화할 수 있다.

따라서 데이터를 적절하게 관리하는 것이 정말 중요합니다.

chat GPT로 매우 hot이 되었을 때, 솔직히 나는 “사람이 AI에 의존하게 되고, 서서히 AI에 지배될 것 같고, 쾌적함에 길들여진 사람들은 점점 어리석어질 것 같다”는 생각 에 무서움을 느꼈다.

그래서 들은 생각은 단순히 지금 잠시 편하게 하려고 AI 기능을 사용해서 어리석게 되지 않았고, 조금은 불편하더라도 내가 직접 하는 방법을 기억하자는 생각을 했다.

그런 생각을 가지고 있는지 별로 없었고, 형제의 채팅 GPT x PPT 자동화 강의를 듣고 나서의 생각이 바뀌었다.

AI에 의존하고 싶지 않다는 이유로 시대에 떨리고 혼자 늦는 것은 더 위험할지도 모른다는 생각이다.

이번 강의를 듣고 나서는 AI 기능을 어떻게 하면 더 잘 사용할 수 있는지를 생각하게 된 것 같다.

그 중 가장 기본이 내 데이터를 정리하고 관리하는 것이라고 생각했습니다.

📒 스터디

1. 올바른 데이터 구조의 세 가지 규칙

📑강의 내용, 꿀, 보충 학습의 정리

I. 데이터 검증/동적 범위 생성

1. 데이터 검증

데이터가 유효한지 확인하고 유효한 데이터만 입력할 수 있도록 제한하는 기능
셀에 입력할 수 있는 데이터의 종류를 제한하고, 제한된 데이터를 쓸 때에 에러 메세지를 나가 좋아하도록 지정할 수 있다.

1) 목록 상자 만들기

작성할 데이터 유형 제한 셀 범위 지정→ 데이터 탭→ 데이터 검증 → 설정 탭→ 제한 대상에서 리스트 선택→ 오리지널에서 목록에 들어가는 조건의 범위 명세

목록 상자를 사용하는 바로 가기: Alt + ↓
오빠 허니칩☆ Alt + ↓는 목록 상자 / 필터 / 일반 데이터 셀하지만 사용 가능
- 일반 데이터 셀에서 사용하는 경우 상단에 생성 된 데이터를 자동으로 인식하기 위해 편리하게 입력 할 수 있습니다.

2) 수치 데이터로만 작성시킨다

작성할 데이터 유형 제한 셀 범위 지정→ 데이터 탭→ 데이터 검증 → 설정 탭→ 제한 대상에서 정수 선택→ 최소값과 마지막 값에서 내가 원하는 값로 지정

3) 불필요한 형식으로 데이터를 쓸 때 발생하는 오류 메시지의 구문 변경

데이터 검증 적용 셀듣다 드래그후, 데이터 탭→ 데이터 유효성검사 → 오류 메시지 탭→ 제목그리고 오류 메시지 필드에 내가 원하는 경고문 작성→확인

제목실버 오류 메시지 제목에 만들고, 오류 메시지는 오류 메시지 본문 내용에 표기된다.

4) Excel 파일을 공유할 때 일부 셀만 타인이 편집 가능하게 만드는 방법

엑셀 시트는 기본적으로 셀 록된다.

그 중에서 수정 가능하게 하는 셀만 셀 락 해제한 후, 시트 보호를 해 주면 된다.

전체 셀이 잠겨 있는지 확인하는 방법: 셀 헤더의 맨 왼쪽에 삼각형을 눌러 전체 시트를 선택아래 →홈 탭→ 서식→셀락되어 있는지 확인

일부 셀만 편집 가능하게 만드는 방법: 편집 가능하게 만들기부분만 드래그뒤로 홈 → 서식 → 셀 잠금을 클릭하여 셀 잠금 해제아래 → 드래그 해제 → 서식 → 시트 보호 → 비밀번호 설정→확인→ 다시 비밀번호 입력→ 확인

오빠 허니칩☆ 공유 받는 사람이 셀을 매일 클릭하지 않아도 편집 가능한 셀에만 배경색을 넣고 별도로 표기합니다.

ex. “노란색 배경 셀만 편집 가능” 식으로 표기하십시오

2. 표에서 동적 범위 만들기

일반적으로 목록 상자의 범위는 고정되어 있습니다.

따라서 나중에 데이터를 추가해도 자동으로 목록 상자에 추가되지 않습니다.

그러나 목록 상자의 범위를 동적 범위로 설정하면 목록 상자에 자동으로 추가할 수 있습니다.

다이나믹 레인지로 할 때 테이블을 만드는 방법이 있다.

1) 표를 만드는 방법

목록 상자에 들어가는 부분 드래그뒤로 삽입 탭→ 테이블 클릭 or 바로 가기 Ctrl + T

데이터가 표로 변경되면 표 하단에 분할 기호가 표시됩니다.

분할 기호 아래에 데이터를 추가해도 테이블 범위가 자동으로 증가합니다.

2) 표로 바꾸면 좋은 점 3개

동적 범위 가능: 범위가 자동으로 확장됩니다.
표의 자동 스타일 만들기
- 스타일이 싫다면 테이블 디자인 탭→ 표 스타일에서 스타일이 없는 것에서 선택할 수 있다.

구조화된 참조 방식 가능
- 테이블 이름을 내가 만든 후에는 나중에 테이블 이름으로 데이터를 호출 할 수 있습니다.
- 데이터로 테이블을 호출할 때 테이블의 일부만 호출할 수 있습니다.

테이블 이름을 변경하는 방법: 테이블 디자인 탭 →왼쪽 표 이름 → 원하는 이름 만들기

표의 일부만 로드하는 방법: = 테이블 이름 작성 → 괄호 ( 작성→ 원하는 테이블의 일부 데이터 지정 후 Tab키 → 괄호 ) 만들기→Enter

- 예를 들어, VLOOKUP 함수로 범위를 지정하는 경우, 셀 주소로 지정하는 것이 아니라, 테이블의 일부의 항목으로 호출할 수 있다.
  
  이렇게 하면 테이블에 입력된 데이터가 증가 또는 감소할 때마다 이에 따라 자동으로 호출할 수 있습니다.

3) 동적 범위에서 목록 상자 만들기

목록 상자 만들기 셀등 드래그→ 데이터 → 데이터 검증→ 설정 → 제한 대상 → 리스트 → 오리지널에 =INDIRECT(“원가표(제품명)”) 작성→확인

** INDIRECT 함수: 문자를 기반으로 범위를 참조하는 함수

3. 기능 기능 일부 사용 (VLOOKUP/ SUM IF)

1) VLOOKUP 함수

세로 방향(VERTICAL)으로 검색(LOOK UP).
예(왼쪽 표를 참고로 박동주의의 평균을 구하는 방법)

들어가는 식
= VLOOKUP (백동주 셀 주소, 검색 범위, 해당 범위 내에서 ‘평균’이 배치된 열이 왼쪽에서 몇 번째인지, 일치 값으로 0 작성)

주의사항
- 검색할 값이 범위의 맨 왼쪽 열에 있도록 지정해야 합니다.
- 검색할 값이 테이블에 여러 개 있는 경우 가장 빠른 행 값 중 하나그냥 찾아준다.
  - 중복 검색 값에 대해 각각 높은 값을 새로 설정하고 중복 값의 이름을 다르게 구별하십시오.새로 생성된 고유값을 검색 범위의 맨 왼쪽 열에 정렬한 후 VLOOKUP 함수 사용
  - ex) 새로운 고가로 이름 + 구분 값로 지정

이름과 구분된 값을 결합하여 새 고유값 생성(중복된 값 삭제) _형제 실습 파일

2) SUM IF함수 이용시 주의사항

셀 머지 기능이 되어 있는 경우, 함수의 기능이 잘 되지 않는 경우가 있다.

따라서 가능한 한 셀 병합 기능을 해제하는 것이 좋습니다.

함수 동작이 올바르게 구현되었거나 오른쪽 상태 행으로 다시 계산하는 것이 매우 중요합니다.

셀 병합 기능을 끄고 빈 셀을 빠르게 채우는 방법

셀을 병합한 부분 드래그 후 셀 병합 해제 → Ctrl + G → 옵션 → 빈셀 클릭 → 선택한 빈 셀 중 활성 셀에 = 바로 위의 셀 주소**입력 → Ctrl +Enter 누르면 빈 셀이 모두 채워집니다.

** <セルマージで作成された値>는 병합 해제 시 ‘병합된 셀’ 중 가장 빠른 행과 열에 만들어집니다.

그래서 「가장 빠른 행」의 바로 다음 순서의 셀에 「가장 빠른 행」의 셀」어드레스를 작성해 주는 것 같다.

※빈센에 기입된 데이터는 수식으로 되어 있다.

이것을 다시 값으로 대체하는 프로세스 필요한다.

셀을 병합하고 분해된 부분을 드래그 → Ctrl + C → Ctrl + Alt + V → 값 선택→확인

항목 분류된 값(카테고리)을 한 번만 다시 작성해야 할 때

기존에 생성된 데이터 Ctrl + C (복사) → 원하는 곳에 Ctrl + V (붙여넣기)→ 데이터 → 중복 항목 삭제 클릭
or 최신 버전의 Excel을 사용하는 경우 =UNIQUE 함수 사용

II. 데이터 규칙 및 피벗 테이블 만들기

1. 데이터 규칙 4개

1) 헤더는 반드시 한 줄로 관리한다

1행으로 하려면 헤더의 2행 중 위의 행을 세로 쌓아 블록의 1열로 합니다.

2) 원 데이터에는 셀 머지 절대 사용 금지

3) 집계 데이터를 원래에서 제외

합계 들어간 데이터를 피벗 테이블로 하면, 합계까지 포함되어, 원의 데이터의 2배의 값으로 스플래시가 된다.

4) 무조건 데이터는 종방향으로 블록 적층되어야 한다.

세로 블록 스택은 내 생각에 카테고리 분류처럼 보입니다.

예를 들어 하나의 범주이지만 해당 범주에 포함된 항목을 각각 열로 설정하면 옆으로 길게 늘어납니다.

그 경우, 데이터가 축적될수록, 데이터 관리가 어려워져, 정리하고 싶은 경우에서도 어디에서 손을 잡을지 모르는 단계가 되어 버린다.

2. 피벗 테이블

1) 피벗 테이블 만들기

표 전체 선택 (1 셀 선택 후, Ctrl + A) → 삽입 탭→ 피벗 테이블 → 원하는 장소 클릭(기존 워크시트라면 추가하려는 부분의 빈셀 클릭)→확인

2) 피벗 테이블 구조 만들기

피벗 테이블 필드가 나오면 십자를 그려 각각 대응하는 숫자 안에 넣고 싶은 부분을 드래그하여 드래그하여 넣으면 된다.

3) 피벗 테이블 범위 자동화(동적 범위로 설정)

리스트 박스 작성시와 같이 피벗 테이블 범위 자체를 고정한 상태로 하면, 원래의 데이터에 새로운 데이터를 추가해 새로운 로고침을 해도 반영되지 않는다.

따라서 이때도 피벗 테이블의 범위를 표로 해준 후 피벗 테이블을 만들어 줘야 한다.

전체 범위를 선택한 후, Ctrl + T을 탭하여 테이블로 만들기(테이블로 동적 범위 만들기) → 테이블 범위에서 셀 하나만 선택하여 피벗 테이블 만들기
(테이블 범위 내의 셀만 지정하여 피벗 테이블을 작성해도 피벗 테이블 범위에 자동으로 테이블이 들어갑니다.

)

4) 계산 필드

원시 데이터의 값을 계산하고 새 값(예: 평균, 금액 등)을 검색하려는 경우 새 열을 추가하면 다른 시트의 원시 데이터에도 새 열을 계속 추가해야 합니다.

이때 피벗 테이블에서 자동으로 계산해주면서 필드를 새롭게 만들 수 있다.

피벗 테이블 중 1셀 클릭→ 피벗 테이블 분석 탭→ 필드, 아이템, 세트→ 계산 필드 → 이름에 추가하려는 열의 이름 작성→ 수식에 수식에 들어가는 필드를 두 번 클릭하여 수식 입력→확인→피벗 테이블 리스트에 가면 계산 필드가 추가되어 있다→내가 좋아하는 장소에 해당 항목을 드래그해 넣어준다

5) 피벗 테이블 행을 그룹화할 때 테이블 형식으로 변경

피벗 테이블 행에 여러 필드가 있는 경우 그룹화됩니다.

처음에는 요약 형식이며 각 목록에는 하나의 열에 두 개 이상의 필드가 모두 포함됩니다.

이 경우 피벗 테이블을 사용하여 데이터를 처리해야 할 때 함수를 작성하는 것이 불편할 수 있습니다.

피벗 테이블 중 1셀 선택→ 디자인 탭→ 보고서 레이아웃 → 표 형식으로 표기 선택

6) 필터 대신 슬라이서 삽입

피벗 테이블을 사용할 때 실무에서는 가능한 한 필터를 사용하지 않는 것이 좋다.

대신 슬라이서를 삽입하고 싶은 것만 보는 것이 좋습니다.

슬라이서를 삽입하고 그래프를 추가하면 내가 원하는 것만 그래프로 표시하는 자동화를 만들 수 있습니다.

피벗 테이블 중 1셀 선택→ 삽입 탭→ 슬라이서 삽입 → 원하는 아이템 선택→확인

한 슬라이서에서 여러 항목을 클릭하려면 Ctrl 키를 누른 상태에서 선택합니다.

_ 형제 연습 파일

III.보충 학습 강의

회사원이 반드시 알아야 할 비즈니스 엑셀 매너

1) 코어가 되는 요약 시트를 맨 앞으로 이동하고, 선택한 셀도 맨 앞 시트의 셀 A1에 클릭한 채로 저장(Ctrl + S) 풀다

2) Excel 시트로 완성된 보고서를 보기 쉽게 눈금선/헤더 숨기기

보기 탭→ 표시 탭→ 눈금선/헤더박스 체크 해제

3) 데이터가 모니터에 들어가지 않으면 프레임을 고정하고 헤더를 고정합니다.

선택한 셀의 왼쪽과 바로 위를 기준으로 프레임 고정이 생성됩니다.
요약 바로가기: Alt → W → F → F

4) Excel에서 문서 형식의 파일을 만들 때 그에 맞는 레이아웃으로 변경하십시오

보기 탭→ 페이지 나누기 미리보기 or 페이지 레이아웃로 변경하십시오.

5) 엑셀 파일을 공유할 때 편집 가능한 부분을 한 번에 확인할 수 있도록 그 부분만의 문자색/셀 배경색을 변경하여 언급한다.

함수가 입력된 셀이 아닌 값만 입력된 셀의 서식을 변경하는 방법
- 데이터가 입력된 모든 선택→ Ctrl + G → 옵션 → 상수 선택하면 값으로 작성된 셀만 선택됩니다 → 문자색/셀 배경색 변경하다

2. Excel 데이터 정렬 기능 – 초보자 완벽한 가이드

세로로 쌓인 데이터에 대해 정렬하려는 열에서 셀 1개를 선택한 다음 오름차순/내림차순으로 정렬합니다.

해당 열에서의 승강 순서로 전체표의 행이 바뀐다.

1) 방법

데이터 탭→ 정렬 →승강순
빠른 실행 툴바위로 승강 순서를 넣어 사용

2) 주의사항

다만, 몇번인가 바꾸어 보면, 원래의 기존의 행 순서로 복구하기 어려운 일이 있다.

항상 테이블의 왼쪽에 테이블 행의 고유 순서 열을 만듭니다.

그러면 마지막으로 고유순열로 정렬을 하여 원래의 원래 행순으로 돌아갈 수 있다.

3. 회사원이 반드시 알아야 할 필터 기능(자동 필터 사용법, 교재 p.281~)

1) 자동 필터와 피벗 테이블의 구분

자동 필터: 특정 항목의 즉각적인 분석 결과 또는 상황을 파악해야 하는 경우 사용
피벗 테이블: 원본 데이터의 전체 상황을 파악하고 지속적인 관리(대시보드 등)가 필요한 경우 사용

2) 사용법

필터 추가: 하나 셀 클릭 후 데이터 탭→ 정렬 및 필터 탭에서 필터 or 바로가기 Ctrl + Shift + L
필터 지우기
- 특정 부분만 필터 지우기: 열 헤더 셀의 오른쪽에 있는 필터 버튼 클릭→OOO에서 필터 해제 클릭
- 필터 초기화: 데이터 탭→ 클리어 또는 데이터 탭에서 필터 버튼(Ctrl + Shift + L) 두 번 클릭하여 기존 적용된 필터를 삭제된 초기 상태 필터로 만들 수 있습니다.

3) 주의사항

중간사이에 데이터가 비어 있는 부분이 있는 경우는, 1 셀 뿐만이 아니라 필터를 지정하는 모든 부분을 직접 드래그하고 나서 필터 해야 한다.

4) 필터 사용 팁

데이터의 종류(숫자, 텍스트)에 따라 다양한 필터 조건을 사용할 수 있으며, 이를 이용하여 검색 기능으로 활용할 수도 있다.
컬러 기준 필터를 통해 특정 컬러로 적용된 셀만 필터링할 수 있다.
필터와 정렬 기능을 함께 사용해야 하는 경우 정렬 기능을 먼저 사용한 후 필터를 사용해야 합니다.

5) 데이터를 보고 ‘특정 데이터 값’을 가진 것만 필터링하고 싶을 때

하는 방법 : 특정 데이터 값을 가지고 셀 오른쪽 클릭 → 필터 → 선택한 셀 값에서 필터링
필터링된 데이터를 드래그하면 우측 하단의 상태바에서 합계, 최대값, 최저값 등을 보면서 데이터 분석을 수행할 수 있다.
원래 데이터를 다시 보려면 Ctrl + Shift + L에서 필터 해제

4. 회사원 필수 단축키 콤보, ‘콘슈엘’ 단축키

특정 셀을 마우스 오른쪽 버튼으로 클릭하여 필터링하는 방법 (3-5)은 셀에 기록 된 채로 입력 된 값만 필터링됩니다.

1) 특정 내용을 포함한 모든 셀을 검색하고 싶을 때하는 방법(순차적으로 진행)

a. Ctrl + Shift + L : 필터 적용

b. Ctrl + ↑ : 해당 열의 헤더로 이동

c. Alt + ↓: 필터 확장

e. E : 필터 검색 창을 이동합니다.

f. 내가 원하는 필터 내용을 작성 → Enter: 필터 적용

2) 내가 필터링 한 데이터 만 새 시트에 저장하고 싶을 때

a. Ctrl + A : 필터링된 데이터에서 전체 데이터를 선택했습니다.

b. Ctrl + C : 필터링된 데이터 복사

c. Ctrl + N : 새 통합 문서 작성

d. Ctrl + V: 붙여넣기

e. Alt → H → O → I : 붙여넣은 데이터의 열 너비를 맞춥니다.

(두 사람 연상해 암기!
호이호이!
)

cf. Ctrl → W : 복사한 데이터의 열 너비와 동일하게 맞춥니다.