1주차

엑셀 기초
엑셀 함수
엑셀 기능
엑셀 시각화
엑셀을 활용한 데이터 분석
결측치와 이상치

1. 엑셀 기초

1-1. 엑셀 데이터의 형태 & 붙여넣기

• 엑셀 데이터의 형태
  값(Value): 텍스트 & 숫자
  수식(Formula): 계산식 & 함수
  서식(Format): 글꼴 서식 & 셀 서식

• 엑셀 데이터의 붙여넣기
  수식 붙여넣기: 서식을 제외한 수식 그대로
  값 붙여넣기: 서식을 제외한 수식을 값으로 변경
  서식 붙여넣기: 값, 수식을 제외한 서식

1-2. 엑셀의 참조

상대 참조: 함수 복사/붙여넣기 => 참조된 셀이 함께 이동
절대 참조(F4키 1번): 함수 복사/붙여넣기 => 참조된 셀 이동 X
혼합 참조(F4키 2,3번): 한 셀의 열 or 행에만 절대 참조, 다른 하나는 상대 참조

1-3. 빠른실행도구모음 활용

빠른실행도구모음: 엑셀 화면의 왼쪽 상단에 자주 사용하는 기능 등록하여 빠르고 편하고 사용 가능

빠른실행도구모음은 "Alt키 + 숫자키"로 빠르게 사용 가능

2. 엑셀 함수

함수: 복잡한 수식이나 계산을 간단한 명령어로 사용 가능

2-1. COUNT 함수

• COUNT 함수
  특정 범위에서 숫자 데이터인 셀의 개수 return

  =COUNT(C5:C12)
  * 입력값: 숫자 데이터의 개수를 파악할 셀의 범위

• COUNTA 함수
  특정 범위에서 데이터가 들어가 있는 셀(비어 있지 않은 셀)의 개수 return

  =COUNTA(C5:C12)

• COUNTBLANK 함수
  특정 범위에서 비어 있는 셀의 개수 return

  =COUNTBLANK(C5:C12)

• COUNTIF 함수
  특정 범위에서 하나의 조건을 만족하는 셀의 개수 return

  =COUNTIF(C5:C12, "A")
  =COUNTIF(C5:C12, ">=5")
  * 입력값: 개수를 파악해야 할 데이터가 있는 범위와 조건

• COUNTIFS 함수
  특정 범위에서 2개 이상의 조건을 동시에 만족하는 셀의 개수 return

  =COUNTIFS(J55:J62, ">5", J55:J62, "<8")
  * 입력값: 데이터의 범위와 조건 pair

  COUNTIFS 함수는 조건이 1개일 때도 사용가능하므로 COUNTIF보다 COUNTIFS를 쓰도록 하자

2-2. IF 함수

• IF 함수
  부등호/등호를 활용하여 조건을 가정 -> 조건에 만족하는 값 & 만족하지 않는 값을 다르게 표시
  목적: 데이터를 분류
  IF 함수를 N번 중첩 사용 => 데이터가 N+1가지로 구분

  =IF(A1>=90, "합격", "불합격")
  * 입력값: 조건(가정), 조건을 만족할 경우 표시할 값, 조건을 만족하지 않을 경우 표시할 값

• IF 함수의 중첩

  =IF(첫번째 IF 조건, 첫번째 IF조건 만족할 경우, IF(두번째 IF 조건, 두번째 IF 조건 만족할 경우, 두번째 IF 조건 만족하지 않을 경우))
  -> 데이터가 3개로 분류

2-3. VLOOKUP 함수✨

• VLOOKUP 함수
  공통 기준 열을 기준으로 n번째 데이터를 return

• VLOOKUP 함수의 조건

  1. 작성 중인 표와 원래 데이터 간의 공통 기준 열 존재
  2. 불러오고자 하는 데이터가 원래 데이터의 공통 기준열 오른쪽에 존재
  3. 공통 기준열에 중복 데이터 존재X

• VLOOKUP 함수의 원리
  원래 데이터의 공통 기준열을 1열로 잡고 시작하여 n번째 열의 데이터를 return

=VLOOKUP(D5, 직원정보LIST!$D:$J, 3, 0)
* 입력값: 찾을 기준 데이터, 원래 데이터의 범위(공통 기준열부터), 불러올 데이터의 열 번호(공통 기준열이 1열), 정확히 일치할 때:0 or 근사치일 때: 1

위의 코드는 불러올 데이터의 열 번호를 숫자로 입력해주었는데 엑셀에서는 숫자를 직접 입력해주기보단 참조하는 것이 좋다. => 참조 or MATCH 함수 사용

