컴활 2급 필기 요약 - 스프레드시트 일반 (Excel)

20문항 | 핵심: 함수, 서식, 데이터 관리, 차트, 매크로, 피벗테이블

2-1 기본 조작 및 단축키

단축키	기능
[Ctrl]+[Home]	A1 셀로 이동
[Ctrl]+[End]	마지막으로 사용된 셀로 이동
[Home]	현재 행의 첫 번째 열(A열)로 이동
[Alt]+[↓]	목록에서 항목 선택 (드롭다운)
[Ctrl]+[Enter]	선택된 여러 셀에 동시 입력
[Shift]+[Enter]	위쪽 셀로 이동
[F4]	절대참조/상대참조 전환
[F11]	새 시트에 차트 삽입
[Alt]+[F11]	VBE(Visual Basic Editor) 열기
[Ctrl]+[Shift]+[Enter]	배열 수식 입력

 

★ [Ctrl]+[Home]=A1 / [Ctrl]+[End]=마지막사용셀 / [Home]=현재행첫셀

2-2 셀 서식

표시형식 코드

코드	설명
#,##0	천 단위 구분, 빈 자리 0 표시
#.##	소수점, 빈 자리 공백 (24678 → 24678 그대로)
0%	퍼센트, 소수점 없음 (0.457 → 46%)
0.0%	퍼센트, 소수점 1자리 (0.457 → 45.7%)
mm-dd	월-일 (숫자 형태, 예: 04-05)
mmm-dd	월약어-일 (예: Apr-05)
yyyy-mm-dd	4자리연도-월-일
@	텍스트 형식
#,##0;#,##0	양수·음수 모두 천 단위 양수 형태로 표시

 

★ #=유효숫자(빈자리 공백) / 0=빈자리도 0 표시

📌 "mm-dd" 서식에 2021-04-05 입력 시 → 04-05 표시 (Apr-04 아님!)

📌 0% 서식: 0.457 → 46% / 0.0% 서식: 0.457 → 45.7%

맞춤 탭

옵션	설명
자동 줄 바꿈	셀 너비 초과 시 자동으로 줄 나눔
셀에 맞춤	글자 크기를 자동으로 줄여 셀 안에 맞춤
셀 병합	선택 영역 병합, 왼쪽 상단 데이터만 유지 (나머지 삭제)
방향(각도)	텍스트 회전 각도 설정 (-90°~90°)

 

★ 셀 병합 시 가장 왼쪽 상단 데이터만 남고 나머지 삭제됨

조건부 서식

항목	설명
기본 개념	조건에 맞는 셀에 자동으로 서식 적용
수식 규칙	=수식 입력, 결과가 TRUE인 셀에 서식 적용
행 전체 서식	열을 $ 고정: =$B2+$C2<=170 → 조건에 맞는 행 전체에 적용
짝수 열 서식	=MOD(COLUMN(A$1),2)=0 → B, D열(짝수열)에 서식 적용
COLUMN(A$1)	행 번호 고정($1), 열은 상대적으로 변경됨

 

★ 행 전체에 조건부서식: =$B2+$C2<=170 (열 고정 $, 행은 상대참조)

★ MOD(COLUMN(A$1),2)=0 → B,D열(짝수열)에 서식 / =1 → A,C열(홀수열)

2-3 핵심 함수

논리 함수

함수	설명
IF(조건, 참, 거짓)	조건 분기
AND(조건1, 조건2)	모두 참일 때 TRUE
OR(조건1, 조건2)	하나라도 참이면 TRUE
NOT(조건)	논리 반전
IFERROR(식, 오류시값)	오류 발생 시 대체값

 

수학/통계 함수

