이거면 고급 필터랑 피벗 테이블 기깔나게 풀기 가능 : ITQ 엑셀썰 #3

 

 

 

| 엑셀 2, 3번 문풀 과정

 

  이전 포스팅과 연결됩니다.

ITQ 엑셀썰 #2

이러나저러나 노베이스 그림러 엑셀 독학 방법 : ITQ 엑셀썰 #2

 

 

그래서 itq 엑셀 부분합 어케 푸냐?

 

  후기썰 2화에서 대략 엑셀 시험 보기 최적화 환경 설정하는 방법과 1번 문제 풀이를 간단하게

이야기해 드렸었죠?

 이번 후기썰 3화에선 2번과 3번에 대한 문제 풀이 팁입니다.

 

  개인적으로 엑셀 시험 준비하면서 머가리가 많이 아팠던 부분이 바로

고급필터 조건범위 수식화하는 것과 3번 피벗테이블 값 구하는 거였는데 말이죠..

 

  푸는 과정을 글로 한번 정리해 봤으니, 아래에서 함께 살펴볼까요??

 

 

2 번 풀이
  기본 적으로 모든 문제는 제 1 작업을 복붙하여 풀거나, 지정하여 만들 수 있습니다.

 이에 제 2 작업은, 즉 2번 문제지에서 제시한 영역을!!

제 1 작업물에서 ctrl+C/ctrl+V 로 복붙해서 2 번 시트에 옮깁니다.

*각 문제 별로 첫 번째, 두 번째 등 순차적 풀이 과정 순서가 있으니 유의해 주세요!

첫 번째 과정 : 목표값 구하기
> 1. 복붙한 표에제시된 조건을 적용하고, 항목 가장 하단에 함수를 기입

전체 합계는 =DSUM(영역 선택)

평균은 =AVERAGE(영역 선택)

> 2. 목표값은 엑셀 상단 메뉴 중,

"데이터" 탭 -> "가상분석" 클릭 -> "목표값 찾기" 클릭(이미지 참고)
*평균 혹은 합계 구하는 과정은 문제에서 안나오는 경우도 있음.

주인장의 경우, mac os에서 엑셀을 연습했습니다만... 윈도우에서 연습하시길 추천드립니다!

 

두 번째 과정 : 고급 필터 적용
> 1. 제 1 작업의 표 일부분(함수 제외)을 복사하여, 제 2 작업 시트의 B2 셀에 붙여넣기하세요. 

이때! 문제지에 보면, 제시하고 있는 조건범위가 2 가지임을 먼저 찾아두시는게 좋습니다.

TIP! 문장을 수식화 하는 법 ("조건 범위" 작성법)
~이면서 : (and)조건으로, 바로 오른쪽 셀에 입력
~이거나 : (or)조건으로, 바로 오른쪽 아래 셀에 입력
~아니(면서/거나) : <>(단어)
~으로 시작 : (단어)*
이상/이하 표시 : >=/<=
초과/미만 표시 : >/<
*예시(아래 표 참고)
문장조건① : 도서코드가 ‘A’로 시작하거나, 판매량(단위:권)이 ‘100’ 이상인 자료의~
문장조건②  : 주요 취미가 ‘스포츠’가 아니면서, 활동비(단위:만)이 ‘70’ 미만인 자료의~

**작성 예시 : 

도서코드	판매량 (단위:권)		주요 취미	판매량 (단위:권)
A*			<>스포츠
	>=1000

> 2. 조건 범위를 적용한 데이터 추출

: 특정 항목에 대한 데이터만 추출해야 할 경우, 제 1 작업 표에서 해당 항목명만을 ctrl+드래그하여 다중 선택 후 복사해두고, 셀(B18~E14)에 붙여넣기 합니다.

> 3. 이후 붙여넣은 표 전체 드래그 및 선택하고

> 4. 상단 메뉴 중, "데이터" 탭의->"정렬 및 필터"의 '고급' 클릭

