(컴퓨터활용능력) 1급 엑셀(스프레드시트) 실기_총 정리/엑셀 단축키 요약

[ 컴퓨터 활용능력 게시글 타래 ]

1. 컴퓨터 활용 능력 1급, 2급 알아보기 : 컴퓨터 활용 능력 1급, 2급 알아보기
2. 컴활 1급 준비 전 필독 : 컴활 1급 준비 꿀팁 ! 접수/필기/액셀/액세스
3. 컴퓨터 활용 능력 1급 필기 공부법과 후기 : 컴활 1급 필기 빠르게 합격하기! / 직장 병행 후기
4. 컴퓨터 활용 능력 1급 실기 공부법과 후기 : 컴활 1급 실기 빠르게 합격하기! / 직장 병행 후기
5. (실기) 스프레드시트 (엑셀) 요약 : 본 게시글
6. (실기) 스프레드시트 (엑셀) 실전 전략 :엑셀(스프레드시트) 실기_기출 분석/실전 전략
7. (실기) 데이터베이스 (엑세스) 요약 : 엑세스(데이터베이스)실기_이론 총정리/단축키 정리
8. (실기) 데이터베이스 (엑세스) 실전 전략 :엑세스(데이터베이스) 실기_기출 분석/실전 전략
9. 컴퓨터 활용 능력 응시 전 필수 체크리스트 (자료 공유) : 시험 전 체크 리스트 - 컴퓨터 활용 능력 1급/2급

 
!! 시험 전 1회독 필수 !!
전반적인 컴활 실기 1급 엑셀 영역에 대한 놓치기 쉬운 부분을 정리하는 요약입니다.
평소 숙지하면 좋은 엑셀 단축키도 함께 정리하였습니다.
 
본 내용은 빠뜨린 개념이 없는지 확인하는 목적으로 사용하고, 반드시 기출문제를 풀어봐야 합니다.
기출문제 및 실전에서 사용할 전략의 경우 엑셀(스프레드시트) 실기_기출 분석/실전 전략을 참고하세요!
 

[알고 가면 좋은 기본 엑셀 단축키]

숙지하면 손이 빨라지는 기본 단축키입니다.
실기 시험 진행 시 꼭 숙지하면 좋을 단축키는 하단에 이론과 함께 정리되어 있습니다.

1. 수식 입력 시 커서 이동을 위해 방향키를 눌렀을 때 셀 이동이 될 때 : F2
   1. 방향기 이동 시 Point Mode (셀 이동), 편집 Mode (수식 내 이동) 의 2가지가 있으므로 F2로 Mode을 바꿀 수 있다.
2. 일반 단축키
   1. 차트 시트 만들기 : Alt + F1
   2. 이전 작업 반복 : F4
   3. 찾기 : Ctrl + F
   4. 찾아 바꾸기 : Ctrl + H
   5. 선택하여 붙여넣기 : Ctrl + Alt + V
   6. 실행 취소 / 다시 실행 : Ctrl + Y / Z
   7. 행 선택 : Alt + Space
   8. 열 선택 : Ctrl + Space
3. 시트 편집 관련 단축키
   1. 화면 확대/축소 : Ctrl + 마우스휠, 또는 +/-
   2. 셀 범위 삽입/삭제 : Ctrl + +/-
   3. 행/열 숨기기 : Ctrl + 9/0
   4. 행/열 숨기기 취소 : Ctrl + Shift + 9/0
   5. 그룹 해제/설정 : Alt + Shift + ←/→
4. 셀 서식 관련 단축키
   1. 행 높이 자동맞춤 : Alt + H + O + A
   2. 열 너비 자동맞춤 : Alt + H + O + I
   3. 오른쪽 정렬 : Alt + H + A + R
   4. 왼쪽 정렬 : Alt + H + A + L
   5. 가운데 정렬 : Alt + H + A + C
   6. 글꼴 취소선 : Ctrl + 5
   7. 글꼴 밑줄 : Ctrl + U
   8. 글꼴 굵게 : Ctrl + B
   9. 글꼴 기울임꼴 : Ctrl + I
   10. 글꼴 대화상자 실행 : Ctrl + Shift + F
   11. 셀 서식 대화상자 실행 : Ctrl + 1
5. 함수 관련 단축키
   1. 상대참조를 절대참조로 형식 변경 : F4
   2. 수식 보기 : Ctrl + ~

 

[엑셀]

기본작업

1. 고급필터/자동필터
   1. 고급필터 : 수식을 입력해 데이터를 필터하여 추출한다.
      1. 추출할 필드명을 복사위치에 작성 시 해당 필드만 필터되어 추출된다.
   2. 자동필터 : 데이터 자체에 필터링을 설정하여 바로 추출한다.
      1. 필터 단축키 : Ctrl + Shift + L
         1. 필터/목록 펼치기 : Alt + ↓
      2. 엑셀 자체적으로 제공하는 기능을 사용한다.
