MOS Expert Excel 자격증 3개월로 끝내기 - 16

 

MOS Excel Expert 자격증 준비 15회차: 약점 보완 및 심화 학습 - 고난도 수식과 매크로 활용 사례

MOS Excel Expert 자격증 준비의 열다섯 번째 회차에서는 학습 과정에서 부족했던 부분을 보완하고, 고난도 수식과 매크로 활용 사례를 통해 심화 학습을 진행합니다. 이번 글에서는 시험에서 자주 출제되는 고급 기능들을 다루며, 실무에서도 바로 활용할 수 있는 고급 기술을 예제와 함께 소개합니다.

1. 학습 목표

• 고난도 수식을 작성하여 복잡한 계산 문제를 해결한다.
• 매크로와 VBA를 활용해 반복 작업을 자동화한다.
• 시험 대비 약점을 보완하고 실력을 강화한다.

반응형

2. 주요 내용

2.1 고난도 수식

예시 문제 1: 배열 수식 활용

문제: 아래 데이터에서 "판매량"이 50 이상인 제품의 총 판매량을 계산하세요.

제품명	판매량
제품 A	45
제품 B	60
제품 C	75

 

풀이:

1. 배열 수식을 사용하여 조건에 맞는 값을 합산합니다.
2. 결과를 표시할 셀(C2)에 아래 수식을 입력한 뒤 Ctrl + Shift + Enter를 눌러 배열 수식을 적용합니다:
    
3. =SUM(IF(B2:B4>=50, B2:B4))
4. 결과:
   • 총 판매량은 135(제품 B와 C의 합계)입니다.

예시 문제 2: INDEX와 MATCH 함수 결합

문제: 아래 데이터에서 "ID"를 기준으로 "부서"를 검색하세요.

ID	이름	부서
101	김철수	영업팀
102	이영희	마케팅팀
103	박민수	개발팀

 

풀이:

1. 검색할 ID가 입력된 셀(A5)에 102를 입력합니다.
2. 부서를 반환할 셀(C5)에 아래 수식을 입력합니다:
    
3. =INDEX(C2:C4, MATCH(A5, A2:A4, 0))
4. 결과:
   • ID 102에 해당하는 부서는 마케팅팀입니다.

예시 문제 3: 중첩 IF 함수로 등급 분류

문제: 아래 데이터에서 판매량 기준으로 등급을 분류하세요:

• 판매량 ≥ 70: "우수"
• 판매량 ≥ 50: "보통"
• 그 외: "미흡"

제품명	판매량	등급
제품 A	45
제품 B	60
제품 C	75

 

풀이:

1. 등급 열(C2)에 아래 수식을 입력합니다:
    
2. =IF(B2>=70, "우수", IF(B2>=50, "보통", "미흡"))
3. 결과:
   • 제품 A: 미흡
   • 제품 B: 보통
   • 제품 C: 우수

728x90

2.2 매크로 활용 사례

예시 문제 4: 반복 작업 자동화

문제: 아래 데이터를 정렬하고 셀 서식을 설정하는 매크로를 작성하세요.

제품명	판매량
제품 A	45
제품 B	60
제품 C	30

 

풀이 (매크로 기록):

1. 개발 도구 탭에서 매크로 기록을 클릭합니다.
2. "데이터 정렬 및 서식"이라는 이름으로 매크로를 생성합니다.
3. 데이터를 선택하고 데이터 > 정렬에서 판매량 기준으로 내림차순 정렬합니다.
4. 정렬된 데이터의 셀 서식을 굵게 설정하고 배경색을 노란색으로 변경합니다.
5. 기록 중지를 클릭하여 완료합니다.

풀이 (VBA 코드):

Sub FormatAndSort() Range("A1:B4").Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlYes With Range("A1:B4") .Font.Bold = True .Interior.Color = RGB(255, 255, 0) End With End Sub

1. VBA 편집기를 열고 위 코드를 입력합니다:
    
2. 저장 후 실행하면 데이터가 정렬되고 서식이 적용됩니다.

예시 문제 5: 사용자 정의 함수(UDF)

문제: 두 숫자의 평균을 계산하는 사용자 정의 함수를 작성하세요.

풀이:

Function CalculateAverage(num1 As Double, num2 As Double) As Double CalculateAverage = (num1 + num2) / 2 End Function

1. VBA 편집기를 열고 위 코드를 작성합니다:
    
2. 저장 후 Excel에서 아래와 같은 방식으로 사용자 정의 함수를 사용합니다:
    
3. =CalculateAverage(10, 20)
4. 결과:
   • 평균 값은 15입니다.

SMALL

2.3 추가 예제

예시 문제: 날짜 및 시간 함수 심화

문제: 프로젝트 시작일과 종료일이 주어졌을 때 총 작업 일수를 계산하고 주말과 공휴일을 제외한 실제 작업 일수를 구하세요.

프로젝트명시작일종료일공휴일

프로젝트명	시작일	종료일	공휴일
프로젝트 A	2025-03-01	2025-03-30	2025-03-15

 

풀이 (총 작업 일수):

1. 총 작업 일수를 계산할 셀(D2)에 아래 수식을 입력합니다:
    
   =DATEDIF(B2, C2, "D")

풀이 (실제 작업 일수):

1. 실제 작업 일수를 계산할 셀(E2)에 아래 수식을 입력합니다:
    
   =NETWORKDAYS(B2, C2, D4)

결과:

• 총 작업 일수: 29
• 실제 작업 일수: 21 (주말과 공휴일 제외)

3. 추가 팁

고난도 수식 연습 방법

• 자주 사용하는 함수(IF, VLOOKUP, INDEX 등)를 조합하여 복잡한 조건을 처리하는 연습을 하세요.
• 배열 수식과 동적 참조를 활용해 데이터를 효율적으로 처리하는 방법을 익히세요.

매크로와 VBA 활용 팁

• 반복적으로 수행하는 작업은 매크로로 기록하거나 VBA 코드를 작성해 자동화하세요.
• VBA 디버깅 도구(중단점 설정, 단계별 실행)를 사용해 코드를 점검하세요.

실무 적용 사례

• 보고서 자동 생성(데이터 입력 및 서식 설정).
• 대량 데이터 처리(정렬, 필터링, 요약).
• 프로젝트 일정 관리(작업 기간 계산 및 시각화).