아. 방학이다.

날은 더워지고, 몸에 힘은 빠지고….

그러고 보니 엑셀 활용법을 가르쳐 주시던

증조할머니도 안 오시네.

많이 바쁘신가?

노느라 바쁘다. 노느라.

깜짝이야!

할머니! 어디 계셨어요!

당연히 저승에 있었지.

더워서 나도 쉬다가 왔다.

저승에서도 휴가를 가나요?

(아니, 죽어도 더위를 느끼나?)

그럼 종일 뭐 하니?

귀신은 잠도 안 자는데.

어디로 가나요?

남들 가는 대로 가지.

산과 바다와 별장으로.

이승 사람들 마주치기 싫어서

조용하고 외딴곳이 인기란다.

(그래서 폐가와 폐건물에

귀신 목격담이 많은 걸지도….)

아직도 엑셀이 고민이니?

아뇨. 방학인걸요.

올해는 휴학하면서 이것저것 경험하려고요.

좋다.

안 그래도 엑셀공부를

조금 달리할 생각이었다.

달리한다니 어떻게요?

복잡한 기능보다는

멋있고 재밌는 기능 위주로

가르치고 싶었다.

엑셀이 멋있으면 얼마나 멋있다고요.

예끼! 요즘은 멋있어야 사는 시대야.

할머니 어렸을 때는 가난해서

일만 해내면 쓸만한 사람이라 했어요.

일은 잘하는 게 맞죠.

세상은 다투어서 이기는 곳.

남보다 잘하지 못하면

아무리 잘해도 진 것과 다름없어.

(으으…)좋아요.

오늘은 뭘 배우죠?

민수는 별점이라고 들어 봤지?

별점이요?

영화잡지 같은 곳에서

영화에 점수를 매길 때 쓰는 방식 아닌가요?

주로 별 넷이나 다섯을 만점으로

영화 등급을 매기죠.

그래.

미슐랭 가이드는 식당을 별 셋 만점으로 표현하지.

영화와 레스토랑에 쓰는 별점이

엑셀과 무슨 상관이죠?

오늘은 말이다.

엑셀을 써서

점수를 별점으로 바꾸는 방법을

알아보자꾸나.

점수를 별점으로 바꾸기

별은 모두 열 개,

점수만큼 검은 별

나머지는 하얀 별을 넣자.

10점은 별 하나.

20점은 별 둘….

이렇게요?

그래.

1의 자리는 버리자꾸나.

예를 들어,

55점은 별 다섯을 쓰는 거다.

즉 10의 자리만 보자는 건데.

점수를 10으로 나누고

소수를 버리기만 한다면….

엑셀에는

QUOTIENT 함수가 있단다.

QUOTIENT

나눗셈 몫의 정수부분만 반환하는 함수

=QUOTIENT(숫자, 나눌 수)

EX> QUOTIENT(17, 4) → 4

(17÷4 = 4.25이므로)

10으로 나눠 정수만 남길 수 있겠네요.

55는 5,

79는 7이 되겠죠.

좋아.

그럼 그 수만큼 별을 써야겠지?

문자를 바라는 대로

쓰는 함수는 없나요?

REPT함수가 그 주인공이다.

REPT

문자는 횟수만큼 반복해 쓰는 함수

=REPT(문자, 횟수)

EX> REPT("가", 5) → 가가가가가

좋아요.

검은 별은 아까 구한 수대로 쓰고

흰 별은 10에서 그 수를 빼서 쓰면 되겠죠?

두 문자열을 붙이는 함수는

CONCATENATE란다.

CONCATENATE

여러 문자열을 합치는 함수

=CONCATENATE(문자열, 문자열 …)

EX> =CONCATENATE("토","마","토") → 토마토

좋아요. 완성했어요!

=CONCATENATE(REPT("★", QUOTIENT(셀 주소,10)), REPT("☆", 10-QUOTIENT(셀 주소,10)))

참고. 별표는 ‘ㅁ’을 누르고 ‘한자’를 눌러서 불러옵니다.

완벽하네요!

별 다섯이 만점인 별점은

10 대신 20으로 나누고

흰 별은 5에서 빼면 되겠지.

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

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

로그에도 종류가 있었죠?

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

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

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

혹시 필요할지 모르니

로그 법칙도 알려주마.

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

수학자 존 네이피어가

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

그건 그렇고,

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

엑셀 로그함수는 말 그대로

LOG란다.

= LOG(수, 밑)

* 밑 생략 시 10

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

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

자연로그는요?

자연로그는 LN이란다.

= LN(숫자)

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

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

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

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

바로 CONCATENATE라는 함수입니다.

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

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

바로 예를 들어 볼까요.

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

이제 결과로

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

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

이렇게 해 보세요.

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

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

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

참!

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

" "

이렇게요.

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

민호야 준비 됐니?

