엑셀 참조 기능으로 데이터 테이블 빈 셀 1분만에 채우는 방법

이 글은 시리즈 "엑셀 기초 : 참조에 대한 모든 것" 6편 중 6번째 글입니다.

지난 수업을 통해서 우리는 엑셀의 참조 기능에 대해 여러번에 걸쳐서 알아보았다. 다른 단순한 팁에 비해 꽤 긴 연재를 통해서 진행할 정도로 쉬운 내용은 아니었다. 하지만 힘들게 배운 참조 기능은 앞으로 수많은 곳에서 다양하게 정말 유용하게 쓰일 것이다.

이번 포스팅에서는 참조 강의의 마지막 과정으로 지난번 참조를 이용한 누적합계처럼 실무에서 바로 활용이 가능한 참조를 활용한 실습을 한가지 더 진행해 보도록 하겠다.

내가 현업에서 강의를 진행시 굉장이 반응이 좋았던 활용예인데, 바로 데이터 테이블을 제대로 활용하기 위해 빈 셀의 값을 효율적으로 편하게 채우는 방법이다. 향후 진행할 데이터를 다루는 엑셀에서 꼭 필요한 작업(데이터 전처리)이기도 하다. 그럼 지금부터 알아보도록 하겠다.

Table of Contents

왜 데이터를 채워야 하는가?
값 채워넣기 : 단축키를 이용한 수작업
값 채워넣기 : 참조를 이용해서 한번에
핵심정리

왜 데이터를 채워야 하는가?

엑셀로 실무를 처리하다보면 여러가지 경로를 통해 수집된 데이터를 분석등에 활용이 가능한 형태로 변경을 해야 하는 경우가 빈번하게 발생한다.

예를 들어 SAP 등의 프로그램을 통해 데이터를 다운 받게 되면 아래와 같은 형태를 보이는 경우가 많다.

나중에 다시 자세히 다루겠지만 데이터를 활용하기 위해서는 각 열의 정보는 하나의 온전한 값들로 구성된 형태가 되어야 한다.
즉, 빈셀이 없어야 한다는 이야기다.

그래서 위의 데이터를 아래와 같은 테이블 형태로 변경이 되어야 한다(각 행의 모든 열이 데이터를 가지는 형태의 테이블).
아마도 피벗테이블 등을 자주 활용하신 분이라면 좀 쉽게 이해할 것 같다. 지금은 이해 못하셔도 상관없다. 수업이 진행되면서 다 천천히 알려드릴 내용들이다.

값 채워넣기 : 단축키를 이용한 수작업

이런 형태로 데이터 변경을 하기 위해 빈셀에 값을 채워 넣어야 하는 경우 어떻게 해야 할까?
보통은 아래와 같은 식으로 많이 작업을 한다. 일일이 복사해서 채우는 방법이다.

위의 방법은 그나마 위의 값 붙여넣기(Ctrl+D) 와 같은 단축키를 사용해서 작업속도를 올리긴 했지만 그래도 데이터 양이 방대한 경우 꽤나 시간이 소요된다. 몇년치의 자료에 분량이 많으면 하루종일 걸리기도 하다.
시간도 시간이지만 더 큰 문제는 이런 반복 작업을 수행하다 보면 사람이 하는 일이기에 실수가 발생하기 마련이다.

[엑셀 셀 채우기 간편 단축키 – 밑으로, 옆으로 채우기] : 위의 값 붙여넣기 단축키가 필요하면 여기를 참조.

하지만 이런 작업을 지금까지 배운 참조를 활용해서 수행할 경우 1분 정도에 작업을 마칠 수가 있다.

생각해보면 엄청난 일이다. 자료의 양에 따라서는 하루 종일 또는 몇일 내내 해야할 작업을 반복작업에 따르기 마련인 실수도 전혀없이 완벽한 형태로 1분이면 해결이 된다는 이야기이다. 어떤가? 구미가 당기지 않는가?

값 채워넣기 : 참조를 이용해서 한번에

먼저 지난 포스팅에서 다루었던 이동옵션을 사용해서 빈셀만 선택을 해보겠다.
※ 이동옵션 : 필요한 범위를 선택 > 이동옵션 – 빈셀

[엑셀 이동 옵션 완벽 가이드｜실무에서 바로 쓰는 활용법 정리]

그런 후 선택된 범위의 입력기준셀 위치를 확인하고 가져와야 하는 셀의 값을 넣어준다.

입력 기준셀의 위치는 K18
가져와야 하는 값은 각셀에서 위의 값이므로 K17을 입력
입력 후 선택되어 있는 범위에 동시입력을 하기 위해 Ctrl + Enter

그럼 빈셀의 값이 일괄로 채워지게 된다.

아직 작업이 끝난게 아니다. 위의 채워진 값은 참조를 통해서 채워져 있기 때문에 원하는 형태로 테이블을 편집할 수가 없다. 편집시 값이 변하기 때문이다. 그래서 위의 작업한 범위를 선택해서 값붙여넣기를 해줘서 값을 고정 시켜 준다.

자 이제 채우기 작업은 끝이 났다. 그 후 필요한 작업을 수행해 주면 된다.

핵심정리

위에서 살펴본 예제는 실무에서 사용시 정말 유용하다. 작업시간을 획기적으로 줄여주면서 실수는 0로 만든다. 거기다 데이터양이 방대했다면 불가능했을 작업 가능하게 해준다.

이번 예제를 포함한 참조 연재는 엑셀 수업을 진행하면서 내가 향후 여러분께 가장 전달하고 싶은 내용에 대한 준비작업이라고 할 수 있다.

향후 내가 수업을 통해서 하고 싶은 이야기 중 하나는 이런 것 이다.

방대한 양의 데이터를 다운 받고 > 다운 받은 자료들을 엑셀 내의 쿼리에서 편집을 하고 > Raw 데이터 간에 필요한 관계 설정을 해서 > 보고싶은 표와 피벗, 챠트를 통해 보고서를 만든다.
그리고 이렇게 만들어진 보고서는 업데이트가 필요하다면 새로 작성할 필요가 없어진다. Raw 데이터만 업데이트 된 데이터로 덮어씌우고 자동으로 업데이트만 하면 된다. 한번 만들어 놓은 보고서를 업데이트하는데는 몇분이 걸리지 않게 된다.

파워풀 하다. 복잡한 월 보고서를 정기적으로 만드는 사람이라면 위의 이야기가 얼마나 실무를 줄여줄 수 있는지, 엄청난 이야기인지를 아마도 알 것이다.

그런데 생각보다 그리 어렵지 않다. 이번에 연재를 통해 진행한 참조는 거기에 가기 위한 첫걸음이라고 생각하면 된다. 조금씩 같이 나아가셨으면 한다. 도움이 되는 내용이었길 바래본다.