BDA 5기_워크시트_이건우_220602

1주차 온라인강의 학습범위(클립번호 합산기준): Excel기초1~30(220530~220602)

1. 데이터분석

데이터분석은 유용한 정보를 발굴하고 결론을 도출해서 의사결정을 지원하기 위해 데이터를 정리하고 변환해서 모델링하는 과정으로 현실에 살고 있는 우리를 더 나은 미래로 나아가게 하는 가장 강력한 도구이다.

1-1 데이터 분석기법을 활용한 인사이트 도출절차

과거 데이터를 분석해서 현실의 문제를 해결할 수 있는 방법을 모색하고 현실상황을 반영해서 어떻게 문제를 해결할 것인지 원인을 파악하고 해결방안을 모색해서 현실과 미래의 차이(GAP)를 줄이게 된다.

1-2 데이터분석의 목적

이러한 목적은 문제를 해결하고 현재를 개설시킬 수 있는 인사이트를 도출하기 위해서다.

다양한 데이터 -> 분석기법 활용 -> 문제발견 -> 원인파악 -> 해결방안모색 -> 인사이트도출

2. 데이터사이언스

데이터분석프로세스를 체계화하여 학문으로 발전시킨분야인데 다양한 학문이 융합되어 새롭게 탄생한 융합학문이다.

Computer Science/IT+Domains/Business Knowledge+Math and Statistics=Data Science

2-1 데이터사이언스분야에 필요한 인재?

예) 데이터분석이 가능한 역사학자, 역사학을 알고 있는 데이터분석가

-> 4차산업혁명시대에서 우리의 목표-데이터분석을 할 수 있는 직무전문가

3. 엑셀을 배워야 할 이유?

3-1 개인적인 관점에서의 이유-나 자신을 위해 어떻게?

엑셀을 잘하면 업무를 빠르고 정확하게 수행할 수 있고, 일에 대한 자신감을 바탕으로 즐겁고 행복한 회사생활을 누릴 수 있게 된다.

3-2 엑셀을 잘 한다는 것은? 만약 어떻게 하면 엑셀을 잘 할수 있을까?

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

즉, 사용자가 누구인지 원하는 정보가 무엇인지 이 자료가 왜 필요하며 어떤 관점을 가지고 있는지 Output(출력)의 결과가  무엇인지 MECE, Visualization, 간략하고 쉽게(Simple&Easy) 구성한다는 뜻이다.

4. 엑셀의 기본원리

4-1 엑셀데이터의 형태와 붙여넣기(Ctrl+V=Paste all)

4-1-1 엑셀데이터의 종류-엑셀에서 입력할 수 있는 데이터

값-텍스트:문자데이터(계산X), 숫자:숫자데이터(계산O) -> 왼쪽정렬이 문자데이터라면 오른쪽정렬은 숫자데이터

수식-계산식:사칙연산/논리연산, 함수:데이터를 입력하면 특정결과 출력

서식- 글꼴 서식:글꼴색.크기,굵기, 기울임, 밑줄 셀서식:셀배경색, 셀테두리

4-1-2 우리가 반드시 기억해야 할 엑셀의 기초

엑셀의 모든 셀에는 서식이 적용되어 있어 복사(Ctrl+C) 혹은 붙여넣기를 할 때 수식/값/서식을 각각 따로 붙여 넣는다.

단순히 붙여넣기를 하면 값과 수식 및 서식을 모두 포함해서 붙여넣게 된다.

수식붙여넣기-서식을 제외한 수식 그대로

값붙여넣기-서식을 제외하고 수식을 값으로 변경

4-2 엑셀의 참조

엑셀의 행은 상하로 열은 좌우로 이동(변경)하며 함수를 사용하는 상황에 따라 각각의 참조를 적절하게 사용해야 한다.

4-3 빠른실행도구모음으로 업무속도 올리기

빠른실행도구모음 등록 추천기능 7가지

합계, 수식붙여넣기, 값붙여넣기, 서식붙여넣기, 화면에 보이는 셀 선택, 틀고정, 병합하고 가운데 맞춤 등

빠른실행도구모음으로 서식에 맞는 붙여넣기를 할 수 없을 때 선택하여 붙여넣기를 사용

4-3 엑셀의 함수

복잡한 수식이나 계산을 간단한 명령어로 사용할 수 있게 하는 도구로 함수에 데이터를 입력하면 결과값을 출력한다

4-4 엑셀의 단축키(Windows기준)

빠른실행도구모음 단축키: Alt+1,2,3...

4-5 표시형식의사용자지정구문

양수일때: 음수일때: 0일때 문자일 때

