[인프런] BDA학회_9기 - Excel 조건식과 피벗 테이블

 

 

---

 

 

[인프런 강의]

 

빅데이터의 통찰력을 키워주는 엑셀 Quick Start

(인프런 X BDA학회 스터디)

 

✔️ Excel 조건식 활용

✔️ Excel 피벗테이블 활용

 

 

---

 

 

✔️ Excel 조건식 활용

 

 

# 조건식의 응용

이 기능에는 내가 임의로 조건을 어떻게 적용할 수 있는지 

 

 

# 조건식의 응용유형

 

1. 기본 기능 + 조건식

(참조를 유의하여 써야 함 : 행, 열 고정에 신경쓸 것)

• 자동필터(숫자 필터)

• 고급필터

• 조건부 서식에서의 조건식

• 유효성 검사에서의 조건식

 

2. 함수식 + 조건식

• IF계열 함수

• AND, OR

• OOOIF 함수

• 조회 함수

• DXXX계열 함수

• 배열 수식

 

 

---

 

 

# 조건의 형식

 

1. 셀 조건 : 시트 속에 조건 표현

고급 필터와 DXXXX계열 함수들의 조건 표현

조건을 실시간으로 바꾸어 결과 확인 유리

조건셀 노출(단점)

 

홈 탭 > 데이터 > 고급

 

다른 장소에 복사 > 목록범위(열이름까지 전부 지정) > 조건 범위(조건 열 이름까지 전부 지정) > 복사 위치(원하는 위치)

 

조건 범위에서 조건셀은 배치에 따라 AND 조건과 OR조건으로 나뉨

 

AND 조건 : 가로 배치

(~이면서 ~인)

 

 

OR조건 : 새로 배치

(~이거나 ~도)

+

 

DSUM(데이터 범위, "기준 열이름", 조건 범위)

=DSUM(A3:G38,"기본급",Criteria)

 

 

 

2. 조건식

엑셀 기능 중 조건이 필요하면 어디든 적용 가능

함수, 기능과 조합 가능

조건이 바뀌면 조건식 부분을 직접 수정해야 함(단점)

 

 

집계함수 활용하여 조건을 지정하는 경우 유의할 점

수식을 적용하는 경우, = 집계함수(데이터범위)는 적용되지 않음

 

="조건"&집계함수(데이터범위) 적용

=">="&AVERAGE(E4:E38)

 

 

IF계열

예) 해당 셀이 100만 이상인 경우 '100만원 이상' 입력, 아니면 '100만원 이하' 입력

=IF(E17 >= 1000000, "100만원 이상", "100만원 이하")

(조건 수식을 넣는 과정에 AND, OR 사용하여 적용 가능)

 

+

 

IFERROR(오류평가 수식, 오류로 평가되면 채택할 값이나 수식)

결과값 : 오류평가 수식의 결과(오류X) or 오류평가로 채택된 값

예) 기본급 / 교통비가 된다면 값 나오고 아니면 --- 입력

=IFERROR(E4/G4,"---")

 

 

조건부 서식

예) E는 고정이고 위 아래 데이터 움직이면서 200만 이상인 경우 주황식으로 지정

(조건 수식을 넣는 곳에 AND, OR 사용하여 적용 가능)

 

 

AND, OR 함수 구분

AND(조건식1, 조건식2, ...) : 해당 조건식들이 모두 참인 경우

OR(조건식1, 조건식2, ...) : 해당 조건식들이 하나만 참이면 되는 경우

NOT(조건식1, 조건식2,...) : 해당 조건식들이 아닌 경우

 

배열 수식

유효성 검사

 

 

3. 조건인자

특정 함수의 여러 인자들을 조합하여 조건이 형성됨(함수 종속적인 방식)

함수마다 조건과 적용 범위 지정법이 상이함(단점)

 

OOIF 계열

예) 기본급이 200만원 이상인 사람은 몇 명이 존재하는가?

COUNTIF(데이터 범위, "조건")

=COUNTIF(E4:E38,">=2000000")

 

Sumproduct함수

조회 함수

 

 

---

 

 

✔️ Excel 피벗테이블 활용

엑셀의 생산성 -> 임무의 우선순위에 적합한 기능을 사용하는가에 따라 달라짐

 

 

# 기초통계 결과 확인

윈도우 : 파일 > 옵션 >  추가 기능 > 분석 도구

MAC : 홈 탭 > 데이터 > 우측 끝 분석 도구 > ☑️ 분석 도구 

 

 

# 데이터 분석(기초 통계)

데이터 > 데이터 분석 > 기술 통계

데이터 범위 지정(inpout) > 출력 위치 설정(output) > 요약 통계 선택하여 확인

(저의 경우는 영어로 나와서 영어로 했습니다)

영어로 나오긴 하지만 동일한 값이 나오는 것을 확인

 

 

 

# 부분합으로 그룹 집계 확인

1. 그룹으로 묶을 열(field)를 정렬

홈 탭 > 데이터 > 오름차순 정렬

 

2. 부분합 기능 적용

홈 탭 > 데이터 범위 지정 > 데이터 > 부분합 적용

(좌측 단계 확장/축소 부분을 보기 싫다면 ctrl + 8로 없앨 수 있음)

 

 

 

# 피벗테이블로 그룹 집계표 생성

1. 피벗테이블 삽입

홉 탭 > 삽입 > 피벗 테이블 

 

2. 필드 배치 

다양하게 필드를 배치하여 원하는  집계 형식(레이아웃) 생성 가능

기본적으로 자동 필터가 적용

 

 

# 피벗 테이블 수정

 

1. 집계함수 수정

- 데이터 부분 선택(혹은 Σ필드 선택)

- 피벗테이블도구 > 옵션 > 필드설정 메뉴 선택

- 값요약기준에서 계산유형 수정

 

 

2. 표시형식 수정

- 데이터 부분 선택(혹은 Σ필드 선택)

- 피벗테이블도구 > 옵션 > 필드설정 메뉴 선택

- 표시형식 버튼 클릭

- 표시형식을 선택하거나 편집

 

 

3. 레이아웃 수정

- 피벗테이블도구 > 디자인 탭

- 부분합 위치, 총합계 유뮤, 다중 필드시 표시형식 등 지정

 

+

 

피벗 차트

피벗 데이터 분석 > 피벗 차트 선택 

 

 

---

 

 

이상으로 [인프런xBDA] 엑셀 강의 스터디의 모든 과정이 완료되었습니다.

해당 과정을 다시 확인하고 싶으시거나 다른 강의를 듣고 싶으신 분은 

인프런 홈페이지에 들어가셔서 강의를 찾아 보시면 좋을 것 같습니다.

https://www.inflearn.com/

인프런 - 라이프타임 커리어 플랫폼