엑셀 데이터와 파워 쿼리(Excel Data & Power Query)_11.23

안녕하세요 루에요☺️

오늘은 프로그램 교육 4일차 엑셀로 데이터를 다루는 방법을 배우는 날이에요

 

어제와 오늘 순서를 바꿔서 진행하기로 해서 오늘 데이터 수업을 들어가기로 했습니다.

 

우선 데이터를 보기 쉽게 하려면 어떻게 정리하면 좋을까요?

엑셀에는 "피벗 테이블"이라는 기능이 있어요!

이 기능은 표에 있는 데이터를 엑셀이 읽고 사용자가 원하는 데이터를명을 필드에 옮겨놓으면 자동으로 테이블을 제작해주는 도구에요.

피벗테이블 만드는 방법 다같이 알아볼까요?

 

🤔 피벗테이블 어떻게 만드는 걸까?

피벗테이블을 만들기위해서는 테이블을 제작하기위한 표가 필요해요

화면에 보이는 자료는 단순 정보라 표로 변환해 주어야 해요!

Ctrl + A(영역 전체선택)을 통해 표 영역을 자동으로 잡아주시거나, 표로 변환하고 싶은 자료가 포함된 영역 내 셀을 선택하여 액티브 셀로 활성화 시키신 후 삽입에서 표(Ctrl + T)로 테이블을 만들어 줍니다.

* 엑셀은 표를 만들 때 Active cell처럼 활성화 셀이 있으면 그 셀의 상하 좌우를 기점으로 자신과 연관된 데이터로 자동 인식하는 특성이 있습니다. 그래서 데이터가 자동으로 함께 잡히는 장점이 있어요!

표로 제작했다면 표 이름을 정해주고 중복된 항목이 있는지 한번 검사해줍시다~

검사 후 삽입 탭에서 피벗테이블 버튼으로 진입하셔도 되고, 활성화된 테이블 디자인 탭에서 피벗 테이블로 요약으로 진입하셔도 됩니다.

두 기능모두 동일 기능입니다.

피벗테이블을 제작하기까지 전 과정이 끝났다면 새 워크시트에 제작할지, 기존 워크시트에 저장할지 선택하고 기존 워크시트라면 하단의 시트내에서 위치를 정해 좌표를 찍고 확인을 눌러주면 됩니다.

 

 

 

 

 

저는 피벗테이블이라는 기존 워크시트의 B10셀을 좌표로 찍고 제작해보았어요!

제작하게 되면 해당 선택 셀을 기점으로 피벖테이블 생성위치가 잡히며 우측에는 데이터로 잡은 표가 자동으로 잡히며 옵션으로 넣을 수 있도록 필드로 추출되어 있음을 확인할 수 있습니다

값에 주문 번호값을 넣었더니 개수라는 값으로 추출이 되었네요~!

피벗테이블의 장점은 필드에 원하는 항목을 기입하면 해당 테이블이 바로 생성된다는 점인데요

또하나의 장점은 생성된 테이블에서 왜 그 값이 나왔는지에 대해 출처를 확인하고자 한다면 더블클릭을 통해 해당 테이블을 빠르게 추출하여 보여준다는 강점도 있습니다.

저는 2017 당일배송건을 더블클릭해서 해당 테이블의 상세 정보를 추출해서 확인해보았어요!

더블 클릭을 하게 되면 정보가 추출됨에 따라 하단에 새 시트가 생성되는 것도 확인하실 수 있답니다.

 

이처럼 피벗 테이블은 피벗테이블은 드래그 드랍을 하여 표를 제작하는 방식으로 표로 사용자와 소통하는 제작 양상을 가지고 있어 대화형 테이블이라고도 합니다.

 

다만 피벗테이블은 레코드의 카운트해 주는데엔 유리하지만 동명이인과 같은 동일 레코드를 제거해주진 않는다는 약점이 있습니다😮‍💨

 

 

 

 

 

피벗테이블 필드는 주문일자를 행에 넣으면 자동으로 엑셀이 그룹화해서 년, 분기, 개월로 그룹화를 시켜줍니다.

그런데 이를 해제하고 다시 마우스 우클릭으로 그룹화해서 묶게되면 하나의 필드에 다단계로 계층구조 형태로 그룹핑이 되게 됩니다

 

 

 

 

 

기본적으로 테이블은 압축형태로 표시되나 디자인 테이블에서 바꿀 수 있습니다

 

거래에 대한 월별 점유율을 구하려면 어떻게 해야할까?

 

피벗 테이블로 필터 추가하기

 

슬라이서 삽입하기

 

 

 

 

 

 

비활성 슬라이스를 숨기고 싶을때는 하단과 같이 하면 됩니다!

 

새로운 과제 시작을 위해 또다른 피벗 테이블을 만들어 보아요!

 

 

 

 

 

 

다음으로는 피벗테이블에서 만들어진 매출표를 일정 값에 따라 그룹화를 시킬거에요

 

 

 

