Ch1. 컴퓨터활용능력 1급 — 스프레드시트 핵심 함수 완전 정복

컴퓨터활용능력 시험 개요

**컴퓨터활용능력(컴활)**은 대한상공회의소 주관의 국가공인 자격증으로, 스프레드시트(엑셀)와 데이터베이스 소프트웨어 활용 능력을 검정합니다. 취업·공무원 가산점 등에서 폭넓게 인정받아 매년 수십만 명이 응시하는 인기 자격증입니다.

급수별 시험 구조

구분	1급	2급
이론 시험	컴퓨터 일반(20문항) + 스프레드시트(20문항) + 데이터베이스(20문항) / 60문항	컴퓨터 일반(20문항) + 스프레드시트(20문항) / 40문항
실기 시험	스프레드시트 + 데이터베이스 (각 45분)	스프레드시트 (40분)
합격 기준	이론 60점 이상, 실기 각 70점 이상	이론 60점 이상, 실기 70점 이상
시험 과목	MS Excel + MS Access	MS Excel

1급 실기 시험 스프레드시트 주요 출제 영역

함수(Function) — 다양한 함수의 중첩 활용
서식 — 조건부 서식, 사용자 지정 서식
데이터 관리 — 정렬, 필터, 부분합
차트 — 종류 변경, 데이터 범위 편집
매크로 — 기록, 편집, 실행

이번 강에서는 **함수(Function)**를 집중적으로 다룹니다.

참조 함수 (Lookup & Reference)

VLOOKUP — 수직 방향 조회

=VLOOKUP(찾는값, 범위, 열번호, [일치유형])

인수:
- 찾는값: 조회할 기준값
- 범위: 검색 대상 표 (첫 번째 열에 기준값 위치)
- 열번호: 반환할 열의 번호 (1부터 시작)
- 일치유형: FALSE(0) = 정확히 일치 / TRUE(1) = 유사 일치

예시:
=VLOOKUP(A2, $E$2:$G$10, 2, FALSE)
→ A2 값을 E~G 범위의 첫 열에서 찾아 두 번째 열 값 반환

VLOOKUP 주의사항:

기준 열이 반드시 범위의 첫 번째 열이어야 함
열 번호를 잘못 입력 시 오류 없이 잘못된 값 반환
왼쪽 방향 검색 불가 (이 경우 INDEX/MATCH 사용)

HLOOKUP — 수평 방향 조회

=HLOOKUP(찾는값, 범위, 행번호, [일치유형])

예시:
=HLOOKUP("판매량", $B$1:$F$3, 2, FALSE)
→ "판매량"을 첫 행에서 찾아 두 번째 행 값 반환

INDEX / MATCH — 강력한 조합 참조

VLOOKUP의 한계(왼쪽 검색 불가, 열 번호 고정 문제)를 극복하는 조합입니다.

INDEX 함수:
=INDEX(반환범위, 행번호, [열번호])
→ 지정한 행·열 위치의 값 반환

MATCH 함수:
=MATCH(찾는값, 검색범위, [일치유형])
→ 찾는값의 위치(순번) 반환
  일치유형: 0 = 정확히 / 1 = 이하 최대 / -1 = 이상 최소

INDEX + MATCH 조합:
=INDEX(C2:C100, MATCH(A2, B2:B100, 0))
→ B열에서 A2 값을 찾아, 그 위치의 C열 값 반환

VLOOKUP 대체 예시:
=VLOOKUP(A2, B:D, 3, FALSE)
=INDEX(D2:D100, MATCH(A2, B2:B100, 0))   ← 동일한 결과

INDEX/MATCH 장점:

왼쪽·위쪽 방향 검색 가능
열 삽입·삭제 시 자동 적응 (VLOOKUP은 열 번호 수동 수정 필요)
속도: 대용량 데이터에서 VLOOKUP보다 빠름

논리 함수 (Logical Functions)

IF — 조건 분기

=IF(조건, 참일_때_값, 거짓일_때_값)

예시:
=IF(B2>=60, "합격", "불합격")
=IF(C2="서울", D2*0.9, D2)   ← 서울이면 10% 할인

중첩 IF — 다중 조건 처리

=IF(B2>=90, "A",
  IF(B2>=80, "B",
    IF(B2>=70, "C",
      IF(B2>=60, "D", "F"))))

주의: 중첩 IF는 최대 7단계까지 가능하지만
      가독성이 떨어지므로 IFS 함수 사용 권장

IFS — 다중 조건 (IF 중첩 대체)

=IFS(조건1, 값1, 조건2, 값2, ..., TRUE, 기본값)

