탐사보도를 위한 엑셀 활용법_한국탐사저널리즘센터 권혜진 박사

가이드2-권혜진01   

가이드2-권혜진02“선배, 기사 내용을 화재통계연감과 비교했는데 부산은 숫자가 안 맞아요.”
저녁 7시 무렵. 후배가 사내용 초판 신문 대장을 들고 뛰어왔다. 2006년 2월. 6대 도시 화재 GIS(Geographic Information System: 지리정보시스템) 분석 기사가 나가는 날이었다. 사회부에서 정보공개청구를 통해 얻은 화재 정보 1만여 건을 GIS 기법으로 분석한 기사였다. 당시 외부 전문 업체가 GIS 분석을 담당했는데 부산 지도가 잘못된 것을 뒤늦게 발견한 것이다. 지도를 만든 전문가를 다급히 찾았다. 하지만 그는 지방 출장으로 고속도로에 있었다. 조언은 가능하나 작업은 불가능한 상황. 45판 신문 제작까지 남은 시간은 2시간 남짓이었다. 그 사이에 어떻게든 틀린 부분을 모두 고쳐야 했다. 아찔했던 상황은 다행히도 무사히 해결됐지만 이 사건은 내게 중요한 교훈을 주었다.

 

기자들도 데이터를 검증할 능력 필요
필자는 당시 동아일보에서 정보검색과 컴퓨터 활용 취재보도CAR를 맡고 있었다. GIS 프로젝트에 참여한 적은 있으나 GIS 프로그램엔 서툴렀다. 기자가 굳이 GIS 분석까지 할 필요가 있나 하는 마음도 내심 있었다. 일반 기자들이 ‘굳이 엑셀Excel까지 익힐 필요가 있나?’라고 생각하는 것처럼. 하지만 오보로 이어질 뻔했던 사건을 겪고 나니, 기자 스스로 데이터를 보는 능력이 없으면 취재원이나 외부 전문가의 데이터가 틀렸을 때 이를 검증할 수 없다는 생각이 들었다. 결국 오보로 이어질 수 있는 것이다.

그 후 다른 매체의 GIS 분석을 담당했던 전문가에게 그 보도의 지번 매칭이 얼마나 정확했는지 물어본 적이 있다. 데이터 클리닝 과정이 불충분해 80% 이상 제대로 매칭되지 않았다는 대답이 돌아왔다. 과연 보도에 참여했던 기자들은 이 사실을 알았을까. 미국탐사보도기자협회IRE가 탐사보도의 요건을 ‘기자 스스로 탐사한 내용’이어야 한다고 강조한 것이 떠올랐다.

탐사보도를 위한 엑셀 지식
엑셀, 엑세스, GIS, 사회연결망분석SNA 등 다양한 분석 기법들이 탐사보도에 활용된다. 하지만 일상적인 취재 보도에선 엑셀의 기초 정도만 알아도 큰 힘이 된다. 그러면 어디에서부터 엑셀 공부를 시작해야 할까. 시중에 나와 있는 엑셀 도서들은 월급 계산을 어떻게 하는지, 차트를 어떻게 그리는지에 대해선 잘 설명하지만 정작 기자들이 필요로 하는 지식은 찾기 어려울 때가 많다. 다음은 기자에게 꼭 필요한 엑셀 기능들.

# 상황
연예인을 시켜주겠다며 필리핀 여성들을 예술흥행(E6) 비자로 데려와 성매매를 시킨 일당이 덜미를 잡혔다. 최근 10년간 예술흥행 비자로 입국한 여성들 숫자를 확인하고 싶다. 취재원에게 국적별 체류 외국인 현황 자료를 받았다. 그런데 스크롤 막대를 오른쪽으로 옮기면 국적이 사라지고 아래로 내리면 체류 자격을 볼 수 없다. 체류 자격과 국적을 고정시킬 수 없을까.

틀 고정(보기 메뉴)

가이드2-권혜진03

엑셀을 처음 시작한다면 우선 ‘틀 고정’이란 기능부터 익히자. 보기 메뉴에 들어가면 있다. 틀 고정 방법에는 클릭한 셀을 기준으로 하는 틀 고정과 첫 행 고정, 첫 열 고정이 있다.

정렬과 필터(데이터 메뉴)
‘정렬’ 기능은 상위 10위, 하위 10위와 같이 순서를 매기는 데 이용된다. 주의할 점은 병합된 셀이 있을 경우 정렬이 되지 않는다는 것이다. 위 표를 보면 ‘국적’이 두 셀을 차지하고 있는데 이런 경우 정렬이 되지 않는다. 먼저 병합된 셀을 풀어주어야 한다. 정렬 후에는 한 줄의 데이터가 함께 움직였는지 꼭 확인한다.

‘필터’ 기능은 일상적인 취재에서 가장 활용도가 높은 기능이다. 위 표를 정리한 후 체류 자격에 필터를 적용하면 필리핀, 중국 등 원하는 국가만 선택해서 볼 수 있다.

