[Part 3-1] 엑셀로 끝내는 실전 데이터 분석 강의 - COUNTIF, IF중첩, RANK, VLOOKUP

---

목차

1. 엑셀 소개 및 기본 원리
행,열,셀의 특성 / 상대,절대,혼합 참조 / 수식값 서식, 함수의 특성 이해하기

2. 업무 속도 향상시키기
빠른 실행도구 모음 / 중복항목 제거 및 텍스트 나누기 & 합치기 / 모두 바꾸기 활용,필터 활용

3. 핵심 함수만 확실하게 알기
기본 함수 / IF 중첩을 통한 데이터 분류 / VLOOKUP / HLOOKUP / COUNTIF / SUMIF / 날짜 함수 / IFERROR

4. 시각화
다양한 차트 / 조건부 서식

5. 엑셀 전문가로 거듭나기
손익계산서(raw data다루기, 피벗테이블, 양식 만들기, SUMIFS로 합계 구하기[raw data연동], 분기와 누계 데이터 추가) / 월별 손익 조회 장표 / 재직증명서 만들기 / 상품재고현황 파악 / 간트차크 그리기 / 대시보드 만들기 / OFFSET 함수 / OFFSET함수와 동적 범위 / SUMPRODUCT / MATCH, INDEX

6. 리더에게 인정받는 자료 작성법
보고자료 만들기 / Superstore 대시보드 만들기 / 엑셀 총정리

---

3

핵심 함수만 확실하게 알기

• COUNT : 숫자가 들어간 셀의 개수
• COUNTA = ALL, 모든 셀의 개수
• COUNTBLANCK : 비어있는 셀의 개수

COUNTIF ( 범위,조건 )

• 단, 부등호를 쓸 때 앞에 특정 대상이 없게 써야하는 경우(EX.매출액이 400만원 이상인 모든 상품 출력) 에는 문자열처럼 "를 앞뒤로 붙여주어야 한다. (많이 쓰이진 않음, EX.">400")

---

IF / IF 중첩을 통해 데이터 분류하기

데이터 분석에서 기준을 세워서 분류하는 이유는 해석을 용이하게 하기 위한 것이지만, 분류의 최종 목적은 분류(A,B,C)에 따른 액션을 취하기 위함

=> 엑셀에서 액션을 취하기 위한 가장 중요한 함수가 If이다.

IF ( 참/거짓을 나눌 가정, [참일 경우 표시할 값], [거짓일 경우 표시할 값] )

• IF를 쓰는 것 자체는 쉽다. IF를 "언제" 써야하는지 알아두는 것이 중요
• 마지막에 [거짓일 경우 표시할 값]에 또 IF를 사용하여 추가 분류 가능
  • 90점 이상 A, 90점 이하 그 외 (False)
  • 90점 이하 False에서 또 IF를 사용하여 80점~90점은 B, 그 외는 False (C)로 ...~
  • IF ( E6 >= 90, "상", IF ( E6 >= 80, "중", "하" ) )
  • IF ( F8 >= 20, "S", IF (F8 >= 15, "A", IF (F8 >= 10, "B", "C" ) )

** 위처럼 함수 안에 숫자를 쓰는 것은 지양해야 한다. (데이터 값은 항상 바뀔 수 있기 때문)
=> IF ( F8 >= V8 , S8, IF ... ) *V8은 기준 표 값(20,15,10) , S8은 기준 표 등급(S,A,B)
** 주의해야할 점은 기준 표의 값을 가져왔으니, 절대참조로 바꿔줄 것
=> IF ( F8 >= $V$8 , $S$8, IF ... )

---

RANK 함수로 순위 정하기

Rank ( 순위 매길 대상 값, 순위 매길 대상 범위, 0 OR 1)

 

범위의 경우 항상 절대참조를 걸어야 빈칸을 잡지 않아 정확한 값을 얻을 수 있다.

• • 0 (디폴트) : 내림차순, 1 : 오름차순
  • 후에 필터를 설정하여 오름차순으로 정렬
• 추가적으로 데이터가 입력될 상황이 대부분인데 범위를 한정해놓으면 수정해야해서 번거롭다.
  • 따라서 실무에서는 해당 열 전체를 범위로 잡는 경우가 많다. 
• 함수를 사용하고 검산하는 과정이 중요하다.
  • '합계'(Alt+1)로 검산 가능

---

실무에서 가장 많이 쓰이는 VLOOKUP으로 필요한 데이터만 불러오기

VLOOKUP에서 V는 Vertical(수직의,열)으로, '특정 열을 기준으로 n번째에 있는 데이터를 찾아오는 함수'

• 조건
  1. 공통 기준 열 필요 (찾고자 하는 데이터의 기준) ex. 사번을 기준으로 다른 데이터를 불러오는 경우
  2. vlookup은 '오른쪽'에 있는 데이터를 불러오는 함수, 공통 기준 열이 1열이 되는 것

VLOOKUP ( 찾을 데이터, 공통 기준 열을 기준으로 찾아올 데이터 범위 , 찾아올 데이터의 열 번호 [기준 열이 1열], 0 OR 1 )

• 총 4가지 인수가 들어가야 한다.
• 마지막 인수를 생략할 경우, 디폴트는 1이다.
  • 1인 경우, 기준과 같지 않아도 근사치가 있으면 근사치에 있는 데이터를 불러오게 되고,
  • 0인 경우, 기준이 정확히 일치하는 값만 찾음
• Rank에서 배웠던 대로 범위의 경우 절대참조 걸어야함 주의

** 헷갈릴 수 있는 부분 (Case1은 직급에 따른 추가수당을 입력하려고 하는 상황)

Case 1) 데이터를 찾는 과정에서 이동하는 방향이 ▶인지, ▼인지만 알면 된다. 여기서는 이름별로 찾는거니까 ▶(행 방향)으로 이동, 열 앞에만 자물쇠 $E걸어주기

