엑셀 함수 정리하는 방법: 자주 쓰는 함수부터 실무용 조합까지

얼마 전 지인이 매출표를 만들다가 엑셀 함수 때문에 30분 넘게 멈춰 있더라고요. 합계는 어떻게든 냈는데, 조건별 합계와 중복 제거가 나오자 손이 딱 멈춘 겁니다. 사실 엑셀 함수는 전부 외울 필요가 없습니다. 자주 쓰는 함수만 용도별로 묶어두면, 파일을 받을 때마다 검색창을 헤매는 시간이 꽤 줄어듭니다.

저도 처음에는 함수 이름을 많이 아는 게 실력이라고 생각했습니다. 그런데 실제 업무에서는 200개를 얕게 아는 것보다 20개를 정확히 쓰는 쪽이 훨씬 빠릅니다. 특히 사무용 문서, 견적서, 정산표, 명단 관리에서는 반복되는 패턴이 거의 정해져 있습니다.

엑셀 함수는 용도별로 묶으면 훨씬 쉽다

엑셀 함수가 어렵게 느껴지는 가장 큰 이유는 이름이 비슷하고, 설명이 딱딱하기 때문입니다. SUM, SUMIF, SUMIFS처럼 생김새가 비슷한 함수가 이어지면 초보자는 어디에 무엇을 써야 할지 헷갈립니다. 그래서 함수 이름보다 먼저 “내가 지금 하려는 작업”을 기준으로 나누는 게 좋습니다.

• 숫자를 더하고 평균을 내는 계산 함수
• 조건에 맞는 값만 골라내는 조건 함수
• 이름, 코드, 품목명을 찾는 조회 함수
• 날짜와 시간을 다루는 날짜 함수
• 글자를 자르거나 합치는 텍스트 함수

이렇게 나누면 함수가 갑자기 실용적인 도구처럼 보입니다. 예를 들어 “거래처별 매출 합계”가 필요하면 SUMIFS를 떠올리면 되고, “사원번호로 이름 찾기”가 필요하면 XLOOKUP이나 VLOOKUP을 보면 됩니다. 함수 암기가 아니라 상황 연결에 가깝습니다.

처음 익히면 좋은 기본 함수

엑셀을 자주 쓰는 사람이라면 아래 함수들은 거의 매주 만나게 됩니다. 문서 양식이나 정산표를 다룰 때도 가장 많이 쓰입니다.

계산할 때 자주 쓰는 함수

• SUM: 선택한 범위의 숫자를 모두 더합니다.
• AVERAGE: 평균값을 구합니다.
• MAX: 가장 큰 값을 찾습니다.
• MIN: 가장 작은 값을 찾습니다.
• COUNT: 숫자가 들어 있는 셀 개수를 셉니다.
• COUNTA: 비어 있지 않은 셀 개수를 셉니다.

예를 들어 B2부터 B20까지 금액이 있다면 합계는 =SUM(B2:B20)처럼 쓰면 됩니다. 견적서, 지출 내역, 주문 수량표에서 가장 기본이 되는 형태입니다. 여기까지는 계산기 대신 엑셀을 쓰는 단계라고 보면 됩니다.

조건을 붙일 때 쓰는 함수

• IF: 조건에 따라 다른 값을 표시합니다.
• SUMIF: 조건 하나에 맞는 값만 더합니다.
• SUMIFS: 여러 조건에 맞는 값만 더합니다.
• COUNTIF: 조건에 맞는 셀 개수를 셉니다.
• COUNTIFS: 여러 조건에 맞는 셀 개수를 셉니다.

실무에서는 단순 합계보다 조건부 계산이 훨씬 자주 나옵니다. 예를 들어 “서울 지점의 8월 매출만 합산”하거나 “완료 상태인 건수만 세기” 같은 작업입니다. 이때 SUMIFS와 COUNTIFS를 익혀두면 피벗 테이블을 쓰기 전에도 꽤 많은 일을 처리할 수 있습니다.

실무에서 체감이 큰 조회 함수

엑셀 작업이 빨라지는 순간은 조회 함수를 쓸 때입니다. 명단, 상품 코드, 단가표, 재고표처럼 서로 다른 표를 연결해야 할 때가 많기 때문입니다. 예전에는 VLOOKUP을 많이 썼고, 최근 버전에서는 XLOOKUP이 훨씬 편합니다.

• VLOOKUP: 기준값을 왼쪽 첫 열에서 찾아 오른쪽 값을 가져옵니다.
• XLOOKUP: 원하는 기준 범위에서 값을 찾아 지정한 결과 범위의 값을 가져옵니다.
• INDEX: 지정한 위치의 값을 가져옵니다.
• MATCH: 값이 몇 번째 위치에 있는지 찾습니다.

예를 들어 상품 코드가 A열에 있고, 단가가 C열에 있다면 상품 코드만 입력해도 단가가 자동으로 들어오게 만들 수 있습니다. 이 구조를 한 번 만들어두면 같은 양식에서 매번 복사 붙여넣기를 하지 않아도 됩니다. 솔직히 이 차이가 꽤 큽니다. 100줄 정도 되는 표에서는 손으로 찾는 시간이 몇 분이지만, 1,000줄이 넘어가면 함수가 없으면 일이 금방 지칩니다.

