설찬범의 파라다이스
글쓰기와 닥터후, 엑셀, 통계학, 무료프로그램 배우기를 좋아하는 청년백수의 블로그
엑셀 강좌 (10)
2화. 그림으로 그래프 그리기
반응형





아.. 덥다.

겨울엔 추워서 고생, 여름엔 더워서 고생.

단군이 터를 잘못 잡아도 한참 잘못 잡았네.

홍익인간이 설마 더워서 벌게진 사람인가?





컴퓨터 방은 들어가기도 싫다.

발열이 왜 이리 심한 거야?

한증막이 따로 없다니까...





으악! 할머니!

여기서 뭐 하세요?





심심해서 왔지.





그러고 보니 할머니.

영혼은 어디든 갈 수 있지 않나요?





원하는 곳은 아무데나 갈 수 있지.





그럼 외국도 가 보셨나요?





물론이지.

일본, 베트남, 호주, 러시아...





여행도 여행이지만

산 사람이 못 가는 곳도 가 보셨어요?

세계적인 미스터리를 풀 수 있을 텐데요.





버뮤다 삼각지대, 체르노빌.

백악관 지하에 정말 비밀기지가 있는지

남미 어딘가에 있는 마야 도시를 찾고..





아! 혹시 우주도 갈 수 있나요?

화성에 생명체를 찾으면 참 좋을 텐데...





...너한텐 이 할미가 수색대원으로 보이는구나.





쓸데없는 호기심은 그냥 접고

엑셀 비법이나 하나 배우렴.





오늘은 뭔가요?





엑셀 막대그래프(차트)를 보면

막대 색을 정할 수 있지?





네, 처음 만들면 늘 파란색이죠.









이걸 그림으로 대신 만들고 싶지 않니?

이렇게 말이다.






오. 그래프 축이 하트모양이 됐어요.

멋진걸요.





만드는 방법을 바로 알려주마

우선 넣을 그림이 필요하겠지?





여기 하트 그림 있어요.





좋다. 그래프를 하나 만들어라.





여기요.








축을 한 번 누르면

모든 축이 전부 선택된다.





오른쪽 마우스를 클릭해

'데이터 계열 서식'을 누르면

오른쪽에 서식메뉴가 나타나지.





왼쪽 '채우기 및 선'을 눌러

채우기 - 그림 또는 질감 채우기를 누르렴





'다음에서 그림 삽입' 밑에 있는

'파일'을 눌러 불러오고 싶은 그림을 불러오면 된단다.





어? 그림이 세로로 늘어났는데요?





밑에서 '늘이기' 대신 '쌓기'를 누르면

우리가 원하는 그래프가 나온단다.






음. 쉽네요.

그래프 축이 그림이면 확실히 보는 재미가 있겠죠?



반응형

'엑셀 > 엑셀 할머니 시즌 2' 카테고리의 다른 글

3화. 엑셀 텍스트 불러오기  (0) 2018.08.17
1화. 엑셀 별점 만들기  (0) 2018.07.04
  Comments,     Trackbacks
엑셀 할머니 시즌 1
반응형

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


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


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




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 함수


반응형
  Comments,     Trackbacks
하루 30분) 엑셀 고급필터
반응형




  고급 필터는 자료를 조건에 맞게 걸러내는 기능입니다. 원래 표에서 원하는 부분만 남길 수도 있고 다른 곳에 표를 다시 만들 수도 있습니다. 원하는 부분만 남기면 나머지는 사라지지 않고 생략됩니다. 이번 포스팅은 다른 곳에 표를 만들어 보겠습니다.

 

  고급 필터 쓰는 법 간단하게



1) 조건식을 빈 곳에 쓴다.

2) 데이터 리본 - [정렬 및 필터] - [고급]을 누른다.

3) 목록 범위는 자료로 정한다.

4) 조건 범위는 1에서 쓴 조건식으로 정한다.

5) 바라는 곳을 복사 위치로 한다.

(‘다른 장소에 복사를 선택했다면)

6) 확인을 누른다.

(자세한 건 예제와 함께)



 

