[패스트캠퍼스] 엑셀 레벨원 : 직장생활이 편해지는 엑셀 필살기 (IF함수_IFERROR, SUMIFS, COUNTIFS, VLOOKUP, 텍스트처리_LEFT/RIGHT/MID/FIND, 피벗테이블, 조건부서식, 차트)

커리큘럼

0. 오리엔테이션 및 엑셀을 배우는 마음가짐

Part 1. 엑셀 초보 탈출, 업무 속도에 날개 달기, 10분만에 끝내는 총정리

Part 2. IF가 들어가는 함수, VLOOKUP, 텍스트 처리, 총정리

Part 3. 함수보다 더 쉬운 피벗 테이블 마스터, 총정리
Part 4. 서식 바꾸는 반복 작업 이제 그만, 엑셀 차트의 세계,총정리
part 5. 전체 복습 및 넥스트 레벨 가이드

 

엑셀 잘 하는 것: 마우스 없이 키보드로 뚝딱?

#사용자의 입장에서

#목적에 부합하도록

#자료를 구성하는 것

 

[PART 1] 엑셀 핵심 기초 

✅ 01-01. 다짜고짜 엑셀에 데이터 입력하기 '값/수식/서식'

엑셀 데이터: 값, 수식 그리고 서식

- 문자면 왼쪽 정렬, 숫자면 오른쪽 정렬

ctrl+1 셀 서식창을 열기 '셀 서식'

값: 키보드로 입력하는 모든 데이터

 

✅ 01-02. 엑셀 '참조'

워크시트 구성 요소: 행(Row), 열(Column) 그리고 셀(Cell)

- 행은 가로로 길지만 상하로 이동함

- 열은 세로로 길지만 좌우로 이동함

상대 참조는 어디로든 나를 따라오는 참조!

절대 참조는 'F4' 키를 누르면 절대 참조로 변경됨, 마치 자물쇠!

행 고정 혼합 참조: 'F4'를 두 번 더 누르면: 위/아래로 이동 X
열 고정 혼합 참조: 'F4' 세 번 누르면: 위 아래로 이동 O

 

 

 

✅ 01-03. 빠른실행도구모음 사용자 지정

[많이 사용하는 명령]을 [모든 명령]으로 변경

ALT키와 숫자 키 활용 Alt 먼저 누르고 1 이런 식...!

'보기' 탭에서 '눈금선'해제

왼쪽 상단의 화살표 > 기타 명령

파일 탭 > 옵션 > 빠른 실행 도구 모음

많이 사용하는 명령 > 모든 명령

 

 

✅ 01-04. 깨지지 않는 서식을 위한, 선택하여 붙여넣기

엑셀 데이터 : 값 수식 그리고 서식

- 엑셀의 모든 셀에는 서식이 적용되어 있음

- 복사 붙여넣기를 가장 많이 함!

원치 않는 테두리나 배경색 등의 서식이 붙여 넣어지는 경우가 많음

Ctrl+Alt+V 를 통해서 수식, 값, 서식을 각각 붙여넣을 수 있음!!!!

빠른실행도구모음 X 붙여넣기 (수식/값/서식을 따로)

 

 

✅ 01-05. 화면에 보이는 셀 선택

숨김처리되어 있는 데이터를 제외하고 화면에 보이는 셀만 복붙할 수 있도록

빠른실행도구모음X화면에 보이는 셀 선택 (ㅋ_ㅋ)

 

행이나 열 숨길 때: 선택해서 (드래그해서) 숨기기, 숨기기 취소를 하는데

마우스보다는 단축키, 7~9행 숨기길 원한다면 걸치게 셀 선택하고
행 숨기기 Ctrl 9 누르면
행 숨기기 취소는 Ctrl Shift 9

열은 Ctrl 0
열 숨기기 취소는 단축키 없음 (싱기하군요)

💥 범위 드래그 후 Alt + ; 화면에 보이는 셀 선택

 

 

 

✅ 01-06. 엑셀 필수 단축키

