엑셀 함수 정리: SUM, AVERAGE, VLOOKUP, CONCATENATE, COUNTIF 예제

엑셀 함수 정리: SUM, AVERAGE, VLOOKUP, CONCATENATE, COUNTIF 예제

엑셀은 숫자를 계산하는 데 그치지 않고, 여러 행의 데이터를 빠르게 합산하거나 평균을 구하고, 조건에 맞는 항목을 찾고, 흩어진 문자를 한 셀에 모으는 업무 도구입니다. 자주 쓰는 함수를 익혀 두면 매출표, 성적표, 재고 목록, 고객 명단처럼 반복해서 다루는 자료를 훨씬 효율적으로 정리할 수 있습니다.

엑셀 함수 정리

이 글 엑셀 함수 정리에서는 엑셀 초보자도 바로 적용할 수 있도록 SUM, AVERAGE, VLOOKUP, CONCATENATE, COUNTIF의 기본 형식과 실무 예제를 살펴보겠습니다. 수식은 함수 이름 앞에 등호 =를 붙여 입력합니다. 아래 예제는 데이터가 2행부터 6행까지 입력되어 있다고 가정하며, 실제 파일에서는 사용하는 열과 행에 맞게 셀 주소를 바꾸면 됩니다.

엑셀 함수 입력의 기본

함수는 정해진 형식에 따라 값을 계산하거나 찾아주는 수식입니다. 셀에 수식을 입력하고 Enter 키를 누르면 엑셀이 결과를 계산합니다. 예를 들어 =SUM(B2:B6)은 B2부터 B6까지의 숫자를 더하라는 뜻입니다.

함수 입력 전 알아 두면 편리한 기초 표현은 다음과 같습니다.

• =: 수식이나 함수를 시작할 때 입력합니다.
• B2: B열 2행에 있는 셀 하나를 가리킵니다.
• B2:B6: B2부터 B6까지 이어진 셀 범위를 뜻합니다.
• ,: 함수에 여러 인수를 넣을 때 인수 사이에 사용합니다. 엑셀 설정에 따라 세미콜론 ;을 사용하는 환경도 있습니다.
• "완료": 조건이나 문자처럼 텍스트를 수식에 직접 입력할 때 큰따옴표로 감쌉니다.

엑셀의 언어 및 지역 설정에 따라 함수 구분 기호가 쉼표가 아니라 세미콜론으로 표시될 수 있습니다. 예를 들어 =COUNTIF(C2:C10,"완료")를 입력했을 때 오류가 난다면 =COUNTIF(C2:C10;"완료")처럼 바꾸어 입력해 보세요. 또한 함수 이름을 입력할 때 철자가 정확한지, 범위에 숫자와 문자가 섞여 있지는 않은지도 확인하는 것이 좋습니다.

SUM 함수로 합계 구하기

SUM 함수는 선택한 셀에 들어 있는 숫자를 더해 합계를 구합니다. 월별 매출, 제품별 판매량, 항목별 비용을 계산할 때 기본으로 활용할 수 있는 함수입니다.

기본 형식은 다음과 같습니다.

• =SUM(숫자1, 숫자2, ...)
• =SUM(합계를 구할 범위)

예를 들어 B2부터 B6까지 매출 금액이 입력되어 있다면 합계 셀에 다음 수식을 입력합니다.

• =SUM(B2:B6)

B2부터 B6까지의 값을 모두 더한 결과가 표시됩니다. 연속된 범위뿐 아니라 떨어져 있는 셀도 함께 더할 수 있습니다. 예를 들어 B2, B5, B8의 값만 합산하려면 다음처럼 작성합니다.

• =SUM(B2,B5,B8)

범위 두 곳을 함께 계산할 때도 사용할 수 있습니다. 예를 들어 1분기 실적이 B2:B6, 2분기 실적이 D2:D6에 있다면 다음과 같이 입력하면 됩니다.

• =SUM(B2:B6,D2:D6)

SUM은 숫자가 아닌 일반 텍스트를 합계에서 제외합니다. 다만 숫자처럼 보이는 값이 문자 형식으로 저장되어 있으면 기대한 합계가 나오지 않을 수 있습니다. 셀 왼쪽 위에 초록색 표시가 있거나 숫자가 왼쪽으로 정렬되어 있다면 텍스트 형식인지 확인해 보세요. 데이터를 숫자로 변환한 뒤 다시 계산하면 해결되는 경우가 많습니다.

