팟캐스트
스크립트
보고서 요약: MOS 엑셀 Expert 함수 핵심 정리
본 보고서는 제공된 자료를 바탕으로 MOS 엑셀 Expert 시험 대비를 위한 주요 함수와 개념을 정리합니다. 날짜 및 시간 함수, 논리 함수, 수학/삼각/통계 함수, 찾기/참조 함수, 재무 함수를 중심으로 시험 출제 경향, 핵심 기능 및 사용 시 주의사항을 다룹니다.
1. 날짜 및 시간 함수
- 핵심 함수: TODAY(), NOW(), YEAR(), MONTH(), DAY(), WEEKDAY()
- 출제 경향: 이 함수들은 시험에 반드시 출제되며, 특히 현재 날짜/시간, 특정 날짜에서 연/월/일 추출, 요일 코드 구하는 문제가 중요하게 다뤄집니다. (96페이지 내용 중요)
- 주요 기능 및 활용:TODAY(): 현재 시스템의 날짜를 표시합니다. 인수가 필요 없습니다. =TODAY() 형태로 사용합니다.
- NOW(): 현재 시스템의 날짜와 시간을 모두 표시합니다. 인수가 필요 없습니다. =NOW() 형태로 사용합니다.
- YEAR(날짜): 특정 날짜에서 연도를 추출합니다.
- MONTH(날짜): 특정 날짜에서 월을 추출합니다.
- DAY(날짜): 특정 날짜에서 일을 추출합니다.
- YEAR() 함수 활용: YEAR(NOW())처럼 사용하여 현재 연도를 구할 수 있습니다.
- WEEKDAY(날짜, [옵션]): 특정 날짜의 요일을 숫자로 반환합니다.
- 옵션 생략 또는 1: 일요일(1) ~ 토요일(7) 반환 (기본)
- 옵션 2: 월요일(1) ~ 일요일(7) 반환
- 시험에서 "일요일이 숫자 1이다" 또는 "월요일이 1번이다"와 같은 요구사항에 따라 옵션을 지정해야 합니다.
- 요일 코드 표시 형식 변경: WEEKDAY 함수의 결과(숫자)를 셀 서식(Ctrl + 1)의 사용자 지정을 통해 "aaa" (한글 요일 축약), "aaaa" (한글 요일 전체), "ddd" (영문 요일 축약), "dddd" (영문 요일 전체) 등으로 표시할 수 있습니다.
- 주의사항: 엑셀에서 날짜와 시간은 숫자 값(일련 번호)으로 계산되지만, 사용자 편의를 위해 셀 서식을 통해 우리가 알아보는 형식으로 표시됩니다.
2. 논리 함수
- 핵심 함수: IF(), AND(), OR()
- 출제 경향: IF, AND, OR 함수는 시험에서 매우 중요하며 안 쓸래야 안 쓸 수가 없는 함수입니다. 특히 IF 함수와 논리 함수(AND, OR)를 조합하여 사용하는 중첩 함수 문제가 출제됩니다.
- 주요 기능 및 활용:IF(조건, 참일 때 값, 거짓일 때 값): 조건식이 참(TRUE) 또는 거짓(FALSE)인지 판정하여 다른 값을 표시합니다.
- AND(조건1, [조건2], ...): 괄호 안의 모든 조건이 참일 때만 참(TRUE)을 반환합니다. "
이고", "이면서", "그리고"와 같은 조건에 사용됩니다. - OR(조건1, [조건2], ...): 괄호 안의 조건 중 하나라도 참이면 참(TRUE)을 반환합니다. "~~이거나", "또는"과 같은 조건에 사용됩니다.
- 중첩 함수: IF 함수 안에 AND나 OR 함수를 넣어 여러 조건을 복합적으로 판단하는 문제를 해결할 수 있습니다. 복잡한 논리 함수 사용 시 함수 마법사(Ctrl + A 또는 Fx 버튼)를 활용하는 것이 오류를 줄이는 데 도움이 됩니다.
- 주의사항: 논리 함수는 결과적으로 TRUE 또는 FALSE 값을 반환하며, 이를 기반으로 IF 함수 등에서 원하는 결과 값을 표시할 수 있습니다. AND와 OR 함수는 결과적으로 TRUE/FALSE만 반환하므로, 특정 값이나 텍스트를 결과로 얻으려면 IF 함수와 함께 사용해야 합니다.
3. 수학/삼각/통계 함수 (조건부 함수)
- 핵심 함수: SUMIF(), SUMIFS(), AVERAGEIF(), AVERAGEIFS(), COUNTIF(), COUNTIFS(), COUNT(), COUNTA(), COUNTBLANK()
- 출제 경향: IF 또는 IFS가 붙는 조건부 함수들은 시험에 각각 한 문제씩 출제되며, 두 문제 이상 반드시 출제됩니다. COUNT, COUNTA, COUNTBLANK 함수의 차이점과 활용 문제도 중요합니다.
- 공통 특징: IF 또는 IFS가 붙는 함수는 특정 조건을 만족하는 데이터에 대해서만 합계, 평균, 개수를 계산합니다.
- 주요 기능 및 활용:SUMIF(조건 범위, 조건, [합계 범위]): 단일 조건을 만족하는 범위의 합계를 구합니다.
- SUMIFS(합계 범위, 조건1 범위, 조건1, [조건2 범위, 조건2, ...]): 두 개 이상의 조건을 모두 만족하는 범위의 합계를 구합니다. SUMIF와 달리 합계 범위 인수가 먼저 옵니다.
- AVERAGEIF(조건 범위, 조건, [평균 범위]): 단일 조건을 만족하는 범위의 평균을 구합니다.
- AVERAGEIFS(평균 범위, 조건1 범위, 조건1, [조건2 범위, 조건2, ...]): 두 개 이상의 조건을 모두 만족하는 범위의 평균을 구합니다. AVERAGEIF와 달리 평균 범위 인수가 먼저 옵니다.
- COUNTIF(조건 범위, 조건): 단일 조건을 만족하는 셀의 개수를 셉니다. 계산 범위 인수가 없습니다. 조건 범위 자체가 개수를 셀 범위가 됩니다.
- COUNTIFS(조건1 범위, 조건1, [조건2 범위, 조건2, ...]): 두 개 이상의 조건을 모두 만족하는 셀의 개수를 셉니다. 계산 범위 인수가 없습니다.
- COUNT 계열 함수:COUNT(범위): 범위 내에서 숫자가 포함된 셀의 개수를 셉니다.
- COUNTA(범위): 범위 내에서 비어 있지 않은 모든 셀의 개수를 셉니다 (숫자, 텍스트 포함). 텍스트 데이터의 개수를 셀 때 사용합니다.
- COUNTBLANK(범위): 범위 내에서 비어 있는 셀의 개수를 셉니다.
- 와일드카드 (Wildcard): 조건에 사용하는 특수 문자입니다. 시험에 출제됩니다.
- (별표): 모든 문자를 대신하며 글자 수에 제한이 없습니다 (예: "김*": 김으로 시작하는 모든 텍스트).
- ? (물음표): 하나의 문자를 대신합니다 (예: "김?": 김으로 시작하는 두 글자 텍스트).
- 주의사항:수식에 사용되는 텍스트 조건은 반드시 쌍따옴표(")로 묶어야 합니다. "SD3"처럼 셀 주소와 동일한 텍스트를 조건으로 사용할 때, 쌍따옴표를 붙이지 않으면 엑셀이 셀 주소로 인식하여 오류가 발생할 수 있습니다.
- SUMIF, AVERAGEIF, COUNTIF 계열 함수에서 비교 연산자(>, <, >=, <=, <>)를 사용할 때도 조건에 따옴표가 붙는 경우가 있습니다 (예: ">70"). 함수 마법사를 사용하면 이러한 실수를 방지할 수 있습니다.
- SUMIFS, AVERAGEIFS, COUNTIFS 함수는 IFS가 붙지 않는 함수들과 인수의 순서가 다릅니다. 계산 범위가 먼저 옵니다.
4. 찾기/참조 함수
- 핵심 함수: VLOOKUP(), HLOOKUP(), INDEX()
- 출제 경향: VLOOKUP 또는 HLOOKUP 함수 중 한 문제는 무조건 출제됩니다. INDEX 함수도 자주 출제되며, 콤보 상자와 연동하여 데이터를 찾는 문제가 중요합니다.
- 주요 기능 및 활용:VLOOKUP(찾을 값, 찾을 범위, 열 번호, [찾는 방법]): 찾을 값을 표의 첫 번째 열에서 찾아 지정된 열의 값을 가져옵니다. 데이터가 열 방향(수직)으로 나열된 표에 주로 사용됩니다.
- HLOOKUP(찾을 값, 찾을 범위, 행 번호, [찾는 방법]): 찾을 값을 표의 첫 번째 행에서 찾아 지정된 행의 값을 가져옵니다. 데이터가 행 방향(수평)으로 나열된 표에 주로 사용됩니다.
- INDEX(찾을 범위, 행 번호, [열 번호]): 지정된 범위 내에서 지정된 행 및 열 위치에 있는 값을 가져옵니다.
- VLOOKUP/HLOOKUP 사용 시 주의사항:찾을 범위(Table_array): 수식을 아래로 채우거나 옆으로 복사할 때 '찾을 범위'가 변경되지 않도록 반드시 절대 참조($)해야 합니다 (F4 키 활용). 이름으로 정의된 범위는 자동으로 절대 참조됩니다.
- 열/행 번호(Col_index_num/Row_index_num): 찾을 범위의 첫 번째 열(VLOOKUP) 또는 행(HLOOKUP)이 1번부터 시작하여 원하는 값을 가져올 열/행의 번호를 지정합니다.
- 찾는 방법(Range_lookup):TRUE 또는 생략: 비슷하게 일치하는 값을 찾습니다. (정렬된 데이터에 사용)
- FALSE 또는 0: 정확하게 일치하는 값을 찾습니다. (정렬되지 않은 데이터에도 사용 가능)
- 대부분의 경우 정확하게 일치하는 값(FALSE 또는 0)을 찾습니다.
- INDEX 함수 활용: 콤보 상자와 함께 사용될 때, 콤보 상자에 연결된 셀의 값(선택된 항목의 순번)을 행 번호로 사용하여 동적으로 값을 찾아올 수 있습니다.
5. 재무 함수
- 핵심 함수: PMT()
- 출제 경향: PMT 함수는 시험에 반드시 출제됩니다. FV 함수는 출제 가능성이 낮습니다.
- 주요 기능:PMT(이자율, 상환 횟수, 현재 가치, [미래 가치], [상환 시점]): 대출금에 대한 정기적인 상환액(월 납부액)을 계산합니다.
- PMT 함수 사용 시 주의사항:이자율(Rate) 및 상환 횟수(Nper): 월 납부액을 구할 때는 이 두 인수를 반드시 동일한 월 단위로 맞춰야 합니다. 연 이자율은 12로 나누고, 연 기간은 12를 곱해야 합니다.
- 현재 가치(Pv): 빌린 돈에서 초기 납입금 등을 제외한 실제로 갚아야 할 금액을 입력합니다.
- 미래 가치(Fv): 마지막 상환 후 남을 금액입니다. 생략하면 전액 상환(0)으로 간주됩니다.
- 상환 시점(Type): 상환이 이루어지는 시점을 지정합니다.
- 0 또는 생략: 기간 말에 상환
- 1: 기간 초에 상환 문제에서 "매월 초에 납입"과 같은 명시가 있으면 1을 사용해야 합니다.
- PMT 함수의 결과는 돈이 나가는 것을 의미하므로 음수로 표시됩니다.
6. 기타 주요 개념 및 팁
- 함수 (Function): 미리 정의된 수식으로 항상 등호(=)로 시작합니다.
- 인수 (Argument): 함수가 작업을 수행하는 데 필요한 값 또는 참조로, 함수 괄호 안에 입력됩니다.
- 수식 입력줄 (Formula Bar): 선택한 셀의 내용(데이터 또는 수식)을 표시하고 편집하는 영역입니다. 괄호 안 클릭 시 해당 함수로 이동하여 인수를 확인/편집할 수 있습니다.
- 셀 채우기 핸들 (Fill Handle): 선택한 셀의 오른쪽 하단 작은 사각형으로, 드래그 또는 더블 클릭하여 데이터나 수식을 빠르게 채울 수 있습니다. 인접한 열에 데이터가 있을 경우 더블 클릭으로 자동 채우기가 가능합니다.
- 절대 참조 ($): 셀 주소를 고정하여 수식 복사/채우기 시 변경되지 않게 합니다 (예: $A$1). VLOOKUP/HLOOKUP의 '찾을 범위' 인수에 필수적으로 사용됩니다. F4 키로 설정합니다.
- 상대 참조: 수식 복사/채우기 시 셀 주소가 자동으로 변경되는 방식입니다 (예: A1).
- 혼합 참조: 행 또는 열 중 하나만 고정합니다 (예: $A1, A$1).
- 함수 마법사 (Ctrl + A 또는 Fx 버튼): 함수의 인수 입력과 설명을 도와주는 기능입니다. 복잡한 함수나 처음 사용하는 함수 입력 시 유용하며, 괄호가 열린 상태에서 실행합니다.
- 텍스트 입력: 수식 내에 텍스트는 반드시 쌍따옴표(")로 묶어야 합니다. 셀 주소와 동일한 텍스트 사용 시 특히 주의해야 합니다.
- 표 (Table): 구조화된 데이터 범위로, 일반 범위와 달리 하나의 수식 입력 시 전체 열에 자동으로 채워지며 (Ctrl + D/R 필요 없음), 수식에서 표 이름이나 필드 이름을 사용합니다. Ctrl + T로 만들 수 있습니다.
- 콤보 상자 (Combo Box): 드롭다운 목록을 제공하는 컨트롤로, 컨트롤 서식에서 '입력 범위' (목록 데이터)와 '셀 연결' (선택한 항목의 순번을 표시할 셀)을 지정하는 것이 중요합니다. 이 '셀 연결'된 셀의 값은 INDEX 함수의 행 번호 등으로 활용될 수 있습니다.