영희와 현숙의 과목별 점수입니다.

둘의 평균 점수는 같지만

현숙이 영희보다 점수가 더 흩어졌습니다.

이렇게 자료가 흩어진 정도를

산포도Dispersion라 합니다.

대푯값처럼 산포도를 측정하는 수치도 여러 가지 있습니다.

제일 단순한 것은 범위Range입니다.

범위는 최댓값과 최솟값을 뺀 값입니다.

당연히 두 값 사이에 어느 자료가 어떻게 있는지

알 수 없어서 산포도를 충분히 알긴 힘듭니다.

분산Variance과 표준편차Standard deviation는

산포도 수치 중 제일 익숙할 겁니다.

편차제곱합을 자료 크기로 나눈 것이 분산인데

모집단 분산은 N으로, 포본 분산은 n-1로 나눕니다.

분산의 제곱근이 표준편차입니다.

분산은 제곱이라 단위도 제곱이 되는데,

표준편차는 자료와 단위가 같습니다.

그런데 여기가 시끄럽네요.

네, 뭐가 문제죠?

“저기 민호와 제가 자료를 분석했는데요.”

“저긴 10단위고 저는 100단위라서

제 분산, 표준편차가 더 커요.”

“제 자료는 원래 큼직해서

뒤에 0이 하나 더 붙었을 뿐인데

산포도가 다르면 불공평하죠!”

일리가 있습니다.

그래서 변동계수Coefficient of Variation는

표준편차를 평균으로 나눕니다.

무단위라서 단위가 다른 자료와 비교도 가능합니다.

산포도를 나타내는 다른 수치는

사분위범위InterQuartile Range, IQR이 있습니다.

3사분위값에서 1사분위값을 뺀 수치로

중앙 50% 값의 범위입니다.

엑셀에서 산포도 구하기

엑셀에서 분산을 구하는 함수는

VAR.P(모집단)과 VAR.S(표본)입니다.

표준편차를 구하는 함수는

STDEV.P(모집단)과 STDEV.S(표본)입니다.

사분위범위를 구하는 함수는 없지만

사분위수를 구하는 함수를 이용할 수 있습니다.

사분위수를 구하는 함수는

QUARTILE.EXC와 QUARTILE.INC가 있습니다.

이런 그래프. 어디선가 보셨을 겁니다.

이건 상자 수염 그림Box-and-whisker Plot,

일명 상자그림Box Plot입니다.

보시다시피 상자에 수염처럼 선이 위아래로 나 있군요.

상자 수염 그림은 데이터 분포를 나타내는 그래프입니다.

값이 어디에 쏠려 있는지, 흩어졌는지 알려주죠.

‘평균만 알면 되는 거 아냐?’

이렇게 생각하신 분 많으시겠죠.

이 데이터는 10명의 수치를 나타냅니다.

그런데 평균이 좀 이상하군요.

바로 수치가 아주 큰 김길동 씨 덕분입니다.

김길동 씨 때문에 10명이 고루 저만큼 가진 것처럼 보이네요.

이처럼 평균은 극단적인 값이 있으면

갈대처럼 요동쳐서 데이터를 잘 대표하지 못합니다.

상자 수염 그림은 어떻게 그릴까요?

상자 수염 그림엔 평균이 들어가지 않습니다.

차근차근 알아봅시다.

일단 최솟값과 최댓값이 필요합니다.

이건 쉽군요.

그리고 1, 2, 3사분위가 필요합니다.

이게 뭐냐고요?

간단히 말해 1사분위는 100명 중 75등 수치,

2사분위는 100명 중 50등 수치, 3사분위는 100명 중 25등 수치입니다.

이제 다섯 가지 수치를 알았으니

상자 수염 그림을 그려봅시다.

상자 수염 그림 속 직사각형이 있습니다.

직사각형 아랫변은 1사분위, 윗변은 3사분위입니다.

직사각형 안에 2사분위를 그읍시다.

사분위수 범위, IQR을 구합니다.

IQR은 3사분위 – 1사분위입니다.

3사분위보다 1.5IQR만큼 큰 수치를 구합시다.

그 수치보다 낮은 값 중 제일 큰 값에 선을 긋습니다.

1사분위보다 1.5IQR만큼 작은 수치를 구합시다.

그 수치보다 큰 값 중 제일 낮은 값에 선을 긋습니다.

두 선은 직사각형과 연결합니다.

