[컴퓨터활용능력1급]수업 정리 4

<배열수식-array>
: 표 형태(범위)로 표현될 수 있는 값들의 집합을 수식, 
  현재 시트에 있는 자료만 수식으로 표현 = 2차원 참조 수식만 가능

- 배열 수식은 3차원 참조가 안된다 => 다른 시트나 다른 파일의 자료를 계산할 수 없다.

- ctrl+shift+enter 로 마무리 → { 중괄호 } : 입력하면 안됨.


- 배열 수식의 계산 원리
  : 논리곱 * (and), 논리합 + (or) 으로 조건 나열이나 연결
    True는 1, False는 0으로 수식에 사용가능

AND OR
T * T = T T + T = T
1 * 1 = 1 1 + 1 = 1
T * F = F T + F = T
1 * 0 = 0 1 + 0 = 1



- 시험에 출제되는 유형
  합계  : sum 조건1 : sum((조건)*값을 구할범위)
조건2 : sum((조건1)*(조건2)*값을 구할 범위))

  sum/if 조건2 : sum(if((조건1)*(조건2), 값을 구할 범위))

  개수 : sum, sum/if, count/if
  평균 : average/if
  최대값 : max, max/if
  n번째 큰 값 : large, large/if
  행에 값을 찾을 때 : index/match, index/match/max,    
index/match/max/if, 
index/match/large, index/match/large/if


- sum만 나오면 조건 때문(연산 우선순위)에 무조건 앞에 괄호 2개 열고 시작
 : Sum((조건)*~~~)


- sum(if((조건1)*(조건2), 값을 구할 범위))
  배열 수식의 if는 true일때 값만 가지고 와서 처리,
 false일때는 값이 없다.


- 양방향 복사 시에는 혼합참조(열절대참조, 행절대참조)
   =데이터의 주소의 공통부분만 고정



<고급필터>
- 조건에 만족하는 데이터만 다른(현) 장소로 추출

조건 : and/or 확인, 필드(열)명 포함 ->다른 곳에 입력
        필드명 임의로 만들기(없는 경우-함수사용시에)

      조건 작성 시 함수가 포함되지 않으면
      조건들간의 관계가 and면 같은 줄, or는 다른 줄에 입력



추출 범위(결과 표시)
       : 모든 열 다 추출은 시작위치(빈셀)만 가르쳐 줌.
특정 열의 추출시에 열(필드)명을 결과를 표시할 
위치에 먼저 입력이나 복사(필드명)한 후 추출

 

고급 필터 = 조건에 만족하는 데이터만 다른/현 장소로 추출
 -> 첫행 포함

조건부 서식 = 조건에 만족하는 데이터만 서식을 지정(시각화, 표시) 
 -> 첫행 제외



<조건부 서식 - 수식 작성 순서>

1. 서식이 적용될 데이터 범위만 블록 설정 후 시작(첫행=머리글행 제외)

2. 행 전체에 서식을 적용하는 방법 = 규칙 유형 : 수식을 ~~~ 만 가능
   : 행전체에 서식을 적용하는 방법은 수식을 사용하는 방법만 가능

3. 수식 작성시 대부분 열절대참조($A4)로 작성


-수식의 수정과 삭제는 규칙관리에서 조건부서식 지정하고 규칙편집


-합계가 20만원이상인 행전체에 노랑채우기
=$F4>=200000


-합계가 25만원이상으로 변경하려면
 : 규칙 관리 - 현재 워크시트 선택 - 해당 조건을 선택 - 규칙 편집
=$F4>=250000



<피벗테이블>
   : 많은 양의 데이터를 통계내는데 쓰는 도구

- 통계를 내고자 하는 일반 자료(엑셀 파일 xlsx, xlsm, 텍스트 파일 txt, csv, prn)의 경우
   :  삽입 탭 - 피벗테이블 - 연결 설정 -더 찾아보기 파일선택 - '데이터 모델에 이 데이터 추가'를 체크 사항 확인 -  위치(영역) 설정

- txt, csv는 '데이터 모델에 이 데이터 추가'를 체크해야만 피벗 테이블 작성가능하나 계산필드(그룹 메뉴-2016 제외)가 비활성화

