[e-Test] 엑셀 학습 노트

메뉴를 통한 변경이나 오른클릭을 통한 변경은 제외함.
모르는 기능 및 방법 위주로 정리.

=== 서식지정 ===
시트 관리
- 시트 삽입 단축키 : Shft + F11 or Alt + Shift + F1
- 시트 이름 바꾸기 : 시트탭 더블클릭

서식 지정
- 테이블 전체 선택 : Ctrl + *
- 사용자 서식
  # : 하나의 숫자 자리수, 없으면 표시 안함
  0 : 하나의 숫자 자리수, 없으면 0 표시
  ? : 소수나 분수의 자리를 맞추기 위한 코드. 없는 자리에 공백 추가
  $, \, (엔표시) : 숫자 데이터에 통화 기호 삽입
  '문자열' : 숫자 데이터 뒤에 문자열 추가
  _ 문자 : 해당 문자 크기 많큼 공백 표시
  * 문자 : 해당 문자 반복 표시
  @ : 문자열 뒤에 특정한 문자열 추가

조건부 서식
- 특정한 조건에 맞는 데이터의 글꼴 색이나 테두리 또는 무늬와 같은 서식을 다르게 지정하여
  다른 데이터와 구분하여 표시하고자 할 때 사용.
- 지정, 변경, 삭제 : 서식 - 조건부 메뉴

자동 서식
- 엑셀은 17개의 자동 서식이 제공 : 테두리, 글꼴 등을 한번에!!


=== 수식과 기본함수 ===

=SUM()
  * 괄호 안에는 숫자나 적용할 셀 범위가 들어감.
  * 셀 선택후 F4 키는 누르면 절대 번지로 지정됨. $A$1 ==> 드래그 하여 다른셀에 값을 채울때 고정됨
  * 위의 두 가지는 특별한 말이 없는한 다른 함수도 동일

=AVERAGE()
  : 평균값

=MAX(), =MIN()
  : 최대, 최소값

=COUNT()
  : 값이 입력되어 있는 셀의 수를 구함

=RANK(Number, Ref, Order)
  : 순위를 구하는 함수
  * Number : 순위를 구하려고 하는 기준 수(셀 주소)
  * Ref : 순위를 구하기 위해 필요한 비교 범위
  * Order : 순위를 부여하는 방법. ( 생략 = 0 혹은 1 )

=ROUND(Number, Num_digits)
  : 반올림
  * Number - 반올림할 수 있는 수
  * 반올림할 number 의 자릿수 ( 자릿수의 위치는 0 을 기준으로 - 면 왼쪽, +면 오른쪽 )
  * 파생함수로 =ROUNDUP, =ROUNDDOWN 이 있음

=MOD(N, D)
  : N 을 D 로 나눈 나머지


=== 함수활용 ===
=IF(Logical_test, Value_if_true, Value_if_false)

=AND(logical_test1, logical_test2  ...)
  : 조건식이 모두 참인 경우에만 참(TRUE)을 출력

=OR(logical_test1, logical_test2, ...)
  : 조건식이 하나라도 참일 경우 참(TRUE)을 출력

=NOT(logical)
  : 조건식의 결과값을 반대값으로 나타냄

=SUMIF(Range, Criteria, Sum_range)
  : Range 에 해당하는 셀이 조건에 만족하는 경우 Sum_range 에 해당하는 셀의 값을 합함.
  * Range : 조건을 적용시킬 셀 범위
    Criteria : 숫자, 수식 또는 텍스트 형태의 찾을 조건. 반드시 큰따옴표 " " 로 묶어야 함
    Sum_range : 합을 구하려는 실제 셀 범위, 생략시 Range 와 같음

=COUNTIF(Range, Criteria)
  : SUMIF() 참고

* 함수마법사 사용시, 명령줄(?)의 함수를 클릭하면 그 함수에 관련된 대화상자로 바뀜


=== 함수활용 - 텍스트 ===

=LEFT(Text, Num_chars), =RIGHT(Text, Num_chars)
  :왼쪽/오른쪽으로 부터 Num_chars 만큼의 텍스트를 추출
  * Text : 텍스트 문자열 혹은 셀
  * Num_chars : 추출할 문자수
   
