Helianthus

내가 자주 쓰는 엑셀 단축키와 함수 3탄 본문

학교와 직장

내가 자주 쓰는 엑셀 단축키와 함수 3탄

룬이 2015. 3. 13. 17:45


이번 포스팅은...

이전 글에서 못다 적은 표/이름관리자/매크로 부분과 더불어, 자주 쓰진 않아도 알아두면 쏠쏠한 단축키/함수에 대한 내용입니다.


이전 포스팅을 보고 싶으신 분들은 아래를 클릭해보시면 됩니다.

2015/02/26 - 내가 자주 쓰는 엑셀 단축키와 함수 1탄 (엑셀뉴비에서 강의를 하기까지...)

2015/03/05 - 내가 자주 쓰는 엑셀 단축키와 함수 2탄

2015/03/13 - 내가 자주 쓰는 엑셀 단축키와 함수 3탄




15. 그 밖에... (표/이름관리자/매크로)

'그 밖에...' 라고 기술한 이유는... 이 부분을 잘 모르더라도 엑셀을 사용하는 데 큰 무리가 없기 때문이다. 


표와 이름관리자

지난 2탄 포스팅의 '12. 특정셀 불러오기 (vlookup/index/match)'와 같은 함수를 쓸 때... raw데이터의 위치(=어디서 찾을래)를 정의했어야만 했다. 예제에서는 셀주소(=$C$2:$E$10과 같은...)로 정의했었는데 이 부분을 '표' 기능을 이용하여 보다 직관적으로 해둘 수가 있다.


예를 들어, 아래 [그림11]의 F1셀부터 H9셀에 이르는 학생 별 몸무게/먹은사과 개수 raw데이터가 있다고 치자. 


[그림11]


F1셀부터 H9셀까지 선택한 다음 [삽입]탭의 [표]를 클릭한다. '학생/몸무게/먹은 사과 개수라'는 머리글이 포함된 상태이므로, [머리글 포함(M)]에 체크한 상태에서 [확인]을 클릭한다.


그럼 아래 [그림12]와 같은 화면이 보일 것이다. 


① 상단 머리글 부분이 필터를 적용한 것처럼 변한다. 

② F1셀부터 H9셀까지의 부분 중 아무곳이나 클릭을 하면, 최상단에 [표 도구]라는 메뉴가 활성화 된다.

③ [표 도구]를 활성화 한 상태에서, 제일 좌측에 있는 [속성]의 [표 이름]이 본래 '표1'로 되어 있을 텐데... 이 부분을 '학생부'라고 수정한다.


[그림12]



이제 [그림11]의 C5에 '학생 라'의 몸무게 값을 vlookup함수를 활용하여 불러와보자.

=vlookup(A5,$F$1:$H$9,2,false)라고 써도 되지만, =vlookup(A5,학생부,2,false)라고 보다 직관적으로 쓸수가 있다. 


혹은, 표로 만들지 않고... F1부터 H9를 선택한 상태에서 [그림13]처럼 '학생부'라고 이름지을 수도 있다. 이후에, [수식]탭의 [정의된 이름]섹션에 있는 [이름관리자]를 눌러보면 학생부가 생겨 있음을 알 수 있다.

이 경우에도, =vlookup(A5,학생부,2,false) 라고 쓰면 알맞은 값이 불러와질 것이다.


[그림13]



그렇다면, 표와 이름의 가장 큰 차이는 무엇일까?!


아래 2가지 차이가 있기 때문에, '표'는 컬럼이 많고 값이 주기적으로 업데이트 되는 데이터에 사용하는 것이 좋고 (ex.일간 현황표 같은...), '이름 정의'는 컬럼이 적거나 한시적으로 보는 데이터에 사용하는 것이 좋다. (ex.시트 별로 동떨어진 데이터를 합칠 때)


업데이트 : 표로 설정해둔 경우, F10셀에다가 '가'라고 쓰면 표가 자동확장 된다. 그러나 이름만 정의해둔 경우, 대상 범위가 확장되지 않는다.