주의. 목록 범위는 자료 제목도 들어가야 합니다.

 


조건식 쓰는 법 - 간단하게



1) 조건을 가리는 항목을 쓴다.

2) 그 밑에 조건을 쓴다.

3)

(자세한 건 예제와 함께)



시작하기 전에 예제파일 받아가세요!


하루 30분 고급필터.xlsx




우리만의 약속



첫째. 예제 파일로 한 번씩 예제 연습하기

둘째. 아리송하면 복습하기

셋째. 부담 없이 잔잔히 즐기기



* 이 포스팅은 엑셀2016을 기반으로 썼습니다.




예제 1) ‘중간고사50 이상인 행을 표시할 것



중간고사

>=50

 


 

예제 2) ‘성별이 여자인 행을 표시할 것




성별

여자



 

 

조건이 여럿일 때



여러 조건이 있다면 AND인지 OR인지 잘 판단해야 합니다.

 

AND : 여러 조건을 동시에 만족해야 함

OR : 여러 조건 중 하나라도 만족하면 됨

 

성별이 여자고 점수가 50점 이상은 여자와 50점 이상을 모두 만족해야 하므로 AND입니다. ‘성별이 여자거나 점수가 50점 이상은 둘 중 하나만 만족하면 조건에 맞으므로 OR입니다.

 

외우자. AND는 같은 줄에 OR은 다른 줄에!

 




예제 3) ‘성별이 남자고 나이50 이하인 행을 표시할 것




성별 나이

남자 <=50



 


예제 4) ‘성별이 남자거나 나이50 이하인 행을 표시할 것



      성별    나이

남자  

              <=50



 

 

원하는 열만 나타내고 싶을 때



바라는 제목들을 입력하고 복사 위치로 지정한다.

 


예제 5) ‘중간고사70점 이상인 행을 이름, 나이, 등록일만 표시할 것



① 미리 원하는 항목을 써둔다



② '복사 위치'를 이 항목들로 한다.





 

예제 6) ‘이름가 들어가는 행만 표시할 것



조건

=FIND("", B3)>=1



 

이름이 아니라 조건이죠?

  고급 필터 조건에 함수나 계산값을 쓰고 싶으면 원본 자료에 없는 제목을 쓰거나 비워야 합니다!

 



참고. 제목을 비워도 조건 범위는 빈칸까지 넣어야 합니다.

 

 

예제 7) ‘등록일7월인 행을 표시할 것



조건

=MONTH(E3)=7



 

 

예제 8) ‘등록일2월이거나 10월인 행을 표시할 것



                           조건 1                     조건 2

=MONTH(E3)=2

                                                              =MONTH(E3)=10

 


 

예제 9) ‘등록일의 일이 10 미만인 행을 표시할 것



조건

=DAY(E3)<10

 


 

예제 10) ‘성별이 남자가 아닌 행을 표시할 것



~가 아닌 조건은 <>가 좋습니다.

 

성별

<>남자

 


 

예제 11) ‘기말고사기말고사평균 이상인 행을 표시할 것


조건

=H3>AVERAGE($H$3:$H$22)

 





 

예제 12) ‘중간고사’, ‘기말고사가 전부 70 이상인 행을 표시할 것



 

첫 번째 방법

중간고사  기말고사

>=70    >=70

 

두 번째 방법

COUNTIF 함수는 조건에 맞는 수를 구하는 함수입니다.

70 이상을 만족하는 점수가 둘인 행을 찾게 합시다.

 

조건

=COUNTIF(G3:H3, ">=70")=2





예제 13) ‘기말고사중간고사보다 높은 행을 표시할 것



조건

=G3<H3

 


 

예제 14) ‘회원번호세 번째 글자가 6 이상인 행을 표시할 것



 

조건

=MID(F3, 3, 1)*1>=6

 



참고. LEFT, MID, RIGHT 함수는 글자를 텍스트로 빼내는 함수입니다. 텍스트를 숫자로 바꾸기 위해 *1을 해줍니다.

 


예제 15) ‘기말고사상위 5등을 표시할 것