예시:
=IFS(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", TRUE, "F")

마지막에 TRUE, "기본값"을 추가해야 누락 조건 처리 가능

AND / OR / NOT

AND: 모든 조건이 참이어야 TRUE
=AND(B2>=60, C2="출석")

OR: 하나 이상 조건이 참이면 TRUE
=OR(B2="서울", B2="경기", B2="인천")

NOT: 조건 반전
=NOT(B2="결석")

IF와 조합:
=IF(AND(B2>=60, C2="출석"), "합격", "불합격")

IFERROR — 오류 처리

=IFERROR(수식, 오류_대체값)

예시:
=IFERROR(VLOOKUP(A2, $D$2:$F$10, 2, FALSE), "미등록")
→ VLOOKUP에서 #N/A 오류 발생 시 "미등록" 표시

=IFERROR(B2/C2, 0)
→ C2가 0이어서 #DIV/0! 오류 발생 시 0 표시

통계 함수 (Statistical Functions)

기본 통계

=COUNT(범위)      ← 숫자 개수
=COUNTA(범위)     ← 비어있지 않은 셀 개수
=COUNTBLANK(범위) ← 빈 셀 개수

=SUM(범위)        ← 합계
=AVERAGE(범위)    ← 평균
=MAX(범위)        ← 최대값
=MIN(범위)        ← 최소값
=MEDIAN(범위)     ← 중앙값
=MODE(범위)       ← 최빈값
=LARGE(범위, k)   ← k번째로 큰 값
=SMALL(범위, k)   ← k번째로 작은 값

조건부 집계 함수 (자주 출제)

COUNTIF — 조건에 맞는 개수:
=COUNTIF(범위, 조건)
=COUNTIF(B2:B100, "서울")
=COUNTIF(C2:C100, ">=60")

COUNTIFS — 다중 조건 개수:
=COUNTIFS(범위1, 조건1, 범위2, 조건2)
=COUNTIFS(B2:B100, "서울", C2:C100, ">=60")

SUMIF — 조건에 맞는 합계:
=SUMIF(조건범위, 조건, 합계범위)
=SUMIF(B2:B100, "서울", D2:D100)

SUMIFS — 다중 조건 합계:
=SUMIFS(합계범위, 조건범위1, 조건1, 조건범위2, 조건2)
=SUMIFS(D2:D100, B2:B100, "서울", C2:C100, ">=60")

AVERAGEIF / AVERAGEIFS — 조건부 평균 (동일 구조)

핵심 차이: SUMIF는 (조건범위, 조건, 합계범위) 순서 / SUMIFS는 (합계범위, 조건범위, 조건) 순서 — 자주 헷갈리는 포인트!

날짜·시간 함수 (Date & Time Functions)

=TODAY()           ← 오늘 날짜 (시스템 날짜, 재계산 시 업데이트)
=NOW()             ← 현재 날짜와 시간
=YEAR(날짜)        ← 연도 추출
=MONTH(날짜)       ← 월 추출
=DAY(날짜)         ← 일 추출
=WEEKDAY(날짜, 2)  ← 요일 반환 (2: 월=1, 화=2, ..., 일=7)
=DATE(년, 월, 일)  ← 날짜 생성
=EDATE(시작일, 개월수) ← n개월 후 날짜
=EOMONTH(날짜, 0)  ← 해당 월의 마지막 날
=DATEDIF(시작, 종료, 단위) ← 날짜 차이

단위: "Y"=연수, "M"=개월수, "D"=일수
예: =DATEDIF(A2, TODAY(), "Y") → 나이(만 나이) 계산

텍스트 함수 (Text Functions)

=LEFT(텍스트, n)     ← 왼쪽에서 n글자 추출
=RIGHT(텍스트, n)    ← 오른쪽에서 n글자 추출
=MID(텍스트, 시작, n) ← 시작 위치에서 n글자 추출
=LEN(텍스트)         ← 문자 길이
=FIND(찾을텍스트, 대상, [시작]) ← 위치 반환 (대소문자 구분)
=SEARCH(찾을텍스트, 대상, [시작]) ← 위치 반환 (대소문자 무관)
=TRIM(텍스트)        ← 앞뒤 공백 및 중복 공백 제거
=UPPER(텍스트)       ← 대문자 변환
=LOWER(텍스트)       ← 소문자 변환
=PROPER(텍스트)      ← 첫 글자만 대문자
=SUBSTITUTE(텍스트, 찾을것, 바꿀것) ← 텍스트 치환
=CONCATENATE(텍스트1, 텍스트2) ← 텍스트 연결 (또는 & 연산자)
=TEXT(값, 서식)      ← 숫자를 서식 문자열로 변환

실전 활용 예시:

주민등록번호에서 생년월일 추출:
=MID(A2, 1, 6)   → 앞 6자리(생년월일)
=LEFT(A2, 6)      → 동일 결과

성별 구분 (주민번호 7번째 자리):
=IF(MID(A2, 7, 1)="1" 또는 "3", "남", "여")

전화번호 형식 정리:
=TEXT(A2, "000-0000-0000")

VLOOKUP vs INDEX/MATCH 심화 비교

항목	VLOOKUP	INDEX/MATCH
검색 방향	왼쪽에서 오른쪽만	양방향 가능
기준 열 위치	반드시 첫 번째 열	어느 열도 가능
열 삽입 영향	열 번호 수동 수정 필요	자동 대응
속도	대용량 다소 느림	더 빠름
사용 난이도	쉬움	약간 복잡
시험 출제 빈도	매우 높음	높음

시험 전략: VLOOKUP을 기본으로 익히되, 왼쪽 검색이 필요한 문제에서 INDEX/MATCH를 적용하는 패턴을 반드시 숙달.

실전 기출 유형 패턴

패턴 1: VLOOKUP + IFERROR 조합

=IFERROR(VLOOKUP(B2, $H$2:$J$50, 2, FALSE), "해당없음")

→ 코드로 제품명 조회, 없으면 “해당없음” 표시

패턴 2: SUMIFS 다중 조건 집계

=SUMIFS($D$2:$D$100, $B$2:$B$100, G2, $C$2:$C$100, "완료")

→ 담당자(G2)이고 상태가 “완료”인 건의 금액 합계

패턴 3: 날짜 함수 + IF 조합

=IF(DATEDIF(C2, TODAY(), "Y")>=5, "장기고객", "일반고객")

→ 가입일로부터 5년 이상이면 “장기고객” 분류

패턴 4: 중첩 함수로 등급 부여

=IF(D2>=90, "우수",
  IF(D2>=70, "보통",
    IF(D2>=50, "미달", "불량")))

패턴 5: COUNTIFS로 조건별 현황 집계

=COUNTIFS($B$2:$B$100, "서울", $C$2:$C$100, ">=80")

→ 지역이 서울이고 점수 80 이상인 인원 수

핵심 개념 카드

VLOOKUP 4가지 인수 ★★★★★ : =VLOOKUP(찾는값, 범위, 열번호, FALSE). 기준값은 반드시 범위의 첫 열에 위치. FALSE = 정확히 일치. 암기 포인트: 범위 고정 시 절대참조($) 필수 — 수식 복사 시 범위 이동 방지

SUMIF vs SUMIFS 인수 순서 차이 ★★★★★ : SUMIF(조건범위, 조건, 합계범위) vs SUMIFS(합계범위, 조건범위1, 조건1, …). 인수 순서가 반대! 암기 포인트: SUMIFS는 “합계범위가 먼저” — S가 하나 더 붙으면 순서 바뀐다

INDEX/MATCH 조합 ★★★★☆ : =INDEX(반환열, MATCH(찾는값, 검색열, 0)). VLOOKUP 불가 상황(왼쪽 검색)에서 사용. 암기 포인트: MATCH는 위치 번호를 반환, INDEX는 그 위치의 값을 반환

IFERROR 활용 ★★★★☆ : =IFERROR(수식, 오류대체값). 오류 발생 가능한 함수(VLOOKUP, 나누기 등)를 감싸서 사용. 암기 포인트: 오류 없으면 수식 결과, 오류면 지정값 — 실기 시험에서 거의 필수

DATEDIF 단위 ★★★☆☆ : =DATEDIF(시작, 종료, 단위). “Y”=연, “M”=월, “D”=일. 만 나이 계산에 자주 활용. 암기 포인트: 엑셀 함수 목록에 없는 숨겨진 함수 — 직접 타이핑해야 함

실전 퀴즈

Q1. 다음 수식의 결과를 설명하라: =SUMIFS(D2:D10, B2:B10, “영업1팀”, C2:C10, ”>=100”)

D열(판매금액) 범위에서, B열이 “영업1팀”이고 C열(판매수량)이 100 이상인 행의 D열 값만 합산한다. SUMIFS는 합계범위를 첫 번째 인수로, 이후 조건범위·조건 쌍을 순서대로 입력한다. SUMIF(단수)와 인수 순서가 다르므로 주의해야 한다.

Q2. VLOOKUP을 사용했는데 #N/A 오류가 발생했다. 원인과 해결 방법을 설명하라.

#N/A 오류는 VLOOKUP이 찾는값을 범위의 첫 번째 열에서 발견하지 못할 때 발생한다. 주요 원인: ① 찾는값이 범위에 실제로 없음 ② 찾는값이나 범위 데이터에 앞뒤 공백이 있음(TRIM으로 해결) ③ 숫자와 텍스트 형식 불일치. 해결책: =IFERROR(VLOOKUP(…), “없음”)으로 감싸거나, 데이터 정리 후 재시도. 또는 INDEX/MATCH로 전환하여 유연성을 높인다.

Q3. 주민등록번호가 A2 셀에 “901231-1234567”과 같이 입력되어 있다. 만 나이를 구하는 수식을 작성하라.

생년월일은 주민번호 앞 6자리로, 연도·월·일로 분리하여 DATE 함수로 재구성한 뒤 DATEDIF로 만 나이를 계산한다.
=DATEDIF(DATE("19"&LEFT(A2,2), MID(A2,3,2), MID(A2,6,2)), TODAY(), "Y")
1990년대 생을 가정해 “19”를 앞에 붙였다. 2000년대 생이 혼재하면 MID(A2,8,1)로 성별코드를 확인하여 IF로 분기 처리해야 한다.

OIYO 편집부