엑셀 함수가 500개가 넘는다는 말에 지레 겁먹으셨나요? 실무에서 반복해서 손이 가는 건 사실 15개 남짓입니다. 이 글은 합계·조건·조회·텍스트 정리라는 네 갈래로 나눠 자주 쓰는 함수의 문법과 실제 입력 예시, 그리고 초보자가 가장 많이 막히는 오류 원인을 정리했습니다.
합계와 평균: SUM보다 SUMIF·SUMIFS를 먼저 익히세요
SUM은 다들 압니다. =SUM(B2:B100)이면 끝입니다. 문제는 "3월 매출만" "강남지점 것만" 같은 조건이 붙는 순간입니다.
| 함수 | 문법 | 입력 예시 |
|---|---|---|
| SUM | =SUM(범위) | =SUM(B2:B100) |
| AVERAGE | =AVERAGE(범위) | =AVERAGE(B2:B100) |
| SUMIF | =SUMIF(조건범위, 조건, 합계범위) | =SUMIF(A2:A100,"강남",B2:B100) |
| SUMIFS | =SUMIFS(합계범위, 조건범위1, 조건1, …) | =SUMIFS(C2:C100,A2:A100,"강남",B2:B100,">=100") |
| ROUND | =ROUND(값, 자릿수) | =ROUND(B2,0) |
여기서 헷갈리는 지점이 하나 있습니다. SUMIF는 합계범위가 맨 뒤, SUMIFS는 맨 앞입니다. 인수 순서가 다릅니다. 조건이 하나뿐이어도 그냥 SUMIFS만 쓰는 게 헷갈릴 일이 없어 편합니다.
조건에 부등호를 넣을 땐 ">=100"처럼 큰따옴표로 감싸야 합니다. 셀 값을 참조할 땐 ">="&E1 형태로 앰퍼샌드를 씁니다. 이걸 몰라서 0이 나오는 경우가 정말 많습니다.
개수 세기: COUNT, COUNTA, COUNTIF의 차이
- COUNT: 숫자가 든 셀만 셈.
=COUNT(B2:B100) - COUNTA: 비어 있지 않은 모든 셀. 텍스트 포함.
=COUNTA(B2:B100) - COUNTBLANK: 빈 셀 개수
- COUNTIF: 조건에 맞는 셀 개수.
=COUNTIF(A2:A100,"완료") - COUNTIFS: 조건 여러 개
중복 확인에 COUNTIF가 유용합니다. =COUNTIF($A$2:$A$100,A2)>1 을 조건부 서식에 넣으면 중복된 주민번호나 사번이 색으로 뜹니다. 이건 함수 결과를 셀에 뿌리는 것보다 조건부 서식으로 처리하는 게 훨씬 깔끔합니다.
조건 분기: IF와 IFS, 그리고 중첩의 한계
=IF(B2>=80,"합격","불합격") 이 기본형입니다. 조건이 세 개 이상이면 IF를 중첩하게 되는데, 3단계만 넘어가도 괄호를 세다가 지칩니다.
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","D")))
이럴 땐 IFS가 훨씬 읽기 좋습니다. Microsoft 365와 Excel 2019 이상에서 씁니다.
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"D")
IFS는 조건을 위에서부터 순서대로 검사하고 처음 참인 것에서 멈춥니다. 그래서 B2>=70을 맨 위에 두면 90점도 C가 됩니다. 큰 값부터 배치하세요. 맨 끝의 TRUE,"D"는 "나머지 전부"를 뜻합니다.
AND·OR도 같이 알아두면 좋습니다. =IF(AND(B2>=80,C2>=80),"통과","재시험") 처럼 씁니다.
조회의 핵심: VLOOKUP과 XLOOKUP
가장 많이 쓰이고, 가장 많이 틀리는 구간입니다.
=VLOOKUP(찾을값, 표범위, 몇번째열, FALSE) =VLOOKUP(A2, $D$2:$F$500, 3, FALSE)
네 번째 인수를 FALSE(또는 0)로 안 넣으면 유사 일치로 동작해 엉뚱한 값을 물어옵니다. 사번·상품코드처럼 정확히 맞아야 하는 조회라면 FALSE는 필수입니다. 그리고 표범위는 F4 키로 $D$2:$F$500처럼 절대참조로 고정해야 아래로 채우기 할 때 범위가 밀리지 않습니다.
VLOOKUP의 구조적 약점은 찾을값이 반드시 표의 첫 열에 있어야 한다는 점입니다. 오른쪽 열을 기준으로 왼쪽을 찾을 수 없습니다.
XLOOKUP은 이 제약이 없습니다. Microsoft 365와 Excel 2021 이상에서 지원합니다.
=XLOOKUP(A2, $D$2:$D$500, $F$2:$F$500, "없음")
찾을 범위와 가져올 범위를 따로 지정하니 방향 제약이 사라지고, 네 번째 인수로 못 찾았을 때 표시할 문구까지 지정됩니다. 기본이 정확히 일치라 실수 여지도 적습니다.
| 항목 | VLOOKUP | XLOOKUP |
|---|---|---|
| 지원 버전 | 대부분의 버전 | 365 / 2021 이상 |
| 왼쪽 방향 조회 | 불가 | 가능 |
| 기본 일치 방식 | 유사 일치 | 정확히 일치 |
| 열 삽입 시 | 열 번호 어긋남 | 영향 없음 |
솔직히 말해, 회사 파일을 여러 사람이 돌려 쓴다면 XLOOKUP은 아직 조심스럽습니다. 2016·2019 버전에서 열면 _xlfn.XLOOKUP 오류로 깨집니다. 혼자 쓰는 파일이면 XLOOKUP, 공유 파일이면 VLOOKUP이 안전합니다.
텍스트 정리: 이름과 주소를 다듬는 함수들
외부에서 받은 데이터는 늘 지저분합니다. 앞뒤 공백, 붙어 있는 이름과 코드.
| 함수 | 하는 일 | 예시 |
|---|---|---|
| LEFT | 왼쪽부터 n글자 | =LEFT(A2,3) |
| RIGHT | 오른쪽부터 n글자 | =RIGHT(A2,4) |
| MID | 중간에서 잘라내기 | =MID(A2,5,6) |
| LEN | 글자 수 | =LEN(A2) |
| TRIM | 앞뒤 공백 제거 | =TRIM(A2) |
| CONCAT | 텍스트 합치기 | =CONCAT(A2,B2) |
| TEXT | 표시 형식 지정 | =TEXT(A2,"yyyy-mm-dd") |
VLOOKUP이 계속 #N/A를 뱉는데 눈으로 보면 값이 똑같은 경우, 열에 열에 아홉은 공백입니다. =LEN(A2)로 글자 수를 세보면 3이어야 할 이름이 4로 나옵니다. TRIM으로 감싸면 해결됩니다.
셀 합치기는 =A2&" "&B2 처럼 앰퍼샌드를 쓰는 게 더 짧고 빠릅니다.
자주 마주치는 오류 메시지와 원인
| 오류 | 원인 | 대처 |
|---|---|---|
| #N/A | 조회 값을 못 찾음 | 공백·데이터 형식 확인, IFERROR로 감싸기 |
| #VALUE! | 숫자 자리에 텍스트 | 숫자로 저장된 텍스트인지 확인 |
| #REF! | 참조 셀이 삭제됨 | 수식 참조 다시 지정 |
| #DIV/0! | 0으로 나눔 | IFERROR 또는 IF로 예외 처리 |
| #NAME? | 함수 이름 오타·미지원 | 철자 확인, 버전 지원 여부 확인 |
=IFERROR(VLOOKUP(A2,$D$2:$F$500,3,FALSE),"미등록") 형태로 감싸두면 보고서에 오류 표시가 뜨지 않습니다. 다만 IFERROR는 모든 오류를 가려버리기 때문에, 검증 단계에서는 벗겨두고 확인한 뒤 마지막에 씌우는 편이 낫습니다.
한 가지 더. 숫자 같은데 왼쪽 정렬로 붙어 있고 합계가 0으로 나온다면 텍스트 형식입니다. 해당 열을 선택하고 데이터 탭의 텍스트 나누기를 눌러 마침만 클릭하면 숫자로 바뀝니다. 셀 서식을 숫자로 바꾸는 것만으로는 안 바뀝니다.
자주 묻는 질문(FAQ)
Q. VLOOKUP과 XLOOKUP 중 무엇을 배워야 하나요?
A. 사용 중인 엑셀이 Microsoft 365나 2021 이상이고 혼자 쓰는 파일이라면 XLOOKUP이 더 간편합니다. 다만 구버전 엑셀에서는 열리지 않으므로, 회사에서 파일을 공유한다면 VLOOKUP도 함께 알아두는 편이 안전합니다.
Q. VLOOKUP에서 #N/A 오류가 계속 나옵니다.
A. 대부분 찾을 값과 표의 값이 미세하게 다른 경우입니다. LEN 함수로 글자 수를 비교해 눈에 안 보이는 공백이 있는지 확인하고, 한쪽은 숫자·다른 쪽은 텍스트로 저장돼 있지 않은지 셀 정렬 방향으로 점검해 보세요.
Q. 수식을 아래로 복사했더니 결과가 이상합니다.
A. 참조 범위가 함께 밀렸기 때문입니다. 고정해야 하는 범위는 수식 입력 중 해당 부분을 드래그한 뒤 F4 키를 눌러 $D$2:$F$500 형태의 절대참조로 바꿔 주세요.
마무리
합계·개수·조건·조회·텍스트 다섯 갈래의 함수만 손에 익혀도 엑셀 실무의 대부분은 해결됩니다.