Excell Skill UP!(2)

들어가며

엑셀 대표 유튜버이신 오빠두엑셀님께서 진행하시는 엑셀 스터디의 두 번째 공부 내용을 정리한 글입니다.

(공개 가능합니다.)

사소하게 시간을 많이 잡아먹던 작업을 한방에 해결하는 유용한 꿀팁이 많으니,  글을 읽으시는 분들께 도움이 되었으면 좋겠습니다.

엑셀강의 대표채널! 오빠두엑셀 - 오빠두엑셀

---

1. 자동 확장 목록상자 만들기

문제 상황

원가표에 새 제품명 추가 시, 기존 목록 상자에는 추가되지 않는 문제 상황 발생

 

해결 방법

(1) 표 생성 

- 기존 원가표(셀)를 표로 만들기

• 단축키 : Ctrl + T
• 표 이름 필수 생성

(2) 유효성 검사 / INDIRECT(" ")

유효성검사 / indirect("원가표[제품명]")을 입력하여,

표에 추가된 제품명을 목록 상자에 자동으로 연동합니다.

---

2. 실전 VLOOKUP

문제 상황

Vlookup 함수 사용 시, 위와 같이 찾을 값이 두 개일 경우가 자주 발생합니다.

해결 방안

고윳값 열 생성 : 찾을 값을 하나로 만드는 열을 생성하여 해결합니다.

---

3. 실전 SUMIF

문제 상황

우측 문제 상황: [지역],[[대분류]] 셀 병합이 생성된 상황에서,

SUMIF 계산이 잘못되는 문제가 발생합니다.

셀 병합을 해제했을 시,

대분류 2개씩(서울-대분류, 경기-대분류)만 합한 것을 알 수 있습니다.

해결방안

빈셀 채우기를 통해 해결합니다.

 

---

4. 꼭 지켜야 할  데이터 규칙

 (1) 세로방향 블록 쌓기 

좌측과 같이 가로방향 데이터 누적 시, 데이터 집계, 피봇테이블 생성에 어려움이 있습니다.

좌측 테이블의 [날짜]는 머리글이 아닌, 날짜 데이터 값이므로 우측 테이블과 같이 날짜 열을 생성해야 합니다. 

(2) 머리글(열 이름)은 한 줄로 관리하기

머리글을 두줄로 생성할 경우, 집계 및 피봇테이블 생성에 어려움이 있습니다.

우측 테이블과 같이 '월' 열을 생성하여 세로방향 블록 쌓기를 합니다.

(3) 셀 병합 사용 금지

셀 병합 사용 시 셀 선택 및 편집, 집계, 피봇 테이블 생성에 어려움이 있습니다.

위와 같이 집계 오류를 유발합니다.

5. 피벗 테이블 필수 규칙

(1) 십자가 규칙

피봇테이블은 위와 같이 십자 모양으로 생성됩니다.

(2) 테이블 형식으로 표시

행 영역에 값이 추가될 경우, 테이블 형식으로 표시하는 것이 깔끔합니다.

(3) 필터 대신 슬라이서 추가

피봇테이블 - 필터 대신 슬라이서를 활용하여 피봇테이블을 필터링합니다.