제일 높은 선과 제일 낮은 선에도 끼지 못한 데이터들은

따로 점을 찍습니다.

‘1.5IQR이나 여유를 주었는데 거기에도 끼지 못하다니!’

이상치로 생각하는 것이죠.

(원하면 평균을 표시해도 좋습니다.)

 (아니면 그냥 최솟값과 최댓값까지 ‘수염’을

그릴 수도 있습니다.)

엑셀로 상자 수염 그림 그리기

엑셀로 상자 수염을 그릴 수 있습니다.

(2016부터)

[삽입] - [차트]에서 히스토그램이 있는 곳을 누르고

[상자 수염 그림]을 선택합니다.

*"사분위수 계산에서 중앙값 포함/제외는 뭐죠?"

중앙값 포함을 선택하면 데이터 수가 홀수일 때 중앙값을 계산에 넣습니다. 중앙값 제외를 선택하면 제외고요.

*"사분위수를 엑셀로 계산할 수 있나요?"

엑셀엔 사분위수를 계산하는 함수가 있습니다.

QUARTILE 함수가 있었는데 2016부터는

QUARTILE.EXC 함수와 QUARTILE.INC 함수가 생겼습니다.

두 함수 모두 뒤에 데이터 배열과 구할 사분위수가 들어갑니다.

예) QUARTILE.EXC(A1:A100, 1)

A1:A100의 1사분위수를 구합니다.

QUARTILE.EXC와 QUARTILE.INC는 거의 같은 함수입니다. 다만 QUALTILE.INC는 0과 4를 넣을 수 있습니다. 0을 넣으면 최솟값, 4를 넣으면 최댓값을 구합니다.

*"상자 수염 그림은 완벽한가요?"

당연히 아닙니다. 상자 수염 그림이라고 모든 데이터 분포를 요약해주진 않습니다.

예를 들어 하나는 평범한 분포고 다른 하나는 양쪽으로 흩어진 분포여도 상자 수염 그림은 같게 나옵니다.

‘한 명의 죽음은 비극이지만, 백만 명의 죽음은 통계다.’

스탈린이 남긴 말이라고 합니다.

숫자가 커질수록 우리는

현실을 수치로 정리합니다.

비겁하다고 생각할 수도 있지만,

이렇게라도 이해해야 하지 않겠습니까.

수많은 자료를 정리하는 방법 하나는

범위를 정하고 범위에 속하는 자료 수를 구하는 것입니다.

네, 흔히 이걸 도수분포표Frequency Table라고 부르죠.

도수분포표의 범위는 계급Class라고 하고요.

엑셀에서 도수분포표, 히스토그램 만들기

도수분포표를 쓰려면 계급을 정하고,

그 계급에 속하는 데이터 수를 알아야 합니다.

FREQUENCY 함수를 이용할 수 있지만

저는 [데이터] - [데이터 분석]을 추천합니다.

[데이터 분석]이 없다고요?

[파일] - [옵션] - [추가 기능]에 들어갑니다.

아래 [이동]을 누르고 [분석 도구]를 선택한 다음

확인을 누릅니다.

[데이터 분석]을 누르고 여러 메뉴 중

[히스토그램]을 고릅니다.

기능 이름이 [히스토그램]이지

도수분포표도 만들 수 있습니다.

그럼 도수분포표부터 만들어 봅시다.

[입력 범위]에는 데이터 범위를 넣습니다.

[계급 구간]을 비우면 엑셀이 자동으로 계산합니다.

[계급 구간]에 자기가 만든 목록을 넣을 수 있습니다.

[계급 구간]은 ‘미만’이 아니라 ‘이하’로 계산합니다.

10, 20, 30이 있으면

‘10 이하’, ‘10 초과 20 이하’, ‘30 초과’입니다.

맨 밑 [차트 출력]을 누르면

히스토그램을 만듭니다.

엑셀 히스토그램 차트도 있으니

더 멋지고 쉬운 것으로 고르시기 바랍니다.

(보너스)

히스토그램 계급 너비 고르기

여기 자료가 있습니다.

히스토그램을 그려 볼까요?

이런, 계급 너비가 너무 좁아서 들쑥날쑥하네요.

이건 계급 너비가 너무 넓어 값을 분류한 의미가 없습니다.

그럼 히스토그램 계급 너비는 어떻게 정할까요?

