[부트캠프] 엑셀을 활용한 데이터 분석

앞서 1주차에서 어떤 내용을 학습했는지 간단하게만 언급했었는데요,

이번 글에서는 좀 더 자세하게 공부한 것들을 정리해볼게요!

---

데이터 분석의 개념

데이터 분석의 1차적인 목표가 무엇일까요?

바로 인간의 의사결정을 지원해주는 것입니다.

그렇다면 데이터 분석의 궁극적인 목표는 무엇일까요?

비즈니스를 더 효율적으로 운영할 수 있게 도와주는 것이 데이터 분석의 궁극적인 목표입니다.

그렇기 때문에 데이터 분석을 통해 평가가 올라가거나 성과가 나는 등

뭐라도 좋아지는 게 있어야 데이터 분석이 의미 있다고 할 수 있겠죠.

 

강사님은 현실과 현실보다 더 나은 이상과의 차이를 문제라고 정의해주셨습니다.

현실과 이상과의 갭을 메우는 것이 문제를 해결하는 것이고,

그러기 위해서는 과거의 데이터를 분석해 현실의 문제를 해결해야 합니다.

 

엑셀을 잘한다는 것은?

엑셀을 잘한다는 것은 단순히 빠르게 자료를 만드는 것을 의미하지 않습니다.

사용자의 입장에서 목적에 부합하도록 자료를 구성하는 것입니다.

예를 들어, 대리님께 드릴 자료와 임원분께 드릴 자료가 같은 내용이면 안 되겠죠?

이처럼 사용자가 누구인지, 원하는 정보가 무엇인지 파악하고

이 자료가 필요한 이유가 무엇인지, 이 자료로부터 얻을 아웃풋이 무엇인지 등을 파악해

그에 맞는 자료를 만드는 것이 엑셀을 잘한다는 의미입니다.

 

엑셀의 기본 활용

가장 먼저 엑셀 데이터의 형태를 알아보겠습니다.

우리가 엑셀에서 입력할 수 있는 데이터로는 먼저 텍스트, 숫자와 같은 값이 있고

계산식, 함수와 같은 수식, 그리고 글꼴 서식이나 셀 서식과 같은 서식이 있습니다.

저는 수업을 들었을 때 '서식도 데이터라고?' 라고 생각했었는데

서식에 따라 의미하는 것도 다르고 아무 서식 지정을 하지 않았다고 해도

엑셀의 모든 셀에는 기본적으로 서식이 적용되어 있는 것이기 때문에

서식도 데이터가 될 수 있겠다는 생각을 했답니다!

 

다음으로 엑셀의 기본 구성 요소에 대해 알아보겠습니다.

엑셀은 하나의 행과 하나의 열이 교차하는 지점인 셀에 데이터를 입력하는 방식의 프로그램입니다.

행은 상하로, 열은 좌우로 변경된다는 특징이 있습니다.

 

엑셀의 참조

엑셀에는 세 가지 참조 방식이 있습니다.

가장 기본으로 적용되는 참조 방식은 상대 참조입니다.

상대 참조는 수식을 복사해서 한 칸 밑으로 붙여 넣을 때 참조된 셀도 함께 이동하는 참조 방식입니다.

절대 참조는 수식을 복사해서 한 칸 밑으로 붙여 넣어도 참조된 셀이 이동하지 않는 참조 방식입니다.

마지막으로 혼합 참조는 한 셀의 열 또는 행 중 하나에 절대 참조가 걸려있고 다른 하나는 상대 참조인 참조 방식입니다.

엑셀의 참조는 굉장히 중요하다고 강사님께서 강조하신 부분이에요!.

별표 치고 꼭 기억하세요! ⭐️⭐️⭐️

 

엑셀에서 반드시 기억해야 할 기초가 두 가지 있는데,

첫째는 엑셀의 모든 셀에는 서식이 적용되어 있기 때문에

평소 습관대로 Ctrl + C / V를 하게 되면 값이나 수식뿐만 아니라

복사한 셀의 서식까지 모두 붙여 넣어지게 되기 때문에

수식 / 값 / 서식을 각각 따로 붙여 넣어야 한다는 점입니다.

둘째는 함수를 사용하는 상황에 따라 각각의 참조를 적절히 사용해야 한다는 점입니다.

 

자주 사용되는 단축키

엑셀에 정말 많은 단축키가 있지만, 그중 많이 사용되는 단축키들을 몇 가지 소개해드릴게요.

윈도우 환경과 맥 환경에서 사용하는 단축키가 조금씩 다르니 참고해주세요!

 

사용자 지정

엑셀에서는 표시 형식을 설정할 수 있는데, 다양한 표시 형식 중 '사용자 지정' 기능을 이용하면

