엑셀 꿀팁 5가지, 업무 상황별로 바로 쓰는 함수 정리
바쁜 현대인을 위한 3줄 요약
- 업무 상황을 먼저 확인한 뒤, 판단표에서 맞는 함수를 고르는 순서로 시작하세요.
- 찾는 값이 왼쪽 열에 있으면 VLOOKUP 대신 XLOOKUP이나 INDEX+MATCH를 씁니다.
- 같은 오류가 세 번 이상 반복되면 원본 데이터 구조를 다시 점검할 시점입니다.
엑셀 꿀팁은 단축키를 많이 아는 것보다 상황에 맞는 함수를 고르는 판단이 더 중요합니다. 이 글은 Microsoft 공식 지원 문서를 근거로 값 찾기부터 오류 처리까지 실무에서 자주 막히는 지점을 정리했습니다. 판단표와 비교표로 어떤 기능을 언제 써야 하는지 바로 확인할 수 있습니다.
어떤 상황에 어떤 기능을 써야 할지 판단하는 기준
아래 표는 업무 중 자주 마주치는 상황을 기준으로 1순위로 시도할 기능과, 그 기능이 통하지 않을 때의 대안을 정리한 것입니다.
| 업무 상황 | 1순위 기능 | 안 될 때 대안 |
|---|---|---|
| 다른 표에서 값을 그대로 가져오고 싶다 | VLOOKUP | 찾는 값이 왼쪽 열에 있으면 XLOOKUP 또는 INDEX+MATCH |
| 조건에 맞는 값만 더하거나 세고 싶다 | SUMIF, COUNTIF | 조건이 2개 이상이면 SUMIFS, COUNTIFS |
| 이름 분리·형식 통일처럼 텍스트를 정리하고 싶다 | 빠른 채우기(Ctrl+E) | 패턴이 불규칙하면 LEFT, RIGHT, MID 같은 텍스트 함수 |
| 같은 데이터가 중복 입력됐는지 확인하고 싶다 | 중복된 항목 제거 | 삭제 전 원본 데이터를 별도 시트에 백업 |
| 수식이 오류로 표시된다 | IFERROR로 대체값 지정 | 대체값보다 먼저 참조 범위와 데이터 형식을 확인 |
값 찾기부터 오류 방지까지, 상황별 엑셀 꿀팁 5가지 기능
- VLOOKUP — 다른 표에서 값을 그대로 가져올 때 사용합니다. 조회 값이 있는 열보다 오른쪽에 있는 값만 가져올 수 있습니다. 찾는 값이 오른쪽 열에 있다면 이 함수로 해결됩니다. 자세한 구문은 VLOOKUP 함수 공식 문서에서 확인할 수 있습니다.
- XLOOKUP — 검색 방향에 상관없이 값을 찾을 때 사용합니다. Microsoft 365와 Excel 2021 이상에서 사용할 수 있고, 이전 버전에서는 지원되지 않습니다. 이 함수가 실행되지 않는다면 버전 문제일 가능성이 있습니다. XLOOKUP 함수 공식 문서에 사용 가능 버전이 명시돼 있습니다.
- INDEX와 MATCH의 조합 — 조회 값이 왼쪽 열에 있거나 조건이 복잡할 때 두 함수를 함께 사용합니다. VLOOKUP, INDEX, MATCH 비교 공식 문서에 조합 방법이 나와 있습니다.
- SUMIF — 조건에 맞는 값만 더할 때 사용합니다. 조건이 하나일 때 가장 간단하게 통과됩니다. SUMIF 함수 공식 문서를 참고하십시오.
- COUNTIF — 조건에 맞는 셀 개수를 셀 때 사용합니다. 여기서 개수가 예상과 다르게 나오면 조건 범위 지정이 잘못됐을 가능성이 큽니다. COUNTIF 함수 공식 문서에 조건 작성법이 있습니다.
- 빠른 채우기(Ctrl+E) — 이름 분리, 형식 통일처럼 패턴이 있는 텍스트를 정리할 때 사용합니다. 이 단축키로 결과가 채워지는 곳은 셀 자체이며, 클립보드에 저장되는 것이 아닙니다. 빠른 채우기 공식 문서에 패턴 인식 조건이 정리돼 있습니다.
- 중복된 항목 제거 — 같은 데이터가 여러 번 입력됐는지 확인하고 정리할 때 사용합니다. 삭제는 되돌릴 수 없으므로 원본을 먼저 백업하는 것이 안전합니다. 중복 항목 찾기·제거 공식 문서에 절차가 나와 있습니다.
VLOOKUP과 XLOOKUP, 지금은 어떤 걸 써야 할까
두 함수는 역할이 비슷하지만 검색 방향과 사용 가능 버전이 다릅니다. 아래 표로 한 번에 비교할 수 있습니다.
| 항목 | VLOOKUP | XLOOKUP |
|---|---|---|
| 검색 방향 | 조회 값 기준 오른쪽만 | 왼쪽·오른쪽 모두 가능 |
| 일치하는 값이 없을 때 | 기본적으로 #N/A 오류 반환 | 인자로 대체값 직접 지정 가능 |
| 사용 가능 버전 | 모든 버전에서 사용 가능 | Microsoft 365, Excel 2021 이상만 가능 |
두 함수의 세부 사양은 각각 VLOOKUP 함수 공식 문서와 XLOOKUP 함수 공식 문서를 기준으로 정리했습니다. 파일을 다른 사람과 공유해야 하는 상황이면, 상대방의 엑셀 버전을 먼저 확인하는 것이 순서입니다.
함수가 안 먹힐 때, 이럴 땐 이렇게
같은 함수인데 결과가 다르게 나온다면 오류 코드부터 구분해야 합니다.
| 오류 코드 | 주로 발생하는 원인 | 1차 조치 |
|---|---|---|
| #N/A | 조회 값이 범위에 없거나 오타가 있음 | 범위와 값을 다시 확인하거나 IFERROR로 대체값 지정 |
| #NAME? | 수식에서 정의되지 않은 이름을 참조하거나 함수명에 오타가 있음. XLOOKUP처럼 구버전에서 지원하지 않는 함수를 쓴 경우에도 같은 오류가 나타날 수 있음 | 함수명 오타 확인 후, 구버전이면 VLOOKUP이나 INDEX+MATCH로 대체 |
| #VALUE! | 숫자 계산에 텍스트 형식 데이터가 섞임 | 셀 서식을 숫자로 통일 후 재입력 |
오류를 화면에서 감추기만 하려면 IFERROR 함수 공식 문서를 참고해 대체값을 지정할 수 있습니다. 다만 위 표의 1차 조치를 두세 번 시도해도 같은 오류가 반복된다면, 함수 수정보다 원본 데이터의 형식이나 구조 자체를 다시 살펴볼 시점입니다.
오늘 바로 적용할 엑셀 꿀팁 정리
업무 상황을 먼저 판단표로 확인하고, 상황에 맞는 함수를 골라 적용한 뒤, 오류가 나면 원인을 코드별로 좁혀가는 순서로 진행하면 됩니다. 같은 오류가 세 번 이상 반복되면 함수보다 원본 데이터 구조를 먼저 점검할 시점입니다.
지금 다루고 있는 표에 위 판단표의 상황 중 하나를 대입해 바로 확인해보십시오.