XLOOKUP을 쓸 수 있는 환경이라면 먼저 익히는 쪽을 권합니다. VLOOKUP은 찾는 기준 열이 왼쪽에 있어야 하는 제약이 있는데, XLOOKUP은 기준 범위와 결과 범위를 따로 지정할 수 있어 구조 변경에 강합니다.

문자와 날짜 함수까지 알면 양식이 깔끔해진다

문서 양식을 다루다 보면 숫자 계산보다 글자 가공이 더 귀찮을 때가 있습니다. 이름과 부서가 한 셀에 붙어 있거나, 주민등록번호 앞자리처럼 일부 문자만 뽑아야 하는 경우가 그렇습니다. 이런 작업에는 텍스트 함수가 잘 맞습니다.

• LEFT: 왼쪽에서 지정한 글자 수만큼 가져옵니다.
• RIGHT: 오른쪽에서 지정한 글자 수만큼 가져옵니다.
• MID: 중간 위치에서 원하는 글자 수만큼 가져옵니다.
• TEXTJOIN: 여러 셀의 글자를 구분자와 함께 합칩니다.
• TRIM: 불필요한 공백을 줄입니다.
• SUBSTITUTE: 특정 문자를 다른 문자로 바꿉니다.

날짜 함수도 은근히 자주 씁니다. 접수일 기준으로 마감일을 계산하거나, 월별 보고서에서 해당 월만 뽑아야 할 때 필요합니다.

• TODAY: 오늘 날짜를 표시합니다.
• YEAR: 날짜에서 연도를 가져옵니다.
• MONTH: 날짜에서 월을 가져옵니다.
• DAY: 날짜에서 일을 가져옵니다.
• DATEDIF: 두 날짜 사이의 기간을 계산합니다.
• EOMONTH: 특정 날짜 기준 월말 날짜를 구합니다.

예를 들어 계약일에서 30일 뒤를 마감일로 잡는다면 단순히 날짜 셀에 30을 더하면 됩니다. 월말 기준 정산일이 필요하다면 EOMONTH가 편합니다. 이런 함수는 처음에는 사소해 보이지만, 양식이 반복될수록 손작업을 크게 줄여줍니다.

내 엑셀 함수표를 만드는 방법

함수를 따로 외우는 대신 나만의 함수표를 만들어두면 좋습니다. 저는 보통 함수 이름, 쓰는 상황, 예시 수식, 주의할 점을 네 칸으로 나눕니다. 이 방식은 검색보다 빠르고, 예전에 막혔던 지점을 다시 밟지 않게 해줍니다.

• 함수 이름: SUMIFS
• 쓰는 상황: 조건이 2개 이상인 합계 계산
• 예시 수식: =SUMIFS(합계범위, 조건범위1, 조건1, 조건범위2, 조건2)
• 주의할 점: 합계 범위와 조건 범위의 행 수가 같아야 합니다.

처음부터 많이 넣을 필요는 없습니다. 계산 함수 6개, 조건 함수 5개, 조회 함수 4개, 텍스트 함수 6개, 날짜 함수 6개 정도면 충분합니다. 대략 25개 안팎만 제대로 적어도 대부분의 기본 문서 작업은 막히는 시간이 줄어듭니다.

근데 여기서 중요한 건 예시 수식을 실제 업무 파일 기준으로 적는 겁니다. 교재식 예제보다 “A열은 거래처, B열은 월, C열은 금액”처럼 내가 자주 보는 표 구조로 적어두면 나중에 바로 꺼내 쓰기 좋습니다. 함수 설명보다 내 업무 상황이 먼저 보여야 합니다.

자주 헷갈리는 부분은 이렇게 구분하면 편하다

COUNT와 COUNTA는 초반에 많이 헷갈립니다. COUNT는 숫자가 있는 셀만 세고, COUNTA는 글자든 숫자든 비어 있지 않으면 셉니다. 참석자 명단처럼 이름 개수를 세려면 COUNTA가 맞고, 점수 입력 칸처럼 숫자 개수를 세려면 COUNT가 맞습니다.

SUMIF와 SUMIFS도 비슷합니다. 조건이 하나면 SUMIF, 조건이 여러 개면 SUMIFS를 쓰면 됩니다. 다만 실무에서는 조건이 나중에 추가되는 일이 많아서 처음부터 SUMIFS로 익히는 편이 덜 헷갈립니다.

VLOOKUP과 XLOOKUP은 가능하면 XLOOKUP부터 쓰는 게 편합니다. 다만 회사 PC의 엑셀 버전이 오래된 경우에는 XLOOKUP이 없을 수 있습니다. 그럴 때는 VLOOKUP이나 INDEX와 MATCH 조합을 써야 합니다. 파일을 다른 사람과 공유해야 한다면 상대방의 엑셀 버전도 한 번 확인하는 게 좋습니다.

엑셀 함수는 많이 아는 사람보다 필요한 순간에 바로 꺼내는 사람이 일을 빨리 끝냅니다. 처음에는 함수표 하나 만들어두고, 막힌 작업이 생길 때마다 한 줄씩 추가하는 식이 제일 현실적입니다. 무료 기본 도구인 엑셀 안에서도 이 정도만 익혀두면 파일 다루는 속도가 꽤 달라집니다.