[패캠 3일차] 엑셀 함수와 기능, 시각화 - 이동훈 강사님

엑셀 함수

COUNT(범위)

특정 범위에서 숫자 데이터가 있는 셀의 갯수 반환

 

COUNTA(범위)

A는 All의 의미

특정 범위에서 데이터가 있는 셀(= 비어 있지 않은 셀)의 갯수 반환

 

COUNTBLANK(범위)

특정 범위에서 비어 있는 셀의 갯수 반환

 

COUNTIF(범위, 조건)

특정 범위에서 조건을 만족하는 셀의 갯수 반환

 

예:

COUNTIF(C5:C12, “A”) ⇒ 데이터가 “A”인 셀의 갯수 반환

COUNTIF(C5:C12, “≥5”) ⇒ 숫자 데이터가 5보다 크거나 같은 셀의 갯수 반환

** 일반적으로 함수 안에 부등식을 사용할 때는 그냥 사용

COUNTIF 함수의 조건일 때는 문자열로 작성

 

COUNTIFS(범위1, 조건1, 범위2, 조건2, …)

조건이 1개일 때도 가능해서 그냥 언제나 COUNTIFS 함수를 써 주면 된다

예시:

COUNTIFS(J55:J62, ‘>5”, J55:J62, “<8”) ⇒ 데이터가 5보다 크고 8보다 작은 셀의 갯수 반환

팁: 범위에는 거의 항상 절대 참조를 건다 (연산 부작용 일어나지 않도록)

 

IF 함수

백화점의 고객 매출이 $2000 이상이면 “Gold”, 아니면 “Silver”로 분류

=IF(C5≥2000, “Gold”, “Silver”)

 

IF 함수 중첩

IF를 중첩해서 사용

 

예:

백화점의 고객 매출이 $5000 이상이면 “Platinum”,

$2000 이상이면 “Gold”,

나머지는 “Silver”로 분류

=IF(C5≥5000, “Platinum”, IF(C5≥2000, “Gold”, “Silver”))

 

VLOOKUP 함수

공통 열 기준으로 데이터를 찾는 함수

원래 데이터의 공통 기준열을 ‘1열’이라고 가정하고

그 열을 기준으로 오른쪽으로 N번째에 있는 열의 데이터를 불러옴

 

=VLOOKUP(기준 공통 열, 원래 데이터의 범위(공통 열부터), 공통 열 기준으로 불러올 열 순번, 0(일치)/1(근사치여도 OK))

 

근사치 1을 쓰는 경우도 있긴 한데 99%의 경우는 정확히 일치하는 데이터를 불러오는 경우가 대부분

 

VLOOKUP의 4번째 값은 대괄호(선택) 값인데 생략을 해버리면 디폴트가 1이므로 꼭 0 또는 false 붙여주기

 

예:

=VLOOKUP($C5, 직원정보LIST!$D:$J, 3, 0)

 

설명

$C5: F4를 여러 번 눌러서 열 고정 (행은 나중에 드래그로 수식을 다 적용할 거라 고정하지 않음)

직원정보LIST!: 화면 하단에서 필요한 시트를 마우스로 찍으면 등록됨, ‘!’는 구분자

$D:$J: 열 전체를 드래그

범위를 잡을 때 표 안에 드래그하면 새로 추가될 신입사원(?) 데이터를 놓칠 수 있음

3: 기준 열(사번)을 기준으로 3번째에 있는 열(팀)의 데이터를 불러옴

0: 근사치가 아닌 정확한 값일 때만 불러옴

 

팁: 범위는 F4 꼭 눌러서 고정해주기

 

VLOOKUP 심화

위에선 숫자를 썼는데 대부분의 경우 참조 사용

(변화가 있을 때마다 매번 수정하는 것 방지)

 

⇒ 열 번호를 참조(D$3)하면 드래그 시 열은 계속 바뀌고 행은 3번째 행으로 고정됨

 

MATCH 함수

찾고 싶은 값이 한 행이나 열에서 몇 번째에 있는지 숫자로 알려줌

 

=MATCH(찾고 싶은 값, 찾고 싶은 값이 포함된 열 또는 행 범위, [근사치인 여부])

 

3을 MATCH로 대체해보자!

