어디 보자. 동아리 MT도 끝났으니

비품비용을 계산해야지...

그러고 보니 4학년 선배는

엑셀에 체크박스를 집어넣으셨던데..

체크박스에 체크하면 체크한 물품만

가격이 계산되었지..

할머니! 할머니!

후후. 잘 했다 민호야.

할머니를 부른 거요?

아니. 스스로 궁금한 것을 찾는 태도 말이다.

할미 시절에는 시키는 것만 잘 해도 됐지만

요즘 시대는 자기가 알아서 찾아야지.

엑셀에 체크박스는 어떻게 만드나요?

일단 체크박스를 만들려면

엑셀에서 [개발 도구]라는 리본을 찾아라.

어?

개발 도구가 없는데요?

없다면

파일 > 옵션 > 리본 사용자 지정에 들어가서

체크 표시를 넣으면 생길 거다.

좋아요. 생겼어요.

체크박스는 그 리본 속

'삽입' 메뉴에 있단다.

양식 컨트롤과 ActiveX 컨트롤에 둘 다

체크박스가 있는데요? 어느 쪽을 고르죠?

지금은 양식 컨트롤에 있는 걸 골라라.

ActiveX 컨트롤은 고수용이야.

체크박스를 클릭하고

원하는 셀에다 누르면 박스가 생긴다.

벌써 뭔가 있어 보이는걸요?

아직 멀었단다.

박스를 오른쪽 마우스로 클릭해 보겠니?

'텍스트 편집'에 들어가면

박스에 붙은 텍스트를 바꿀 수 있단다.

이번에는 거추장스러우니 텍스트를 없애보렴.

그리고 자동 채우기로 체크박스를 늘리렴

참고로 지금 늘리지 않고 나중에 가면

곤란해진단다.

그 다음 '컨트롤 서식'에 들어가서

아무 셀이나 정해서 셀 연결을 해 보렴.

연결한 셀은 체크하면 TRUE라고 뜨고 안 하면 FALSE라고 뜨네요.

이제 이 박스는 저 셀에 '연결'된 거란다.

이 상태로 자동 채우기를 써서

박스를 늘리면 모두 한 셀로 연결되니까 귀찮아져요.

그래서 연결 전에 박스를 늘리라고 말한 거란다.

TRUE와 FALSE가 생긴 건 알겠어요.

그런데 이걸로 어떻게 선택한 가격만 합치죠?

여기서 나오는 함수가

바로 SUMIF 함수란다.

AVERAGEIF 함수 기억나니?

네. 조건에 맞는 수들로만

평균을 내는 함수였죠.

(지난 포스팅 참고)

SUMIF 함수는 조건에 맞는 수들만

합계를 내는 함수란다.

SUMIF 함수에는

첫째. 기준이 될 범위

둘째. 기준

셋째. 기준에 맞게 합을 낼 범위가 들어간단다.

=SUMIF( 범위 , TRUE , 비용범위)

기준이 될 범위는 TRUE와 FALSE가 있는 곳

기준은 TRUE

합을 낼 범위는 가격이 적힌 곳으로 해 봐라.

와. 체크한 곳만 숫자가 더해지네요.

나중에 체크를 바꿀 수도 있고요.

체크표시는 참 좋은 거란다.

봐라. 얼마나 멋지니.

게다가 클릭만 하면 계산이 달라진다니.

깔끔하고 보기 좋은 기능이야.

할머니는 엑셀이 좋으세요?

좋다마다.

할미는 저승에서 숫자놀음이

제일 좋아요.

민호한테는 말 안 했지만,

알고 싶은 사람들이 있을까 봐

할미가 따로 수업을 준비했어요.

예전 글에서 봤다시피

날짜에서 요일을 알고 싶을 때는

TEXT 함수를 이용하는 것이 제일 쉽다고 했어요.

WEEKDAY함수는 쓰기가 조금

복잡해서요.

