어떻게 하면 엑셀을 쉽게 설명할 수 있을까 고민했습니다. 떠오른 해답은 '대화체'였죠. 교과서처럼 늘어놓기보다는 한 사람이 설명하고 다른 사람이 질문하며 이해하는 식이 좋다고 생각했습니다. 엑셀 할머니는 그런 아이디어로 태어났습니다.

  왜 돌아가신 증조할머니를 떠올렸는지는 모르겠습니다. 약간의 블랙 코미디라고 보시면 될 것 같습니다. 엑셀이 뭔지도 모르고 살다 간 사람이 현재 젊은 사람보다 엑셀을 더 많이 안다? 거기에 저승에 있는 사람이라면 이런저런 이야기를 꺼내기 좋을 겁니다.

  좋은 글도 있고, 나쁜 글도 있습니다. 조금씩 고쳐나갈 생각입니다.

1화 - 상대참조와 절대참조

2화 - 평균내기

3화 - 가중평균

4화 - 기하평균과 평균성장률(+조화평균)

5화 - 최빈값과 중앙값

6화 - 반올림, 배수로 반올림

7화 - 행과 열 바꾸기

8화 - 분산과 표준편차

9화 - 요일 표시하기

10화 - PDF파일을 엑셀로 변환하기

11화 - 체크박스 만들기

12화 - 드롭다운 단추(목록만들기)

13화 - 엑셀 부가세를 구해보자

14화 - 엑셀 고급필터

15화 - 엑셀 FREQUENCY 함수

16화 - 엑셀암호 걸기

17화 - OFFSET 함수와 응용

18화 - 엑셀 FIND함수와 응용

19화 - 엑셀 사진 삽입하기

20화 - 엑셀 ROW와 COLUMN으로 셀 주소 알아내기

21화 - 엑셀 만나이 계산하기

22화 - 엑셀 함수 만들기

23화 - 엑셀 가나다순 정렬

24화 - 엑셀 로그

25화 - 엑셀 목표값 찾기

26화 - 스파크라인이란?

27화 - 엑셀 스파크라인 만들기

외전 1화 - WEEKDAY 함수(+CHOOSE)

외전 2화 - 엑셀 할인율

외전 3화 - 엑셀 vlookup 함수(+index, match)

외전 4화 - 엑셀 CONCATENATE 함수

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

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

알려주세요!

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

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

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 함수는 쉼표로 구분한 셀이나 텍스트, 숫자를 하나로 이어준답니다.

참!

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

" "

이렇게요.

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

민호야 준비 됐니?

무슨 준비요?

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

말했잖니.

그래서요?

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

으이구.

민호야. 잘 들으렴.

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

정말요?

빨리 알려주세요.

일단 함수를 만들고 싶다면

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

개발 도구 맨 왼쪽에 있는

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"을 붙이게 했단다.

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

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

함수를 직접 만들고 싶니?

후후후...

다음 화에 계속...