- xlsx, xlsm은  '데이터 모델에 이 데이터 추가'를 체크하지 않으면 계산 필드, 그룹 메뉴를 지정할 수 있지만, 체크를 하면 마찬가지로 비활성화


- 외부데이터(데이터베이스 accdb, mdb - 액세스 파일)로 피벗테이블 작성 
   : 데이터 탭 - 외부 데이터 가져오기 - 기타 원본 - 쿼리 마법사(테이블 - 열 선택 - 필터링 - 정렬) - 마지막 단계에서 피벗테이블로 작성


- 피벗테이블 구역 : 필터(대분류), 행레이블, 열레이블, 값 영역은 수식을 추출하고자 하는 자료

- 피벗테이블 위치는 필터는 제외

- 계산필드 : 옵션 탭-수식-계산필드-필드명, 수식입력 -추가

- 필드를 비율로 표시하고자 하는 경우 : 필드명에서 마우스 오른쪽 - 값 표시 형식 - 총합계/행/열 비율을 선택

- 필드명 변경 : 필드명 클릭하고 마우스 오른쪽 -값필드 설정 - 사용자 지정 이름(자유롭게)으로 변경

- 슬라이서 삽입 : 보여지는 자료에 항목으로 추가하지 않고, 보고자 하는 부분만 바로 필터링해서 보여주는 기능(항목 선택)

- 새로 고침 : 원본의 내용이 변경된 경우에 피벗테이블로 바로 반영되지 않으므로 꼭 새로고침!!!

- 연결 속성 : 원본의 불필요한 열이나 잘못 가져왔을 경우
  (피벗 테이블 도구 - 분석 탭 - 새로 고침 텍스트 - 연결 속성 - 정의 - 쿼리 편집 - 확인-새로 고침 그림 클릭)

- 데이터 원본 변경 : 원본의 자료가 늘어났고자 영역이 변경되었을 경우(내용아님)에는 데이터 원본 변경에서 셀 주소를 변경


자동서식 
- excel옵션-빠른 실행 도구 모음- 모든 명령- 자동 서식 추가
- 빠른 실행 도구 모음에 나타나므로 활용


<데이터 유효성 검사>
: 범위나 셀에 특정 값이나 형식만 나타나도록 하는 규칙
  특정 범위를 블록 설정한 후 데이터 탭 - 데이터 유효성 검사 - 목록 
  - 원본 : '항목 입력'이나 '=이름 범위', '셀주소' 설정


오류 메시지 - 유효성 검사 규칙에서 어긋나면 표시되는 메시지

설명 메시지 - 셀마다 따라다니면서 설명(메모) 표시

잘못된 데이터 - 유효성 검사 규칙에서 어긋난 셀에만 빨간색으로 표시



<통합>  : 3차원 참조 가능(다른 파일, 다른 시트 통합 가능)

- 실행순서 :
   1. 값이 나타날 곳(결과표)을 블록설정하거나 이름표가 없는 경우 첫셀(시작)을 클릭(표전체)
   2. 데이터 탭 - 통합 - 함수 선택, 참조 자료 추가시 표전체를 추가(ctrl은 사용불가)
   3. 사용할 레이블 : 첫행, 왼쪽열 체크(기준)



<정렬>
- 기준 하나 : 기준(지정) 열의 아무곳이나 누르고 실행
- 기준이 여러 개 : 그 표안에 커서를 두거나 그 표를 블록 설정(제목행하고 붙어있는 경우)하고 실행
- 정렬 시에 병합된 셀과 지정 범위의 총합계 행 같은 것들은 블록에서 제외하면 좋다.
- 사용자 지정 목록은 excel에서 옵션-고급 - [사용자 지정 목록 편집]에서 추가하거나 정렬 시에 정렬방법에서 사용자 지정 목록을 선택하고 추가해서 사용

- 행(가로) 방향의 정렬 시에는 데이터만 블록 설정(행머리글 제외)한 후 
  정렬 옵션에서 방향(왼쪽에서 오른쪽)을 변경하고 정렬 실행

- 숫자, 글자, 공백(오름차순), 글자, 숫자, 공백(내림차순)
- 소문자, 대문자(오름차순), 대문자, 소문자(내림차순) : 이 경우는 정렬옵션에서 대소문자 구분 체크