하지만 WEEKDAY도 엄연한 엑셀 함수.

알아둬서 나쁠 건 없겠지요.

WEEKDAY 함수란?

WEEKDAY 함수는 날짜에 해당하는 요일을 반환하는 함수예요.

요일은 기본적으로 1(일)부터 7(토)까지의 정수 형태로 반환하죠.

WEEKDAY 함수 첫 부분에는 날짜 일련번호를, 두 번째에는 반환유형을 넣어요.

두 번째는 생략 가능하고요.

날짜 일련번호

◇ 일련번호는 1900년 1월 1일부터 1이예요. (그 전 날짜는 쓸 수 없어요)

◇ 날짜 셀로 입력할 수 있답니다.

반환 유형

◇ 반환 유형값은 어느 요일을 1로 할지 정하는 곳이라고 보면 돼요.

◇ 생략하면 1이 일요일이 된답니다.

반환 유형값에 따른 반환 유형

1 (아니면 생략) : 1이 일요일

2 : 1이 월요일

3 : 0이 월요일 (일요일이 6)

11 : 1이 월요일

12 : 1이 화요일

13 : 1이 수요일

14 : 1이 목요일

15 : 1이 금요일

16 : 1이 토요일

17 : 1이 일요일

CHOOSE 함수와 같이 요일 표시하기

보시다시피 WEEKDAY 함수는  반환값이 정수라서 불편하죠.

이때 WEEKDAY 함수와 함께 쓰는 함수가 CHOOSE함수입니다.

CHOOSE함수는 입력한 인수에 따라 미리 만들어준 목록에 맞는 값을 반환합니다.

CHOOSE 함수 첫째 인수는 입력값입니다. 두 번째 인수는 입력값이 1일 때 반환할 값, 세 번째 인수는 입력값이 2일 때 반환할 값...이죠. 반환할 값은 254개까지 설정 가능합니다. 지금은 7개면 족하지만 말입니다.

=CHOOSE(WEEKDAY(셀주소), "일요일", "월요일", "화요일", ... , "토요일")

CHOOSE 함수 안에 WEEKDAY 함수를 중첩시킨 다음,

각 값에 맞게 반환할 요일 텍스트를 써넣읍시다.

WEEKDAY 함수 반환유형을 정하지 않았으니 1이 일요일이겠죠.

텍스트에 큰따옴표("") 붙이는 거 잊지 마시고요.

보세요. 요일이 나왔죠.

네이버 달력과 비교해 봅시다.

요일이 일치하는군요.

자동 채우기로 나머지 날짜들의 요일도 한 번에 알아낼 수 있죠.

도움이 되셨나요?

앞으로도 많은 엑셀 이야기를 남길 테니

즐겨찾기와 덧글 부탁드려요.

할머니, 할머니?

뭐냐, 민호냐?

설날인데 고향에 안 가고.

저야 공부를 좋아하니까요.

남들 쉴 때 쉬어야 성공하는 사람이죠.

진짜 부자들은

남들 쉴 때 쉬고

남들 일할 때도 쉬는데 말이다.

뭐... 어쩔 수 없죠 그건.

그건 그렇고

왜 불렀니?

이번에 신입생을 위해서

과 스케줄표를 작성하기로 했어요.

날짜별로 요일을 쓰려고 하는데,

달력에서 일일이 찾기가 귀찮아서요.

그래서, 날짜만으로 요일을 알아내는 함수가

있는지 궁금했구나.

네, 혹시 있나요?

날짜로 요일을 만드는 방법은 물론 있지

함수는 조금 다르지만.

아, 혹시 WEEKDAY 함수 말씀이신가요?

함수 이름부터 요일이잖아요.

WEEKDAY함수는 날짜에서 요일을 반환하는 함수지

정확히 말하면 요일을 숫자로 반환한단다.

바로 '월요일'이 튀어나오지는 않아.

게다가 날짜를 그대로 넣지도 못해.

