Ch3. 엑셀 기초~고급 — 고급 함수 마스터
왜 고급 함수인가?
기초 함수(SUM, AVERAGE, IF)로는 실무의 복잡한 요구사항을 처리하기 어렵습니다. 고급 함수는 다른 시트·파일에서 데이터를 가져오고, 복잡한 조건을 처리하며, 오류를 우아하게 다루는 데 필수입니다.
VLOOKUP — 기초부터 한계까지
VLOOKUP 기본 구조
=VLOOKUP(찾을값, 범위, 열번호, [일치방식])| 인수 | 설명 |
|---|---|
| 찾을값 | 검색할 값 (셀 참조 권장) |
| 범위 | 데이터가 있는 범위 (첫 열에서 검색) |
| 열번호 | 반환할 값의 열 위치 (범위 기준) |
| 일치방식 | FALSE(0) = 정확히 일치, TRUE(1) = 근사 일치 |
실전 예시
직원 번호로 이름 찾기:
=VLOOKUP(A2, 직원테이블!$A:$D, 2, FALSE)
→ A2의 직원번호로 직원테이블 2열(이름) 반환
제품코드로 단가 찾기:
=VLOOKUP(B5, $F$2:$H$100, 3, FALSE)
→ B5의 코드로 F~H 범위 3열(단가) 반환VLOOKUP의 3가지 한계
한계 1: 왼쪽 검색 불가
범위의 첫 번째 열에서만 검색 가능
→ 이름으로 직원번호를 찾으려면 VLOOKUP 불가한계 2: 열 삽입 시 번호 깨짐
=VLOOKUP(A2, A:D, 3, FALSE)
→ B~C 사이에 열 삽입 시 자동으로 4열 이동하지 않음
→ 하드코딩된 열번호 "3"이 틀린 열을 반환한계 3: 중복값 처리 불가
찾을값이 여러 개 있으면 첫 번째만 반환INDEX + MATCH — VLOOKUP 완전 대체
각 함수 이해
INDEX: 범위에서 특정 위치의 값을 반환
=INDEX(범위, 행번호, [열번호])
=INDEX(B2:B100, 5) → B2:B100 범위의 5번째 값
=INDEX(A2:D100, 3, 2) → 3행 2열의 값MATCH: 범위에서 찾을값의 위치(행/열 번호)를 반환
=MATCH(찾을값, 범위, [일치방식])
=MATCH("홍길동", A2:A100, 0) → "홍길동"이 있는 행 번호 반환
일치방식: 0 = 정확히 일치 (대부분의 경우)INDEX + MATCH 조합
=INDEX(반환범위, MATCH(찾을값, 검색범위, 0))VLOOKUP vs INDEX+MATCH 비교:
[VLOOKUP 방식] 이름으로 연봉 찾기:
=VLOOKUP("홍길동", B:E, 4, FALSE)
→ B열 첫 번째에 이름이 있어야만 가능
[INDEX+MATCH 방식] 어느 열도 검색 가능:
=INDEX(E2:E100, MATCH("홍길동", C2:C100, 0))
→ C열(이름)에서 찾아 E열(연봉) 반환
→ 검색열이 반환열보다 오른쪽이어도 OKINDEX+MATCH 실전 활용
이중 조건 검색 (배열 수식):
부서와 직급이 모두 일치하는 연봉:
=INDEX(D2:D100,
MATCH(1, (B2:B100="영업팀")*(C2:C100="과장"), 0))
Ctrl+Shift+Enter로 입력동적 열 선택:
헤더명으로 열을 동적으로 찾기:
=INDEX(A1:F100,
MATCH("홍길동", A1:A100, 0),
MATCH("연봉", A1:F1, 0))
→ 행과 열을 모두 MATCH로 찾아 교차점 값 반환중첩 IF — 다중 조건 처리
IF 기본 복습
=IF(조건, 참일때값, 거짓일때값)
=IF(A1>=60, "합격", "불합격")IF 중첩 (3단계 예시)
점수에 따른 등급 배정:
=IF(A1>=90, "A",
IF(A1>=80, "B",
IF(A1>=70, "C",
IF(A1>=60, "D", "F"))))주의: IF 중첩이 깊어질수록 읽기 어려워집니다. Excel 2019+에서는 IFS를 권장합니다.
IFS 함수 (Excel 2019+)
=IFS(조건1, 값1, 조건2, 값2, ..., TRUE, 기본값)
=IFS(A1>=90, "A",
A1>=80, "B",
A1>=70, "C",
A1>=60, "D",
TRUE, "F")IF 중첩 vs IFS 비교:
| 항목 | IF 중첩 | IFS |
|---|---|---|
| 가독성 | 낮음 (들여쓰기 복잡) | 높음 (평면적 구조) |
| 최대 조건 수 | 7중첩 권장 | 127개 |
| 기본값 | 마지막 FALSE 값 | TRUE, 기본값 |
| 지원 버전 | 모든 버전 | Excel 2019+ |
SWITCH 함수 (정확한 값 매칭)
=SWITCH(기준값,
비교값1, 결과1,
비교값2, 결과2,
...,
기본값)
부서 코드를 부서명으로:
=SWITCH(A1,
"S", "영업팀",
"M", "마케팅팀",
"D", "개발팀",
"기타")SUMIF / COUNTIF / AVERAGEIF
단일 조건 집계
SUMIF(범위, 조건, [합계범위])
COUNTIF(범위, 조건)
AVERAGEIF(범위, 조건, [평균범위])실전 예시:
서울 지역 매출 합계:
=SUMIF(B2:B100, "서울", D2:D100)
80점 이상 학생 수:
=COUNTIF(C2:C100, ">=80")
영업팀 평균 연봉:
=AVERAGEIF(A2:A100, "영업팀", E2:E100)
이달 1일 이후 매출:
=SUMIF(A2:A100, ">="&DATE(2024,1,1), C2:C100)다중 조건 집계 — SUMIFS / COUNTIFS / AVERAGEIFS
SUMIFS(합계범위, 조건범위1, 조건1, 조건범위2, 조건2, ...)
COUNTIFS(조건범위1, 조건1, 조건범위2, 조건2, ...)실전 예시:
서울 + 영업팀 매출:
=SUMIFS(D2:D100, B2:B100, "서울", A2:A100, "영업팀")
특정 기간 + 특정 제품 판매 수량:
=SUMIFS(E2:E100,
C2:C100, "노트북",
A2:A100, ">="&DATE(2024,1,1),
A2:A100, "<="&DATE(2024,3,31))IFERROR — 오류 우아하게 처리
오류 종류
| 오류 | 원인 |
|---|---|
#N/A | VLOOKUP/MATCH에서 찾는 값 없음 |
#DIV/0! | 0으로 나누기 |
#VALUE! | 잘못된 데이터 타입 |
#REF! | 참조 범위 삭제됨 |
#NAME? | 함수명 오타 |
IFERROR 사용법
=IFERROR(수식, 오류시반환값)
오류 시 0 반환:
=IFERROR(VLOOKUP(A2, D:E, 2, FALSE), 0)
오류 시 빈 칸:
=IFERROR(VLOOKUP(A2, D:E, 2, FALSE), "")
오류 시 안내 메시지:
=IFERROR(VLOOKUP(A2, D:E, 2, FALSE), "데이터 없음")IFNA — #N/A 오류만 처리
=IFNA(수식, N/A오류시값)
=IFNA(MATCH(A2, B2:B100, 0), "미등록")
→ VLOOKUP/MATCH의 #N/A만 처리, 다른 오류는 그대로 표시TEXT 함수 — 숫자·날짜를 원하는 형식으로
기본 사용법
=TEXT(값, 서식코드)자주 쓰는 서식 코드
| 목적 | 서식코드 | 결과 |
|---|---|---|
| 천 단위 구분 | "#,##0" | 1,234,567 |
| 소수 2자리 | "0.00" | 3.14 |
| 원화 표시 | "₩#,##0" | ₩50,000 |
| 날짜 한국식 | "yyyy년 mm월 dd일" | 2024년 03월 15일 |
| 요일 | "dddd" | Friday |
| 한국 요일 | "aaa" | 금 |
| 시간 | "hh:mm:ss" | 09:30:00 |
실전 활용
문자열과 날짜 합치기:
="보고서 작성일: "&TEXT(TODAY(), "yyyy-mm-dd")
숫자에 단위 붙이기:
=TEXT(B2, "#,##0")&"원"
→ 1,500,000원
퍼센트 변환:
=TEXT(A2/B2, "0.0%")
→ 75.3%XLOOKUP — 미래의 표준 (Excel 365)
VLOOKUP의 완전한 대체
=XLOOKUP(찾을값, 검색범위, 반환범위,
[없을때값], [일치방식], [검색방향])장점:
- 왼쪽 검색 가능
- 여러 열 동시 반환
- #N/A 처리 내장
- 역방향 검색 지원
이름으로 직원번호 찾기 (왼쪽 검색):
=XLOOKUP("홍길동", C2:C100, A2:A100, "없음")
없을 때 "없음" 자동 처리:
=XLOOKUP(A2, F:F, G:G, "데이터 없음")핵심 개념 카드
VLOOKUP 한계 요약 ★★★★★ : 왼쪽 검색 불가, 열 삽입 시 번호 깨짐, 중복값은 첫 번째만 반환. 대안: INDEX+MATCH 조합 또는 XLOOKUP(365)
IFERROR 공식 ★★★★
: =IFERROR(원래수식, 오류시표시값) — 모든 에러를 캐치.
주의: IFNA는 #N/A만 처리 — 다른 에러는 그대로 노출
IFS vs IF중첩 ★★★ : IFS는 Excel 2019+에서만 사용 가능. 구버전 환경이면 IF 중첩 필수. 호환성 확인 후 선택
실전 퀴즈 5문항
Q1. =VLOOKUP(A2, B:E, 3, FALSE)에서 B열과 C열 사이에 새 열을 삽입하면 어떻게 되는가?
- ① 자동으로 수식이 조정된다
- ② 원래 C열 데이터 대신 삽입된 열 데이터가 반환된다
- ③ 수식이 오류를 반환한다
- ④ 아무 변화가 없다
정답: ② (열번호 3이 새로 삽입된 열을 가리키게 됨 — VLOOKUP의 한계)
Q2. =INDEX(D2:D100, MATCH(“홍길동”, B2:B100, 0))에서 MATCH 함수의 역할은?
- ① D열에서 “홍길동”을 찾는다
- ② B열에서 “홍길동”의 위치(행 번호)를 반환한다
- ③ D열의 100번째 값을 반환한다
- ④ “홍길동”의 개수를 센다
정답: ② (MATCH는 위치를 반환, INDEX는 그 위치의 값을 반환)
Q3. =SUMIFS(E2:E100, A2:A100, “영업팀”, C2:C100, ”>=50000”)의 의미는?
- ① A열이 “영업팀”인 행의 수 × 50000
- ② A열이 “영업팀”이고 C열이 50000 이상인 행의 E열 합계
- ③ A열이 “영업팀”이거나 C열이 50000 이상인 행의 E열 합계
- ④ E열의 평균
정답: ② (SUMIFS는 모든 조건을 AND로 처리)
Q4. =IFERROR(VLOOKUP(A2,D:E,2,FALSE), “없음”)에서 VLOOKUP이 #DIV/0! 오류를 반환하면?
- ① “없음”을 표시한다
- ② #DIV/0!가 그대로 표시된다
- ③ 0을 반환한다
- ④ 빈 셀을 반환한다
정답: ① (IFERROR는 #N/A 포함 모든 오류를 처리)
Q5. =TEXT(45000, ”₩#,##0”) 의 결과는?
- ① 45000
- ② ₩45,000
- ③ 45,000원
- ④ #VALUE!
정답: ② (TEXT는 숫자를 지정한 서식 문자열로 변환)
OIYO 편집부
Content Editor지식 인큐베이터이자 전문 콘텐츠 크리에이터. 경영, 경제, 법률 및 실생활에 유용한 실무/자격증 중심의 깊이 있는 정보를 연구하고 공유합니다.