Case 2) vlookup으로 신규 매출 등급만 가져오기

여기서도 위와 마찬가지로 이름에 따른 데이터를 찾는거라서 행 방향으로 이동, 열인 E에만 자물쇠

** 범위 설정시 구분자 열까지 포함시키느냐 안하느냐는 영향을 주지 않기에 고민하지 않아도 된다.
** 이름(문자) 데이터의 경우 고객이면 고객코드, 사원은 사번, 상품은 상품코드가 있는 것처럼 여러가지 절대 코드들이 있기 때문에, VLOOKUP 실행 시 문자열보다는 코드 중심으로 사용하면 중복값 없이 효율적으로 사용할 수 있다.

원본 데이터 중 의사결정권자들에게 필요하지 않은 정보를 제외시키는 과정에서 VLOOKUP을 활용할 수 있다

• EX. 인사 평가 데이터에서 등급을 매기는데 사용된 점수데이터는, 등급이 산출된 기준이니 의사결정하는 데 필요하지 않은 정보이므로 제외한다.

데이터가 클 경우, 열 번호를 하나하나 고칠 수 없으므로 (앞서 직접 숫자를 대입한 건 좋은 함수가 아니라고 강조했었음)

VLOOKUP 속 바뀌는 열 번호를 일일이 입력하는 대신 자동으로 바뀌게 하기 위해서 혼합 참조 활용

 

F행은 원본 시트에서 VLOOKUP에 쓸 열 번호를 써놓은 행

여기서는 변경하는 방향이 (찾는 데이터가 있는 방향이) ▼(열 방향)이므로 6열 앞에 $자물쇠로 고정해준다.

** 함수에 사용된 필요없는 정보(위 사진처럼 다른 시트의 열 번호를 적어놓은 부분)는 배경 색(흰색)과 동일하게 글씨를 바꾸어 안보이도록 하기

특정 조건에 해당하는 데이터 하이라이트하기

• 홈 탭에서 조건부 서식 선택
  • 규칙 관리 - 조건부 서식 규칙 관리자 - 새 서식 규칙 - '수식을 사용하여 서식을 지정할 셀 결정'
  • 다음 수식이 창인 값의 서식 지정 : "=$J$8=1" (*여기서는 절대 참조가 자동으로 걸림)
    • 서식 - (EX.)채우기:파랑, 글꼴:하양, 굵게 로 설정
    • 8행 앞의 자물쇠($)를 삭제하여 행 방향으로(순위가1위인 '이름'을 찾는 상황) 데이터를 찾을 수 있도록 한다. "=$J8=1" 
      • J8열이 1이면 (J8열은 '순위') 배경은 파란색, 글씨는 파란색, 굵게로 변하도록 한다.
  • 추가로 =$J8>5, 배경색을 회색으로 설정하여 5위 아래인 데이터는 회색으로 보일 수 있도록 하기

=> 원본 데이터의 변화에 따라, 순위, 하이라이트 되는 부분이 자동으로 바뀌기 때문에 편리하다.

---