[빨강]#,##0; [파랑]#,##0; 0; "수치확인"

가운데 따옴표는 세자릿수를 구분할 수 있으며, 오른쪽에서 두개는 소숫점을 설정할 수 있다.

서식의 형태를 지정할 수 있다.

범주를 사용자가 직접 지정할 수도 있다. 파란색에 표시된 것은 5000이상이면 빨간색을 적용하고 2000이상이면 파란색을 적용해서 나머지는 색상을 변화시키지 않도록 설정했다.

셀서식의 표시형식을 설정해서 적용했더니 5000이상이면 빨간색을 적용하고 2000이상이면 파란색을 적용해서 나머지는 색상을 변화시키지 않는 결과가 확실하게 적용되었다.

5. 탐색적 데이터 분석(EDA)

5-1 탐색적데이터분석의 개념

기존의 통계학이 정보의 추출에서 가설검정 등에 치우쳐 자료가 가지고 있는 본연의 의미를 찾는데 어려움이 있어, 이를 보완하고자 주어진 자료만 가지고도 충분한 정보를 찾을 수 있도록 하는 탐색적 자료분석방법

5-2 탐색적데이터분석의 중요성

• 내 입맛대로 수집되는 데이터는 1도 없다
• 수집된 데이터의 특성 및 분포를 파악
• 결측치, 이상치의 존재유무 파악
• 탐색적데이터분석결과로 분석에 필요한 데이터전처리 수행
• 가장 적합한 분석방법 결정가능

-> 즉, 탐색적데이터분석을 잘 수행해야 전반적인 데이터분석도 잘 수행할 수 있다.

5-3 데이터의 형태파악

데이터리터러시실습에서 수행했던 데이터의 형태 및 종류를 파악하는 작업

[Product Name]

데이터유형: 문자데이터

데이터개수: 20개

데이터계급종류: BushSomerset...-CiscoSPA

데이터계급개수: 20개

계급별 데이터 개수: 계급당1개

5-4 데이터분석도구를 활용한 기초통계량 계산(Excel)

데이터분석도구는 엑셀에서 다양한 통계적 데이터분석을 수행할 수 있는 추가기능

예) Sales의 표본 9723개를 범위로 잡아서 데이터분석에서 기술통계법으로 아래처럼 구현한 수치로 나타냈다. 

Sales(매출) 평균 231.9182 표준 오차 6.377604 중앙값 54.96 최빈값 12.96 표준 편차 628.8654 분산 395471.7 첨도 302.4468 왜도 12.94329 범위 22638.04 최소값 0.444 최대값 22638.48 합 2254940 관측수 9723

 

Sales를 계산한 값으로 SUM은 합계, AVERAGE은 평균, MAX은 최대값, MIN은 최소값, MEDIAN은 중앙값, MODE은 최빈값, VAR은 분산, STDEV는 표준편차이다.

5-5 엑셀에서 가장 파워풀한 도구

피벗테이블은 커다란 표의 데이터를 요약하는 통계표

피벗테이블 완성 예시2

피벗테이블을 생성하는 창에서 셀에 표시된 범위 전부를 잡은 후 배치할 시트위치를 선정하게 된다. 새 워크시트는 새로운 시트에 배치하고 기존워크시트는 기존에 만들어 둔 시트의 위치를 선정해서 배치하게 된다.

피벗테이블을 완성하면 테이블필드에서 카테고리, ID, 날짜, 금액, 지역 등을 필터필드에서 필요한 것만 보여주거나 열과 행방향필드으로 어떤 것을 보여주거나 합계 외7가지를 값필드에서 보이게 할 수 있다.

5-6 산점도와 BOXPLOT

데이터분포를 나타내는 산점도는 데이터를 점으로 표현해 흩어져있는 분포를 파악

이 차트의 제목은 Super Store Sales+Quantity이며 x값은 Sales로 금액을 y값은 Quantity로 할인하는 물량이다. 이 그래프는 초록색 산점도로 데이터를 표현한 것인데 이를 분석한 결과 x값은 $1000부터 $4000까지 y값은 0부터 100까지 수치가 확인되었는데 빨간색 추세선으로 표시했더니 물량이 많을수록 금액

 

서식 창에서 점표식과 추세선은 테두리만 제거하고 차트창은 색상의 테두리와 채우기를 되도록 하지 않도록 함. 

데이터를 요약하는 BOXPLOT은 최소값, 제1사분위수, 중앙값, 제3사분위수, 최대값을 활용해서 그리는 그래프로 이상치유무파악에도 활용

6. 데이터전처리

