엑셀 실무에서 자주 쓰는 수식 4가지 정리 및 활용법

엑셀 실무에서 가장 많이 쓰이는 수식은 조건부 합계(SUMPRODUCT), 날짜 계산, VLOOKUP 최적화, 대용량 데이터 처리 4가지예요. 각 상황별로 올바른 수식을 선택하면 작업 효율이 크게 올라가요.

조건부 합계 수식 — 짝수만 더하기

엑셀에서 특정 조건에 맞는 데이터만 합계하는 것은 실무에서 자주 나오는 작업이에요.

짝수 값만 합계하려면 SUMPRODUCT와 MOD 함수를 조합해서 써요.

\=SUMPRODUCT((MOD(A1:A200,2)=0)A1:A200)
\
– MOD(A1:A200,2) — 각 셀을 2로 나눈 나머지 구하기 (짝수면 0, 홀수면 1)
– (MOD(A1:A200,2)=0) — 결과가 0인 것만 TRUE로 변환
– 곱하기(A1:A200)** — TRUE인 행의 값만 실제로 합산

이 방식은 SUMIF로는 못 하는 수학 계산(2로 나눈 나머지) 기반 조건도 다룰 수 있어서 매우 유연해요.

실제 활용 예시

판매 데이터에서 짝수 거래액만 통계낼 때, 또는 ID 번호가 짝수인 고객의 구매액만 분석할 때 쓸 수 있어요. MOD 함수는 나머지 계산뿐 아니라 일정 간격마다 값을 추출하는 용도로도 자주 쓰이니까 꼭 알아두면 좋아요.

날짜 변환 수식 — 특정 시점 기준으로 자동 계산

월말 마감 작업이나 급여 관리할 때는 특정 날짜를 기준으로 다음 달을 자동으로 계산해야 해요.

오늘이 25일 이상이면 다음달 1일을 표시하는 수식:

\=IF(DAY(TODAY())>=25, DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1), TODAY())
\
구성:
– DAY(TODAY()) — 현재 날짜의 ‘일’ 추출 (1~31)
– >=25 — 25일 이상인지 판별
– DATE(YEAR, MONTH+1, 1) — 다음달 1일 만들기
– 그 외는 TODAY() — 25일 미만이면 오늘 날짜 유지

이 수식을 급여 기산일이나 보험료 자동 계산에 넣으면 수동 입력 없이 자동으로 월말을 기준점으로 설정할 수 있어요.

월말 자동 계산의 중요성

급여 관리 시스템에서는 월말이 기준일이 돼야 해요. 보험료나 사용료도 마찬가지예요. 이 수식을 한 번 만들어두면 매달 수동으로 입력할 필요가 없어서 실수도 줄어들고 시간도 절약할 수 있어요.

VLOOKUP 채우기 — 범위 복사하지 말고 자동 확장

VLOOKUP 수식을 한 셀에 입력한 후 아래로 쭉 복사하는 방식은 느리고 비효율적이에요.

Ctrl+D 기능으로 채우기:
1. 수식이 들어간 셀 선택 (예: C2)
2. 복사할 범위 선택 (C2:C100)
3. Ctrl+D 누르면 수식이 한 번에 채워짐

또는 표 기능으로 자동 확장:
– 데이터 범위를 표로 변환 (Ctrl+T)
– 새 열에 VLOOKUP 입력
– 나머지 칸이 자동으로 채워짐

→ 이 방식이 수작업으로 복사하는 것보다 5~10배 빠르고, 실수가 거의 없어요.

천 건 이상 데이터에서의 효율 차이

마우스로 드래그해서 수식을 복사하면 시간이 오래 걸릴 뿐 아니라 도중에 실수할 가능성도 커요. Ctrl+D는 한 번에 처리돼서 누락된 셀이 없고, 표 기능은 자동으로 새 데이터가 추가되면 수식도 따라 확장돼요. 대량 데이터를 다루는 직무라면 필수로 숙달해야 해요.

대용량 데이터 최적화 — VLOOKUP 대신 INDEX-MATCH 사용

데이터가 만 건 이상이면 VLOOKUP의 성능 문제가 눈에 띄게 나타나요.

원인: VLOOKUP은 조건에 맞을 때까지 계속 스캔하기 때문에 데이터가 많을수록 느려져요.

해결법 — INDEX와 MATCH 함수 조합:

\=INDEX(반환범위, MATCH(찾는값, 범위, 0))
\
예를 들어 품명으로 가격을 찾는다면:

→ 대용량 데이터라면 처음부터 INDEX-MATCH로 설계하는 게 운영 시간을 크게 아낄 수 있어요.

성능 차이가 나는 이유

VLOOKUP은 표의 맨 왼쪽 열에서 찾는 값을 순차 검색해요. 데이터가 많으면 마지막 행까지 가야 하는 경우가 많아서 느려져요. 반면 INDEX-MATCH는 MATCH 함수가 찾는 값의 위치만 빠르게 반환하고, INDEX가 그 위치의 값을 꺼내오는 방식이라서 계산 복잡도가 훨씬 낮아요. 특히 50만 건 이상 데이터면 체감할 수 있을 정도로 차이가 나요.