[직장인 엑셀 2탄] 알면 '엑셀 고수' 소리 듣는 강력한 함수 BEST 5!

 

textjoin 함수 예시

 

지난번 기본 필수 함수 5가지에 이어, 이번에는 업무 효율을 한 차원 더 끌어올려 줄 '실무 심화 함수'들을 준비했습니다. 이 함수들을 다루기 시작하면 사무실에서 "엑셀 좀 하네?"라는 칭찬을 들으실 수 있을 거예요! 😎

---

1. XLOOKUP : VLOOKUP의 완벽한 진화형 👑

VLOOKUP의 치명적인 단점(기준값의 오른쪽에 있는 데이터만 가져올 수 있음)을 완벽하게 해결한 최신 함수입니다. 왼쪽, 오른쪽 상관없이 데이터를 가져오며, 에러 처리(IFERROR) 기능까지 자체적으로 품고 있습니다.
(단, 엑셀 2021 이상 또는 Microsoft 365 버전에서만 지원합니다.)

• 수식: =XLOOKUP(찾을값, 찾을범위, 가져올범위, [못찾을때반환값])
• 실무 활용 예시:
• 기준값이 맨 끝 열에 있어도 첫 번째 열의 데이터를 거꾸로 찾아올 때.
• =XLOOKUP("홍길동", D열(이름), A열(사번), "데이터없음")

2. SUBTOTAL : 필터링 된 화면 '그대로' 계산하기 🔎

일반적인 SUM 함수는 숨겨진 셀이나 필터로 걸러진 셀의 값까지 모두 더해버립니다. 하지만 SUBTOTAL을 사용하면 현재 화면에 필터링 되어 보이는 데이터만 정확하게 계산해 줍니다.

• 수식: =SUBTOTAL(함수번호, 참조범위)
• 함수번호: 9를 쓰면 합계(SUM), 1을 쓰면 평균(AVERAGE)을 구합니다.

• 실무 활용 예시:
• 전체 매출표에서 자동 필터로 '영업1팀'만 걸러냈을 때, 화면에 보이는 영업1팀만의 매출 합계를 실시간으로 보고 싶을 때.
• =SUBTOTAL(9, 매출액범위)

3. EDATE & EOMONTH : 날짜/기간 계산의 달인 📅

계약 만료일, 수습 기간 종료일 등 날짜를 다룰 때 필수입니다. 달마다 30일, 31일, 28일 등 일수가 달라서 단순 덧셈(+)으로는 날짜 계산이 꼬일 때가 많은데, 이 함수들이 깔끔하게 해결해 줍니다.

• 수식: =EDATE(시작일, 개월수), =EOMONTH(시작일, 개월수)
• 실무 활용 예시:
• EDATE (정확히 N개월 뒤 날짜): 오늘 계약했는데 3개월 뒤의 만료일을 구할 때. =EDATE(A2, 3)
• EOMONTH (N개월 뒤의 '마지막 날짜(말일)'): 다음 달 말일에 대금을 지급해야 할 때. =EOMONTH(A2, 1)

4. UNIQUE : 지긋지긋한 중복 값, 한 방에 싹! 🧹

복잡하고 방대한 리스트에서 중복을 제외하고 어떤 항목들이 있는지 '고유 값'만 뽑아내야 할 때 씁니다. 예전처럼 '중복된 항목 제거' 기능을 클릭할 필요 없이 수식 하나로 리스트가 자동으로 완성됩니다.

• 수식: =UNIQUE(범위)
• 실무 활용 예시:
• 1년간의 판매 내역 1만 줄에서, 도대체 어떤 '거래처'들이 있는지 중복 없는 거래처 명단만 뽑고 싶을 때.
• =UNIQUE(거래처명이_있는_열)

5. FILTER : 조건에 맞는 데이터만 쏙쏙 뽑아 배열하기 🪄

가장 혁신적인 최신 배열 함수 중 하나입니다. VLOOKUP은 하나의 값만 가져오지만, FILTER 함수는 조건에 맞는 '모든 데이터'를 한 번에 쫘르륵 불러와 새로운 표를 만들어 줍니다.

• 수식: =FILTER(가져올전체범위, 조건범위=조건, [결과가없을때값])
• 실무 활용 예시:
• 전체 사원 명부(A~F열)에서 '재무팀'에 해당하는 직원들의 모든 정보(행 전체)만 다른 시트에 실시간으로 표출하고 싶을 때.
• =FILTER(A2:F100, 부서열="재무팀", "해당직원없음")

---

💡 마무리하며
오늘 배운 SUBTOTAL이나 EDATE는 구버전 엑셀에서도 바로 쓸 수 있는 꿀 함수이고, XLOOKUP, UNIQUE, FILTER는 최신 엑셀 업무의 패러다임을 바꾼 강력한 무기들입니다.

1탄의 기본 함수와 2탄의 심화 함수를 조합하면 엑셀에서 못 해낼 업무가 거의 없습니다. 오늘 당장 여러분의 데이터에 마법을 부려보세요! 🚀