무슨 준비요?

지난 시간에 '차라리 함수가 따로 있었으면 좋겠다'고 

말했잖니.

그래서요?

설마 마이크로소프트에 직접 가신 건..

으이구.

민호야. 잘 들으렴.

엑셀에는 함수를 만들 수 있는 기능이 있단다.

정말요?

빨리 알려주세요.

일단 함수를 만들고 싶다면

개발 도구 리본에 들어가렴.

개발 도구 맨 왼쪽에 있는

Visual Basic을 누르렴.

뭔가 이상한 창이 나타났어요.

Visual Bacis for Applications

흔히 VBA라고 부르는 기능이다.

VBA를 잘 쓰면 엑셀 고수가 될 수 있지.

여기서 사용자 정의 함수를 만들어보자꾸나

프로젝트니 속성이니...

머리가 아파요.

지금은 일단

삽입 - 모듈을 눌러라.

메모장 비슷한 창이 나타났어요.

여기에 글을 쓰면 함수를 만들 수 있다.

엑셀에 내리는 일종의 명령인 셈이지.

프로그래밍을 배워 본 적 있니?

아뇨. 한 번도 없는데요...

좋아. 프로그래밍을 알면 VBA도 쉽게 하지만

몰라도 할 수는 있단다.

그럼 차근차근 배워 보자.

1단계 : 함수 이름

일단 처음이니까, today 함수처럼

아무 인수도 필요 없는 함수를 만들어보자.

예를 들어서 무조건

숫자 5를 반환하는 함수를 만들어보자.

함수 이름은 뭘로 하고 싶니?

숫자 5를 반환하니까

그냥 five로 하죠?

좋다. 그럼 이렇게 쓰렴.

Function five()

five = 5

End function

무슨 뜻이죠?

일단 Function과 End function은

'이제부터 함수를 입력하겠습니다 ~ 이제 함수 입력이 끝났습니다.'

라는 뜻이다.

five()는 '이 함수 이름은 five입니다.

괄호 안은 필요 없어서 비워놨습니다.'란 뜻이고.

five = 5는 '이 함수 결과는 5입니다'라는 뜻이다.

만약 함수 이름이 onlyfive였으면, onlyfive=5라고 써야 하나요?

그렇지.

자, 이제 파일 - 저장을 누르렴.

저장할 때는 파일 형식을 '엑셀 매크로 사용 통합 문서'로 정해야 한단다.

그렇지 않으면 파일을 켰다 끄면 새로 만든 함수가 날아간단다.

VBA를 끄고 함수를 시험해 보려무나.

아무 칸에나 =five()라고 쓰니까..

정말 5가 나왔네요!

2단계 : 함수와 인수.

그럼 이제 숫자를 계산하는 함수를 만들어보자.

민호는 어떤 함수를 만들고 싶니?

어...

두 수를 더한 다음 제곱하는 함수는 어떨까요?

좋아. 그럼 기본틀은 이렇겠지.

Function 제곱(숫자1,숫자2)

End Function

함수이름이 한글이어도 되나요?

그럼.

좋아요. 두 숫자를 더해서 제곱하는 거니까...

Function 제곱(숫자1,숫자2)

제곱 = (숫자1 + 숫자2) ^ 2

End Function...

좋아요. 이번 함수도 잘 작동하네요.

3단계 : 이미 존재하는 함수 불러오기

좋아요. 이번에는 원의 넓이를 구하는 함수를 만들어보죠.

Function 넓이(반지름)

넓이 = 반지름 ^ 2 * PI()

End Function

맞죠?

어라? 왜 안 되지?

여기는 함수를 만드는 곳.

이미 존재하는 함수를 막 부를 수는 없단다.

그럼 어떡하죠?

Application.WorksheetFunction.함수이름

으로 이미 존재하는 함수를 불러올 수 있단다.

4단계 : IF 구문

좋다. 이번에는

시험점수에 따라 텍스트를 출력하는 함수를 만들어보자.

50점 미만은 '더 노력하세요'

50점에서 99점은 '괜찮아요'

100점은 '잘했어요'

101점부터는 '다시 입력하세요'

...라고 말이다.

음, 조건에 따라 다른 결과를 내려면...

이때는 IF 구문을 이용하면 된다.

if 첫 조건 then

결과 1

elseif 두 번째 조건 then

결과 2

...

else 나머지 경우일 때 결과

end if

'이 조건일 때는 이것을.. 이 조건일 때는 저것을...

그도저도 아닐 때는 이걸....'이라는 뜻이란다.

알았어요. 이렇게 하면 되죠?

결과도 제대로 나왔네요.

* VBA 단축키는 Alt + F11입니다.

할머니, 생년월일을 알면

엑셀에서 나이계산을 할 수 있나요?

그럼. 그전에 계산식부터 생각해 보자.