좀 더 내가 원하는 방향으로 데이터를 표시해 줄 수 있습니다.

사용자 지정에 사용되는 기호들은 아래와 같습니다.

사용자 지정을 사용하면 표기뿐 아니라 서식도 지정할 수 있는데요.

예를 들어, 입력된 값이 양수일 때 글자 색은 빨간색으로 바꾸고 1000 단위마다 쉼표를 찍고

입력된 값이 음수일 때 글자 색은 파란색으로 바꾸고 1000 단위마다 쉼표를 찍고

입력된 값이 0이면 0으로, 문자면 "수치 확인"이라고 표시하고 싶다면

[빨강]#,##0; [파랑]#,##0; 0; "수치 확인" 이라고 적으면 적용이 됩니다.

 

탐색적 데이터 분석 (EDA)

탐색적 데이터 분석(EDA)은 주어진 데이터만 가지고도 충분한 정보를 찾을 수 있도록 하는 분석 방법입니다.

EDA는 왜 그렇게 중요할까요?

데이터를 수집할 때 내가 원하는 대로만 수집되는 데이터는 아마 없다고 봐도 무방할 것입니다.

EDA를 하면 수집된 데이터의 특성이나 분포를 파악할 수 있고,

결측치나 이상치가 있는지 확인할 수 있으며,

EDA를 통해 어떤 전처리를 해야 하는지, 어떤 분석 방법을 사용해야 하는지 알 수 있습니다.

따라서 EDA를 잘 수행해야 전반적인 데이터 분석 역시 잘 수행할 수 있겠죠!

 

간단하게 엑셀의 데이터 분석 도구를 활용해 기초 통계량을 살펴 보며 EDA를 할 수 있습니다.

데이터 분석 도구가 추가가 안 되신 분들은

[파일]탭 → [옵션] → [추가 기능] → 분석 도구 (팩) 선택 → [이동] → 분석 도구 (팩) 체크 → [확인]

이렇게 추가해 주세요!

 

엑셀 데이터 분석 도구로 기초 통계량 계산하기

1. 엑셀의 [데이터] 탭에서 [데이터 분석 도구]를 클릭하고 [기술 통계법]을 선택한다.

2. 기초 통계량을 계산할 데이터 범위를 열 이름과 함께 선택한다.

3. 데이터의 첫 행을 이름표로 사용할 수 있도록 체크한다.

4. 기초 통계량을 출력할 셀을 선택한다.

5. 요약 통계량을 선택한다.

 

이렇게 쉽게 기초 통계량을 계산할 수 있다니!

다들 한번씩 해보시길 추천드려요 👍

 

피벗 테이블

엑셀의 꽃은 뭐다? 피벗 테이블이다!

피벗 테이블은 쉽게 말해 데이터를 요약하는 통계 표입니다.

채용 공고에 보면 엑셀 활용 능력의 기준으로 피벗 테이블을 적어둔 곳을 여러 번 봤는데

그럼 그만큼 피벗 테이블이 어렵다는 뜻일까요?

아닙니다.

 

사람들이 피벗 테이블을 어렵게 생각하는 가장 큰 이유는

이 복잡하고 방대한 데이터 속에서 내가 어떤 요약 통계표를 만들어야 하는지

스스로 명확하지 않기 때문이라고 합니다.

내가 만들어야 하는 피벗 테이블을 먼저 손으로 그려보면 피벗 테이블을 만드는 것은 아주 쉬운 일이 될 수 있습니다!

한번 머리 속으로 정리하고 나면 드래그 앤 드롭으로 쉽게 표를 만들 수 있기 때문입니다.

앞으로는 지레 겁 먹지 말고 차근차근 내가 필요한 통계표가 무엇인지 생각하고 피벗 테이블을 그려봅시다 😊

 

데이터 전처리

데이터 분석의 과정 중 데이터 전처리 단계가 있습니다.

데이터 전처리는 데이터를 분석 목적과 방법에 맞게 가공하고 처리하는 과정을 말합니다.

데이터 전처리를 하는 방법으로는 데이터의 형태 변환, 변수 선정, 결측치 및 이상치 처리,

데이터 분류, 데이터 분리 및 결합 등이 있습니다.

데이터 전처리 단계는 데이터 분석 과정 중 가장 많은 시간과 비용이 필요한 과정이라고 할 수 있는데요.

실제로 데이터 분석 과정 중 데이터 전처리가 약 60~80% 정도를 차지한다고 합니다.

왜 그럴까요?

"Garbage in, garbage out" 이라는 말이 있습니다.

