안녕하세요?
올해 직장생활 15년차에 접어든 김경훈이라고 합니다.
https://www.linkedin.com/in/kyung-hoon-kim-55455387/
2004년 kt에 입사한 이후 4번을 이직해서 (NAVER, Accenture, 제일기획, eBay Korea)
현재 다섯번째 회사인 이베이코리아에서 직장생활을 하고 있습니다.
세번째 회사인 Accenture쯤 다니게 되면서,
(저는 Powerpoint로 슬라이드 덱을 만드는 것은 여전히 어려웠지만,)
Excel은 꽤나 잘 활용하는 축에 속한다는 것을 알게 되었습니다.
(Accenture와 같은 국내 컨설팅펌들은 사람을 극한의 상황으로 몰아넣곤 하는데,
이때 Powerpoint, Excel과 같은 기본 오피스 프로그램을 잘 활용하는 것은 본인 뿐만 아니라 프로젝트 팀 전체에 영향을 주게 됩니다.)
그래서, 제가 일하면서 체득한 몇가지 꿀팁들을 제일기획 재직 중에 한번 공유한 적이 있었는데 (2015년쯤..?)
반응이 나쁘지 않았습니다... ^^;
음.. 이 콘텐츠가 실무에게 많이 와닿았다고 생각하여,
여기에 한번 풀어놓고자 합니다. :)
제 생각에 이 내용을 읽으면 좋을 분들은...
보통 직장생활로 치면 3~5년차 정도로 스탭 조직 막내들... 혹은 클라이언트사 리포트 작성이 작은 영업조직 등등...
엑셀로 취합하는 일이 엄청 많으면서도
엑셀 실력이 sum, vlookup 에서 정체된 분들... 일 것 같습니다.
그냥... 대한민국 모든 엑셀 직장인들에게 이 글을 바칩니다.
오늘도 우리 미생이들 화이팅입니다!
[ 1 ] 우선, 소소한 내용부터 시작합니다.
[ 2 ] 간단한 단축키를 몇개 익혀봅시다.
(1) 시트간 이동 : CTRL - 페이지업/페이지다운 (* MAC에서는 CTRL - fn - ↑/↓)
(* 마우스로 시트명을 클릭하는 것보다 훨씬 "있어-보입니다". 실제 생산성도 높구요.)
(2) 데이터 사이 빠른 이동 : CTRL - 상하좌우 화살표
(3) 셀 수정 : F2를 누르면 마우스 더블클릭보다 빨라요
(4) CTRL - ` (숫자1 왼쪽 버튼) : 수식 펼쳐 보기
(* 수식이 걸려있지 않은 곳을 확인하거나, 수식이 잘못 걸려있는 내용 등을 쉽게 확인할 수 있습니다.)
(5) 기타 MS 오피스 공통사항인데, 여러개의 엑셀 파일이 열려있는 경우, CTRL-TAB을 눌러서, 엑셀 파일 간 이동이 가능합니다.
(* ALT-TAB은 전체 파일간의 이동인 것과 비교해보면 이해가 쉽습니다.)
(6) CTRL - Y 혹은 F4를 누르면 방금 했던 작업이 반복됩니다.
(* 색칠하기 버튼 계속 마우스로 클릭하는 것보다 커서 이동하면서 F4누르는 것이 훨씬 빠릅니다.)
혹시 손이 더 빠른 분들이 계시다면... 다음 팁도 유용할 겁니다.
(7) ALT-I-R : Insert Row 한줄 삽입
(8) ALT-I-C : Insert Column 한열 삽입
[ 3 ] 데이터 유효성 검사
이 메뉴는 까먹으면, 막상 생각이 잘 나지도 않고, 검색해볼 만한 키워드도 마땅치 않아서,
아... 그 옆에 화살표 박스로 선택할 수 있게 표시하고... 그거 뭐더라-? 라면서 많이 답답해질 수 있는 부분입니다. ^^;
바로 이 그림인데요... :)
가위/바위/보 게임을 한다면,
바보 와 같은 값은 입력을 받으면 안되겠죠...? (아재...)
이 경우에 필요한 제약 조건이 바로
데이터 탭 > "데이터 유효성" 메뉴 > 허용에서 [목록]으로 바꿔넣고
아래 소스에 넣고 싶은 값을 "가위","바위","보"와 같이 직접 타이핑해서 넣거나 해당 값이 있는 영역을 선택해주면 됩니다.
[ 4 ] 마케팅 이벤트 담당자라던가, 조직 내에서 공지할 일이 많은 스탭 조직에 있다면,
몇백개, 몇천개의 이메일을 한꺼번에 보내야하는 경우가 많을텐데요,
이메일 주소를 어떻게 하면 효율적으로 입력해서 보낼 수 있을까요?
일반적인 이메일 프로그램에서 이메일 주소를 a@b.com ; c@d.com 과 같이 세미콜론(;)을 사이에 넣으면 한꺼번에 넣을 수 있다는 특징을 이용해서 편하게 일을 해볼 수 있습니다.
우선, 그냥 한번 따라해봅시다.
첫번째 이메일 주소 바로 오른쪽에 = 버튼을 누르고, 왼쪽 화살표를 눌러서 아래와 같은 수식을 만듭니다.
자, 한번 더 저를 믿고 다시 따라해봅시다.
그 다음 줄에서 = 버튼을 누르고, 윗쪽 화살표를 누른 다음에, &";"& 라는 외계어를 입력하고, 왼쪽 화살표를 눌러서 아래와 같은 수식을 만듭니다.
자... 느낌이 오시죠?
이제 뭘 하면 될까요?
C6번 셀의 오른쪽 아래 콩알같은 부분을 더블클릭하면
Voila~! C10번 셀에 우리가 원하는 결과가 나왔죠?
C10번 셀을 복사해서 이메일프로그램 주소 창에 넣고 엔터를 치면... 한번에 이메일 발송을 할 수 있겠죠? :)
[ 5 ] 숫자 쉽게 이해하기
엑셀로 수많은 실적을 관리해야하는 우리들 입장에서는...
빠르게 차트를 그리는 역량도 중요하지만,
차트 띄우는데 시간도 오래걸리고, 데이터 따로 차트 따로 있는 것도 보기 싫은 경우가 있습니다...
그렇다고 위와 같은 포맷에서 숫자를 눈으로만 보고 느끼...?는 것도 쉬운 일은 아닙니다.
이때, 정말 간단하면서 빠른 방법이 있습니다.
바로, 홈탭에 잘 보시면 중간 쯤에 "조건부 서식"이 있습니다.
보고자 하는 영역을 선택한 다음에, 이 조건부 서식을 선택하기만 하면
막대그래프 만큼 정확하지는 않더라도 이와 같이 한 눈에 숫자를 느낄...? 수 있습니다.
이렇게 약간은 heat-map 스타일로 만들어볼 수도 있구요-
숫자만 줄줄이 적혀있는 보고서보다,
이와 같은 간단한 트릭으로 한결 직관적으로 이해하기 쉬운 엑셀 보고서를 만들 수 있겠죠-
[ 6 ] VLOOKUP과 INDEX(MATCH())의 비교
보통, VLOOKUP을 통해서 위와 같이 원빈이 속한 팀을 쉽게 구할 수 있습니다.
그런데, 문제는... 가끔 원본 데이터 중에 아래와 같이 찾고자 하는 조건과 데이터가 좌우가 바뀐 경우가 있습니다.
이런 경우에는 VLOOKUP으로 원하는 검색을 할 수 없는데요...
어떻게 하면 좋을까요?
... 가장 화끈한 해법으로는,
원본 데이터의 좌우를 다시 바꾼다...는 콜롬버스의 달걀 급의 솔루션을 내세우는 분들도 있습니다.
(*존중합니다.)
... 그런데, INDEX와 MATCH라는 함수를 섞어서 쓰면, 이렇게 좌우가 뒤바뀐 경우에도 원빈의 팀을 찾을 수 있습니다.
=INDEX(찾는위치배열, MATCH(찾는값, 찾는위치 줄, 0), 결과값)
이와 같이 적용하는 것인데... 사실 이 부분은 막상 이해가 어렵습니다.
오늘은 INDEX와 MATCH라는 함수를 조합하여 해결할 수 있다-라고만 이해하시고,
이는 반드시 필요한 경우에 이 글을 다시 보면서 따라해보시는 것을 추천합니다-!
[ 7 ] 오류 발생 항목을 IFERROR로 깔끔하게 정리하기
김제동, 강다니엘, 김동률이 속한 팀을 VLOOKUP을 통해서 찾아봤는데요, (=VLOOKUP(E13,$C$5:$D$9,2,FALSE))
김다니엘은 원본 데이터에 없으므로, #N/A라고 약간 보기 싫은 형태로 나타납니다.
이런 경우에... IFERROR로 감싸주면 깔끔하게 정리할 수 있습니다.
즉, 위 VLOOKUP함수를,
=IFERROR(VLOOKUP(E13,$C$5:$D$9,2,FALSE),"")와 같이 정리한다면
이와 같이 깔끔하게 정리할 수도 있고,
=IFERROR(VLOOKUP(E13,$C$5:$D$9,2,FALSE),"채용 반드시 필요") 라고 정리한다면
오류메시지 대신 "채용 반드시 필요"하다는 메시지를 담을 수도 있겠네요-
이와 같이 IFERROR라는 간단한 함수를 통해 #N/A와 같은 오류메시지를 깔끔하게 클렌징하시기 바랍니다.
[ 8 ] 중복 조건
COUNTIF라는 함수를 쓰면 어떤 조건에 해당하는 갯수를 셀 수 있습니다.
=COUNTIF(E:E,">=80%")
그런데, 조건이 2개 이상이 된다면 어떻게 할 수 있을까요?
countifS라는 함수가 있습니다. :)
범위, 조건, 범위, 조건... 같은 형태로 계속 적어나가면 됩니다.
=COUNTIFS(E:E,">=80%",D:D,"내가제3잘나가팀")
[ 9 ] 엑셀 차트 보조축
생각보다 보조 축의 존재를 모르는 분들도 많고,
저도 차트 자체를 자주 쓰지 않다보니 막상 메뉴 위치를 잘 못 찾을 때가 많아서
여기에 한번 정리합니다.
광고 리포트 만들다보면, 노출량과 클릭율은 단위 자체가 다르기 때문에, 클릭율은 바닥에 깔려버리는데요,
데이터계열서식 > 계열옵션 > 보조축 만 설정해주면
우리가 원하는 트렌드를 모두 확인할 수 있습니다.
[ 10 ] 빈 칸만 선택해서 윗 셀 값 채워넣기
제가 가장 아끼는 꿀 팁이라고 할 수 있는데요...
팀 막내일 때 많이 하는 작업인데, 자료를 이래저래 취합하고 나면,
아래와 같이 셀 병합된 경우들이 모이면서 Pivot도 잘 되지 않고 개수 집계도 어려운 경우를 많이 겪게 됩니다.
보통 이런 일을 겪고 나면 ...
조직간의 업무 프로세스의 표준화에 대해서 얘기하기 시작하고;;;
저 팀의 막내는 일을 잘 못한다- 등등 조직간의 불필요한 잡음들이 발생하게 되는데요...;;;
굳이 싸울 필요 없이 깔끔하게 취합하는 사람이 해결하는 방법이 있습니다!
[ 병합풀기 > 홈 > 찾기 및 선택 > 이동 > 옵션 > 빈셀 > =위쪽커서 > CTRL-ENTER, 값 복사 ]
입니다.
묻지도 따지지도 말고 차근차근 따라해보겠습니다.
병합을 풉니다.
홈 탭 가장 오른쪽에 '찾기 및 선택'을 누르고 '이동' 을 누르고, '옵션'에서 '빈셀'을 선택합니다.
그럼, 이와 같이 빈 셀 들만 선택되고,
이 상태에서 조심조심 따라합니다.
=을 입력한 다음에 위쪽 화살표를 한번 누릅니다.
이 상태에서 [CTRL-ENTER]를 누릅니다.
짜잔~
이제 PIVOT이건 COUNTIF건, 다른 팀과 싸울 필요 없이 내가 원하는대로 편집할 수 있겠죠?
(* 아직 함수 상태이므로, 값을 다시 복사해서 넣는 것을 추천합니다.)
이외에도 몇가지 더 있긴 한데-
일단 이정도만 따라오셔도,
우리 각 팀 막내분들의 퇴근이 분명히 2시간 이상 앞당기셨으리라 믿습니다. ^^;
이외에 혹시 더 궁금하신 점 있으시면 질문 남겨주세요~
제가 아는 건 꼭 알려드리도록 하겠습니다. ^^
우리 미생 직장인 모두 화이팅입니다!
감사합니다.