[오빠두엑셀] 엑셀 기초 완성 3주 특별 챌린지 #2

 

🔥 2주차 챌린지 목표 :

• 실무에서 올바른 데이터 관리가 중요한 이유를 이해한 후, 함수를 사용하지 않고 간단한 기능과 클릭만으로 자동화 보고서를 만드는 과정을 실습합니다.

 

📚 스터디 노트 :

<작성한 파일을 지키는 방법! - 데이터 유효성 검사>

데이터 유효성 검사 & 목록상자

■ 데이터 유효성 검사 :

• 데이터 유효성 검사 : 셀에 조건을 걸어 이외의 조건을 입력할시 입력불가하게 경고창을 띄워준다.
• 목록상자 : 셀에 입력할 내용을 미리 지정해줘서 선택 가능하게 해준다.
• 적용할 셀 범위 드래그 → 데이터 탭 → 데이터 유효성 → 설정 → 유효정 조건 - 허용: 목록 클릭 → 원본 : 범위 드래그 → 원하는 항목만 입력 가능 (Alt + 아래화살표)
• 목록상자 적용 범위 : ① 필터 ② 일반데이터 셀
• Tip! 데이터 유효성 검사를 모르는 분들을 위해 [오류메시지]에 설명을 넣어준다.
• 오류메시지 : 적용할 셀 범위 드래그 → 데이터 탭 → 데이터 유효성 → 오류메시지 → 원하는 제목과 설명 입력

■ 시트 보호 :

• 홈 탭 → 서식 → 시트 보호 → 암호 설정

 

<앞으로 최소 5배 더 중요해질 주요 기능, '표' 사용법>

■ 표 만들기 :

• Ctrl + T
• 삽입 탭 → 표 → 테이블 만들기 - ✔ 머리글 포함해서 표에 사용할 데이터 범위 지정
• 표 만들기는 세로로 데이터를 쌓을 때 자동으로 표 범위가 적용&범위 확장이 되어서 표 테두리를 따로 만들어 주지 않아도 된다.
• 표를 만들면 처음에는 파란색 계열의 디자인으로 나오지만 변경 가능하다. 표 디자인에서 원하는 디자인 선택
• 표 이름 설정해주면, =표이름[ㅇㅇㅇ] 을 입력했을 때 범위가 지정됨
  • 표는 데이터를 추가해도 자동적으로 범위가 늘어나기 때문에 편하다. (표 오른쪽 아래에 꺽쇠가 생김)
  • 표 이름을 범위 대신 입력하면 편하다. ex) VLOOKUP사용시에도 범위 입력 대신 ‘표 이름’만 입력하면 된다. 기준이 되는 표는 새로운 데이터를 입력해도 자동으로 범위가 지정되어 늘어나기 때문!
• INDIRECT 함수 : 문자를 참조

 

<실무에서 진짜 쓰는 함수 예제 - VLOOKUP/SUMIF 함수>

■ VLOOKUP :

• 찾을 이름을 세로로 검색해서 불러오는 함수
• =VLOOKUP(찾을 값, 범위, 찾을 열, true/false)
• 찾을 값은 VLOOKUP을 입력할 셀 왼쪽에 있어야 한다.
• 찾을 값은 ‘고유값’이라고 가정되어 있다. 여러 값 중 첫 번째 값을 불러와 준다.

■ SUMIF :

• 어떤 값을 만족하는 값들을 불러온다.
• =SUMIF(찾을 고유값이 있는 범위, 찾을 고유값, 합계할 범위)
• 함수를 쓰기 전에 ‘고유값’을 설정해 주어야 한다.
• 셀 병합을 피해야 하는 이유! : 
  • 데이터 원본에 셀 병합을 사용하면 ‘망.한.다!!!’
  • 셀 병합을 해제하면 실제로는 첫 번째 셀에만 입력되어 있다. → 빈 셀의 값들은 더해지지 않는다!
• 셀 병합 문제 해결법(셀 병합 해제 후 나머지 빈 셀 채워넣기) :
  • 셀 병합된 범위 선택 → 셀 병합 해제 → Ctrl + G → 이동에서 ‘빈 값’ 선택
  • → 셀의 경우, 위의 셀을 참조하기 때문에 우선, 내용을 채울 빈칸을 지정해줘야 한다
  • → 선택된 셀 중 흰 색으로 된 부분에 위 내용을 =등호를 이용해서 입력해주고 → Ctrl + Enter → 모든 셀이 채워진다.
  • → 그리고 그 셀 값은 =등호로 되어 있기 때문에 값(수식X)으로 변경해 준다. (Ctrl + Alt + V : 값으로 붙혀 넣기 선택)

 