LARGE 함수를 응용합니다.

LARGE는 범위에서 ~번째로 큰 값을 반환합니다.

 

조건

=H3>=LARGE($H$3:$H$22, 5)

 



참고. LARGE 함수의 반대는 SMALL입니다.

SMALL은 범위에서 ~번째로 작은 값을 반환합니다.

 

 

여러 조건 한 셀에 쓰기



여러 조건은 나누어 써야 쉽지만, 시험에서 한 줄, 한 셀에 다 쓰라고 시키는 때도 있습니다. 이땐 ANDOR 함수를 써야 합니다.

 



예제 16) ‘성별이 여자고 중간고사30점 미만인 행을 표시할 것(조건은 셀 하나에 쓸 것)




조건

=AND(C3="여자", G3<30)

 



예제 17) ‘나이30 미만이거나 50 초과면서, '중간고사''기말고사'가 모두 80 이하인 행을 표시할 것(조건은 셀 하나에 쓸 것)



조건

=AND(OR(D3<30,D3>50),AND(G3<=80,H3<=80))

 



참고. 조건이 복잡할수록 큰 틀부터 짜맞춥시다.

반응형

'엑셀 > 하루 30분 엑셀카페' 카테고리의 다른 글

하루 30분) 엑셀 조건부 서식  (1) 2018.07.02
  Comments,     Trackbacks
하루 30분) 엑셀 조건부 서식
반응형



  수많은 자료 중에서 원하는 자료만 색을 바꾸거나 굵은 글씨로 만들 수 있다면 얼마나 좋을까요. 한눈에 들어와서 보기도 좋고 쓰기도 좋을 겁니다. 엑셀 조건부 서식은 실용성도 좋지만 컴활 자격증 시험에서 물어보기도 하니 꼭 알아둬야 하겠죠.

 

  엑셀은 이미 여러 조건부 서식을 지원합니다. ~보다 높은 숫자나 상위 몇 퍼센트를 강조하는 식이죠. 기본 내용은 예전 포스트에서도 설명했으니 이번엔 수식을 직접 입력해서 조건부 서식 고수가 되어 봅시다.

 


시작하기 전에 예제 파일 받아가세요!


조건부서식-엑셀카페.xlsx




우리만의 약속


첫째. 예제 파일로 한 번씩 예제 연습하기

둘째. 아리송하면 복습하기

셋째. 부담 없이 잔잔히 즐기기



* 이 포스팅은 엑셀2016을 기반으로 썼습니다.

* 밑 조건식을 그대로 붙여넣기 하면 작동하지 않았습니다. 까닭은 모르지만 참고만 하시고 손수 쓰시기 바랍니다.

 



범위 정하기



  조건부 서식 규칙을 만들기 전에 조건을 적용할 셀들을 지정합니다.

드래그로 범위를 정합니다.

 



주의. 제목 셀은 범위에 넣지 않습니다!

 


조건부 서식 시작하기




  범위를 정하고 홈 리본에 있는 [조건부 서식]을 클릭합니다.

[새 규칙]을 누르고 수식을 사용하여 서식을 적용할 셀 결정을 누릅니다.




  밑에 입력창이 하나 생깁니다. 이곳에 규칙을 입력합니다.


  '서식'에서는 규칙에 맞는 셀에 어떤 서식을 줄지 정합니다. 이번 포스트에서는 전부 하늘색으로 셀을 칠하겠습니다.



 

 



예제 1) 나이가 50세 이상인 사람의 행을 칠할 것



=($D3>=50)





주의. 규칙을 입력할 때 방향키를 누르면 셀 주소가 나타납니다. 입력 커서는 마우스로만 움직입시다!

 

 




예제 2) 남자인 행만 칠할 것



=($C3="남자")



 

참고. C열이 남자가 아닌 행은 조건식이 뭘까요? <>를 씁니다.


=($C3<>"남자")

 


 

예제 3) 회원번호 첫 글자가 A인 사람의 행을 칠할 것



엑셀 LEFT 함수는 셀 텍스트를 왼쪽부터 추출하는 함수입니다.