2. 조건부 서식
   
   1. 수식 입력 시 '=' 입력 필수, 액세스는 없이 시작한다.
   2. 조건부서식 적용 시 필드명 행은 제외하고 데이터만 선택한다.
   3. 상세 설정 적용 시 "새 규칙" 선택, 자체 제공 기능 적용 시 "셀 강조 규칙, 상위/하위 규칙, 데이터 막대, 색조, 아이콘 집합" 이 있다.
3. 시트 보호와 통합 문서 보기
   1. 셀 서식 대화상자 실행 단축키 : Ctrl + 1
   2. 모든 셀에는 기본적으로 '셀 잠금'이 설정되어 있다.
   3. 차트 영역 서식은, 그래프 외 영역을 우클릭해야 메뉴에서 확인 가능하다. (그래프 영역 선택 시 "그림 영역 서식"이 열림)
   4. [시트보호] 대화상자에는 기본적으로 '잠긴 셀의 내용과 워크시트 보호'가 설정되어 있다.
   5. [시트보호] 를 해제하려면 [검토]-[변경 내용]-[시트보호] 를 클릭한다.
4. 페이지 레이아웃
   1. [페이지설정] 대화상자에서 페이지, 여백, 머리글/바닥글, 시트 (인쇄 영역, 인쇄 제목, 인쇄 스타일 (눈금선, 흑백, 간단하게, 행/열 머리글)) 설정이 가능하다.
5. 외부 데이터 가져오기
   1. 쿼리 마법사 실행 순서 : 열 선택 / 데이터 필터 / 정렬 순서 / 마침

 

계산 작업

• 계산식

산술연산자
연산자	기능	연산자	기능	연산자	기능
+	더하기	*	곱하기	^	거듭제곱
-	빼기	/	나누기	%	백분율
비교연산자
연산자	기능	연산자	기능	연산자	기능
>	초과	<	미만	=	같다
>=	이상	<=	이하	<>	같지않다
연결연산자
연산자	기능
&	여러 데이터를 하나로 연결
예) "커피"&"가격 : "&3500			커피가격 : 3500

 
* 2023년 새로운 컴활 엑셀 함수 정리
AREAS : 영역 수
TRANSPOSE : 행/열 바꿈
    입력될 곳을 미리 블럭설정 후, TRANSPOSE(영역) 후 Ctrl+Shift+Enter / TRANSPOSE({“배열요소1”,”배열요소2“,…}) 로 입력
INDIRECT : 지정한 셀주소
FIXED(숫자,소숫점자리,쉼표 유무) : 수를 고정 소수점 형식의 텍스트로 바꿈
ADDRESS(행번호,열번호,참조영역의 유형,(논리),(Sheet_text)) : 셀주소
CELL(확인해 볼 속성,셀) : 셀의 속성
RANDBETWEEN(수1,수2) : 두 수 중의 난수
SIGN(수) : 수의 부호값 (정수면 1, 0이면 0, 음수면 -1)
SQRT : 수의 제곱근
ISERROR : 오류면 TRUE

• 데이터베이스 함수

조건을 만족하는 자료들에 대한 각종 분석
형식 : 함수(데이터베이스 범위,필드,조건범위)
연산자	기능
DSUM	조건에 맞는 데이터의 합계
DAVERAGE	조건에 맞는 데이터의 평균
DCOUNT	조건에 맞는 데이터의 숫자 개수
DCOUNTA	조건에 맞는 공백이 아닌 데이터의 숫자 개수
DMAX	조건에 맞는 데이터의 최대값
DMIN	조건에 맞는 데이터의 최소값
DSTDEV	조건에 맞는 데이터의 표준편차
DVAR	조건에 맞는 데이터의 분산
DGET	조건에 맞는 고유한 데이터를 추출
DPRODUCT	조건에 일치하는 값을 곱함

 * 조건 입력 : 필드명이 위, 조건이 아래에 가게 입력한다.

자료 출처 : 이기적 컴활 1급 실기 (2021) p66

• 수학과 삼각  함수

