brunch

You can make anything
by writing

C.S.Lewis

by 장영학 Jan 10. 2024

엑셀질에 대한 고찰

주말에 엑셀하다 쓰는 뻘글

대학 졸업 할 때까지 별로 쓸 일이 없다가(학과에 따라 다를 수도 있지만) 직장 생활 시작하면 매일 열어봐야 하는 프로그램이 바로 엑셀이다. 워드, 파워포인트보다 하수와 고수의 수준 차이가 확 벌어지는 프로그램이기도 하다. 군대 행정병 시절에는 엑셀 하면서 마우스에 손을 대는지, 단축키를 얼마나 외우고 있는지로 혼나기도 했다. 엑셀 수식을 어떻게 쓰냐에 따라 야근 시간이 획기적으로 줄어들기도 하고, 중간에 수식 하나 틀린 것 찾느라 반대로 없던 야근을 하게 되기도 한다. 그나마 틀렸다는 걸 미리 알면 다행이지, 잘못 계산한 숫자로 보고해 놓고 나중에 실수를 발견해서 난감했던 상황 다들 한 번쯤은 있을 것이다.

나는 엑셀의 신도 아니고, 수식 잘 쓰는 법을 가르쳐 줄 수는 없지만, 주말에 엑셀질 하다 문득 드는 생각이 있어 몇 자 끄적여본다.

내 멋대로 나누자면 회사에서 돌아다니는 엑셀 파일은 용도에 따라 크게 네 가지가 있다.

워드, 파워포인트 대신 쓰는 엑셀

현황 파악을 위한 엑셀

시뮬레이션을 위한 엑셀

시스템을 대체하는 엑셀

워드, 파워포인트 대신 쓰는 엑셀

평범한 문서는 보통 워드나 파워포인트로 작성한다. (여기서 문서란, 필요에 따라 A4 용지에 프린트해서 볼 수 있는 글 위주의 파일이라 치자.) 엑셀은 문서 작성을 위한 최적의 도구는 아니지만, '뭐든지 다 할 수 있는' 엑셀의 특성(?)을 이용해서 온갖 문서를 엑셀로 만들어 내는 사람들이 있다.

그나마 합리화해 보자면, 결과가 보고서 형태로 나와야 하는데 보고서 본문의 표나 그래프만 주기적으로 업데이트해야 하는 경우 엑셀로 보고서를 만들 수 있다. 이런 엑셀 파일을 보면 시트 이름이 '0.표지', '1.요약', 같은 식으로 되어있고, 모든 시트를 출력해서 스테이플러로 찍으면 마치 워드나 파워포인트로 만든 보고서 같은 모양이 나온다. 물론 어지간히 반복되는 보고서가 아니면 이렇게 쓸 필요는 없고, 정상인들은 엑셀은 엑셀대로 돌리고 파워포인트나 워드로 쓴 보고서에 숫자만 업데이트한다.

가끔 합리화가 안 되는, 엑셀을 쓸 이유가 전혀 없는 문서도 접할 수 있다. 시스템 사용 매뉴얼 같은걸 엑셀에 화면 하나하나 캡처해서 만들질 않나, 계약서(견적서 말고 계약서 본문)가 엑셀로 오기도 한다. 그런 케이스는 문서를 만든 사람이 엑셀을 사랑해서라고 밖에 생각할 수 없다.

이걸 굳이 엑셀로?

현황 파악을 위한 엑셀

숫자 현황을 표나 그래프로 정리해서 보기 위한 엑셀 파일이다. '현황'이기 때문에 한 번만 작성하는 경우도 있지만 주간 리포트처럼 같은 양식으로 주기적으로 보는 경우가 더 많은 것 같다. 정기적으로 매출, 비용, 회원 수, 객단가, 다운로드 수, MAU 등 주요 지표를 엑셀로 예쁘게 만들어서 메일로 뿌리는 회사가 많다. 물론 Tableau 같은 BI 시스템에 투자를 하면 수작업을 안 하고도 정기적으로 데이터를 볼 수 있다. 하지만 대시보드 형태를 자주 직접 수정하고 싶다든지, 내가 만든 엑셀 파일이 더 예쁘다든지, 무엇보다 시스템 투자를 할 돈이 없으면 엑셀이 가장 편한 방법이다.