머리글 : F1셀부터 H9셀까지 표기능을 이용하여 '학생부'라는 표로 지정한 다음... 아무셀에다가 =match(A5,학생부[[#모두],[학생]],0)라고 써보자. 뜻은... "학생부'라고 이름지은 표"에 "학생'이라는 컬럼(머리글)"이 있을텐데, 거기 데이터를 "모두" 뒤져서 A5(학생 '라')와 일치하는 값이 어딨는지 불러와라... 라는 의미이다.


즉, (대부분의 경우) 표로 만든 순간 가장 윗행의 컬럼명이 머리글로 지정되기 때문에... 데이터 불러오기를 할 때 영어/숫자로 이루어져 알기 어려운 셀주소를 적지않고 머리글을 쓰면된다. 반면, 이름만 정의한 경우 표처럼 선택한 영역을 하나의 데이터 세트로 인식하지 못하기 때문에 머리글이 자동 정의되지 않는다.



매크로

예를 들어, 위 [그림11]에서 A1셀부터 D15셀까지를 매일 복사해야 한다 치자. (예시라 데이터 범위가 작아서 그렇지... 만약 C3748셀부터 AS10245셀까지를 매일 복사해야 한다면?! 이럴경우 아래와 같이 해보자.)




[보기]탭의 [매크로 섹션]의 [매크로 기록(R)]을 클릭 → [바로 가기 키(K)]에 'Ctrl+빈칸'이라 써져 있을 텐데 빈칸에다가 a라고 써줌  [확인]클릭

② ①번 과정을 마치면... 일종의 "녹화"상태라고 볼 수 있다. 이제 A1셀부터 D15셀까지를 선택  Ctrl + C하여 복사하기  [보기]탭의 [매크로 섹션]의 [기록 중지(R)] 클릭

③ ②번 과정을 마치면... "녹화"를 끝냈다고 보면된다. 이제 Ctrl+a를 눌러보자. A1셀부터 D15셀까지가 선택 후 복사된 상태까지 자동으로 되는 것을 확인할 수 있다!!!



16. 자주까진 아니지만 알아두면 쏠쏠한 단축키/함수

이 부분은 생각이 날 때마다 업데이트 하려고 한다. 아무래도 자주 쓰는게 아니다보니 --;; 포스팅하는 순간에 다 쏟아낼 수가 없다 =_=;;


Ctrl + + : 현재 선택된 셀의 위로 혹은 왼쪽에다가 셀이나 행/열 전체를 삽입

Ctrl + - : 현재 선택된 셀 혹은 현재 선택된 셀이 소속된 행/열 전체를 삭제

=edate(날짜가 적힌 어떤 셀,1) : 어떤 날짜가 적힌 셀에서 월간만 +1달 해준다. 매월 일수가 다른데 자동으로 계산해주므로 편리하다. 1이 아니라 -1로 적어주면 이전달로 바뀐다.

=len(숫자나 글자가 적힌셀) : 숫자나 글자가 몇 자 적혀있는지 알려줌

=day(eomonth(날짜가 적힌 어떤셀,0)) : 해당월의 일수를 구할 수 있다. 날짜가 적힌 셀이 '15년 4월이라면, 4월의 일수인 30이 구해짐

=today() : 오늘날짜(연-월-일)가 구해짐

=min(숫자나 숫자가 적힌셀 여러개) : 선택된 값 중에서 가장 작은 값이 무엇인지 알려줌

=max(숫자나 숫자가 적힌셀 여러개) : 선택된 값 중에서 가장 큰 값이 무엇인지 알려줌

=median(숫자나 숫자가 적힌셀 여러개) : 선택된 값들의 중위수가 구해짐. 예를 들어, =median(10,3)라고 하면 6.5가 구해짐

=mode(숫자나 숫자가 적힌셀 여러개) : 선택된 값들의 최빈치가 구해짐. 예를 들어, =mode(1,2,2)라고 하면 2가 구해짐



>> 제 블로그의 내용이 도움 되셨다면, 아래 하트 버튼(공감UP) 한번씩 눌러주세요 ^ㅁ^ <<


Comments