엑셀 데이터 활용하기(1) 데이터 정리・ 파악
with. HEARTCOUNT
안녕하세요. 하트카운트 팀입니다.
데이터를 활용하기 위해 사용하는 툴은 매우 다양합니다. #엑셀, #Tableau, #PowerBI, #HEARTCOUNT 등이 대표적입니다.
그 중에서도 가장 오래 전부터 사용되었고, 여전히 많은 현업 분들이 데이터를 분석하기 위해 활용하는 엑셀에 대해서 다루어 보려고 합니다.
약 세 편에 걸쳐서 작성할 예정이나, 작성 과정에서 편 수는 조정될 수 있습니다.
첫 편으로 데이터를 정리하고 파악하는 것부터 시작하겠습니다.
엑셀에서는 데이터를 활용하기 위해서 함수를 활용하거나 피벗 테이블을 주로 활용합니다.
두 기능에서 오류가 생기지 않도록 하기 위해서 엑셀이 읽기 쉽도록 데이터를 먼저 편집해 주는 단계가 필요합니다.
데이터 정리 1단계. 시트 구성 정돈
데이터를 받게 되면 대체로 엑셀로 오픈할 수 있는 형태(xlsx, csv, ...)로 다운로드됩니다. 이때 바로 활용하기 좋은 형태라면 좋겠지만 그렇지 않은 경우가 더 많습니다.
아래 이미지와 같이 1열부터 값이 채워져 있지 않거나, 열 머리글이 하나로 정해져 있지 않을 때를 예로 들 수 있습니다. 특히 열 머리글이 하나가 아닐 경우라면 아래에 위치한 열머리글이 열 내용으로 인식될 수 있습니다.
해결은 아래와 같이 할 수 있습니다.
1. 열머리글의 병합 풀어주기
2. 각 열을 더 잘 구분하는 열머리글만 남기기
3. 불필요한 빈 행 제거하기
gif로 더 쉽게 이해해 봅시다 :)데이터 정리 2단계. 적합한 셀 서식 지정
엑셀을 활용해 보신 분들은 셀 좌측 상단에 초록색 삼각형이 뜬 것을 보신 적이 있을 겁니다. 이때 마우스를 가져다 두면 그 이유가 나타나는데, 이번에 다룰 내용은 숫자 데이터가 텍스트 서식으로 지정되어 있는 경우입니다.
이번 글에서 활용하는 데이터셋의 경우에는 각 셀의 서식이 잘 지정되어 있는 상태입니다.
그럼에도 잘못 지정된 셀의 서식은 추후 데이터의 집계값을 파악하거나 함수를 사용할 때 문제가 될 수 있으므로 한 번 살펴 봅시다.
먼저 아래의 gif에 빨간색 사각형으로 강조된 위치에서 셀의 서식을 파악할 수 있습니다.
셀의 서식이 잘못되어 있는 경우 아래의 방법을 따라해 보세요. 1번부터 수행하며 그럼에도 안 될 경우 다음 번호로 넘어가세요 :)
1. 열 선택 - 위 gif의 빨간 사각형 안 메뉴를 클릭해 원하는 형식 선택
2. 열 선택 - 우클릭 - '셀 서식'메뉴에서 원하는 형식 선택
3. 열 선택 - 상단 [데이터] 메뉴 - '텍스트 나누기' 기능 활용
데이터 정리 3단계. 필터
필터를 통해서 테이블을 더 보기 좋고 읽기 쉽게 구성할 수 있습니다.
'필터' 기능은 상단 [데이터] 메뉴에서 찾을 수 있습니다.
필터를 걸면 각 열머리글 옆에 삼각형 ▼이 생겨납니다.
삼각형을 클릭하면 해당 열을 정렬 기준으로 지정할지를 선택하고 그 기준도 선택할 수 있습니다. 또한 해당 열에 속하는 값의 종류(목록)도 한 눈에 확인할 수 있습니다.
데이터를 어느정도 정리했으면 이제 데이터를 파악해야 합니다.
데이터의 전반적인 구조와 변수의 종류, 데이터셋의 사이즈를 먼저 파악하게 되면 더 효과적으로 분석의 주제와 방법을 정할 수 있습니다.
이번에는 단계가 아니라 두 가지 방법을 제시하겠습니다.
데이터 파악 방법 1. 클릭과 드래그 이용
가장 쉬운 방법이죠? 그러나 확인할 수 있는 내용은 개수, 합계, 평균 정도로 한정되어 있기 때문에 필요한 상황에 따라서 다르게 활용하면 되겠습니다.
먼저 행의 수를 파악해 데이터의 사이즈를 파악할 수 있습니다.
가장 기준이 될 만한(비어 있지 않은) 열의 셀을 클릭한 후 [control] + [아래 방향키 ️]를 누르면 해당 열의 마지막 셀로 이동합니다.
이 방법으로 비어 있는 셀을 찾을 수도 있습니다.
그 다음으로는 특정 열을 선택해 하단에서 열 데이터 전체의 평균과 개수, 합계를 확인해 봅시다. (아래 이미지의 빨간 사각형 부분)
이때 셀의 서식이 '텍스트'인 열이라면 개수만 확인할 수 있습니다.
데이터 파악 방법 2. 피벗테이블 이용
피벗테이블은 다양한 목적으로 사용할 수 있는 기능입니다. 이번에는 데이터를 파악하는 데 활용해 보겠습니다.
상단의 [삽입] 메뉴에서 '피벗테이블' 기능을 이용할 수 있습니다. 피벗테이블을 생성할 때 행, 열과 값(계산)을 선택하는 과정을 겪게 되는데 이 선택이 어렵다면 '권장 피벗테이블' 기능을 이용해 봐도 좋겠습니다.
아래는 '권장 피벗테이블'을 선택한 결과입니다.
기본 '피벗 테이블' 메뉴를 선택하면 아래와 같은 창이 뜹니다. 표/범위는 기본적으로 값이 입력된 전체 테이블이 선택됩니다. 알아보기 좋게 배치 위치는 새 워크시트를 선택하겠습니다.
확인을 누른 결과는 아래 이미지와 같습니다.
오른쪽에 있는 피벗 테이블 필드를 통해서 열과 행, 값을 지정하게 됩니다. 위의 필트 이름 상자에서 원하는 변수를 아래 영역으로 드래그 합니다.
궁금한 내용을 바꿔가면서 피벗테이블 표시 결과를 확인해 보세요.
아래로 드래그 된 변수명 옆에는 'i'가 생기는데 이것을 클릭하면 사용할 함수와 데이터 표시 형식을 선택할 수 있습니다.
이 이상의 분석을 위한 피벗테이블의 사용법은 추후에 다루어 보겠습니다.
지금까지 엑셀을 활용해 데이터를 정리하고 전반적으로 파악하는 방법을 살펴 보았습니다.
이렇게 엑셀을 이용하는 방식도 쉽고 간편하고 무엇보다 익숙하겠지만 더 쉽고 간편한 데이터 파악 방법도 함께 제시하려고 합니다.
데이터에 대한 시각적 분석을 무료로 할 수 있는 툴 HEARTCOUNT를 활용한 방법입니다.
우선 위의 '데이터 정리 1단계'까지 마무리한 엑셀 파일을 지정 위치에 드래그앤드랍 합니다.
gif 이미지가 짤린다면 클릭해 원본을 확인해 보세요.업로드 후 [다음 단계] 버튼을 눌러, 데이터셋을 테이블 형태로 볼 수 있고 변수별로 카드 형태로도 제시됩니다.
*요약 정보를 바탕으로 각 변수 카드 중 문제가 되는 카드를 활인할 수 있습니다.
이번 데이터셋에서는 비어있는 열이 하나 있고, 숫자형과 범주형 중 형식을 지정해야 할 열이 2개 있다고 하네요.
*요약 정보에서는 데이터셋에서 사용자가 추가로 지정해야 할 변수를 제시해 주고, 의미없는 변수(비어있거나 내용이 동일해 분석 과정에 의미가 없는 열)를 자동으로 파악하고 삭제한 결과를 볼 수 있습니다.
먼저 카드를 볼까요?
각 변수를 이루고 있는 값을 한 눈에 볼 수 있고, 데이터의 전반적인 분포도 시각적으로 파악할 수 있습니다. 또 형식에 따라서 자동으로 시계열인지, 숫자형인지, 범주형인지 구분됩니다. 각 구분은 변수 명 왼쪽에 작은 아이콘으로 표시됩니다. (각 달력, #, abc로 표시)
이때 HEARTCOUNT의 장점은 숫자형 변수들도 자동으로 구간이 지정되어(구간은 지정도 가능) 파생변수를 만들어, 범주화된 채로 표시됩니다. 이 기능은 이후에 더 다채로운 분석을 가능하게 합니다.
변수명 옆에 'i'가 표시된 것은 위의 요약 정보에서 언급된 변수입니다. '수량'과 '할인율'에서 확인되는데, 두 가지 모두 변수의 형식을 무엇으로 할지 지정하라고 알려주는 것이라고 볼 수 있습니다. 등급처럼 구분을 위한 숫자가 아니므로 숫자형 변수로 그대로 두겠습니다.
추가적으로 변수명 오른쪽 아래에 있는 삼각형 ▼을 선택해서 요약값을 확인할 수 있습니다.
주로 사용하고 있는 데이터 활용 방법이나 툴이 있는 분들도 계실 거고, 찾아 가는 단계인 분들도 계실 겁니다.
오늘은 엑셀과 하트카운트를 통한 데이터 정리 방법을 다루었습니다. 두 가지 툴의 장단점이 있을텐데요. 자신에게 맞는 방법을 선택해 활용하시면 되겠습니다 :-)
분석 전 단계인 '데이터 파악'을 학습하는 데 도움이 되었기를 바라며 글을 마무리 하겠습니다.
----------------------------------------------------------------------------------------------------------------------