데이터분석프로세스 중 가장 중요한 단계로 데이터의 분석목적과 방법에 맞게 데이터를 가공 또는 처리하는 과정

6-2 데이터전처리의 중요성

데이터분석과정 중 가장 많은 시간과 비용이 필요한 과정(60~80%

예) 쓰레기가 유입되지만, 쓰레기도 버린다.

6-3 데이터카테고리분류하기

6-3-1 IF함수

부등호/등호(<,>,=)를 활용해서 조건을 가정하고 데이터를 조건에 만족하는 값과 그렇지 않은 값으로 분류

국어점수가 90점 이상이면 합격, 그렇지 않으면 불합격으로 분류

IF함수를 중첩하는데 2번이면 3가지 데이터로 구분, 3번이면 4가지 데이터로 구분된다.

-> IF함수를 N번 중첩하면 N+1가지 데이터로 구분한다.

예) 백화점의 고객매출이 $5000이상이면 Platinum $2000, $2000이상이면 Gold, 나머지는 Silver

6-3-2 VLOOKUP함수

공통기준열을 기준으로 n번째에 있는 데이터를 불러오는 함수

사용예시) VLOOKUP($D5, 직원정보LIST!$D:$J, 3, 0)  # 0(FALSE)=정확히 일치할 때만, 1(TRUE)=근사차여도

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

열번호 참조 참조

col_index_num에 숫자가 아닌 열번호가 입력 된 셀 참조는 복사하거나 붙여넣으면 열번호가 자동으로 변경된다.

6-3-3 INDEX함수와 MATCH함수

INDEX함수로 아래 이미지의 RAW시트에 있는 전사손익계산서에서 표 전체를 잡고 MATCH함수로 하나는 매출액에 해당하는 범위를 찾는데 표 전체를 참조하고 또 하나는 1월에 해당하는 범위를 찾는데 표 전체를 잡으면 매출액을 구할 수 있다.

전사손익주요지표를 구하는데 필요한 전사손익계산서

매출액을 구한 값을 기반으로 수식을 모두 붙여넣기해서 자동으로 계산한 값을 채워넣기

6-4 데이터 전처리 함수

6-4-1 SUMIF함수

특정조건(2개이상의 조건)을 (동시에)만족하는 데이터의 합계를 계산해서 자동손익비교장표를 만들수 있다.

예) SUMIF($C$5:$C$35, $D$5:$C$35, "2030년", $C$5:$C$36, "매출액")

                 # 더할값들의 범위, 더할조건범위1,  조건1,     더할조건범위2,    조건2

6-4-2 SUMIF함수와 SUMIFS함수의 차이

전자는 조건이 1개일 때와 후자는 조건이 2개이상일 때인 것으로 알고 있을 것이지만...

사실 SUMIFS함수는 이렇게 쓴다.

예) 영업a팀 1월매출액구하기

=SUMIFS('RAW2020(실습)'!$K:$K,'RAW2020(실습)'!$C:$C,'PL2020(실습)'!D$51,'RAW2020(실습)'!$G:$G,'PL2020(실습)'!$B52,'RAW2020(실습)'!$J:$J,'RAW2020(실습)'!$N$3)

해석하면 -> (금액전체,월전체,"1월",중분류전체,"매출액",팀전체,"영업a팀")

7. 통계적 데이터 분석

7-1 통계학

관심의 대상이 되는 집단(모집단)의 특성을 파악하기 위해 모집단으로부터 일부의 자료(ㅛ본)을 수집, 정리, 요약, 분석해서 표본의 특성을 파악한 후 이를 이용ㅎ해서 모집단의 특성에 대해 추론하는 원리와 방법을 배우는 학문이며 일부분으로 전체를 파악하려는 노력의 일환

[모집단]->[표본(일부분)]->[추론]"이 도형은 빨간 원일거야"=>통계학은 완벽하지 않다.

7-2 기초통계상식

우리는 일을 할 때 모두 통계학을 사용

• 기술통계학: 요약통계량. 그래프,표 등을 이용해서 데이터를 정리, 요약하여 데이터의 전반적인 특성을 파악하고 그래프를 활용해서 데이터를 시각적으로 표현하고 통계량 등으로 수치를 요약
• 추론통계학: 데이터가 모집단으로부터 나왔다는 가정하에 모집단으로부터 추론을 파악
• 가설검정: 모집단에 대한 가설을 설정하고 표본을 관찰해 가설의 채택 또는 기각의 결정을 내리는 것

가설의 종류

• 귀무가설(제1종오류):기존의 알려진 사실을 간단하면서도 구체적으로 표현
• 대립가설(제2종오류):실험자가 사실임을 입증하고자 함

