엑셀 기초 4시간 완성 챌린지 2일차

• 데이터 유효성

데이터 탭 - 데이터 유효성 검사 - 목록(원본에 범위 지정) / 정수(정수범위)+오류메시지 입력 가능

• 목록 보기 단축 키 (목록상자 / 필터 / 셀에서 사용가능)

Alt+↓

• 기존 목록 상자에 추가 등록 시

기존 등록된 목록 상자에 고정된 범위로 새로운 제품명 또는 원가 등록 시 반영되지 않는다.

따라서, 머리글 포함하여 표로 생성하면 추가로 등록 시에도 데이터가 자동 확장됩니다.

• 표 선택 시 테이블 디자인 설정 가능

<TIP>표 이름 지정 시

=원가표 쓰고 [대괄호 열고 제품명 선택하고 TAP 키 -> ]대괄호 닫기 : 범위 자동 지정되서 편리함.

VLOOKUP으로 범위 지정 시에도 마우스로 개별 지정할 필요 없이 한번에 자동 지정됨.

• 표 만들기 단축키

Ctrl+T(테이블~)

• 함수 INDIRECT

자동확장 목록을 만드는 함수 : 문자를 바탕으로 범위를 참조하는 함수

=INDIRECT("원가표[제품명]")

• 함수 VLOOKUP

범위에서 특정 값의 자료를 찾아주는 함수

=VLOOKUP(찾을 값, 범위,열번호,0)

※ 찾을 값은 범위의 맨 왼쪽에 있어야 한다.

※ 맨위에 있는 값만 불러온다. -> 고유값(=문자&문자)으로 만들어주면 중복 보완 가능

• 다중조건의 VLOOKUP

고유값

열 이동 시 : 열 선택 후 사방으로 된 방향키 뜨면 Shift 누른 후 원하는 위치로 이동.

• 함수 SUMIF 

어떤 조건을 만족하는 값들을 합하는 것

=SUMIF(조건범위,조건,합계범위)

1. 기준(대분류) 만들기

+중복 단어가 있는 경우 데이터 탭 - 중복된 항목 제거

+UNIQUE 함수 이용 : 고유 값으로 필터링된 범위(세로) 반환

=UNIQUE(범위)

2. 셀병합 기능을 사용한 경우 범위가 모두 반영되지 않아 합계가 맞지 않음.

<TIP>

셀병합 해제 후 Ctrl+G 또는 F5 -> 옵션 -> 빈 셀 클릭

=셀 선택 -> Ctrl+enter : 여러 범위에 수식을 한번에 입력



※수식으로 붙여넣기 된 상태로 복사 -> 불여넣기(Ctrl+Alt+V) -> 값

3. 최종!!!!!! 함수 이용 후 합계 각각 맞는지 꼭!꼭! 확인 필요.

• 피벗테이블

십자가



※※※올바른 데이터 구조 3가지 규칙: 세로방향 블록 쌓기

1. 머리글은 반드시 '한 줄'로 관리하기

※하나의 그룹(날짜, 수량, 단가 등)은 머리글이 아니므로 세로로 쌓기

2. 원본데이터에는 셀 병합 절대 사용 금지!!

3. 집계 데이터는 원본에서 제외하기(총합까지 집계되어 뺀다)

• 피벗테이블 자동화

일반 범위로 피벗테이블 생성하면 데이터 추가 시 반영 안되므로 표로 만든 후 범위 지정.

-> 자료 추가 -> 피벗테이블 선택 -> 마우스 오른쪽 키 새로고침 클릭

• 피벗테이블 내 그룹 지정 가능

Shift+휠 돌려 그룹 확대 및 축소 가능



보통 보고서 레이아웃은 압축형식이 아닌 테이블 형식으로 하고 있음.

압축형식의 경우 한 셀에 누적되어 표시되어 불편함

테이블 형식                                                압축 형식

           

• 피벗테이블 슬라이서 삽입

십자가의 왼쪽 상단 필터는 거의 사용하지 않음

대신, 피벗테이블 슬라이서 삽입하여 보기 편하게 봄

피벗테이블 선택 - 피벗테이블 분석 탭 - 슬라이서 삽입

+ Ctrl 키 눌러서 중복 선택 가능함.

↓제품명 / 구분 / 년도 슬라이서

• 틀고정

B3열 선택) 기준으로 왼쪽과 위로 고정

단축키 : Alt+W+F+F

• 시작시트의 A1셀로 빠르게 초기화하는 단축키 콤보

Ctrl + PgUp → Ctrl + Home