연산자	()	기능	예시
SUM	인수1,인수2..	총합	SUM(1,4)=5
SUMIF	범위,조건,합계 범위	조건에 맞는 값 총합	SUMIF(A1:A10,">=40",C1:C10)=
SUMIFS	합계를 구할 범위,조건 범위1,조건1,조건 범위2,조건2,..	여러 조건에 맞는 값 총합	SUMIFS(A1:A10,B1:B10,">10",C1:C10,"<5")=
ROUND	인수,자릿수	반올림	ROUND(3.141592,0)=3
ROUNDUP	인수,자릿수	올림	ROUNDUP(3.141592,0)=4
ROUNDDOWN	인수,자릿수	내림	ROUNDDOWN(3.141592,0)=3
ABS	숫자	절대값	ABS(-2023)=2023
MOD	인수,제수	나머지	MOD(5,2)=1
TRUNC	인수,자릿수	지정한 자릿수를 소수점 아래에 남기고 나머지 자리 버림	TRUNC(5.2555,2)=5.25
INT	인수	인수를 넘지 않는 가장 가까운 정수	INT(5.2555)=5
SQRT	숫자	양의 제곱근	SQRT(9)=3
FACT	숫자	계승	FACT(3)=6
POWER	인수1,인수2..	거듭제곱승	POWER(2,4)=16
EXP	수치	e의 거듭제곱승	EXP(2)=7.389056
PRODUCT	수치1,수치2..	수치를 모두 곱함	PRODUCT(1,4)=4
SUMPRODUCT	배열1,배열2..	수치배열에 대응하는 요소의 곱의 합	SUMPRODUCT({1,2,3},{7,8,9})=50
RAND		0 이상 1 미만의 난수	RAND()=0.70091 (랜덤)
RANDBETWEEN	최소,최대	수치 이상 ~ 이하의 수치를 분포로 하는 정수 난수	RANDBETWEEN(1,4)=3.12355
PI		원주율	3.141593
MDETERM	정방배열	행렬식	MDETERM({3,2,3;4,5,6;7,8,9})=-6
MINVERSE	정방배열	역행렬식	MINVERSE({3,2,3;4,5,6;7,8,9})=0.5
MMULT	정방배열1,정방배열2..	두 배열의 행렬곱	MMULT({1,2;3,4;5,6},{6,5,4;3,2,1})=12

[참고-배열]
 *배열의 행렬식

 
*배열의 역행렬

 
*두 배열의 행렬곱

출처 : https://images.app.goo.gl/maqkKBQSuvp8EgSH7

 

• 통계 함수

연산자	()	기능	예시
AVERAGE	인수1,인수2…	인수들의 평균값	AVERAGEA(10,20,30)=20
AVERAGEA	인수1,인수2…	문자열이나 논리값이 있는 인수들의 평균값	AVERAGEA(10,20,30,TRUE)=15.25 (텍스트와 FALSE는 0, TRUE는 1로 계산)
AVERAGEIF	범위,조건,평균을 구할 범위…	조건을 만족하는 모든 셀의 평균	AVERAGEIF(A2:A5,">2000",B2:B5)
AVERAGEIFS	평균범위,조건범위1,조건1,조건범위2,조건2,…	여러 조건을 만족하는 모든 셀의 평균
MIN	인수1,인수2…	최소값	MIN(10,20,30)=10
MINA	값1,값2,값2…	숫자, 논리, 텍스트 등 인수 목록에서 최소값	MINA(0,0.1,FALSE)=0
MAX	인수1,인수2…	최대값	MAX(10,20,30)=30
MAXA	값1,값2,값2…	숫자, 논리, 텍스트 등 인수 목록에서 최대값	MINA(0,0.1,TRUE)=1
LARGE	배열,K	배열에서 K번째 큰 값	LARGE(A1:A10,3)=
SMALL	배열,K	배열에서 K번째 작은 값	SMALL(A1:A10,3)=
RANK.AVG	값,참조 영역,[순위 결정 방법]	참조 영역에서 순위를 구함(동순위면 평균 순위 반환)	2 셀이 공동 4위이면 값 = 4.5 (순위 결정 방법 : 0이나 생략 - 내림차순 / 0이 아닌 값 - 오름차순)
RANK.EQ	값,참조 영역,[순위 결정 방법]	참조 영역에서 순위를 구함(동순위면 높은 순위 반환)	2 셀이 공동 4위이면 값 = 4 (순위 결정 방법 : 0이나 생략 - 내림차순 / 0이 아닌 값 - 오름차순)
VAR	표본의 범위	분산
STDEV	표본의 범위	표준 편차
MEDIAN	인수1,인수2…	중간값	MEDIAN(10,20,50,90,70)=50
MODE	인수1,인수2…	최빈값	MODE(10,20,20,90)=20
COUNT	인수1,인수2…	수치 데이터 개수
COUNTA	인수1,인수2…	인수 중 공백이 아닌 데이터 개수
COUNTBLANK	범위	공백인 셀의 개수
COUNTIF	조건 범위, 조건	조건에 맞는 셀의 개수
COUNTIFS	조건범위1,조건1,조건범위2,조건2,…	여러 범위에 걸쳐 조건을 적용하고 모든 조건에 만족하는 셀의 개수
FREQUENCY	데이터 배열, 구간 배열	데이터의 도수 분포를 세로 배열형태로 구함 (Ctrl+Shift+Enter)
PERCENTILE	배열,K	배열(범위)에서 K번째 백분위 수	PERCENTILE({1,2,3,4,5},0.2)=1.8
GEOMEAN	인수1,인수2…	양수 데이터 집합의 기하 평균
HARMEAN	인수1,인수2…	양수 데이터 집합의 조화 평균

 [참고-산술 평균과 기하 평균과 조화 평균]

* 조건에 조건식이 들어갈 때 : 부등호에만 따옴표를 사용하고 &으로 조건식을 써 준다.
EX) COUNTIFS(A10:A20,">200",B10:B20,">="&AVERAGE(B10:B20))&"명"
       ↘ A10~A20 : >200 
       ↘ B10~B20 : B10~B20의 평균 이상 
 

• 찾기/참조 함수