=MID(Text, Start_num, Num_chars)
  : 지정한 위치로부터 지정한 개수의 문자 추출
  * Start_num : 시작 위치 지정

=CONCATENATE(Text1, Text2, ...)
  : 하나의 텍스트로 합침, 같은 기능을 하는 연산자는 &

=VALUE(Text)
  : 문자열 형식의 수를 숫자형식으로 변환

=LEN(Text)
  : 텍스트 길이


=== 함수활용 - 찾기/참조 ===

=INDEX(Array, Row_num, Column_num)
  : 데이터 범위의 지정한 행과 열번호에 해당하는 값을 찾아서 보여줌
    (해당 Array 의 왼쪽위를 기준으로 Row, Col 값)

=MATCH(Loopup_value, Lookup_Array, Match_type)
  : 찾고자 하는 값이 지정된 범위 내에서 몇 번째 있는지 위치를 알려줌
  * Lookup_value : 찾고자 하는 값
  * Lookup_Array : 찾으려고 하는 값이 포함된 셀들의 범위
  * Match_type:
      1 - Lookup_value 보다 작거나 같은 값중에서 최대값을 찾음. 반드시 오름차순 정렬이 되어 있어야 함
      0 - Lookup_value 와 같은 첮째 값. 임의의 순서일때 사용
      -1 - Lookup_value 보다 크거나 같은값 중에서 최소값을 찾음. 내림차순 정렬이 되어 있어야 함.

=VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)
  : 지정 범위의 첫째 열에서 Lookup_value 를 찾아, 찾은 Row 의 Col_index 에 해당하는 셀 값을 구함
  * Lookup_value : 배열의 첫 번째 열에서 찾으려는 값
  * Table_array : 기준이 되는 표(테이블)범위
  * Col_index_num : 찾으려는 열의 번호
  * Range_lookup : TRUE - 정확한 값이 없는 경우 Lookup_value 보다 작은 값중 최대값을 찾는다.
                           FALSE - 일치하는 값이 없으면 #N/A 오류값을 표시

=HLOOKUP()
  : VLOOKUP() 함수와 거의 동일. 범위의 첫 행에서 값을 찾아, 해당 Col 의 Row_index 에 해당하는 값 구함

=CHOOSE(index_num, Value1, Value2, ...)
  : 1 ~ 29 사이의 인수에 해당하는 값을 목록 중에서 찾아줌
  * index_num : 인수( 셀 혹은 수식 - 값은 1에서 29사이여야 함.)
  * Value1 : 인수가 1일 때 돌려주는 값
  * ValueN : 인수가 N일 때 돌려주는 값


=== 차트 작성 및 편집 ===
- 차트 영역 범위 지정 방법
  1. 계열 포함하여
  2. X(항목)축 레이블 제외한 계열과 수치데이터 포함 << 좀더 편리함

- 실행 : 삽입-차트
           표준도구모음의 아이콘
           Alt + I + H

- 차트 마법사
 1. 차트 종류 결정
 2. 데이터의 범위, 방향, 계열 지정
   * 테이터의 방향(열 or 행) : X 축의 [항목]을 나타냄( 레이블이 아님!! 표시되는 데이터를 말함 )
   * 계열 : X 축의 레이블 설정.
 3. 차트의 구성요서 존재 여부와 모양, 위치 등 차트 옵션 지정
   * 제목, 축, 눈금선, 범례, 데이터 레이블, 데이터 테이블
 4. 차트 위치 지정 - 새로운 시트로 혹은 워크시트에 삽입

- 차트 편집하기
  1. 차트 제목 서식 : 차트 제목 더블클릭 혹은 우클릭-제목서식
  2. 축 서식 : 축 더블클릭 혹은 우클릭-축서식
    * 무늬, 눈금선 주단위 등
  3. 데이터 계열 서식 : 그림 개체의 서식을 말함. 역시 더블 클릭 혹은 우클릭-데이터계열서식
    * 데이터 레이블, 계열 순서, 간격 깊이 및 너비, 차트 갚이 설정
  4. 3차원 보기 : 차트-3차원보기 혹은 우클릭-3차원보기
    * 기울임이나 회전 설정

