엑셀 주요 함수 정리: 실무에서 바로 쓰는 필수 함수 목록

1. 엑셀 함수는 왜 순서보다 목적별로 익혀야 할까

엑셀 함수는 이름을 많이 외운다고 바로 빨라지지 않습니다. 먼저 “더하기”, “조건 판단”, “찾아오기”, “개수 세기”, “날짜 계산”처럼 지금 해결하려는 일을 분류하면 필요한 함수가 선명해집니다. 업무 파일을 열 때도 수식부터 손대기보다 결과 칸이 무엇을 말해야 하는지 한 문장으로 적어 보세요.

처음에는 함수 하나를 한 셀에서 완성한 뒤 아래로 채우는 방식이 안전합니다. 열 전체에 복잡한 수식을 한 번에 넣으면 참조가 틀렸을 때 원인을 찾기 어렵습니다. 원본 데이터는 복사본으로 보관하고, 결과 열의 첫 두세 행을 눈으로 대조한 뒤 범위를 확장하세요.

함수는 서로 연결됩니다. 예를 들어 IF로 조건을 판단하고, XLOOKUP으로 단가를 가져오고, ROUND로 금액을 정리할 수 있습니다. 한 수식에 전부 넣기보다 보조 열로 나누면 다른 사람이 파일을 인수받아도 수정하기 쉽고 오류도 빨리 발견됩니다.

2. 기본 집계 함수: SUM, AVERAGE, MIN, MAX

SUM은 숫자의 합계를 구하는 가장 기본 함수입니다. =SUM(B2:B20)처럼 범위를 지정하면 빈 셀과 텍스트는 건너뜁니다. 단, 숫자처럼 보이는 텍스트는 합계에 포함되지 않을 수 있으므로 자료를 붙여넣은 뒤 셀 왼쪽 위 경고 표시를 확인하세요.

AVERAGE는 평균, MIN과 MAX는 최솟값과 최댓값을 구합니다. 매출 평균을 볼 때는 환불이나 0원 주문을 포함할지 먼저 정해야 합니다. 값의 의미가 섞인 열을 그대로 평균내면 그럴듯하지만 잘못된 결과가 나오므로 필터 조건을 명확히 하는 것이 먼저입니다.

합계 행을 만들 때는 데이터 범위 안에 새 행을 끼워 넣지 말고 표 기능이나 별도 요약 영역을 쓰는 편이 좋습니다. 중간 합계가 범위에 포함되면 이중 계산이 생깁니다. 월별 표는 각 월 합계를 별도 열로 만들고 마지막에 SUM으로 묶으면 검산이 쉽습니다.

3. 조건 함수 IF와 IFS: 기준을 문장으로 먼저 적기

IF는 조건이 참이면 한 값, 거짓이면 다른 값을 보여 줍니다. =IF(C2>=70,"통과","재검토")처럼 기준과 결과를 분리해 쓰면 읽기 쉽습니다. 비교 기준이 숫자인지 텍스트인지, 이상인지 초과인지까지 업무 규칙을 확인한 뒤 수식을 작성하세요.

조건이 세 개 이상이면 IFS가 읽기 좋을 수 있지만, 조건 순서가 곧 우선순위입니다. 예를 들어 90점 이상을 먼저 검사하지 않고 70점 이상부터 검사하면 90점도 그 조건에 걸립니다. 겹치는 구간은 높은 기준부터 작성하고, 마지막에는 예상 밖 값을 처리하는 기본 조건을 두세요.

IF 안에 IF를 너무 깊게 넣으면 수정이 어려워집니다. 등급표처럼 기준이 많은 경우에는 별도 표를 만들고 XLOOKUP 또는 LOOKUP으로 연결하는 편이 낫습니다. 수식은 짧아지고, 기준 변경도 표의 숫자만 고치면 되므로 실무에서 안전합니다.

4. 조건 집계: COUNTIF, SUMIF, AVERAGEIF

COUNTIF는 특정 조건을 만족하는 셀 수를 셉니다. =COUNTIF(D2:D100,"완료")처럼 상태별 건수를 빠르게 확인할 수 있습니다. 조건 텍스트 앞뒤에 공백이 있으면 다른 값으로 인식되므로 데이터 입력 규칙을 통일하거나 TRIM으로 정리하세요.

SUMIF는 조건에 맞는 금액만 합산합니다. 예를 들어 담당자 열과 매출 열이 따로 있을 때 =SUMIF(A2:A100,"김고해",C2:C100)로 담당자 매출을 구합니다. 조건 범위와 합계 범위의 시작 행이 어긋나지 않도록 열 제목 바로 아래부터 같은 길이로 잡아야 합니다.

