[엑셀 꿀팁] 피벗 테이블, 피벗 테이블 데이터 그룹화, SUMIF함수

연구개발비 산정보고서란 

제품 개발 또는 학문을 위한 연구를 위해 필요한 장비, 원자재, 인건비, 실험 등의 비용을 개발 전에 미리 예측 산정하여 정리한 문서입니다.

연구개발비 산정보고서를 작성하는데 필요한 Skill

 

 

 

 

---

4.2.1 피벗 테이블 만들기

 

피벗 테이블은 대량의 데이터를 다양하게 요약해서 보여주는 대화형 테이블입니다. 피벗 테이블을 이용하면 데이터를 원하는 대로 배치하고 요약할 수 있습니다.

지출 코드, 일자별로 지출비용의 합계를 표시하는 피벗 테이블을 만들려면

1. [부서별 개발비 산정 비용] 표의 아무 셀에 마우스를 클릭합니다.
2. [삽입] > [표] > [피벗 테이블]을 클릭합니다.
3. [피벗 테이블 만들기] 대화 상자에서 ‘표 또는 범위 선택’ 항목이 체크되어있는지 확인합니다.
4. ‘기존 워크시트’ 항목을 체크하여 피벗 테이블 보고서 넣을 위치를 정합니다. (위치는 ‘I10’ 셀을 마우스로 선택합니다.) 
5. [확인]을 클릭합니다.

 

6. 워크시트 오른쪽에 표시된 ‘피벗 테이블 필드’ 창에서 [지출 코드] 필드를 열 영역으로, [일자] 필드를 행 영역으로, [지출비용] 필드를 값 영역으로 드래그하여 배치합니다. 

 

 

---

4.2.2 SUMIF 함수로 논리식 계산하기

부서별 개발비 산정 비용 표에서 산정 항목별로 합계를 알아 보고자 합니다. 산정 항목별로 합계를 구하기 위해서는 SUMIF 함수가 필요합니다.

 

 

=SUMIF(range, criteria, [sum_range])

 

Range: 조건이 포함된 범위

Criteria: 조건

[sum_range] : 합계를 구할 범위]

 

부서별 개발비 산정비용 표에서 각 산정 항목별로 합계를 구하려면

1. [산정 비용] 워크시트의 교육훈련비 항목 오른쪽의 J3셀을 클릭합니다. “=SUMIF( “를 입력합니다.
2. 부서별 개발비 산정비용 표에서 F3셀에 마우스를 클릭한 뒤, CTRL + SHIFT + ↓키를 눌러서 마지막 데이터가 입력된 F447셀까지 선택합니다.
3. 선택한 F열을 절대 참조로 변환하기 위해서 F4키를 누릅니다.
4. 오른쪽 산정 항목 표의 I3셀을 마우스로 클릭합니다.
5. 합계 값을 구할 범위인 G3셀부터 G447셀까지 CTRL+SHIFT+↓키를 누릅니다.
6. 선택한 G열을 절대 참조로 변환하기 위해 F4키를 누릅니다.
7. 전체 함수 식 =SUMIF($F$3:$F$447, I3,$G$3:$G$447) 이 완성됩니다.

 

8. 교육훈련비의 합계는 2,874,900원입니다.
9. J3셀을 선택한 상태에서 셀 오른쪽 하단의 채우기 항목 핸들에 마우스를 가져가고 십자가 모양으로 변하면 J7셀까지 드래그하여 수식을 복사합니다. 

 

 

---

4.2.4 피벗 테이블 데이터 그룹화하기 

피벗 테이블의 데이터를 다양한 기준으로 그룹화하여 데이터를 한눈에 알아보기 쉽게 표시할 수 있습니다.  이전에 만든 일자별 지출 코드에 따른 지출비용의 합계를 표시한 피벗 테이블을 일자별이 아닌 분기별로 행 레이블을 다시 그룹화하려면

1. [일자] 필드를 기준으로 데이터를 분기별로 요약해서 표시하려면 행 레이블 중 아무 곳에 마우스를 클릭한 상태에서 [피벗 테이블] 탭의 [그룹]에서 [필드 그룹화]를 클릭합니다.

 

 

2. [분기] 항목을 선택하고 [확인] 클릭합니다.

 

3. 행 레이블이 분기별로 표시됩니다.

 

SUMIF 함수와 피벗테이블은 엑셀 작업 시 가장 자주 쓰이는 기능이라고 해도 과언이 아닐 텐데요. 특히 피벗테이블은 모든 직장인 또는 대학생들의 작업 효율을 높여주는 획기적인 기능이라는 사실! 다들 잘 알고 계시죠? 이번 기회에 꼭 익히셔서 앞으로도 유용하고 편리하게 엑셀 작업을 하시길 바랍니다. :D