엑셀은 직장인의 기본 도구지만 수식을 제대로 활용하는 사람은 의외로 적다. 실무에서 가장 많이 쓰이는 엑셀 수식을 카테고리별로 정리했다. 이 글 하나면 검색 없이도 바로 적용할 수 있다.
엑셀 수식 기초 – 이것만 알면 시작 가능
엑셀 수식은 반드시 등호(=)로 시작한다. 이 기본 원칙만 기억하면 된다. 셀 참조 방식에는 상대 참조(A1), 절대 참조($A$1), 혼합 참조($A1, A$1) 세 가지가 있다.
엑셀 수식에서 가장 먼저 익혀야 할 것은 사칙연산이다. 더하기(+), 빼기(-), 곱하기(*), 나누기(/)를 셀 주소와 조합하면 대부분의 기본 계산이 가능하다.
▲ 절대 참조($)를 쓰지 않으면 수식을 복사할 때 셀 주소가 자동으로 바뀐다. 고정해야 할 셀은 반드시 $를 붙이는 습관이 필요하다.
핵심 포인트
엑셀 수식을 작성할 때 F4 키를 누르면 상대 참조와 절대 참조를 빠르게 전환할 수 있다. 실무에서 가장 많이 쓰는 단축키 중 하나다.
실무 필수 엑셀 수식 – 합계와 조건부 계산
SUM은 엑셀 수식 중 가장 기본이면서 가장 많이 쓰이는 함수다. =SUM(A1:A10)처럼 범위를 지정하면 해당 범위의 합계를 구한다. 단순하지만 실무의 80%가 여기서 시작된다.
SUMIF는 조건에 맞는 값만 합산하는 엑셀 수식이다. =SUMIF(A1:A10,”서울”,B1:B10)이라고 쓰면 A열이 “서울”인 행의 B열 값만 더한다. 부서별, 지역별 집계에 필수적이다.
SUMIFS는 SUMIF의 확장형으로 여러 조건을 동시에 적용할 수 있다. =SUMIFS(C1:C10,A1:A10,”서울”,B1:B10,”영업팀”)처럼 쓴다. 복잡한 데이터 분석에 빈번하게 활용된다.
COUNTIF는 조건에 맞는 셀의 개수를 세는 엑셀 수식이다. 특정 항목이 몇 번 등장하는지 파악할 때 유용하다. 출석 체크, 재고 관리 등에 자주 쓰인다.
| 함수 | 구문 | 용도 |
|---|---|---|
| SUM | =SUM(범위) | 범위 합계 |
| SUMIF | =SUMIF(조건범위,조건,합계범위) | 조건부 합계 |
| COUNTIF | =COUNTIF(범위,조건) | 조건부 개수 |
| AVERAGE | =AVERAGE(범위) | 평균값 |
| AVERAGEIF | =AVERAGEIF(조건범위,조건,평균범위) | 조건부 평균 |
데이터 검색 엑셀 수식 – VLOOKUP과 XLOOKUP
VLOOKUP은 엑셀 수식 중 실무자들이 가장 어려워하면서도 가장 많이 쓰는 함수다. =VLOOKUP(찾을값,테이블범위,열번호,FALSE)로 쓴다. 대용량 데이터에서 원하는 값을 뽑아올 때 필수다.
다만 VLOOKUP에는 치명적인 한계가 있다. 찾을 값이 반드시 테이블의 첫 번째 열에 있어야 하고, 왼쪽으로는 검색이 불가능하다. 이 문제 때문에 INDEX+MATCH 조합을 쓰는 사람도 많다.
2019년 이후 버전이라면 XLOOKUP을 강력히 추천한다. =XLOOKUP(찾을값,찾을범위,반환범위)로 구문이 직관적이고, 왼쪽 검색도 가능하다. VLOOKUP의 상위 호환이라고 봐도 된다.
INDEX와 MATCH를 조합한 엑셀 수식도 알아두면 좋다. =INDEX(반환범위,MATCH(찾을값,찾을범위,0))으로 쓰며 VLOOKUP의 모든 한계를 해결한다. 엑셀 고수들이 애용하는 조합이다.
VLOOKUP 먼저 익히기
기본 구문을 외우고 단순 검색부터 연습한다. 마지막 인수는 항상 FALSE로 넣는 것이 안전하다.
INDEX+MATCH 조합 연습
VLOOKUP의 한계를 느끼면 INDEX와 MATCH 조합으로 넘어간다. 유연성이 훨씬 높다.
XLOOKUP으로 전환
최신 엑셀 버전이라면 XLOOKUP이 가장 깔끔하다. 구문도 간단하고 기능도 강력하다.
텍스트 처리와 날짜 엑셀 수식
LEFT, MID, RIGHT는 텍스트에서 특정 부분을 추출하는 엑셀 수식이다. 주민등록번호에서 생년월일만 뽑거나 전화번호를 분리할 때 자주 사용한다.
CONCATENATE 또는 TEXTJOIN은 여러 셀의 텍스트를 하나로 합치는 함수다. 이름과 부서를 결합하거나 주소를 하나의 셀로 만들 때 쓴다. 최신 버전에서는 &(앰퍼샌드)로 간단히 대체할 수도 있다.
TODAY()와 NOW()는 현재 날짜와 시간을 반환하는 엑셀 수식이다. DATEDIF는 두 날짜 사이의 차이를 계산한다. 근속 연수, D-day 계산, 만기일 관리에 필수적이다.
마이크로소프트 엑셀 공식 지원 페이지에서 모든 함수의 상세 구문과 예제를 확인할 수 있다. 새로운 엑셀 수식이 추가될 때마다 업데이트되므로 즐겨찾기 해두면 유용하다.
- LEFT(텍스트,글자수) – 왼쪽부터 지정한 글자 수만큼 추출
- MID(텍스트,시작위치,글자수) – 중간 부분 추출
- RIGHT(텍스트,글자수) – 오른쪽부터 추출
- TRIM(텍스트) – 불필요한 공백 제거
- TEXT(값,서식) – 숫자를 원하는 형식의 텍스트로 변환
자주 묻는 질문 FAQ
Q. 엑셀 수식에서 #N/A 오류가 나오면 어떻게 하나?
A. VLOOKUP이나 XLOOKUP에서 찾는 값이 없을 때 발생한다. IFERROR 함수로 감싸면 오류 대신 원하는 값을 표시할 수 있다. =IFERROR(VLOOKUP(…),”해당없음”) 형태로 쓰면 된다.
Q. VLOOKUP과 XLOOKUP 중 어떤 엑셀 수식을 배워야 하나?
A. 회사에서 오래된 엑셀 버전을 쓴다면 VLOOKUP을 먼저 익혀야 한다. Microsoft 365나 엑셀 2021 이상이라면 XLOOKUP을 바로 배우는 게 효율적이다. 두 함수 모두 아는 것이 가장 좋다.
Q. 엑셀 수식을 빠르게 익히는 방법이 있나?
A. 실제 업무 데이터로 연습하는 것이 가장 빠르다. 마이크로소프트 함수 레퍼런스를 옆에 두고 하루 한 함수씩 적용해보는 것을 추천한다. SUM부터 시작해서 VLOOKUP, IF 순서로 익히면 된다.