Ch4. 컴퓨터활용능력 1급 — 고급 함수와 매크로
이번 강의 학습 목표
컴활 1급 스프레드시트 실기의 고급 영역인 **고급 함수 조합, 배열 수식, 매크로(VBA)**를 집중 학습합니다. 이 챕터의 내용은 단독 문제로 출제될 뿐 아니라 여러 함수를 중첩하는 복합 문제의 핵심 재료가 됩니다.
찾기·참조 함수 심화
VLOOKUP / HLOOKUP 응용
1장에서 기초를 배웠다면, 이제 다양한 변형 패턴을 숙달할 차례입니다.
기본형:
=VLOOKUP(찾는값, 범위, 열번호, FALSE)
응용 패턴 1 — 열번호를 MATCH로 동적 결정:
=VLOOKUP(A2, $D$1:$H$100, MATCH(B2, $D$1:$H$1, 0), FALSE)
→ B2에 입력한 열 제목을 기준으로 자동으로 열번호 결정
응용 패턴 2 — 찾는값을 TEXT 함수로 형식 맞추기:
=VLOOKUP(TEXT(A2,"000"), $D$2:$F$50, 2, FALSE)
→ A2가 숫자 5이면 "005"로 변환 후 조회
응용 패턴 3 — IFERROR로 오류 처리:
=IFERROR(VLOOKUP(A2, $D$2:$F$50, 2, FALSE), "미등록")OFFSET 함수
OFFSET은 기준 셀에서 지정한 행·열만큼 이동한 위치의 값을 반환하거나, 범위를 동적으로 생성합니다.
=OFFSET(기준셀, 행이동, 열이동, [높이], [너비])
기본 사용:
=OFFSET(A1, 2, 3)
→ A1에서 2행 아래, 3열 오른쪽 = D3의 값
동적 범위 생성 (SUM과 조합):
=SUM(OFFSET(A1, 0, 0, COUNTA(A:A), 1))
→ A열에 데이터가 입력된 만큼 자동으로 합계 범위 확장
OFFSET 활용 — 마지막 입력값 참조:
=OFFSET(A1, COUNTA(A:A)-1, 0)
→ A열 마지막으로 입력된 값 반환INDEX / MATCH 심화
기본 조합:
=INDEX(반환범위, MATCH(찾는값, 검색범위, 0))
이중 MATCH — 행과 열 동시 검색:
=INDEX($B$2:$F$10,
MATCH(A2, $A$2:$A$10, 0),
MATCH(B2, $B$1:$F$1, 0))
→ A2 행 값 + B2 열 값으로 교차점 데이터 조회
역방향 검색 (VLOOKUP 불가 패턴):
=INDEX(A2:A100, MATCH(E2, C2:C100, 0))
→ C열에서 E2를 찾아 같은 행의 A열 값 반환
(VLOOKUP은 왼쪽 검색 불가)
근사 일치 활용 (가격 구간 등):
=INDEX(D2:D6, MATCH(B2, A2:A6, 1))
→ A열이 오름차순일 때 B2 이하 최대값의 위치에서 D열 값 반환
(요금표, 세율표 등에 활용)조건부 함수 조합 심화
SUMIFS / COUNTIFS / AVERAGEIFS 고급 패턴
다중 OR 조건 (SUMIFS 여러 개 더하기):
= SUMIFS(D:D, B:B, "서울", C:C, "A")
+ SUMIFS(D:D, B:B, "부산", C:C, "A")
→ 서울 또는 부산이고 등급이 A인 금액의 합계
와일드카드 조건:
=SUMIFS(D:D, B:B, "서울*") ← "서울"로 시작하는 모든 지역
=COUNTIFS(C:C, "*팀") ← 이름이 "팀"으로 끝나는 모든 항목
날짜 범위 조건:
=SUMIFS(D:D, A:A, ">="&DATE(2026,1,1), A:A, "<="&DATE(2026,3,31))
→ 2026년 1분기 금액 합계 (날짜는 & 연산자로 결합)
셀 참조 조건:
=SUMIFS(D:D, B:B, G2, C:C, ">="&H2)
→ G2의 지역이고 H2 이상인 금액 합계 (조건에 셀 참조 포함 시 & 사용)SUMPRODUCT — 배열 없는 배열 계산
SUMPRODUCT는 배열 수식 없이 여러 조건의 곱을 합산하는 강력한 함수입니다.
기본 구조:
=SUMPRODUCT(배열1, 배열2, ...)
→ 각 배열의 같은 위치 값을 곱한 후 전부 합산
조건부 합계 (SUMIFS 대체):
=SUMPRODUCT((B2:B100="서울")*(C2:C100>=80)*D2:D100)
→ B열=서울 AND C열>=80인 행의 D열 합계
TRUE*TRUE*값 = 1*1*값 = 값 / 조건 불만족 시 0
고유값 개수 세기:
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))
→ A2:A100에서 중복을 제외한 고유 항목 수 반환
(단, 빈 셀이 없어야 함)배열 수식 (Array Formula)
배열 수식이란
배열 수식은 여러 셀의 값을 한꺼번에 처리하는 수식입니다. 일반 수식은 하나의 값을 반환하지만, 배열 수식은 각 셀의 연산 결과 배열을 만들어 최종 값을 반환합니다.
입력 방법: 수식 입력 후 Ctrl + Shift + Enter (일반 Enter 아님)
표시: 수식 입력줄에 {=수식} 형태로 중괄호가 자동으로 표시됨
배열 수식 기본 패턴
여러 열의 곱 합계:
{=SUM(B2:B10 * C2:C10)}
→ B2*C2 + B3*C3 + ... + B10*C10
(SUMPRODUCT로도 동일 결과 가능)
조건부 최대값 (MAXIFS 없는 버전):
{=MAX(IF(B2:B100="서울", C2:C100))}
→ B열이 "서울"인 행 중 C열 최대값
(Excel 2019 이후는 MAXIFS 함수 직접 사용 가능)
조건부 최소값:
{=MIN(IF(B2:B100="영업1팀", D2:D100))}
→ B열이 "영업1팀"인 행의 D열 최소값
고유값 목록 추출 (고급):
{=INDEX(A:A, MATCH(0, COUNTIF($F$1:F1, A$2:A$100), 0)+1)}
→ A열에서 중복 없는 고유값 목록 순서대로 추출MAXIFS / MINIFS (Excel 2019+)
=MAXIFS(최대값범위, 조건범위1, 조건1, ...)
=MINIFS(최소값범위, 조건범위1, 조건1, ...)
예시:
=MAXIFS(C2:C100, B2:B100, "서울")
→ 지역이 서울인 행의 점수 최대값
=MINIFS(D2:D100, B2:B100, "서울", C2:C100, "1분기")
→ 지역=서울 AND 분기=1분기인 행의 최소 판매량텍스트 함수 고급 활용
LEFT / RIGHT / MID 조합 패턴
주민등록번호 분해 (A2 = "901231-1234567"):
생년월일: =LEFT(A2, 6) → "901231"
성별코드: =MID(A2, 8, 1) → "1"
성별판별: =IF(MID(A2,8,1)="1","남","여")
생년(연도): ="19"&LEFT(A2,2) → "1990"
제품코드 분해 (A2 = "KR-2026-ABC"):
국가코드: =LEFT(A2, 2) → "KR"
연도: =MID(A2, 4, 4) → "2026"
제품번호: =RIGHT(A2, 3) → "ABC"
공백 구분 이름 분리 (A2 = "홍 길동"):
성: =LEFT(A2, FIND(" ", A2)-1) → "홍"
이름: =MID(A2, FIND(" ", A2)+1, LEN(A2)) → "길동"SUBSTITUTE와 REPLACE
SUBSTITUTE — 특정 문자열 치환:
=SUBSTITUTE(A2, "-", "")
→ "901231-1234567" → "9012311234567" (하이픈 제거)
=SUBSTITUTE(A2, " ", "_")
→ 공백을 언더스코어로 교체
=SUBSTITUTE(A2, "A", "B", 2)
→ 두 번째로 등장하는 "A"만 "B"로 교체
REPLACE — 위치 기반 치환:
=REPLACE(A2, 8, 7, "*******")
→ 8번째 위치부터 7글자를 "*******"로 교체
(주민번호 뒷자리 마스킹에 활용)TEXT 함수 서식 코드
숫자 → 문자열 변환:
=TEXT(1234567, "#,##0") → "1,234,567"
=TEXT(0.1234, "0.00%") → "12.34%"
=TEXT(A2, "yyyy-mm-dd") → "2026-05-25"
=TEXT(A2, "yyyy년 m월 d일") → "2026년 5월 25일"
=TEXT(A2, "[$-412]aaa") → "월" (한국어 요일 약자)
숫자에 단위 붙이기:
=TEXT(A2, "#,##0") & "원" → "1,234,567원"
=TEXT(A2, "0.0") & "점" → "85.3점"매크로 기록과 실행
매크로 기록 절차
1단계: 개발 도구 탭 활성화
파일 → 옵션 → 리본 사용자 지정 → 개발 도구 체크
2단계: 매크로 기록 시작
[개발 도구] → [매크로 기록] 클릭
또는 [보기] → [매크로] → [매크로 기록]
3단계: 매크로 이름 설정
이름 규칙: 영문/한글, 공백 없음, 숫자 시작 불가
올바른 예: 합계계산, SumMacro, 서식적용1
잘못된 예: 합계 계산(공백), 1번매크로(숫자 시작)
4단계: 바로가기 키 지정 (선택)
Ctrl + 소문자: Ctrl+a ~ Ctrl+z (일부 기존 단축키와 충돌)
Ctrl + Shift + 대문자: 충돌 최소화 권장
5단계: 저장 위치 선택
- 현재 통합 문서: 해당 파일에서만 사용 가능
- 개인용 매크로 통합 문서(PERSONAL.XLSB): 엑셀 전체에서 사용
6단계: 작업 수행 (신중하게!)
정확한 순서로 작업 — 실수도 모두 기록됨
7단계: 기록 중지
[개발 도구] → [기록 중지] 또는 상태표시줄 정지 버튼절대 참조 vs 상대 참조 매크로
절대 참조 (기본값):
→ 항상 기록 당시의 셀 위치에서 작업 수행
→ 예: C3 셀을 클릭하며 기록했다면, 실행 시 항상 C3에 작업
상대 참조:
→ 실행 시점의 활성 셀을 기준으로 상대적 위치에 작업
→ 설정: [개발 도구] → [상대 참조 사용] 버튼 클릭 후 기록
→ 예: 현재 셀에서 오른쪽 2칸 이동하여 값 입력매크로 실행 방법
방법 1 — 바로가기 키: 기록 시 지정한 Ctrl+키 조합
방법 2 — 메뉴 실행: [보기] → [매크로] → [매크로 보기] → 선택 → 실행
방법 3 — 양식 컨트롤 단추 연결:
[개발 도구] → [삽입] → 양식 컨트롤의 "단추" 그리기
→ 매크로 지정 대화상자에서 연결할 매크로 선택
방법 4 — 도형에 연결:
도형 우클릭 → [매크로 지정] → 매크로 선택VBA 기초 코드 작성
VBA 편집기 열기와 구조
편집기 열기: Alt + F11
프로젝트 탐색기: 왼쪽 창 (통합문서·시트·모듈 구조 표시)
코드 창: 오른쪽 큰 창 (코드 작성 영역)
속성 창: 하단 (선택한 개체의 속성 표시)
모듈 추가: 프로젝트 탐색기에서 우클릭 → [삽입] → [모듈]변수와 데이터 형식
Sub 변수예시()
' 변수 선언
Dim 이름 As String ' 문자열
Dim 나이 As Integer ' 정수 (-32768 ~ 32767)
Dim 금액 As Long ' 큰 정수
Dim 비율 As Double ' 실수(소수)
Dim 합격 As Boolean ' True/False
Dim 날짜 As Date ' 날짜/시간
' 변수에 값 대입
이름 = "홍길동"
나이 = 25
금액 = 1500000
비율 = 0.1234
합격 = True
날짜 = Date ' 오늘 날짜
' 셀에 출력
Range("A1").Value = 이름
Range("B1").Value = 나이
Range("C1").Value = 금액
End Sub조건문 (If ~ Then ~ Else)
Sub 조건문예시()
Dim 점수 As Integer
점수 = Range("B2").Value
' 단순 조건
If 점수 >= 60 Then
Range("C2").Value = "합격"
Else
Range("C2").Value = "불합격"
End If
' 다중 조건 (ElseIf)
If 점수 >= 90 Then
Range("D2").Value = "A"
ElseIf 점수 >= 80 Then
Range("D2").Value = "B"
ElseIf 점수 >= 70 Then
Range("D2").Value = "C"
ElseIf 점수 >= 60 Then
Range("D2").Value = "D"
Else
Range("D2").Value = "F"
End If
' Select Case (다중 분기를 깔끔하게)
Select Case 점수
Case Is >= 90
Range("E2").Value = "우수"
Case 70 To 89
Range("E2").Value = "보통"
Case Else
Range("E2").Value = "미달"
End Select
End Sub반복문 (For ~ Next, Do ~ Loop)
Sub 반복문예시()
Dim i As Integer
Dim 합계 As Long
합계 = 0
' For ~ Next: 지정 횟수 반복
For i = 1 To 10
합계 = 합계 + Cells(i, 1).Value ' A열 1~10행 합계
Next i
Range("A11").Value = 합계
' Step으로 증가 간격 지정
For i = 2 To 20 Step 2 ' 짝수만 처리
Cells(i, 2).Value = i * i ' B열에 제곱값
Next i
' Do While ~ Loop: 조건이 참인 동안 반복
Dim 행 As Integer
행 = 2
Do While Cells(행, 1).Value <> "" ' A열이 빌 때까지
Cells(행, 3).Value = Cells(행, 1).Value * 1.1 ' C열에 10% 증가값
행 = 행 + 1
Loop
' For Each: 컬렉션 순환
Dim 셀 As Range
For Each 셀 In Range("A1:A20")
If 셀.Value < 0 Then
셀.Font.Color = RGB(255, 0, 0) ' 음수는 빨간색
End If
Next 셀
End Sub자주 쓰는 VBA 패턴
' 메시지 박스
MsgBox "작업이 완료되었습니다", vbInformation, "알림"
MsgBox "정말 삭제하시겠습니까?", vbYesNo, "확인"
' 입력 박스
Dim 검색어 As String
검색어 = InputBox("검색할 이름을 입력하세요", "검색")
If 검색어 = "" Then Exit Sub ' 취소 시 종료
' 마지막 행 번호 구하기
Dim 마지막행 As Long
마지막행 = Cells(Rows.Count, 1).End(xlUp).Row
' → A열 기준으로 데이터가 입력된 마지막 행 번호
' 시트 전환
Worksheets("집계").Select
Worksheets("집계").Range("A1").Select
' 자동 채우기 (AutoFill)
Range("B2").AutoFill Destination:=Range("B2:B100")
' 열 너비 자동 맞춤
Columns("A:E").AutoFit
' 셀 서식 일괄 적용
With Range("A1:E1")
.Font.Bold = True
.Font.Size = 14
.Interior.Color = RGB(0, 112, 192) ' 파란 배경
.Font.Color = RGB(255, 255, 255) ' 흰 글씨
.HorizontalAlignment = xlCenter
End With핵심 개념 카드
배열 수식 입력 단축키 ★★★★★
: 수식 입력 후 Ctrl+Shift+Enter. 자동으로 {=수식} 중괄호 표시. 배열 수식 셀 수정 시에도 동일하게 Ctrl+Shift+Enter로 완료.
암기 포인트: “배열 수식 = 세 손가락 조합(Ctrl+Shift+Enter)”
SUMPRODUCT vs 배열 수식 ★★★★☆
: SUMPRODUCT는 일반 Enter로 입력 가능한 배열 계산. 조건 비교를 괄호로 묶어 TRUE/FALSE 배열 생성. (조건1)*(조건2)*값범위 패턴이 핵심.
암기 포인트: SUMPRODUCT가 배열 수식보다 입력이 간단 → 가능하면 SUMPRODUCT 사용
VBA 변수 선언 (Dim) ★★★★☆ : Integer(정수) / Long(큰 정수) / String(문자열) / Double(실수) / Boolean(참거짓) / Date(날짜). 선언 없이도 Variant로 동작하지만 명시적 선언이 권장. 암기 포인트: “Dim 이름 As 형식” — Dim = Dimension(치수/크기를 지정한다)
For Each vs For i ★★★☆☆ : For Each는 컬렉션(범위, 시트 등) 순환에 간결. For i는 셀 주소(Cells(i, j))를 계산해야 할 때 유용. 대부분 For Each가 코드가 깔끔함. 암기 포인트: “컬렉션을 순서대로 처리 = For Each, 행·열 번호 계산 필요 = For i”
OFFSET의 활용 ★★★☆☆ : =OFFSET(기준, 행이동, 열이동, [높이], [너비]). 동적 범위(데이터 증가에 자동 대응)를 만들 때 COUNTA와 조합. 이름 관리자의 동적 범위 정의에 자주 사용. 암기 포인트: “OFFSET = 기준점에서 이동 + 범위 크기 지정”
실전 퀴즈
Q1. =SUMPRODUCT((B2:B100=“서울”)*(C2:C100>=80), D2:D100) 수식의 결과를 설명하라.
B2:B100이 “서울”인 조건을 TRUE/FALSE 배열로 변환하면
{TRUE, FALSE, ...}배열이 된다. C2:C100이 80 이상인 조건도 동일하게 배열화된다. 두 배열을 곱하면 AND 효과가 나타난다(TRUE=1, FALSE=0). 이 결과 배열에 D2:D100을 곱하면 두 조건을 모두 만족하는 행의 D열 값만 남는다. SUMPRODUCT가 이 최종 배열을 합산하므로, 결과는 지역이 “서울”이고 점수가 80 이상인 행의 D열 합계이다. SUMIFS와 동일한 결과를 반환하지만, SUMPRODUCT는 배열 수식 없이도 동작한다.
Q2. 배열 수식 {=MAX(IF(B2:B100="영업1팀", C2:C100))}는 무엇을 계산하는가? 왜 Ctrl+Shift+Enter가 필요한가?
B2:B100에서 “영업1팀”과 같은 행의 C열 값만 선별하여 그 최대값을 반환하는 수식이다. IF가 배열 인수를 받아 각 행을 평가하면 “영업1팀”이면 C열 값을, 아니면 FALSE를 반환하는 배열을 만든다. MAX는 이 배열에서 최대 숫자를 반환한다. 일반 Enter로 입력하면 단일 값으로만 처리되어 FALSE가 섞인 배열을 올바르게 계산하지 못한다. Ctrl+Shift+Enter는 엑셀에 “배열 전체를 순서대로 계산하라”는 신호를 보내므로 반드시 필요하다. Excel 2019+ 에서는 MAXIFS 함수로 같은 결과를 일반 Enter로 얻을 수 있다.
Q3. VBA에서 A열의 데이터가 입력된 마지막 행을 자동으로 찾는 코드를 작성하라.
Dim 마지막행 As Long 마지막행 = Cells(Rows.Count, 1).End(xlUp).Row
Rows.Count는 시트의 전체 행 수(1,048,576)를 의미하며,End(xlUp)는 해당 열의 맨 아래서 위로 이동하다가 처음 만나는 데이터 셀을 찾는다..Row로 행 번호를 반환한다. 이후For i = 2 To 마지막행으로 데이터 범위를 동적으로 처리할 수 있다.
Q4. 매크로를 양식 컨트롤 단추에 연결하는 절차를 설명하라.
① [개발 도구] 탭 → [삽입] → 양식 컨트롤 섹션에서 “단추(Button)” 클릭. ② 시트에서 드래그하여 단추 크기·위치 지정. ③ 드래그 완료 즉시 [매크로 지정] 대화상자가 자동으로 나타남. ④ 목록에서 연결할 매크로 이름 선택 후 확인. ⑤ 단추 텍스트를 더블클릭하여 레이블 변경(예: “합계 계산”). 이후 단추를 클릭하면 지정한 매크로가 실행된다.
Q5. SUBSTITUTE와 REPLACE 함수의 차이를 예를 들어 설명하라.
SUBSTITUTE는 텍스트 내용 기반 치환이고, REPLACE는 위치 기반 치환이다.
=SUBSTITUTE("010-1234-5678", "-", "")는 모든 하이픈을 제거하여 “01012345678”을 반환한다. 반면=REPLACE("010-1234-5678", 9, 4, "****")는 9번째 위치부터 4글자(“5678”)를 ""로 교체하여 “010-1234-”를 반환한다. 치환할 내용(문자열)을 알 때는 SUBSTITUTE, 치환 위치(시작 위치와 길이)를 알 때는 REPLACE를 사용한다.
OIYO 편집부
Content Editor지식 인큐베이터이자 전문 콘텐츠 크리에이터. 경영, 경제, 법률 및 실생활에 유용한 실무/자격증 중심의 깊이 있는 정보를 연구하고 공유합니다.