> 5. "고급 창"에서
->"다른 장소에 복사" 선택

->"목록 범위"는 자동 입력됨

->"조건 범위"는 B14 부터 C15 혹은 16 까지 직접 드래그

->"복사할 영역"은 꼭 B18 에서 E18 까지 선택 드래그

->"확인" 버튼 클릭
*여기까지가 고급 필터 적용 과정입니다.

 

> 7. 그리고 나서 표 스타일을 적용

: 이때 간혹, 고급필터 결과셀 영역을 "채우기 없음"으로 설정하고 '표 스타일 보통~'이라는 서식 적용 조건이 나오면!
-> 결과셀 드래그 후 상단 메뉴 중,"홈 탭"

->(글씨체 선택하는 칸에서)"페인트통" 아이콘 클릭

->"채우기 없음" 클릭

->"홈 탭"

->"서식" 클릭

-> 제시된 스타일 명 찾아서 클릭
*머릿글 행, 줄무니 행 변경은 아래 이미지 참고.
**표 스타일 적용하는 것은 문제에서 안나오는 경우도 있음.

---

3 번 풀이
  3 번 문제의 경우, 피벗 테이블 혹은 부분합 중 하나가 나옵니다.

첫 번째 과정 : 피벗 테이블
> 1. 피벗 테이블 생성:
-> 제 1 작업에서 B4~H12 까지의 셀을 영역 지정(드래그) 해둔 뒤

-> 상단 메뉴 중 "삽입 탭"에서

-> 가장 왼쪽에 있는 "피벗 테이블(이하 "표 또는 범위의 피벗 테이블")" 클릭

->"기존 워크시트" 선택 후

->"표/범위" 칸에 마우스 커서 올려두고

-> 제 3 작업 시트 클릭 및 해당 시트의 B2 셀 클릭

-> 다시 피벗 테이블 창으로 돌아와 "확인" 버튼 클릭

 

  그럼 제 3 작업 시트의 오른편에 "피벗 테이블 필드" 창이 뜰겁니다.

> 2. 피벗 테이블 필드 설정

: 문제의 조건(1)에서 행과 열에 들어갈 항목을 알려줍니다.
 따라서 "피벗 테이블 필드" 창의 목록들 중, 조건(1)에서 언급된 항목을

각각 행과 열 필드(이하 칸 부분)에 드래그해서 넣으시면 됩니다.

  또한, 조건(1)의 마지막에 보면- 구해야하는 항목(값)에 대한 언급이 있을 텐데,

해당 항목을 필드에서는 "값" 부분에 드래그/드롭 하세요.

> 3. 피벗 테이블 필드에서 특정 값 구하기-조건(1)

: 여기서 "값" 필드에서 합계/평균 등을 변경해야 하는 경우엔 다음과 같이 진행합니다.
-> 필드에 넣은 항목에 마우스 오른쪽 클릭

->"필드 설정(값 필드 설정)"에 들어가 사용할 함수(평균/합계/개수 등) 변경
-> 동시에 "사용자 지정 이름" 부분에서 수동으로 (문제에 제시된) 명칭으로 수정 진행

> 4. 피벗 테이블 부분 그룹화-조건(2) :
->조건(2)에서 제시된 "그룹화"는 B5 셀을 선택 후 마우스 오른쪽 클릭
->"그룹" 항목 선택
-> 창이 뜨면, 해당 셀에 들어가 있는 단위가 자동으로 보일텐데 여기서 문제에 주어진
"단위만 빼고 모두 선택 해제"
-> 확인 클릭
-> 조건(2)에 제시된 <<출력형태>>와 같은 "정렬"은, 생성된 피벗 테이블의 가장 상단에
있는 "항목⬇" 에서 화살표 클릭
-> 정렬 방식을 "텍스트 오름차순/내림차순" 중, 문제에 맞게 택 1