엑셀 작업 속도 향상을 위한 필수 단축키

- 빠른실행도구모음 Alt + 1,2,3...

데이터 선택 단축기

- 셀 범위 지정하기: Shift + 방향

- 연속된 데이터 범위 지정 : Ctrl + Shift + 방향키
- 모두 선택 Ctrl + A

- 필터 설정 Ctrl + Shift + L

 

행/열 관련 단축키

- 행 선택하기 Shift + Space bar

- 열 선택하기 Ctrl + Space bar
- 행/열 삽입 Ctrl + Shift + +

- 행/열 삭제 Ctrl + 

 

이동 관련 단축키

- 행/열 마지막으로 이동 Ctrl + 방향키
- A1셀로 이동 Ctrl + Home

 

선택하여 붙여넣기 

Ctrl + Alt + V

 

셀서식 창 열기 Ctrl 1

글씨 굵게 Ctrrl + B

셀 편집 F2

절대 참조 설정 F4

셀 내 줄 바꾸기 Alt + Enter

 

 

✅ 01-07. 총정리

서식: 글꼴, 셀 등 디자인 요소
- 글꼴 서식: 글꼴 색, 크기, 굵게, 기울임,...

 

참조: 상대 참조, 절대 참조 그리고 혼합 참조

어떤 행이나 열만 고정하는 것, 행 고정 혼합 참조 및 열 고정 혼합 참조(행은 위아래로 이동하지만 열은 이동하지 않음)

 

빠른실행도구모음

Alt + 1,2,3...

 

선택하여 붙여넣기 Ctrl + Alt + V : 수식/값/서식을 따로 붙여넣을 수 있음!

 

화면에 보이는셀 선택: Alt + ; 를 통해서

 

필수 단축키

 

 

[PART 2] 엑셀 필수 함수 (IF, VLOOKUP, 텍스트 처리)

✅ 02-01. IF 함수

부등호/등호(>, <, =)를 활용해 조건을 가정하고

가정을 만족하는 값과 만족하지 않는 값으로 데이터를 분류

IF(A1>1000000, "A", "B")

"A"는 value_if_ture

"B"는 value_if_false

 

 

IF 함수 중첩

- 2번 중첩하면 3가지로 구분

- 3번 중첩하면 4가지로 구분

- N번 중첩해서 사용하면 데이터 N+1가지로 구분

 

 

나이를 연령대로 구분하기 위해서는?

연령대: silver, blue, green으로 구분하고자 함
IF(E5>=50,"SILVER",IF(E5>=20,"BLUE","GREEN"))
*함수 안의 문자는 쌍따옴표 안에! 

*데이터는 계속 변하고, 업무는 계속 반복되므로...
SILVER의 기준 연령을 수식에 값으로 써 놓으면, 직접 수정해서 반영한 후에 밑으로 복사 붙여넣기 해야하므로
다른 셀을 참조하는 것이 좋음!
**관계형데이터베이스의 느낌과 개념 자체는 유사하다...

참조하고 복붙할 때 '절대참조' '혼합참조' 써야됨!!!!

옆이나/밑으로 따라와도 되는지를 생각해야함~~~~~~

여러 개의 셀을 한 번에 절대참조 걸고 싶으면, 드래그 걸어서 한 번만 눌러도 됨

 

✅ 02-02. COUNTIFS 함수

COUNT: 특정 범위에서 "숫자 데이터"가 들어있는 셀의 개수 파악

COUNTA: 특정 범위에서 문자와 숫자를 모두 포함한 데이터가 들어있는 셀 (비어있지 않은 셀)의 개수 파악

COUNTBNALK: 특정 범위에서 비어있는 셀의 개수 파악

COUNTIF: 특정 범위에서 하나의 조건을 만족하는 셀의 개수 파악

= COUNTIF(C5:C12,"A") = 1

= COUNTIF(C5:C12,">=5") = 1

 

COUNTIFS: 2개 이상의 조건을 동시에 만족하는 셀의 개수 파악

