10차시
함수 마스터 2: 논리·찾기/참조·D함수·재무·배열 수식
Part 2. 스프레드시트 일반 · 출제 빈도 ★★★
이 차시를 공부하는 이유
1급에서 2과목 고득점을 가르는 단원입니다. 특히 배열 수식과 INDEX+MATCH는 매회 출제되며, 원리를 한 번 이해하면 여러 문제에 그대로 적용됩니다.
2급에서 먼저 보면 좋은 내용
VLOOKUP·INDEX·MATCH는 2급에서 익히고, 1급은 XLOOKUP, D함수 조건표, 재무 함수, 배열 수식으로 확장됩니다.
학습 목표
IF·IFS·SWITCH·IFERROR 등 논리 함수로 조건 분기 수식을 해석할 수 있다.
VLOOKUP·XLOOKUP·INDEX·MATCH·CHOOSE의 인수와 옵션을 정확히 사용한다.
데이터베이스 함수의 조건 범위와 재무 함수의 인수를 이해한다.
배열 수식과 배열 상수의 규칙, 조건별 합계·개수 배열 수식을 작성할 수 있다.
핵심 개념
1. 논리 함수
| 함수 | 기능 |
|---|---|
| IF(조건, 참, 거짓) | 조건이 참이면 인수1, 거짓이면 인수2 |
| IFS(조건1, 값1, 조건2, 값2, …) | 처음으로 참이 되는 조건의 값을 반환. 모두 거짓이면 #N/A이므로 마지막에 TRUE, 기본값을 둠 |
| SWITCH(식, 값1, 결과1, …, 기본값) | 식이 값1과 같으면 결과1… 일치하는 값이 없으면 기본값 |
| IFERROR(수식, 오류 시 값) | 수식 결과가 오류이면 지정한 값을, 아니면 결과를 반환 |
| AND / OR / NOT | 모두 참이면 참 / 하나라도 참이면 참 / 논리값 반대 |
예시
=IFS(B2>=90,"A", B2>=80,"B", TRUE,"C")
=SWITCH(LEFT(A2,1), "1","남", "3","남", "2","여", "4","여", "확인")
=IFERROR(VLOOKUP(E2, A2:C10, 3, FALSE), "없음")
2. 찾기/참조 함수
| 함수 | 기능 |
|---|---|
| VLOOKUP(찾을 값, 범위, 열 번호, 옵션) | 범위의 첫 열에서 값을 찾아 같은 행의 지정 열 값을 반환. 옵션 FALSE(0)=정확히 일치, TRUE(1)·생략=근삿값(작거나 같은 값 중 최대, 첫 열 오름차순 정렬 필요) |
| HLOOKUP(찾을 값, 범위, 행 번호, 옵션) | 범위의 첫 행에서 찾아 같은 열의 지정 행 값을 반환 |
| XLOOKUP(찾을 값, 찾을 범위, 반환 범위, 없을 때 값, 일치 옵션, 검색 옵션) | 찾을 범위에서 값을 찾아 반환 범위의 같은 위치 값을 반환. 일치 옵션 0·생략=정확히 일치, -1=작거나 같은 값, 1=크거나 같은 값, 2=와일드카드 / 검색 옵션 -1=마지막부터 검색 |
| LOOKUP(찾을 값, 범위) | 범위의 첫 행·열에서 찾아 마지막 행·열의 같은 위치 값 반환 |
| INDEX(범위, 행 번호, 열 번호) | 범위에서 지정한 행·열 교차 위치의 값 |
| MATCH(찾을 값, 범위, 옵션) | 범위에서 찾을 값의 상대 위치(번호). 옵션 1=작거나 같은 값 중 최대(오름차순), 0=정확히 일치, -1=크거나 같은 값 중 최소(내림차순) |
| XMATCH(찾을 값, 범위, 일치, 검색) | MATCH의 확장판, 기본이 정확히 일치 |
| CHOOSE(번호, 값1, 값2, …) | 번호에 해당하는 순서의 값을 반환 |
| OFFSET(기준, 행, 열, 높이, 너비) | 기준 셀에서 지정한 행·열만큼 떨어진 셀(범위)을 반환 |
| INDIRECT(텍스트) | 주소 형태의 텍스트를 실제 셀 참조로 변환 |
| ROW / ROWS / COLUMN / COLUMNS | 행 번호 / 행 개수 / 열 번호 / 열 개수 |
| TRANSPOSE(범위) | 행과 열을 바꿔 반환 |
| ADDRESS(행, 열, 참조 유형) | 셀 주소를 텍스트로 반환 (1 절대, 2 행만 절대, 3 열만 절대, 4 상대) |
자주 나오는 조합
=INDEX(C2:C10, MATCH("김철수", A2:A10, 0)) → 이름으로 찾아 C열 값 반환
=XLOOKUP("김철수", A2:A10, C2:C10, "없음") → 위와 같은 결과
=CHOOSE(WEEKDAY(A2), "일","월","화","수","목","금","토")
=VLOOKUP(B2, $F$2:$G$6, 2, TRUE) → 점수 구간표로 등급 찾기
시험 함정 포인트
VLOOKUP은 찾을 값이 반드시 범위의 첫 번째 열에 있어야 합니다. 왼쪽 열 값을 가져와야 하면 INDEX+MATCH나 XLOOKUP을 씁니다.
VLOOKUP은 찾을 값이 반드시 범위의 첫 번째 열에 있어야 합니다. 왼쪽 열 값을 가져와야 하면 INDEX+MATCH나 XLOOKUP을 씁니다.
3. 데이터베이스 함수 (D함수)
형식은 모두 D함수(데이터 범위, 필드, 조건 범위)입니다. 데이터 범위는 필드명(머리글)을 포함해야 하고, 필드는 열 번호나 필드명("급여") 또는 머리글 셀 주소로 지정합니다.
AND 조건 = 같은 행
부서 | 급여영업 | >=300
→ 영업부이면서 급여 300 이상
OR 조건 = 다른 행
부서영업총무
→ 영업부 또는 총무부
| 함수 | 기능 |
|---|---|
| DSUM / DAVERAGE | 조건에 맞는 레코드의 합계 / 평균 |
| DCOUNT / DCOUNTA | 조건에 맞는 숫자 셀 개수 / 비어 있지 않은 셀 개수 |
| DMAX / DMIN | 최대값 / 최소값 |
| DGET | 조건에 맞는 하나의 값 (여러 개면 #NUM!, 없으면 #VALUE!) |
| DPRODUCT / DSTDEV / DVAR | 곱 / 표준편차 / 분산 |
시험 함정 포인트
조건 범위의 필드명은 데이터 범위의 필드명과 똑같아야 합니다. 조건에 수식을 쓸 때는 필드명을 원본과 다르게(또는 비워) 지정합니다.
조건 범위의 필드명은 데이터 범위의 필드명과 똑같아야 합니다. 조건에 수식을 쓸 때는 필드명을 원본과 다르게(또는 비워) 지정합니다.
4. 재무 함수와 정보 함수
| 함수 | 기능 |
|---|---|
| FV(이율, 기간, 정기 납입액, 현재 가치, 납입 시점) | 미래 가치. 매월 일정액을 적금했을 때 만기 금액 |
| PV(이율, 기간, 정기 지급액, 미래 가치, 납입 시점) | 현재 가치. 앞으로 받을 돈을 지금 가치로 환산 |
| PMT(이율, 기간, 현재 가치, 미래 가치, 납입 시점) | 대출금을 갚기 위한 정기 상환액 |
| NPV(할인율, 값1, 값2, …) | 미래 현금 흐름의 순 현재 가치 |
| SLN(취득가, 잔존 가치, 수명) | 정액법 감가상각액 |
- 월 단위 계산이면 이율은 연이율/12, 기간은 년×12로 맞춥니다. 납입 시점은 0·생략=기말, 1=기초입니다.
- 엑셀 재무 함수는 내가 내는 돈은 음수, 받는 돈은 양수로 표시합니다.
=PMT(5%/12, 36, 10000000)의 결과는 음수(상환해야 할 돈)입니다. - 정보 함수:
ISBLANK빈 셀,ISERROR모든 오류,ISERR#N/A를 제외한 오류,ISNUMBER,ISTEXT,ISNONTEXT,ISEVEN/ISODD짝수/홀수,ISLOGICAL TYPE: 숫자 1, 텍스트 2, 논리값 4, 오류 16 /N: 숫자는 그대로, 날짜는 일련번호, TRUE는 1, 나머지 0 /CELL("정보 유형", 셀): 셀의 주소·서식·내용 정보
5. 배열 수식과 배열 상수
- 배열 수식은 수식을 입력하고
Ctrl+Shift+Enter를 누르면 앞뒤에 중괄호 { }가 자동으로 붙습니다. 중괄호를 직접 입력하면 안 됩니다. - 배열 수식에 쓰이는 배열 인수들은 행과 열의 개수가 같아야 합니다.
- 배열 상수: 열은 쉼표(,), 행은 세미콜론(;)으로 구분합니다.
{1,2,3;4,5,6}은 2행 3열입니다. - 배열 상수에는 숫자·텍스트·논리값·오류값을 쓸 수 있지만 셀 주소, 수식, $, %, 괄호, 길이가 다른 행·열은 쓸 수 없습니다.
- 배열 수식에서 조건을 AND로 묶을 때는 *, OR로 묶을 때는 +를 씁니다.
조건별 계산 배열 수식 패턴 (부서 A2:A20, 직급 B2:B20, 급여 C2:C20)
합계 {=SUM((A2:A20="영업")*(B2:B20="대리")*C2:C20)}
합계 {=SUM(IF((A2:A20="영업")*(B2:B20="대리"), C2:C20))}
개수 {=SUM((A2:A20="영업")*(B2:B20="대리"))}
개수 {=COUNT(IF((A2:A20="영업")*(B2:B20="대리"), 1))}
최대 {=MAX((A2:A20="영업")*C2:C20)}
평균 {=AVERAGE(IF(A2:A20="영업", C2:C20))}
위치 {=INDEX(A2:A20, MATCH(MAX(C2:C20), C2:C20, 0))}
시험 함정 포인트
엑셀 2021은 Enter만 눌러도 동적 배열로 계산되지만, 시험은 Ctrl+Shift+Enter와 { } 표기를 기준으로 출제합니다. '{ }를 직접 입력한다'는 오답입니다.
엑셀 2021은 Enter만 눌러도 동적 배열로 계산되지만, 시험은 Ctrl+Shift+Enter와 { } 표기를 기준으로 출제합니다. '{ }를 직접 입력한다'는 오답입니다.
확인 문제
1 / 5
문제 1
A2:A10에 사원 이름, C2:C10에 급여가 있을 때 '김철수'의 급여를 구하는 수식으로 옳지 않은 것은?
💡 정답 ④ 해설: MATCH는 찾은 값의 위치(번호)만 반환하므로 급여가 아니라 김철수가 몇 번째인지를 돌려줍니다.
문제 2
배열 상수 {1,2,3;4,5,6}에 대한 설명으로 옳은 것은?
💡 정답 ② 해설: 배열 상수에서 쉼표(,)는 열, 세미콜론(;)은 행을 구분합니다. 세미콜론이 1개이므로 2행, 각 행에 값이 3개이므로 3열입니다.
문제 3
데이터베이스 함수의 조건 범위를 아래와 같이 지정했을 때 의미로 옳은 것은?
부서 | 급여
영업 |
| >=300
부서 | 급여
영업 |
| >=300
💡 정답 ② 해설: 조건이 서로 다른 행에 있으면 OR 조건입니다. 같은 행에 있어야 AND 조건이 됩니다.
문제 4
수식 =ISERR(#N/A 값이 있는 셀)의 결과와 =ISERROR(같은 셀)의 결과를 순서대로 바르게 나타낸 것은?
💡 정답 ② 해설: ISERR는 #N/A를 제외한 오류일 때만 TRUE이므로 FALSE, ISERROR는 모든 오류에 TRUE를 반환합니다.
문제 5
부서(A2:A20)가 '영업'이고 직급(B2:B20)이 '대리'인 사원의 급여(C2:C20) 합계를 구하는 배열 수식으로 옳은 것은?
💡 정답 ② 해설: 두 조건을 모두 만족(AND)해야 하므로 조건끼리 *로 곱하고 급여 범위를 곱해 SUM합니다. +는 OR 조건입니다.
10차시 문제 더 풀기
확인문제와 실전 모의고사에서 이 차시 범위 문제만 모아 해설과 함께 볼 수 있습니다.