=VLOOKUP($G12, ‘직원정보LIST(완성)’!$D:$J, MATCH($H$11, ‘직원정보LIST(완성)’!$D$4:$J$4, 0)

 

원래 데이터에 중복 값이 있을 경우

‘유재석’이란 사람이 2명이면

ID+이름 등으로 고유한 값이 들어간 기준 열을 새로 만든 다음 VLOOKUP 함수 사용

 

주의: VLOOKUP 할 때 표 위에 써 둔 숫자는 지우면

VLOOKUP이 해당 숫자를 참조하고 있어서 셀에서 오류남 (#VALUE)

⇒ 해결: 지우지 말고 숫자들을 흰 글씨로 바꾸기 (나중에 본인이 봐도 티 안 남)

 

SUMIF

조건에 맞는 데이터들의 합계 반환

SUMIF(더할 조건들이 있는 범위(열), 조건(문자열), 조건에 맞는 경우 더할 값들의 범위(열))

 

예시:

=SUMIF($C$5:$C$35, “식비”, $D$5:$D$35)

 

SUMIFS

여러 조건을 동시에 만족하는 데이터들의 합계 반환

 

** SUMIF와 SUMIFS 함수의 차이

SUMIF는 더할 값들의 범위를 맨 마지막에 작성

SUMIFS는 더할 값들의 범위를 맨 처음에 작성

⇒ 이유: 조건들이 여러 개여서 계속 길어질 수 있기 때문에 맨 뒤에 쓰지 않고 앞으로 당겨옴

 

SUMIFS(더할 값들의 범위, 더할 조건의 범위 1, 조건 1, 더할 조건의 범위 2, 조건 2, …)

 

예시:

SUMIFS(D5:D35, B5:B36, “2030년”, C5:C36, “매출액”)

 

팁: SUMIFS는 조건이 1개일 때도 가능 ⇒ 그냥 SUMIFS를 쓰는 게 편하다

 

원하는 텍스트의 위치 찾기

FIND 함수

특정 단어나 문장이 시작하는 위치를 숫자로 반환

띄어쓰기 포함

대소문자를 구분

 

FIND(찾을 문자열, 찾을 범위, [문자열을 찾기 시작할 위치(숫자)])

 

예시:

=FIND(”아빠”, A1) ⇒ 첫 번째 아빠를 찾음

=FIND(”아빠”, A1, 101) ⇒ 101번째 문자부터 아빠를 찾음 (⇒ 두 번째 아빠 발견)

 

SEARCH 함수

특정 단어나 문장이 시작하는 위치를 숫자로 반환

띄어쓰기 포함

대소문자를 구분하지 않음

 

 

** SEARCH와 FIND의 차이

대문자든 소문자든 ‘에이’만 찾으면 돼 ⇒ SEARCH 함수

무조건 대문자 A 또는 소문자 a를 찾고 싶어 ⇒ FIND 함수

 

 

텍스트 처리 함수 2

LEFT

텍스트의 가장 왼쪽부터 원하는 문자열까지 추출

LEFT(전체 텍스트, 불러올 문자열 수)

 

RIGHT

텍스트의 가장 오른쪽부터 원하는 문자열까지 추출

RIGHT(전체 텍스트, 불러올 문자열 수)

 

MID

텍스트의 중간 시작 위치부터 원하는 문자열까지 추출

MID(전체 텍스트, 불러올 문자열의 시작 위치, 불러올 문자열 수)

 

FIND/SEARCH 를 LEFT/RIGHT/MID 와 함께 활용

LEFT/RIGHT/MID 함수의 불러올 문자열 수나

불러올 문자열의 시작 위치에 FIND/SEARCH 함수 활용!

 

LEN 함수

문자열의 길이 반환

LEN(문자열)

 

날짜 처리 함수 1

엑셀에서 날짜 데이터는 연도-월-일 순서로 하이픈으로 연결하는 게 정석

가끔 날짜 데이터를 받았는데 4~5자리 숫자로 나오는 경우가 있음

 

하이픈으로 작성된 날짜를 입력하면 서식이 일반에서 날짜로 자동으로 변경

⇒ 이 서식을 다시 일반으로 변경했을 때 날짜 데이터가 4~5 자리의 숫자가 됨

 

엑셀은 1900-01-01을 “1”일(자신의 탄생일, 이 세상의 시작)이라고 인식

이 날로부터 몇 일이 지났는 가를 표현하는 것

 

2020-10-01 날짜 데이터 서식을 ‘일반’으로 바꾸면

1900년 1월 1일로부터 44105번째 되는 날이라고 인식

 

만약 이렇게 4~5자리 숫자로 날짜가 나와 있다면

서식을 ‘일반’에서 ‘날짜’로 바꿔주면 해결

이렇게 엑셀이 날짜를 숫자로 바꿔버리는 이유는 날짜 데이터끼리 계산을 하기 위함

 

 

날짜 처리 함수 2

날짜 데이터 계산

어떤 날짜 간의 기간을 구하고 싶다면

 

종료일 - 시작일 = 기간

 

예:

2020-10-15(K2)

2020-10-05(K3)

=K2-K3 (결과 10)

 

기간에 종료일을 포함시키려면 종료일 - 시작일 + 1

 

기간 구하는 함수

주문 날짜와 배송 날짜를 활용하여 배송 기간 구하기

 

DAYS 함수

기간을 숫자로 반환

=DAYS(종료일, 시작일)

 

DATEDIF 함수

발음: ‘데이티드이프’

기간을 숫자로 반환

 

=DATEDIF(시작일, 종료일, 옵션)

 

옵션 종류

“D”: 일 수

“M”: 월 수

“Y”: 연 수

“YM”: 올해의 월 수

“MD”: 이번 달의 일 수

 

DATEDIF는 정식으로 등록된 함수는 아니어서 자동 완성에는 뜨지 않는데

첫 번째 괄호(’(’)까지 입력하면 함수 이름은 뜸 (매개변수는 알려주지 x)

 

YEAR/MONTH/DAY 함수

예:

주문날짜(D3)

2033-11-08

=YEAR(D3) → 2033 (연도 반환)

=MONTH(D3) → 11 (월 반환)

=DAY (D3) → 8 (일 반환) → 앞 자리 ‘0’은 빼고 반환

 

날짜가 이상하게 나온다? 대부분 서식 문제

(상단의 ‘표시 형식’에서 일반 ↔ 날짜 왔다갔다 하며 쓰기)

 

피벗테이블로 데이터 추출하기

피벗테이블이란?

날 것의 raw 데이터를 요약하거나 통계를 내는 표

 

이 표를 그냥 나타내는 게 아니라 ‘전환(pivot)’

⇒ 열과 행을 바꿔가면서 내가 보고 싶은 뷰 형태로 데이터를 살펴볼 수 있음

 

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

내가 어떤 표를 만들지 명확해야 피벗테이블 작성 가능

 

만드는 방법

raw 데이터에서 아무데나 마우스 커서를 둔 다음 ‘삽입’ - ‘피벗테이블’

⇒ 엑셀이 자동으로 범위를 잡으면서 팝업이 뜸

 

텍스트 나누기

하나의 셀에 있는 하나의 데이터(예: 문자열)를

두 개 이상의 셀에 나누어 넣는 기능

 

예: ‘-’으로 연결된 3개의 단어 문자열을 ‘-’ 기준으로 나누어 각각 3개의 셀에 넣음

 

만드는 방법

‘데이터’ - ‘텍스트 나누기’

 

나눠진 데이터를 합치려면

나눠져 있는 각각의 셀들을 클릭해서 &”-”&로 연결 (& = +)

 

필터

표에서 열 제목 부분을 드래그하고 ‘데이터’ - ‘필터’

 

단축키가 여러 개

예전: Alt + D + F + F

요즘: Alt + A + T

 

Alt + A + T 설명

홈 탭에 있다가 A를 누르면 데이터 탭으로 이동

거기서 T를 누르면 필터가 선택됨

 

Alt + A + T보다 Alt + D + F + F가

손가락 거리가 가까워서 쓰기 편함

 

정식 단축키: Ctrl + Shift + L (이건 두 손으로 써야 해서 잘 안 씀)

 

고급 필터

‘데이터’ - ‘고급’

 

조건을 한 행에 쓰면 AND로 인식 (모두 만족)

조건을 여러 행에 나눠 쓰면 OR로 인식 (하나 이상 만족)

 

필터 후 열 이름 옆에 아래 방향 화살표가 생김

 

눌러보면

색 기준 필터: 해당 색이 입혀진 셀만 필터해서 보여줌

숫자 필터: ‘크거나 같음’을 선택 후 조건 설정하면 조건에 맞는 셀 필터

 

고급 필터는 자주 쓸 일은 없고 OR 조건같이 까다로운 경우에 사용

 

데이터 유효성 검사

데이터 유효성 검사로 데이터 입력 제한하기

셀이나 범위, 상황에 따라 유효하다고 인정하는 데이터만 입력되도록 하는 기능

 

엑셀 시각화

차트 작성하기 1

차트에 사용될 데이터 범위를 잘 선택하는 것이 중요

 

만드는 방법

필터와 달리 열 제목만 선택하는 게 아니라

열 이름 바로 아래에 있는 집계 값(예: 합계)을 같이 선택

선택한 후 ‘삽입’ - 원하는 그래프 이미지 선택

 

막대 그래프

수치의 크고 작음을 표현

 

꺾은선형, 영역형 차트

시간 흐름에 따른 수치의 크고 작음을 표현

추세를 보기에 좋음

 

원형, 도넛형 차트

전체에서 차지하는 비율 확인

 

조건부 서식 적용하기 1

조건부 서식이란?

특정한 조건 규칙을 만족하는 데이터에만 자동으로 서식이 적용되도록 하는 기능

 

홈 - 조건부 서식

 

데이터 분석 기초

데이터 분석의 목적

데이터를 분석하는 데서 끝나지 않고 인사이트를 얻어야

 

예:

“우리 회사 매출의 70% 이상을 차지하는 제품들은 무엇인가?” (= 인기 제품 파악)

“A 제품군의 개수가 적합한가?” (= 재고 관리)

“B 제품군을 A 제품군으로 전환시키기 위해서 할 수 있는 일은?”

“C 제품군을 지속적으로 개발하여 A, B 제품군으로 전환시켜야 하는지 아니면 C 제품군을 Drop 시켜야 하는지”

 

데이터 분석 과정

데이터 수집

데이터 전처리 - 데이터 분석 전체 과정의 60~80%를 차지, 데이터의 질이 분석의 결과를 결정해서 중요한 단계

데이터 모델링

검증 및 평가

데이터 시각화

 

엑셀은 데이터 전처리와 데이터 시각화에 특화된 툴!

 

엑셀 데이터 분석 활용

손익 계산서 작성 및 분석

손익 계산서란?

회계 기간에 속하는 모든 수익과 이에 상응하는 모든 비용을 표시하여 손익을 나타내는 회계 문서

매출 분석을 위한 대시보드 작성

대시보드 = 현황판