본문 바로가기

파워쿼리

[일머리 스킬] 엑셀 파워쿼리(8)_열 합계 구하기 오빠* 엑셀 사이트 커뮤니티에 누군가가 질문한 내용이 있어 올려본다. 파워쿼리의 결과물로 열 합계, 평균 등을 구하는 일이 많지 않아 해당 기능을 잘 사용하지 않아 사람들이 잘 모르는 기능이다. 질문은 아래와 같이 피벗을 하고 합계 열을 넣어 합계를 구하고 싶은데 어떻게 하면 되는지였다. 방법은 의의로 간단하다. 번호 열을 기준으로 피벗을 실행 → 열 추가 메뉴에서 "통계" 기능을 활용하면 된다. 1. 번호열을 기준으로 피벗 → 값은 집계 안함으로 처리 2. 합계를 구할 1,2,3,4번 열을 선택하고 통계 클릭하고 합계 값을 구하면 된다. (추가된 열은 "덧셈'으로 표기된 것을 '합계 변경하면 된다) 더보기
[일머리 스킬] 엑셀 파워쿼리(6)_구간 데이터를 연속형 데이터로 변환 파워쿼리의 유용한 기능 중 하나가 구간 형식으로 만들어진 데이터를 연속형 데이터로 쉽게 만들 수 있다는 것이다. 구간 형식의 데이터는 아래 형식의 데이터다. 위의 데이터로는 해당 구간 안에 존재하는 값을 엑셀 Vookup이나 파워쿼리 조인 기능을 활용하기 어렵다. 그래서 파워쿼리에서는 구간 데이터를 연속형 데이터로 변환해 줘야 한다. 1. 기본 방법 : 새로운 열을 추가하여 {시작..종료}로 열 수식 입력 → List형 열 생성 → 새행으로 확장 후 열 정리 { } : 리스트형 의미, ".."로 연속 데이터(숫자(정수), 문자(파워쿼리 내장 된 문자 순서)만 가능) 표시 2. 예시 (빈 쿼리 삽입 후) 3. 실전 예제(엑셀 파일 참고) 1) 사용자 지정 열 추가 2) 사용자 지정열 새행으로 확장 3) 필.. 더보기
[일머리 스킬] 엑셀 파워쿼리(1)_그룹화와 결합(추가/병합) 엑셀 파워쿼리에서 핵심 기능을 말하라고 한다면 나는 2가지 기능이라고 생각한다. 첫 번째는 그룹화 기능이고 두 번째는 병합 기능이다. 왜냐하면 다른 기능들은 엑셀의 기본 기능들로 커버가 가능하지만 위의 2가지 기능은 엑셀 기본기능으로 쉽게 구현하기 힘들지만 파워쿼리는 너무나 쉽게 구현할 수 있기 때문이다. 그래서 파워쿼리를 공부하는 사람은 2가지 기능을 익혀 자주 사용하면 업무에 많은 도움이 얻을 수 있다. 그룹화 : 하나 이상의 열에서 동일한 값을 그룹화된 단일 행으로 묶는 기능 ※ 엑셀의 피벗 테이블 기능과 유사하나 더 쉽고 더 많은 기능을 활용할 수 있음 [방법] 그룹화 선택 → 그룹해야 할 열 선택 → 그룹화된 열을 기준으로 새로운 열 추가(다양한 연산 기능이 있음) [연산] 합계, 평균, 중앙값.. 더보기
[일머리 스킬] 엑셀 파워쿼리(2)_최대 생산 시작 년월 구하기 파워쿼리로 최대 생산량과 최대 생산량의 시작되는 년월을 구할 수 없을까? 어제/오늘 위의 문제로 2시간가량 헤매다가 겨우 방법을 찾았다. 처음에는 쉽게 될 줄 알고 시작했는데 하나의 문제에 막혀 헤매었는데 구하고 나니 허무하기도 하다. ^^ 문제. 기종별 최대 생산 대수와 최대 생산대수가 시작되는 년월을 구할 수 없을까? (데이터는 계속 변동됨) 1차 시도: 기종 그룹화 → 최대생산량 구하고, Table.Max M함수로 최대 생산량의 생산년월을 가져오자 결과: 실패( ※ 문제점: 최대년월의 시작이 아니라 마지막을 참조하게 됨 → 여러 방법을 찾았지만 실패!!ㅜㅜ) 2차 시도: GPT에게 질문(3번 수정) → 그룹화를 위와 같이 실행하고 편집기에서 List.PositionOf M함수로 새로운 필드를 추가하.. 더보기
[일머리 스킬] 엑셀 기본(1)_양식과 데이터를 분리 관리해라 사내 직원들의 데이터 정리. 분석 역량 향상을 위해 연초에 3가지 과정을 구성하여 진행하고 있다. 구분 기초 중급 심화 교육 과정 엑셀 함수/피벗/챠트 데이터 정규화 엑셀 파워쿼리 활용 파이썬 기초 엑셀 VBA(Chat GPT 활용) 파이썬 활용 시간 2H 4H 4H 수강자가 신입사원들이 아니라 엑셀에 대해 어느 정도의 지식과 스킬은 갖추고 있어 실무에 도움이 되는 부분들만 골라서 교재를 만들고 강의를 하고 있다. 실무 예제를 중심으로 진행하다 보니 호응도 괜찮은 편이다. 강의 준비가 힘이 들지만 나의 역량도 올리고 다른 직원들에게 도움을 줄 수 있다는 생각에 애정(?)을 가지고 진행하고 있다. 오늘은 중급 과정 中 파워쿼리 교육을 진행하였는데 진행 간에 강조한 사항을 정리하여 올려본다. 나는 아래 사항.. 더보기
[일머리 스킬] 엑셀 파워쿼리(3)_3개월 평균 생산 수량 산출 우리는 과거와 현재의 실적을 기반으로 미래를 예측한다. 왜냐하면 해당 방법이 가장 손쉽게 구하기 쉽고 근거 자료로 완벽하지는 않지만 비난은 받지 않기 때문이다. 즉 가성비가 괜찮다는 것이다. 오늘은 실무에서 자주 활용되는 직전 3개월 평균 수량을 엑셀 파워쿼리로 손쉽게 구하는 방법을 공유하고자 한다. (유튜브 채널 유앤더스 아카데미 콘텐츠를 기반으로 학습한 내용임. 파워쿼리 공부시 활용 바람) 파워쿼리는 별도의 코딩 없이 데이터 산출을 자동화할 수 있는 것이 장점이고, VBA 코딩보다 초중급자가 배우기가 훨씬 더 용이하다. 그래서 일잘러의 업무 시간 단축에 꼭 필요한 Tool임으로 필히 익혀 두길 바란다. 파워쿼리로 직전 3개월 평균 생산 수량 자동화 산출하는 방법 1. 원본 Data를 테이블로 변환 →.. 더보기
[일머리 스킬] 엑셀 파워쿼리(4)_인덱스열/나누기/모듈로 활용 엑셀 파워쿼리는 데이터 정리/분석을 위한 전처리 작업을 할 때 가장 유용하게 사용할 수 있다. 그중에서 가장 흔하게 사용하는 것이 인덱스 열과 표준 서식인 나누기와 모듈로를 활용하여 반복되는 패턴의 데이터를 처리하는 것이다. 예시 1. 세로형 Data를 가로형 Data로 변환 → 3칸씩 반복되는 패턴 방법 1. 인덱스열 추가 → 인덱스열 선택 후 "표준"에서 모듈(나머지)로 선택 → "3"입력 2. "모둘로"열을 선택 후 피벗열 선택 → 값 열 "Data", 고급옵션에서 데이터 집계 안 함 선택 3. 변환에서 채우기(위로) 선택 후 o번 열에서 null 해 4. 인덱스열 삭제, 열이름 변경(완료) 예시 2. 세로형 Data를 가로형 Data로 변환 → 7칸씩 반복되는 패턴 방법 1. 열분할 → 인덱스추가.. 더보기
[일머리 스킬] 엑셀 파워쿼리(5)_특정 단어 추출 오늘 타 부서의 팀장님이 찾아와서 엑셀에 있는 특정 단어를 추출하려고 하니까 너무 시간이 많이 걸린다고 혹시 도와줄 수 있는지 물었다. 함수를 사용해서 해보려고 했는데 셀 안에 있는 문자들이 특정 패턴을 찾기가 어렵고 한 셀에 여러 개 데이터가 있어 함수로 처리하기 어려워서 하다가 포기를 했다고 했다고 한다.그래서 먼저 데이터를 보여달라고 했다. 데이터 구조는 열에 많은 텍스트가 있고 그중에서 DI로 시작되는 특정 문자열을 추출하고 싶어 했다. 정말 간단한 작업으로 원하는 데이터를 구할 수 있었다. 소요시간이 20분 정도 걸려서 데이터를 넘겨주었는데 팀장님이 연신 고맙다고 표현을 했다. 알면 간단히 결과물을 만들 수 있지만 모르면 많은 시간과 노력이 필요하다. 그래서 직장인들은 필히 엑셀 파워쿼리와 VB.. 더보기

티스토리툴바