연산자	()	기능	예시
LOOKUP	검색값,검사범위,대응범위 검색값,배열	검사범위에서 검색값을 찾아 대응범위에서 같은 위치의 값을 표시 배열에서 검색값을 찾아 마지막 행/열의 같은 위치에 있는 값을 표시	LOOKUP("가",A1:A5,B1:B5)=LOOKUP("가",A1:B5) : "가"를 A1:A5에서 찾아 B1:B5의 같은 위치에 있는 값을 표시
VLOOKUP	검색값,범위,열번호,[검색 유형]	수직표를 참조 [검색 유형] TRUE/생략 : 근사값 FALSE/0 : 정확한 값	VLOOKUP("가",A1:B5,2,0) : "가"를 범위의 첫 열에서 찾아 지정한 열번호에서 같은 행의 값을 표시
HLOOKUP	검색값,범위,열번호,[검색 유형]	수평표를 참조 [검색 유형] TRUE/생략 : 근사값 FALSE/0 : 정확한 값	HLOOKUP("가",A1:C5,2,0) : "가"를 범위의 첫 행에서 찾아 지정한 행번호에서 같은 열의 값을 표시
TRANSPOSE	배열	배열의 수평/수직 방향을 서로 바꾸어 나타나게 함 (Ctrl+Shift+Enter 입력)
INDEX	범위,행번호,열번호,참조영역번호	표나 범위 값, 값에 대한 참조 영역을 구함	INDEX({1,2,3;4,5,6;7,8,9},1,3) : 배열에서 (1,3)의 값
OFFSET	기준,행수,열수,[높이],[폭]	기준으로부터 행수, 열수만큼 떨어진 곳의 특정 높이와 너비의 참조 영역을 표시	OFFSET(A1,2,2,1,1) : A1셀에서 2행, 2열 이동한셀에서의 1행, 1열 셀의 값
CHOOSE	인덱스 번호,값1,값2	인덱스 번호에 위치에 있는 값
MATCH	검사값,검사범위,[검사유형]	검사값을 검사범위에서 검색하여 대응하는 값이 있는 경우 상대적 위치 [검사 유형] 1 : 작거나 같은 값 중 최대값 0 : 같은 첫째 값 -1 : 크거나 같은 값 중 최소값
ADDRESS	행번호,열번호,참조유형	지정된 행/열번호에서의 셀의 주소	ADDRESS(4,5)=$E$4
AREAS	참조	참조 영역에 있는 (개체가 있는) 영역 수
COLUMN	참조	참조 영역의 열 번호
COLUMNS	배열	참조 영역의 열 수
ROW	참조	참조 영역의 행 번호
ROWS	배열	참조 영역의 행 수
HYPERLINK	대상 문서 경로 또는 파일 이름, 셀에 표시되는 이동 텍스트 또는 숫자 값
INDIRECT	참조할 텍스트	텍스트 문자열로 지정한 셀 주소

 *LOOKUP 류 함수 : 검색값을 범위에서 찾을 때, 숫자로 된 검색값을 "100 미만" 등의 텍스트여도 가능하다.

 

• 날짜/시간 함수

연산자	()	기능
YEAR	일련번호 또는 날짜 문자열	날짜의 연도
MONTH	일련번호 또는 날짜 문자열	날짜의 월
DAY	일련번호 또는 날짜 문자열	날짜의 일자
HOUR	일련번호 또는 시간 문자열	시간의 시
MINUTE	일련번호 또는 시간 문자열	시간의 분
SECOND	일련번호 또는 시간 문자열	시간의 초
TODAY		컴퓨터 시스템 현재 날짜
NOW		컴퓨터 시스템 현재 날짜 및 시간
DATE	연,월,일	지정한 연,월,일로 날짜 데이터
TIME	시,분,초	지정한 시,분,초로 날짜 데이터
WEEKDAY	날짜 문자열, 종류	날짜 문자열의 요일 일련 번호 [종류] 1 : 일요일을 1로 시작 2 : 월요일을 1로 시작 3 : 월요일을 0으로 시작
DAYS	종료 날짜, 시작 날짜	일 수 계산 (종료 날짜-시작 날짜)
DATEVALUE	날짜 문자열	문자열 날짜에 해당하는 일련 번호
EDATE	시작 날짜,개월 수	시작 날짜에 개월 수를 더한 날의 일련 번호
EMONTH	시작 날짜,개월 수	시작 날짜에 개월 수를 더한 달의 마지막 날짜의 일련 번호
NETWORKDAYS	시작 날짜,끝 날짜,[휴일]	휴일을 제외한 시작 날짜와 끝 날짜 사이 업무일
WEEKNUM	날짜,요일을 결정할 숫자	특정 날짜의 주 번호를 반환
WORKDAY	시작 날짜,날짜 수,[휴일]	시작 날짜에 날짜 수에서도 평일 수만을 적용한 날짜의 일련 번호

 

• 텍스트 함수

