예제파일 실습을 통해 파워쿼리로 데이터를 가공하고 셀병합 문제를 해결하는 방법에 대해 단계별로 살펴보겠습니다.
범위를 표로 변경하기: 예제파일의 영업팀-1월 시트로 이동합니다. B2:F15 범위를 선택 후, [삽입] - [표]를 선택하거나 단축키 Ctrl + T 로 표 만들기로 이동합니다. 이후 '머릿글 포함'을 체크해제 후, [확인]버튼을 눌러 범위를 표로 변환합니다.
범위 선택 후 삽입 - 표를 클릭하여 범위를 표로 변환합니다.
표를 선택 후, [표 디자인] - 표 스타일 옵션에서 '머릿글 행'을 체크 해제합니다. 이후 표 스타일을 없음으로 변경하면 기존 범위와 동일한 디자인으로 표가 만들어집니다.
표 스타일을 변경합니다.
파워쿼리 실행하기: 표를 선택 후, [데이터] - [테이블 범위에서]를 클릭하면 파워쿼리 편집기가 실행됩니다. 추가기능 설치로 파워쿼리를 추가했다면, [파워쿼리] - [테이블에서] 를 클릭합니다.
표를 선택 - 데이터 - 테이블/범위에서를 클릭하면 파워쿼리가 실행됩니다.
오빠두Tip : 엑셀 버전에 따라 화면에 표시되는 레이아웃이 다를 수 있습니다. 만약 [테이블/범위에서]가 보이지 않을 경우, [테이블에서]버튼을 클릭합니다.
파워쿼리 편집기가 실행되면서 기본적인 데이터 가공 단계가 추가됩니다. 추가된 단계는 파워쿼리 오른쪽 [적용된 단계]에서 확인할 수 있습니다. 각 단계에 대한 설명은 영상강의를 참고하세요.
파워쿼리가 실행되면서 기본 단계가 적용됩니다.
오빠두Tip : 엑셀 버전에 따라 기본 가공 단계가 추가되지 않을 수도 있습니다.
데이터 가공하기: 기존 단계가 추가되었다면, 단계 왼쪽에 있는 [X] 버튼을 클릭해서 기존 단계를 모두 제거합니다.
적용된 기본 단계를 모두 제거합니다.
범위의 첫번째 행은 불필요한 머리글이므로 제거합니다. [홈] - [행 제거] - [상위 행 제거]를 선택합니다. 이후 제거할 행 개수로 1을 입력 후 [확인] 버튼을 클릭하면 표의 첫번째 행이 제거됩니다.
표의 첫번째 행 (불필요한 머리글)을 제거합니다.
오빠두Tip : 또는 표의 좌측 상단의 [표 모양 아이콘]을 클릭 - [상위 행 제거]를 선택해도 동일한 작업이 실행됩니다.
이후 첫 행을 머리글로 승격합니다. [홈] - [첫 행을 머리글로 사용]을 클릭하거나, 표 좌측 상단 [표 모양 아이콘]을 클릭 - [첫 행을 머리글로 사용]을 클릭하면 첫번째 행이 머리글로 승격됩니다.
표의 첫번째 행을 머리글로 승격합니다.
병합된 셀 채우기: 범위가 표로 변환되면서 셀 병합이 해제되면, 병합된 범위의 첫번째 셀에만 데이터가 입력되고 나머지 셀은 빈칸이 반환됩니다. 따라서 셀 병합이 풀리면서 추가된 비어있는 셀을 채워줍니다. 표의 '담당부서' 열을 선택 후, [변환] - [채우기] - [아래로]를 클릭하면 데이터가 아래 방향으로 채워집니다.
파워쿼리로 반환되는 표는 데이터를 새로고침 할 때마다 표 안에 입력된 값에 따라 넓이가 자동맞춤 됩니다. 만약 표의 열 넓이를 항상 고정해야 할 경우, 표를 선택 - [표 디자인] - [속성] - [열 너비 조정]을 체크 해제하면 표의 열 너비를 항상 고정할 수 있습니다.
안녕하세요. 유튭에서 매번 좋은 강의 보고 있습니다. ^^
회사업무 중에 파워쿼리를 가끔 사용하는데, 늘 같은 문제로 어려움을 겪고 있어 문의드려 봅니다.
파워쿼리 실행 후 폴더에서 불러오기 실행
Content 칼럼 외 나머지 칼럼 삭제
Content 칼럼 확장하면 오류 발생 (오류메세지 :: DataFormat.Error : 입력을 올바른 Excel 문서로 인식할 수 없습니다. I 세부정보 : Binary)
해당 엑셀파일들은 회사 ERP 서버에서 월별로 내려받은 자료를 모아놓은 폴더에서 불러온 자료입니다. 구글링 결과 예상되는 원인은 회사 DRM 문제인 것 같은데. 원인에 대한 해결책은 못찾은 상태입니다.
현재는 매월 파일을 시트추가하여 TABLE로 만들어 놓고 테이블에서 쿼리추가 해서 병합/추가하여 사용하고 있습니다.
오빠두엑셀작성자2021.07.11 06:00
안녕하세요. DataFormat.Error가 발생하는 원인은 다양합니다.
특히 ERP에서 받아온 엑셀 파일(오래된 Oracle 버전을 사용중이거나 할 경우..) 또는 DRM을 사용중일 경우.. 등 여러가지 원인이 있습니다.
DRM의 기본 목적은 파일의 외부 유출을 막는 것이기 떄문에, DRM 걸려있는 파일이라면 파워쿼리로 읽어오지 못하는 것은 어찌보면.. 당연하다고 볼 수 있습니다.
따라서 DRM을 해제한 뒤 다시 시도해보시고, 만약 DRM을 해제해도 오류가 발생한다면 파일 형식이 xls(2007 이전 버전은 아닌지) 또는 데이터 형식이 숫자/문자가 섞여있지는 않은지 한번 확인해보세요.
김유현2022.07.03 18:27
파일 형식 문제일 수 있습니다.
CSV 형태의 파일을 편의상 엑셀 확장자로 내려받도록 설정해두는 경우가 있는데, 만약 이 문제라면,
폴더가 아닌 파일 1개씩 불러올 경우에는 오류가 안 생길겁니다.
그럼 폴더로 불러올때,
Excel.Workbook 말고 Csv.Document 로 로드하시면 됩니다.
유니코드 설정등은 폴더말고 파일로 불러오기 실행한 후에 유니코드 관련 설정들만 복사 붙여넣기하여 완성하시면 될 것 같습니다.
제프2021.07.08 12:04
그냥 선생님. 데이터 관련 거의 모든 프로그램, 코딩 다 하시는 듯.
정말 감사합니다. 시간이 아깝지 않아요.
비오는날커피향기2021.07.08 14:34
파워쿼리의 중요성은 인지하고 있었는데 오늘 강의를 통해서 정리가 되었습니다~ 진짜 감사해요!!! 배움에 감사!! 활용에 즐거움!! 직장인에게는 필수 도구네요!!!
거리2021.07.08 20:03
좋은 내용 감사드립니다.
min****2021.07.09 12:34
쿼리를 어찌접근해야할지 몰랐는데 정말 좋은강의 감사합니다.
다만 쿼리를 적용한파일을 365 웹에서
열었을때 새로고침에 문제가 있습니다.
비즈리스365 사용중입니다.
아 데스크탑에서편집 에서는 새로고침 에 문제가 없습니다
오빠두엑셀작성자2021.07.09 22:28
안녕하세요.
어떤 오류가 발생하시나요? 좀 더 자세한 설명을 적어주시겠어요? :)
또는 홈페이지 Q&A 게시판에 올려주시면 확인 후 답변 드리겠습니다.
https://www.oppadu.com/question
감사합니다
SEANPAUL2021.07.12 06:04
최고에요!!
나아두2021.07.12 10:35
유튜브 보고 바로 가입했습니다~~
엑셀어려워2021.07.16 11:08
파워커리에서 데이터형식이 안나오는데 어떻게 해야하나요?ㅠㅠ
오빠두엑셀작성자2021.07.16 23:54
안녕하세요.
파워쿼리 편집기에서 열을 선택 - [변환] - [데이터 형식]을 확인해보시겠어요?
감사합니다.
jacool82@naver.com2021.07.16 15:56
이 영상보고 가입하게 되었습니다. 제가 엑셀은 10%기능도 쓰지 않고 있다는걸 느끼게 하네요.
긍정적인마인드2021.07.18 19:53
파워쿼리 머리글 왼쪽에 아이콘이 안나오는 관계로 data taype을 누르고 Percentage로 변경하려고 하는데 안나오네요/혹시 해결 방법이 있을까요?
현재 Excel2016 사용하고 있습니다.
오빠두엑셀작성자2021.07.19 02:54
안녕하세요.
필드에 입력된 데이터가 모두 숫자로 이루어져 있는지 한번 확인해보세요.
데이터에 문자가 포함되어 있을 경우 백분율 형식으로 변경이 불가능합니다.
감사합니다.
갖고싶다 파워쿼리2022.03.03 18:12
예제파일로 그대로 작업 했는데 백분율 형식이 안나오네요
오빠두엑셀작성자2022.03.09 22:11
안녕하세요.
시트에 표시되는 표에는 표시형식을 한번 더 적용해주셔야 합니다.
범위 우클릭 - 셀서식에서 백분율 형식으로 변경해보세요.
회사업무 중에 파워쿼리를 가끔 사용하는데, 늘 같은 문제로 어려움을 겪고 있어 문의드려 봅니다.
특히 ERP에서 받아온 엑셀 파일(오래된 Oracle 버전을 사용중이거나 할 경우..) 또는 DRM을 사용중일 경우.. 등 여러가지 원인이 있습니다.
DRM의 기본 목적은 파일의 외부 유출을 막는 것이기 떄문에, DRM 걸려있는 파일이라면 파워쿼리로 읽어오지 못하는 것은 어찌보면.. 당연하다고 볼 수 있습니다.
따라서 DRM을 해제한 뒤 다시 시도해보시고, 만약 DRM을 해제해도 오류가 발생한다면 파일 형식이 xls(2007 이전 버전은 아닌지) 또는 데이터 형식이 숫자/문자가 섞여있지는 않은지 한번 확인해보세요.
CSV 형태의 파일을 편의상 엑셀 확장자로 내려받도록 설정해두는 경우가 있는데, 만약 이 문제라면,
폴더가 아닌 파일 1개씩 불러올 경우에는 오류가 안 생길겁니다.
그럼 폴더로 불러올때,
Excel.Workbook 말고 Csv.Document 로 로드하시면 됩니다.
유니코드 설정등은 폴더말고 파일로 불러오기 실행한 후에 유니코드 관련 설정들만 복사 붙여넣기하여 완성하시면 될 것 같습니다.
정말 감사합니다. 시간이 아깝지 않아요.
다만 쿼리를 적용한파일을 365 웹에서
열었을때 새로고침에 문제가 있습니다.
비즈리스365 사용중입니다.
아 데스크탑에서편집 에서는 새로고침 에 문제가 없습니다
어떤 오류가 발생하시나요? 좀 더 자세한 설명을 적어주시겠어요? :)
또는 홈페이지 Q&A 게시판에 올려주시면 확인 후 답변 드리겠습니다.
https://www.oppadu.com/question
감사합니다
파워쿼리 편집기에서 열을 선택 - [변환] - [데이터 형식]을 확인해보시겠어요?
감사합니다.
현재 Excel2016 사용하고 있습니다.
필드에 입력된 데이터가 모두 숫자로 이루어져 있는지 한번 확인해보세요.
데이터에 문자가 포함되어 있을 경우 백분율 형식으로 변경이 불가능합니다.
감사합니다.
시트에 표시되는 표에는 표시형식을 한번 더 적용해주셔야 합니다.
범위 우클릭 - 셀서식에서 백분율 형식으로 변경해보세요.