[한입엑셀_집계편]데이터베이스를 이용하여 보고용 집계표 만들기

• 파워쿼리를 활용하여 데이터베이스 목록 만들기
• 데이터베이스 목록을 집계표로 만들기
• SUMIFS 함수의 활용

지난번엔 파워쿼리를 활용하여 데이터들을 가공할 수 있도록 전처리해 보았습니다. 

지난 결과물

이렇게 가공 준비가 된 데이터들은 우리가 보기에 익숙한 형식은 아닙니다. 

보고하거나 발표하기에 적합한 모양은 더더욱 아닙니다.

때문에 우리는 이 데이터들을 보기 좋게 가공하여 남들에게 보여줘야 합니다. 

다음의 집계표를 만들어 내용물을 채워보도록 하겠습니다.

또한 데이터베이스 목록의 구분에서는 보건&위생과 연구&개발이 번갈아가며 나오는데 이를 SUMIFS 함수를 이용하여 집계해 보도록 하겠습니다. 

파워쿼리를 활용하여 데이터베이스 목록 만들기

<지난번 피드 참고>

2023.08.03 - [엑셀] - [합입엑셀_수식편]데이터 가공을 위한 전처리하기(Feat. 파워쿼리)

[합입엑셀_수식편]데이터 가공을 위한 전처리하기(Feat. 파워쿼리)

데이터베이스 목록을 집계표로 만들기

먼저 A열의 단어 사이 빈칸을 없애겠습니다.

A2를 선택 후, [Ctrl]+[Shift]+[↓]를 눌러 전체 열 지정해 줍니다.

[홈]-[편집] 그룹-[찾기 및 선택]-[바꾸기]-찾을 내용: (한 칸 띄기) & 바꿀 내용: (입력 X) - [모두 바꾸기]

그럼 단어 사이의 공간이 사라집니다.

우선 쿼리를 이용해 만든 표를 전체지정(Ctrl+A)하고

[수식]-[정의된 이름] 그룹-[선택 영역에서 만들기] 선택 후, [첫 행]만 지정합니다. 

그 집계표로 가서 SUMIFS함수를 입력해 줍니다. 

SUMIFS함수는 두 개 이상의 조건에 맞는 합계를 구할 때 사용하는데

SUMIFS(합을 구할 범위, 조건 범위 1, 조건 1, 조건 범위 2, 조건 2,....)로 입력하면 됩니다. 

• 우리가 합을 구할 범위: 횟수
• 조건 범위 1 & 조건 1: 장소(호실)
• 조건 범위 2 & 조건 2: 구분(보건&급식, 연구&개발)
• 조건 범위 3 & 조건 3: 월별(1~6월)

따라서 함수식: =SUMIFS(횟수,장소,$A4,분야,$B4&$C4,월별,D$3) 입니다.

Tip. '$A4'와 같이 중간에 $는 앞의 값을 고정한다는 뜻으로, [F4]를 여러 번 눌러 조정할 수 있습니다.

D4부터 I4까지 드래그해 줍니다. 

그럼 한 행의 값이 모두 입력됩니다. 

D4~I4가 지정된 상태에서 [수식]-[자동합계]를 선택하면 J4셀에 자동으로 합계가 입력됩니다. 

이후 오른쪽 아래 꼭짓점을 더블클릭하면 빈셀이 자동으로 채워서 집계표 완성입니다.

여기서 끝일까요?

우리는 "보고"를 위해 이 집계표를 작성한다는 것을 생각해야 합니다. 

자세히 보면 표의 스타일도 통일적이지 않고 같은 단어가 불필요하게 반복되고 있습니다.

B5 ~ C24를 지정하고 [홈]-[스타일] 그룹-[조건부서식]-[새 서식 규칙]을 선택하고,

'▶수식을 사용하여 서식을 지정할 셀 결정' 선택, 아래 입력 칸엔 '=B4=B5'입력합니다. 

[서식]-[테두리]-스타일: 없음, 위테두리 클릭하여 없애기

[표시형식]-[사용자 지정]-형식: ";;;"입력-[확인]

Tip. [표시형식]-[사용자 지정]-[형식]에 ";;;"를 입력하면 셀에 데이터가 표시되지 않고, [테두리에서 위쪽 테두리를 없애면 구분항목이 바뀔 때만 테두리가 표시됩니다. 

그리고 셀 스타일을 통해 좀 더 통일감 있고 깔끔하게 만져줍니다. 

집계표가 완성되었습니다. 

위의 방법을 활용하여 여러 집계표를 만들고 보고에 이용할 수 있습니다. 

감사합니다.