왜 그렇게 복잡하죠?

엑셀도 컴퓨터도 결국은 숫자놀음이야.

모두 숫자로 치환해야 계산하기 쉽거든.

아무튼 Weekday 함수는 지금 볼 필요는 없단다.

다른 함수가 있나요?

내가 하라는 대로 해 봐라.

날짜 옆에

=TEXT(셀 주소, "aaaa")라고 써 보는 거다.

어?

요일이 나타나는데요!

TEXT 함수는 원래

셀 서식을 적용하는 함수다

방금 쓴 수식은

'셀 안에 있는 날짜를 요일서식으로 바꿔서 표시하라'는 말이야.

오. 신기한데요.

'월요일', '화요일'... 대신에

'월', '화'... 로 쓰고 싶으면

"aaa"라고만 해라.

다음 화에 계속...

레포트도 이제 끝.

어릴 땐 계산기로 다 두드렸는데

지금은 엑셀이 있으니까 편하네...

똑똑. 할미다.

할머니! 올림픽 개막식 보셨어요?

그럼, 봤다마다.

저승에도 텔레비전은 있단다.

귀신이시면

직접 가실 수도 있지 않나요?

처음 저승에 가서 운동경기를

얼마나 많이 봤는지 아니?

젊은 것들이 좋아하는

챔스 결승전도 가 봤어요.

이제 할미는 그런 거 지겨워.

아무튼 할머니가 오셨으니

오늘 엑셀도 안심이네요.

음. 평균과 분산, 표준편차라.

평균이야 다 할 줄 알테고.

분산과 표준편차가 뭔지는 알지?

그럼요. 문과도 그건 배우거든요.

분산과 표준편차는 자료가 얼마나

흩어졌는지 알려주는 '산포도'의 일종이에요.

각 자료에서 평균을 빼서 제곱합을 구하고

데이터 개수만큼 나눈 것이 분산,

분산의 제곱근이 표준편차죠.

분산과 표준편차가 클수록

그 자료들은 많이 흩어진 거죠.

역시 똑똑한 내 증손주야.

그런데 데이터 개수(n)로 나눌 수도 있고

n-1로 나눌 수도 있다는 것 아니?

글쎄요?

고등학교에서는 n으로 나누라고 배웠는데.

조사에는 두 종류가 있단다.

데이터를 전부 조사한 조사(전수조사)와

일부만 조사한 조사(표본조사)가 있지.

전교생이 100명인 학교에서

100명한테 전부 물어보면 전수조사,

10명만 뽑아 물어보면 표본조사

이런 건가요.

맞아.

그리고 전수조사나 표본조사냐에 따라

n이냐 n-1이냐도 다르단다.

전수조사일 때는 n으로 나누고

표본조사일 때는 n-1로 나누는 것이 자연스럽단다.

왜죠?

음. 설명하려면

자유도와 불편추정량을 이야기해야 하는데..

지금은 일단

표본조사는 전수조사에 비해

분산과 표준편차가 낮게 나오는 경향이 있어서

n-1로 나누어 조금 값을 키운다고 생각하려무나.

엑셀에서

둘을 구분하나요?

물론이지.

두 경우 모두 엑셀 함수가 있단다.

먼저 분산은

VAR함수가 있단다

2010버전부터 VAR.S 함수가 새로 생겼지만

둘 기능은 같다.

VAR와 VAR.S는 어느 쪽이죠?

S가 Sample의 약자임을 기억하면

표본조사지.

그럼 전수조사는요?

엑셀 전수조사 분산 함수는

VAR.P란다.

P는 뭐의 약자죠?

Population이란다.

인구. 말 그대로 전부라는 뜻이지.

그러니까

지금 자료가 전체의 일부면

VAR이나 VAR.S 함수를.

지금 자료가 전체면

VAR.P 함수를 쓰면 되겠죠?

정답이다.

표준편차도 마찬가지로