COUNTIFS는 1개일때도 쓸 수 있음!

 

Q. 남성 고객 몇 명일까?

= COUNTIFS($C$5:$C$8072,M5)

기존 데이터 밑으로 신규 데이터가 추가될 때, $C:$C 열 자체를 잡아버리기!

데이터가 늘었다, 줄었다할 때 함수의 범위를 열 또는 행으로 잡으면 좋음

 

Q. 직업별 인원은?

=COUNTIFS($G:$G,M11)

 

데이터의 결측치가 있다는 것을 성별의 SUM과 직업별 SUM이 다른 것에 의해 발견할 수 있음!

 

Q. 남성 중 연령대가 40살 이상인 고객

=COUNTIFS($C:$C,P5,$F:$F,Q5)

첫번째 조건범위, 조건, 두번째 조건범위, 조건

 

Q. 여성 중 연령대가 40살 이상인 고개

=COUNTIFS($C:$C,P11,$E:$E,">=40")

*조건범위 쓸 때 부등호 쓸거면 꼭 쌍따옴표 안에 넣기

 

=COUNTIFS($C:$C,P11,$E:$E,">="&Q11)

*Q11에 원래 40이 써져있을 때 ,&라는 함수를 활용할 수 있음

 

 

누락 인원을 찾기 위해 COUNTBLANK 쓸 때는
- 열/행을 전체로 잡지 말기
- Ctrl+Shift+방향키 보다는 *여러 번 눌러야 되니까
- 범위의 첫칸 선택해두고 스크롤을 내려서 Shift를 누른 상태에서 클릭!

 

숫자 기재 건을 찾기 위해 COUNT를 쓰기

숫자가 아닐 때는 COUNTA를 쓰기, 대신 Column명이 포함될 수 있음 -1로 빼주기...

 

 

✅ 02-03. 매출이 가장 높은 팀을 찾아라 SUMIFS!

특정 조건을 만족하는 데이터의 합계 계산

 

=SUMIFS(더할값들의범위, 조건 범위1, 조건1, 조건 범위2, 조건2)
=SUMIFS(매출,월,1월,팀,팀명)
*sum_range: Ctrl+Shift+방향키 해도 되고 열을 통째로 잡아도 되고(절대참조 꼭!)

*열고정/행고정 혼합 참조 적절히 쓰기!

숫자를 많이 다루는 경우 SUMIFS를 많이 쓰는 듯

 

 

✅ 02-04. 엑셀의 대명사 VLOOKUP 함수!
VLOOKUP: 특정 열에서 원하는 데이터를 찾아 그 열로부터 n번째 있는 데이터를 불러 오는 함수

Vertical에서('열'을 중심으로) Look up: ~을 찾다(데이터를 찾아 오다)

어떤 열을 기준으로 n번째 떨어져있는 열에서 데이터를 찾아오기

 

 

불러올 데이터의 공통 기준열(사번)을 1열이라고 가정하고 n번째 있는 열의 데이터를 불러옴

즉 기준의 오른쪽에 있는 데이터만 가져올 수 있고 왼쪽은 불가능함

기준열은 중복값이 없는 키값이어야 함!

 

lookup_value(찾을 데이터)

table_arry(불러올 데이터 범위)

col_index_num(불러올 열의 번호)
range_lookup 근사치여부 0(False) = 정확히 일치할 때만! 1(True)=근사치여도 ㅇㅋㅇㅋ

 

팀은 3번째, 직급은 4번째, 담당업무는 7번쨰 열에 있었음

=VLOOKUP($C5, LIST!$D:$J,3,0) =VLOOKUP($C5, LIST!$D:$J,4,0) =VLOOKUP($C5, LIST!$D:$J,7,0)

=VLOOKUP($C6, LIST!$D:$J,3,0) =VLOOKUP($C6, LIST!$D:$J,4,0) =VLOOKUP($C6, LIST!$D:$J,7,0)
*아래로 내려가면서 바뀌어야 함: 행이 바뀌어야 함
옆으로 바꿀 때는 바뀌면 안됨: 즉 C열은 고정되어야 함. C열 앞에만 달러 표기되게 C열 고정!
*데이터가 추가될 수 있으므로 열 머리글에서 모두 선택하기 (절대참조 적용하기)

 

 