좋지 않은(부적절한) 데이터로 모델을 돌리면 좋지 않은 결과가 나올 수 밖에 없다는 얘기입니다.

 

엑셀로 데이터 전처리를 하기 위해서는 함수에 대해 알아야 하는데요,

지금부터 살펴볼까요?

 

함수

여러분은 '함수'라는 말을 들으면 어떤 게 가장 먼저 떠오르나요?

저는 중고등학교 때 수학 시간에 선생님이 칠판에 그려주신 깔떼기 그림이 생각 납니다 😄

엑셀에서도 복잡한 수식이나 계산을 간단한 명령어로 사용할 수 있도록 함수들이 여러 개 있는데요,

수학과 마찬가지로 함수에 데이터를 입력하면 결과값을 출력해주죠.

 

엑셀에서 함수를 사용할 땐 아래와 같이 사용합니다.

= 함수명(인수1, 인수2, [인수3] ∙∙∙)

여기서 인수1은 주로 함수의 재로가 되는 입력 값을 나타내고,

모든 인수는 콤마로 구분됩니다.

그리고 대괄호 안에 있는 인수는 생략이 가능하다는 것을 의미합니다.

---

수요일엔 어제 언급만 하고 끝난 함수를 이어서 공부했습니다.

그럼 이제부터 엑셀의 다양한 함수들에 대해 알아볼게요!

---

 

IF 함수

= IF(logical_test, [value_if_true], [value_if_false])
# Ex. = IF(A1 >= 90, "합격", 불합격")

IF 함수는 부등호나 등호를 활용해 조건을 가정하고, 데이터를 조건에 만족하는 값과 그렇지 않는 값으로 분류하기 때문에

IF 함수의 결과는 True나 False 두 가지로 나타납니다.

IF 함수는 중첩해서 사용이 가능한데, n번 중첩했을 때 데이터가 n+1가지로 분류된다는 특징이 있습니다.

 

COUNT 계열 함수

COUNT 함수는 특정 범위에서 숫자 데이터가 들어가 있는 셀의 개수를 파악할 때 사용합니다.

COUNT 함수 안에 숫자 데이터의 개수를 파악할 셀 범위를 넣으면 숫자 데이터가 있는 셀의 개수를 반환해줍니다.

COUNTA 함수는 특정 범위에서 데이터가 들어가 있는 셀의 개수를 파악할 때 사용합니다.

즉, 비어 있지 않은 셀의 개수를 파악할 수 있습니다.

COUNTA 함수 안에 모든 데이터의 개수를 파악할 셀 범위를 넣으면 됩니다.

COUNTBLANK 함수는 이름에서도 알 수 있듯이 특정 범위에서 빈 셀의 개수를 파악할 때 사용합니다.

마찬가지로 괄호 안에 빈 데이터의 개수를 파악할 셀 범위를 넣으면 됩니다.

 

조건을 걸어서 개수를 셀 수 있는 함수들도 있습니다.

바로 COUNTIF 함수와 COUNTIFS 함수인데요.

COUNTIF 함수는 특정 범위에서 하나의 조건을 만족하는 셀의 개수를 파악할 때 사용합니다.

이 함수에서는 특이하게 함수 조건으로 부등식을 사용할 경우 ""(큰따옴표) 안에 넣어야 한다는 특징이 있습니다.

예를 들어, C5와 C12 사이에 있는 셀 중 값이 5 이상인 셀의 개수를 알고 싶으면

= COUNTIF(C5:C12, ">=5") 라고 적어야 합니다.

COUNTIFS 함수는 특정 범위에서 2개 이상의 조건을 동시에 만족하는 셀의 개수를 파악할 때 사용하는데,

조건이 1개일 때도 이 함수가 사용이 가능하기 때문에

헷갈리지 않게 웬만하면 COUNTIFS 함수는 사용하는 게 꿀팁입니다!

 

VLOOKUP 함수, MATCH 함수

VLOOKUP 함수는 기준 열을 1열이라고 가정하고 n번째 있는 데이터를 불러올 때 사용하는 함수입니다.

엑셀을 처음 배울 때 VLOOKUP 함수를 많이들 어려워 하는데 굉장히 많이 쓰이는 함수라고 합니다.

아마 회사에서 데이터를 열어보시면 100단위의 열의 개수가 있는 게 아니라

십 만, 백 만 단위로 방대한 데이터가 있을 것입니다.

이렇게 많고 다양한 데이터에서 내가 필요한 데이터를 먼저 가져오는 게 모든 업무의 시작이겠죠?

 

원래 표에서 1열은 주로 순번이지만 VLOOKUP 함수를 쓸 때는 고유값을 가진 공통 기준열을 1열이라고 가정합니다.