연산자	()	기능
LEN	텍스트	문자열의 길이
LEFT	텍스트,문자수	왼쪽에서부터 지정한 문자수만큼 추출
MID	텍스트,시작위치,문자수	시작위치에서 지정한 문자수만큼 추출
RIGHT	텍스트,문자수	오른쪽에서부터 지정한 문자수만큼 추출
LOWER	텍스트	소문자로 변환
UPPER	텍스트	대문자로 변환
PROPER	텍스트	첫문자만 대문자, 나머지 소문자 변환
TRIM	텍스트	단어 간 공백을 1개만 두고 나머지 삭제
REPLACE	텍스트1,시작위치,문자수,텍스트2	텍스트1에서 시작위치로부터 해당 문자수만큼 텍스트 2로 바꿈
SUBSTITUTE	텍스트1,텍스트2,텍스트3	텍스트1에서 텍스트2를 텍스트3으로 바꿈
VALUE	텍스트	텍스트를 숫자로 변환함
TEXT	숫자값,표시형식	숫자 값을 표시 형식을 지정해 텍스트로 변환함
FIXED	수치,소수점 이하 자릿수,콤마 표시 여부	수를 반올림하여 텍스트로 만듦 [콤마 표시 여부] TRUE : 콤마 표시하지 않음 FALSE/생략 : 콤마를 천 단위마다 표시
CONCATENATE	텍스트1,텍스트2,텍스트3	여러 텍스트를 한 텍스트로 합침
EXACT	텍스트1,텍스트2	대/소문자를 구분해 텍스트값에서 다른 텍스트가 있으면 FALSE, 같은 텍스트이면 TRUE
REPT	반복할 텍스트, 반복할 횟수	횟수만큼 텍스트를 반복
FIND	찾을 텍스트, 찾을 텍스트를 포함한 텍스트	대/소문자를 구분해 텍스트가 어느 위치에 있는지 문자 단위로 찾음
FINDB	찾을 텍스트, 찾을 텍스트를 포함한 텍스트	FIND를 바이트 단위로 찾음
SEARCH	찾을 텍스트, 찾을 텍스트를 포함한 텍스트	대/소문자 구분 없이 텍스트가 어느 위치에 있는지 문자 단위로 찾음
SEARCHB	찾을 텍스트, 찾을 텍스트를 포함한 텍스트	SEARCH를 바이트 단위로 찾음

 

• 논리 함수

연산자	()	기능
IF	조건식,값1,값2	참이면 값1, 거짓이면 값2
AND	조건1,조건2,..	모든 조건이 참이면 TRUE, 나머지는 FALSE
OR	조건1,조건2,..	모든 조건이 거짓이면 FALSE, 나머지는 TRUE
TRUE
FALSE
NOT	논리식
FERROR	수식,값	수식에서 오류가 발생할 경우 지정한 값을 반환

 

• 재무 함수

연산자	()	기능
FV	이율,납입횟수,정기납입액,[현재가치][납입시점]	정기적으로 일정 기간 동안 은행에 적립할 때의 미래가치 예금/적금 [납입시점] 0/생략 : 월말 / 1 : 월초
PMT	이율,불입총횟수,현재가치,[미래가치],[납입시점]	대출금(투자총액)에서 정기적으로 납입되는 금액 대출 원금 상환/돈 모으기
PV	이율,납입횟수,정기납입액,[현재가치][납입시점]	투자액의 현재가치 현재가치
NPV	할인율,값1,값2..	투자액과 그 후 일정 수입이 보장될 때 현재가치
AMORDEGRC	비용,자산취득일,첫째 회계 기간의 마지막 날짜,잔존 가치,기간,감가 상각률,날짜 체계	매 회계 기간의 감가 상각액 (비용을 지출해서 구입한, 장기간 사용하는 자산의 비용화)
AMORLINC	비용,자산취득일,첫째 회계 기간의 마지막 날짜,잔존 가치,기간,감가 상각률,날짜 체계	매 회계 기간의 감가 상각액 (비용을 지출해서 구입한, 장기간 사용하는 자산의 비용화)
DB	취득가치,잔존가치,내용연수,기간,첫 해의 개월 수	정률법을 사용해 특정 기간 동안의 감가 상각액
DDB	취득가치,잔존가치,내용연수,잔액이 감소하는 비율	이중 체감법에 의해 첫 해의 감가 상각액, 연차가 경과할수록 상각액이 작아짐
SLN	비용,잔존가치,내용연수	매년 동일 액수를 감가 상각을 구함(정액법)
SYD	취득가치,잔존가치,내용연수,기간	마지막 연차의 감가 상각액을 구한 후 계속 더해가며 이전 연차의 감가 상각을 구함(연수합계법)

 *[참고] 감가 상각 : 구입한 자산이 시간이 지날수록 가치가 떨어질 때, 자신의 가치 하락을 기록하는 방법
1. 정액법 : 고정자산이 내용년수만큼 균일하게 감가 상각
2. 정률법 : 시간이 갈수록 감가상각비가 줄어드는 경향
3. 이중 체감법 : 정률법, 그러나 상각률을 간편하게 정액법에 의한 상각률의 2배로 적용
4. 연수 합계법 : 초기에 상각을 많이 하고, 매년 상각비가 줄어드는 가속 상각법