텍스트 나누기(데이터 메뉴)
정보공개청구를 통해 여러 곳에서 엑셀 문서를 얻은 경우, 데이터를 합치기 위해 많이 쓰는 기능이다. 예를 들어 어떤 자료엔 주소의 시·군·구가 따로 입력돼 있고, 어떤 자료엔 한 칸에 합쳐져 있다고 하자. 이때 ‘텍스트 나누기’를 이용하면 하나의 주소를 시·군·구로 나눠줄 수 있다.
화재 발생 일시와 같이 날짜로 입력되어야 할 필드가 ‘20040101’처럼 숫자, 텍스트 등으로 뒤섞여 있을 때 텍스트 나누기를 이용하면 한방에 해결할 수 있다. 해당 필드에서 ‘텍스트 나누기’를 선택한 후 ‘다음’을 두 번 눌러 날짜 형식으로 지정하면 된다.

피벗테이블(데이터 메뉴)
처음에는 익숙해지기 어렵지만 잘 익혀두면 만능 해결사가 되는 기능이다. 예를 들어 국회의원 고액 후원금 자료를 받았을 때 피벗 테이블을 이용하면 정당별, 지역별, 고액 후원자별 합계를 1분 안에 낼 수 있다. 선거 때처럼 시시각각 데이터가 업데이트되는 상황에선 미리 피벗 테이블을 만들어 놓고 데이터만 교체한 후 ‘새로 고침’ 버튼을 눌러 최근 통계를 확인하면 된다.

함수의 활용
엑셀에는 수많은 함수가 있지만 ‘VLOOKUP’ 정도만 알아도 써먹을 데가 많다. 예를 들어 국회의원의 생년, 출신지, 학교가 있는 인물 정보 파일과 고액 후원금 합계 파일이 따로 있는 경우, 두 파일을 국회의원 이름 기준으로 합칠 수 있는 기능이 VLOOKUP이다.

되돌리기(Ctrl+Z)
엑셀 데이터를 만지다 보면 실수할 때가 있다. 당황한 나머지 여태까지 작업한 문서를 저장하지 않고 그냥 종료하는 경우를 본다. 이때 컨트롤Ctrl키와 Z키를 같이 눌러 ‘되돌리기’를 하면 된다. 두 키를 여러 번 누르면 원하는 작업 지점까지 안전하게 돌아갈 수 있다.

 PDF를 엑셀로 변환하기

가이드2-권혜진04매년 3월이면 고위 공직자의 재산신고 내용이 PDF 파일로 관보에 공개된다. 대부분의 언론에선 상위 10걸, 하위 10걸과 같이 보도자료 내용을 그대로 보도한다. 고위 공직자들이 어느 지역에 주로 부동산을 많이 갖고 있는지, 주식은 주로 어떤 종목을 갖고 있는지, 가장 비싸게 신고한 귀중품 순위는 어떻게 되는지, 가장 비싼 자동차를 타는 사람은 누구인지 알 수 있을까. 이런 분석이 가능하려면 PDF 문서를 엑셀 문서로 변환해야 한다. 하지만 PDF 문서의 내용을 복사해서 엑셀 문서에 붙여 넣으면 표의 행과 열이 깨진다. 좋은 방법이 없을까.

Nitro PDF사에서 제공하는 무료 웹 서비스(www.pdftoexcelonline.com)를 이용하면 프로그램을 설치하지 않고도 PDF 문서를 엑셀 문서로 변환할 수 있다. 변환하고자 하는 PDF 파일을 선택한 후 이메일 주소를 넣으면 변환된 엑셀 문서가 메일로 배달된다. 변환할 문서의 양이 많을 때는 Nitro 유료 프로그램이나 어도비Adobe사의 애크로뱃 XI 제품을 쓰는 게 좋다.

엑셀 기반으로 할 수 있는 것
어느 정도 엑셀에 익숙해지면 노드 엑셀Node Excel이나 구글 퓨전 테이블Fusion Table에도 도전해 보자. 노드 엑셀은 엑셀에 플러그인 형태로 설치해서 네트워크 분석을 할 수 있는 프로그램이다. 트위터나 페이스북 데이터를 자동으로 수집할 수 있기 때문에 정치인들의 소셜 네트워크 분석에도 자주 활용된다.

구글 퓨전 테이블을 활용하면 엑셀 자료의 위치 정보를 구글 맵으로 시각화할 수 있다. 전문 GIS 프로그램으로는 ArcGIS(www.arcgis.com)나 퀀텀 GIS(www.qgis.org)가 많이 쓰인다.

주의할 점
정부에서 제공한 데이터는 완벽할 것이라고 생각하지만 예상과 다르게 잘못 입력된 곳이 종종 발견된다. 정당이 입력될 자리에 선거구가 들어간 경우도 있었고, 숫자 끝에 빈칸이 들어가서 텍스트로 인식되는 바람에 합계가 틀려지는 경우도 있다. 따라서 첫 단계인 데이터 정제cleaning를 꼼꼼히 하는 게 무엇보다 중요하다. ‘Garbage in, garbage out.’1) 임을 명심하고 많은 시간을 데이터 검증에 투자하라고 권하고 싶다.