[MD 역량강화 프로젝트 4] 액셀을 이용한 데이터분석
마켓디자이너스의 크루 역량 강화를 위한 프로젝트 네 번째, 데이터분석을 위한 액셀 교육이 지난 1월 15일 화요일 우리의 새 보금자리인 위워크 삼성2호점에서 진행되었습니다.
이번 강의는 현재 마켓디자이너스 내 튜터링에서 COO(Chief Operating Officer)를 맡고 계신 저스틴(최영남)이 수고해주셨는데요. 저스틴 님은 액셀의 기본 원리를 이해하지 못해 액셀에 많은 시간을 비효율적으로 사용하는 사람들, 일명 엑알못(엑셀을 알지 못하는 사람들)을 위해 또는 알아두면 도움이 될 액셀의 숨겨진 기능들을 알려주기 위해 자발적으로 강의에 나섰다고 합니다.
저스틴님은 사전에 진행한 설문조사를 바탕으로 수강생들이 어려워하는 것, 꼭 배웠으면 하는 것, 엑셀을 배우는 목적, 엑셀에 대한 지식 수준을 검토하고 이에 맞춰 강의를 준비해주셨다고 합니다.
(수강생과의 소통......감동)
설문조사 중에 강사님이 특히 공감했던 의견은"유튜브를 보고 액셀로 무언가를 만들 수는 있겠으나, 만들어진 액셀의 자료를 읽어내고 분석하는 법을 알기가 어렵다" 는 애로 사항이었습니다.
이에 대한 해답은 "there is no spoon!"
강사님은 영화 '매트리스'의 명대사 "there is no spoon" 으로 답변을 대신했는데요. 현상에 집착하면 본질을 보지 못한다는 뜻입니다. 즉, 액셀도 원리를 이해해야 자료를 읽어낼 수 있음을 강조하셨습니다.
그럼 저스틴님과 함께 액셀의 원리를 이해하러 출발해볼까요?
CHAPTER1. 액셀에서 데이터란?
[ Numeric type / String type]
J: 기본적으로 엑셀에서 셀안에 정의되는 데이터는 숫자(Numeric type)와 문자(String type)가 있습니다. 다시 Numeric type에는 정수와 소수(int/decimals) , 날짜와 시각(date&time), 논리적인 옳고 그름을 따지는 (bool)이 있습니다.
정수와 소수, 날짜와 시간은 알겠는데 bool은 뭐냐구요? 액셀에서 0은 논리적으로 옳지 않은 false, 1은 논리적으로 옳은 true로 정의됩니다.
Q. 날짜, 시간도 연산이 가능한가요?
J: 네. 날짜, 시간에 대한 정보 역시 연산이 가능합니다.
1899-12-31 0:00 (1899년 12월 31일 0시 00분)
=1900-01-01 0:00(1900년 1월 1일 0시 00분)은 1이 됩니다.
=1900-01-02 0:00(1900년 1월 2일 0시 00분)은 ? 2가 됩니다.
여러분들 모두 눈치 채셨겠지만, 액셀에서 하루는 1입니다. 0시 0분을 날려버리고 싶다면, 정수를 나타내는 함수 =INT(날짜입력) 하면 됩니다.
Q. 그럼 문자도 연산이 가능한가요?
J: 네. 가능합니다. 예를 들어 액셀에서 F열의 4행에 abc가 입력되어있고, 5행에 def가 입력되있다고 가정해봅시다. 이 두 문자를 더하고 싶다면, 임의의 셀에 함숫값으로 =F4&" "&F5 를 입력하면 거짓말 처럼 그 셀은
abc def가 되어있을 것입니다. 여기서 &는 ADD의 의미이고 " "는 한칸 띄는 의미가 됩니다. (수강생들 함성, 박수)
또 길이를 세고 싶다. 하면 length함수를 이용하면 됩니다.
=len(F4)*len(F5) 를 입력하면, 9가 나옵니다. 여기서 *은 곱셈입니다.
J: 보셨듯이 어떤 종류의 데이터도 잘 다루기만 하면 가공하고 연산할 수 있습니다. 이제 데이터를 정리할 때 주의할 점을 알려드릴게요.
*알아두면 뼈가 되고 살이 될 데이터 정리 팁*
데이터를 정리 할 때 제발! 두 가지만 기억해주세요. ( feat. justin)
1. 한 열과 행에는 각각 한 종류의 데이터만 입력하기
J: 여러 종류의 데이터가 섞여 있으면 그 때부터 데이터 정리는 hell gate...
2. 셀 병합 금지
J: 같은 그룹으로 묶고 싶을 때, 우리는 보기 좋게 만들기 위해 셀 병합을 하기 쉬운데요. 셀을 병합하는 순간 논리적인 규칙이 깨지면서 데이터 정리가 불가능해집니다. 예를 들어 왼쪽 열은 숫자가 하나씩 증가하는데 그 옆에 열은 1, 4, 7, 10 이런 식으로 가는거죠. 이때부터 엑셀은 어떤 게 규칙인지 헷갈려합니다. 한 행이 증가하면, 반드시 그 옆에 행도 한 행이 증가해야합니다. 여러분의 그 욕구. 얘가 얘랑 같다고 말하고 싶은 그 마음!!!!!!!!!!! 알겠습니다. 정말 보기싫다 하시면 셀병합 대신, 글자 색만 살짝 흰색으로 바꿔주세요.
다 같이 머쓱해진 수강생들(셀병합을 습관처럼 했던 수강생들 반성의 웃음)
CHAPTER2. 함수의 종류
J: 이번에는 액셀에서 다루는 함수에 대해서 알아보겠습니다.
1. count (data!c13:c21)
데이터시트에서 c13에서 c21까지를 세 ! 라고 명령하는 함수입니다.
2. countif (data!c13:c21,"샤론")
데이터시트에서 c13에서 c21까지 샤론과 같은 문자가 몇 개인지 세 ! 라고 명령하는 함수입니다.
3. countifs(data!c13:c21,"전화",data!d13:d21,"샤론")
데이터시트에서 c13에서 c21까지 샤론과 전화가(and의 개념) 들어가는 문자가 몇 개인지 세! 라고 명령하는 함수입니다.
4. sum
범위에 있는 값을 다 더합니다.
5. sumif (조건, 더할 열)
범위 내 조건에 만족하는 값을 더합니다.
6. sumifs (더할 열, 조건a, 조건b,..., )
범위 내 제시한 조건들에 모두 만족하는 값을 더합니다.
7. if
조건문 함수입니다. 괄호안의 조건을 만족할 경우, 그렇지 않을 경우 특정 값을 정의합니다.
if(3<1, 참 일 때 천재, 거짓일 때 바보) 라고 두면 값은 바보라고 표시됩니다.
J: 여기서 잠깐 액셀에서 같다는 = 인데 다르다는 어떻게 표시할까요?
작으면서 동시에 클수는 없겠죠? 다르다는 >< 이렇게 표시합니다.
8. And
논리곱/교집합 함수입니다.
9. Or
논리합/합집합 함수입니다.
10. Is error(iferror)
만약 에러가 리턴된다면, 0이라면 해줘! 라고 명령하는 함수입니다.
= iferror(B6/E6,0)
= 0
11. now() / year()
현재의datetime/ 연도를 리턴한다.
J: 아까 말했듯이, 날짜/시간도 사칙연산이 다 됩니다. 액셀에서 하루는 1입니다.
e18= 1899.12.31.0:00
= e18+1= 1900.1.1.0:00
소수점이 보기 싫으면 int함수를 이용하세요! 정수만 반환하기 때문에 시간 정보는 나오지 않게 됩니다!
12. find
특정 문자열을 찾아줘! 라고 명령하는 함수입니다.
CHAPTER3. 실제 데이터 분석 시간
J: 다음은 실제 데이터를 가지고 분석해보겠습니다. 개인적인 정보는 지워서 가져왔습니다.
=countif(sampledata!$A:$A,A2)
sampledata시트에서 A열 전체 중에 A2랑 같은 날짜를 카운트해줘! 라는 뜻으로 달러표시는 A열을 고정시키는 표시입니다.
=countifs(sample_data!$A:$A,A2,Sample_data!,"연결실패(=2회 이상 연결 불가)")
sampledata시트에서 A열 전체 중에 A2랑 같은 날짜중에 연결실패인 경우를 카운트 해줘! 라는 뜻입니다.
수식이 잘 되어있는 한 세트만 있으면 카피앤페이스트하면 끝입니다. 반복적인 업무로 인한 시간을 상당히 효율적으로 절감해주는거죠.
J: 마지막으로 세상 귀찮은 사람들을 위한 피벗테이블입니다.
컨트롤(Ctrl)+쉬프트(Shift)하면 데이터가 정리되어 있는 것이 선택이 됩니다.
행에 날짜, 값에 총 결제금액을 클릭해볼께요.
열정리- 2차원정리할때 중복조건을 정리할 때 사용합니다.
J: 여러분 재밌게 들으셨나요? (네!!!!!!!!!!!!!!!!) 제가 실무에서 사용할 수 있는 유용한 정보만 모아왔어요. 인터넷에 물론 이보다 상세하게 설명되어있을 수도 있어요.
그런데 어떤게 중요한지 모를 정도로 너무 많은 정보가 있잖아요.
J: 그래서 제가 필요한 것만 모아온 겁니다. 지금 알려드린 것만 할 줄 안다면 실제 업무의 90%는 사용 할 수 있어요. 여러분이 데이터를 정리할 때 목적, 방향, 생각이 가장 중요합니다. 내가 어떤 목적으로 데이터를 정리하고, 어떻게 나아가야 할지 생각해야 정리가 잘 될 수 있고 그 다음에 이제 분석을 할 수 있게 됩니다. 꼭 기억해주세요. 그럼, 여태까지의 강의를 정리하고 마무리하겠습니다. 들어주셔서 감사합니다.
[ chapter1 강의 정리 ]
DATA의 타입은 크게 2가지
1. Numeric type: 숫자(int/decimals), 날짜나 시각(date&time), 논리적인 옳고 그름(bool)
2. String type: 문자(String)
→ 두 데이터 모두 잘 다루기만 하면 가공하고 연산이 가능하다.
데이터 정리할 때 주의해야 할 것
1. 한 열에는 각각 한 종류의 데이터만 입력
2. 셀 병합하지 말기
→ 논리적인 규칙을 깨지 않는 것이 가장 중요
[Chapter2 강의 정리]
함수의 종류 (count, countif, countifs, sum ···)
1. 논리만 명확하면 모두 가능
2. 심플 이즈 베스트
쉬운 논리로 분해하면 복잡한 논리도 okay
[Chapter3 강의 정리]
피벗테이블: 내가 보고 싶은 데이터만 정리해서 보여줌
컬럼: 항목
로우: 기간
값에는 숫자를. 더할 것인가?/ 셀 것인가?
* Some more tips
엑셀의 각종 에러 메세지 : https://www.excel-easy.com/functions/formula-errors.html
엑셀의 셀복사 노하우 : https://www.excel-easy.com/functions/cell-references.html
엑셀 차트 생성 노하우 : https://www.excel-easy.com/data-analysis/charts.html
액셀 강의 영상본을 공개합니다~! 음하하
*1부*
시간 - 01:22:37초
고용량 링크(6.4GB) :
https://drive.google.com/file/d/1669xnxzEGpyMmyItFx7vWnp3EbmC6pjx/view?usp=sharing
저용량 링크(3.25GB) :
https://drive.google.com/file/d/1CgYgDmOY3x1vtfSQSDn3FNGtvAGmP01G/view?usp=sharing
*2부*
시간 - 55:15초
고용량 링크(4.73GB) :
https://drive.google.com/file/d/1UBBibBn17eIzUrfXWDcyTXkvyAUzsFzc/view?usp=sharing
저용량 링크(2.18GB) :
https://drive.google.com/file/d/1ml-TmkouaK3Nl1Qf0ksyiHny50ojw8Qd/view?usp=sharing
작성자: 마켓디자이너스 인턴 Rainy (홍비)
https://brunch.co.kr/magazine/mdstory 마켓디자이너스 매거진 구독해주시면 업데이트 내용을 더욱 빨리 만나실 수 있습니다.
