D.S

www.ciokorea.com

고수의 노하우··· 엑셀로 데이터 다루기 '5가지 팁' - CIO Korea

고수의 노하우··· 엑셀로 데이터 다루기 '5가지 팁' - CIO Korea 고수의 노하우··· 엑셀로 데이터 다루기 '5가지 팁' How To / 애플리케이션 / 자기계발 Computerworld 데이터 작업이 많은가? 원하는 데이터를 필요한 형식으로 일관되게 얻기 위해 통계 모델을 이용하지는 않을 것이다. 데이터 청소나 포맷을 많이 하는 사람이라면 파이선(Python)이나 오픈리파인(OpenRefine)이나 R과 같은 특별한 툴의 사용에 주목하곤 한다. 하지만 단순히 엑셀 스프레드시트 만으로도 데이터를 강력히 조작할 수 있는 여지가 많다는 점은 잘 모를 것이다. 물론 적절한 공식을 변경할 줄 알아야 한다는 전제조건이 있다. 최근 개최된 2014년 컴퓨터지원 보고 컨퍼런스(Computer Assisted Reporting)에서는, 뉴욕의 신문사인 디지털 퍼스트 미디어(Digital First Media)의 수석 데이터 전문가인 메리조 웹스터가 자신의 엑셀 기술을 공유했다. 웹스터는 “이전에는 왜 이런 것들을 몰랐지?” 라고 말할 수 있는 것 한가지라도 배우길 바란다고 말했다. 날짜 기능(Datefunctions) 팁 1 : 날짜 데이터를 다른 필드로 분리하라 엑셀을 사용하여 연,월,일을 별개의 필드로 분리하려면 =Year(CellWithDate), =MONTH(CellWithDate), 그리고 =DAY(CellWithDate) 등의 공식을 사용할 수 있다. 이러한 방식으로 연월일을 분리하는 것은 마이크로소프트 엑세스에서도 지원 가능하다고 웹스터는 말했다. 또한 엑셀에서 =WEEKDAY(CellWithDate)를 기입함으로써 연월일을 입력했을 때 요일 정보를 얻을 수 있다. 하지만 요일 대신 숫자가 나오므로 유의해야 한다. 예를 들어, 1은 일요일, 2는 월요일 과 같은 식이다. 숫자 대신 요일을 나타내기 위해서는 WEEKDAY 함수의 결과값인 숫자에 포멧 셀(Format cells) > 커스텀(Custom)을 누르고 타입(Type) 텍스트 상자에 ddd를 누르게 되면 알파벳 세 자리로 된 요일 결과를 얻을 수 있으며 dddd를 누르게 되면 전체 요일 날짜를 얻을 수 있다. 팁 2 : 생년월일로 나이를 확인하는 방법 다른 사람의 생년월일 정보를 알 경우, 오늘 기준의 현재 나이를 알 수 있다. =DATEDIF()와 =TODAY()기능을 이용하면 된다. TODAY()는 여러분도 쉽게 유추 가능하듯 오늘의 날짜를 입력해 준다. DATEDIF()는 제시된 두 날짜 사이의 차이를 보여주는데 구체적인 공식은 다음과 같다. =DATEDIF(Date1, Date2, 측정단위) 따라서 현재의 나이를 알기 위해서는 다음과 같은 공식을 사용하면 된다. =DATEDIF(CellWithBirthday, TODAY(), “y”) 상기 함수의 “y”부분은 몇 살 인지만 표기할 뿐 나이자료를 반올림하지 않는다. 데이터 재가공(Reshaping data) 팁 3: 한가지 행으로부터 다수의 행을 만드는 법 데이터 상의 포맷이 관찰을 위해서는 하나의 행만을 필요로 할 때도 있다. 하지만 실제로 갖고 있는 정보를 통해 여러 가지 관측을 내릴 수 있다. 웹스터는 일례로 미국 건보개혁법안의 가격책정 정책에서, 각 연령대(1-20세, 21세, 22세 등)마다 행이 설정되어 있다고 설명했다. 한편 몇몇 시각화 및 분석 툴은 계획/가격 조합에 대한 각각의 행에 대한 조합을 요구하지 다양한 가격에 대한 하나의 행을 요구하지 않는다. 타블로(Tableau) 시각화 소프트웨어는 행 당 여러 개의 데이터포인트가 아닌 하나의 데이터 포인트를 필요로 하는 툴 중 하나다. 따라서 벤더들은 타블로 리셰이퍼 툴(Tableau Reshaper Tool)을 통해 엑셀 최신버전과 호응을 이룰 수 있도록 만들었다. 현재 태블로 웹사이트에서 다운로드 받을 수 있다. 애드인 하나가 엑셀 2010용이라고 밝히고 있지만, 테스트 결과 윈도우 8 PC에 설치된엑셀 2013과도 호환됨을 확인했다. 2014 CAR(2014 Computer Assisted Reporting conference) 참석자 몇몇에 따르면 이들이 많은 시간을 들여 대규모 데이터셋을 바꾸곤 했다. 수작업으로 잘라 오려붙여넣기를 했던 것이다. 그들은 타블로 툴이 많은 시간을 절약해줄 것이라고 기대했다. 한편 이를 사용하기 위해서 다른 타블로 소프트웨어를 자신의 컴퓨터에 갖고 있어야 할 필요는 없다. 여러분이 행 ID 열(row ID columns)로 관리하고 있는 것들은 좌측에 위치되어야 하고, 데이터 열들이 오른쪽에 와야 한다. 리셰이퍼 툴의 사용을 위해 커서를 사용자가 전환하고자 하는 첫 번째 셀에 위치시킨다. 그리고 나서 타블로 메뉴로 들어가 데이터 리셰이핑(reshape data)를 선택하고 OK를 누른다. 아래에서 간략한 예시를 확인할 수 있다. 팁 4 : 분류 가능한 데이터를 좀 더 쉽게 만들기 데이터 포맷 관련 흔한 문제가 있다. 분류 가능한 데이터가 아니라 워드 문서와 비슷한, 칼럼 헤더(column header)가 있는 스프레드 시트로 인한 문제다. 스프레드시트인데 팀의 이름이 한 행에 있고 그 다음 행에 팀의 소속 선수, 그 다음 행에 다른 팀의 이름, 그리고 다른 팀 선수들의 이름이 있는 스프레드시트가 그 좋은 예시이다. 즉 칼럼 헤더가 데이터에 고루 뿌려져 있지 않아 워크시트 분석이 어렵다. 왜냐하면 팀 별로 데이터를 소팅이나 필터링을 하거나, 시각화 하기가 쉽지 않기 때문이다. 새로운 열을 더하여 팀 선수 이름을 추가함으로써 이러한 문제에 대응할 수 있다.   “이것이 가능한 이유는 따라야 하는 패턴이 있기 때문이다”라고 웹스터는 말한다. 상기의 예시에서, 포지션 열은 팀 이름 행을 위해 비워져 있으며 선수 행을 위해 채워져 있다. 손수 팀 이름을 첫 셀에 입력하게 되면 사용자는 다음의 공식을 이용하여 자동적으로 나머지를 채울 수 있다. =IF(B3=””,A3,C2) 이것이 의미하는 바는 만약 B3셀이 공란이라면, 첫 열의 동일 행(상기 예시에서는 A3)에 셀값을 입력해야 한다는 것이다. 그렇지 않다면 바로 그 위에 있는 셀값을 입력할 수 있다.(상기 예시에서는 C2인데 이는 전체 선수 행의 바로 윗 행에 팀 이름이어야 한다.) 첫 번째 헤더 행을 손수 입력한 후 첫 선수 행부터 시작한다는 점을 유념하라. 검색과 바꾸기 팁 5 : 새로운 열을 만들어라 문서 편집기와 마찬가지로 콘트롤-F 기능을 이용하면 엑셀에서 검색과 바꾸기가 가능하다는 사실을 알고 있을 것이다. 하지만 아마도 엑셀에서 현재의 열에 검색과 바꾸기 기능을 이용하여 완전히 새로운 행을 만들 수 있다는 사실은 잘 모를 것이다. 이를 위해서는 =SUBSTITUTE 기능이 필요한데 자세한 공식은 다음과 같다. =SUBSTITUTE(CellWithText, “oldtext”,”newtext”) 다른 워크시트의 데이터 참조(lookup)에 필요한 VLOOKUP() 함수 등 웹스터가 제공하는 더 많은 엑셀 조언이 필요한 경우, 웹스터가 작성한 PDF 문서(My Favorite Excel Things 2014) 와 샘플 워크시트 를 내려받기를 권한다. ciokr@idg.co.kr   인쇄