[Part 3-2] 엑셀로 끝내는 실전 데이터 분석 강의 - HLOOKUP, COUNTIFS, SUMIFS, 날짜 함수, IFERROR

---

목차

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

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

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

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

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

 

---

HLOOKUP으로 필요한 데이터만 불러오기

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

• 보통 열을 기준으로 데이터를 작성하기 때문에, 행을 기준으로 데이터를 불러오는 HLOOKUP은 실무에서 자주 쓰이지 않는다.

** 조건문 안에 조건이 여러 개일 경우, and를 쓰고 괄호로 묶어주기 ex. if(and(H6>=4000000,J6<100%), "기대주", "일반)

** 함수 사용 시 사용 목적, 분석 목적이 무엇인지 판단해야 VLOOKUP, HLOOKUP 중 적절한 함수를 사용할 수 있다.

---

COUNTIF(S)로 조건에 맞는 데이터 개수 구하기

COUNTIF ( 개수를 세어야 할 값이 있는 셀의 전체 범위, range에서 세어야 하는 조건 )

 

COUNTIFS ( 개수를 세어야 할 값이 있는 셀의 전체 범위(1번째), range1에서 세어야 하는 조건, 개수를 세어야 할 값이 있는 셀의 전체 범위(2번째), range2에서 세어야 하는 조건 ...)

EX.

백화점 회원 현황 데이터 (고객명, 지역, 성별, 등급(골드,..), 구매 금액, 할인쿠폰 발행 여부, 사은품 증정 여부, ... ) 

• 백화점에 오는 고객들의 성별
• 백화점에 오는 고객들이 사는 지역
• 그리고 플래티넘,골드,할인쿠폰 발행 여부를 위 성별/지역별로 알고 싶을 수 있다.

=> vip회원 현황, 즉 등급별로 성별과 지역 표를 작성

=> 예산이 한정되어 있으니, 이벤트를 기획할 때 서울에서 가장 vip고객이 많이 오는 것을 확인하고 우선순위를 정하는 등의 의사결정을 할 수 있을 것

• EX. COUNTIFS( $G:$G, $O15, $I:$I, "O" ) : G열(등급)에서 $O15(플래티넘)에 해당하는 고객 중 할인쿠폰 발행($I:$I)에 "O" 인 데이터의 개수를 세는 식

**범위 잡을 때 데이터가 추가될 수 있으므로 전체 열 잡아야 한다고 강조했었음 - G열(등급)과 I열(할인 쿠폰 발행) 전체를 범위로 잡음 - 그래서 G:G, I:I

---

SUMIF(S)로 조건에 맞는 데이터의 합계 구하기

SUMIF ( 조건을 찾을 범위, 조건 값, 더할 값들이 있는 범위 )

EX. 요약 손익 계산서

• 매출액, 매출원가, 판매관리비가 있는 raw data를 표로 정리
• 구분 항목들을 복사하여 데이터 탭의 '중복된 항목 제거'기능을 이용하면 총 몇가지를 구해야하는지 알 수 있다.
• 손익계산서 관련
  • 매출이익률 = 매출이익 / 매출액
  • 영업이익 = 매출이익 - 판매관리비
  • 영업이익률 = 영업이익 / 매출액

**백만원 단위로 표시하기 ( Ctrl + Alt + V 이용 / Ctrl + 1 셀 서식 이용 )

:이유는 의사결정을 내리는 데 있어서 백만원 이하 단위는 크게 영향을 미치지 않기 때문

1. 일괄적으로 나누기 : 아무 빈 칸에 100만을 써준다 => 매출액과 매출원가만 100만원으로 나누기 위해 선택 후 Ctrl + Alt + V 를 누르면, 엑셀에 있는 모든 붙여넣기의 경우의 수가 나와있다.
   • '값' 선택 후, 연산에서 '나누기' 선택
   • 일괄적으로 연산할 때 편리한 기능
2. 보이는 수만 바꾸기 : 매출액, 매출원가, 매출이익, 영업이익 선택 후 Ctrl + 1 (셀 서식)
   • 표시 형식에서 사용자 지정 - 형식에 (0)
     • 여기서 0은 방정식에서 x처럼 임의의 수 한 개를 의미
     • (@)는 임의의 문자열에 괄호를 치라는 의미
     • (#) 은 임의의 문자/숫자 모든 데이터에 괄호를 치라는 의미
     • 0, 임의의 수를 의미하는 0 뒤에 쉼표(,) 입력 시 0 3개를 생략해줌, 여기서는 1000000이니까 쉼표 두 번 입력 => 0,,
     • 띄어쓰기는 _-

SUMIFS ( 더할 값들이 있는 범위, 첫 번째 조건 범위, 조건 값 1, 두 번째 조건 범위, 조건 값 2 ... )

• SUMIF에서 순서가 1,2,3이었다면 여기에서는 3,1,2,1,2...
• 전년대비 상승률을 적을 때에는 이전해 매출액 / 올해 매출액 나눈 뒤 1을 빼준다. (서식도 %로 맞춰주기)

문서 정리 방법 두 가지 (셀 서식 - 맞춤 관련)

1. 가운데 맞춤

: '병합하고 가운데 맞춤'은 후에 다른 기능 이용 시 제약이 많기 때문에 지양하고, 대신 이 방법을 사용 

• 셀 서식 (Ctrl + 1) - '맞춤'
  • 텍스트 맞춤 - 가로 - 선택 영역의 가운데로

2. 칸에 문자열 너비를 모두 같게 설정하기

• 셀 서식 - 맞춤 -- 텍스트 맞춤 - 가로 - 균등 분할 (들여쓰기)
• 문자 양 옆에 여백을 두고 싶은 경우 셀 서식에서 '들여쓰기'에 1 입력

반대로 정리된 표에서 특이사항을 발견하여 특정 데이터에 대해 인사이트를 찾고 싶은 경우, raw data로 돌아가 필터를 이용, 해당 날짜의 데이터를 확인

• EX. 2018년 매출 원가가 2017년에 비해 많이 줄어듦(-22%)
  • 필터를 설정하여 항목=매출원가, 연도=2017년 금액을 모두 확인
  • 확인해보니 두 배가 넘는 큰 금액이 지출된 부분이 있었음
  • 특이사항에 대해 구매 담당자와 일시적인 것인지, 거래처가 바뀐 것인지, 신제품으로 바꾼 것인지, 제품 가격이 올라간건지 ..등등 에 대해 소통하여 문제를 파악하여 해결

---

날짜 관련 필수 함수 알아보기

• 외부 엑셀 파일을 다운받아 보는 경우, 날짜의 서식이 일반으로 되어있어 43101 이런식으로 이상하게 쓰여있는 경우가 많음, 날짜형식 OR 간단한 날짜형식으로 바꾸어주기

DAYS ( 종료일, 시작일 )

• 기간 계산시 사용
• 결과가 날짜처럼 나오는 경우, 날짜로 되어있는 서식을 일반으로 되돌려주기

DATEDIF ( 시작일, 종료일, ["D"/"M"/"Y"/"YM"/"MD"] )

• ym일시 year은 무시하고 개월 수만 (ex. 1년7개월이라면 7 출력), md도 마찬가지 (n개월 16일 => 16)
• 날짜가 쓰여있는 셀로 '입금 여부' 입력 - if ( F8 = 0, "X", "O" ) => 셀이 0 이면 (입금확인일이 공란), X

• TODAY() : 기준 일 입력시 사용 - 인수 들어가지 않음
• YEAR(날짜) / MOMTH(날짜) / DAY(날짜) : 앞서 '일자'를 연,월,일로 나누어 적어주었을 때 (텍스트 나누기) 이 함수를 이용해서 간단히 작성 가능

NETWORKDAYS ( 시작일, 종료일, [휴무일] ) / NETWORKDAYS.INTL ( 시작일, 종료일, [주말], [휴무일] )

• [ ]는 생략 가능한 인수,디폴트=토,일요일 주말을 제외시켜줌
• 근무일과 휴무일이 일정하지 않을경우 NETWORKDAYS.INTL 함수 사용

---

IFERROR로 오류 숨기기

IFERROR ( 수식이나 함수, 앞의 수식이나 함수가 오류 났을 때 표시할 값 )

• EX. 연봉상승률 계산 수식에서, 전년도 연봉이 존재하지 않았던 사원의 경우, 분자가 0이라 에러가 난다. 이런 경우 수식을 고쳐주기 위하여 IFERROR 함수를 사용
  • 해당 수식에서 에러가 날 시, 어떤 문구를 출력할지 정할 수 있어 편리하다.
  • IFERROR ( 올해 연봉 / 작년 연봉 -1, "신입사원" ) => 작년 연봉 값이 없는 경우, 신입사원 문구가 출력된다.
• VLOOKUP에도 사용 가능
  • 사번과 이름 표를 새로 생성하는 경우, 원본 데이터에 존재하지 않는 사번이 VLOOKUP 안에 들어올 경우, 에러 대신 "사번입력오류"를 띄우기, VLOOKUP수식에 IFERROR로 둘러주고 출력할 문자를 입력해주면 된다.

---