[파워쿼리] Tip. 개발일정 점검 자동화

이번 포스팅은 파워쿼리를 이용해서 개발일정계획을 쉽게 점검할 수 있도록 진행된 작업 사례입니다. 

Task/작업자 단위로 일정계획을 엑셀로 작성하는 경우가 많죠. 엑셀이 입력은 쉽지만 작성내역을 검토하는 것은 쉽지 않습니다. 물론 단순하고 비교적 짧은 기간을 계획하는 경우라면 한눈에 일정을 파악하기 쉽기 때문에 검토와 수정이 쉽습니다. 하지만 계획 대상기간이 길고 복잡한 경우라면 엑셀로 작성된 계획은 검토와 수정 작업이 만만치 않습니다. 

오늘은 작업자와 Task 단위로 시작일과 종료일이 기록된 일정계획표를 점검 할 수 있는 작업을 해보려 합니다. 

 

1. 데이터  

작업자 / 작업명 / 시작일 / 종료일 로 구성된 계획 입니다. 

담당자 A와 B가 Task 1~11을 약 3개월 간 수행하는 일정계획입니다. 

만들고 싶은 모양은 담당자/월별 작업일수를 월별로 구성하는 피벗테이블입니다.  

2. 쿼리 구성 

파워쿼리로 데이터를 생성하기 이전에 데이터를 엑셀 수식으로 아래와 같이 늘려줍니다. 

1~10 열은 아래 수식으로 추가 한 열입니다. 

=IF(G$3>=[@작업일수],"0",WORKDAY([@시작일],G$3)) 

시작일 부터 주말을 제외한 일자를 하루씩 늘려 놓은 겁니다. 다만 IF 를 사용하여 작업일수를 넘기는 경우 '0'이 되도록 했습니다. 

이 상태로 파워쿼리를 생성하여 피벗팅 해제를 통해 데이터 행을 일자별로 늘려줍니다. 

파워쿼리로 생성하고 

담당자/작업명/작업일수 를 선택하고 "다른 열 피벗 해제"를 클릭합니다. 

피벗 해제 후에 필터링으로 "0" 을 제외한 후 데이터 값을 일자로 변경하여 정렬해 놓은 결과입니다. 

이제 달력 테이블을 만들어 줍니다. 작업기간은 8월부터 12월까지 3개월이므로 8월 1일부터 12월 31일까지 달력테이블을 만들어 줍니다. 

엑셀에 WORKDAY 함수로 달력Table을 만들어주고 파워쿼리로 생성합니다. 

그리고 달력(fDate)쿼리 에서 "쿼리병합"을 실행합니다. 

'쿼리를 새 항목으로 병합' 하게 되면 새로운 쿼리에 병합(Join)된 결과를 만들어줍니다. 

fDate(달력) 과 f일정계획을 각각의 일자 열로 조인을 해줍니다. 

조인 종류는 "왼쪽 외부" Left Outer 조인입니다. 

이렇게 하면 달력의 데이터는 다 나오면서 일정계획이 있는 일자가 추가되는 형태가 됩니다. 

병합된 쿼리에 '확장' 기능으로 담당자와 작업명을 추가합니다.

아래 그림이 쿼리의 최종결과입니다. 

보시면 8월 1일 부터 17일 까지는 담당자와 작업명이 null (빈값) 입니다. 즉 작업이 없는날은 빈값으로 데이터가 구성된 것 인데요. 이것을 피벗테이블로 구성하면서 Date를 열항목으로 넣어주면 

이런 형태가 됩니다. 

조건부 서식을 추가 해보면 아래와 같이 간트 차트가 구성됩니다.  

위 차트로 작업자 A는 Task1과 Task2 사이에 8월 26일 일정이 빠져 있는 것이 바로 확인이 됩니다. 

그러면 ... 원본 일정계획 표에 Taske2의 시작일을 8월 26일로 하루 당길 수 있겠네요. 수정 후에는 새로 고침만 하면 됩니다. 

MS-Project 수준은 아니지만 간단하게 계획을 검토하는 피벗테이블을 구성 해 보았습니다. 피벗 테이블로 구성했기 대문에 행합계는 각 담당별 로드가 되고 열합계는 일자별 작업공수가 되겠네요. 

예산이나 개발 공수 체크 할 때 여러모로 유용하게 쓰일것 같습니다. 

예제 파일 첨부합니다. 

직접 보시는것이 이해가 편할 것 같네요. 

파워쿼리_일정계획 점검.xlsx

0.05MB