• MATCH 함수
  찾고 싶은 값이 하나의 행/열에서 몇 번쨔에 있는 지 숫자로 알려준다.

  =MATCH(찾고 싶은 값, 찾고 싶은 값이 포함된 단일 열/행 범위, [정확히 일치 or 근사값])
  * []: 생략 가능한 인수

• VLOOKUP 함수 사용시 중복 값
  기준 열에 중복 값이 있으면 가장 위의 데이터 return
  => 중복 값이 없는 새로운 기준 열 생성 e.g. 순번&이름
  

2-4. SUMIF 함수

• SUMIF 함수
  특정 조건에 맞는 데이터들의 합계 계산
  SUMPRODUCT 함수와 서로 대체 가능

  =SUMIF($C$5:$C$35, "식비", $D$5:$D$35)
  * 입력값: 더할 조건들의 범위, 조건, 더할 값들의 범위

• SUMIFS 함수
  2개 이상의 조건을 동시에 만족하는 데이터들의 합계 계산

  =SUMIFS(D5:D35, B5:B36, "2030년", C5:C36, "매출액")
  * 입력값: 더할 값들의 범위, 더할 조건 범위와 조건의 pair
  * SUMIF와의 차이점: 더할 값들의 범위를 먼저 지정

  SUMIFS 함수는 조건이 1개일 때도 사용가능하므로 SUMIF보다는 SUMIFS 함수를 사용을 생활화하자
  

2-5. SUMPRODUCT 함수

• SUMPRODUCT 함수
  인수(배열)끼리 곱하고 곱한 것들의 합 계산
  SUMIFS 함수와 서로 대체 가능

  =SUMPRODUCT(A2:A6, B2:B6)
  * 입력값: 배열 인수1, 배열 인수2
  A2*B2 + A3*B3 + ...

• 논리곱
  TRUE(1) X TRUE(1) = TRUE(1)
  TRUE(1) X FALSE(0) = FALSE(0)
  FALSE(0) X TRUE(1) = FALSE(0)
  FALSE(0) X FALSE(0) = FALSE(0)

• SUMPRODUCT 함수와 논리곱을 활용

  =SUMPRODUCT((C9:C10="헤드튜브")*(곱할 값 범위1)*(곱할 값 범위2))

2-6. 텍스트 처리 함수

• FIND 함수
  특정 단어나 문장이 시작하는 위치를 숫자로 return (띄어쓰기 포함, 대소문자 구분)

  =FIND("아빠", A1)
  =FIND("아빠", A1, 101)
  * 입력값: 찾을 텍스트, 긴 텍스트, 문자열을 찾기 시작한 위치
  * 문자열을 찾기 시작한 위치 지정해도 처음부터 문자를 센 위치 return

• SEARCH 함수
  특정 단어나 문장이 시작하는 위치를 숫자로 return (띄어쓰기 포함, 대소문자 구분X)

2-7. 날짜 처리 함수

하이픈을 사용해서 날짜를 입력하면 서식이 자동으로 [일반] -> [날짜] 변경
날짜를 입력했는데 4~5자리 숫자로 나오는 경우는 서식이 [일반]인 경우
4~5자리 숫자 = 1900-01-01로부터 "4~5자리 숫자"번째 되는 날

• 날짜 데이터 계산
  1. 종료일 제외

  =종료일 - 시작일
  =DAYS(종료일, 시작일)
  =DATEDIF(시작일, 종료일, "D")
  * D, M, Y, TM(올 해의 월 수), MD(이번 달의 일 수)

  2. 종료일 포함
     기간을 구하는 수식 + 1
• 연도, 월, 일 추출하는 함수

=YEAR(셀)
=MONTH(셀)
=DAY(셀)

3. 엑셀 기능

3-1. 피벗테이블✨

• 피벗테이블
  커다란 표의 데이터를 요약하는 통계표

• 생성
  피벗테이블을 만들 Raw data 위에 커서가 있어야 함(드래그X) -> [삽입]탭 -> 피벗테이블
  [피벗 테이블 필드]의 필드를 필터, 열, 행, 값 중에 드래그해서 완성

• 슬라이서(like 필터)
  피벗테이블 위에 커서 -> [피벗 테이블 분석]탭 -> [슬라이서 삽입]

  ** 슬라이서 삭제할 때 유의: 슬라이서에 필터 해제 후 삭제

  ** Tip! 피벗테이블의 행에 변수들을 하나씩 넣어보면서 데이터 확인 가능

3-2. 텍스트 나누기