전수조사와 표본조사에 맞는 함수가

따로 있고.

표본조사는

STDEV 함수가 있단다.

역시 2010버전부터 STDEV.S가 새로 생겼지만

기능은 같고.

이번에도

S는 Sample의 약자죠?

설마 전수조사 표준편차 함수는

STDEV.P인가요?

하나를 알면 둘을 깨치는구나.

다음 화에 계속....

오늘은 동아리 기부금 보고서인가...

천 원, 2천 원... 고만고만하네

그런데 평균이 뭐 이리 크지?

맞다! 90학번 선배가 술김에
50만 원을 기부하셨지.

평균이 이리 높아서야...
누가 보면 다 이렇게 낸 줄 알겠네.

민호야, 평균이 너무 높아서 고민이니?

뭐, 고민이랄 건 아닌데요
"한 명 당 47909원씩 기부했다"
고 말하기는 좀 이상하잖아요.

그럼 평균 말고
다른 대푯값을 쓰면 되잖니.

평균 말고요?
뭔가 이상한데...

민호는
'평균의 함정' 이야기를 모르는구나?

평균의 함정이요?

옛날에 어느 장군이 군대를 몰고
행군 중이었단다.

도중에 강이 나타나자
장군은 지나가던 농부를 불러서
강 깊이를 물어봤지.

농부는
'강 깊이는 평균 1미터'라고 했고
군대는 안심하고 강에 들어갔어.

그래서요?

그렇지만 군대는
물에 빠져 죽고 말았단다.
평균깊이가 1미터라면
제일 깊은 곳은 1미터보다 깊기 때문이지.

모든 평균이 자료를 잘 대표하지는 않는다는

교훈을 담은 이야기란다.

그럼 기부금액은 평균 말고
무슨 값을 써야 할까요?

이 경우엔...
중앙값이 어떠니?

중앙값이요?

중앙값이란 말 그대로
여러 수치 중에 딱 절반 위치에 있는
값이란다.

다섯 수치가 있으면
세 번째로 큰 수치가 중앙값이지.

그럼 90학번 선배가 낸 50만원은
영향을 덜 주겠네요.

그렇단다, 민호야.
중앙값은 일부 극단적인 값에
흔들리지 않는단다.
심지어 수치가 없을 때도 말이다.

수치가 없을 때라뇨?

예를 들어 80학번 총각이
어마어마한 거액을 준비했는데
그 금액을 모른다고 생각해봐라.

금액을 모르면
평균을 못 구하잖아요.

그렇지.
그런데 중앙값에서 보면
사람이 한 명 늘 뿐이니까
수치가 얼마든 중앙값을 구할 수 있단다.

할머니는 엑셀 말고도
아시는 게 많네요.
... 설마 엑셀에도?

후후후.
엑셀에는 MEDIAN이라는 함수로
중앙값을 구한단다.
사용법은 AVERAGE와 똑같다.

와, 대단...
잠깐만요, 할머니!

왜 그러니?

중앙값은 정 가운데 값이라면서요
그럼 자료 개수가 짝수면 어떡하죠?
짝수에는 중간이 없잖아요.

안 그래도 설명하려고 했다.
자료 개수가 짝수면
중간 양옆에 있는 두 수치의 평균을 내려무나

자료가 6개라면
3번째와 4번째의 평균을 내면 된단다.

엑셀 MEDIAN 함수도

자료개수가 짝수면
똑같이 하나요?

당연하지.
이런 유능한 마이크로소프트를 두고
쓸데없이 맥만 찾는 요즘 젊은것들은.. 엥이..

응? 또 전화가?
여보세요. 어, 알았어.

또 누가 새 일을 시켰구나?

네. 이번엔
기부금액 중
제일 많은 사람이 기부한
금액을 구하라네요.

즉, 최빈값 말이구나?

최빈값이요?
'빈'이 '빈번하다'의 빈이라면...