현황 엑셀도 크게 두 가지가 있는데, 일정 주기마다 raw data를 추가하면 summary 표가 업데이트되도록 수식이 걸린 경우도 있고, 모든 데이터가 값으로만 들어가 있는 파일도 있다. 보통 사람이 작업하는 엑셀은 전자, 시스템에서 내린 엑셀은 후자이지만, 데이터와 수식 때문에 파일을 여는데 너무 느려진다든지 하면 사람이 수식으로 작업하는 워킹 파일을 두고 값복사한 배포용 파일을 따로 돌리기도 한다. 정기적으로 raw data를 업데이트하는 엑셀을 관리하고 있다면, raw data를 입력하는 시트를 아예 분리하는 것이 좋다. 예를 들어, '거래내역(RAW)' 같은 시트에 데이터를 입력하면 '주간 매출달성율' 같은 시트에 브랜드별 매출 목표와 실적, 달성율이 정리되어 나오게 하는 것이다.

현황 파악용 엑셀은 보통 숫자가 엄청 많다. 그래서 중요한 숫자가 강조되도록 볼드나 글자색 같은 시각화를 잘 써야 한다. 매번 강조할 부분을 직접 색칠할 생각이 아니라면 조건부 서식을 적절하게 활용하면 좋다. 지난주보다 수치가 떨어졌다든지, 이익이 마이너스라든지 조건에 따라 글자색, 셀 바탕색이 바뀌게 할 수 있다. 수치에 따라 녹색, 노란색, 빨간색 신호등을 켜는 것도 가능하며, 셀 안에 그래프를 표시할 수도 있다. 다만 조건부 서식에 재미 들려 이것저것 넣다 보면 정신사나워서 못 보는 엑셀이 된다. 튜닝의 끝이 순정인 것처럼(?) 심플하면서 정말 필요한 곳만 강조되는 엑셀을 만들도록 하자.

시뮬레이션을 위한 엑셀

시뮬레이션은 말 그대로 특정 셀의 숫자를 바꿔가면서 다른 값이 어떻게 변하는지를 보기 위한 엑셀 파일이다. 예를 들어 환율 변동에 따라 구매 단가와 영업이익률이 어떻게 변하는지 추정해 본다든지, IRR에 따라 현금흐름의 현재 가치가 얼마가 되는지 계산하는 것을 엑셀로 할 수 있다.

위에 문서로서의 엑셀이나 현황 파악을 위한 엑셀에도 SUM(), VLOOKUP(), SUMIF() 같은 수식이 일부 걸릴 수 있지만, 시뮬레이션용 엑셀이야 말로 정교한 수식이 많이 걸리게 된다. 그래서 시뮬레이션을 위한 엑셀이 엑셀질의 꽃(?)이라고 생각한다. 문서 작성이나 현황 정리는 엑셀 말고 다른 프로그램으로도 할 수 있지만, 시뮬레이션은 코딩이라도 하지 않는 한 엑셀로만 가능하기 때문이다.

시뮬레이션 엑셀의 문제는 만든 놈만 해석할 수 있는 파일이 되기 십상이라는 점이다. 엑셀의 수식은 한눈에 보이지 않고 한 셀 한 셀 들여다봐야지만 파악할 수 있다. 그러다 보니 매출에서 원가와 판관비를 빼서 영업이익을 계산한 것인지, 매출과 영업이익률, 판관비를 고정해 놓고 원가를 역산한 것인지 겉으로 봐서는 구별이 안된다. 그러다 보니 파일이 조금만 복잡해져도 만든 사람이 아니면 시뮬레이션을 할 수 없는, 괴물 같은 파일이 되어버린다. 상태가 심각한 경우는 작성자 자신도 자기가 쓴 수식이 헷갈려 수정을 못하는 파일이 된다.

갑분 시뮬레이션 엑셀 잘 만드는 Do&Don't

Do. '변수가 뭔지 눈으로 구분되게 해라'이다. 예를 들어 원가율을 바꿔가며 이익을 시뮬레이션하려면, 원가율 셀을 15% 처럼 글자색과 셀 배경색을 다르게 하는 것이다. 나머지 흰 바탕에 검은 숫자들은 저 원가율이 바뀌면 수식으로 연동되어 수정되는 숫자들이다. 나는 아예 엑셀시트 상단 표 제목 옆에 파란 숫자만 수정 같은 식으로 써놓는다. 미래의 내가 멍청하게 수식을 어떻게 걸었는지 까먹을 수 있기 때문이다.