AVERAGE 함수로 평균 구하기

AVERAGE 함수는 선택한 숫자의 산술 평균을 계산합니다. 시험 점수의 평균, 직원별 처리 건수 평균, 일정 기간의 일평균 매출처럼 전체적인 수준을 확인할 때 유용합니다.

기본 형식은 다음과 같습니다.

• =AVERAGE(숫자1, 숫자2, ...)
• =AVERAGE(평균을 구할 범위)

C2부터 C6까지 점수가 들어 있다면 다음 수식을 입력합니다.

• =AVERAGE(C2:C6)

예를 들어 C2:C6에 80, 90, 75, 95, 85가 입력되어 있다면 다섯 점수의 평균을 계산해 표시합니다. 일부 셀이 비어 있거나 일반 문자가 입력되어 있으면 해당 셀은 평균 계산에서 제외됩니다. 반면 숫자 0은 실제 숫자로 계산에 포함됩니다. 결석이나 미입력 값을 0으로 기록하면 평균이 낮아질 수 있으므로, 빈칸과 0의 의미를 먼저 정해 두는 것이 중요합니다.

평균 결과를 소수점 없이 표시하고 싶다면 셀 서식에서 소수 자릿수를 조정할 수 있습니다. 계산값 자체를 반올림하려면 ROUND 함수와 함께 사용할 수 있습니다.

• =ROUND(AVERAGE(C2:C6),1)

이 수식은 평균을 구한 뒤 소수점 첫째 자리까지 표시하도록 반올림합니다. 평균을 해석할 때는 단순히 숫자만 보는 것보다 데이터의 분포도 함께 확인하는 편이 좋습니다. 매우 큰 값이나 작은 값이 섞여 있으면 평균이 일반적인 값처럼 보이지 않을 수 있습니다.

VLOOKUP 함수로 원하는 값 찾기

VLOOKUP 함수는 표의 첫 번째 열에서 기준값을 찾고, 같은 행의 다른 열에서 원하는 값을 가져오는 함수입니다. 상품 코드로 상품명이나 단가를 찾거나, 사번을 기준으로 부서명을 불러오는 작업에 활용할 수 있습니다.

기본 형식은 다음과 같습니다.

• =VLOOKUP(찾을 값, 표 범위, 열 번호, 일치 방식)

예를 들어 A2:C6에 다음과 같은 상품표가 있다고 가정해 보겠습니다.

• A열: 상품 코드
• B열: 상품명
• C열: 단가
• A2:A6: P101, P102, P103, P104, P105
• B2:B6: 상품별 이름
• C2:C6: 상품별 단가

E2 셀에 찾고 싶은 상품 코드가 있다면 상품명을 가져오는 수식은 다음과 같습니다.

• =VLOOKUP(E2,A2:C6,2,FALSE)

이 수식은 E2의 값을 A2:A6에서 찾고, 해당 행의 두 번째 열인 B열 값을 반환합니다. 같은 코드로 단가를 가져오려면 열 번호를 3으로 바꿉니다.

• =VLOOKUP(E2,A2:C6,3,FALSE)

마지막 인수 FALSE는 정확히 일치하는 값을 찾으라는 의미입니다. 상품 코드나 사번처럼 정확한 값이 기준이 되는 업무에서는 보통 FALSE를 사용합니다. TRUE 또는 생략은 근삿값을 찾는 방식이므로, 데이터가 정렬되어 있어야 하고 결과 해석에 주의해야 합니다.

VLOOKUP에는 알아 둘 제한도 있습니다. 기준으로 찾는 값은 지정한 표 범위의 가장 왼쪽 열에 있어야 합니다. 그리고 반환할 열 번호는 표 범위의 왼쪽부터 세어 입력합니다. 표의 열을 추가하거나 이동하면 열 번호가 바뀌어 결과가 달라질 수 있습니다. 검색 결과가 없을 때 #N/A가 표시되는 것을 줄이고 싶다면 IFERROR와 함께 사용할 수 있습니다.

• =IFERROR(VLOOKUP(E2,A2:C6,2,FALSE),"코드 확인")

일치하는 코드가 없으면 오류 대신 “코드 확인”이라는 문구를 표시합니다. 다만 오류를 감추는 것만으로 데이터 문제가 해결되지는 않으므로, 기준 코드에 앞뒤 공백이 있는지, 숫자와 텍스트 형식이 서로 다르지 않은지도 함께 살펴보세요.