함수	설명
SUM(범위)	합계
SUMIF(범위,조건,합계범위)	조건에 맞는 합계
SUMIFS(합계범위,범위1,조건1,...)	다중 조건 합계
AVERAGE(범위)	평균
COUNT(범위)	숫자 셀 개수
COUNTA(범위)	비어있지 않은 셀 개수
COUNTIF(범위,조건)	조건에 맞는 셀 개수
COUNTIFS(범위1,조건1,...)	다중 조건 개수
MAX/MIN(범위)	최대값/최소값
LARGE(범위,k)/SMALL(범위,k)	k번째 큰값/작은값
RANK.EQ(수,범위,방향)	순위 (동일값 동일순위)
MOD(수,제수)	나머지
QUOTIENT(수,제수)	몫(정수 부분)
ROUND(수,자릿수)	반올림
ROUNDUP/ROUNDDOWN	올림/내림
INT(수)	정수 변환(내림)
FIXED(수,자릿수,쉼표)	반올림 후 텍스트 변환
SUBTOTAL(함수번호,범위)	필터 상태에서 집계

 

★ COUNTIF(범위, ">="&AVERAGE(범위)) → 비교연산자는 따옴표 안, 셀참조는 & 연결!

★ RANK.EQ(D4, $D$2:$D$4) → 범위는 절대참조로 고정 필수

날짜/시간 함수

함수	설명
TODAY()	오늘 날짜
NOW()	현재 날짜+시간
DATE(년,월,일)	날짜 생성
YEAR/MONTH/DAY(날짜)	연도/월/일 추출
DAYS(끝날짜,시작날짜)	두 날짜 사이 일수 (끝-시작)
EOMONTH(날짜,개월)	지정 개월 후 월의 마지막 날
DATEDIF(시작,끝,"단위")	Y:년차이, M:월차이, D:일차이
NETWORKDAYS(시작,끝)	근무일수(토일 제외)

 

★ DAYS("2020-11-1","2020-10-1") = 31 (끝날짜 - 시작날짜 순서 주의)

텍스트 함수

함수	설명
LEFT(텍스트,n)	왼쪽에서 n자
RIGHT(텍스트,n)	오른쪽에서 n자
MID(텍스트,시작,n)	중간에서 n자
LEN(텍스트)	문자열 길이
FIND(찾을,텍스트)	위치 반환 (대소문자 구분)
SEARCH(찾을,텍스트)	위치 반환 (대소문자 무관)
REPLACE(텍스트,시작,길이,새텍스트)	위치 기반 교체
SUBSTITUTE(텍스트,찾을,바꿀)	특정 문자 교체
REPT(텍스트,횟수)	텍스트 n번 반복
TRIM(텍스트)	앞뒤 공백 제거
TEXT(수,서식)	숫자를 텍스트로 변환
VALUE(텍스트)	텍스트를 숫자로 변환

 

★ REPT(IF(B2>70%,"■","□"), QUOTIENT(B2,10%)) → 성취도 막대 표시 공식

📌 SEARCH는 대소문자 구분 없음 / FIND는 대소문자 구분

참조/탐색 함수

함수	설명
VLOOKUP(값,범위,열번호,방식)	세로 탐색 / FALSE=정확히, TRUE=근사치
HLOOKUP(값,범위,행번호,방식)	가로 탐색
INDEX(범위,행,열)	교차 위치 값 반환
MATCH(값,범위,방식)	위치 번호 반환 / 0=정확히
CHOOSE(번호,값1,...)	번호에 해당하는 값
ROW(셀)/ROWS(범위)	행번호/행개수
COLUMN(셀)/COLUMNS(범위)	열번호/열개수
OFFSET(기준,행,열,높이,너비)	이동한 범위 반환

 

★ VLOOKUP: 찾는값이 반드시 첫번째 열에! / FALSE=정확히 일치

★ =YEAR(TODAY())-YEAR(VLOOKUP(C11,C4:E10,2,0)) → 근속년수 계산 패턴

2-4 데이터 관리

정렬 및 필터

기능	설명
정렬	다중 기준 정렬 가능 / 셀색·아이콘·글꼴색으로도 정렬 가능
자동 필터	드롭다운으로 조건 선택, 필터된 데이터만 표시
고급 필터	복잡한 조건, 결과를 다른 위치에 복사 가능
사용자 지정 목록	정렬 기준으로 활용 / 기본 제공 목록은 수정 불가

 

부분합

항목	설명
사용 순서	정렬 → 데이터 탭 → 부분합
추가 부분합	"새로운 값으로 대치" 체크 해제 후 추가
제거	"모두 제거" 버튼 클릭

 