두 조건 이상은 COUNTIFS와 SUMIFS를 씁니다. 날짜 조건은 ">="&시작일처럼 셀 참조와 비교 연산자를 붙여 작성합니다. 날짜가 텍스트로 저장되어 있으면 조건이 전혀 적용되지 않을 수 있으므로, 먼저 셀 서식이 아니라 실제 값 형식을 확인하는 습관이 중요합니다.

5. 찾기 함수 XLOOKUP과 VLOOKUP: 무엇을 써야 할까

XLOOKUP은 찾을 열의 왼쪽과 오른쪽을 가리지 않고 값을 반환할 수 있어 새로운 파일에서는 우선 추천할 만합니다. =XLOOKUP(A2,상품표!A:A,상품표!C:C,"없음")처럼 찾을 값, 찾을 범위, 반환 범위, 없을 때 표시를 순서대로 넣습니다.

VLOOKUP은 오래된 파일과의 호환성 때문에 아직 많이 보입니다. 다만 찾을 값이 표의 첫 번째 열에 있어야 하고 열을 삽입하면 반환 열 번호가 바뀔 수 있습니다. 정확히 찾을 때는 마지막 인수에 FALSE를 넣어야 비슷한 값을 잘못 가져오는 일을 막을 수 있습니다.

찾기 오류의 대부분은 공백, 숫자·텍스트 형식 차이, 중복 키에서 생깁니다. 제품 코드처럼 반드시 하나여야 하는 값은 사전에 COUNTIF로 중복 여부를 확인하세요. XLOOKUP은 첫 번째 일치값만 돌려주므로 중복 데이터 자체를 해결하지는 않습니다.

6. 텍스트 정리 함수: LEFT, RIGHT, MID, TRIM, TEXTJOIN

LEFT, RIGHT, MID는 코드나 문장에서 필요한 일부를 꺼낼 때 씁니다. =LEFT(A2,3)은 앞 세 글자, =MID(A2,5,2)는 다섯 번째 글자부터 두 글자를 반환합니다. 주민번호나 계좌번호처럼 민감한 정보는 분석용 파일에서도 불필요하게 노출하지 않도록 마스킹 원칙을 지키세요.

TRIM은 연속 공백과 앞뒤 공백을 줄여 붙여넣기 데이터 정리에 특히 유용합니다. 다만 웹에서 복사한 특수 공백은 TRIM으로 남을 수 있습니다. 비교나 찾기가 계속 실패한다면 CLEAN, SUBSTITUTE를 함께 써서 보이지 않는 문자를 점검하세요.

TEXTJOIN은 여러 셀을 구분기호와 함께 연결합니다. 주소, 태그, 담당자 목록을 만들 때 편하지만 빈 셀을 어떻게 처리할지 인수를 확인해야 합니다. 보여 주기용 문장을 만들기 전에 원본 데이터 열을 덮어쓰지 않는 것이 나중에 수정하기 편합니다.

7. 날짜와 시간 함수: TODAY, EDATE, NETWORKDAYS

TODAY는 파일을 여는 날의 날짜를 반환하므로 보고서의 기준일 표시에 적합합니다. 그러나 과거 보고서에 TODAY를 그대로 쓰면 나중에 열었을 때 날짜가 바뀝니다. 제출일처럼 고정되어야 하는 날짜는 값으로 붙여넣거나 별도 입력 셀에 기록하세요.

EDATE는 기준일에서 몇 개월 뒤 또는 앞의 날짜를 계산합니다. 계약 만료일, 정기 점검일 계산에 쓰기 좋지만 월말 처리 규칙을 확인해야 합니다. 예를 들어 1월 31일에서 한 달 뒤처럼 달마다 날짜 수가 다른 경우 결과가 의도와 맞는지 샘플 날짜로 검산하세요.

NETWORKDAYS는 주말을 제외한 영업일 수를 계산합니다. 공휴일 목록을 별도 범위로 관리하면 휴일도 제외할 수 있습니다. 마감일 계산에는 시작일을 포함하는지, 반차를 어떻게 처리하는지처럼 조직의 운영 규칙을 함께 문서화해야 분쟁을 줄일 수 있습니다.

8. 반올림과 오류 처리: ROUND, IFERROR의 안전한 사용

