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

엑셀 단축키와 함수 부분을 보려고 오신 분은...

아래 잡설을 스크롤로 휙휙 스킵해 주세요 =_=.... 

첫 직장에(and 현직장인...) 마케팅 직군으로 합격하여,

서비스쪽 이벤트/프로모션 직무가 아닌... 데이터 분석 직무를 할 수 있는 부서에 지원을 했었다.

내 성격 자체도 활동적이기보다 조용하고, 무언가 꼼꼼하게 나누어 보고 분류하는 걸 좋아하는 지라...ㅎㅎ...

해서 가게 된 곳이 고객분석팀이었는데,

선임분께 받은 첫 임무(?)가 자사 서비스의 현황표를 작성하는 것이었다.

그 흔하다는 모스 자격증 하나 없이, 내가 보기엔 운좋게 합격을 한지라 -_-;; (해외연수,봉사활동,인턴경험,자격증 ...아무것도 없음... ㅠ) 현황표 엑셀 파일은 나에게 카오스 그 자체였다.

정말로 심각했던 것이... 시트가 뭐고 셀이 뭔지도 몰랐고;; 셀 안에 글자를 타이핑 할 수 있다는 것도 몰랐다. 말 그대로 엑셀은 익스플로러 실행하려다 잘못 눌러서 실행한 적 있는 프로그램이었던 것이다 -_-;;

물론, 아무 생각없이 분석팀에 지원한 것은 아니었고,

학부 시절 심리학을 전공하면서... 소비자 심리에 대해 나름대로 분석해봤다는(+SPSS를 돌려볼줄 안다는) 알량한 자신감이 있어서였다.

---

작년 미생이라는 드라마가 화제였는데, 참 공감이 되었던 것이... 

회사 혹은 업무에서 쓰이는 용어(특히 줄임말과 영어표현 --;;ㄷㄷ). 그리고, 회사 내에 통용되는 문서 양식과 그를 준수하는 작성 기술이... 회사에서 생존하기 위해선 필요하다는 것이었다.

(처음엔 못하더라도 노력해서 체득해야함...)

그러나, 당시 난... 

고객분석팀원으로 요구되는 각종 지표 정의도 몰랐거니와, 엑셀의 'ㅇ'도 모르는 풋내기였던 것이다!!!! ㅠㅠㅠㅠ 회사가 신입사원에게 많은걸 원하진 않는다 하더라도, 내 스스로가 겪었던 엑셀과의 첫 조우(=카오스)는 아직도 생생하다.

선임분께 현황표 작성 방법을 설명 듣고(노트에 열심히 받아적음)... 엑셀로 직접 현황표를 작성한날, 퇴근시간 까지도 쩔쩔매는 바람에 결국 그날 보고에 실패했다. 본래 9시까지 출근해서 오전 10시 전후로는 전사 임직원에게 메일을 보냈어야 했는데 그렇지 못한 것이다!! 충격을 먹은 나는 바로 인터넷 엑셀 강좌를 신청하고, 입사 첫 1~2달간 퇴근시간 이후에 자습(?!!)을 했다.

이후 수 많은 시행착오를 겪으며 (답답한 나를 기다려주시고 도와주신 선임분, 동기분들 진심 감사합니다ㅎㅎㅎㅎㅎ) 아래와 같이 신입사원을 대상으로 강좌까지 하게 되었다.

대단한 것은 절대 아니고... 내가 겪었던 고통과 그를 해결해주는 간단한 엑셀 단축키/함수에 대해 알려주는 수준이었는데, 나름 강의평이 좋아서 이후에도 서너번 사내 강의를 하게 되었다. 

엑셀의 세계는 정말 무궁무진해서, 아직도 엑셀의 'ㅇ'자도 제대로 안다 말하기 어렵지만.... 초급 수준의 단축키와 함수를 공유해보고자 한다.

(엑셀 관련 내용보다 사설이 더 길었던거 같은...................ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ;;;;)

혹시 다른 내용이 궁금하신분은 아래를 클릭하시면 됩니다.

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

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

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

---

(아래 내용은 엑셀 2010버젼을 기준으로, 개인적으로 자주 쓰는 것들만 작성하였습니다. 업무에 따라 자주 쓰는 기능이 다를 수 있습니다~~)

1. 셀 이동 / 선택하기

ⓐ Ctrl + 방향키 : 확확 이동

ⓑ Shift +방향키 : 선택하면서 이동

ⓒ Ctrl + Shift + 방향키 : 확확 선택하면서 이동

2. 셀 내용(수식) 작성 / 수정하기

ⓐ 원하는 셀에서 키보드의 '='(등호)을 누름 : 작성 시

ⓑ 키보드의 F2를 누름 : 작성 혹은 수정 시

ⓒ Shift + 방향키로 여러 셀범위를 선택하고, 2-ⓐ or ⓑ로 수식을 작성할 경우... Ctrl + Enter로 일괄 수식 적용이 가능

