엑셀 날짜별 데이터 추출, 건수/합계 구하기 (FILTER, OFFSET 함수 활용)

 

 

엑셀 날짜별 데이터 추출, 건수/합계 구하기 (FILTER, OFFSET 함수 활용)

데이터 분석의 핵심은 원하는 정보를 빠르고 정확하게 추출하는 데 있습니다. 엑셀의 강력한 함수들을 활용하면 날짜 범위에 맞는 데이터 추출 및 건수, 합계 계산을 효율적으로 자동화할 수 있습니다. 특히 FILTER 함수와 OFFSET 함수의 조합은 동적 데이터 처리에 탁월한 성능을 발휘합니다. 이 글에서는 두 함수의 활용법을 자세히 알아보고, 실무에 바로 적용 가능한 팁들을 공유합니다. 데이터 분석 능력을 한 단계 업그레이드하고 싶으신가요? 그렇다면 지금 바로 시작해 보세요!

FILTER 함수: 데이터 추출의 새로운 패러다임

과거 엑셀에서 특정 날짜 구간의 데이터를 추출하려면, '고급 필터' 기능을 사용하는 것이 일반적이었습니다. 하지만 이 방법은 정적이라 날짜 범위가 바뀔 때마다 설정을 다시 해야 하는 불편함이 있었죠. 😫 하지만 이제는 다릅니다! Microsoft 365와 Excel 2021 이후 버전에 등장한 FILTER 함수는 이러한 문제를 말끔히 해결해 줍니다. 🎉 FILTER 함수는 마치 마법처럼 조건에 맞는 데이터만 쏙쏙 골라내는 놀라운 능력을 가지고 있답니다. ✨

FILTER 함수 기본 구문과 작동 원리

FILTER 함수의 기본 구문은 FILTER(배열, 조건, [if_empty])입니다. '배열'은 데이터 추출 대상 범위를, '조건'은 추출 조건을 나타내는 논리식을 의미합니다. 'if_empty'는 조건에 맞는 데이터가 없을 경우 표시할 값을 지정하는 선택적 인수입니다. 만약 조건에 맞는 데이터가 하나도 없다면?! 😱 'if_empty' 인수를 사용하지 않으면 빈 배열이 반환됩니다.

예를 들어, B3:E17 범위의 데이터에서 H2 셀의 시작 날짜와 J2 셀의 종료 날짜 사이에 있는 데이터만 추출하려면 다음과 같은 수식을 사용할 수 있습니다.

=FILTER(B3:E17,(B3:B17>=H2)*(B3:B17<=J2))

여기서 (B3:B17>=H2)*(B3:B17<=J2) 부분이 바로 마법의 핵심! 두 개의 논리식을 곱셈 연산자로 연결했는데요, 엑셀에서 TRUE는 1, FALSE는 0으로 처리됩니다. 💡 따라서 두 조건을 모두 만족하는 경우에만 TRUE(1) 값을 반환하게 되는 것이죠!

FILTER 함수와 동적 배열: #SPILL! 오류 해결

FILTER 함수는 동적 배열 함수이기 때문에, 수식을 입력한 셀(예: G6)을 기준으로 추출된 데이터가 자동으로 인접 셀에 채워집니다. 이 얼마나 편리한 기능인가요! 🤩 하지만 결과가 채워질 영역에 다른 데이터가 있다면 #SPILL! 오류가 발생할 수 있습니다. 😭 이 오류를 방지하려면 FILTER 함수 결과가 표시될 영역에 충분한 공간을 확보해야 합니다. 미리 공간을 비워두는 센스! 😉

OFFSET 함수: 동적 범위 지정의 마스터

FILTER 함수로 원하는 데이터를 추출했다면, 이제 건수와 합계를 계산해야겠죠? OFFSET 함수는 기준 셀에서 지정된 행과 열만큼 떨어진 위치에 있는 셀 또는 셀 범위를 반환하는 함수입니다. OFFSET 함수의 기본 구문은 OFFSET(reference, rows, cols, [height], [width])입니다. 'reference'는 기준 셀, 'rows'와 'cols'는 기준 셀에서 떨어진 행과 열의 개수, 'height'와 'width'는 반환할 셀 범위의 높이와 너비를 의미합니다. OFFSET 함수는 동적 범위 지정에 특히 유용하며, 다른 함수와 조합하여 다양한 계산을 수행할 수 있도록 도와줍니다.