(LEFT 함수의 첫 인수는 셀 주소둘째 인수는 추출할 글자 수입니다)

 

=(LEFT($F3,1)="a")





참고. 셀 텍스트를 오른쪽부터 추출하는 함수는 뭘까요?

, 맞습니다. RIGHT 함수입니다.

 

 

예제 4) 회원번호 두 번째 글자가 5 이상인 행을 칠할 것



  셀 텍스트를 중간부터 추출하는 함수는 MID입니다.


MID(셀 주소, 추출을 시작할 글자, 추출할 글자 수)

(이 경우에는 셀 주소, 2, 1이겠죠)

 

=(MID($F3, 2, 1)*1>=5)



 

잠깐! ‘*1’이 있죠?

  LEFT, MID RIGHT 함수는 셀 글자를 텍스트로 추출하는 함수입니다. 아무리 숫자를 빼냈어도 텍스트 상태기 때문에 다른 숫자와 비교할 수가 없습니다. 따라서 *1을 해서 숫자로 바꿔야 제대로 서식이 적용됩니다.

 

 

예제 6) 행 번호가 짝수/홀수인 행을 칠할 것



행 번호를 반환하는 함수는 ROW입니다.

ISEVEN 함수는 인수가 짝수면 TRUE를 반환하고

ISODD 함수는 인수가 홀수면 TRUE를 반환합니다.

 

행 번호가 짝수인 행을 칠할 땐

=ISEVEN(ROW())

행 번호가 홀수인 행을 칠할 땐

=ISODD(ROW())



 

참고. 열 번호를 반환하는 함수는 COLUMN입니다.

 

 

예제 7) 행 번호가 3의 배수인 행을 칠할 것



MOD 는 나눗셈 후 나머지를 구하는 함수입니다.

3으로 나누어 나머지가 0이면 3의 배수겠죠?

 

=MOD(ROW(), 3)=0



 




예제 8) 등록일이 8월인 행을 칠할 것



  날짜에서 달수를 추출하는 함수는 MONTH입니다.

 

=MONTH($E3)=8



 

예제 9) 등록 날짜가 15일 이상인 행을 칠할 것



날짜에서 날짜를 추출하는 함수는 DAY입니다.

 

=DAY($E3)>=15



 

 

예제 10) 점수가 상위 10등인 행을 칠할 것



LARGE 함수는 범위에서 ~등인 값을 반환합니다. 이 함수를 응용합시다.

 

=$G3>=LARGE($G$3:$G$22,10)

해석 : 점수 범위에서 10등인 값 이상인 점수 행을 색칠





예제 11) 점수가 평균보다 높은 행을 칠할 것



평균을 구하는 함수는 AVERAGE입니다.

 

=$G3>AVERAGE($G$3:$G$22)



 


예제 12) 여자면서 나이가 40세 이상인 행을 칠할 것



이제 조건이 두 가지입니다.

두 조건을 만족하는 조건부 서식을 쓰는 법은 둘입니다.

 

첫째, AND 함수 이용

둘째, * 이용

 

=AND($C3="여자", $D3>=40)

=($C3="여자")*($D3>=40)



 

참고. AND 함수는 두 조건이 모두 맞을 때만 TRUE를 반환합니다.

 

참고. *AND처럼 TRUE*TRUE일 때만 TRUE를 반환합니다.

반응형

'엑셀 > 하루 30분 엑셀카페' 카테고리의 다른 글

하루 30분) 엑셀 고급필터  (0) 2018.07.03
  Comments,     Trackbacks
엑셀로 통계하기 2 - 상자 수염 그림
반응형






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

 




이건 상자 수염 그림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:A1001사분위수를 구합니다.


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

 

 

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


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

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

반응형
  Comments,     Trackbacks
엑셀 할머니 27 - 엑셀 스파크라인 만들기
반응형





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




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

알려주세요!





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

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

1) 꺽은선형

2) 열

3) 승패(양수와 음수)




창이 나타나면

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

확인을 누르렴




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




음. 두께도 너무 가늘고

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




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

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




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


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


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




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




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


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