• 정보 함수

연산자	()	기능
ISBLANK	검사 대상	공백 셀인지 조사
ISERROR	검사 대상	에러 값인지 조사
CELL	정보유형텍스트,[참조주소]	셀 서식, 위치, 내용 정보 반환
ISERR	값	#N/A를 제외한 오류라면 TRUE
ISODD	숫자	홀수이면 TRUE
ISEVEN	숫자	짝수이면 TRUE
ISLOGICAL	값	논리값이면 TRUE
ISTEXT	값	텍스트이면 TRUE
ISNONTEXT	값	텍스트가 아니면 TRUE
ISNUMBER	값	숫자면 TRUE
TYPE	값	데이터 형식을 나타냄. 1 : 숫자, 2 : 텍스트, 4 : 논리 값, 16 : 오류 값

 

• 배열 수식

배열 수식의 원리
나열	AND : *	OR : +
만족	만족 : TRUE (1)	불만족 : FALSE (0)
수식 입력	Ctrl+Shift+Enter
함수	기능
SUM	합계
SUM,IF	합계
SUM	개수
SUM,IF	개수
COUNT,IF	개수
AVERAGE,IF	평균
MAX,IF	최대값
LARGE,IF	N번째 큰 값
INDEX,MATCH,MAX	행(열)에 값을 찾을 때

 

• 사용자 정의 함수
  • 함수 입력 
    • 1. Alt+F11
    • 2. [삽입]-[모듈]
    • 3. 합수 정의
    • 4. 시트에 함수 적용

 

분석 작업

1. 피벗 테이블
   1. 피벗 테이블 단축키
      1. 피벗 차트 보고서 만들기 : F11
      2. 피벗 차트 만들기 : Alt + F1
      3. 피벗 테이블 항목 숨기기 : Ctrl + -
      4. 피벗 테이블 그룹화 해제/설정 : Alt + Shift + ←/→
      5. 피벗 테이블 전체 선택 : Ctrl + A
   2. 외부 쿼리를 피벗 테이블로 가져오려면 [데이터 필터], [정렬 순서] 에서 설정 없이 [다음]을 클릭하고, [데이터 가져오기] 에서 바로 [피벗 테이블 보고서] 를 선택해야 한다.
   3. 외부 테이블을 피벗 테이블로 가져오려면 [피벗 테이블 만들기]에서 '데이터 모델에 이 데이터 추가' 를 체크해야 한다.
   4. [피벗 테이블 도구]는 상단 메뉴탭에서 확인할 수 있다. 피벗 테이블 스타일 등을 선택할 수 있다.
   5. [피벗 테이블 옵션]은 테이블 안에 셀 포인터가 있을 때 우클릭 시 메뉴가 확인된다.
   6. 필드 행에 대해 그룹은 필드 선택 후 우클릭 시 [그룹] 메뉴에서 편집한다.
   7. 피벗 테이블에 필터를 사용 시, 피벗 테이블 왼쪽 상단에서 2칸 위에 필터 필드가 생성된다.
   8. [값 필드 설정]은 필드를 더블클릭하면 해당 필드에 포함된 데이터에 대한 표시 형식을 편집할 수 있다.
2. 데이터 유효성 검사
   1. [데이터]-[데이터 도구] 그룹에서 [데이터 유효성 검사]를 선택한다.
   2. 제한대상이 목록일 때, 여러 목록은 '원본'에 콤마 로 구분한다.
3. 중복된 항목 제거
   1. [데이터]-[데이터 도구] 그룹에서 [중복된 항목 제거]를 선택한다. 이 때, 필드명 좌측 상단에 셀 포인터가 있으면 자동으로 데이터가 선택된다.
4. 데이터 표
   1. 가상분석 : 하나 또는 2개의 X, Y 에 대해서, 특정 수식이 지정되어 있을 때 데이터 표를 채워 준다.
      1. 감가상각액 = (취득원가 - 잔존가액) × (내용연수의 역순 / 내용연수의 합계) : 자산을 이용하는 기간 즉, 내용연수에 걸쳐 배분할 총비용. 자산의 취득원가에서 잔존가치를 차감한 금액. 
   2. 수식을 데이터 표의 좌측 상단에 입력하고 데이터 표 영역을 전부 선택한 후 [데이터]-[예측]-[가상분석]-[데이터 표]을 선택한다. 행, 열에 해당하는 X, Y를 지정해 준다.
5. 목표값 찾기
   1. 가상분석 : 변수 1개와 특정 수식이 있을 때, 변수의 값을 찾아 준다.
   2. 목표값에 해당하는 셀을 선택한 후 [데이터]-[예측]-[가상분석]-[목표값 찾기]을 선택한다. 수식과 찾으려는 값, 변수 셀을 차례로 지정해 준다.
6. 통합
   1. 정해진 포맷의 (정형화된) 데이터들을 참조하여, 동일한 데이터 포맷에 대해 통계값을 얻을 수 있다.
   2. 정형화된 데이터들을 추가해 참조 영역을 지정해 준다.