1개의 셀에 있는 데이터를 2개 이상의 셀로 나누기
나눌 데이터 선택&복사 -> 나눌 셀 중 첫 번째 셀에 붙여넣기 -> 붙여넣은 셀 선택 -> [데이터]탭 -> [텍스트 나누기] 선택

3-3. 중복된 항목 제거

데이터 선택 -> [데이터]탭 -> [중복된 항목 제거] 선택 -> 중복값이 있는 열 선택

3-4. 필터 & 고급 필터

• 필터
  단축키: [Alt] + [D] + [F] + [F] -> 가장 쓰기 편함
  [Alt] + [A] + [T][Ctrl] + [Shift] + [L]
  
• 고급 필터
  조건을 범위로 지정할 수 있다
  조건을 한 행에 쓰면 AND조건 / 조건을 여러 행에 쓰면 OR 조건

3-5. 데이터 유효성 검사

• 데이터 유효성 검사의 목적

1. 내가 잡은 범위에서 유효하지 않은 데이터들을 골라내서 표시
2. 목록 등을 만들어서 내가 인정하는 데이터들만 들어가게 하는 것❗

• 사용법
  셀 or 범위 선택 -> [데이터]탭 -> [데이터 유효성 검사] -> 제한 대상, 제한 방법 지정
  목적1: 셀 or 범위 선택 -> [데이터]탭 -> [데이터 유효성 검사] -> [잘못된 데이터]
  목적2: 셀 or 범위 선택 -> [데이터]탭 -> [데이터 유효성 검사] -> [목록]

4. 엑셀 시각화

4-1. 차트 (막대형 / 꺾은선형 / 원형)

차트를 그릴 데이터 범위 선택 -> [삽입]탭 -> [차트] -> 원하는 차트 선택

차트 이름	정의
막대형 차트	막대 그래프의 높낮이를 통해서 수치의 크고 작음 표현
꺾은선형 차트	선형 그래프의 높낮이를 통해서 수치의 크고 작음 표현
원형 차트	원형 그래프의 넓이를 통해서 수치의 많고 적음 표현

4-2. 조건부 서식

• 조건부 서식
  특정 조건 규칙을 만족하는 데이터에만 자동으로 서식 적용

1. 셀 값을 기준으로 모든 셀의 서식 지정
   선택한 범위의 모든 셀에 적용
   [2가지 색조] / [데이터 막대] / [아이콘 집합]

2. 다음을 포함하는 셀만 서식 지정
   선택한 범위에서 조건을 만족하는 데이터에만 적용 (조건부 서식계의 IF함수)

3. 상위 또는 하위 값만 서식 지정
   상위 또는 하위에 해당하는 데이터에만 적용

4. 평균보다 크거나 작은 값만 서식 지정
   선택한 데이터의 평균보다 크거나 작은 데이터에만 적용

5. 고유 또는 중복 값만 서식 지정

6. 수식을 사용하여 서식을 지정한 셀 결정
   수식의 결과가 참(TRUE)인 데이터에만 적용
   (수식에서 행은 고정X)

5. 엑셀을 활용한 데이터 분석

5-1. 손익계산서 작성 & 손익 데이터 분석

• 손익계산서
  회계기간에 속하는 모든 수익과 대응하는 모든 비용을 적정하게 표시하여 손익을 나타내는 회계문서

6. 결측치와 이상치

6-1. 결측치

• 결측치
  데이터에 값이 없는 것(NA, NaN, Null, 빈 칸)
  ** 엑셀에서의 결측치는 빈 칸이다

• 결측치 처리 방법
  결측치의 유형, 비율에 따라 결정

결측치 처리 방법	정의
제거	결측치가 있는 행/열 삭제
치환	결측치 대체
모델 기반 처리	결측치 예측하는 모델 구성하여 결측치 채우기

6-2. 이상치

• 이상치(Outlier)
  정상군의 상한과 하한의 범위를 벗어나거나 패턴에서 벗어난 수치
  평균 +- 3*표준편차의 미만,초과인 값

• 사분위수
  데이터를 4등분한 값

• IQR(Inter Quartile Range)
  3사분위수 - 1사분위수
  IQR = 3Q - 1Q
  이상치: 1Q - 1.5XIQR 보다 작은 값과 3Q + 1.5XIQR 보다 큰 값

+ 빅데이터의 이해 & 데이터 리터러시 함양하기

데이터 분석이 실무에서는 어떠한 방식으로 진행되는 지에 대해 알 수 있는 시간이었다. 이 시간을 통해, 나에게 부족한 부분은 무엇이고 앞으로 어떠한 방식으로 무엇을 채워나가야할지에 대한 방향성을 잡을 수 있어 많은 도움이 되었다.