OFFSET 함수와 COUNTA 함수: 데이터 건수 계산

추출된 데이터의 건수를 계산하려면, COUNTA 함수와 OFFSET 함수를 함께 사용하면 됩니다. COUNTA 함수는 비어 있지 않은 셀의 개수를 반환하는 함수죠. 추출된 데이터가 G6 셀부터 시작하여 최대 G100 셀까지 표시된다고 가정하면, 건수 계산 수식은 다음과 같습니다.

=COUNTA(OFFSET(G6,0,0,COUNTA(G6:G100),1))

OFFSET(G6,0,0,COUNTA(G6:G100),1) 부분은 G6 셀을 기준으로 G6:G100 범위에서 비어 있지 않은 셀의 개수만큼의 행과 1개의 열로 구성된 범위를 반환합니다. 즉, FILTER 함수로 추출된 데이터의 날짜 값들이 포함된 범위를 동적으로 지정하는 것이죠! COUNTA 함수는 이 범위 내의 비어 있지 않은 셀의 개수를 세어 추출된 데이터의 건수를 계산합니다.

OFFSET 함수와 SUM 함수: 데이터 합계 계산

수량 데이터가 J6 셀부터 시작한다고 가정하면, 합계 계산 수식은 다음과 같습니다.

=SUM(OFFSET(J6,0,0,COUNTA(J6:J100),1))

이 수식 역시 OFFSET 함수를 사용하여 추출된 데이터의 수량 값들이 포함된 범위를 동적으로 지정하고, SUM 함수를 통해 해당 범위의 값들을 합산하여 총합을 계산합니다.

INDEX & MATCH: OFFSET 함수의 대안

OFFSET 함수는 강력하지만, 셀 참조가 변경될 경우 수식이 깨질 위험이 있습니다. 더욱 안정적인 동적 범위 지정을 위해 INDEX & MATCH 조합을 사용하는 것을 추천합니다. INDEX 함수는 지정된 범위에서 특정 행과 열에 있는 값을 반환하고, MATCH 함수는 지정된 값이 범위에서 몇 번째 위치에 있는지 반환합니다. 이 두 함수를 조합하면 OFFSET 함수와 동일한 결과를 얻을 수 있으면서도, 셀 참조 변경에 대한 안정성을 확보할 수 있습니다.

예를 들어, OFFSET(G6,0,0,COUNTA(G6:G100),1) 대신 INDEX(G:G,SEQUENCE(COUNTA(G6:G100),1,ROW(G6))) 와 같이 사용할 수 있습니다. 이렇게 하면 G6 셀부터 시작하여 비어 있지 않은 셀의 개수만큼의 행을 동적으로 선택하여 범위를 지정합니다.

데이터 분석: 그 이상의 가능성

FILTER 함수와 OFFSET 함수, 그리고 INDEX & MATCH 함수를 활용하여 날짜 범위 기반 데이터 추출 및 집계 작업을 효율적이고 자동화된 방식으로 수행할 수 있습니다. 하지만 데이터 분석의 세계는 여기서 끝나지 않습니다! 추출된 데이터를 피벗 테이블로 요약하여 다양한 분석을 수행하거나, 차트로 시각화하여 데이터 트렌드를 파악할 수도 있습니다. 또한, FILTER 함수의 조건에 추가적인 논리식을 결합하여 더욱 복잡한 조건에 맞는 데이터를 추출할 수도 있습니다. 예를 들어 특정 제품 또는 특정 부서의 데이터만 추출하고 싶다면, 해당 조건을 추가하여 더욱 세분화된 분석을 수행할 수 있습니다.

마무리: 데이터 분석 전문가로 향하는 길

데이터 분석은 현대 비즈니스에서 필수적인 역량입니다. 엑셀의 강력한 함수들을 활용하여 데이터를 자유자재로 다루는 능력은 여러분의 커리어에 날개를 달아줄 것입니다. 끊임없는 학습과 실습을 통해 데이터 분석 전문가로 성장하고, 데이터 기반 의사 결정을 통해 성공적인 비즈니스를 이끌어 나가시기 바랍니다.