엑셀 스파크라인 예제.xlsx


여기 샘플 파일이 있으니

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

반응형
  Comments,     Trackbacks
엑셀 할머니 23 - 엑셀 가나다순 정렬
반응형









자료들이 여러 줄 있을 때,

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






정렬 자체는 아주 쉽단다.







원하는 범위를 드래그하고

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






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

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

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






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

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








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

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








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





음...




왜 그러니?

석연치 않은 구석이 있니?







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














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






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

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







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






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

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








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









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

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





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

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





맞아요.








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







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

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









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







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

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






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

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

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









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

반응형
  Comments,     Trackbacks
엑셀 할머니 22 - 엑셀 함수 만들기
반응형





민호야 준비 됐니?






무슨 준비요?






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

말했잖니.





그래서요?

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





으이구.

민호야. 잘 들으렴.

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





정말요?

빨리 알려주세요.







일단 함수를 만들고 싶다면

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



 * 개발 도구 리본이 없다면

파일 - 옵션 - 리본 사용자 지정에 들어가서 개발 도구를 체크합시다.








개발 도구 맨 왼쪽에 있는

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입니다.

반응형
  Comments,     Trackbacks
엑셀 할머니 21 - 엑셀 만나이 계산하기
반응형





할머니, 생년월일을 알면

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







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

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

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





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"는 연도 수를 계산하라는 정보 형식이란다.




 * 다른 정보형식은 다음과 같습니다.


 "M"은 개월 수 계산

 "D"는 날짜 수 계산

 "MD"는 월과 연도를 무시한 날짜 차이

 (예 : 2001년 8월 1일과 2002년 9월 2일은 "MD"로 1일 차이)

 "YM"는 일과 연도를 무시한 개월 차이

 "YD"는 연도를 무시한 날짜 차이







그럼 할머니,

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












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

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








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







그렇단다.

주민번호 앞 여섯 자리가

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







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








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






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

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








함수를 직접 만들고 싶니?

후후후...




다음 화에 계속...

반응형
  Comments,     Trackbacks
엑셀 할머니 16화 - 엑셀암호 걸기
반응형







할머니, 엑셀에 암호를 걸려면 어떡해야 하나요?






파일 말이냐? 셀 말이냐? 통합문서 말이냐?





엑셀 암호 종류가 그렇게 많나요?

저는 암호를 입력해야 파일이 열리는 걸 원하는데...






그럼 엑셀 파일에 암호를 거는 법을 알려주마.








저장 버튼을 누르고 경로를 정하기 전에

'저장' 옆 '도구'를 눌러서

'일반 옵션'에 들어가렴.




암호는 두 가지.

열기 암호와 쓰기 암호가 있다.










읽기 암호를 걸면 암호를 넣지 않는 이상

읽을 수도 없어요.



쓰기 암호를 걸면 읽을 수는 있지만(읽기 전용으로 열기)

내용을 바꾸지는 못하지.

필요하면 둘 다 걸어도 된단다.






아까 말씀하신 셀에 암호 걸기는 어떻게 하죠?






엑셀 검토 리본에 들어가서

'시트 보호'라는 메뉴를 눌러라.



그런 다음 암호를 두 번 입력하면

그 워크시트가 통째로 잠기게 돼서

한 글자도 입력할 수가 없단다.


(* 다른 워크시트는 그대로입니다.)



시트 보호를 풀고 싶으면

아까 '시트 보호'가 있던 바로 그 위치를 클릭해서 암호를 입력하면 된단다.










통합문서 보호는 뭔가요?







이건 워크시트를 잠그는 기능이란다.

통합문서 보호를 켜면

새 시트를 추가하거나 시트 이름을 바꿀 수 없게 되지.





방법은 시트 보호랑 같단다.

검토 리본에서 '통합문서 보호'를 누르고

암호를 입력하면 끝.

풀 때도 같은 버튼을 누르고 암호를 입력하면 된다.


(* 모든 암호는 대소문자를 구분합니다.)



반응형
  Comments,     Trackbacks