> 5. 피벗 테이블 서식 변경-조건(3),(4) :
-> 피벗 테이블의 셀 아무거나 오른쪽 클릭
->"피벗 테이블 옵션" 클릭
-> 그럼 옵션 창이 뜰텐데, 여기서 "레이아웃 및 서식"의 '레이블이 있는 셀 병합/가운데 정렬'에 체크
->'빈 셀 표시' 부분에는 문제에 제시된 '***' 등을 직접 입력
->"요약 및 필터"에서는 조건(4)에 제시된 '행의 총합계' 체크 해제

> 6 .마무리

: 이제 마지막으로, <<출력형태>>와 똑같이 만들었는지 확인합니다.
혹 행이나 열 레이블의 이름이 문제와 다르면!
-> 해당 셀 더블 클릭-> 이름 변경(직접 타이핑)

  그리고 표 보면, 숫자의 경우 "가운데 및 오른쪽 정렬"일 때가 많은데

이는 C5~H8 셀을 모두 영역 지정한 뒤!
-> 상단의 "홈 탭"->"맞춤" 부분에서 "가운데 정렬" 클릭-> "표시" 형식에서 ',(쉼표)' 클릭

으로 맞춰주세요.

 

두 번째 과정 : 부분합 및 정렬
*제 1 작업에서 B4~H12 까지의 셀을 영역 지정 복사하고, 제 3 작업 시트의 B2 셀에 붙여 넣으면서 시작합니다.

 

> 1. 부분합 지정-조건(1) :
-> 복붙한 영역의 셀 아무거나 클릭
-> 상단 메뉴 중,"데이터" 탭의 오른쪽 기능에 "부분합"을 선택
*부분합 문제는 풀어야 하는 것이 2 개 정도 묶여 나옵니다.

> 2. 부분합 조건(1) 적용 :
->"부분합" 창에서 '그룹화'할 항목을 (문제에서 제시된 대로) 선택
-> 사용할 함수 역시 문제에서 제시한 '평균/합계/개수' 등 택 1
-> 부분합 항목은 문제지에 나온 조건대로 택 1
-> 나머지 항목은 "체크 해제"
->"확인" 클릭

> 3. 부분합 조건(1) 설정

: 두 번째 부분합 과정은 위와 동일하게 진행하되, 꼭 "새로운 값으로 대치" 항목에 체크 해제할 것!
*여기까지가 조건(1) 해결입니다.

> 4. 부분합 서식-조건(2) :
-> 개요(윤곽)의 경우, 표의 셀 선택
-> 상단 메뉴 중,"데이터" 탭의
->"부분합" 옆에 보면!!!! '그룹 해제' 아이콘의 ⌄(화살표) 클릭
->"개요 지우기" 클릭
*간혹, 표의 특정 열에 내용이 길면 경계선이 생략 되기도 하는데

 이건 내용이 긴 셀이 위치한 열의 간격을 늘려주면 됩니다.
**열의 가장 바깥 선에 마우스 커서 올리면 커서 모양이 양쪽 화살표모양으로 바뀌는데,

 그때 오른쪽으로 클릭 드래그하면 돼요.

 

 

  저는 피벗 테이블보다 부분합 풀이에서 좀 버벅였습니다..

 글 보다 실제 화면을 보면서 풀고 싶으신 분들은 유튭을 참고하시면 더 쉬워영!ㅎㅅㅎ

 

 

  그래서?

  주인장의 경우,

 1 번 문제에 15 분, 2 번 10~13 분, 3 번 15 분, 4 번은 15 분 정도 걸렸습니다.

 

  4 번인 그래프 생성은 단축키 및 서식, 범례 수정만 알면 금방 풀기 때문에 겁먹을 필요 없어요.

 무튼~ 4 번 풀이는 다음 포스팅에서 다뤄보겠습니다:)

 

 

 

 

 본 포스팅은 작성자의 주관적인 경험을 바탕으로 작성되었습니다.