brunch

매거진 직장을즐겁게

라이킷 265 댓글 11

You can make anything
by writing

C.S.Lewis

계정을 잊어버리셨나요?

by 김형석 Apr 19. 2018

직장인, 엑셀을 잡아라

#직장을즐겁게 #15

직장인에게는 쌍칼이 필요하다. 한 손엔 PPT, 다른 손에는 엑셀이 그것이다. 간혹 'PPT를 쓰지 않는다'고 강조하는 회사가 있는데, 워드나 엑셀을 희한하게 변형해서 쓰고 있는 경우가 많다. 이럴 바에는 그냥 PPT를 간소하게 만들도록 하는 것이 더 나을수도 있다.

그러나 '엑셀을 쓰지 않습니다!'라고 강조하는 회사는 아직까지 보지 못했다. 엑셀은 그만큼 중요하고, 거의 모든 회사에서 광범위하게 사용되기 때문이다.

그리고 그 중요도에 비해 엑셀을 잘 하는 사람들은 정말로 그 숫자가 적다.

1. 엑셀을 잘한다는 것에 대한 오해: 자기과시형 엑셀가

직장에서 '엑셀가'로 통하는 사람들은 조금 이상하게 엑셀을 쓰는 경우가 많다.

- 엑셀을 굉장히 복잡하고 무겁게 만들어 사용한다

- 중요한 지표를 꼭꼭 숨긴다

- 숫자와 그래프만 있고 Insight는 행방불명이다

- 무엇을 분석하기 위한 엑셀인지 애매하다

- 주기적으로 업데이트하는데 첫 시트 외에는 아무도 제대로 살펴보지 않는다

'엑셀을 연구하는 사람들'의 비밀조직이 어딘가 있어 직장마다 위장 침투조를 보내는 것이 틀림없다.

2. 엑셀은 결국 무엇인가를 알고 싶기 때문에 쓰는 툴이다

PPT가 커뮤니케이션의 툴이라면 엑셀은 '분석'의 툴이다. 영화가 아닌 현실에선 숫자를 보고 바로 이해하기는 어렵다. 엑셀을 통해 데이터를 해석하고 인사이트를 얻는 것이 필요한 이유다.

따라서 엑셀을 잘 한다는 것은 목적-추출-분석 3단계를 잘한다는 것을 의미한다.

- 목적: 알고 싶은 것이 무엇인가?

- 추출: 어디서 필요한 데이터를 추출하는가?

- 분석: 이 데이터가 무엇을 의미하는가?

왜 만들었는지 목적이 불분명하거나, 불필요한 raw data와 함수 및 참조를 많이 써서 파일을 무겁게 하거나, 해석이 담겨있지 않는 엑셀은 분명히 문제가 있다.

3. 추출의 중요성: Garbage in Garbage out

대학에서 통계학 교수님이 가장 강조했던 것은 '쓰레기를 넣으면 쓰레기가 나온다'는 것이었다. 수업시간에 배운 모든 것은 어차피 다 잊어버릴 테니 꼭 기억하라고 당부하셨다.

- 쓰레기 데이터를 돌려도 뭔가 그럴듯한 해석이 나온다

- 그러나 그 해석이 실제로 의미있는 것은 아니다

- 따라서, raw data를 제대로 확보하는 것이 모든 분석의 시작이다

굉장히 많은 회사에서 raw data 추출을 막내(쥬니어 of 쥬니어)에게 시킨다. 이건 정말로 위험천만한 일인데, 그 이유는 대략 다음과 같다.

첫째, 데이터가 쌓여있는 구조를 모르는 사람은 필요한 데이터를 제대로 추출 요청을 하기 어렵다

둘째, 알아서 적당히 추출해 달라고 하면 Garbage in 상태에 들어간다

셋째, 잘못 추출된 데이터를 가지고 아무리 분석해도 손에 남는 것은 Garbage일 뿐이다