<엑셀이 어려운 이유, 바로 "데이터"가 원인입니다!>

■ 머리글은 한 줄만 쓴다 :

• 머릿글이 두 줄로 만들어지는 이유는, 똑같은 필드를 여러 번 표시하려다보니 월별로 구분을 하게 되어서다. Why? 방법을 몰라서.
• 머릿글을 한 줄로 만들면서 기존 여러 줄의 머릿글을 왼쪽으로 세로로 쌓아준다. (날짜는 머릿글이 아님)

 

<피벗 테이블>

■ 피벗 테이블 :

• 원본이 바뀌어도 실시간으로 반영되지 않는다.
• 데이터 탭 → 모두 새로고침
• 데이터가 새롭게 쌓이면, 새로고침으로 반영이 되지 않는다.
  • 피벗 테이블 분석 탭 → 데이터 원본 변경 → 범위 재지정
• 데이터 원본을 표로 설정 → 표 아무 셀이나 클릭해서 피벗 테이블 만들기 → 데이터를 새로 추가하고 피벗 테이블을 새로고침하면 데이터가 반영된다.

■ 슬라이서 :

• 피벗 테이블을 보기 쉽게 선택단추를 만들어준다.
• 함수 없이 데이터를 분류하여 볼 수 있다.
• 피벗 테이블 분석 → 슬라이서 삽입

■ 피벗 테이블 차트 : 

• 피벗 테이블도 차트 생성이 가능하다.

■ 보고서 만들기 : 표 만들기 → 피벗 테이블 → 피벗 테이블 → 보고서 레이아웃 - 테이블형식으로 표시

 

💡오늘의 정리 :

1. 아래 빈칸을 채워서 올바른 데이터 구조의 3가지 규칙을 완성하고, 각 규칙에 대해 간략히 설명합니다.

 ① 셀 병합은 절대 사용하지 않습니다.

• 데이터 원본에 셀 병합을 사용하면 ‘망.한.다!!!’ → 셀 병합을 해제하면 실제로는 첫 번째 셀에만 입력되어 있다. → 빈 셀의 값들은 더해지지 않는다!

 ② 머리글은 반드시 ’한 줄’로 관리합니다.

• 머리글이 두 줄로 만들어지는 이유는, 똑같은 필드를 여러 번 표시하려다 보니 월별로 구분을 하게 되어서다. Why? 방법을 몰라서.
• 머리글을 한 줄로 만들면서 기존 여러 줄의 머리글을 왼쪽으로 세로로 쌓아준다. (날짜는 머리글이 아님)

 ③ 집계 데이터는 원본에서 제외합니다.

• 합계가 포함되어 있으면, 피벗테이블 만들 때 데이터의 합이 두 배가 되어 버린다.

 

2. 실무에서 올바른 자료 관리를 위해 꼭 지켜야 하는 "세로방향 블록쌓기" 규칙에 대해, 아래 그림을 예제로 간략히 설명합니다.

세로방향 블록쌓기

• "세로방향 블록쌓기" 규칙으로 데이터를 관리해야 자동화 보고서를 만들기 편해진다.
  • 정렬이 쉽고, 보기 편하다.
  • 표 만들기를 통해 기존 데이터 아래에 새로 내용을 작성하면 자동으로 서식적용이 가능하다. (가로방향도 표 만들기 적용은 되긴 된다.)
  • 피벗테이블 만들고 원하는 형식으로 보고서 만들기가 용이하다.

 

3. 피벗테이블 만들기의 핵심 규칙인 '십자가 규칙'에 대해서 간략히 설명합니다.

피벗 테이블 만들기

• 위 그림처럼 가상으로 ‘십자가’를 두고 데이터를 배열하면 보기 편한 보고서를 만들 수 있다.
• 함수를 사용하지 않아도 쉽게 데이터 정렬이 가능하다.

 

4. 피벗테이블과 슬라이서를 활용해 나만의 멋진 자동화 보고서를 만들어보세요! 보고서를 완성한 후, 스크린샷을 첨부합니다.

하이브 그룹 (방탄소년단, 세븐틴, 르세라핌, 뉴진스)의 데이터를 이용하여 만든 피벗 테이블 & 슬라이서

• 적용한 것 : 표 만들기, 피벗 테이블, 슬라이서, 피벗 테이블 차트

---

2023.03.17 - [Excel] - [오빠두엑셀] 엑셀 기초 완성 3주 특별 챌린지 #1

[오빠두엑셀] 엑셀 기초 완성 3주 특별 챌린지 #1