엑셀 여러 시트 조건에 맞는 데이터만 추출하기 (FILTER, VSTACK 함수 활용)

 

 

데이터 분석에 날개를 달아줄 엑셀 필터 기능, 제대로 활용하고 계신가요? 특히 여러 시트에 흩어진 데이터를 한 번에 분석해야 할 때, VSTACK과 FILTER 함수의 조합은 그야말로 신세계! 복잡한 데이터 정리, 이제 걱정 마세요! 엑셀 함수 마법으로 순식간에 원하는 데이터만 쏙쏙 뽑아내는 비법, 지금 바로 공개합니다! 😉

데이터 통합과 필터링의 환상적인 만남: VSTACK & FILTER

여러 시트에 흩어져 있는 데이터를 하나로 모으고, 원하는 조건에 맞는 정보만 추출하는 것은 데이터 분석의 기본 중 기본! 하지만, 시트를 일일이 옮겨 다니며 복사-붙여넣기 하느라 시간을 낭비하고 계시진 않나요? 엑셀의 VSTACK과 FILTER 함수만 알면 이런 수고는 이제 그만! 복잡한 데이터 작업을 자동화하고 분석 효율을 극대화하는 방법, 지금부터 자세히 알려드릴게요!

VSTACK 함수: 데이터 통합의 마법사

VSTACK 함수는 마치 마법처럼 여러 시트의 데이터를 하나의 테이블로 쌓아 올려줍니다. 예를 들어 VSTACK(Sheet1!A1:C10, Sheet2!A1:C10, Sheet3!A1:C10)처럼 사용하면 Sheet1, Sheet2, Sheet3의 A1:C10 범위 데이터가 마치 레고 블록처럼 차곡차곡 쌓여 하나의 테이블이 됩니다. 시트 이름에 공백이나 특수 문자가 있다면? 당황하지 마세요! 'Sheet 1'!A1:C10 처럼 작은따옴표로 감싸주면 됩니다. 각 시트의 데이터 범위가 다르더라도 VSTACK 함수 안에서 각 범위를 지정하면 OK! 단, 모든 시트의 열 개수는 동일해야 한다는 점, 꼭 기억해 두세요! Microsoft 365 또는 Excel 2021 이상 버전을 사용하고 있다면 바로 적용 가능! 이전 버전에서는 CHOOSE 함수와 INDIRECT 함수 조합으로 비슷한 기능을 구현할 수 있지만, 수식이 복잡해지고 성능이 느려질 수 있다는 점 유의하세요.🤔

FILTER 함수: 조건에 딱 맞는 데이터만 쏙쏙!

FILTER 함수는 이름 그대로, 특정 조건에 맞는 데이터만 걸러내는 역할을 합니다. FILTER(배열, 조건, [if_empty]) 형태로 사용하는데, VSTACK으로 합친 데이터 범위를 "배열"에 넣고, 원하는 조건을 "조건"에 입력하면 끝! 예를 들어 판매량(C열)이 100 이상인 데이터만 뽑고 싶다면 CHOOSECOLS(VSTACK(Sheet1!A1:C10, Sheet2!A1:C10, Sheet3!A1:C10),3)>100 와 같이 조건을 작성하면 됩니다. 여기서 CHOOSECOLS 함수는 특정 열만 쏙 뽑아내는 역할을 하죠! 세 번째 인수 [if_empty]는 조건에 맞는 데이터가 없을 때 표시할 값을 지정하는 선택적 인수입니다. FILTER 함수 덕분에 복잡한 IF 함수 중첩 없이도 간편하게 원하는 데이터만 추출할 수 있습니다. 훨씬 깔끔하죠? ✨

LET 함수: 복잡한 수식도 깔끔하게 정리!

LET 함수는 마치 수식 정리 전문가처럼 복잡한 수식을 깔끔하게 만들어줍니다. 수식 안에서 사용할 변수를 정의하고, 이 변수를 이용해 수식을 작성할 수 있도록 도와주죠. 예를 들어 LET(데이터, VSTACK(Sheet1:Sheet3!A1:C10), FILTER(데이터, CHOOSECOLS(데이터,3)>100)) 처럼 사용하면 VSTACK 결과를 "데이터"라는 변수에 저장하고, 이 변수를 FILTER 함수에서 사용할 수 있습니다. 이렇게 하면 수식이 훨씬 간결해지고 이해하기 쉬워질 뿐만 아니라, 중복 계산을 피할 수 있어 성능 향상에도 도움이 됩니다. LET 함수 역시 Microsoft 365 또는 Excel 2021 이상 버전에서 사용 가능합니다.

실전 예제: 서울 지역 판매량 500개 이상인 제품 데이터 추출하기

자, 이제 실제 예시를 통해 FILTER와 VSTACK 함수의 활용법을 제대로 익혀볼까요? 세 개의 시트(Sheet1, Sheet2, Sheet3)에 "제품명," "지역," "판매량" 데이터가 있다고 가정해 보겠습니다. "서울" 지역에서 판매량이 500개 이상인 제품 데이터만 추출하려면 어떻게 해야 할까요? 정답은 바로 아래 수식입니다!

=LET(
    데이터, VSTACK(Sheet1:Sheet3!A1:C10),
    FILTER(데이터, (CHOOSECOLS(데이터,2)="서울")*(CHOOSECOLS(데이터,3)>=500))
)

CHOOSECOLS 함수로 "지역" 열과 "판매량" 열을 추출하고, * 연산자로 AND 조건을 만들어 "서울 지역 & 판매량 500개 이상" 조건을 만족하는 데이터만 뽑아낼 수 있습니다. 참 쉽죠? 😊

무궁무진한 활용 가능성: 다양한 함수와의 조합

FILTER와 VSTACK 함수는 다른 함수들과도 환상의 궁합을 자랑합니다. SUMIFS, COUNTIFS, AVERAGEIFS 같은 조건부 집계 함수와 함께 사용하면 특정 조건에 맞는 데이터의 합계, 개수, 평균 등을 간편하게 계산할 수 있습니다. UNIQUE 함수와 결합하면 중복 데이터를 제거하고 고유한 값만 추출할 수 있고, SORT 함수를 활용하면 특정 열을 기준으로 데이터 정렬도 가능합니다. XLOOKUP이나 VLOOKUP 함수와 함께 사용하면 추출된 데이터를 바탕으로 다른 데이터를 조회할 수도 있죠. 활용 방법은 정말 무궁무진합니다!

데이터 분석, 이제 엑셀 함수로 날개를 달자!

FILTER와 VSTACK 함수는 엑셀에서 대용량 데이터를 효율적으로 처리하고 분석하는 데 필수적인 도구입니다. 이러한 함수들을 잘 활용하면 복잡한 데이터 작업을 자동화하고, 정확한 분석 결과를 얻어 업무 생산성을 획기적으로 높일 수 있습니다. 다양한 함수 조합과 꾸준한 연습을 통해 엑셀의 강력한 기능을 마스터하고 데이터 분석 전문가로 거듭나세요! 궁금한 점이나 더 알고 싶은 내용이 있다면 언제든 댓글 남겨주세요! 함께 엑셀의 세계를 탐험해 봐요! 🚀