따라서 raw data 추출은 시니어가 직접 하거나, 정말로 믿을 수 있는 사람에게 시키는 것이 필요하다. 그리고, 자료를 받으면 분석 전에 제대로 뽑혔는지를 반드시 확인하는 것이 좋다. 가령, 매출 데이터를 뽑았다면 지난 달 전체 매출이 얼마인지 맞춰본 후 세부 분석을 시작하는 것이다. 보통 잘못 뽑힌 데이터는 20-30%가 아니라, '0'단위가 차이가 나기 때문이다.

4. 피벗테이블과 V-lookup

서점에 가면 엑셀책이 굉장히 많은데 굉장히 두껍고 많은 내용을 다룬다. 그러나 실제로 직장 엑셀의 90%는 피벗테이블과 V-lookup이라고 해도 과언이 아니다.

1) 피벗테이블

피벗테이블이 중요한 이유는 '피벗테이블을 쓰는 방법' 자체보다는, '피벗테이블을 사용할 수 있는 포맷'으로 애초에 raw data를 추출해야 한다는 점에 있다.

이렇게 이야기하면 빵터지는 분들이 있고, '그게 무슨 의미지?'라고 생각하는 분들이 있을 수 있다.

피벗테이블을 사용하기 위해서는 엑셀파일 안에 최소한 한 개 이상의 raw data 시트가 존재해야 한다. 그리고 이 시트는 '눈에 보기 좋게' 정리된 것이 아니라, '피벗테이블을 돌릴 수 있는' 형태로 정리되어야 한다. 가령 제품별 월별 판매 데이터를 정리해야 하는 경우를 생각해보자.

Option 1:

제품 - 2018/02 - 2018/03

냉장고 - 200,000,000 - 300,000,000

TV - 500,000,000 - 400,000,000

Option 2:

Year - Month - 제품 - 판매금액

2018 - 02 - 냉장고 - 200,000,000

2018 - 02 - TV - 500,000,000

2018 - 03 - 냉장고 - 300,000,000

2018 - 03 - TV - 400,000,000

심하게 이야기해서 열 명 중 아홉명은 Option 1의 형태로 추출해 달라고 한다. 월별로 시계열 패턴을 보기 편하고 바로 그래프도 그릴 수 있기 때문이다. 그런데 이와 같은 형태로 raw data를 뽑으면 피벗테이블을 돌릴 수가 없게 된다. 만약 월별 매출이 아니라 분기별 매출을 뽑아야 한다면? 작년 매출을 물어본다면? 작년과 올해 1분기의 YoY를 비교해 보고 싶다면?

Option 1의 경우 그때마다 새로 노가다를 하거나 추출요청을 다시 해야한다.

그러나 Option 2는 새로운 요청이 있을 때마다 피벗테이블로 간단하게 처리할 수 있다. 총 판매금액을 알고 싶다면 제품을 선택하지 않고 판매금액만 넣으면 되고, 전년과 올해 1월 매출만 알고 싶으면 해당 월만 옵션에서 선택하면 된다. 연도별 매출, 분기별 매출 시트가 필요하면 raw data 시트를 기반으로 피벗테이블 시트를 각각 만들면 된다. 만약 raw data에 오류가 있어 데이터를 수정했다면? 수정 후 raw data 업데이트 버튼을 클릭하면 모든 시트에 한 번에 변경내용이 반영된다.

피벗테이블을 제대로 활용하기 위한 모든 내용은 검색하거나 유투브 동영상으로 널려있다. 중요한 것은 피벗테이블을 활용할 수 있는 포맷으로 raw data를 추출하는 것이 왜 중요한지를 이해하는 것이다.

2) V-lookup

