예산이 다른 여러 프로젝트를 실행 중이고 각 프로젝트에 대한 비용을 시각화하려고 한다고 가정해 보겠습니다. 즉, 이 소스 테이블에서:
.. 다음과 같이 얻으십시오.
즉, 각 프로젝트의 날짜에 걸쳐 예산을 분산하고 프로젝트 Gantt 차트의 단순화된 버전을 가져와야 합니다. 손으로 하는 것은 길고 지루하고 매크로는 어렵지만 이러한 상황에서 Excel용 Power Query는 그 위력을 그 영광으로 보여줍니다.
파워 쿼리 거의 모든 소스에서 Excel로 데이터를 가져온 다음 다양한 방식으로 변환할 수 있는 Microsoft의 추가 기능입니다. Excel 2016에서 이 추가 기능은 기본적으로 이미 기본 제공되며 Excel 2010-2013의 경우 Microsoft 웹 사이트에서 다운로드한 다음 PC에 설치할 수 있습니다.
먼저 다음 명령을 선택하여 원래 테이블을 "스마트" 테이블로 바꾸어 보겠습니다. 표로 형식 지정 탭 홈 (홈 — 표 형식) 또는 키보드 단축키를 눌러 Ctrl 키+T :
그런 다음 탭으로 이동 Data (Excel 2016이 있는 경우) 또는 탭에서 파워 쿼리 (Excel 2010-2013이 있고 Power Query를 별도의 추가 기능으로 설치한 경우) From Table / Range 버튼을 클릭합니다. :
스마트 테이블은 파워 쿼리 쿼리 편집기에 로드되며, 여기서 첫 번째 단계는 테이블 헤더의 드롭다운을 사용하여 각 열의 숫자 형식을 설정하는 것입니다.
일일 예산을 계산하려면 각 프로젝트의 기간을 계산해야 합니다. 이렇게 하려면 선택(키를 누른 상태에서 Ctrl 키) 열 먼저 마감재
하고 스타트 그리고 팀을 선택 열 추가 – 날짜 – 날짜 빼기 (열 추가 — 날짜 — 일 빼기):
결과 숫자는 필요한 것보다 1이 적습니다. 왜냐하면 각 프로젝트는 아침에 시작하여 마지막 날 저녁에 끝내야 하기 때문입니다. 따라서 결과 열을 선택하고 명령을 사용하여 단위를 추가하십시오. 변환 – 표준 – 추가 (변환 — 표준 — 추가):
이제 일일 예산을 계산하는 열을 추가해 보겠습니다. 이렇게 하려면 탭에서 열 추가 나는 놀지 않는다 맞춤 항목 (사용자 정의 열) 표시되는 창에서 목록의 열 이름을 사용하여 새 필드의 이름과 계산 공식을 입력합니다.
이제 가장 미묘한 순간 - 1일 단위로 처음부터 끝까지 날짜 목록이 있는 계산된 열을 하나 더 만듭니다. 이렇게하려면 버튼을 다시 누르십시오. 맞춤 항목 (사용자 정의 열) 라고 하는 기본 제공 파워 쿼리 언어 M을 사용합니다. 목록.날짜:
이 함수에는 세 가지 인수가 있습니다.
- 시작 날짜 – 우리의 경우 열에서 가져옵니다. 스타트
- 생성할 날짜 수 – 우리의 경우 이것은 열의 앞부분에서 계산한 각 프로젝트의 일 수입니다. 뺄셈
- 시간 단계 – 설계에 의해 설정 #지속시간(1,0,0,0), M 언어로 하루, XNUMX시, XNUMX분, XNUMX초를 의미합니다.
클릭 후 OK 테이블 헤더의 버튼을 사용하여 새 줄로 확장할 수 있는 날짜 목록(목록)을 얻습니다.
... 그리고 우리는 다음을 얻습니다:
이제 생성된 날짜를 새 열의 이름으로 사용하여 테이블을 축소하는 일만 남았습니다. 이에 대한 책임은 팀에 있습니다. 세부 정보 열 (피벗 열) 탭 개 심자 (변환):
클릭 후 OK 우리는 원하는 결과에 매우 가까운 결과를 얻습니다.
이 경우 Null은 Excel의 빈 셀과 유사합니다.
불필요한 열을 제거하고 명령을 사용하여 원본 데이터 옆에 결과 테이블을 언로드해야 합니다. 닫기 및 로드 – 닫기 및 로드… (닫기 및 로드 — 닫기 및 로드...) 탭 홈 (집):
결과적으로 다음을 얻습니다.
더 큰 아름다움을 위해 탭에서 결과 스마트 테이블의 모양을 사용자 지정할 수 있습니다. 건설자 (디자인): 단일 색상 스타일 설정, 필터 버튼 비활성화, 합계 활성화 등. 또한 날짜가 있는 테이블을 선택하고 탭의 조건부 서식을 사용하여 숫자 강조 표시를 활성화할 수 있습니다. 홈 — 조건부 서식 — 색상 스케일 (홈 — 조건부 서식 — 색상 스케일):
그리고 가장 좋은 점은 미래에 이전 항목을 안전하게 편집하거나 원래 테이블에 새 프로젝트를 추가한 다음 마우스 오른쪽 버튼으로 날짜로 올바른 테이블을 업데이트할 수 있다는 것입니다. 그러면 파워 쿼리가 자동으로 수행한 모든 작업을 반복합니다. .
자보세요!
- 조건부 서식을 사용하는 Excel의 Gantt 차트
- 프로젝트 마일스톤 캘린더
- 파워 쿼리로 중복 행 생성