ROUND는 표시 형식이 아니라 실제 계산 값을 반올림합니다. 세금이나 단가처럼 금액 계산에서는 어느 단계에서 반올림할지 규칙이 중요합니다. 항목별로 반올림한 합계와 전체 합계를 마지막에 반올림한 값이 다를 수 있으므로 기준을 통일하세요.

IFERROR는 오류가 날 때 다른 값을 보여 주어 보고서가 지저분해지는 것을 막습니다. 다만 =IFERROR(수식,0)처럼 모든 오류를 0으로 숨기면 데이터 누락을 정상값으로 착각할 수 있습니다. 조회 실패는 "미등록", 계산 불가 값은 "확인 필요"처럼 원인을 드러내는 문구가 더 안전합니다.

#N/A, #VALUE!, #DIV/0!는 같은 오류가 아닙니다. 먼저 어떤 오류가 왜 생겼는지 확인하고, 필요한 경우에만 IFNA 또는 IFERROR를 씁니다. 오류를 숨기기 전에 원본 열의 형식과 참조 범위를 점검하면 사후 수정 시간을 크게 줄일 수 있습니다.

9. 실무 절차: 데이터 받기부터 검산까지

첫째, 원본 시트는 손대지 않고 작업용 시트를 만듭니다. 둘째, 열 제목과 데이터 시작 행이 맞는지 확인합니다. 셋째, 필요한 계산을 보조 열에서 작은 범위로 시험합니다. 이 순서를 지키면 복잡한 파일에서도 잘못된 수식을 원본에 퍼뜨리지 않을 수 있습니다.

넷째, 결과가 나왔으면 무작위로 다섯 행 이상을 손계산 또는 원본 화면과 대조합니다. 다섯째, 필터로 빈값·오류·중복을 확인하고, 합계가 이전 보고서와 크게 다르면 이유를 메모합니다. 숫자가 맞아도 기준이 달라졌을 수 있기 때문입니다.

마지막으로 수식이 필요한 셀과 값으로 고정할 셀을 구분합니다. 다른 사람이 갱신할 파일이면 입력 칸 색상, 수식 보호, 간단한 사용 메모를 남기세요. 좋은 엑셀 파일은 함수가 화려한 파일보다 다음 사람이 실수 없이 갱신할 수 있는 파일입니다.

10. 자주 하는 실수와 예방 방법

수식을 아래로 채웠는데 참조 범위가 함께 움직이는 실수가 흔합니다. 고정해야 하는 세율표나 기준 셀은 $를 붙인 절대참조로 만드세요. $A$1, A$1, $A1이 각각 무엇을 고정하는지 작은 표에서 시험하면 실전에서 혼동이 줄어듭니다.

숫자가 화면에서는 같아 보여도 한쪽은 텍스트일 수 있습니다. 정렬이 이상하거나 SUM 결과가 작으면 숫자 서식만 바꾸지 말고 VALUE, 텍스트 나누기, 오류 검사로 실제 형식을 확인하세요. 날짜도 텍스트로 들어오면 기간 조건과 피벗테이블이 정상 작동하지 않습니다.

복사한 수식에 외부 파일 경로가 남아 있으면 다른 PC에서 값이 깨질 수 있습니다. 파일을 공유하기 전 데이터 연결과 이름 정의를 확인하고, 외부 참조가 필요한 경우에는 갱신 방법을 함께 안내하세요. 중요한 보고서는 PDF로 내보낸 최종본도 별도로 보관하는 것이 좋습니다.

1. 결과 열의 첫 수식을 검산한다.
2. 절대참조와 범위를 확인한다.
3. 다섯 행 이상을 원본과 대조한다.
4. 오류를 숨기지 말고 원인을 표시한다.
5. 공유 전 다른 버전에서 열어 본다.

11. 함수 선택표와 실전 체크리스트

아래 표는 무엇을 계산하려는지에 따라 가장 먼저 떠올릴 함수를 정리한 것입니다. 표의 예시는 출발점일 뿐이며, 실제 수식은 열 구조와 데이터 형식에 맞게 조정해야 합니다.

체크리스트는 수식을 복사하기 전에 한 번, 파일을 공유하기 전에 한 번 사용하면 좋습니다. 특히 날짜·금액·중복 키처럼 결과에 큰 영향을 주는 항목은 동료에게 교차 검토를 요청하는 편이 비용이 적게 듭니다.

12. 자주 묻는 질문

엑셀 버전에 따라 새 함수가 없을 수 있습니다. 회사의 공용 파일은 협업자가 사용하는 버전을 확인한 뒤 호환 가능한 수식을 선택하세요. 최신 함수가 편하더라도 다른 사람이 열었을 때 #NAME? 오류가 나면 실무에서는 오히려 불편합니다.