학자들은 자신만의 계급 개수 공식을 만들어왔습니다.

(계급 개수를 알면 계급 너비도 정해지므로 둘은 결국 같습니다.)

스털지스 공식Sturges' formula에 따르면

계급 개수는

 입니다. (단 n이 30 미만일 때는 부적합)

라이스 공식Rice rule에 따르면

계급 개수는

입니다.

물론 계급 개수, 너비에 정답은 없습니다.

데이터 분포를 잘 나타내는 값이라면 뭐든 좋겠지요.

지난 이야기 : 스파크라인이란?

할머니, 스파크라인 만드는 법 좀

알려주세요!

[삽입] - 스파크라인 탭에서

원하는 스파크라인을 선택하렴

1) 꺽은선형

2) 열

3) 승패(양수와 음수)

창이 나타나면

데이터 범위와 위치 범위를 선택하고

확인을 누르렴

그럼 바로 스파크라인이 나타난단다.

음. 두께도 너무 가늘고

색깔도 바꾸고 싶은데...

그럼 스파크라인을 클릭하고

위에 있는 디자인 탭을 누르렴

[데이터 편집]에서는 데이터와 위치를 다시 정하거나, 빈 셀 처리법(생략하거나 0으로 간주)을 고를 수 있다.

[종류]에서는 스파크라인 종류를 바꾼다.

[표시]는 최고점이나 최저점에 점을 찍거나 색을 달리 하도록 선택할 수 있단다.

[스타일]에서는 색과 두께 등을 고를 수 있단다.

[축]에서는 축 옵션을 바꾸고..

스파크라인을 없애고 싶다면 [지우기] - [선택한 스파크라인 지우기]를 누르렴.

엑셀 스파크라인 예제.xlsx

여기 샘플 파일이 있으니

한번 보고 원하는 색과 모양을 비교해보렴.

할머니, 여기 [삽입] 탭에 있는

스파크라인은 뭐죠?

잘 봤다.

스파크라인은 엑셀 2010부터 생긴 신기능이지

스파크라인을 한 마디로 하자면

'미니 그래프'라고 보면 된다.

셀 배경에 조그마한 그래프를

바로 집어넣을 수 있지.

스파크라인의 특징

그냥 그래프를 그리면 안 되나요?

스파크라인은 일반 엑셀 차트와는 조금 다르단다.

첫째, 스파크라인은 셀의 배경이란다.

배경이기 때문에 스파크라인이 있는 셀에

내용을 입력할 수도 있지.

둘째, 스파크라인은 명료하단다.

차트는 축과 내용 등을 이것저것 지정해야 하지만

스파크라인은 단순 변화, 양수/음수를 직관적으로 표시한다.

셋째, 스파크라인은 단순하단다.

스파크라인은 만들기도 쉽단다. 차트처럼 데이터가 바뀌면 스파크라인 그래프도 곧바로 반영하고.

셀 내용이기 때문에 인쇄에 곧바로 반영된단다.

스파크라인. 멋져 보이네요.

어떻게 만들죠?

스파크라인은 만드는 법도 간단하단다.

- 다음 화에 계속 -

안녕하세요. 예전 엑셀 할머니 12화에서는 엑셀 데이터 유효성 검사로 목록 버튼을 만드는 법을 알아보았죠.

이번 시간에는 유효성 검사를 이용해 여러 쓸모있는 기능을 직접 연습해 봅시다.

1. 범위 제한하기

  미리 셀에 들어갈 숫자 범위를 지정해 두고, 범위를 벗어난 값을 쓰면 경고 메시지가 나오게 하고 싶다면 어떡해야 할까요?

1) 일단 원하는 범위를 선택하세요.

2) 데이터 유효성 검사를 켜세요.

제한 대상을 정수로 두고 최소값과 최대값을 입력하세요.

3) 설명 메시지 탭에서는 셀에 나타날 메모를, 오류 메시지에서는 범위를 초과한 값을 쓰면 나올 메시지를 입력할 수 있습니다.

4) 이제 여기에 범위에 벗어난 값을 쓰면 경고 메시지가 뜨게 되죠.

2. 중복 입력 막기

  목록을 채우다 똑같은 내용을 두 번 쓰는 일을 막고 싶으시다고요? 이번에도 데이터 유효성 검사를 이용합니다.

1) 원하는 범위를 선택하세요.

2) 데이터 유효성에 들어가 제한 대상을 '사용자 지정'으로 정하세요.

