엑셀/파워쿼리 공부(20)
-
파워쿼리로 문자 검색기 만들기(복수의 단어)
이번 포스팅에서는 파워쿼리를 이용해서 검색 기능을 만들어 봅니다. 최근 프로젝트에서 품목 속성, 특히 명칭의 정비하는 일을 하다보니 부쩍 문자 검색과 관련된 작업에 관심이 많아졌네요. 예전 직장에서도 이와 비슷한 일을 한적이 있었는데 정말 힘들었던 기억이 납니다. 엄청난 노가다 작업이기도 한데요. 다행히 그당시에는 TFT 내에 VBA를 잘 다루는 형님이 계셔서 좀 수월 했죠. 하지만 엑셀도 발전해서 최근에 나온 FILTER 함수는 아주 기가 막히게 좋습니다. 이걸 보면 앞으로 VBA를 쓸일이 있을까 하는 생각이 들정도로 간편하고 뛰어나다는 생각이 듭니다. 단, 이 함수는 구독형인 Office 365버젼에서만 지원됩니다. 아무래도 Microsoft가 수익성이 좋은? 구독형 비지니스로 아예 오피스를 다 넘기..
2020.11.19 -
파워쿼리를 이용하여 엑셀에 입력한 조건으로 DB 쿼리하기
구글링을 해서 해외 블로그에서 찾은 방법으로 요약하면 엑셀 시트에서 입력한 값을 SQL WHERE 절에 넣어서 데이터를 가져오는 방법입니다. DB에서 데이터를 가져올 때 SQL문을 사용하면 필요한 만큼 데이터를 가져올 수 있죠. 게다가 필요한 정보를 SQL로 쿼리 하여 아예 구성해서 가져올 수 있기 때문에 파워쿼리 또는 파워피벗이 해야 하는 일을 나누어 최적화가 가능합니다. 요즘 읽고 있는 책에도 이 방법을 1순위로 추천하고 있네요. 특히 MS에서 다운로드 받아 구성한 Contoso DB가 양이 너무 많아 줄이지 않으면 제 PC에서 감당이 안되네요. 그래서 이왕 하는 김에 이것도 까먹지 않기 위해 포스팅 합니다. 1. 먼저 DB에서 데이터를 가져오기 부터 시작합니다. 데이터 가져오기 에서 SQL Serv..
2020.11.10 -
엑셀 & 파워쿼리 영문 대소문자 구분
요즘 자재 마스터를 클랜징 하는 일을 하고 있는데요. 파워쿼리가 작업에 많은 도움을 주고 있습니다. 자재의 명칭이 대문자와 소문자를 규칙없이 등록되어 있어서 이것을 골라내다가 매우 중요한 사실을 알게 되어 포스팅합니다. 데이터 베이스에서는 데이터 값의 영문 대,소문자를 구분 할까요? 제가 사용경험이 있는 2개의 DB만 따져보겠습니다. 먼저. 오라클 DB 에서는 구분합니다. 그리고 MSSQL은 쿼리에서 구분을 하지 않습니다. (물론 구분해서 쿼리하는 것도 가능합니다. 기본 기능이 그렇다는 얘기입니다. ) 엑셀은 구분 할까요? 검색이나 필터, 수식에서 구분할까요? 피벗 테이블로 구성하면 같은 항목으로 볼까요? 아니면 두줄로 나누어 별도의 데이터로 인식할까요? 하나 씩 정리 해보죠~! 1. 엑셀 시트에서 구분..
2020.10.23 -
[파워쿼리-Tip] PDF 파일의 표를 엑셀로 가져오기
이번 포스팅은 파워 쿼리의 데이터 가져오기 방법 중 PDF 파일의 표를 쿼리로 가져오기에 대해서 소개합니다. 1. 가져오기 예시를 통한 기능 소개 '데이터>데이터 가져오기> 파일에서> PDF에서'를 클릭합니다. 클릭하면 나타나는 파일탐색기로 가져올 파일을 선택합니다. PDF 파일을 선택하고 확인을 클릭합니다. 오늘 가져올 파일은 국민은행에서 제공하는 보고서를 다운로드하였네요. 가져오기를 누르면 파워 쿼리가 파일을 분석하여 다음 그림처럼 파일 안에 있는 표(Table)를 찾아서 가져올 데이터를 선택할 수 있도록 해줍니다. 가져올 항목 목록에 페이지가 괄호 안에 표시되어 있으니 필요한 데이터가 위치한 페이지의 내용만 미리보기를 통해 확인해보고 쿼리를 생성하면 됩니다. 3페이지에 있는 'KOSPI 종합지수 추..
2020.07.11 -
[파워쿼리] 파워쿼리 쿼리 추가 - union all
지난 쿼리 병합 포스팅에 이어 오늘은 쿼리추가 에 대해서 정리를 해보자. 쿼리 병합은 조인이다. 그럼 쿼리 추가는 ?? 포스팅 제목 처럼 SQL 의 'Union all' 인 셈인데, 조인은 엑셀에서 처리가 어려우니 당연히 파워쿼리로 처리하는 것이 데이터 다루는데 좋다는 것이 명확한다. 그러나 '쿼리추가'는 언제 써야 하나? 왜 이런 고민이 드냐하면... 데이터 전처리는 엑셀 그자체로 너무 훌륭한 편? 이기 때문에 데이터를 더 붙일 일이 있으면 수고 스럽지만 그냥 시트에서 복사해서 붙여넣기 작업을 하면된다. 굳이 어렵게 쿼리 추가를 익힐 이유가 없다. 하지만 파워쿼리로 익혀두면 발생되는 장점, 데이터 전처리의 자동화를 생각해보면 솔깃할 수 밖에 없을 것이다. 데이터 수집 과정에서 흔히 겪는 문제인 두가지 ..
2020.06.19 -
[파워쿼리] 웹에서 데이터 가져오기
파워쿼리를 사용해서 웹에서 데이터를 가져 올 수 있다. 방법은 매우 간단하다. '데이터 > 데이터 가져오기 및 변환 > 웹' 을 클릭하면 URL을 입력하는 창이 뜨고 여기에 가져올 웹 주소를 입력하고 확인을 누르면 끝이다. 이것까지는 정말 간단하다. 하지만.. 더 유용하게 사용하려면 HTML 등 추출 할 웹을 구성하는 관련 구조에 대해서 공부가 필요하다. 가지고 올 웹 페이지의 주소를 복사하여 입력 창에 넣고 확인을 누르자. 지금 가져올 데이터는 '시가총액 상위 50개의 시세 정보' 이다. 코스피 를 선택하고 로드를 클릭~! 자. 이제 내일 장이 열리면 새로고침을 해서 네이버에 들어가지 않아도 시세를 확인 할 수 있다. 단, 나는 Page 단위로 가져오기 밖에 할 줄을 몰라서 아쉬움이 크다. 전 종목을 ..
2020.06.10