면접을 볼 때 엑셀을 잘하는지를 물으면 열 명 중 아홉명은 잘한다고 답한다. 그러면 피벗테이블과 V-lookup을 할 수 있는지 물어본다. 망설이는 눈빛이 있을 수 있고, '나를 뭘로 보고'로 답하는 사람이 있다. 차라리 전자의 경우라면 (뽑아놓고 가르치면 되니까) 괜찮은데 후자의 사람들에게는 반드시 추가 질문을 한다.

V-lookup은 언제 사용하게 되나요?

V-lookup을 할 수 있다고 답한 사람 중에 이 질문에 대답하지 못하는 사람들이 의외로 상당히 많다. 앞으로도 엑셀실력이 가장 늘지 않을 가능성이 높은 사람들이다.

V-lookup은 '서로 다른 시트에 있는 데이터를 결합하기 위해 사용'한다. 만약 여기에 'key값을 기준으로'라는 말을 하면 엑셀에 대해서는 더 물어보지 않아도 된다.

Sheet 1

회원번호 - 이름 - 핸드폰 - 주소

K001 - 홍길동 - 01011112222 - 분당동112

K002 - 김철수 - 01022223333 - 송파동223

K003 - 최미미 - 01033334444 - 마포동 331

Sheet 2

회원번호 - 회원등급 - 전년도구매액

K001 - VIP - 1,000,000

K003- Silver - 300,000

K007 - Gold - 500,000

Sheet 1과 Sheet 2에는 서로 다른 데이터가 담겨 있다. Sheet 1에서 Sheet 2에 있는 회원등급이나 전년도 구매액 데이터를 불러와야 할 때가 많은데 이 때 V-lookup을 사용하게 된다. Sheet 1과 Sheet 2에 공통적으로 있는 Key값인 회원번호를 기준으로 서로 다른 시트에 있는 데이터를 불러오게 되는 것이다.

보통 "=Vlookup(Key값이 있는 셀, 참조할 데이터 범위, 키값을 기준으로 몇 번째 열에서 데이터를 불러올 지, 0"의 형태로 구성되는데 자세한 내용은 네이버나 다음에 물어보면 된다.

V-lookup이 중요한 이유는 'Key'에 대한 이해이다. 가령 위의 예시의 경우, 굉장히 간단한 내용이지만 의도적으로 이름을 키값으로 하지 않고 회원번호를 키값으로 설명했다. Key의 경우, 중복되면 안되는데 이름은 동명이인이 있을 수 있기 때문이다.

피벗테이블이 데이터를 추출하는 '방식'을 이해하는데 필요하다면 V-lookup은 애초에 데이터를 '어떤 기준'으로 쌓아야 하는지를 이해해야 한다. 하나의 테이블에 모든 데이터를 넣는 것은 비효율적이다. 너무 무거워질 뿐 아니라, 의도적으로 분리해야 하는 경우도 많기 때문이다. 가령 위 예시의 경우에 Sheet 1은 개인정보를 담고 있고, Sheet 2는 (개인정보가 아닌) 등급과 구매액 정보만 있다. 개인정보는 분리해서 저장하거나 따로 관리해야 하는데 이럴 때 나중에 해당 데이터를 맵핑할 수 있는 Key를 염두에 두느냐 그렇지 않느냐는 큰 차이를 낳게 된다.

5. 잡다한 함수들과 매크로

엑셀에는 정말로 수 많은 함수들이 있지만 기억해야 할 것은 하나다.

함수 자체가 아니라,

필요한 함수를 검색하는 방법을 익힌다.

네이버, 다음, 구글, 유투브에 믿을 수 없을 만큼 많은 설명들이 담겨 있다. 함수를 기억하는 대신, 필요한 함수가 있을 때 그 함수를 어떻게 검색하면 되는지의 방법들만 익히면 된다. 자주 쓰는 함수는 굳이 의식하지 않아도 자연스럽게 그냥 외워지고, 한 번 외워진 함수라도 잘 안쓰다 보면 또 금방 잊어버리게 되는데,

그냥 그렇게 두면 된다.