3. 복사한 셀 내용 붙여넣기 옵션

Ctrl + C → Ctrl + V 하면, 작은 옵션창이 우측하단에 생기는데, 이 때

ⓐ Ctrl + V : 값만 붙여넣기

ⓑ Ctrl + T : 행열바꿔 붙여넣기

4. 절대 참조 헷갈리지 않고 걸기

예를 들어, =X12라고 정의된 셀이 있다치자. 이때, F4를 누르면 =$X$12로 바뀐다. 

이게 무슨 의미냐면... (난 아래와 같이 이해/암기 했다.)

$(달러)가 무엇인가? 돈이다!!... 즉, 엑셀의 셀이 뇌물(돈)을 먹어서인지 절대 안움직이겠다는 건데, 열번호인 X열도 돈을 먹고, 행번호인 12행도 돈을먹어서... 행열을 모두 고정하겠단 의미가 된다.

F4를 한번 더 누르면 =X$12로 바뀐다.

즉, 행번호인 12행만 돈을 먹었으므로... 행만 고정하고 열정보는 바뀔 수 있다.

5. 수식 작성시 깨알상식

ⓐ '(작은따옴표) : 무조건 텍스트로 인식하게 해주는 마법가루

ⓑ & : 접착제

ⓒ " "(큰 따옴표) : 글자를 모르는 엑셀로부터 텍스트를 보호해주는... 일종의 보호막

예를 들어, 어떤 셀에 '가갸거겨'라고 쓰고 싶다면... (아래 그림1) 아래와 같이 4가지 방식이 있다. 어떨 때 어떤 방식을 쓰느냐는... 케바케라... 경험으로 쌓아야 된다 ㅠㅠ

① F2를 눌러 그냥 타이핑 : 데이터 구분/제목 달 때

② '(마법가루)를 찍고 타이핑 : 데이터 구분/제목이 숫자일때

③ 특정셀에 가갸거겨를 그냥 타이핑해두고, &(접착제)로 붙이기

④ " "(보호막)으로 글자를 보호해주고, &(접착제)로 붙이기

: ③과 ④의 경우, 이미 있는 글자를 조합해서 제목을 달고 싶을 때

[그림1]

6. Ctrl 콤보키 (위 3번도 Ctrl 콤보키의 일종임)

ⓐ Ctrl + N : 새문서

ⓑ Ctrl + S : 저장하기

ⓒ Ctrl + F : 찾기

ⓓ Ctrl + H : 찾아서 바꾸기

ⓔ Ctrl + G : 특정셀의 메뉴를 원하는 대로 이동하기

예를 들어, 아래 그림2의 우측표처럼 빈 구분1을 채우고 싶다고 치자.

예시라 셀이 몇개 안되서 손으로 복붙하는게 훨 빠르지만 (이게 빠르다면 당연히 손으로 복붙해야 한다!!) 만약 데이터가 엄청나다면?!

[그림2]

A2셀부터 A15까지 선택 → Ctrl + G → 이동메뉴가 뜨면 하단 [옵션(S)...] 버튼 클릭 → [빈 셀(K)] 옵션을 선택 후 확인 → (구멍뚫린 부분만 선택된걸 확인하고)=A2 라고 셀정의 → Ctrl + Enter

7. Alt 콤보키

ⓐ Alt + W + F + F : 틀 고정

ⓑ Alt+ D + F + F : 필터 (Ctrl + Shift + L 혹은 Alt + A + T 도 있지만, 틀 고정과 함께 외우고자...)

ⓒ Alt + M + U + S : 합계 (합계를 내고 싶은 셀들 우측 혹은 하단에서 할 것)

ⓓ Alt + M + U + A : 평균 (평균을 내고 싶은 셀들 우측 혹은 하단에서 할 것)

8. 조건부 서식 : 데이터의 크고 작음을 빠르게 파악해야 할 때

그렇게 보길 원하는 데이터를 모두 선택한 상태에서, (선택된 데이터 내 최소/최대값을 따져 데이터의 대소가 구분됨)

ⓐ 최상단 탭 중 [홈]클릭 → 스타일 섹션 중 [조건부 서식] 클릭 → [데이터 막대] 클릭 → 원하는 스타일 선택

ⓑ 최상단 탭 중 [홈]클릭 → 스타일 섹션 중 [조건부 서식] 클릭 → [색조] 클릭 → 원하는 스타일 선택

9. 텍스트 나누기와 date/left/right/mid 함수

개발자가 데이터를 추출해서 줄 경우, 대부분 텍스트파일이다. (.txt , .csv 등등...) 예를 들어, 아래와 같은 데이터를 받았다고 치자. (그림3)

[그림3]

ⓐ 우선, A1부터 A13까지를 선택 → [데이터]탭 클릭 → 데이터도구 섹션의 [텍스트나누기]클릭 → [구분 기호로 분리됨] 선택 후, [다음(N)] 클릭 → [기타(O)] 선택 후, 우측에 '|'(파이프)를 기입하고 [다음(N)] 클릭 → 미리보기 화면을 확인 후, [마침(F)] 클릭