유의확률(P-Value)

귀무가설이 맞다는 전제하에, 표본에서 실제로 관측된 통계치와 같거나 더 극단적인 통계치가 관측될 확률.

대립가설기각사유

-> 대립가설이 맞다는 걸 증명하는 통계치가 나올확률 80%, 대립가설이 틀리단 걸 증명하는 통계치가 나올 확률20%

예) 가공식품을 만들기 위한 돼지 10000마리의 체중분포

-> 귀무사설의 결과 돼지10000마리의 평균체중은 100kg으로 귀무가설은 참일 수도, 거짓일 수도 있다.

이를 위해 귀무가설이 참인지 증명하기 위해 무작위로 돼지100마리를 선정해 평균체중 측정

-> 귀무가설이 참이면(평균 100kg이면), 100마리씩 여러 번 추출한 표본들의 확률도 100kg안팎으로 나올 가능성이 있음.

우리가 추출한 표본의 평균이 75kg일 때, 귀무가설이 참이면(평균 100kg이면), 다음 추출한 표본의 평균이 75kg과 같거나 클 확률이 높아야 함.

만약 모평균이 100kg이면 평균 75kg이상인 표본도 많이 나타나야 한다.

7-3 t-test(t-검정)

두개 집단의 평균에 통계적으로 유의미한 차이가 있는지 검정해서 우리 회사의 신약이 질병치료에 유의미한 효과가 있는지 검증

f-검정: 두 집단의 등분산성(분산이 같은지 여부)을 검정

-> P-value가 0.05보다 크면 두 집단의 분산이 같고, 0.05보다 적으면 두 집단의 분산이 다름.

귀무가설의 경우 일반적으로 인정되는 사실로 두 집단의 분산은 같지만 두 집단의 평균에 유의미한 차이가 없다.

대립가설의 경우 우리가 주장하는 가설로 두 집단의 분산이 같지 않지만 두 집단의 평균에 유의미한 차이가 있다.

7-4 회귀분석

두개 이상의 연속형변수(수치)인 종속변수와 독립변수 간의 관계를 파악해서 매출액에 가장 큰 영향을 주는 요인이 무엇인지 분석   y=ax+b

종속변수y: 우리가 알고 싶은 값으로 광고비에 의한 매출변화를 알고 싶을 때 파악한 매출액

독립변수x: 우리가 알고 있는 값으로 광비에 의한 매출변화를 알고 싶을 때 지출한 광고비

회귀분석의 목적: 두 변수간의 관계파악해서 미래값 예측

회귀분석의 종류

회귀모형의 모양에 따라 선형회귀분석과 비선형회귀분석으로 구분

독립변수의 개수에 따라 단순회귀분석과 다중회귀분석으로 구분

단순선형회귀분석: 독립변수가 변할 때, 종속변수값이 어떻게 변하는지를 가장 잘 설명해주는 직선을 찾아 직선이 x와 y의 관계를 얼마나 설명하는지 분석하는 방법-> y와 x사이의 1차방정식 구하기

-> 점의 분포와 추세선의 차이의 크기와 오차의 차이는 비례

단순선형회귀분석의 평가와 해석을 할 때 결정계수/F값/y절편 및 x1의 계수 확인

다중선형회귀분석: 여러개의 독립변수(x1,x2,x3...)가 종속변수(y)값에 미치는 영향을 파악해서 매출액에 가장 큰 영향을 주는 요인이 무엇인지 확인-> 조정된 결정계수/F값/Y절편 및 X1의 계수확인

7-5 상관분석

상관계수: 두변수사이의 상관성을 나타내며 일반적으로 피어슨상관계수를 사용

상관계수표: 분석대상변수들의 상관관계를 한눈에 보여주는 표

주요지표간 상관계수를 나타낸 표로 1에 가까울수록 색이 진해지고 0에 가까울수록 색이 연해진다.

마무리

엑셀자료를 다른사람에게 전달할 때 지켜야할 사항을 준수하면 내가 먼저 남을 배려해야 남도 나를 배려한다.

• 마우스커서는 항상A1에(문서의 가장위)
• 필요한 경우 구분자를 중심으로 틀고정
• 시트(sheet)이름 반드시 변경
• 시트가 많을 경우(10개이상) 빈 시트로 구분
• 사용자가 보기 좋은 비율설정(75~100%)

이번 강의의 목표? -> 3-2 참고

"비를 느끼는 사람"

"영영 사람들은 비를 느끼고, 다른 사람들은 그냥 젖는다."