그리고, 매크로나 VBA의 경우 어지간히 특수 업종이 아닌 이상 대부분의 직장인에게는 필요가 없는 경우가 많다. 엑셀로 무엇을 하고 싶은지, 어떻게 raw data를 추출할 것인지, 피벗테이블과 V-lookup 정도를 확실하게 이해하고, 나머지 함수는 그냥 필요할 때 검색하는 것으로 많은 것이 해결된다.

6. 그래프

피벗테이블, V-lookup 외에 엑셀을 좀더 잘하고 싶다면 그래프는 좀더 고민할 필요가 있다. 숫자 자체로는 알기 어려운 것들이 그래프로는 굉장히 쉽게 설명이 되는 경우가 많기 때문이다. 여기에도 몇 가지 팁이 있다.

1) 막대그래프, 꺽은선 그래프만 잘 써도 90%는 해결된다

엑셀에는 또 수없이 많은 그래프가 있는데, 그냥 막대그래프와 꺽은선 그래프만 잘 써도 대부분 해결된다. 오히려 좀더 중요한 것들은,

- 언제 막대그래프를 쓰고, 언제 꺽은선 그래프를 쓸 것인가

- 누적 막대그래프의 경우 어떻게 데이터를 '쌓을' 것인가와 같은 것들이다.

2) 이중축 같은 것은 왠만해선 쓰지 않는다

이중축을 남발하는 사람들이 많다. 심지어 이중축을 해 놓고 각 축이 의미하는 것을 적어놓지도 않는다(뭐가 매출이고 뭐가 영업이익인지 물어보면 본인도 헷갈려한다). 이중축을 반드시 써야 할 경우가 있지만, 대부분의 경우 그냥 두 개의 그래프로 만들어 나란히 배치하는 것이 더 효과적이다.

3) 스캐터 그래프를 잘 쓰면 인사이트를 얻는데 굉장히 도움이 된다

스캐터 그래프는 가로 세로 축이 있고, 각 좌표를 점으로 찍어주는 그래프다. 데이터의 패턴을 직관적으로 살펴보는데 굉장히 편하다. 전체적인 패턴을 스캐터 그래프로 살피고, 가로 세로 기준축의 범위를 바꾸어 특정 부분을 좀더 자세히 살펴보면 효과적이다.

한 가지 추가적인 팁이 있다면, 각 점이 의미하는 라벨을 일일히 붙여넣느라 노가다를 하는 경우가 있는데 그럴 필요가 없다. 구글에서 검색해보면 엑셀 플러그인 중에 스캐터 좌표를 자동으로 그래프에 표시해주는 것들이 나온다(게다가 무료다). 오히려 문제는 너무 많은 좌표를 전부 다 자동으로 찍어줘서 문제인데, 이럴 때도 요령이 있는데 이 정도는 스스로 생각해 보자.

7. 호기심 많은 분들을 위한 알뜰신잡

1) IFERROR

예전엔 ISERROR 함수를 많이 썼는데 IFERROR가 나와서 더 편해졌다. 언제 쓰는가 하면, V-lookup 함수를 했을 때 참조하는 셀에 해당 키값이 없어 에러표시가 날 때다. IFERROR 함수를 쓰면 에러표시 대신 특정 값을 대신 넣어주게 되는데 일반적으로 '0'을 많이 넣는다. 왜 0을 넣을까?

Error가 뜨면 Sum이 안된다. 0을 대신 넣으면 Sum이 가능하기 때문이다.

2) 특정 셀의 값에 따라 해당 행 전체에 색깔 넣기

가령 각 행이 하나의 확인 필요사항이고, 열 중에 하나가 우선순위(1~5)라고 하자. 이 때 우선순위가 가장 높은(우선순위가 1인) 행만 색깔을 다르게 표시할 수 있다. 비슷한 용도로 우선순위말고 '진행중'과 '완료'를 구분하여 색깔을 넣을 수도 있다. 방법은 검색하면 널려 있으니 생략.

