minbe-data 님의 블로그

Power Query를 활용해 엑셀 가로 데이터를 세로 형태로 변환하는 방법을 정리했습니다. 엑셀 데이터를 정리하다 보면 시간1, 시간2, 시간3처럼 가로로 나열된 값을 하나의 컬럼으로 세로 정리해야 하는 경우가 자주 발생합니다. 이 작업을 수식으로 처리하려고 하면 구조가 복잡해지기 쉽습니다. Power Query에서는 Unpivot 기능을 활용하면 이 문제를 간단하게 해결할 수 있습니다. 먼저 주민등록번호나 이름과 같은 기준 컬럼은 그대로 유지하고, 시간1부터 시간5까지의 컬럼을 선택합니다. 이후 Unpivot을 적용하면 가로로 나열된 값들이 하나의 컬럼으로 펼쳐집니다. 이 과정에서 생성되는 Attribute와 Value 구조 중에서 실제 필요한 값만 남기고, 빈 값(null)은 제거하면 깔끔한 데이터..

Power Query를 활용해 같은 값 기준으로 순번을 자동 생성하는 방법을 정리했습니다. 데이터를 정리하다 보면 같은 값이 반복되는 경우가 많습니다. 이때 각 값별로 1차, 2차, 3차와 같은 순번을 만들어야 하는 상황이 자주 발생합니다. 이 작업은 단순 계산으로는 처리하기 어렵지만 Power Query에서는 그룹 기능을 활용하면 쉽게 해결할 수 있습니다. 먼저 전체 데이터의 순서를 유지하기 위해 Index를 추가한 뒤, 특정 컬럼 기준으로 그룹화를 진행합니다. 이후 각 그룹 내부에서 다시 Index를 생성하면 그룹별 순번이 만들어집니다. 그 다음 데이터를 다시 펼치고 원래 순서대로 정렬하면 원하는 형태의 결과를 얻을 수 있습니다. 이 구조를 만들어두면 데이터가 추가되더라도 새로고침만으로 순번이 자동으..

Power Query를 활용해 엑셀 카드 사용 데이터를 월별로 자동 집계하고, 없는 월까지 표시하며 총계 오류를 해결하는 방법을 정리했습니다. 엑셀에서 월별 집계를 만들다 보면 데이터가 없는 월이 아예 표시되지 않거나, 빈 값 또는 특수 문자(-)로 인해 총계 계산 시 오류가 발생하는 문제가 자주 발생합니다. 또한 계정이나 계좌 기준으로 데이터를 정리하는 과정에서 구조가 깨지는 경우도 많습니다. 이러한 문제는 Power Query를 활용하면 한 번에 해결할 수 있습니다. 핵심은 단순 집계가 아니라 데이터가 없어도 월을 유지하고, 오류 없이 총계를 계산할 수 있는 구조를 만드는 것입니다. 먼저 날짜 데이터를 기준으로 월 정보를 생성한 뒤, 계정과 계좌 기준으로 데이터를 그룹화하여 월별 합계를 계산합니다. ..

Power Query를 활용해 엑셀 출퇴근 데이터를 자동으로 정리하는 방법을 정리했습니다. 출퇴근 시스템 데이터를 보면 같은 사람의 출근 기록이 여러 번 존재하거나, 퇴근 기록도 여러 번 찍히는 경우가 많습니다. 또한 지문, 키패드 등 다양한 장치에서 기록된 데이터가 섞여 있어 그대로는 근태표로 사용하기 어려운 경우가 많습니다. 이럴 때 Power Query를 활용하면 데이터를 간단하게 정리할 수 있습니다. 기본 원리는 같은 사람과 같은 날짜를 기준으로 데이터를 그룹화한 뒤, 출근 시간은 가장 빠른 시간, 퇴근 시간은 가장 늦은 시간을 자동으로 계산하는 것입니다. 이 과정을 통해 여러 번 찍힌 기록 중에서 실제 근태에 필요한 값만 남길 수 있습니다. 또한 Power Query로 구조를 만들어두면 이후 데..