수학으로 식만 구할 수 있다면

엑셀에서는 대부분 구현할 수 있으니.

2000년생이라고 가정해 볼까?

2000년에 태어나면 2000년에는 1살,

2001년에는 2살, 2002년에는 3살...

그럼 2018년에는 몇 살일까?

19살이겠죠.

그럼 식은 어떻게 될까?

현재 년도에서 태어난 년도를 뺀 다음

1을 더하면 나이가 되겠네요.

한국식 나이는 그렇게 계산하지.

물론 요즘 사람들은 싫어하는 것 같다만.

하긴, 1월 1일에 태어난 사람과 12월 31일에 태어난

사람이 년도가 같다고 나이가 같으면 조금 이상하겠구나.

아무튼 엑셀로 나이를 계산한다면 이렇게 되겠지.

=YEAR(TODAY()) - YEAR (생년월일) +1

TODAY는 뭐죠?

TODAY는 오늘 날짜를 반환하는 함수란다.

괄호 안에는 아무것도 넣을 필요 없단다.

*만약 TODAY 함수가 자동으로 업데이트되지 않는다면

파일 - 옵션 - 수식 - 계산 옵션에서 자동을 선택합시다.

그럼 만 나이도 계산 가능할까요?

만 나이 계산법은 아니?

만 나이는 말 그대로 만(滿), 1년을 꽉 채워야 한 살이 된다는 뜻이죠.

2000년 5월 5일에 태어난 사람은

2001년 5월 5일이 되어야 만으로 한 살인 거죠.

원래 나이에서 만 나이를 구하는 법도 아니?

생일이 안 지났으면 원래 나이-2,

생일이 지나면 원래 나이-1이죠.

그럼 엑셀로 만 나이를 구하기 귀찮겠네요.

IF 함수를 이용해서 생일이 지났는지 안 지났는지 검사한 다음에...

아니다 민호야.

놀랍게도 만 나이를 구하는 함수가

엑셀에 있단다.

정말요?

정확히 말해서 만 나이를 위해 만든

함수는 아니지만 말이다.

바로 DATEDIF 함수란다.

DATEDIF요?

DATEDIF는 두 날짜 사이 경과한 기간을 구하는 함수란다.

경과한 날짜, 경과한 달수, 경과한 년도수를 구할 수 있지.

그럼 경과한 년도수가...

그렇단다. DATEDIF함수로 생년월일과

현재 날짜 사이 경과한 년도수를 구하면, 그게 자동으로 만 나이가 되겠지.

DATEDIF 함수를 쓰는 법을 알려주세요!

물론이다.

=DATEDIF( 생년월일 , 오늘 날짜, "Y")

여기서 "Y"는 연도 수를 계산하라는 정보 형식이란다.

그럼 할머니,

주민번호를 엑셀에 넣고 만 나이를 알 수 있을까요?

생년월일을 알면 한국식 나이도 만 나이도

위에서 설명한 방법으로 계산할 수 있지.

그러니까, 주민번호로 생년월일만 추출하면 되는 거군요.

그렇단다.

주민번호 앞 여섯 자리가

각각 생년월일의 년도, 월, 일이잖니.

내가 한번 단순한 생년월일 추출 수식을 만들어 봤다.

XX가 셀 주소일 때 : 

=IF( MID(XX, 10, 1)<"3","19", "20") &LEFT(XX, 2)&"-"&MID(XX, 3, 2)&"-"&MID(XX, 5, 2)

아, MID함수는 텍스트를 추출하니까,

두 글자씩 추출해서 생년월일을 만드는군요.

그런데 IF함수는 왜 있죠?

그 사람이 21세기 출생자일 수도 있잖니.

21세기에 태어나면 뒷자리가 3과 4로 시작한단다.

그래서 뒷자리가 3보다 작으면

20세기 출생자라서 년도에 "19"를 붙이고

3 이상이면

21세기 출생자니까 년도에 "20"을 붙이게 했단다.

단순하면서도 좋은 수식이네요.

마이크로소프트가 차라리 나이계산 함수를 만들어 줬으면 좋겠는데...

함수를 직접 만들고 싶니?

후후후...

다음 화에 계속...

할머니, 행렬은 왜 행렬이라고 부르죠?

행렬은 영어로 MATRIX라고 하지.

MATRIX에는 어머니, 자궁이라는 뜻이 있단다.

아마 종이 위 숫자를 2차원으로 담은 행렬이

아이를 품은 자궁과 비슷해서 그런 걸지도 모르지.

훗날 MATRIX가 일본에 들어오고,

일본 사람들은 그걸 행렬이라고 번역했지.

말 그대로 행렬에는 행과 열이 있으니까.

국가, 사회, 기술...

일본인들이 근대에 번역한 서양 언어들은

지금 우리나라도 널리 쓰고 있지.

할머니, 그나저나

