엑셀 함수 완벽 가이드 🔍 기초부터 실무까지 모든 함수 총정리

엑셀 함수 완벽 가이드

기초부터 실무 활용까지, 엑셀 함수의 모든 것

목차

• 1. 엑셀 함수 소개
• 2. 수학/계산 함수
• 3. 논리 함수
• 4. 검색 및 참조 함수
• 5. 텍스트 함수
• 6. 날짜 및 시간 함수
• 7. 통계 함수
• 8. 재무 함수
• 9. 효율적인 함수 사용 팁

1. 엑셀 함수 소개

엑셀 함수는 복잡한 계산이나 데이터 분석을 쉽게 할 수 있도록 도와주는 미리 정의된 수식입니다. 엑셀에는 수백 개의 내장 함수가 있으며, 이를 통해 단순한 합계 계산부터 복잡한 통계 분석까지 다양한 작업을 자동화할 수 있습니다.

모든 엑셀 함수는 =함수이름(인수1, 인수2, ...) 형태로 작성됩니다. 인수는 함수가 작동하는 데 필요한 값이나 셀 참조입니다.

함수 작성 기본 규칙

• 모든 함수는 등호(=)로 시작합니다.
• 함수명 뒤에는 괄호가 필요하며, 괄호 안에 인수를 입력합니다.
• 여러 인수는 쉼표로 구분합니다.
• 인수는 숫자, 텍스트, 셀 참조, 범위, 다른 함수 등이 될 수 있습니다.

2. 수학/계산 함수

엑셀의 가장 기본적이고 많이 사용되는 함수 중 하나는 수학적 계산을 수행하는 함수들입니다. 이들 함수는 간단한 합계부터 복잡한 공학적 계산까지 다양한 수학 작업을 수행할 수 있습니다.

SUM 함수

여러 셀이나 범위의 합계를 계산합니다.

=SUM(숫자1, [숫자2], ...)

예시:

=SUM(A1:A10) - A1부터 A10까지의 모든 숫자의 합계를 구합니다.
=SUM(A1:A5, C1:C5) - 두 범위에 있는 모든 숫자의 합계를 구합니다.

실무 팁: 자동 합계를 빠르게 구하려면 범위를 선택한 후 Alt + = 단축키를 사용하세요.

SUMIF 함수

특정 조건을 만족하는 셀들의 합계를 계산합니다.

=SUMIF(조건범위, 조건, [합계범위])

예시:

=SUMIF(B1:B10, "사과", C1:C10) - B열에서 "사과"인 항목에 해당하는 C열 값들의 합계를 구합니다.
=SUMIF(B1:B10, ">100", B1:B10) - B열에서 100보다 큰 값들의 합계를 구합니다.

AVERAGE 함수

여러 셀이나 범위의 평균을 계산합니다.

=AVERAGE(숫자1, [숫자2], ...)

예시:

=AVERAGE(A1:A10) - A1부터 A10까지의 숫자의 평균을 구합니다.

3. 논리 함수

논리 함수는 조건에 따라 다른 값을 반환하거나 여러 조건을 평가하는 데 사용됩니다. 이러한 함수들은 데이터 분석과 의사 결정 과정에서 매우 유용합니다.

IF 함수

조건을 평가하고, 참이면 하나의 값을, 거짓이면 다른 값을 반환합니다.

=IF(조건, 참일_때_값, 거짓일_때_값)

예시:

=IF(A1>80, "합격", "불합격") - A1의 값이 80보다 크면 "합격", 그렇지 않으면 "불합격"을 반환합니다.

다중 조건 사용하기: IF 함수를 중첩하여 다중 조건을 평가할 수 있습니다.
=IF(A1>90, "A", IF(A1>80, "B", IF(A1>70, "C", "F")))

AND, OR 함수

여러 조건을 함께 평가합니다.

=AND(조건1, [조건2], ...)
=OR(조건1, [조건2], ...)

예시:

=AND(A1>70, A1<90) - A1의 값이 70보다 크고 90보다 작으면 TRUE를 반환합니다.
=OR(A1<60, A1>90) - A1의 값이 60보다 작거나 90보다 크면 TRUE를 반환합니다.

IF와 함께 사용하기:
=IF(AND(A1>70, A1<90), "B등급", "다른 등급")

4. 검색 및 참조 함수

