본문 바로가기
📚배우기

[1주차] 엑셀 API 기초 3주 완성_데이터 관리 규칙/열 피벗 해제

by 오자몽 2023. 1. 20.

📔 오늘의 공부 목록

올바른 데이터 관리 규칙
파워쿼리를 이용한 열 병합 해제
그 밖의 유용한 엑셀 팁_피벗 테이블 필드 구성방법 / 슬라이서 / TEXT 함수

 

엑셀에선 원본데이터를 기본으로 하여 필요한 데이터를 피벗테이블 등으로 가공하는게 올바른 데이터 관리이다.

원본데이터가 올바르지 않은 구조로 되어있다면 데이터를 추출하는데 어려워 활용도가 떨어진다.

 

 

엑셀에서 올바른 구조로 데이터를 관리하기 위해 지켜야 할 3가지 중요 규칙

 

[예시1]

1. 머릿글은 반드시 '한 줄'로 관리하기

 

2. 원본데이터에는 '셀 병합' 절대 사용하지 않기

 

위 예시1에서 보이는 것 처럼 머릿글이 2줄+셀병합으로 이루어진 경우, 보기는 편할 수 있으나 원본데이터로서의 역할을 전혀 하지 못한다. 또한 데이터를 가로로 늘어놓는 것은 올바른 구조라고 볼 수 없다.

 

3. 집계 데이터는 원본에서 제외하기

 

보통 마지막 행에 '합계' 등을 포함하여 집계데이터를 추가하곤 하는데, 피벗 테이블 작성 시 마지막 집계된 데이터까지 원본으로 인식하여 값을 추출하므로 결과값이 정확하지 않다.

(예를들면  [피벗테이블 합계=각 항목의 합 + 합계값]으로 계산하게되어 결과값이 정확하지 않다.)

 

 

세로방향 블록쌓기_데이터 관리의 기본

위에서 한 번 언급했듯이 데이터는 가로로 길게가 아닌 세로로 쭉 나열되어 있어야 관리가 쉽다.

아래 사진처럼 가로로 쌓은 데이터는 피벗테이블 필드에서 날짜를 일일히 선택해야 한다.

>>> [날짜]라는 필드만을 선택할 수 있게 만들기 위해선 세로쌓기를 해야한다.

 

[예시2] 가로쌓기 데이터 예시

 

세로로 쌓은 데이터는 피벗테이블을 통해 보고싶은 형식으로 데이터를 자유롭게 변환할 수 있다.

 

[예시3] 세로쌓기를 통한 올바른 데이터로 원하는 값을 볼 수 있다.

 

[예시3]에서 피벗을 해서 날짜별로 피벗테이블을 만드니 [예시2]와 같은 결과가 나온다

[예시2]는 원본데이터가 아닌 이미 피벗된 데이터로 볼 수 있다.

 

그렇다면 이미 잘못 만들어버린 데이터를 원본 데이터로 복구할 수 있는 방법이 없을까?

 

파워쿼리의 [열 병합 해제]

 

나의 퇴근시간과 엑셀 노가다를 피하기 위해 파워쿼리의 "열 피벗 해제"를 사용한다.위에 나와있는 잘못된 데이터들을 파워커리의 [열 피벗 해제]를 통해 다양하게 활용 가능한 원본데이터를 만들 수 있다.

영어로 unpivot > 피벗을 해제한다 + 가로로 잘못 나열되어있는 열을 해제한다 > 열 피벗 해제

 

 

파워커리 실행하기

 

표 전환 : 범위 선택 > Ctrl+T or 삽입 -  표 > 머릿글 포함 체크(머릿글이 한 줄일 경우) > 확인

파워커리 실행 : 표 선택 > 데이터 > 표/테이블 범위에서 선택

 

실행된 파워커리 창////무섭게 생김

 

가로쌓기 데이터 세로쌓기로 만들기

 

행 선택 > 변환 > 열 피벗 해제 옆 삼각형 > 다른 열 피벗 해제(1주차~)

 

 

 

그러면 깔끔하게 가로로 잘못 나열되어 있던 데이터가 자동으로 세로쌓기가 된 것을 볼 수 있다.

홈 탭의 닫기 및 로드를 누르면 깔끔하게 정리된 원본 데이터를 얻을 수 있다.

 

 

 

원본 데이터에 데이터가 추가된다면 >> 쿼리로 만들어진 표 우클릭 > 새로고침을 하면 추가된 데이터까지 적용된다.

 

 

머릿글이 여러 줄인 경우

 

예시 1과 같이 머릿글이 여러 줄 인 경우
1. 행/열을 바꿔서 머릿글을 세로로 나열
2. 머릿글을 구분기호를 추가하여 한 줄로 합친다.(머릿글에 사용되지 않은 구분기호로 사용)
3. 다시 머릿글로 올려준다.
4. 열 피벗 해제 위와 같이 동일하게 진행
5. 구분기호를 기준으로 행을 나눠준다.

 

 

이건 복잡해서 여러 번 다시 시도해봐야 겠다.

 
 

그 밖의 유용한 엑셀 팁

이건 강의를 들으면서 우와우와 하면서 알게된 팁들인데 까먹을까봐 적어놓음
 

 

1. 피벗테이블 할 때마다 값을 어디에 놓는지 헷갈린다면

십자가를 그리고 그 십자가에 어떤 값을 넣을 지 생각하여 필드를 끌어넣으면 된다!!! 별거 아닌데 굉장히 충격적!!
 

 

2. 뭔가 있어보이는 피벗테이블 슬라이서 만들기

 

필터보다 간지나는 슬라이서

 

피벗 테이블 분석 > 슬라이서 삽입 > 슬라이서가 짜잔하고 나온다.

필터를 매번 설정하고 누를 필요 없이 슬라이서로 자유롭게 원하는 데이터만을 추출하여 볼 수 있다.

 

 

3. 요일넣기 =TEXT(날짜셀," aaa") // "ddd"는 영문요일

 

 

 

출퇴근에 오며가며 Shorts로 친해진 [오빠두엑셀]에서 3주 챌린지를 시작했다.

여러 꿀 팁을 보는데 막상 쓰려고 하면 머릿속에 남아 있지 않아 이번엔 복습 겸 해서 길게 글을 작성해봤다.

첫 포스팅이라 시간이 너무 오래걸림...😂😂😂