이것을 굳이 여기서 설명하는 이유는 하나다. 엑셀에 불필요하게 색상을 알록달록하게 넣지 말자. 굳이 넣고 싶다면 위에서 말한 서식으로 넣는 것이 훨씬 효율적이다.

3) 빈 칸을 채우기

마우스 끌어서 채우는 것을 의미하는 것이 아니다.

피벗 테이블을 통해서 정리된 내용을 값으로 붙여넣기를 해서 파일로 받게 되면 눈에 보기는 좋으나, 나중에 비어 있는 칸에 해당 내용을 채워야 하는 경우가 생긴다. 이 때 하나하나 셀을 끌어서 노가다로 채우지 말자. 찾아보면 비어있는 칸들을 한 번에 선택하여 그 위쪽에 마지막으로 들어간 값으로 채워넣는 옵션이 있다. 역시 자세한 설명은 생략.

4) 마우스로 여러 셀들을 선택해서 우측 아래에 Sum값 확인하기

이렇게 하지 말자.

게다가 이렇게 눈으로 본 숫자를 다른 시트에 붙여넣는 작업은 더더욱 하지 말자. 반드시 실수(Human Error)가 일어나고, 이거 찾기가 더 힘들다. 너무 꼼꼼해서 실수 안하는 사람이라면 더 문제다. 진짜 문제를 해결하지 못하게 하니까 말이다.

Sumif 같은 것을 쓸 필요도 없다. 피벗테이블을 쓰면 거의 다 해결된다.

5) Vlookup의 맨 끝 인자를 '1'로 설정하기

Vlookup의 마지막 인자를 '0'으로만 사용하는 사람들이 많다. 0이 의미하는 것은 Key값과 '정확히 일치할 때만 불러옴'의 의미이다. 그렇다면 1은 언제 사용할까?

대표적으로 범주에 있는 값을 라벨링할 때 사용한다.

가령, 점수를 매긴다고 치자. 0~60점은 F, 61~70은 D, 71~80은 C, 81~90은 B, 91~100은 A를 매겨야 한다면, 위에 해당되는 점수표를 테이블로 만들고 해당 점수값을 키값으로 Vlookup을 돌리되 끝 인자를 1로 하면 된다. 73은 C로, 92는 A와 같이 각 점수가 자동으로 등급화되서 들어간다. 두 번의 시험이 있어서 상하반기 평균이 83.5점이어도 상관없다. 그냥 B로 들어간다. 알아두면 의외로 쓸 용도가 많다.

6) 행이 겁나게 많다면 액세스를 배우자

행이 많아지면 노트북의 성능과 관계없이 엑셀이 사정없이 느려진다. 게다가 V-lookup은 이를 더욱 가중시킨다. 그런데 보통 이런 경우는 V-lookup이 문제가 아니고 애초에 너무 많은(불필요한) raw data를 가져왔기 때문인 경우가 많다.

액세스를 쓰면 몇 백만개의 행도 순식간에 처리가 된다.

따라서 다량의 raw data를 액세스에 넣고, 필요한 부분만 액세스에서 엑셀로 추출한 후, 액셀에서 분석 및 그래프로 만드는 작업을 하는 것이 효과적이다.

7) 소질이 있다면 SQL을 배우는 것이 좋다

데이터를 다룰 수 있다는 것은 생각 이상으로 직장인에게 큰 힘을 가져온다. SQL은 개발자가 아닌 직장인도 도전 가능한 영역이다. 물론 데이터 엔지니어 만큼 잘하지는 못하겠지만, 기본적인 문법과 Join 정도만 알아도 왠만한 직장생활에서 빛을 발한다.

쓰다보니 굉장히 길어졌는데,

직장인에게 엑셀은 업무효율을 높여주는 요술봉이나 다름없다. 필요한 만큼은 꼭 마스터하자.