3) 그 다음 수식창에 이렇게 입력하세요.

=countif(범위 처음:범위 끝, 범위 처음)<2

*이때 앞 범위를 쓰는 두 셀 주소는 F4를 누르거나 $를 추가해서 절대참조로 바꿔야 합니다.

*방금 쓴 수식의 뜻은 '범위 내에서 앞으로 쓸 내용은 등장횟수가 2 미만이어야 한다'는 뜻입니다.

4) 실제로 범위에 같은 값을 입력하면 경고 메시지가 뜹니다.

3. 글자 수 제한하기

  주민등록번호, 제품 번호, 학번 등 긴 숫자들로 목록을 구성하다 보면 꼭 한 글자를 더 넣거나 덜 넣게 되죠. 이렇게 글자 수가 다를 때 엑셀에서 알려 준다면 얼마나 좋을까요?

1) 범위를 선택합니다.

2) 데이터 유효성 검사에 들어가 제한 대상을 '사용자 지정'으로 정합니다.

3) 수식을 입력합니다. 이때 범위에 최소한 하나라도 내용이 있어야 합니다.

=len(범위 시작 셀)=원하는 글자 수

4) 이제 글자수를 다르게 쓰면 경고창이 뜹니다.

4. 잘못된 데이터 찾기

  이미 목록을 완성했는데, 잘못 쓴 셀이 어디 있는지 알고 싶어도 유효성 검사를 사용할 수 있습니다.

1) 원하는 줄을 범위 선택합니다(위 사진은 기준목록에 없는 내용을 찾기 위함)

2) 데이터 유효성 검사에 들어가 원하는 기준(글자 수, 범위, 목록...)을 입력합니다.

(위 사진에서는 셀에 들어갈 목록을 입력했습니다)

3) 데이터 도구 - 데이터 유효성 검사 - 잘못된 데이터를 누릅니다.

4) 아까 설정한 기준에 맞지 않는 셀에 붉은 동그라미가 그려집니다. 내용을 기준에 맞게 고치거나 데이터 유효성 검사 - 유효성 표시 지우기를 누르면 원은 사라집니다.

엥? 뭐야, 여기가 비었잖아!

민호야, 엑셀은 프로그램이다.

프로그램에 소리쳐 봤자란다.

여기 보세요.

원금과 지속 년수, 최종 금액까지 다 있는데

이자율만 없어서 계산을 못 해요.

이걸 풀려면 지수가 미지수인 방정식을 풀어야 하는데...

그러지 말고 목표값 찾기를 이용해 보면 어떻니?

목표값 찾기요?

목표값 찾기란 엑셀에서 제공하는

일종의 방정식 답 찾는 프로그램이란다.

식과 결과물이 있으면

'이 식으로 이 결과물이 나오려면 무슨 값이 필요할까?'를 구하는 거지.

지금 엑셀 표를 보니

금액을 구하는 식과 최종 금액은 나와 있구나.

데이터 리본에 들어가

가상 분석을 클릭해

'목표값 찾기'를 클릭하렴.

세 가지 값이 나왔어요.

수식 셀, 찾는 값, 값을 바꿀 셀이요.

자, 차근차근 해 보자.

수식 셀은 식을 적은 셀이야.

찾는 값은 수식 셀이 되어야 할 값이야.

우린 최종금액이 500만원이길 원한다고 하자.

값을 바꿀 셀은 우리가 알고 싶은 값이야.

바로 이자율이 있는 셀이지.

이 셋을 모두 입력하고 확인을 누르면

엑셀은 온갖 값을 넣으면서 최종금액이 500만이 되게 만든단다.

봐라. 금방 찾지 않았니.

이자율은 약 8.38%구나.

할머니, 엑셀로도 로그함수를 쓸 수 있죠?

당연하지. 문과도 로그 함수는 배우지 않니?

로그에도 종류가 있었죠?

아무 숫자 없는 로그랑, LN으로 쓰는 로그가 있었는데...

로그 중에서 밑이 10인 로그는 상용로그,

밑이 자연상수 e인 로그는 자연로그라고 하지.

혹시 필요할지 모르니

로그 법칙도 알려주마.

그런데 왜 로그라고 부르죠?

수학자 존 네이피어가

logos(비율)과 arithmos(수)를 합쳐서 만든 logarithm에서 유래했단다.