함수를 외울 때는 매일 한 개씩 실제 데이터에 써 보는 방식이 좋습니다. 단축키와 수식을 동시에 외우려 하기보다, 자주 하는 업무에서 반복되는 두세 개의 문제부터 해결하면 학습이 오래 남습니다.

함수 실력을 빠르게 쌓는 연습법

함수를 익히는 가장 빠른 방법은 예제 파일을 한 번 만드는 것이 아니라, 매주 반복되는 실제 업무를 작은 자동화 대상으로 고르는 것입니다. 예를 들어 주문 목록에서 담당자별 건수를 세거나, 상품 코드로 단가를 가져오거나, 마감일까지 남은 영업일을 구하는 식입니다. 한 번 만든 수식은 다음 주 파일에 다시 적용하면서 참조 범위와 예외 처리를 자연스럽게 익히게 됩니다.

연습 파일은 데이터와 결과를 같은 시트에 뒤섞지 말고 원본, 계산, 요약 시트로 나누세요. 원본에는 붙여넣기만 하고, 계산 시트에서는 보조 열을 사용하며, 요약 시트에서는 보고할 숫자만 보여 주는 구조가 좋습니다. 이 구조는 수식 오류가 생겼을 때 어느 단계에서 값이 달라졌는지 추적하게 해 주며, 불필요한 수식 복사도 줄여 줍니다.

새로운 함수는 평소 쓰던 함수와 비교하며 검증하세요. XLOOKUP을 배웠다면 같은 데이터를 VLOOKUP으로도 한 번 구해 보고, 결과가 다르면 정확히 일치 여부와 반환 범위를 확인합니다. 두 방식의 장단점을 직접 경험하면 이름만 외웠을 때보다 훨씬 오래 기억되고, 이전 버전 엑셀을 써야 하는 상황에서도 판단이 빨라집니다.

마지막으로 결과 숫자와 수식 자체를 모두 확인하는 습관을 들이세요. 셀을 클릭해 수식 입력줄을 보고, 범위의 첫 행과 마지막 행을 확인하며, 필터를 적용한 상태인지도 점검합니다. 함수는 계산을 대신해 주지만 기준을 정하는 일과 결과를 설명하는 일까지 대신하지는 않습니다. 그래서 검산 메모와 기준일 기록이 좋은 수식만큼 중요합니다.

업무별 미니 사례로 함수 고르기

매출 보고서에서 “이번 달 서울 담당자의 환불 제외 매출”을 구해야 한다면 먼저 조건을 열로 나눠 생각합니다. 지역, 담당자, 주문 상태가 조건이고 금액이 더할 대상이므로 SUMIFS가 적합합니다. 반대로 한 고객의 주문 번호가 몇 번 등장하는지만 알고 싶다면 COUNTIF가 더 단순하고 빠릅니다. 결과를 먼저 문장으로 말해 보면 필요한 함수가 자연스럽게 좁혀집니다.

상품 마스터에 있는 단가를 주문표에 붙일 때는 상품 코드가 고유한지 확인한 다음 XLOOKUP을 사용합니다. 단가가 비어 있으면 0원을 넣기보다 “단가 확인”이라고 표시해 담당자가 원인을 볼 수 있게 하세요. 이 작은 차이만으로도 미등록 상품, 코드 오타, 표 갱신 누락을 조기에 잡을 수 있고 결재 전에 다시 수작업으로 확인하는 시간을 줄입니다.

근태나 일정표에서는 날짜가 계산의 출발점입니다. 입사일과 기준일 사이의 근무 기간, 마감일까지의 영업일, 다음 정기 점검일은 각각 날짜 함수와 휴일 목록의 조합으로 구할 수 있습니다. 휴일 표는 한 사람이 개인 파일에 숨겨 두지 말고 공용 기준 시트로 관리하고, 변경한 날짜에는 수정 이유를 남겨야 다음 보고서에서 결과가 달라진 이유를 설명할 수 있습니다.

보고서를 제출하기 직전에는 함수가 맞는지뿐 아니라 결과가 읽기 쉬운지도 확인합니다. 천 단위 구분, 음수 표시, 단위, 기준일, 제외 조건을 표 가까이에 적으면 숫자만 보고 성급하게 판단하는 일을 줄일 수 있습니다. 엑셀은 계산 도구이지만 실무에서는 의사결정 자료이므로, 다른 사람이 같은 결론에 도달할 수 있게 만드는 것이 마지막 품질 기준입니다.