엑셀에서 날짜순으로 정렬된 데이터에서 월별 마지막 날의 정보를 추출하는 것은 업무에서 자주 발생하는 과제입니다. 특히 집계표에서 각 월의 최종 데이터를 효율적으로 찾아야 할 때 LOOKUP과 TEXT 함수의 조합은 매우 강력한 도구가 됩니다. 이 함수들을 활용하면 복잡한 조건문 없이도 간단한 수식으로 원하는 결과를 얻을 수 있어 시간과 노력을 크게 절약할 수 있습니다.
LOOKUP 함수의 기본 원리와 활용법
LOOKUP 함수는 벡터 형태의 데이터에서 특정 값을 찾아 대응하는 결과를 반환하는 함수입니다. 이 함수의 가장 큰 특징은 정확히 일치하는 값이 없을 때 가장 가까운 작은 값을 반환한다는 점입니다. 이러한 특성을 이용하면 오름차순으로 정렬된 날짜 데이터에서 월별 마지막 날짜의 정보를 효과적으로 추출할 수 있습니다. LOOKUP 함수는 =LOOKUP(찾는 값, 찾을 범위, 결과 범위) 형태로 작성하며, 벡터 형태와 배열 형태 두 가지 방식으로 사용 가능합니다. 날짜 데이터 처리에서는 주로 벡터 형태를 활용하여 더 직관적이고 효율적인 결과를 얻을 수 있습니다.
TEXT 함수로 날짜 형식 변환하기
TEXT 함수는 숫자나 날짜 값을 지정된 형식의 텍스트로 변환하는 중요한 역할을 합니다. 날짜 데이터를 다룰 때 TEXT(날짜, “yymm”) 형식을 사용하면 연도와 월 정보만을 텍스트로 추출할 수 있어 월별 비교 작업이 훨씬 간편해집니다. 예를 들어 2024-04-15라는 날짜에 TEXT 함수를 적용하면 “2404”라는 텍스트로 변환되어 같은 월의 데이터들을 쉽게 식별할 수 있습니다.
- TEXT 함수는 다양한 형식 코드를 지원하여 원하는 형태로 날짜를 표시할 수 있습니다
- “yyyy-mm” 형식을 사용하면 년-월 형태로 표시되어 더 읽기 쉬운 결과를 얻을 수 있습니다
- TEXT 함수로 변환된 결과는 텍스트이므로 계산에는 사용할 수 없지만 비교 작업에는 매우 유용합니다
- 마이크로소프트 공식 문서에서 더 자세한 TEXT 함수 형식 코드를 확인할 수 있습니다
월별 마지막 데이터 추출 공식의 작동 원리
=LOOKUP(2, 1/(TEXT(날짜,”yymm”)=TEXT(E5,”yymm”)), 인원) 공식은 복잡해 보이지만 실제로는 매우 논리적으로 구성되어 있습니다. 먼저 TEXT 함수가 각 날짜를 “yymm” 형식으로 변환하고, 이를 검색하고자 하는 월과 비교하여 TRUE/FALSE 배열을 생성합니다. 1을 이 배열로 나누면 TRUE는 1이 되고 FALSE는 #DIV/0! 에러가 됩니다. LOOKUP 함수는 2라는 값을 찾지만 실제로는 없으므로 마지막 유효한 값인 1을 선택하게 되어 해당 월의 마지막 데이터를 반환합니다.
| 단계 | 처리 과정 | 결과 |
|---|---|---|
| TEXT 함수 적용 | 날짜를 yymm 형식으로 변환 | “2404”, “2404”, “2405” 등 |
| 비교 연산 | 각 날짜와 검색 월 비교 | TRUE, TRUE, FALSE 배열 |
| 나눗셈 연산 | 1을 TRUE/FALSE로 나누기 | 1, 1, #DIV/0! 배열 |
| LOOKUP 실행 | 2를 찾고 마지막 유효값 선택 | 해당 월 마지막 데이터 반환 |
실무에서 활용할 수 있는 응용 기법들
이 공식은 단순히 인원수 추출에만 그치지 않고 다양한 업무 상황에 응용할 수 있습니다. 매출 데이터에서 월말 실적을 추출하거나 재고 현황에서 월별 최종 수량을 파악하는 등 다양한 분야에서 활용 가능합니다. LOOKUP 함수의 공식 가이드를 참조하면 더 고급 기법들을 익힐 수 있습니다.
또한 이 공식을 변형하여 분기별이나 반기별 데이터 추출도 가능합니다. TEXT 함수의 형식 코드를 “yyyyq”로 변경하면 분기별 데이터를, “yyyyh”로 변경하면 반기별 데이터를 처리할 수 있어 다양한 기간 단위의 분석 작업에 활용할 수 있습니다.
공식 작성 시 주의사항과 오류 해결
이 공식을 사용할 때 가장 중요한 점은 원본 데이터가 날짜 순으로 정렬되어 있어야 한다는 것입니다. LOOKUP 함수는 정렬된 데이터를 전제로 작동하므로 순서가 뒤바뀐 데이터에서는 정확한 결과를 얻을 수 없습니다. 또한 날짜 데이터의 형식이 일관되게 유지되어야 하며, 텍스트로 입력된 가짜 날짜는 올바르게 처리되지 않을 수 있습니다.
공식에서 #N/A 오류가 발생한다면 대부분 데이터 형식 문제이거나 검색하고자 하는 월이 데이터에 존재하지 않는 경우입니다. 이런 상황에서는 TEXT 함수 심화 활용법을 참고하여 데이터 형식을 점검하고 필요시 IFERROR 함수를 추가하여 오류 처리를 강화할 수 있습니다. 또한 명명된 범위를 사용하면 공식의 가독성을 높이고 유지보수를 용이하게 할 수 있습니다.