1. 날짜로 취급시키고 연월일로 구분하고 싶다면 하이푼이나 슬래쉬로 구분해주어야 합니다
2. 구분해주면 주문일자를 행에 넣었을 경우 해당화면처럼 엑셀에서 알아서 구분해서 그룹을 지어줍니다
3. 만일 일자별로 주문 건수를 확인하고자 한다면 그룹 풀기로 일자별로 확인하면 됩니다
4. 일자일때 다시 그룹화시켜서 원하는 분기나 월, 일자로 묶고싶다면 우클릭 후 그룹핑을 하면 된다. 그러나 대충 그룹핑이 된다는 점이라는 문제가 있습니다
5. 슬라이서를 만들때 우측에서 피벗테이블 메뉴에 피벗테이블 종류항목을 드래그 앤 드롭하면 상단에 리스트가 있는 슬라이서를 만들 수 있습니다
6. 피벗테이블을 제작한 후 원하는 형태로 빠르게 바꾸고 싶다면 엑셀에서 지원하는 보고서 레이아웃이 있으니 적재적소에 활용하면 좋습니다
7. 다음은 보고서 레이아웃 → 테이블 형식으로 표시의 예시 화면이다
8. 주문번호를 값에 넣는다 → 주문번호를 값에 또 부여한다 → 두번째 부여된 주문번호 피벗테이블에 우클릭으로 값 필드 설정에 들어간다 → 열 합계 비율을 선택한다 → 분기와 년을 선택하여 피벗테이블을 생성 → 생성된 피벗테이블에 활성화되지 않은 탭이 있다 → 해당 탭은 데이터가 없는 항목으로 제거하고 싶다면 슬라이서 - 슬라이서 설정 버튼을 - 데이터가 없는 항목 숨기기 - 확인 순서로 해당 데이터는 숨길 수 있다
9. 피벗테이블의 이름과 같이 설정을 바꾸고 싶다면 더블클릭을 통해 설정에 들어간 후 수정을 진행할 수 있습니다

 

 

 

 

---

1. 빈 셀에서 피벗테이블을 만들고자 하는 경우 피벗테이블을 누르고 표 범위를 선택후 제작하고자 하는 위치를 잡는다
2. 구간값을 줄때 쓰는것이 그룹화
3. 우클릭 → 그룹 →
4. 파워피벗을 쓰려면 데이터 모델이 있어야 한다
5. 상단의 파워피벗 → 데이터 추가 → 표를 선택하고 데이터를 추가하면 외부 창이 열린다
6. 보낸 데이터를 기반으로 피벗테이블또한 새로 만들 수 있따.
7. 파워피벗을 통해서 만든 피벗테이블은 이전에 일반 표를 통해 만든 피벗테이블과는 다른 양상을 가진다
8. 이렇게 만든 피벗테이블을은 값필드 설정에 들어가면 맨 하단에 고유 갯수라는 탭이 생겨있다
9. 테이블이 두개여도 서로 각자 만들면 테이블이 연동되지 않는 경우가 있는데 이때 두 테이블을 함께 연동시키고자 한다면

---

1. min 함수는 최솟값을 의미
2. 파워피벗과 피벗테이블로 연결되어있기 때문에 데이터를 수정하면 연동되어서 내용이 바뀐다
3. 첫 고객의 주문해를 알기 위해서는 앞에 YEAR을 붙여줘야 한다.
4. @는 각각의 참조를 할 경우를 의미한다 각각의 고객행위를 기준으로 볼 때
5. 파워피벗을 닫았을 경우 ㅍ워피ㅣ벗 탭에 들어가서 데이터모델 관리를 누르면 다시 열린다
6. 고유 개수 : 중복 고객을 제거하고 고유한 인원으로 갯수를 추출하는 것

---

1. 열의 갯수가 같고 순서가 같다
2. 순서가 다르면 고민해야한다
3. 하나로 만든다의 개념은 행이 늘어난다는 개념
4. 두개의 테이블을 하나로 만든다는 것은 데이터 두개를 아내로 늘어나는 경우가 있고 테이블이 옆으로 열이 늘어나는 경우로 둘을 분리해서 이야기하는데 옆으로 늘어나는것이 브이룩업이다.
5. 데이터 → 데이터 가져오기 → 폴더/파일 선택(팩트테이블) → 결합 → 데이터 결합 및 변환 → 파일병합화면 → 샘플파일 선택 → 파일을 선택하면 미리보기 화면도 지원한다 (미리보기가 하나만 있는경우 시트가 하나만 있음을 의미함) → 폴더아이콘을 누르면 폴더에 있는 모든 시트를 넣겠다는 의미 (폴더 클릭) →
6. 파워쿼리가 열리면 엑셀로 돌아갈 수 없다. 마무리를 해야 나갈 수 있다.
7. 파워쿼리는 Ctrl +Z 로 돌아가는것이 불가능하다 대신 오른쪽에 적용할 단계에서
8. 데이터가 파워쿼리에서 뜨지않을 경우 파워쿼리 내부로 들어가서 새원본 → 파일선택 → → 데이터변환(파워쿼리에서 그대로 열라는 명령)
9. 바이너리 초록색으로 뜨는 것들은 안에 더 추가적인 무언가가 있다는 것이다
10. 사용자 지정 열에 들어가는 언어는 대소문자를 구분하는 언어
11. 사용가능열에 뜨는 컨텐츠를 더블클릭하면 알아서 참조가 드러간다
12. 구문오류가 있는지 보고 확인을 클릭한다.

 

 

불법으로 가져가시거나 공유하시는 행위는 일절 금지입니다.

필요하시다면 댓글로 말씀을 물어주세요☺️