셀의 주소를 알 수 있는 방법이 있나요?

그럼, 있고말고.

엑셀엔 ROW와 COLUMN이라는 함수가 있단다.

ROW와 COLUMN은 각각 셀의 행 주소와 셀 주소를 반환하는 함수다.

그냥 함수 뒤에 셀 주소만 넣으면 되지.

예를 들어 셀 C4를 ROW 함수에 넣으면 4를 반환하고,

COLUMN 함수에 넣으면 3을 반환한다.

C4는 4번째 행, 3번째 열에 있기 때문이지.

만약 두 함수에 아무것도 넣지 않으면

함수를 적은 그 셀의 행과 열을 각각 반환한단다.

뭐, 표가 아주 크지 않은 이상

행과 열은

그냥 보고 적으면 될 것 같기도 하네요.

ROW와 COLUMN은 시트에 행과 열을 추가하거나 삭제하면

그 추가와 삭제를 반영한단다.

그냥 행과 열을 적어넣어서는 할 수가 없지.

게다가 두 함수는 범위를 반환할 수도 있어.

예를 들어 이 범위(A1:A3)의 행 주소를 알고 싶다고 하자.

범위가 세 줄이니까

반환할 범위도 세 줄이겠지?

원하는 세 칸을 드래그해서

ROW 함수에 범위를 넣은 다음,

CTRL+SHIFT+ENTER를 누르렴.

기억 나요.

범위를 반환할 때

그 키를 입력했죠?

그런데 혹시 말이죠.

셀 주소를 'X행 Y열' 처럼 쓸 수는 없을까요?

좌표처럼요.

물론 있지.

&를 활용하면 된다.

&가 뭐죠?

지금은 그냥

'문자와 문자를 이어주는 접착제'라고만 해 두자.

좌표를 알기 원하는 셀이 D7이라고 하자.

그럼 이렇게 써 보려무나.

= ROW(D7)&"행 "&COLUMN(D7)&"열"

와!

결과에 텍스트를 넣을 수도 있었네요.

&랑 큰따옴표만 알면 누구나 가능한 비법이란다.

할머니,

오늘 성적 보고서를 보니까

OFFSET 함수로 원하는 사람의 점수를 구한다는데

OFFSET 함수가 뭐죠?

OFFSET 함수는 시작 지점에서 이동한 셀의 내용을

반환하는 함수란다.

시작 셀을 정해 두고 '아래로 몇 칸, 오른쪽으로 몇 칸'

을 명령하면 그 위치에 있는 값을 반환하지.

OFFSET 함수 구성은 기준 셀, 이동할 칸수로 구성된단다.

이동할 칸수는 처음에는 아래, 다음에는 오른쪽이지.

- 예시

예를 들어

지금 홍길동의 영어 성적을 알고 싶다고 하자.

기준 점은 표 맨 왼쪽 위로 잡는다면,

홍길동의 영어 성적은 아래로 몇 칸, 오른쪽으로 몇 칸 가야 할까?

홍길동의 영어 성적은 여기 있으니까

아래로 다섯 칸, 오른쪽으로 두 칸 가야겠죠.

그럼 OFFSET 함수에 이렇게 입력해보렴

=OFFSET( 기준 셀, 5, 2)

어? 정말로 홍길동의 영어점수가 나왔네요.

- 찾기가 귀찮다면, MATCH 함수와 함께

그런데 할머니.

지금은 홍길동의 영어점수가 어디 있는지 보이지만

내용이 많아서 찾기 어려우면 어떡하죠?

그럴 때는 MATCH 함수를 같이 쓰면

쉽게 원하는 내용을 찾을 수 있다.

MATCH 함수요?

MATCH 함수는 범위에서 원하는 내용이 몇 번째에 있는지

위치를 반환하는 함수란다.

MATCH를 이용하면

'홍길동'이 몇 번째에 있는지 알 수 있고

'홍길동'이 몇 번째에 있는지 알면...

OFFSET 함수에 넣어서

그만큼 밑으로/오른쪽으로 가라고 명령할 수 있겠네요?

그렇지.

하나를 가르치면 열을 아는구나.

이렇게 써 봐라.

=OFFSET( 기준 셀 , MATCH("홍길동" , 이름 범위, 0) , MATCH("영어" , 항목 범위, 0)

이것만 있으면 사람이 수천 명이어도

금방 원하는 사람의 점수를 찾을 수 있겠네요.

- 보너스

OFFSET 함수는 사실 범위를 인식할 수도 있습니다.

함수 마지막 인수로 폭과 높이를 지정하면, 원하는 곳의 범위를 입력할 수 있습니다.

주로 SUM 함수와 연계해서 범위 합계를 구하는 데에 씁니다.

= SUM(OFFSET (기준 셀, 세로 이동, 가로 이동, 높이, 폭))