그건 그렇고,

빨리 엑셀 로그함수를 알려주세요.

엑셀 로그함수는 말 그대로

LOG란다.

= LOG(수, 밑)

* 밑 생략 시 10

일반적으로 LOG를 쓸 때는 밑 다음에 수를 쓰지만

여기서는 밑이 나중에 오니까 헷갈리지 마렴.

자연로그는요?

자연로그는 LN이란다.

= LN(숫자)

자료들이 여러 줄 있을 때,

가나다순으로 어떻게 정렬하죠?

정렬 자체는 아주 쉽단다.

원하는 범위를 드래그하고

'데이터' 리본에 있는 '정렬 및 필터' 부분을 찾으렴.

ㄱ과 ㅎ과 화살표가 보이지?

보이는 대로 위는 가나다순(오름차순)이고

아래는 가나다 역순(내림차순)이란다.

그런데 이렇게 정렬을 누르면

한쪽 줄만 바뀌고, 옆 데이터는 그대로지 않나요?

그래서 요즘은 한 줄만 드래그하고 버튼을 누르면

선택 영역을 확장할지 말지를 고를 수 있게 해 뒀단다.

만약 선택 영역을 확장하면 옆 줄에 있는 데이터도 같이 움직이지.

음...

왜 그러니?

석연치 않은 구석이 있니?

만약에 정렬을 두 번 하고 싶으면 어떡하죠?

예를 들어서 이 데이터를 보세요.

아까처럼 첫 줄을 가나다순으로 정렬해도

두 번째 줄이 알파벳순으로 정렬되지는 않잖아요.

그럴 때도 정렬은 가능하단다.

아까처럼 데이터를 선택하고 똑같이

'데이터' 리본에 가보렴.

가나다순 정렬 버튼 옆에 '정렬'이 있지?

이곳 정렬 메뉴에서는 여러 기준들을 만들고,

그 적용 순서를 조절할 수 있단다.

네가 하고 싶은 건, 먼저 첫 줄을 가나다순으로 정렬하고,

그 다음 둘째 줄 알파벳을 알파벳 순으로 정렬하고 싶다는 거지?

맞아요.

그럼 일단 첫 기준부터 정하자꾸나.

정렬 기준에서 정렬할 열을 선택하고,

정렬 기준은 값으로 한 다음 정렬은 오름차순으로 하면 되겠지.

그 다음 '기준 추가'를 눌러서 기준을 하나 더 만들자꾸나.

다음 기준은 두 번째 열을 고르고,

역시 정렬 기준은 값, 정렬은 오름차순으로 하자.

만약 기준 적용 순서를 바꾸고 싶다면

기준을 누르고 위 화살표 버튼을 누르렴.

기준을 올리고 내려서 순서를 바꾼단다.

짜잔! 이제 같은 가나다순 안에서도 알파벳이 알파벳 순으로 정렬되었지.

안녕하세요. 오늘은 엑셀에서 텍스트를 이어붙이는 법을 배워 봅시다.

엥? 이미 &로 텍스트를 이어붙이는 법을 말하지 않았냐구요?

물론 &를 이용하면 쉽게 텍스트를 붙일 수 있습니다.

하지만 다른 방법, 특히 엑셀 함수로 텍스트를 붙일 수 있어요.

바로 CONCATENATE라는 함수입니다.

CONCATENATE에는 '잇다'라는 뜻이 있습니다.

말 그대로 내용을 이어버리는 함수죠.

바로 예를 들어 볼까요.

기업 년도별로 수익률이 있군요.

이제 결과로

"XX기업은 YY년에 ZZ수익률을 기록"이라는 글을 쓰고 싶다고 합니다.

기업 이름, 연도, 수익률을 베껴 써도 되지만, 여긴 엑셀입니다.

이렇게 해 보세요.

=CONCATENATE(셀 주소, "기업은 ", 셀 주소, "년에 ", 셀 주소 , "퍼센트 수익률을 기록")

보세요. 금방 텍스트가 나타났죠?

이처럼 CONCATENATE 함수는 쉼표로 구분한 셀이나 텍스트, 숫자를 하나로 이어준답니다.

참!

띄어쓰기를 하고 싶으시다면, 큰따옴표 사이에 칸을 비워 두세요.

" "

이렇게요.

그리고 텍스트를 쓰실 때 큰따옴표를 넣는 것을 잊지 마세요.