2022년 3월 31일 목요일
2022년 3월 30일 수요일
문자함수와 MOS
본 포스팅은 MOS 365 Excel Expert(마스터 필수 과목) 실제 시험에 반드시 출제되는 4대 핵심 기능을 기반으로 작성되었습니다. 아래 제공된 실습 테이블 데이터를 엑셀에 그대로 복사해서 넣으시면, 블로그를 보면서 바로 실습 수업을 진행하실 수 있습니다!
1. 고급 수식 및 함수 활용 (가장 배점 높음)
주어진 조건에 맞는 함수를 정확한 인수로 작성하는 문제가 반드시 출제됩니다.
🔹 [유형 1] XLOOKUP 함수 활용
[시험 시나리오]"인사현황"워크시트의I5셀에 함수를 사용하여B5셀의 사원번호에 해당하는 직급을"급여기준"워크시트의B3:C12범위에서 찾아 표시하시오.
📍 실습 데이터 테이블
아래 표를 엑셀의 각각 다른 시트(
인사현황, 급여기준)에 복사하여 구성하세요.[인사현황 워크시트]
| 사원번호 (B열) | 성명 (C열) | 부서 (D열) | 직급 (I열) — [수식 입력 위치] |
|---|---|---|---|
| M365-01 | 김철수 | 인사팀 | 여기에 수식 입력 |
| M365-02 | 이영희 | 기획팀 | (채우기 핸들로 드래그) |
| M365-03 | 박민수 | 영업팀 | (채우기 핸들로 드래그) |
[급여기준 워크시트]
| 사원번호 (B3:B12 범위) | 직급 (C3:C12 범위) | 기본급 |
|---|---|---|
| M365-01 | 대리 | 3,500,000 |
| M365-02 | 과장 | 4,500,000 |
| M365-03 | 부장 | 5,500,000 |
🔑 정답 및 해설 (I5 셀 입력)
excel
=XLOOKUP(B5, 급여기준!$B$3:$B$12, 급여기준!$C$3:$C$12)
- 구조:
=XLOOKUP(찾을_값, 찾을_범위, 가져올_범위) - 강의 팁: 과거 VLOOKUP과 달리 참조 범위가 서로 떨어져 있어도 열 번호를 셀 필요가 없어 편리합니다. 수식을 아래로 채워 넣어야 하므로 참조 범위에 절대참조(
$)를 지정하는 습관이 중요합니다.
🔹 [유형 2] 조건부 함수 (SUMIFS) 활용
[시험 시나리오]"매출내역"워크시트의M2셀에 지점이"서울"이면서 판매품목이"모니터"인 총 매출액의 합계를 구하는 수식을 작성하시오.
📍 실습 데이터 테이블
[매출내역 워크시트]
| 지점 (B열) | 판매품목 (C열) | 수량 (D열) | 매출액 (E열) |
|---|---|---|---|
| 서울 | 모니터 | 5 | 1,500,000 |
| 부산 | 노트북 | 2 | 3,000,000 |
| 서울 | 키보드 | 10 | 500,000 |
| 경기 | 모니터 | 3 | 900,000 |
| 서울 | 모니터 | 4 | 1,200,000 |
🔑 정답 및 해설 (M2 셀 입력)
excel
=SUMIFS(E2:E6, B2:B6, "서울", C2:C6, "모니터")
- 구조:
=SUMIFS(합계를_구할_범위, 조건범위1, 조건1, 조건범위2, 조건2, ...) - 강의 팁: 단수형 조건 함수인
SUMIF와 다르게SUMIFS는 합계를 구할 범위가 가장 맨 앞에 위치합니다. 학생들이 가장 많이 실수하는 영역이니 주의 깊게 지도해 주세요.
🔹 [유형 3] 논리 함수 (IF, AND) 활용
[시험 시나리오]"평가점수"워크시트의G열에 1차 점수(C열)와 2차 점수(D열)가 모두 80점 이상이면"합격", 그렇지 않으면"재시험"이 표시되도록 논리 함수를 사용하여 수식을 완성하시오.
📍 실습 데이터 테이블
[평가점수 워크시트]
| 성명 (B열) | 1차 점수 (C열) | 2차 점수 (D열) | 결과 (G열) — [수식 입력 위치] |
|---|---|---|---|
| 홍길동 | 85 | 90 | 기에 수식 입력 |
| 강감찬 | 75 | 95 | (결과: 재시험 자동 출력) |
| 이순신 | 90 | 82 | (결과: 합격 자동 출력) |
🔑 정답 및 해설 (G5 셀 입력)
excel
=IF(AND(C5>=80, D5>=80), "합격", "재시험")
- 구조:
=IF(AND(조건1, 조건2), 참일_때_값, 거짓일_때_값) - 강의 팁: 콤마(
,) 누락이나 큰따옴표("") 오타로 감점되는 사례가 빈번합니다. 인수를 채워 넣을 때 하단에 뜨는 도움말 상자를 실시간으로 체크하는 훈련이 필요합니다.
2. 고급 차트 및 피벗테이블 관리
데이터 분석 기능의 세부 옵션을 설정하는 고난도 유형입니다. 아래 통합 원본 데이터를 활용해 실습을 구성합니다.
📍 실습 데이터 테이블
[매출분석 / 분기별 그래프 워크시트 통합 원본 데이터]
| 날짜 (A열) | 지역 (B열) | 상품명 (C열) | 매출액 (D열) | 예상 매출 (E열) |
|---|---|---|---|---|
| 2026-01-10 | 서울 | 모니터 | 1,500,000 | 1,600,000 |
| 2026-01-15 | 부산 | 노트북 | 3,000,000 | 2,800,000 |
| 2026-02-05 | 서울 | 키보드 | 500,000 | 600,000 |
| 2026-02-20 | 경기 | 모니터 | 900,000 | 1,000,000 |
| 2026-03-02 | 부산 | 모니터 | 1,200,000 | 1,100,000 |
🔹 [유형 1] 피벗테이블 그룹화 및 계산 방식 변경
[시험 시나리오]"매출분석"워크시트의 피벗테이블에서'날짜'필드를'월'단위로 그룹화하고, 값 영역의'매출액'필드를'합계'대신'평균'으로 표시되도록 설정을 변경하시오.
🔑 합격 정답 가이드
- 날짜 그룹화: 피벗테이블 내부의 임의의 '날짜' 데이터 셀 우클릭 ➡️ [그룹] 클릭 ➡️ '월'만 파랗게 선택된 상태로 확인.
- 요약 방식 변경: 값 영역에 있는 '매출액' 데이터 셀 우클릭 ➡️ [값 요약 기준] ➡️ [평균] 변경.
🔹 [유형 2] 피벗테이블 슬라이서 삽입 및 서식 설정
[시험 시나리오] 기존 피벗테이블에'지역'필드를 이용한 슬라이서를 삽입하고, 슬라이서의 열 개수를3으로 설정한 뒤"분석 슬라이서"라는 이름의 스타일을 적용하시오.
🔑 합격 정답 가이드
- 피벗테이블 내부 클릭 ➡️ 상단 메뉴 [피벗테이블 분석] 탭 ➡️ [슬라이서 삽입] 클릭 ➡️
'지역'체크 후 확인. - 생성된 슬라이서 테두리 클릭 ➡️ 상단 [슬라이서] 탭 ➡️ 우측 단추 그룹에서 '열' 개수를
3으로 변경. - [슬라이서] 탭 ➡️ '슬라이서 스타일' 목록 열기 ➡️ 문제에서 지정한 이름인 "분석 슬라이서" 마우스 오버 후 정확히 선택.
🔹 [유형 3] 이중 축 혼합형 차트 수정
[시험 시나리오]"분기별 그래프"워크시트의 기본 차트에'예상 매출'데이터 계열을 추가하고, 이 계열의 차트 종류를'표식이 있는 꺾은선형'으로 변경한 후'보조 축'으로 지정하시오.
🔑 합격 정답 가이드
- 원본 데이터의 '예상 매출' 데이터 영역을 드래그하여 복사(Ctrl+C) ➡️ 작성되어 있는 기본 차트 영역을 마우스로 한 번 클릭 ➡️ 붙여넣기(Ctrl+V)를 통해 계열을 추가합니다.
- 차트 영역 우클릭 ➡️ [계열 차트 종류 변경] 메뉴 이동.
- 가장 아래 [콤보] 메뉴 선택 ➡️ '예상 매출' 항목의 차트 종류를 [표식이 있는 꺾은선형]으로 변경하고 바로 옆 [보조 축] 체크박스에 체크 후 확인.
3. 데이터 유효성 검사 및 고급 필터
🔹 [유형 1] 데이터 유효성 검사 및 오류 메시지
[시험 시나리오]"예약목록"워크시트의C5:C20범위에 1부터 10 사이의 정수만 입력되도록 데이터 유효성 검사를 설정하고, 범위를 벗어난 값이 입력될 경우"오류: 1~10 사이의 숫자만 입력 가능합니다."라는 오류 메시지가 나타나도록 하시오.
📍 실습 데이터 테이블
[예약목록 워크시트]
| 순번 (B열) | 고객명 (C열) | 예약인원 (C5:C20) — [유효성 검사 제한 설정 영역] |
|---|---|---|
| 1 | 김고객 | 여기에 1~10 사이 숫자를 입력하는 실습 진행 |
| 2 | 이고객 | 만약 11 이상의 숫자를 치면 오류창이 뜨는지 테스트 |
🔑 합격 정답 가이드
C5:C20데이터 범위를 먼저 마우스로 드래그하여 블록을 설정합니다.- 상단 [데이터] 탭 ➡️ [데이터 유효성 검사] 아이콘 클릭.
- [설정] 탭: 제한 대상을 [정수]로 변경 ➡️ 제한 방법을 [해당 범위] 선택 ➡️ 최솟값
1, 최댓값10입력. - [오류 메시지] 탭: 스타일은 '중지' 상태 유지 ➡️ 오류 메시지 내용 상자에 공백 및 오타 없이
"오류: 1~10 사이의 숫자만 입력 가능합니다."문구를 정확히 입력 후 확인.
🔹 [유형 2] 고급 필터 (복수 조건 추출)
[시험 시나리오]"전체명단"워크시트에서 나이가"30 이상"이면서 근무지가"부산"인 행만 필터링하여, 그 결과를A35셀부터 표시되도록 고급 필터를 적용하시오. (※ 조건은F1:G2범위에 작성되어 있음)
📍 실습 데이터 테이블
[전체명단 워크시트 (원본 데이터 A1:D5)]
| 성명 (A열) | 나이 (B열) | 근무지 (C열) | 직급 (D열) |
|---|---|---|---|
| 홍길동 | 28 | 서울 | 사원 |
| 김선달 | 35 | 부산 | 과장 |
| 임꺽정 | 42 | 인천 | 부장 |
| 성춘향 | 31 | 부산 | 대리 |
[조건 범위 세팅 내용 (F1:G2)]
| 나이 (F1) | 근무지 (G1) |
|---|---|
| >=30 | 부산 |
🔑 합격 정답 가이드
- 원본 데이터 내부의 셀을 하나 클릭한 채로 상단 [데이터] 탭 ➡️ [고급] 필터 버튼 클릭.
- 고급 필터 창이 열리면 결과 위치를 [다른 장소에 복사] 버튼에 체크 변경.
- 조건 범위: 마우스로
F1:G2영역 드래그 선택. - 복사 위치: 기존 주소를 지우고
A35셀을 마우스로 클릭한 뒤 확인 버튼 클릭.
(※ 결과 위치인 A35 셀부터 김선달, 성춘향의 행 데이터가 정상적으로 추출되면 성공입니다!)
4. 통합 문서 옵션, 보안 및 매크로
🔹 [유형 1] 수식 숨기기 및 시트 보호
[시험 시나리오]"보안"워크시트의 모든 수식 셀을 숨기고, 사용자가 시트의 내용을 수정할 수 없도록 시트를 보호하시오. (단, 암호는 지정하지 않음)
📍 실습 데이터 테이블
[보안 워크시트]
| 항목 (A열) | 수량 (B열) | 단가 (C열) | 금액 (D열) — [수식 셀] |
|---|---|---|---|
| A형 상품 | 10 | 1,500 | =B2*C2 |
| B형 상품 | 5 | 3,000 | =B3*C3 |
🔑 합격 정답 가이드
- 수식이 포함된 셀들만 빠르게 찾기 위해 상단 [홈] 탭 ➡️ 맨 우측 [찾기 및 선택] ➡️ [이동 옵션] 클릭 후 [수식]에 체크하고 확인을 누릅니다.
- 수식 셀들만 블록이 잡힌 상태에서 키보드
Ctrl + 1(셀 서스 창 열기) ➡️ 맨 우측 [보호] 탭으로 이동 ➡️ [숨김] 체크박스에 체크 후 확인. - 상단 [검토] 탭 ➡️ [시트 보호] 클릭 ➡️ 암호 입력창을 비워둔 채 그대로 하단 [확인] 버튼 클릭.
🔹 [유형 2] 도형에 기존 매크로 지정하기
[시험 시나리오]"출력"워크시트에 있는'인쇄하기'도형 버튼을 클릭했을 때, 기존에 작성되어 있는'Print_Format'매크로가 실행되도록 매크로를 지정하시오.
🔑 합격 정답 가이드
- 워크시트에 배치되어 있는
'인쇄하기'도형 아이콘 위에서 마우스 우클릭. - 바로가기 메뉴 중 [매크로 지정] 메뉴 선택.
- 매크로 지정 대화상자가 열리면 매크로 이름 목록 중에서 문제에 명시된
Print_Format을 마우스로 정확히 선택한 후 [확인] 클릭.
함수
1. FILTER 함수 (원하는 데이터만 쏙 뽑아내기)
기존의 번거로운 '고급 필터'나 '자동 필터' 메뉴를 누를 필요 없이, 함수 수식 단 하나로 조건에 맞는 원본 데이터를 실시간으로 추출하는 혁신적인 함수입니다.
🔹 [실습 시나리오]
"영업실적"워크시트에서 부서가"영업1팀"인 직원들의 성명, 부서, 실적 데이터를F5셀부터 자동으로 추출되도록 수식을 작성하시오.
📍 실습 데이터 테이블
[영업실적 워크시트] (A4:C9 영역에 원본 배치)
| 성명 (A열) | 부서 (B열) | 실적 (C열) | 추출 결과 위치 (F5 셀) — [수식 입력] | ||
|---|---|---|---|---|---|
| 김철수 | 영업1팀 | 4,500,000 | =FILTER(A5:C9, B5:B9="영업1팀", "데이터 없음") | ||
| 이영희 | 영업2팀 | 3,800,000 | (엔터를 누르면 하단 영역까지 결과가 자동 스필(Spill)됨) | ||
| 박민수 | 영업1팀 | 5,200,000 | |||
| 최서연 | 영업3팀 | 2,900,000 | |||
| 정우진 | 영업1팀 | 4,100,000 |
🔑 정답 및 해설 (F5 셀 입력)
excel
=FILTER(A5:C9, B5:B9="영업1팀", "데이터 없음")
- 구조:
=FILTER(가져올_전체범위, 조건범위=조건, [조건에_맞는_데이터가_없을_때_표시할_문구]) - 강의 팁: FILTER 함수는 결과를 출력할 첫 번째 셀(
F5)에만 수식을 입력하면, 조건에 맞는 행 개수만큼 데이터가 아래로 자동 확장(Spill, 동적 배열)됩니다. 결과가 뻗어나갈 자리에 다른 텍스트가 적혀 있으면#SPILL!오류가 발생하므로 주변 셀을 비워두어야 함을 강조해 주세요.
2. SORTBY 함수 (지정한 열 기준으로 스마트하게 정렬하기)
원본 데이터의 순서를 직접 뒤흔들지 않고, 내가 원하는 특정 열의 기준(오름차순/내림차순)에 맞추어 정렬된 새로운 테이블을 실시간으로 만들어주는 함수입니다.
🔹 [실습 시나리오]
"인사평가"워크시트의 전체 데이터를 종합점수가 높은 사람부터 순서대로(내림차순) 정렬하여F5셀부터 표시되도록 수식을 작성하시오.
📍 실습 데이터 테이블
[인사평가 워크시트] (A4:C8 영역에 원본 배치)
| 사원명 (A열) | 직급 (B열) | 종합점수 (C열) | 정렬 결과 위치 (F5 셀) — [수식 입력] | ||
|---|---|---|---|---|---|
| 홍길동 | 대리 | 85 | =SORTBY(A5:C8, C5:C8, -1) | ||
| 강감찬 | 과장 | 92 | (가장 점수가 높은 강감찬 과장 행이 맨 위로 자동 정렬되어 출력) | ||
| 이순신 | 부장 | 95 | |||
| 임꺽정 | 사원 | 78 |
🔑 정답 및 해설 (F5 셀 입력)
excel
=SORTBY(A5:C8, C5:C8, -1)
- 구조:
=SORTBY(정렬할_전체범위, 기준열_범위, 정렬기준)- 정렬기준 인수:
1은 오름차순(낮은 순),-1은 내림차순(높은 순)
- 정렬기준 인수:
- 강의 팁: 기존 SORT 함수는 몇 번째 열인지를 숫자로 지정해야 해서 헷갈렸지만, SORTBY는 정렬 기준이 될 범위(
C5:C8)를 마우스로 직접 드래그하면 되기 때문에 실무 실수를 크게 줄여줍니다.
3. IFS 함수 (복잡한 IF 중첩 대신 한 줄로 깔끔하게 정리하기)
과거에 조건이 많아질수록
=IF(조건1, 참, IF(조건2, 참, IF(...))) 처럼 끝없이 괄호를 닫아야 했던 복잡한 중첩 IF 수식을 완전히 대체하는 M365 필수 함수입니다.🔹 [실습 시나리오]
"성적표"워크시트의D5셀에 점수별 등급 기준표를 참고하여 등급을 산출하는 수식을 작성하시오.
(기준: 90점 이상 "A", 80점 이상 "B", 70점 이상 "C", 그 외/70점 미만은 "F")
📍 실습 데이터 테이블
[성적표 워크시트]
| 학생명 (B열) | 점수 (C열) | 등급 (D열) — [수식 입력 위치] |
|---|---|---|
| 이대한 | 93 | =IFS(C5>=90, "A", C5>=80, "B", C5>=70, "C", TRUE, "F") |
| 김민국 | 74 | (결과: C 자동 출력) |
| 박만세 | 65 | (결과: F 자동 출력) |
| 최우리 | 82 | (결과: B 자동 출력) |
🔑 정답 및 해설 (D5 셀 입력 후 아래로 드래그)
excel
=IFS(C5>=90, "A", C5>=80, "B", C5>=70, "C", TRUE, "F")
- 구조:
=IFS(조건1, 결과1, 조건2, 결과2, 조건3, 결과3, ..., TRUE, 나머지_모든_경우의_결과) - 강의 팁: IFS 함수는 맨 앞에서부터 조건을 검사하며 만족하는 순간 멈춥니다. 따라서 수식을 짤 때는 반드시 점수가 높은 조건(90점 이상)부터 순서대로 내려가야 꼬이지 않습니다. 마지막에 어떤 조건도 만족하지 않는 나머지 전체를 처리할 때는
TRUE를 적고 결과를 입력한다는 점이 핵심 합격 포인트입니다!
📊 기출 함수 정의 및 구문 일람표
| 함수명 | 분류 | 핵심 용도 및 기능 | 기본 구문 (인수 구조) |
|---|---|---|---|
| XLOOKUP | 찾기/참조 | 지정한 범위에서 특정 값을 찾아 같은 행/열의 데이터를 추출 | =XLOOKUP(찾을_값, 찾을_범위, 가져올_범위, [없을_때_값], [일치_모드]) |
| VLOOKUP | 찾기/참조 | 표의 첫 번째 열에서 값을 찾아 지정한 열 번호의 데이터를 추출 | =VLOOKUP(찾을_값, 참조_범위, 열_번호, [정확도_여부]) |
| SUMIFS | 수학/삼각 | 여러 개의 조건을 동시에 만족하는 셀들의 합계를 계산 | =SUMIFS(합계를_구할_범위, 조건범위1, 조건1, 조건범위2, 조건2, ...) |
| COUNTIFS | 통계 | 여러 개의 조건을 동시에 만족하는 셀들의 개수를 계산 | =COUNTIFS(조건범위1, 조건1, 조건범위2, 조건2, ...) |
| IF | 논리 | 조건이 참(True)일 때와 거짓(False)일 때 각각 다른 값을 반환 | =IF(조건식, 참일_때_결과, 거짓일_때_결과) |
| AND | 논리 | 나열된 모든 조건이 참(True)일 때만 최종 참(True)을 반환 | =AND(조건1, 조건2, ...) |
| OR | 논리 | 나열된 조건 중 하나라도 참(True)이면 최종 참(True)을 반환 | =OR(조건1, 조건2, ...) |
| CONCAT | 텍스트 | 여러 셀의 문자열이나 텍스트를 하나로 결합 (기존 CONCATENATE 대체) | =CONCAT(텍스트1, 텍스트2, ...) |
| IFS | 논리 | 여러 조건을 나열하여 처음으로 만족하는 조건의 결과를 반환 | =IFS(조건1, 결과1, 조건2, 결과2, ..., TRUE, 나머지_경우_결과) |
| FILTER | 동적 배열 | 원본 테이블에서 설정한 조건에 맞는 데이터 행만 실시간 추출 | =FILTER(전체_범위, 조건_범위=조건, [데이터가_없을_때_문구]) |
| SORTBY | 동적 배열 | 원본 데이터를 손대지 않고 특정 기준 열에 맞춰 자동으로 정렬 | =SORTBY(전체_범위, 기준열_범위, 정렬_정수) (1:오름차순, -1:내림차순) |
피드 구독하기:
글 (Atom)
