다이써

얼마 전 저희 팀의 마케팅 담당 대리님이 수많은 행으로 이루어진 가맹점별 판매 매출 데이터를 붙잡고 멍하니 모니터를 바라보고 계시더라고요. 팀장님께서 "올해 1분기 동안 서울 강남점에서 판매된 'A 모델'의 총매출 합계를 당장 가져오라"고 지시하셨기 때문이었죠.  조건이 '1분기(기간)', '강남점(매장)', 'A 모델(상품)' 무려 세 가지나 되다 보니, 대리님은 필터 기능을 켜서 강남점을 고르고, 다시 날짜를 체크하고, 상품을 하나하나 클릭해가며 수동으로 합계를 계산하고 계셨습니다. 이 방식은 조건이 바뀔 때마다 필터를 다시 걸어야 하고, 실수로 행 하나라도 놓치면 데이터 전체가 틀어지는 위험천만한 상황이었죠. 이처럼 실무에서는 하나의 조건이 아니라 여러 개의 조건이 동시에 만족하는 합계를 구해야 하는 일이 대부분입니다. 이 문제를 해결하기 위해 제가 대리님께 알려준 함수가 바로 SUMIFS였습니다. 이름 뒤에 'S'가 붙은 것에서 알 수 있듯이, 여러 개(Plural)의 조건을 동시에 만족하는 범위를 찾아 합계를 내주는 아주 기특한 녀석이죠.  기존의 SUMIF 함수는 조건을 하나밖에 지정하지 못하지만, SUMIFS는 최대 127개의 조건까지 한 번에 처리할 수 있어서 복잡한 데이터 분석도 막힘없이 처리할 수 있답니다. 함수의 기본 구조만 이해하면 사용하는 방법은 생각보다 정말 간단해요. 엑셀 창에 =SUMIFS( 를 입력한 뒤, 가장 먼저 기억해야 할 것은 '내가 진짜로 더하고 싶은 숫자가 있는 범위'를 먼저 지정해 주는 것입니다. 그 뒤로는 [조건범위1, 조건1], [조건범위2, 조건2]... 의 형태로 짝을 지어 나열해 주기만 하면 끝이랍니다. 방금 대리님이 겪었던 실무 상황을 예제로 삼아 수식을 짜볼까요? 매출 금액이 D열, 매장 위치가 B열, 상품명이 C열에 있다고 가정해 볼게요. 강남점의 A 모델 매출 합계를 구하는 수식은 =SUMIFS(D:...

얼마 전 기획팀의 한 대리님이 대용량 자재 관리 테이블을 보며 깊은 한숨을 쉬고 계시더라고요. 사유를 보니, 제품명을 기준으로 왼쪽에 있는 '부품 번호'를 찾아와야 하는데 VLOOKUP 함수가 자꾸 에러를 뿜어낸다는 것이었습니다. 아시다시피 VLOOKUP은 기준이 되는 값이 무조건 찾으려는 범위의 '첫 번째 열(가장 왼쪽)'에 있어야만 작동하잖아요?  기준값보다 왼쪽에 있는 데이터는 죽었다 깨어나도 읽어오지 못하는 치명적인 약점이 있죠. 대리님은 결국 이 값을 찾으려고 수천 줄짜리 원본 데이터의 열 위치를 강제로 바꾸려다, 다른 셀에 걸려있던 수식들까지 줄줄이 깨져서 멘붕이 온 상태였습니다. 게다가 VLOOKUP은 데이터를 찾을 열의 위치를 '3', '4' 같은 고정된 숫자로 지정하다 보니, 중간에 새로운 열을 하나라도 삽입하면 순식간에 참조가 밀려 엉뚱한 값을 가져오는 대참사가 일어나기도 합니다.  10년 동안 이런 상황 때문에 수많은 동료가 밤새워 데이터를 복구하는 모습을 봐왔기에, 저는 대리님의 마우스를 잠시 멈추게 하고 VLOOKUP의 모든 한계를 단번에 날려버릴 INDEX MATCH 조합 을 처방해 주었습니다. 이 조합은 열의 위치에 구애받지 않고, 데이터가 왼쪽에 있든 오른쪽에 있든, 심지어 행과 열이 복잡하게 얽혀있든 상관없이 원하는 값을 정확하게 끄집어내는 만능 열쇠거든요. 처음에는 두 개의 함수가 합쳐져서 수식이 복잡해 보이지만, 원리만 이해하면 아주 직관적이에요. 역할을 분담하는 것이죠. MATCH 함수 는 "내가 찾고 싶은 값이 이 눈금판에서 몇 번째 자리에 있어?" 하고 위치(번지수)를 알려주는 역할을 합니다. 그리고 INDEX 함수 는 "그럼 내가 지정한 범위에서 방금 찾은 그 번지수에 있는 실제 값 을 쏙 꺼내올게!" 하고 최종 결과물을 배달해 주죠. 쉽게 말해 MATCH가 주소를 찾고, INDEX가 그 주소에 있는 택배를 가져오는 찰떡궁합 구...

회사에서 열심히 정산서나 중간 보고서를 작성하다 보면, 다른 시트나 외부 파일에서 데이터를 복사해 와야 하는 일이 정말 많죠. 얼마 전 저희 팀 후배 사원이 아주 예쁘게 짜인 매출 현황 표에 다른 부서에서 취합한 데이터를 복사해서 붙여넣었는데요, 순식간에 표의 테두리 선이 굵어지고 배경색이 알록달록하게 바뀌면서 기존의 깔끔했던 디자인이 엉망진창이 되어버렸답니다.  당황한 후배는 되돌리기( Ctrl + Z )를 누르고 다시 일일이 손으로 숫자를 타이핑하기 시작하더라고요. 수백 줄의 데이터를 그렇게 손으로 치다 보면 오타가 날 확률도 높고, 무엇보다 소중한 퇴근 시간이 저 멀리 사라지게 되니 옆에서 보기 참 안타까웠습니다. 많은 직장인이 겪는 이 문제의 원인은 바로 단순한 복사( Ctrl + C )와 붙여넣기( Ctrl + V ) 때문이에요. 엑셀에서 무턱대고 붙여넣기를 하면, 눈에 보이는 '숫자'나 '글자'뿐만 아니라 그 셀에 적용되어 있던 글꼴, 배경색, 테두리 같은 '서식'까지 통째로 복사되어 오기 때문이죠.  10년 넘게 실무에서 일하면서 저 역시 수없이 겪었던 시행착오였고, 엑셀을 다루는 사람이라면 누구나 한 번쯤은 겪게 되는 아주 흔한 상황이랍니다. 이럴 때 복사해 온 데이터의 '성격'에 따라 스마트하게 골라 붙여넣는 기술이 바로 '선택하여 붙여넣기'예요. 저는 후배 사원의 손을 살짝 멈추게 한 뒤, 마우스 우클릭의 비밀을 알려주었어요. 우선 가져올 데이터를 복사( Ctrl + C )한 다음, 붙여넣을 자리에 마우스 우클릭을 하면 여러 가지 붙여넣기 옵션 아이콘들이 나타납니다.  그중에서 숫자 '123'이 그려진 '값만 붙여넣기' 아이콘을 누르면, 원본의 화려한 색상이나 굵은 테두리는 모두 버리고 오직 '알맹이 데이터(값)'만 쏙 빠져나와 기존 표의 예쁜 서식 속에 부드럽게 스며들게 되죠. 셀에 걸려있던 수식을 없애고 최종 결과물인 숫자만...

얼마 전 저희 팀의 기획자 한 분이 프로젝트 일정표를 들고 제 자리로 찾아왔어요. 상반기 주차별 업무 계획을 엑셀로 빼곡하게 정리해 두었는데, 상사분께서 "표가 가로로 너무 길어서 모니터를 한참 넘겨봐야 하니, 가로세로를 바꿔서 위아래로 스크롤하며 볼 수 있게 수정해 오라"고 하셨다는 거예요.  이미 수십 개의 업무와 날짜들이 가로로 길게 나열되어 있어서, 대리님은 이걸 하나하나 다시 복사해서 세로로 옮겨 심어야 하냐며 한숨을 깊게 쉬고 계셨죠. 마침 미팅 시간은 30분밖에 남지 않아 마음이 아주 급해 보였습니다. 이렇게 이미 완성된 표의 구조를 통째로 바꿔야 할 때, 가장 빠르고 간편하게 쓸 수 있는 첫 번째 무기는 바로 '선택하여 붙여넣기'의 행열 바꿈 기능이에요. 복사하고 싶은 원본 표를 마우스로 쭉 드래그해서 복사( Ctrl + C )한 다음, 새롭게 표를 만들 빈 셀을 클릭합니다. 그리고 마우스 우클릭을 하면 나타나는 메뉴에서 붙여넣기 옵션 중 '행/열 바꿈' 아이콘(가로세로 화살표가 직각으로 교차하는 모양)을 누르기만 하면 끝이랍니다. 1초 만에 가로에 있던 날짜들이 세로로, 세로에 있던 업무명이 가로로 완벽하게 대칭 이동을 하게 되죠. 하지만 이 방법은 한 가지 아쉬운 점이 있어요. 원본 표의 숫자를 수정해도, 행열을 바꿔 새로 붙여넣은 표에는 그 수정 사항이 자동으로 반영되지 않는다는 것이죠. 만약 원본 데이터가 수시로 업데이트되는 상황이라면, 두 번째 방법인 'TRANSPOSE 함수'를 사용하는 것이 훨씬 스마트합니다.  TRANSPOSE는 단어 뜻 그대로 '위치를 바꾸다'라는 의미를 가진 함수예요. 이 함수를 사용하면 원본 표와 링크(연결)가 걸린 상태로 행열이 바뀌기 때문에, 원본의 값을 바꾸면 바뀐 내용이 새 표에도 실시간으로 쏙쏙 적용된답니다. 함수 사용법도 정말 간단해요. 새로운 빈 셀에 =TRANSPOSE( 를 입력한 뒤, 가로세로를 바꿀 원본 표 영역을 마우...

얼마 전 저희 옆 부서의 대리님이 다급하게 저를 찾아왔어요. 거래처에서 보낸 상품 마스터 데이터를 정리해야 하는데, 상품 코드와 수량이 'ABC-105', 'D-20'처럼 하이픈(-)으로 연결되어 있거나, 심지어 '노트북15대', '마우스20개'처럼 문자와 숫자가 무작위로 붙어 있어서 통계를 낼 수가 없다는 것이었죠.  마감은 오늘 오후인데, 데이터를 하나하나 복사해서 옆 칸에 붙여넣고 계시더라고요. 수백 개가 넘는 품목을 언제 다 분리하냐며 울상을 짓는 모습을 보니, 예전에 저도 신입 시절에 밤새워 가며 수작업을 했던 기억이 나 마음이 참 짠했습니다. 이처럼 하나의 셀 안에 성격이 다른 데이터가 섞여 있을 때, 엑셀에게 어디를 기준으로 쪼갤지 규칙만 알려주면 순식간에 해결할 수 있습니다. 바로 상단 메뉴의 [데이터] 탭에 숨겨져 있는 '텍스트 나누기' 기능 덕분이죠. 이 기능은 크게 두 가지 방식으로 작동해요.  하이픈(-)이나 쉼표(,), 공백처럼 문자와 숫자 사이에 확실한 기호가 있을 때는 '구분 기호로 분리됨'을 선택하면 되고, 기호는 없지만 '사번 4자리+이름'처럼 글자 수가 딱딱 맞아떨어질 때는 '너비가 일정함'을 선택해서 원하는 위치를 칼로 자르듯 쪼갤 수 있습니다. 제가 대리님과 함께 문제를 해결한 과정도 정말 간단했어요. 우선 분리할 데이터가 들어있는 열을 통째로 선택한 뒤, 메인 메뉴의 [데이터] -> [텍스트 나누기]를 차례로 클릭했습니다. 하이픈 기호가 있는 데이터의 경우에는 마법사 창이 떴을 때 '구분 기호로 분리됨'을 선택하고, 기타 분리 기호 칸에 하이픈(-)을 쏙 넣어주었죠. 그러자 화면 하단의 미리보기 창에서 하이픈을 중심으로 문자와 숫자가 양옆으로 완벽하게 갈라지는 것을 확인할 수 있었습니다. 그대로 '마침'을 누르니 1초 만에 깔끔한 두 개의 열로 데이터가 분리되었답니다. 만약 ...

얼마 전 새로 입사한 신입 사원이 하얗게 질린 얼굴로 모니터를 뚫어지게 쳐다보고 있더라고요. 사유를 물어보니, 거래처 명단을 바탕으로 매출 데이터를 매칭하고 있는데 분명히 명단에 있는 업체인데도 엑셀이 자꾸만 올바른 값을 찾아오지 못한다는 것이었어요.  데이터 양은 수천 줄이 넘어가는데 마감 시간은 코앞이라 하나하나 더블클릭해가며 지우고 있었던 거죠. 제가 다가가서 수식 입력줄을 확인해 보니, 어떤 데이터는 글자 앞에 공백이 들어가 있고, 어떤 것은 글자 뒤에 스페이스바가 서너 번이나 눌려 있는 상태였습니다. 컴퓨터 입장에서는 '삼성'과 '삼성 '을 완전히 다른 단어로 인식하기 때문에 발생한 문제였던 것이죠. 이처럼 외부 시스템에서 데이터를 다운로드하거나 여러 사람이 제각각 입력한 자료를 취합할 때는 사방에 흩어진 공백들이 실무자들을 정말 힘들게 만듭니다. 이 문제를 해결하기 위해 제가 신입 사원에게 소개해 준 무기가 바로 'TRIM' 함수였습니다.  TRIM은 '다듬다', '잘라내다'라는 뜻을 가진 단어인데요, 엑셀에서도 그 역할이 똑같답니다. 텍스트의 양쪽 끝(앞과 뒤)에 있는 모든 공백을 흔적도 없이 지워주고, 글자와 글자 사이에 들어간 불필요한 연속 공백은 딱 한 칸만 남기고 알아서 예쁘게 다듬어주는 아주 영리한 함수예요. 해결 방법은 놀라울 정도로 간단해요. 공백을 정리하고 싶은 데이터 옆에 빈 열을 하나 만든 뒤, =TRIM(A2) 처럼 함수를 입력하고 괄호 안에 공백이 섞인 셀 주소만 쏙 넣어주면 끝이랍니다. 그러고 나서 엔터를 누르고 채우기 핸들을 아래로 더블클릭해 수식을 쭉 복사해 주면, 그 많던 수천 줄의 유령 공백들이 단 1초 만에 마법처럼 사라집니다.  지저분했던 데이터가 칼로 자른 듯 정갈하게 정렬되는 모습을 보면 속이 다 시원해지는 기분이 들 거예요. 신입 사원도 꼬여있던 데이터가 한 번에 매칭되는 것을 보고는 감탄을 금치 못했답니다. 마지막으로 가작 중요한 실무 ...

회사 생활을 하다 보면 예상치 못한 순간에 식은땀이 흐르는 경험을 하곤 해요. 얼마 전 저희 팀의 한 주임님이 수천 줄에 달하는 분기별 매출 실적 데이터를 정리하다가 잔뜩 상기된 얼굴로 저를 찾아왔답니다. 거래처별 매출을 보기 좋게 정렬하고 특정 지역만 필터링해서 보고서를 만들어야 했는데, 무슨 이유에서인지 정렬을 누를 때마다 엉뚱한 행의 데이터가 뒤섞여 버린 것이었죠.  데이터의 행과 열이 서로 맞지 않게 꼬여버리니, 공들여 작성한 기존의 실적 수치마저 신뢰할 수 없는 상황이 되어버렸어요. 마감 시간은 다가오는데 데이터가 엉망이 되었으니 얼마나 당황스러웠을까요? 원인을 살펴보니 엑셀의 데이터 구조에 작은 구멍들이 있었기 때문이었어요. 중간중간 병합된 셀이 존재했고, 아무런 데이터도 없는 빈 행과 빈 열이 섞여 있다 보니 엑셀이 전체 데이터 범위를 제대로 인식하지 못했던 것이죠.  이 상태에서 무턱대고 정렬 기능을 실행하니 필터가 걸리지 않은 영역은 제자리에 있고, 필터링된 영역만 따로 움직이면서 전체 데이터의 매칭이 완전히 틀어져 버린 상태였습니다. 10년 넘게 이 일을 하면서 저 역시 연차를 불문하고 정말 많은 동료들이 이 똑같은 함정에 빠지는 모습을 봐왔기에, 주임님의 마음이 십분 이해가 갔답니다. 우선 저는 주임님을 안심시키고 함께 차근차근 문제를 해결해 나가기 시작했어요. 가장 먼저 한 일은 뒤섞여 버린 데이터를 원래대로 되돌리는 일이었죠. 다행히 실행 취소로 되돌린 후, 데이터의 기본 뼈대를 먼저 다듬었습니다.  정렬과 필터가 오류 없이 작동하려면 표 내부에 빈 행이나 빈 열이 없어야 하거든요. 그리고 데이터 분석의 가장 큰 적인 '셀 병합'을 모두 해제하고, 비어 있는 칸에는 올바른 값을 채워 넣었습니다. 그 후 데이터가 시작되는 첫 번째 행에 정확하게 '필터(Ctrl + Shift + L)'를 적용했어요. 이렇게 전체 범위를 명확하게 지정해 준 뒤에야 비로소 원하는 기준에 맞춰 정렬을 실행했답니다. 숫자는 내림차순으로...