CONCATENATE 함수로 문자 합치기

CONCATENATE 함수는 여러 셀이나 텍스트를 하나의 문자열로 연결합니다. 이름과 성을 한 셀에 합치거나, 지역 코드와 번호를 이어 붙이거나, 문장 안에 셀 값을 넣을 때 사용할 수 있습니다.

기본 형식은 다음과 같습니다.

• =CONCATENATE(텍스트1, 텍스트2, ...)

A2 셀에 이름, B2 셀에 성이 각각 들어 있다고 가정하겠습니다. 두 셀의 문자를 붙이는 수식은 다음과 같습니다.

• =CONCATENATE(A2,B2)

이름과 성 사이에 공백을 넣고 싶다면 공백을 큰따옴표 안에 입력합니다.

• =CONCATENATE(A2," ",B2)

고객 안내 문구에 셀 값을 넣을 수도 있습니다. A2에 상품명, B2에 재고 수량이 있다면 다음처럼 구성할 수 있습니다.

• =CONCATENATE(A2,"의 현재 재고는 ",B2,"개입니다.")

다만 최신 엑셀에서는 텍스트를 연결할 때 CONCAT 함수나 & 연산자를 사용하는 방식도 널리 쓰입니다. CONCATENATE는 기존 파일과의 호환성을 위해 계속 제공되지만, 새 수식을 작성할 때는 CONCAT 또는 &를 활용하는 방법도 알아두면 편리합니다.

• =CONCAT(A2," ",B2)
• =A2&" "&B2

문자 사이에 쉼표나 공백 같은 구분 기호를 넣고, 빈 셀은 건너뛰어 연결하고 싶다면 TEXTJOIN 함수가 더 편리할 수 있습니다. 예를 들어 A2:C2의 내용을 쉼표와 공백으로 연결하려면 다음과 같이 입력할 수 있습니다.

• =TEXTJOIN(", ",TRUE,A2:C2)

COUNTIF 함수로 조건에 맞는 셀 세기

COUNTIF 함수는 지정한 범위 안에서 조건을 만족하는 셀의 개수를 셉니다. 특정 상품의 주문 건수, “완료” 상태의 작업 수, 기준 점수 이상인 인원처럼 조건별 개수를 구할 때 유용합니다.

기본 형식은 다음과 같습니다.

• =COUNTIF(조건을 확인할 범위, 조건)

C2부터 C20까지의 상태 중 “완료”가 몇 개인지 확인하려면 다음 수식을 입력합니다.

• =COUNTIF(C2:C20,"완료")

B2:B20에 숫자가 있고 100 이상인 값의 개수를 세려면 조건을 큰따옴표로 묶습니다.

• =COUNTIF(B2:B20,">=100")

조건을 셀에 입력해 두었다면 조건 셀을 참조할 수도 있습니다. E2에 기준 숫자가 들어 있고 그 값보다 큰 셀의 개수를 세고 싶다면 비교 연산자와 셀 주소를 연결합니다.

• =COUNTIF(B2:B20,">"&E2)

COUNTIF는 텍스트 조건을 사용할 때 일부 와일드카드도 지원합니다. 물음표는 임의의 문자 하나, 별표는 임의의 문자열을 뜻합니다. 예를 들어 상품명에 “커피”가 포함된 셀을 세려면 다음처럼 입력할 수 있습니다.

• =COUNTIF(A2:A20,"*커피*")

조건에 맞는 셀의 개수는 데이터가 추가되거나 수정될 때 자동으로 달라집니다. 다만 조건 범위와 실제 데이터 범위가 서로 어긋나면 일부 행이 계산에서 빠질 수 있습니다. 데이터가 계속 추가되는 목록이라면 범위를 넉넉하게 지정하거나 엑셀 표 기능을 활용해 범위가 확장되도록 관리하는 편이 좋습니다.

함수 사용 중 자주 만나는 오류와 점검 방법

수식 입력 후 결과가 예상과 다를 때는 함수 자체보다 셀 형식, 범위, 기준값을 먼저 점검하면 문제를 빠르게 찾을 수 있습니다. 특히 수식 복사 과정에서 참조 범위가 움직였거나 숫자가 문자로 저장된 경우가 자주 발생합니다.

다음 항목을 순서대로 살펴보세요.