이때 공통 기준열 이전의 데이터는 가져올 수 없다는 한계가 있습니다.

 

VLOOKUP 함수를 사용하는 방식은 크게 두 가지가 있습니다.

먼저 열 번호를 참조하는 방식입니다.

= VLOOKUP(찾을 기준 데이터, 불러올 데이터의 범위(공통 기준열부터), 불러올 데이터의 열 번호(공통 기준열을 1열로 했을 때), 0)

하지만 이렇게 참조하게 되면 이후 다른 데이터를 사용할 때 일일이 열 번호를 바꿔줘야 한다는 단점이 있습니다.

그럴 땐 열 번호 자리에 숫자가 아닌 열 번호가 입력되어 있는 셀을 참조하면 됩니다.

 

두 번째로는 MATCH 함수를 대입하는 방식입니다.

열 번호를 참조한다고 해도 처음에 불러올 데이터가 몇 번째 열인지 직접 세야 하는 번거로움이 있습니다.

열이 만약 500개가 있다면 시간이 굉장히 오래 걸리겠죠?

그럴 땐 MATCH 함수로 자동으로 열 번호가 계산되고 변경될 수 있도록 해야 합니다.

= MATCH(lookup_value, lookup_array, [match type])

MATCH 함수는 내가 찾고 싶은 값이 한 행이나 열에서 몇 번째에 있는지 결과를 숫자로 반환하는 함수입니다.

MATCH 함수 안에 내가 찾고 싶은 값, 내가 찾고 싶은 값이 포함된 단일 열 또는 행의 범위를 넣으면

몇 번째에 있는지 숫자로 알려줍니다.

MATCH 함수를 VLOOKUP 함수의 불러올 데이터의 열 번호 자리에 넣으면

내가 계산하지 않아도 열 번호를 바로 반환할 수 있는 것이죠.

 

INDEX 함수

=INDEX(array, row_num, [column_num])

INDEX 함수는 특정 범위에서 행 번호와 열 번호로 원하는 데이터를 불러올 때 사용하는 함수입니다.

데이터의 전체 범위, 내가 찾고 싶은 데이터의 범위 내 행 번호, 찾고 싶은 데이터의 범위 내 열 번호를 넣어

원하는 데이터를 가져올 수 있습니다.

이때, 행의 번호나 열의 번호는 데이터가 추가 되거나 삭제 됨에 따라 계속 바뀔 수 있기 때문에

INDEX 함수를 쓸 때도 MATCH 함수와 같이 사용한다면 최고의 선택이겠죠?

 

SUMIF 함수

아까 COUNTIF와 COUNTIFS 함수 기억하시나요?

조건을 걸어 데이터의 합을 구할 때도 두 가지 함수로 사용할 수 있습니다.

SUMIF 함수는 특정 조건을 만족하는 데이터의 합계를 계산할 때 사용합니다.

SUMIFS 함수는 2개 이상의 조건을 동시에 만족하는 데이터의 합계를 계산할 때 사용합니다.

보통 함수를 쓸 때는 인수1로 값을 지정하는데,

SUMIF 함수는 특이하게 인수1로 더할 조건들의 범위를 요구합니다.

이 점이 헷갈릴 수 있기 때문에 SUMIF 대신 SUMIFS 함수를 생활화하는 것을 추천하신다고 강사님께서 말씀해주셨습니다!

 

데이터 시각화

데이터를 시각화 할 때는 일단 어떤 데이터로 시각화를 할 지 결정해야 합니다.

그리고 차트를 그릴 때 이름까지 포함해서 범위를 잡는 게 추후 알아보기 편하다고 합니다.

 

강사님만의 디자인 법칙을 알려주셨는데요!

1. 같은 색으로 표현된 건 같은 계열을 나타낸다 → 계열 데이터를 회색으로 변경한다.

2. 차트에서 가장 중요한(내가 강조해야 하는) 데이터를 선정한다.

3. 차트의 메인 컬러를 1개만 선택한다.

4. 2번에서 선정한 데이터에만 3번의 메인 컬러를 적용한다.

 

데이터를 시각화 할 때 디자인을 해야 하는 이유는 무엇일까요?

그냥 예쁘니까? 아닙니다.

디자인은 하기 전보다 의미 전달이 좋을 때만 디자인을 하는 의미가 있다고 합니다.

앞으로 데이터 시각화를 할 때 이 점 꼭 기억하도록 해요 🤙

---

목요일엔 온라인 강의로 앞서 배운 내용들을 공부했고,

금요일부터는 기초 통계에 대해 배웠습니다!

기초 통계에서 어떤 걸 배웠는지는 다음 글에서 확인해주세요 😊