위 ⓐ과정을 마치고 나면 A열에서 C열까지 3개 컬럼으로 잘 나누어진다. 그러나, 문제는 A열의 date 정보가 말그대로 날짜가 아닌, 숫자로(20,110,901) 인식될 것이다. 따라서, 아래와 같이 함수를 써줘야 한다.

천천히 해보자. A열~C열까지 나누어진 상태에서, A열과 B열 사이에 빈 열 하나를 삽입한다.

ⓑ date함수 =date(년,월,일) : 년은 ⓒleft함수로 정의하고, 월은 ⓓmid함수로 정의하고, 일은 ⓔright함수로 정의한다.

ⓒ left함수 = left(텍스트 적힌 셀, 몇 자를 자를 것인가) : 예제에 적용하면, 20110901에서 왼쪽 4개를 잘라 연도를 만들어야 하므로 =left(A2,4)가 될 것이다. 

ⓓ mid함수 = mid(텍스트 적힌 셀, 몇 번째 글자부터, 몇 자를 자를 것인가) : 예제에 적용하면, 20110901에서 5번째 글자인 0부터 2개를 잘라 월을 만들어야 하므로 =mid(A2,5,2)가 될 것이다.

ⓔ right함수 = right(텍스트 적힌 셀, 몇 자를 자를 것인가) : 예제에 적용하면, 20110901에서 오른쪽 2개를 잘라 일을 만들어야 하므로 = right(A2,2)가 될 것이다.

최종적으로, 

=date(left(A2,4),mid(A2,5,2),right(A2,2)) 로 작성하면...  숫자였던 20,110,901이 날짜인 2011-09-01로 되는 걸 볼 수 있을 것이다!!

10. 피벗

피벗은 엑셀의 꽃이라 할 수 있는데, 만드는 것 자체는 간단하나 어떻게 동적으로 이리저리 활용하느냐는 경험을 많이 해볼 수 밖에 없다. 

ⓐ 피벗 삽입하기 : 만들 대상이 되는 raw데이터를 선택한 다음 [삽입]탭의 [표]섹션의 [피벗 테이블]을 클릭하면 된다. 자주 써먹을 수 있는 것 위주로, 팁을 주자면 아래와 같다.

ⓑ 값 업데이트 : ⓐ에서, raw데이터를 선택할 때... 데이터 부분만 선택하지 말고, 열 전체를 선택해서 만들어 두는 것이 좋다. 

예를 들어, 위 [그림3]을 가지고 피벗을 만들 때, A1부터 A13만 선택하지 말고 A열 전체를 선택해두면, A14에 새로운 값이 생겼을 때 피벗이 동적으로 변화할 수 있다.

raw데이터가 변동 되거나 추가 된 경우, 그려진 피벗테이블에서 마우스 우측클릭 후, [새로 고침(R)]을 클릭

ⓒ 날짜 그룹핑 : 예를 들어, 아래와 같은 데이터를 가지고 피벗을 만들었다 치자. 거의 모든 데이터의 경우, [행 레이블]에 일자를 넣고 [값]에 평균/합계 등을 낼 지표를 넣는다.

[그림4]

이후, 행 레이블에 커서를 대고 우클릭 → 그룹(G) 클릭 → 단위(B)에서 월과 연을 모두 선택하고 [확인] 클릭 → 10개의 raw데이터가 2011년 9월 4개, 12월 6개로 그룹핑된다!! 

현재 값의 지표가 '개수'로 되어 있는데, F2열을 더블클릭하면 [값 필드 설정]창이 뜬다. → [값 필드 요약 기준(S)]를 개수가 아닌 평균으로 바꿔주면 그룹 별로 평균치가 나오게 될 것이다!!

ⓓ 슬라이서 삽입 : 피벗 테이블 영역을 아무곳이나 클릭 → [피벗 테이블 도구] 메뉴 - [옵션]탭 클릭 → [정렬 및 필터] 섹션의 [슬라이서 삽입] 클릭 → 원하는 컬럼 선택

예를 들어, 아래와 같은 데이터를 피벗으로 만든 경우, 성별과 같은 컬럼은 피벗의 필터 기능이 아니라 슬라이서로 삽입해 클릭해 보는게 편할 때가 많다. 

(참고. 남성과 여성을 함꺼번에 선택하고 싶을 경우, Ctrl를 누른 상태에서 클릭하면 됨)

[그림5]

---

금번 포스팅이 길어져, 아래 부분은 다음 포스팅에서 ..... -_-;;

11. IF함수와 응용 (sumif/sumifs/averageif/averageifs)

12. 특정셀 불러오기 1탄 (vlookup함수)

13. 특정셀 불러오기 2탄 (match함수와 index함수)

14. 드랍박스 (데이터유효성 검사)

15. 스크롤바 넣은 동적 현황표 만들기

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

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