엑셀의 개별 함수들은 그 자체로도 강력하지만, 여러 함수를 조합할 때 진정한 시너지를 발휘하며 복잡한 데이터 분석 작업을 간소화해줍니다.
오늘은 FILTER, COUNTIF, VSTACK 이 세 가지 함수를 함께 사용하여 실제 업무에서 마주할 수 있는 다양한 시나리오를 해결하는 방법을 알아보겠습니다.
1. FILTER 함수: 원하는 데이터만 쏙쏙 뽑아내기
FILTER 함수는 특정 조건을 만족하는 데이터 행 또는 열만 추출하여 새로운 배열로 반환합니다.
대량의 데이터에서 필요한 정보만 걸러낼 때 매우 유용하죠.
기본 구문:=FILTER(array, include, [if_empty])
- array: 필터링할 원본 데이터 범위입니다.
- include: 조건을 지정하는 논리 배열입니다. TRUE인 행(또는 열)만 반환됩니다.
- if_empty (선택): 필터링 결과가 없을 때 반환할 값입니다.
예시: "강남 지점"의 판매 데이터만 추출하고 싶을 때:
=FILTER(A:C, A:A="강남")
2. COUNTIF 함수: 특정 조건의 개수 세기
COUNTIF 함수는 특정 범위에서 지정된 조건을 만족하는 셀의 개수를 셉니다. 데이터 요약 및 통계에 필수적인 함수입니다.
기본 구문:=COUNTIF(range, criteria)
- range: 조건을 확인할 범위입니다.
- criteria: 개수를 셀 조건입니다.
예시: "사과"의 판매 건수를 세고 싶을 때:
=COUNTIF(B:B, "사과")
3. VSTACK 함수: 여러 범위의 데이터를 수직으로 통합하기
VSTACK 함수는 여러 범위 또는 배열의 데이터를 수직으로(행 방향으로) 쌓아 하나의 배열로 통합합니다.
여러 시트에 흩어진 데이터를 한곳에 모을 때 특히 유용합니다.
기본 구문:=VSTACK(array1, [array2], ...)
- array1, array2, ...: 통합할 범위 또는 배열입니다.
예시: Sheet1과 Sheet2의 상품 목록을 합치고 싶을 때:
=VSTACK(Sheet1!A:A, Sheet2!A:A)
함수 조합으로 복잡한 실무 데이터 분석하기 (최상급 예제)
이제 이 세 함수를 조합하여 실제 업무에서 마주할 수 있는 시나리오를 해결해 보겠습니다.
가정: 우리는 각 지점에서 발생한 클레임 데이터를 관리하고 있으며, 데이터는 여러 시트(서울_클레임, 부산_클레임, 대구_클레임)에 분산되어 있습니다. 각 클레임에는 지점, 상품명, 클레임 유형, 발생일 등의 정보가 있습니다.
목표:
- 모든 지점의 클레임 데이터를 한 곳으로 모으기.
- 특정 기간(2025-01-01부터 2025-06-30까지)에 발생한 클레임 중 "불량" 유형의 클레임만 필터링하기.
- 필터링된 "불량" 클레임 중에서 각 상품별로 몇 건의 클레임이 발생했는지 집계하기.
원본 데이터 예시 (서울_클레임 시트):
| 지점 | 상품명 | 클레임 유형 | 발생일 |
| 서울 | 사과 | 불량 | 2025-01-15 |
| 서울 | 바나나 | 파손 | 2025-02-01 |
| 서울 | 사과 | 오배송 | 2025-03-10 |
| 서울 | 오렌지 | 불량 | 2025-04-22 |
(다른 시트에도 유사한 데이터가 있다고 가정합니다.)
단계 1: 모든 클레임 데이터 통합 (VSTACK)
먼저, 각 시트에 흩어져 있는 클레임 데이터를 하나의 배열로 통합합니다.
예를 들어, 각 시트의 데이터가 A1:D100 범위에 있다고 가정합니다.
=VSTACK(서울_클레임!A:D, 부산_클레임!A:D, 대구_클레임!A:D)
이 수식을 입력하면 모든 지점의 클레임 데이터가 한 곳에 모여 동적 배열로 나타납니다.
이 배열을 가상으로 모든_클레임_데이터라고 부르겠습니다.
단계 2: 특정 기간 및 유형으로 필터링 (FILTER)
이제 통합된 모든_클레임_데이터에서 2025년 상반기 (1월 1일 ~ 6월 30일) 에 발생한 "불량" 유형의 클레임만 필터링합니다.
클레임 유형은 3번째 열, 발생일은 4번째 열에 있다고 가정합니다.
=FILTER(
VSTACK(서울_클레임!A:D, 부산_클레임!A:D, 대구_클레임!A:D),
(VSTACK(서울_클레임!D:D, 부산_클레임!D:D, 대구_클레임!D:D) >= DATE(2025,1,1)) *
(VSTACK(서울_클레임!D:D, 부산_클레임!D:D, 대구_클레임!D:D) <= DATE(2025,6,30)) *
(VSTACK(서울_클레임!C:C, 부산_클레임!C:C, 대구_클레임!C:C) = "불량")
)
설명:
- VSTACK(...) 부분은 위에서 설명한 전체 클레임 데이터를 통합하는 부분입니다. 필터 조건에서도 각 열에 맞춰 VSTACK을 사용하여 통합된 열을 참조해야 합니다.
- * 연산자는 AND 조건을 의미합니다. 세 가지 조건(날짜 범위 시작, 날짜 범위 끝, 클레임 유형)을 모두 만족하는 행만 필터링합니다.
이 수식의 결과는 2025년 상반기 불량 클레임 데이터라는 새로운 동적 배열이 됩니다.
단계 3: 상품별 불량 클레임 건수 집계 (UNIQUE + COUNTIF)
마지막으로, 필터링된 2025년 상반기 불량 클레임 데이터에서 고유한 상품명을 추출하고, 각 상품명별로 클레임이 몇 건 발생했는지 집계합니다. 상품명은 통합된 데이터의 2번째 열에 있습니다.
먼저, 필터링된 데이터에서 상품명만 추출하는 부분을 만듭니다.
FILTER(VSTACK(서울_클레임!B:B, 부산_클레임!B:B, 대구_클레임!B:B), ...) (2단계의 필터 조건과 동일하게 사용)
이 결과를 UNIQUE 함수로 감싸 고유한 상품 목록을 만듭니다.
=UNIQUE(
FILTER(
VSTACK(서울_클레임!B:B, 부산_클레임!B:B, 대구_클레임!B:B),
(VSTACK(서울_클레임!D:D, 부산_클레임!D:D, 대구_클레임!D:D) >= DATE(2025,1,1)) *
(VSTACK(서울_클레임!D:D, 부산_클레임!D:D, 대구_클레임!D:D) <= DATE(2025,6,30)) *
(VSTACK(서울_클레임!C:C, 부산_클레임!C:C, 대구_클레임!C:C) = "불량")
)
)
이 수식은 2025년 상반기 불량 클레임 상품 목록을 반환합니다.
예를 들어, {"사과";"오렌지";"포도"}와 같은 형태일 것입니다. 이 목록을 특정 셀(G1이라고 가정)에 표시합니다.
이제 COUNTIF를 사용하여 각 상품의 개수를 셉니다. H1 셀에 다음 수식을 입력합니다.
=COUNTIF(
FILTER(
VSTACK(서울_클레임!B:B, 부산_클레임!B:B, 대구_클레임!B:B),
(VSTACK(서울_클레임!D:D, 부산_클레임!D:D, 대구_클레임!D:D) >= DATE(2025,1,1)) *
(VSTACK(서울_클레임!D:D, 부산_클레임!D:D, 대구_클레임!D:D) <= DATE(2025,6,30)) *
(VSTACK(서울_클레임!C:C, 부산_클레임!C:C, 대구_클레임!C:C) = "불량")
),
G1#
)
설명:
COUNTIF의 첫 번째 인수는 2단계에서 필터링된 상품명 목록입니다. 즉, "2025년 상반기에 발생한 불량 클레임의 상품명들"입니다.
G1#: G1 셀에 표시된 고유 상품 목록 전체를 참조합니다. COUNTIF는 이 고유 상품 목록의 각 항목에 대해 필터링된 상품명 목록에서 몇 번 나타나는지 계산합니다.
최종 결과 (예시):
| 상품명 | 불량 클레임 건수 |
| 사과 | 5 |
| 오렌지 | 3 |
| 포도 | 2 |
이처럼 FILTER, COUNTIF, VSTACK 함수를 유기적으로 조합하면, 여러 시트에 흩어져 있고 복잡한 조건을 가진 데이터도 손쉽게 통합하고 분석하여 원하는 인사이트를 얻을 수 있습니다.