7. 정렬
   1. [데이터]-[정렬 및 필터]-[정렬]
   2. 셀 값에 대해 정렬을 새로 만들고 싶으면, 목록 항목에 텍스트를 입력 후 엔터로 구분하여 추가한다.
   3. 오름차순 : 낮은 숫자 > 높은 숫자, ㄱ > ㅎ, A > Z
8. 부분합
   1. 부분합은 먼저 추가한 것이 아래에 위치한다. 2개 이상의 부분합 중 가장 아래에 있는 것이 가장 먼저 추가한 것이다.
   2. 정렬을 언제나 먼저 시행해야 한다. 그렇지 않으면 중복 그룹에 대해 전부 부분합이 계산된다.
   3. '요약'으로 계산되는 부분합이 있다면, 찾기 및 바꾸기를 이용해 이름을 바꿔준다.
9. 시나리오
   1. 가상분석 : 정해진 변수가 특정값으로 변했을 때의 계산값을 예측할 수 있다.
   2. 변수 필드와 셀, 결과 필드와 셀을 선택 후 [수식]-[정의된 이름]-[선택영역에서 만들기] 또는 [이름 정의]로 '셀 이름 정의'를 한다. 이후 시나리오 보고서에서 지정한 이름으로 정리되어 나온다.
      1. 셀 이름 규칙 : 이름은 255자까지 지정할 수 있고, 공백을 포함할 수 없고, 문자나 밑줄이나 \ 중 하나로 시작해야 하고, 셀 주소 형식이 될 수 없다.
   3. 계산값이 여러 개일 때, 결과 셀에 여러 셀을 입력하면 된다.

 

기타 작업

1.  차트
   1. 글꼴 서식 : [홈]-[글꼴]
   2. 도형 서식 : [차트도구]-[서식]-[도형 스타일]
2. 매크로 : 반복적인 작업을 바로가기나 명령 단추로 자동화하여 한 번에 실행하는 기능
   1. 매크로 이름 : 공백과 기호는 포함될 수 없고, 첫글자는 문자여야 한다.
   2. 매크로 바로가기 : 영문 대문자 조합만 가능하다. 대문자를 입력하면 저절로 Shift 와 조합된다.
   3. 매크로 단추를 그릴 때 셀 꼭짓점에서 다른 셀 중간 정도에서 Alt 키를 누르면 셀 눈금에 맞추어 그릴 수 있다.
   4. 매크로 종료 전에 범위가 해제되거나 해제되지 않아도 감점되지 않는다.
3. 프로시저 : 매크로와 같이 특정 작업을 처리하는 구문
   1. VBE (Visual Basic Editor) 바로가기 단축키 : Alt + F11
      
      1. VBE 종료 단축키 : Alt + F11 / Alt + Q
      2. 매크로 목록 표시 : Alt + F8
   2. 프로그램 탐색기 (단축키 : Ctrl + R) : 열린 통합 문서 목록과 현재 문서 내 시트 목록 등 작업을 구성하는 개체를 계층적으로 표현
   3. 속성 창 (단축키 : F4) : 선택한 개체의 속성 목록과 각 속성의 현재 값이 나타나며, 탐색기에서 다른 개체를 선택해 보면 속성 창 내 속성 목록이 변경
      1. 사전순 : 알파벳 순 속성
      2. 항목별 : 기능별 속성
   4. 코드 창 (단축키 : F7) : VBA 코드를 나타내는 곳. 코드 창에서 매크로를 편집하고 새로 만들 수 있다. 
      
      1. 개체 목록 : 모듈 시트에서는 (일반)항목이, 컨트롤 개체에서는 개체 목록이 보임
      2. 프로시저 목록 : 선택한 개체 속에 있는 프로시저 (매크로 목록)을 표시.-
      3. 코드 보기 방식 : 프로시저 보기 / 전체 모듈 보기
   5. VBA 구문
      1. 프로시저의 형태
         1.  Sub 매크로이름(프로시저명)
         2.         처리구문
         3. End Sub
      2. 개체 : 어떤 작업이나 처리를 할 때 그 대상이 되는 독립적 성질을 갖는 사물
         1. 예) 통합 문서, 시트, 셀, 차트 등
         2. 계층 구조이므로 개체 내 다른 개체가 포함된다.
      3. 컬렉션 : 개체의 집합
         1. 예) 하나의 통합문서 내 여러 시트 -> 개체 컬렉션
      4. 메서드 (Method) : 개체가 수행할 수 있는 행동
         1. 예) Sheet1 개체를 선택하는 코드 : Sheets("Sheet1").Select
      5. 속성 (Property) : 개체가 가지는 고유 특성 (크기, 색상, 위치 등)
         1. 형식 : 개체.속성명 = 속성값
         2. 예) 현재 셀에 숫자 5를 입력하는 코드 : ActiveCell.Value="5"
      6. 이벤트 (Events) : 조작을 위해 하는 마우스 클릭, 키보드 누르기, 특정 단추 누르기 등
         1. 발생 순서
            1. Load : 개체가 메모리에 올라온다
            2. Initialize : 개체가 보이기 전 발생
            3. Activate : 개체 활성화 전 발생
            4. Show : 폼이 보인다
            5. Deactivate : 개체가 비활성화되기 전 발생
            6. QueryClose : 폼이 닫히기 전 실행
            7. Terminate : 폼이 메모리에서 제거되기 전 실행
            8. Unload : 폼이 메모리에서 제거됨
         2. 종류
            1. 속성 : 개체의 특성
               1. 값을 할당, 참조 : 메소드보다 종류가 많다
            2. 메서드 : 개체에 작용하는 결과
            3. 이벤트 : 개체 이벤트에 작용
            4. 함수
               1. 활성화 : Private Sub object_Activate()
               2. 비활성화 : Private Sub object_Deactivate()
               3. 셀 내용 변경 : Private Sub object_Change(ByVal Target As Range)
               4. 시트 더블 클릭 : Private Sub object_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
               5. 재계산 : Private Sub object_Calculate()
               6. 통합 문서를 닫기 전 : Private Sub object_BeforeClose(Cancel As Boolean)
               7. 저장하기 전 : Private Sub object_BeforeSave(ByVal SavsAsUI As Boolean, Cancel As Boolean)
         3. 변수 : 특정한 값을 지정할 수 있도록 할당된 기억 공간
            1. 전역 변수 : 다른 모듈에서도 사용할 수 있는 변수
               1. 선언 : Public 변수명
            2. 지역 변수 : 특정 모듈에서만 사용할 수 있는 변수
               1. 선언 : Dim 변수명
                  1. 예) Dim i As Integer : i를 정수형 변수로 사용
               2. 데이터 유형과 저장 크기 범위