<부분합>
- 그룹화 기준(~별)에 따라 정렬부터 하고 실행
- 두 개 이상의 함수 - 추가 시에는 '새로운 값으로 대치'를 해제하고 실행
- 부분합 작성 후 행머리글 앞에 1, 2, 3...=그룹수준, 선(+, -)=데이터 윤곽선
- 그룹해제-윤곽 지우기 실행하면 그룹수준과 윤곽선만 삭제되고 원본 데이터와 부분합은 그대로 남아있음.


<가상분석-what if 분석 도구>

<목표값 찾기>
- 정의 : 수식(결과셀)을 원하는 값(목표값)으로 변경했을 때 수식에 사용된 데이터(한 곳만)가 어떻게 변화될 것인가 예측

     예) 월 10만원씩 연이율 2%에 3년짜리 저축 가입하고 있는데, 예금 총액 370만원인데, 예금 총액 천만원을 모으려면 매월 얼마씩 납부해야 할까요?
수식 셀(결과셀) : 예금총액이 있는 셀(FV)
목표값(찾는값) : 천만원
값을 바꿀 셀 : 월납부액(PMT)

- 실행 방법 : 문제 그대로 입력하면 실행 가능
- 단점 : 확인하면 예측치만, 취소하면 원본만 나타남 - 원본하고 비교 불가



<데이터 표>
- 한개나 두 종류의 값을 다양하게 변경해서 예측 - 결과를 표형태로 출력
- 혼합참조의 수식과 결과가 동일, 배열수식으로 실행
- 실행 방법 : 
   1. 표의 첫 셀에 예측하고자 하는 기본 수식을 배치(셀 연결, =셀지정)
   2. 그 셀부터 값이 나타날 곳(변경할 값들까지) 블록 설정
   3. 데이터 탭 - 가상분석 - 데이터 표실행
   4. 행 입력 셀 = 수식을 기준으로 행방향으로 있는
                       값들(첫행)이 들어갈 자리(수식의 자료의 원본)
   5. 열 입력 셀 = 수식을 기준으로 열방향으로 있는
                       값들(왼쪽열)이 들어갈 자리(수식의 자료의 원본)

- 한 종류만 변경시 수식을 값이 나타날 바로 위의 셀로 가져와서 실행
- 단점 : 두 종류까지만 가능, 세 종류 이상은 설정 불가능



<시나리오> 
- 여러 종류의 값을 다양하게 변경해서 예측의 결과를 보고서(요약 시트)로 출력
- 셀에 이름 설정하고 실행하면 편리

- 시나리오 작성 : 추가 - 시나리오 이름 - 변경될 셀 지정 - 변경될 값 입력
- 시나리오가 여러 개인 경우 변경 셀은 동일하고, 변경될 값은 다양하게 변화

- 시나리오 요약 시트(보고서) 작성 : 요약 - 결과 셀 지정(변경 값이 적용될 수식결과)  현재 시트(원본) 앞에 시나리오 요약보고서 나타남.
- 시나리오 요약 보고서를 삭제해도 시나리오와 원본 데이터는 삭제 안됨.

- 변경 셀 : 예측하고자 하는 값이나 결과 셀(수식)에 적용될 데이터(숫자)
- 결과 셀 : 변경 셀의 값이 적용되어 나타날 수식 셀
- 시나리오가 변경/수정되면 기존의 요약시트(보고서)는 삭제하고, 다시 작성



<인쇄>

: 페이지 레이아웃 - 페이지 설정

- 여러 장의 자료를 한 장에 인쇄 
   : 인쇄 미리보기 - 페이지 설정 - 페이지 탭 - 자동 맞춤
     페이지 나누기는 자동 맞춤 상태에서는 불가능
1 : 1, 모든 열을 한 페이지에(너비 1), 
모든 행을 한 페이지에(높이 1)

- 한 쪽으로 치우쳐 있는 자료를 균형 맞추기
   : 인쇄 미리보기 - 페이지 설정 - 여백 탭 - 페이지 가운데 맞춤(가로/세로) 설정