✅ 02-05. VLOOKUP 함수를 활용한 자동 견적서 

품목코드만 입력하면 다른 정보가 자동으로 변경되는 견적서 만들기

 

 

=VLOOKUP(품목번호,탭명!$B:$H,2,0)

=VLOOKUP($C17,RAW!$B:$H,2,0)

 

선택하여 붙여넣기
Ctrl+Alt+V 수식 붙여넣기!

총 합계 SUM 구하기, 공급가액, 부가세액=공급가액*0.1, 합계는 공급가액+부가세액

 

✅ 03-01. 텍스트 처리 함수
LEFT / RIGHT / MID 함수

텍스트의 가장 왼쪽, 오른쪽 또는 중간부터 원하는 문자열을 추출하는 함수

 

 

FIND 함수

텍스트에서 특정 단어나 문장이 시작하는 '위치'를 숫자로 출력

IFERROR(FIND("맛", $E2,1),0)

*맛이 시작하는 '위치' 보다는 빈도가 중요하지 않나?

 

키워드 분석

맛: =COUNTIFS(C:C,">0")

배송: =COUNTIFS(D:D,">0")
*어떤 주제에 대한 리뷰를 많이 남기는지...!

여러 키워드를 중첩해서 찾아야 하긴 함... '좋' '싫'

 

전체 리뷰수

=COUNTA($B:$B)-1

 

 

✅ 03-02. FIND 함수를 활용한 리뷰 분석 : 이 집 맛집일까?
FIND 함수

텍스트에서 특정 단어나 문장이 시작하는 '위치'를 숫자로 출력

IFERROR(FIND("맛", $E2,1),0)

 

맛 / 맛+10 / 없 / 별로 / 서비스 / 서비스 +10

=FIND(C$1,$B2,1)

=IFERROR(FIND(C$1,$B2,1),0)

 

맛 / 맛+10 / 없 / 별로 / 서비스 / 서비스 +10

*맛 뒤로 10개의 텍스트 추출 

=MID($B2,C2,10)
=IFERROR(MID($B2,C2,10),0)
*맛~의 내용과 평점이 높은 것이 교집합인 경우: 대다수 긍정 후기

 

맛 / 맛+10 / 없 / 별로 / 서비스 / 서비스 +10

맛이 포함된 10개의 텍스트 중에 '없' 혹은 '별로' 있는지 확인

 

 

분석하기

 

맛이라는 단어가 언급되었지만 없/별로가 없어야 함

맛이라는 단어가 언급되었지만 없/별로가 있어야 함

서비스(긍정): COUNTIFS(서비스열>"0",평점>">=4")

서비스(부정): COUNTIFS(서비스열>"0",평점>"<4")

 

시각화하기

맛(긍정-부정)과 서비스(긍정-부정)

데이터 범위 전체 선택

차트: 분산형 차트 클릭
- 우리 식당 데이터 > 데이터 선택 > 계열 X값, Y값

*우리 식당이 다른 계열로 구분되어서 색상을 바꿀 수 있음

*차트 테두리 윤곽선 없음으로 처리하고

*가로 세로 선도 선택해서 지우기

*점 색상도 원하는 컬러로 바꾸기

*사분면 구분 (1사분면, 2사분면, 3사분면, 4사분면)

삽입 탭에 도형 삽입이 가능하므로 네모 도형 만들어서 윤곽선 없애고
색상 채워서 투명도를 주고 맨 뒤로 보내기 (차트 배경색은 채우기 없음으로 바꾸고!)

 

총정리

IF 함수는 부등호와 등호를 활용해서 조건 가정

세 개의 인수, 데이터 분류조건, 만족 시 표기값, 불만족 시 표기값

두 개보다 여러 개여야 한다면 중첩 사용하면 됨