Do. 변수를 입력하는 칸은 한쪽으로 몰아라. 예를 들어 환율, 이자율, 단가 세 가지 변수가 있다면 표 왼쪽 위에 환율, 오른쪽에 단가, 아래쪽에 이자율 입력을 따로 하는 것보다, 아예 시트 위쪽에 변수만 입력하는 표를 따로 만들어 두고, 아래쪽에 변수에 따라 시뮬레이션 값이 변하는 수식 표를 만들어 두면 이 파일을 공유받은 다른 사람들도 훨씬 작업하기 수월해진다. 위에 현황 파악을 위한 엑셀에서 raw data 업데이트하는 시트를 따로 두라는 것도 같은 맥락이다. 건드려도 되는 부분과 건드리면 안 되는 부분을 명확하게 구분만 해놓아도 엑셀 실수가 상당히 줄어든다.

대충 이런 느낌...

Don't. 수식에 변수를 하드코딩 하지 마라. 무슨 말이냐면, D5 셀이 인원수, D6 셀이 평균 연봉, D7 셀이 인건비 배수라면 D8 인건비 셀의 수식은 '=D5*D6*D7' 이어야 한다. 여기다 수식을 '=5*4860*1.4'으로 넣는 인간이 실제로 존재한다.

Don't 수식을 일관성 있게 넣어라. 예를 들어 C열을 '영업이익 = 매출 - 원가 - 판관비'로 넣었다면, D열 이익도 같은 수식을 거는 것이 상식적이다. 만약 어떤 이유가 있어 C열 이익과 D열 이익이 수식이 다르다면 엑셀 상에도 메모든 색칠이든 뭔가 표시가 있어야 한다. 예를 들어 23년 영업이익은 확정치라 '매출 - 원가 - 판관비'로 계산하고, 24년은 목표치라 '목표 매출 * 목표 영업이익률'로 계산했다면 C열과 D열은 셀 배경색이라도 다르게 해주는 것이 좋다. 수식이 같아야 할 것 같은 셀들에 다른 수식이 걸려있으면 나중에 이게 의도한 것인지, 실수한 것인지 구분이 안되고 수식이 꼬이는 중요한 원인이 된다.

시스템을 대체하는 엑셀

흔한 케이스는 아니지만 거의 시스템 수준의 엑셀 파일도 있다. 용도로만 보자면 위에 언급한 현황 파악이나 시뮬레이션의 연장 선상인데, 좀 더 데이터가 크고 마치 시스템 화면처럼 엑셀에 버튼들이 달려있다. 일반적인 수식이 아니라, 비주얼베이직(VBA)으로 버튼들을 코딩해 놓았다는 뜻이다. 이런 파일이 흔하지 않은 이유는 보통 회사에 VBA를 쓸 줄 아는 사람이 그리 많지 않기 때문이다.

시스템을 구축하기 위한 프로젝트에서 미리 시스템 활용도를 파일럿 테스트 하고자 시스템에서 나와야 할 데이터를 엑셀에서 보이게 구현하는 경우가 있다. 나도 한 10년 전에 한번 만들어 봤고, 여태 직장 생활하면서 서너 번 이런 파일을 본 것 같다. 당연히 데이터가 너무 커지면 느려져서 못쓰기 때문에 계속 엑셀로 돌리려는 용도는 아니고, 실무자들의 피드백을 받기 위해 길어야 한 달 정도 쓰는 게 정상인 것 같다.

이런 파일은 계속 쓸 수도 없는 게, VBA는 엑셀 양식이 조금만 수정되어도 코드가 꼬여버리고 만다. 무슨 말이냐면, 엑셀 수식은 셀을 드래그해서 옮긴다든지, 행/열 삽입이나 삭제를 하면 친절하게 연결된 수식의 레퍼런스 값이 바뀐다. 그런데 VBA는 같은 이유로 B5 셀이 B7 셀이 되면 VBA의 코드에 박힌 'B5'를 모두 'B7'으로 일일이 바꿔줘야 한다. (지금은 혹시 개선이 되었는지? 예전에 내가 코딩했을 때는 저거 때문에 머리가 너무 아팠음...) 그러니 남이 만든 VBA 들어간 파일을 받아서 표를 수정했는데, 나는 VBA를 쓸 줄 모른다면 바로 파일이 쓰레기가 된다.

그래서 엑셀에서 VBA를 쓰는 건 개인적으로 추천하지 않는데, 어쩔 수 없이 써야 하는 경우가 있다. 대표적으로 엑셀 수식에는 for loop가 없기 때문에, 간단한 반복 작업이지만 수식으로는 해결이 안 될 때가 생긴다. 데이터가 몇십 줄이면 노가다로라도 하겠는데 양이 많아지면 어쩔 수 없이 VBA를 쓰게 된다.