엑셀 기초 4시간 완성, 원데이 챌린지 챕터 2

데이터 유효성 검사

• 유효한 데이터만 입력할 수 있도록 제한
• 목록상자 만들기 : 목록 상자 만들 셀 선택 → 데이터 탭 → 데이터 유효성 검사
  • 목록 제한 : 대상 - 목록, 원본 - 목록이 입력된 범위(중복x)
    • 원본 : 목록에 해당하는 셀 드래그 or 구조적 참조 시 =INDIRECT(”표이름[머리글]”)
  • 숫자로만 제한 : 대상은 정수, 최대값과 최소값 설정
  • 오류 메시지 → 제목(오류 이유), 오류메시지(요구사항)
• 목록상자 열기 : alt+↓
  • 기타 필터, 일반 셀(위 데이터 자동인식해서 입력) 등에도 사용 가능
• 목록 추가 : 목록을 표로 설정하기

표 설정

• 원하는 범위 드래그 → 삽입 탭 → 표
  • 머리글 있으면 머리글 포함 체크
  • Ctrl+t
• 기능 : 데이터 추가 시 표 자동확장
  • 표 생성 시 병합이 해제
• 테이블 디자인 탭 → 표 스타일 → 없음 선택
• 테이블 디자인 탭 → 제일 왼쪽 위 표 이름 설정
  • 표이름[머리글] 시 … 범위 바로 참조 + 자동확장
  • 구조적 참조 : 표 참조
    • 직접 범위 선택할 필요 없이 표이름[…] 사용 가능
    • INDIRECT(”표이름[머리글]”)

VLOOKUP 함수

• 세로로 입력된 넓은 범위에서 특정 값의 데이터를 찾아주는 함수
• VLOOKUP(찾을값(f4), 참조범위(f4), 열번호, 0)
  • 열번호 모르겠으면 범위 시작부분부터 오른쪽으로 드래그한 다음 왼쪽 위 보기
  • 열번호 바꾸고 싶으면 임의의 셀(e.g E7)에 열번호 적고, 함수 열번호를 해당 셀(E7)로 설정하기
  • 열번호 다 불러와야 할 시 Column 함수 사용 가능
    • Column($첫번째열:두번째열) 후 오른쪽으로 채워넣기
  • 찾을값은 고유해야 하며, 참조범위의 맨 왼쪽에 있어야 함
    • 중복 시 고유값 설정
      • 두 개의 셀 합쳐서 고유값 생성(e.g 박상민 1차, 2차) : 셀&셀(박상민1차, 박상민2차)

SUMIF 함수

• 조건을 만족하는 값들을 집계
• SUMIF(참조범위, 조건, 합계범위)
  • 참조범위는 표 전체가 아니라 해당 기준이 적혀있는 열
• 중복 제거 : 데이터 탭 → 중복된 항목 제거 → 현재 선택 영역으로 정렬
  • Unique 함수와 기능 같음
• 병합 시에는 첫 번째 값만 입력되니 주의
  • 병합 해제 → ctrl+G or f5 → 옵션 → 빈 셀 선택 → 선택된 셀(엑셀 왼쪽 위 참조)=선택된 셀 위/옆의 셀 → ctrl+enter
    • 수식을 값으로 변환 : 원하는 범위 드래그 → ctrl+alt+v → 붙여넣기 : 값

피벗 테이블

• 범위 선택 → 삽입 탭 → 피벗 테이블
  • 열 : 날짜, 행 : 기준(제품, 수량 등), 값 : 데이터(값?)
    • 열/행이 없으면 행/렬과 해당 값만 입력
  • 행에 넣는 대신 필터에 특정 기준 넣어서 볼 수도 있음 : 잘 안 씀
• 슬라이서 : 필터링 하고자 하는 필드 추가 - 보통 필터 대신 이거 씀
  • 피벗 테이블 분석 → 슬라이서 삽입
  • 여러 피벗 테이블 한꺼번에 슬라이서 적용
    • 한 피벗 테이블 슬라이서 생성 → 슬라이서 우클릭 → 보고서 연결 → 적용할 다른 피벗 테이블 선
  • 색 변경 : 슬라이서 탭 → 슬라이서 스타일 열기 → 스타일시트 파일 기반 색 변경
  • 열 개수 바꾸기 : 슬라이서 탭 → 열 개수 변경
  • 불필요한 범위 숨기기 : 슬라이서 우클릭 → 슬라이서 설정 → 데이터가 없는 항목 숨기기 체크
    • 안 되면 피벗 차트 분석 탭 → 필드 단추 → 모두 숨기
• 피벗 테이블의 행을 테이블로 설정 : 피벗 테이블 디자인 탭 → 보고서 레이아웃 : 테이블 형식
• 피벗 테이블 생성 시 원본 데이터는 표로 만들어야 데이터 추가 가능
  • 피벗 테이블 분석 → 데이터 원본 변경 으로도 가능
  • 원본 데이터 추가 후 피벗 테이블 반영 : 데이터 탭 → 모두 새로고침
• 그룹핑 : 머리글 행/렬 우클릭 → 그룹 원하는 단위 선택
  • shift + 마우스 휠 : 그룹 여닫기
• 정렬 : 레이블 셀 오른쪽 아래 필터 클릭 → 기타 정렬 옵션 → 내림차순 숫자 정렬
  • 빠른 실행 도구 모음 사용해도 됨
• 피벗 테이블 기반으로 차트 생성 시 있는 필드 단추 숨기기 : 버튼 우클릭 → 차트에서 모든 필드 단추 숨기기

데이터는 머릿글을 기준으로 세로로 블럭쌓아야 함

• 가로 방향에서 자동채우기, 빠른채우기, Vlookup 사용 불가
• 머릿글은 1일, 2일, 3일, … 식이 아님
  • 이건 피벗테이블 결과물이지 데이터가 아님

왼쪽 하단, 오른쪽 하상단 그래프