* 자주 쓰는 함수

1. With ~ End With : ~의 반복을 줄여줌
2. ListIndex : 목록에서 선택한 항목을 숫자로 변환해 줌

 

• 사용자 지정 서식
  • 서식 지정 : '양수;음수;0;문자' 값 형식
    • 음수 서식에 음수 부호를 따로 지정하지 않으면 표시되지 않는다.
    • 세미콜론으로 분리하지 않으면 원본 데이터 그대로 표시된다.
    • 세미클론으로 분리했으나 형식을 지정하지 않은 경우 원본 데이터가 표시되지 않는다.
    • %는 숫자에 곱하기 100을 한 뒤, '%'을 붙여서 표시한다.
  • 대괄호 사용하여 서식 지정 : '[색깔][조건]서식' 형식으로 작성한다. 이 때 색깔, 조건은 순서가 바뀌어도 된다.
  • 숫자와 문자에 관한 코드

서식코드	의미
#	숫자 하나 또는 여러 개를 의미, 유효 자릿수만 표시 (#으로 표시한 서식의 길이보다 입력한 값이 짧은 경우에는 값의 길이만큼 표시됨)
0	숫자 하나를 의미, 0으로 표시한 서식의 길이보다 입력한 값이 짧은 경우 나머지는 0으로 채움
?	숫자 하나를 의미, ?로 표시한 서식의 길이보다 입력한 값이 짧으면 나머지는 공백으로 채움, 소수점을 고정된 위치에 표시할 때 사용할 수 있다.
;	섹션 구분 서식 양수;음수;0;문자서식
,	천 단위 표시용 쉼표(콤마)
_(밑줄)	숫자에서 공백을 표시할 때 사용, 밑줄 다음의 문자와 같은 너비의 공백을 만들 때 사용(예: “_)”을 입력하면 “)”의 문자크기만큼 공백이 생김)
@	문자를 그대로 출력, @을 2번 반복하면 입력된 문자를 2번 반복해서 출력
*	* 뒤의 문자를 셀 너비에 맞게 반복
[](대괄호)	조건이나 글꼴 색을 지정할 때 대괄호 안에 입력

• 날짜에 관한 코드

서식코드	의미	서식코드	의미
yy	21	d	1~31
yyyy	2021	dd	01~31
m	1~12	ddd	Sun~Sat
mm	01~12	dddd	Sunday~Saturday
mmm	Jan~Dec	aaa	일~월
mmmm	January~December	aaaa	일요일~월요일

• 날짜와 시간 형식 지정
  • Format(Date, "yyyy-mmmm-dd dddd") : 2023-February-01 Monday
  • Format(Time, "hh:nn:ss ampm") : 09:05:55 오전
  • Format(Time, "hh:nn:ss am/pm") : 09:05:55 am
• 셀을 참조하지 않고 직접 입력하기
  • Cmb도시명.AddItem "서울"

 
 
* 참고 : 단축키
https://www.oppadu.com/%ec%97%91%ec%85%80-%eb%8b%a8%ec%b6%95%ed%82%a4-%eb%aa%a8%ec%9d%8c/

엑셀 단축키 모음 - 자주쓰는 핵심 단축키 TOP 50 - 오빠두엑셀

 
*출처 : 시나공 이기적 컴퓨터활용능력 1급 실기