맞다, 민호야.
최빈값은 자료에서 제일 빈번히
등장하는 값이란다.

최빈값도 평균을 대체할
대푯값인가요?

그래.
수치 종류가 두세 가지뿐이라
평균보다는 횟수가 중요할 때
최빈값을 쓴단다.

게다가 최빈값은
자료가 숫자가 아니어도
사용할 수 있지.

오. 쓸만한데요?
물론 엑셀에도
최빈값 함수가 있겠죠?

역시 똘똘한 내 증손주라니까.
MODE 함수가 최빈값 함수란다.

MODE 함수에 범위를 넣으면...

간단하네요!

그나저나 엑셀을 공부하다 보니
수학까지 공부하게 되네요!

엑셀이 계산하는 법을 배우려면
쓰는 사람부터 계산을 할 줄 알아야지.

다음 화에 계속...

좋아. 동아리 일은 끝났으니

레포트를 좀 써 볼까?

조원들이 직접 가서 잰 수치와

추측으로 구한 수치들이네...

민호야, 또 엑셀 중이구나.

앗! 노크라도 하고 들어오세요!

놀랐잖아요.

미안하다 얘야.

이승 구경은 질리지가 않구나.

아무튼 이번에는 걱정 마세요.

그냥 평균만 구하면 되니까요.

세상에 그냥 평균은 없단다.

모든 개념, 모든 물체는 찬찬히 살펴보면

다 다르거든.

예를 들면요?

지금 네가 구하려는 평균이 그렇단다.

직접 측정한 수치와 추측한 수치가

같은 취급을 받으면 불공평하잖니?

그럼 어떻게 하죠?

내가 시키는 대로 해 봐라.

직접 측정한 가치는 두 배 중요한 것 같으니

곱하기 2를 해서 평균을 구해봐라.

할머니, 그럼 안 돼요.

값을 두 배로 해버리면

평균이 너무 올라가는걸요.

그럼 나누는 값도 올려야지

지금까지 다섯 명의 평균을 내면

5로 나누지 않았니?

네, 할머니.

5로 나눈다는 것은

다섯 명이서 1씩 분담하는 거야.

수가 두 배가 되면

1이 아니라 2를 맡아야지

그러니까 할머니 말씀은,

직접 잰 수치는 곱하기 2를 하되

직접 잰 사람 수도 곱하기 2를 하라는 거군요.

정확하다, 얘야.

그걸 전문용어로는 가중평균이라고 하지

어느 값에 가중치(Weight)를 둔다는 말이란다.

와! 할머니는 그런 것도 알고 계셨군요.

쯧쯧. 민호가 이과를 갔어야 했는데...

그런데, 할머니. 엑셀에서 가중평균을 구하는

함수는 없나요?

일단 제일 쉬운 방법은

가중치와 숫자를 곱한 합을 구하고

가중치 합으로 나누는 것이지

함수는 따로 없고요?

합동결혼식처럼

셀과 셀들을 차례로 곱하는 함수를

써 보면 어떨까?

그런 함수가 있나요?

SUMPRODUCT가 그런 함수지.

원래 행렬 연산에 쓰이는 함수지만

단순 배열도 행렬의 한 종류니까

사용 가능하단다.

보다시피

SUMPRODUCT에 두 배열을 넣으면

행렬 곱셈을 하듯이

두 배열을 차례차례 곱해서 더해준단다

어디 보자.

SUMPRODUCT 에 측정치 범위와

가중치 범위를 같이 넣으면...

측정치와 가중치를 각각 곱해서 더한 값이네요!

그 값을 가중치 합계로 나누면 되겠지.

SUM 함수를 이용해보렴.

오! 그냥 평균보다

뭔가 믿음직스러운데요.

그렇지?

물론 가중평균을 쓸지 말지, 가중치가 얼마일지는

잘 정해야 한단다.

무엇보다 평균 이야기는 아직 끝나지 않았어.