[소소한컴퓨터공부] 엑셀 메모

엑셀 메모 하기

 

 

정렬, 부분합, 통합

부분합_ 자동으로 특정 필드를 그룹화하여 분류하고, 그룹별로 합계 평균, 개수 등을 자동 계산하는 기능

부분합을 지정하면 그룹별 소개가 구해지고 윤곽 기호가 나타난다.

윤곽 기호를 클릭해 요약된 결과만 표시된 상태에서 데이터를 복사하고 붙여 넣으면 숨겨진 하위 수준까지 모두 붙여넣기 되므로 화면에 보이는 셀만 복사한 후 붙여 넣는다

 

 

피벗테이블보고서, 표서식

피벗테이블을 사용하면 함수 없이도 원하는 형식의 집계표를 만들 수 있으며

복잡한 데이터를 요약하고 흐름이나 추이를 비교하는데 편리하다

 

삽입 탭 – 표 그룹 – 피벗테이블

 

피벗테이블로 요약한 보고서는 다른 시트로 복사해서 양식에 맞게 수정하여 업무용 보고서로 사용할 수 있다

 

GETPIVOTDATA(데이터 필드, 피벗 테이블 필드, [필드1], [항목 값1]………….)

피벗 테이블의 값 영역 필드의 값을 참조하는 함수

다른 표에서 피벗 테이블 보고서 값 영역 내 집계 값을 참조

데이터 필드 : 피벗 테이블 보고서의 값 영역에서 값을 참조해올 필드 이름

피벗 테이블 필드 : 피벗 테이블 보고서 내 임의의 셀 주소, 피벗 테이블 영역을 확인하는 용도로 사용되며 보통 피벗 테이블 위치의 첫 번째 셀 주소 사용

필드 : 피벗 테이블 보고서에서 참조해올 행/열 영역 내 필드명

항목 값 : 피벗 테이블 보고서에서 행/열 영역 내 필드명의 항목 값

 

피벗 테이블 필드 항목을 사용자 지정 목록으로 정렬하기

1 사용자 지정 목록 추가하기

파일 탭 – 옵션 – 고급 – 일반 – 사용자 지정 목록 편집 – 목록항목 (지정하고자 하는 목록 입력) – 추가 – 확인 – 옵션대화상자 확인

 

2 사용자 지정 순서로 정렬하기

 

 

피벗 테이블 보고서의 통합 기능을 사용하면 여러 시트의 데이터를 합쳐 피벗 테이블 보고서로 만들 수 있다

 

빠른 실행 도구 모음 사용자 지정 – 기타 명령 – 명령선택에서 리본 메뉴에 없는 명령 – 피벗 테이블/피벗 차트 마법사 – 확인

생성된 피벗 테이블 / 피벗 차트 마법사 를 클릭하여 다중 통합 범위 선택하여 통합 시트를 형성한다

 

 

VLOOKUP, INDEX, QUOTIEN, MOD, ROW, CHOOSE _ 데이터베이스 가공

원본 데이터베이스가 크로스탭 형태이면서 레이블이 2열 이상인 때는 피벗 테이블 보고서의 레이아웃을 사용자가 원하는 형태로 만들기 어렵다. 데이터베이스 구성이 적합하게 이루어져야 엑셀에서 제공하는 다양한 데이터베이스 기능을 사용할 수 있다.

 

ROW

A4에 1을 넣고 싶다면 =Row ( ) – 3

QUOTINENT

행 번호를 나눠 몫을 표시하는 함수

= QUOTINENT (ROW(A3), 3)은 행번호 ROW(A3)를 3으로 나눠 몫을 표시

 

=INDEX(전체범위, 행번호, 열번호)

전체범위에서 행번호와 열번호가 교차하는 위치의 값을 가져온다

 

피벗 차트는 일반 차트와 만드는 방법이 동일하다

다만, 피벗 차트는 피벗 테이블과 연동되어 차트가 표시되며,

일부 차트(분산형, 거품형, 주식형)를 만들 수 없다

엑셀 2016버전에 제공하는 트리맵, 선버스트,, 히스토그램, 파레토, 상자 수염 그림, 폭포, 깔대기형 차트도 피벗 차트로 만들 수 없다

피벗 테이블에서 행 영역의 필드는 레이블(항목)로, 열 영역의 필드는 데이터 계열로 표시된다

 

슬라이서

피벗 테이블 데이터 중에서 사용자가 원하는 자료를 필드 목록으로 세분화하고 필터링하여 필요한 내용만 표시할 수 있다

피벗테이블도구 – 분석 탭 – 필터 그룹 – 슬라이서 삽입

엑셀 2007 : 슬라이서 삽입 기능을 사용할 수 없다

엑셀 2010 : 피벗테이블도구 – 옵션 탭 – 정렬 및 필터 그룹 – 슬라이서

 

SUMPRODUCT

배열 대 배열을 곱하고 더하는 함수

 

빈 열 삽입 : 열을 삽입하고자 하는 머리글 선택, ctrl + Shift + +

빈 행 삽입 : Ctrl + +