📑 목차
엑셀 실무에서는 함수를 많이 아는 것보다 자주 반복하는 업무에 맞는 함수를 정확히 쓰는 것이 중요합니다. 조건에 따라 결과를 표시하는 IF, 여러 조건의 합계를 구하는 SUMIFS, 기준표에서 값을 찾는 XLOOKUP만 익혀도 견적서·매출표·재고표·근태표 작업 시간을 크게 줄일 수 있습니다.
이 글은 바로 복사해 연습할 수 있도록 ‘날짜·지역·제품·수량·매출·담당자’로 구성된 실무형 표를 기준으로 설명합니다. 사용하는 Excel 버전과 지역 설정에 따라 쉼표 대신 세미콜론을 사용해야 할 수 있습니다.
함수 이름과 지원 버전을 공식 문서에서 확인하세요.
특히 XLOOKUP·FILTER·UNIQUE는 사용하는 Excel 버전에 따라 지원 여부가 다릅니다.
엑셀 실무 필수 함수 요약표
| 함수 | 하는 일 | 대표 활용 |
|---|---|---|
| IF | 조건에 따라 서로 다른 결과 표시 | 달성·미달, 재고부족, 합격 판정 |
| SUMIFS | 여러 조건을 만족하는 값의 합계 | 지역·담당자·기간별 매출 |
| COUNTIFS | 여러 조건에 맞는 행 개수 | 결근 횟수, 주문 건수, 불량 건수 |
| XLOOKUP | 기준값을 찾아 같은 행의 결과 반환 | 사번으로 이름·부서·단가 찾기 |
| IFERROR | 수식 오류를 지정한 값으로 대체 | #N/A·#DIV/0! 대신 안내문 표시 |
| TEXT | 숫자·날짜를 원하는 표시 형식의 텍스트로 변환 | 보고서 날짜, 코드, 금액 문구 |
| TODAY·EOMONTH | 오늘 날짜와 월말 계산 | 마감일·연체·근속기간 관리 |
예제 열 구성: A열 날짜, B열 지역, C열 제품, D열 수량, E열 매출, F열 담당자로 가정합니다. 실제 파일에서는 열 위치에 맞게 범위를 바꾸세요.
1. IF 함수: 조건에 따라 결과 나누기
IF는 조건이 참일 때와 거짓일 때 표시할 결과를 정합니다. E2의 매출이 100만 원 이상이면 ‘달성’, 아니면 ‘미달’을 표시하는 수식입니다.
=IF(E2>=1000000,"달성","미달")재고가 10개 미만이면 발주가 필요하다고 표시하려면 다음처럼 사용합니다.
=IF(D2<10,"발주 필요","정상")조건이 많아 IF를 여러 번 겹치면 수식이 읽기 어려워집니다. 세 단계 이상의 등급을 나눌 때는 IFS 함수나 별도의 기준표와 XLOOKUP을 검토하는 것이 좋습니다.
2. SUMIFS 함수: 여러 조건의 합계 구하기
SUMIFS는 합계를 구할 범위를 먼저 적고, 조건범위와 조건을 쌍으로 이어 붙입니다. 서울 지역의 제품A 매출 합계를 구하는 예제입니다.
=SUMIFS($E$2:$E$100,$B$2:$B$100,"서울",$C$2:$C$100,"제품A")조건을 셀에 입력해 바꿔가며 조회하려면 문자열 대신 셀 주소를 사용합니다. H2에 지역, I2에 제품명을 입력한 경우입니다.
=SUMIFS($E$2:$E$100,$B$2:$B$100,H2,$C$2:$C$100,I2)범위를 아래로 복사해도 움직이지 않게 하려면 F4 키로 `$`가 붙은 절대참조를 사용하세요. 합계범위와 모든 조건범위의 행 수가 같아야 올바르게 계산됩니다.
3. COUNTIFS 함수: 조건에 맞는 건수 세기
COUNTIFS는 합계가 아니라 조건을 만족하는 행의 개수를 셉니다. 서울 지역에서 제품A를 판매한 건수를 구하는 수식입니다.
=COUNTIFS($B$2:$B$100,"서울",$C$2:$C$100,"제품A")월별 건수를 집계할 때는 시작일 이상, 다음 달 시작일 미만 조건을 함께 쓰면 월말 날짜가 달라도 안전합니다.
=COUNTIFS($A$2:$A$100,">="&H2,$A$2:$A$100,"<"&EDATE(H2,1))4. XLOOKUP 함수: 기준표에서 원하는 값 찾기
XLOOKUP은 사번·상품코드처럼 고유한 값을 검색하고 같은 행의 이름·단가·부서 등을 돌려줍니다. H2의 상품코드를 상품표 A열에서 찾아 C열의 단가를 가져오는 예제입니다.
=XLOOKUP(H2,상품표!$A$2:$A$100,상품표!$C$2:$C$100,"코드 없음")- 첫 번째 인수: 찾을 값
- 두 번째 인수: 찾을 값이 있는 범위
- 세 번째 인수: 결과를 가져올 범위
- 네 번째 인수: 찾지 못했을 때 표시할 문구
버전 주의: Microsoft 공식 안내에 따르면 XLOOKUP은 Excel 2016과 Excel 2019에서 사용할 수 없습니다. 구버전 사용자와 파일을 공유한다면 VLOOKUP 또는 INDEX·MATCH 조합을 사용하세요.
5. IFERROR 함수: 오류 대신 안내문 표시하기
조회값이 없거나 0으로 나눌 때 발생하는 오류를 지정한 값으로 바꿉니다. XLOOKUP을 지원하지 않는 환경에서 VLOOKUP 오류를 감추는 예제입니다.
=IFERROR(VLOOKUP(H2,$A$2:$C$100,3,FALSE),"코드 확인")수량 D2로 매출 E2를 나누되 수량이 0이면 빈칸으로 표시할 수도 있습니다.
=IFERROR(E2/D2,"")IFERROR는 모든 오류를 가리므로 원래 수식이 잘못됐는데도 알아채지 못할 수 있습니다. 먼저 오류 원인을 확인한 뒤 사용자에게 보여줄 최종 표에서 적용하세요.
6. TEXT 함수: 날짜와 숫자 표시 형식 만들기
TEXT는 숫자나 날짜를 원하는 형식의 텍스트로 바꿉니다. 다른 문구와 날짜를 연결할 때 유용하지만 결과가 텍스트가 되므로 이후 합계 계산에는 원본 숫자 셀을 사용하는 것이 좋습니다.
=TEXT(A2,"yyyy-mm-dd")="매출 "&TEXT(E2,"#,##0원")=TEXT(A2,"aaa")마지막 수식은 날짜의 요일을 짧은 형식으로 표시합니다. 표시 언어는 운영체제와 Excel 지역 설정에 따라 달라질 수 있습니다.
7. TODAY·EOMONTH: 마감일과 남은 날짜 계산하기
TODAY는 오늘 날짜를 반환하며 파일을 열거나 다시 계산할 때 갱신됩니다. A2의 마감일까지 남은 일수를 구하려면 다음 수식을 사용합니다.
=A2-TODAY()오늘이 속한 달의 마지막 날짜는 EOMONTH로 구할 수 있습니다.
=EOMONTH(TODAY(),0)다음 달 마지막 날은 두 번째 인수를 1로, 지난달 마지막 날은 -1로 바꾸면 됩니다.
실무에서 수식 오류를 줄이는 6가지 습관
- 원본 데이터는 한 행에 한 건씩 입력하고 중간 빈 행을 만들지 않습니다.
- 금액과 날짜를 문자로 저장하지 않았는지 확인합니다.
- 반복해서 복사할 범위에는 F4로 절대참조를 지정합니다.
- SUMIFS와 COUNTIFS의 조건범위 크기를 동일하게 맞춥니다.
- 조회용 상품코드 앞뒤의 숨은 공백과 숫자·문자 형식 차이를 확인합니다.
- IFERROR를 적용하기 전에 원래 오류가 왜 발생했는지 먼저 찾습니다.
같이 알아두면 좋은 단축키
| 단축키 | 기능 |
|---|---|
| Ctrl + T | 선택 범위를 표로 만들기 |
| Ctrl + Shift + L | 필터 켜기·끄기 |
| Ctrl + 1 | 셀 서식 창 열기 |
| F4 | 수식 입력 중 절대·혼합 참조 전환 |
| Ctrl + ; | 현재 날짜 입력 |
| Alt + = | 자동 합계 삽입 |
자주 묻는 질문
Q1. SUMIF와 SUMIFS는 무엇이 다른가요?
SUMIF는 기본적으로 한 조건의 합계, SUMIFS는 여러 조건의 합계에 사용합니다. 두 함수는 합계범위의 인수 위치도 다르므로 수식을 바꿀 때 주의해야 합니다.
Q2. XLOOKUP에서 #N/A가 표시되는 이유는 무엇인가요?
찾는 값이 없거나 숫자와 문자의 형식이 다르거나 앞뒤에 공백이 있을 수 있습니다. XLOOKUP의 네 번째 인수에 ‘코드 없음’ 같은 문구를 지정하면 미일치 결과를 구분하기 쉽습니다.
Q3. XLOOKUP을 Excel 2019에서 사용할 수 있나요?
Microsoft 공식 문서에 따르면 Excel 2016과 2019에서는 지원되지 않습니다. VLOOKUP이나 INDEX·MATCH를 사용해야 합니다.
Q4. 수식을 아래로 복사하면 범위가 바뀌는 이유는 무엇인가요?
기본 셀 참조는 상대참조이기 때문입니다. 고정할 범위를 선택하고 F4를 눌러 `$A$2:$A$100`처럼 절대참조로 바꾸세요.
Q5. 수식 결과가 0으로만 나오는 이유는 무엇인가요?
숫자가 문자 형식으로 저장됐거나 조건 문구가 실제 데이터와 다르거나 합계범위와 조건범위의 크기가 다를 수 있습니다. 셀 형식과 숨은 공백도 확인하세요.
공식 참고자료
- Microsoft 지원: SUMIFS 함수
- Microsoft 지원: XLOOKUP 함수와 지원 버전
- Microsoft 지원: IFERROR 함수
- Microsoft 지원: 조건에 따라 숫자·날짜 개수 계산
- Microsoft 지원: Excel 함수 범주별 목록