검색 및 참조 함수는 테이블이나 범위에서 데이터를 검색하는 데 사용됩니다. 이런 함수들은 대규모 데이터셋에서 특정 정보를 찾을 때 매우 유용합니다.

VLOOKUP 함수

테이블의 첫 번째 열에서 값을 찾아 해당 행의 다른 열 값을 반환합니다.

=VLOOKUP(찾을값, 테이블범위, 열번호, [일치유형])

일치유형:

• TRUE 또는 생략: 근사 일치 (찾을값보다 작거나 같은 값 중 가장 큰 값 찾기)
• FALSE: 정확히 일치하는 값만 찾기

예시:

=VLOOKUP("사과", A1:C10, 2, FALSE) - A1:A10 범위에서 "사과"를 찾아 같은 행의 B열 값을 반환합니다.

실무 팁: VLOOKUP 사용 시 찾을 값이 항상 테이블의 첫 번째 열에 있어야 합니다. 다른 열에서 찾아야 할 경우 INDEX와 MATCH 함수 조합을 사용하세요.

HLOOKUP 함수

VLOOKUP의 가로 버전으로, 테이블의 첫 번째 행에서 값을 찾아 해당 열의 다른 행 값을 반환합니다.

=HLOOKUP(찾을값, 테이블범위, 행번호, [일치유형])

INDEX와 MATCH 함수

더 유연한 검색 기능을 제공하는 함수 조합입니다.

=INDEX(배열, MATCH(찾을값, 검색범위, [일치유형]))

예시:

=INDEX(B2:D10, MATCH("사과", A2:A10, 0), 2) - A열에서 "사과"를 찾아 해당 행의 C열 값을 반환합니다.

VLOOKUP보다 나은 점: INDEX-MATCH 조합은 찾을 값이 첫 번째 열에 있지 않아도 되며, 오른쪽뿐만 아니라 왼쪽 열의 값도 반환할 수 있습니다.

5. 텍스트 함수

텍스트 함수는 문자열을 조작하고 처리하는 데 사용됩니다. 이러한 함수들은 데이터 정리와 형식 지정에 유용합니다.

CONCAT 함수

여러 텍스트 문자열을 하나로 결합합니다.

=CONCAT(텍스트1, [텍스트2], ...)

예시:

=CONCAT("안녕하세요, ", A1, "님!") - A1 셀의 이름과 인사말을 결합합니다.

LEFT, MID, RIGHT 함수

문자열의 일부를 추출합니다.

=LEFT(텍스트, 문자수)
=MID(텍스트, 시작위치, 문자수)
=RIGHT(텍스트, 문자수)

예시:

=LEFT("안녕하세요", 2) - "안녕"을 반환합니다.
=MID("안녕하세요", 3, 2) - "하세"를 반환합니다.
=RIGHT("안녕하세요", 1) - "요"를 반환합니다.

UPPER, LOWER, PROPER 함수

텍스트의 대소문자를 변환합니다.

=UPPER(텍스트) - 모두 대문자로 변환
=LOWER(텍스트) - 모두 소문자로 변환
=PROPER(텍스트) - 각 단어의 첫 글자만 대문자로 변환

6. 날짜 및 시간 함수

날짜 및 시간 함수는 날짜와 시간을 계산하고 조작하는 데 사용됩니다. 이러한 함수들은 일정 관리, 납기일 계산, 근무 시간 추적 등에 유용합니다.

TODAY 함수와 NOW 함수

현재 날짜와 시간을 반환합니다.

=TODAY() - 현재 날짜 반환
=NOW() - 현재 날짜와 시간 반환

주의: 이 함수들은 파일을 열 때마다 자동으로 업데이트됩니다.

DATE 함수

년, 월, 일을 지정하여 날짜 값을 만듭니다.

=DATE(년, 월, 일)

예시:

=DATE(2024, 4, 15) - 2024년 4월 15일 날짜 값을 반환합니다.

YEAR, MONTH, DAY 함수

날짜에서 연도, 월, 일을 추출합니다.

=YEAR(날짜)
=MONTH(날짜)
=DAY(날짜)

예시:

=YEAR(TODAY()) - 현재 연도를 반환합니다.
=MONTH("2024-04-15") - 4를 반환합니다.

NETWORKDAYS 함수

두 날짜 사이의 근무일수(주말 제외)를 계산합니다.

=NETWORKDAYS(시작일, 종료일, [휴일])

예시:

=NETWORKDAYS("2024-04-01", "2024-04-30") - 4월 한 달의 근무일수를 계산합니다.

실무 팁: 공휴일을 별도의 범위로 지정하여 세 번째 인수로 추가할 수 있습니다.

7. 통계 함수

통계 함수는 데이터 세트의 통계적 특성을 분석하는 데 사용됩니다. 이 함수들은 데이터 분석에 매우 유용합니다.

COUNT, COUNTA, COUNTBLANK 함수

셀 개수를 계산하는 함수들입니다.

=COUNT(범위) - 숫자가 들어있는 셀의 개수 반환
=COUNTA(범위) - 비어있지 않은 셀의 개수 반환
=COUNTBLANK(범위) - 비어있는 셀의 개수 반환

예시:

=COUNT(A1:A10) - A1부터 A10까지 숫자가 들어있는 셀의 개수를 반환합니다.
=COUNTA(A1:A10) - A1부터 A10까지 값이 있는 셀의 개수를 반환합니다.

COUNTIF 함수

특정 조건을 만족하는 셀의 개수를 계산합니다.

=COUNTIF(범위, 조건)

예시:

=COUNTIF(A1:A10, ">80") - A1부터 A10까지 80보다 큰 값을 가진 셀의 개수를 반환합니다.
=COUNTIF(B1:B10, "사과") - B1부터 B10까지 "사과"라는 값을 가진 셀의 개수를 반환합니다.

MAX, MIN 함수

최댓값과 최솟값을 찾습니다.

=MAX(범위)
=MIN(범위)

STDEV, VAR 함수

표준편차와 분산을 계산합니다.

=STDEV.S(범위) - 표본의 표준편차 계산
=VAR.S(범위) - 표본의 분산 계산

8. 재무 함수

재무 함수는 대출, 투자, 감가상각 등 재무적 계산을 수행하는 데 사용됩니다. 이 함수들은, 기업 및 개인 재무 분석에 유용합니다.

PMT 함수

고정 금리 및 정기 납입금에 대한 대출 납입액을 계산합니다.

=PMT(이자율, 기간, 현재가치, [미래가치], [유형])

예시:

=PMT(5%/12, 12*30, 300000000) - 3억 원을 연 이자율 5%, 30년 상환 조건으로 대출했을 때 월 납입액을 계산합니다.

FV 함수

일정한 이자율과 정기 납입금을 바탕으로 미래 가치를 계산합니다.

=FV(이자율, 기간, 정기납입금, [현재가치], [유형])

예시:

=FV(7%/12, 12*10, -500000) - 매월 50만 원씩 연 이자율 7%로 10년 동안 저축했을 때 최종 금액을 계산합니다.

NPV 함수

순현재가치(Net Present Value)를 계산합니다.

=NPV(할인율, 범위1, [범위2], ...)

9. 효율적인 함수 사용 팁

함수 중첩 사용하기

여러 함수를 조합하여 복잡한 계산을 수행할 수 있습니다. 함수는 다른 함수의 인수로 사용될 수 있으며, 이를 통해 강력한 수식을 만들 수 있습니다.

=IF(COUNTIF(A1:A10, "완료")>5, "목표 달성", "진행 중") - A1부터 A10까지 "완료"라는 값이 5개 이상인지 확인합니다.

절대 참조 사용하기

수식을 복사할 때 특정 셀 참조가 변경되지 않도록 하려면 $ 기호를 사용하여 절대 참조를 만듭니다.

=A1*$B$1 - 수식을 복사해도 B1은 항상 B1을 참조합니다.

단축키 팁: 셀 참조를 선택한 상태에서 F4 키를 누르면 상대, 절대, 혼합 참조 간에 전환할 수 있습니다.

오류 처리하기

함수 사용 시 발생할 수 있는 오류를 처리하기 위해 IFERROR, IFNA 함수를 사용할 수 있습니다.

=IFERROR(값, 오류발생시값)

=IFERROR(VLOOKUP(A1, B1:C10, 2, FALSE), "찾을 수 없음") - VLOOKUP 함수가 오류를 반환할 경우 "찾을 수 없음"을 표시합니다.

함수 마법사 활용하기

복잡한 함수를 작성할 때 함수 마법사를 활용하면 훨씬 쉽게 함수를 구성할 수 있습니다.

단축키: 수식 입력 시 Shift + F3를 누르면 함수 마법사가 열립니다.