COUNTIFS 2개 이상의 조건을 동시에 만족하는 셀의 개수 

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

VLOOKUP 특정 열에서 원하는 데이터를 찾아 그 열로부터 n번째에 있는 데이터를 불러 오는 함수

0은 정확한 일치,1은 근사값

LEFT / RIGHT / MID / FIND 함수; 텍스트 처리, FIND는 내가 찾는 특정 단어나 문장이 시작하는 위치를 숫자로 출력

IFERROR와 FIND를 섞어서 쓰면 어떤 키워드가 포함된 데이터를 필터링할 수 있음

 

 

[PART 3] 피벗 테이블 마스터

✅ 04-01. 피벗 테이블 (Pivot Table)
커다란 표의 데이터를 요약하는 통계표

기능 자체는 드래그만 해도 됨, 복잡한 데이터에서 내가 어떤 요약 통계 표를 만들어야 하는지 명확히 정의하지 못해서...

내가 만들어야 하는 피벗테이블을 손으로 직접 그린 후에 피벗 테이블로 따라 만들기

]피벗 테이블 필드]의 필드를 필터/열/행/값 중 필요한 부분에 드래그하기~

 

RAW sheet에 가서...

데이터 범위 Ctrl+A 혹은 Ctrl+Shift+방향키로 다 잡기도 하지만

깨끗하게 정리되어 있는 경우에는 한 셀에 커서가 클릭만 되어 있어도 됨

삽입 > 피벗테이블 > 누르는 순간: 엑셀이 자동으로 표/범위 잡아줌

보통 기존 워크시트에 '실습' 탭을 누른 순간 이름이 들어감

탭에 넘어와서 빈 셀을 클릭해야 거기에 피벗테이블이 만들어짐!

 

피벗테이블이 생겼고 오른쪽에는 피벗 테이블 필드가 생성됨

'열'별로 정리되기도, '행'별로 정리되기도 함

문자데이터는 체크하면 자동으로 '행'으로 들어감

숫자데이터는 체크하면 자동으로 '값'으로 들어감

값에 들어간 금액의 분류를 기준으로 보고 싶다면 > 필터로 넣어주기

e.g 매출액만 보거나... 등

 

✅ 04-02. 피벗 테이블 (Pivot Table)
피벗 테이블을 활용한 ABC 분석 (=파레토 분석)

관리대상을 A, B, C 그룹으로 나눠서...

 

 

피벗 테이블을 활용한 ABC 분석 (=파레토 분석)

관리대상을 A, B, C 그룹으로 나눠서...

제품 별 매출을 파악하기 위한 피벗테이블 만들기

- 매출이 큰 순서대로 제품 나열하기

행에는 제품 ID넣고

값에는 Sales 넣고 (숫자 내림차순 정렬) 마우스 오른쪽으로

금액 구성비는 =/$전체매출

누적 구성비도 pivot으로 가능

*필드 목록 표시해서

값에 세일즈를 하나 더 넣은 후에 누계 비율로 바꾸기

- 값 필드 설정

- 요약 기준은 합계 그대로

- 값 표시 형식 탭에 '누계 비율'이 그대로 있음

 

소수의 제품군이 매출의 70%를 차지하므로...

피벗 테이블 분석 > 피벗 차트

묶은 세로 막대형을 눌렀을 때

계열 차트 종류 변경 : 이걸 하면 한 판에 두 개의 그래프 그릴 수 있음 (막대형+꺾은선형...!) 💥

- Sales2 : 누계 비율은 퍼센트 축을 새로 만들어줄 것임 (보조 축 체크)
퍼센트 지표 챙기면서 100%에 수렴하게 생김

축서식 가서 최대값을 1.2가 아닌 1로 바꿈 (100%)

주 눈금선을 클릭하면 퍼센티지의 기준으로 가로선이 생김,

70%의 가로선까지 만나는 상품들이 주력 상품들인 것을 알 수 있음

 

 

정리하기

피벗 테이블: 커다란 표의 데이터를 요약하는 통계표 (열, 행, 값, 필터)

 

 