- 제목행을 모든 페이지에 반복 시키기
   : 기본보기 화면 상태-페이지 레이아웃 탭 - 인쇄 제목 - 반복할 행
     - 행번호 지정
    (인쇄 미리보기에서는 설정 불가능)

- 페이지 설정
  페이지 탭 : 용지방향, 확대/축소, 자동맞춤(한 페이지에 여러 장 출력)
  여백 탭 : 여백 설정, 페이지 가운데 맞춤(가로/세로)
  머리글/바닥글 탭 : 페이지 번호, 짝/홀수 다르게 설정, 첫 페이지 다르게 설정, 페이지 여백에 맞추기
  시트탭 : 인쇄영역(일부분 설정), 인쇄 제목(반복할 행/열), 
             눈금선/행열 머리글/간단하게 인쇄/메모/오류 인쇄 사항 설정




<보기>

틀 고정-틀 고정(고정 이동하지 않음)/첫 행 고정(1번행 고정)


- 화면 상에 제목행 계속 보이게 설정 
  : 고정할 다음 행번호를 클릭하고 보기 탭 - 틀 고정 - 틀 고정 설정
  : 1번 행은 첫 행 고정으로 설정
  : 행과 열을 모두 고정하려는 경우 - 이동가능한 첫 셀을 클릭하고 보기 탭 - 틀 고정 - 틀 고정 설정

- 틀 고정 해제 : 보기 탭 - 틀 고정- 틀 고정 취소 

- 페이지 레이아웃 보기에서는 틀 고정 불가능


숨기기
: 행/열번호를 선택 후 행/열번호 위에서 마우스 오른쪽 숨기기

숨기기 취소
: 숨겨진 행/열의 앞뒤 번호를 블록설정 후 행/열번호 위에서 마우스 오른쪽 숨기기 취소


나누기(동일 시트)
:보기 탭 - 나누기 클릭
행번호 클릭 시 : 수평 나누기
열번호 클릭 시 : 수직 나누기
중간 셀을 클릭시 : 1/4 나누기(수평, 수직 모두 다)

나누기 취소
: 보기 탭 - 나누기 다시 클릭


<창 추가>

- 한개 파일의 여러 시트를 비교해서 보거나 수식을 작성할 경우

  1. 보기 탭 - 새창(원하는 갯수만큼 창추가- 파일명 뒤에 갯수확인)
  2. 보기 탭 - 모두 정렬 - 정렬 방법 선택 - 현재 통합 문서 창 부분을 꼭 체크!



<기타 작업 - 차트>

차트 문제 풀이 시 주의점
- 작성된 차트를 수정 : 새로 만들면 0점 처리 - 위치 확인

- 차트 위치 셀선에 Alt+드래그로 이동

- 자료의 열은 차트의 데이터 계열로 나타남
- 자료의 행(레코드)는 차트의 항목으로 나타남
- 데이터 계열 중 하나만 다시 클릭하면 데이터 요소
- 자료의 계열이 여러 개인 경우 수치가 차이가 많이 나거나, 단위가 다른 경우는 보조(세로)축을 추가해서 사용(데이터 계열/요소 서식-보조축 선택)
- 보조 축을 추가하면 계열이 겹쳐지므로 그 지정 계열만 차트 종류 변경 = 이중축 혼합형 차트

- 가로(항목) 축을 상단(위)에 배치하려면 세로(값) 축 서식 - 가로 축 교차 - 축의 최대값

- 원형 차트 : 하나의 계열만 표시, 전체를 100%로 보고 각 항목의 비율을 나타냄.
        레이블 옵션서식 - 백분율 설정하면 비율 표시
        전체 조각 - 데이터 계열을 선택하자마자 바깥쪽으로 드래그
        하나만 조각 - 데이터 요소를 선택하고 바깥쪽으로 드래그

- 여러 계열의 비율을 표시하려면 도넛 차트로 설정

- 차트 제목을 셀의 내용과 연결
   : 차트 제목을 클릭한 후 수식 입력줄에 등호(=)를 입력한 후 셀을 선택

- 막대 차트 채우기 : 데이터 계열/요소 서식 - 채우기 - 그림 또는 질감 채우기- 클립아트 - 검색어 입력/임의의 - 이미지 선택