• 수식 앞에 등호가 있는지 확인합니다. 등호가 없으면 엑셀이 수식을 계산하지 않고 일반 문자로 취급할 수 있습니다.
• 함수 이름과 괄호를 확인합니다. 괄호가 빠지거나 인수 사이 구분 기호가 잘못되면 수식 오류가 발생합니다.
• 범위가 정확한지 확인합니다. B2:B6이어야 할 범위가 B2:B5로 잘못 입력되면 마지막 행이 빠집니다.
• 숫자 형식을 점검합니다. 숫자로 보이는 값이 텍스트로 저장되면 SUM이나 AVERAGE 결과가 달라질 수 있습니다.
• VLOOKUP의 기준 열을 확인합니다. 찾을 값은 표 범위의 가장 왼쪽 열에 있어야 하며, 반환 열 번호도 범위 기준으로 세어야 합니다.
• COUNTIF의 조건 표기를 확인합니다. ">=100"처럼 비교 연산자를 포함한 조건은 큰따옴표로 묶습니다.
• 함수 구분 기호를 확인합니다. 엑셀 환경에 따라 쉼표 대신 세미콜론이 필요할 수 있습니다.

수식을 다른 행으로 복사할 때는 셀 참조가 함께 이동하는지도 확인해야 합니다. 예를 들어 =SUM(B2:B6)을 아래로 복사하면 참조 범위가 한 행씩 이동할 수 있습니다. 특정 범위를 고정하려면 $ 기호를 붙여 $B$2:$B$6처럼 절대 참조로 지정할 수 있습니다. 데이터가 추가되는 표에서 수식을 반복적으로 사용한다면 이 참조 방식까지 익혀 두면 수식 관리가 쉬워집니다.

실무에서 다섯 함수를 함께 활용하는 방법

다섯 함수는 각각 다른 역할을 하지만, 하나의 업무 파일 안에서 함께 사용할 수 있습니다. 예를 들어 상품 코드와 판매량이 있는 주문표에서 VLOOKUP으로 상품명을 가져오고, CONCATENATE로 안내 문구를 만들며, SUM으로 총판매량을 계산할 수 있습니다. AVERAGE로 주문당 평균 수량을 확인하고, COUNTIF로 특정 상태의 주문 건수를 세면 기본적인 현황을 빠르게 파악할 수 있습니다.

함수를 조합할 때는 수식을 한 번에 길게 작성하기보다 각 단계가 올바르게 계산되는지 확인하는 편이 안전합니다. 먼저 기준 데이터와 범위를 정리한 다음, 결과가 맞는 간단한 수식을 입력하고 필요한 기능을 더해 보세요. 예를 들어 조회 결과를 문장에 넣을 때는 VLOOKUP을 먼저 별도 셀에서 확인한 뒤 CONCATENATE나 &로 문장을 구성하면 오류 원인을 찾기 쉽습니다.

엑셀 수식은 입력한 값과 데이터 구조에 따라 결과가 달라집니다. 따라서 함수 이름을 외우는 것보다 “어떤 범위에서 무엇을 계산하거나 찾을 것인지”를 먼저 정리하는 습관이 중요합니다. SUM은 더하고, AVERAGE는 평균을 구하며, VLOOKUP은 기준값으로 표를 조회하고, CONCATENATE는 문자를 연결하며, COUNTIF는 조건에 맞는 셀을 셉니다. 이 기본 역할을 이해하면 다른 함수도 목적에 맞게 익혀 나갈 수 있습니다.

결론

SUM, AVERAGE, VLOOKUP, CONCATENATE, COUNTIF는 엑셀에서 자주 사용하는 대표 함수입니다. 합계와 평균을 구하는 계산부터 코드에 맞는 정보 조회, 문자 결합, 조건별 데이터 집계까지 기본적인 문서 작업의 폭넓은 상황에 활용할 수 있습니다.

처음에는 작은 예제 범위에 수식을 직접 입력하고, 각 인수가 어떤 의미인지 확인해 보세요. 이후 실제 업무 파일에 적용할 때 데이터 범위와 셀 형식, 조건 표기, 참조 방식까지 점검하면 수식 오류를 줄일 수 있습니다. 반복되는 계산을 함수로 바꾸면 작업 시간을 줄일 뿐 아니라 결과를 같은 기준으로 관리하는 데도 도움이 됩니다.