[PART 4] 엑셀을 활용한 시각화

✅ 05-01. 조건부 서식

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

 

규칙 관리: 새규칙

규칙 유형 선택, 

서식 스타일 (2가지 색조: 최소 vs 최대)

최소값에 가까울 때 보통 더 연하게 하는 편

부정적인 수치일 때 빨간색 많이 씀

한 끗 다르게 만드는 것은?

- 서울과 경기가 너무 빨간 색이라서 나머지가 잘 안 보임...

- 대다수의 데이터를 더 잘 표현하기 위해서 최대, 최소를 자동화하는 것이 아니라 '숫자'로 종류를 바꿈
- 그래서 최대값을 1,000으로 만들어서 차이가 잘 표현되게끔 

- 중간값을 만들고, 최소값을 0으로 넣어서...

 

데이터막대를 쓸 때

데이터도 최대값이 너무 서울/경기가 유사하니까

1,000을 최대값으로 놓게 되면~

 

아이콘 집합

데이터를 신호등처럼 나눠서 정상은 초록, 일반은 노랑, 위험은 빨강~

코로나 확진자수이므로 숫자 클 때 더 안 좋게하고 싶으면 아이콘 순서 바꾸기로 일괄 적용! 

데이터를 안 보이게 아이콘만 표시를 한 후에 데이터를 가운데 맞춤을 하면 정렬됨

지표의 좋고 나쁨을 한 눈에 볼 수 있게됨 (세밀한 숫자가 없더라도)

 

 

 

규칙 관리 > 다음을 포함하는 셀만 서식 지정 (IF 함수와 유사)

상위또는 하위 값, 평균보다 크거나 작은 값, 고유 또는 중복 값만 서식 지정

 

수식 사용하여 서식을 지정할 셀 결정

e.g 1월에 가장 많은 확진자가 나온 지역 찾아서 그 셀에 표기되게 하기

- 구분 행 제외 색칠 칠해질 열/행 다 선택하기

- 다음 수식이 참인 값의 서식 지정

- 조건부 서식은 기본 절대참조로 잡혀있음, 행의 절대값 빼주고 MAX 함수 써서~

 

 

✅ 05-02. 자동 테두리 서식
아래 데이터가 추가되면 자동으로 테두리 서식이 변경되는 조건부 서식의 적용

 

(상황)

- 변동이 큰 데이터라고 했을 때

- 맨 밑에 테두리가 그려져있는데

- 데이터가 추가된다고 테두리를 다시 설정하는 것이 귀찮으니까...

 

COUNTA에서 열을 통으로 잡고 데이터 개수 세는 것처럼

행의 번호가 우리 전체 데이터의 개수와 동일해지면

테두리 마지막을 설정해달라는 명령! 💥 

 

조건부 서식 : 수식을 사용하여 서식을 지정할 셀 결정

 

=ROW($B5)-4=COUNTA($B:$B)-2
*지정한 셀의 행 번호를 추출해는 함수: ROW

수식 붙여넣기 혹은 값 붙여넣기를 통해서 Ctrl + Alt + V

새로 생긴 데이터 끝에 테두리 생김! (와 싱기하다ㅠ)

테두리 정리하는 귀찮은 작업을 안해도 됨 (ㅋㅋㅋㅋㅋㅋ)

 

 

✅ 05-02. 차트 디자인하는 법 
심미적인 의도 외에도 더 많은 정보 더 빠르게 전달

삽입 탭의 차트 > 그리고 싶은 차트 선택

숫자 데이터를 잡을 때 왼쪽이나 윗쪽의 기준이 되는 이름을 선택해야 함

데이터 레이블 추가: 값이 추가됨, 강조용만 폰트 색상 바꾸거나 막대 색상 바꾸기 등등등~

 

총정리 (열두시에도 넘어가는군...)

조건부 서식

차트 = 숫자의 표현

 

 

 

 

^^ 다 들었다. 12:05에 끊기는구나...^^