★ 부분합 추가 시 → "새로운 값으로 대치" 반드시 해제! (자주 출제)

가상분석 도구

도구	특징
목표값 찾기	결과값 설정 후 입력값 역산 / 변수 1개
데이터 표	1~2개 변수에 따른 결과 표 / 부분 수정·삭제 불가
시나리오	여러 가상 상황 값 집합 정의·비교 / 변수 여러 개 가능

 

★ 데이터 표 = 일부 수정·삭제 불가 (배열 형태로 저장)

★ 목표값 찾기 = 변수 1개 / 시나리오 = 변수 여러 개 / 데이터 표 = 1~2개

이름 정의

항목	설명
이름 규칙	첫 글자: 문자 또는 밑줄(_) / 슬래시(/) 불가 / 공백 불가
최대 길이	255자
대소문자	구분 없음
정의 방법	수식 탭 → 이름 정의 / 이름 상자 직접 입력

 

★ 이름 첫 글자: 문자 or 밑줄(_)만 / 슬래시(/) 불가 / 공백 불가

2-5 피벗 테이블

항목	설명
목적	대용량 데이터 요약·분석 / 행·열·값·필터 영역
그룹화	날짜를 월/분기/연도로 그룹화
슬라이서	필터 조건을 시각적 버튼으로 제공
업데이트	원본 변경 시 새로 고침 필요 (자동 업데이트 안됨)

 

★ 피벗 테이블 원본 변경 시 → 새로 고침 필요 (자동으로 업데이트 되지 않음)

2-6 차트

차트 종류	특징
세로/가로 막대형	항목 비교, 여러 계열 가능
꺾은선형	시간에 따른 추이
원형	하나의 계열만 표시 (비율·비중)
분산형(XY)	두 변수 간 관계, 3D 불가, 기본 5종
방사형	여러 축의 균형 비교
도넛형	원형과 유사, 여러 계열 가능
영역형	시간 흐름에 따른 변화 크기

 

★ 원형 차트 = 하나의 계열만 표시 / 분산형 = 3D 불가, 기본 5종

서식 옵션	설명
계열 겹치기	음수=간격 벌어짐 / 양수=겹쳐짐
축 서식	최소·최대값, 단위(간격) 설정
차트 제목 연결	제목 선택→수식입력줄에 ='Sheet1'!$A$1 입력
[F11]	새 시트에 차트 삽입

 

★ 차트 제목을 셀에 연결: 수식입력줄에 ='Sheet1'!$A$1 입력

2-7 인쇄 및 페이지 설정

항목	설명
페이지 나누기	파선=자동 / 실선=수동 / 행높이·열너비 변경 시 자동 위치 이동 가능
인쇄 영역	특정 범위만 인쇄
반복 행/열	매 페이지마다 제목 행/열 반복
인쇄 배율	%로 축소/확대 또는 페이지 맞춤

 

★ 페이지 나누기: 파선=자동, 실선=수동 / 행높이·열너비 변경 시 자동 나누기 위치 변경됨

2-8 매크로 기초

항목	설명
매크로 기록	개발도구 탭 → 매크로 기록 / [Alt]+[F8]
바로가기 키	Ctrl+소문자 또는 Ctrl+Shift+대문자 / 생성 후 옵션에서 변경 가능
저장 위치	현재 통합문서 / 개인용 매크로 통합문서 / 새 통합문서
XLSM	매크로 포함 통합문서 형식 (.xlsm)

 

★ 매크로 바로가기 키: Ctrl+소문자 / 생성 후에도 옵션에서 변경 가능

채우기 핸들

조작	결과
기본 드래그(숫자)	1씩 증가
[Ctrl]+드래그(숫자)	동일 값 복사 (증가 없음)
[Ctrl]+드래그(문자+숫자)	숫자 1씩 증가
두 셀 선택 후 드래그	두 값의 차이만큼 증가
사용자 정의 목록	목록 첫 항목 입력 후 [Ctrl]+드래그 → 목록 순서대로

 

★ [Ctrl]+숫자셀 드래그 → 복사 (증가 없음) / 일반 드래그 → 1씩 증가