- 기타 팁
  1. 같은 데이터 계열의 요소마다 다른색 사용하기
    : 데이터 계열 서식 - 옵션 탭 - 요소마다 다른 색 사용 체크


=== 데이터 관리와 분석 ===
- 필터 : 방대한 데이터 목록에서 일정한 조건에 맞는 자료만 화면에 나타내게 함.

- 자동필터 : <필터> 단추를 이용하여 조건을 설정해 빠르고 쉽게 데이터 추출
  * 자동필터 실행 : 데이터 - 필터 - 자동필터  혹은  Alt + D + F + F
  * 자동필터 해제 : 필터 단추가 있는 상태에서 다시 위 메뉴나 단축키 실행
  * 공백 데이터 추출 : (필드 값 없음) 선택
  * Top 10 : 선택적 자동 필터 - 표시할 항목 수(혹은 퍼센트), 순서
  * 사용자 지정 자동 필터 - 원하는 조건을 지정
  * 고급필터 : 셀에 조건을 입력한 다음 셀에 입력한 조건을 이용해 필터링

- 피벗 테이블 : 방대한 양의 데이터를 사용자가 한 눈에 쉽게 파악할 수 있도록 데이터 요약해 보여주는 기능
                     ( 자료를 종류별로 묶어서 요약해서 보여줌 )
  * 실행 : 데이터-피벗 테이블/피벗 차트 보고서  혹은  Alt + D + P]
  * 레이아웃 : 피벗 테이블의 구조 지정
  1. 테이블 아무 셀이나 선택한 뒤 실행
  2. (일반적으로 디폴트를 그대로 사용)
  3. 3단계에서 레이아웃 설정

- 정렬 : 데이터를 일정한 순서로 정렬
  * 실행 : 데이터 - 정렬  혹은 표준도구모음  혹은 Alt + D + S
  * 두가지 이상의 조건도 가능
  * 사용방법 : 임의의 셀 선택후 실행

- 부분합 : 특정한 항목을 기준으로 그룹으로 묶은 다음 각 그룹에 포함된 자료들만 따로 따로 계산하는 기능
              ( 반드시 정렬 먼저! 부분합후 각 그룹 밑에 결과가 나타남 )
  * 실행 : 데이터 - 부분합  혹은  Alt + D + B
  1. 그룹화할 항목 : 부분합 계산을 위해 그룹으로 묶을 항목
  2. 사용할 함수 : 원하는 계산 방법
  3. 부분합 계산 항목 : 실질적으로 부분합 계산이 이루어질 필드
  * 부분합 기능에는 같은 종류의 데이터를 그룹으로 묶는 기능이 없으므로, 정렬을 이용해 그룹으로 묶음
  * 부분합 후에는 왼쪽에 윤곽기하고 나옴, 세부데이터 표시/숨김때 사용

- 텍스트 나누기 : 하나의 셀 내용을 여러셀로 나눔
  * 실행 : 데이터 - 텍스트 나누기  혹은  Alt + D + E

- 목표값 찾기 : 특정한 목표값을 얻기 위한 하나의 조건 값이 어떻게 변하는지 찾는 기능
                     ( 목표치 달성을 위해 특정 조건값이 얼마가 되어야 하는지 구할 때 사용 )
  * 실행 : 도구 - 목표값 찾기  혹은  Alt + T + G
  1. 수식셀 : 수식이 입력되어 있는 셀로 값을 바꿀 셀
  2. 찾는값 : 변수와 수식을 이용해 최종적으로 계산할 목표값
  3. 값을 바꿀 셀 : 수실셀의 수식을 계산해 찾는 값을 만들기 위해 값을 조정할 대상 셀

- 시나리오 : 변수의 값이 변경되었을 때 여러개의 결과 값이 어떻게 변경되는지 분석하는 기능
                 ( 변수값의 변경에 따라 결과가 어떻게 바뀌는지 여러 시나리오 별로 보여 줌 )
  * 실행 : 도구 - 시나리오  혹은  Alt + T + E
  1. 추가를 통해 시나리오들 추가
  2. 표시 혹은 요약을 통해, 시나리오 대로 변경시 어떻게 결과가 변하는지 알아보기 쉽게 한다.