엑셀 여러 시트 중복 항목 추출 및 개수 구하기 (UNIQUE, VSTACK 활용)

 

 

데이터 분석의 정석! 여러 엑셀 시트에 흩어진 데이터, 이제 걱정 마세요! 중복 항목 추출부터 개수 계산까지, UNIQUE와 VSTACK 함수를 활용한 스마트한 해결 전략을 제시합니다. 실무 예제와 함께 숨겨진 데이터 분석 팁까지, 지금 바로 만나보세요! (중복 제거, 데이터 통합, 개수 계산, 엑셀 함수, 실무 팁)

데이터 분석의 시작: 목표 설정 및 구조 파악

데이터 분석, 뭔가 거창해 보이지만?! 사실 시작은 아주 간단합니다. 분석 목표와 데이터 구조 파악, 이 두 가지만 기억하면 돼요! 마치 건물의 설계도처럼, 데이터 구조를 명확히 이해해야 어떤 함수를 써야 할지, 어떤 전략을 세워야 할지 감이 잡히죠. 예를 들어 제품별 판매량 분석이 목표라면, 제품명, 판매량, 지역 등 어떤 정보가 어떤 시트에 있는지 파악하는 것이 첫걸음입니다.

데이터 분석, 어떻게 시작해야 할까요?

분석 목표 설정 → 데이터 구조 파악 → 함수 및 전략 선택. 이 세 단계만 기억하세요! 마치 퍼즐 조각 맞추듯, 데이터 분석의 큰 그림을 그려나가는 겁니다.

VSTACK & HSTACK, 그리고 CHOOSEROWS: 데이터 통합의 마법

여러 시트에 흩어진 데이터, 하나로 모아야 분석이 쉽겠죠? VSTACK 함수는 여러 시트의 데이터를 마치 블록 쌓듯 수직으로 결합해 줍니다. "Sheet1", "Sheet2", "Sheet3"의 A1:B10 범위 데이터를 통합하고 싶다면? =VSTACK(Sheet1:Sheet3!A1:B10) 이렇게 간단하게 해결! HSTACK은 데이터를 수평으로 결합하는 함수인데, 시트별로 제품명, 판매량, 지역 데이터가 A, B, C열에 있다면 =HSTACK(Sheet1:Sheet3!A:C)를 사용하면 됩니다. 특정 시트나 행만 골라서 결합하고 싶다면 CHOOSEROWS 함수를 활용해 보세요. =VSTACK(CHOOSEROWS(Sheet1!A1:B10,1,3,5))는 Sheet1의 1, 3, 5행만 쏙쏙 뽑아서 결합해 줍니다.

데이터 통합 함수, 어떤 상황에 어떤 함수를 써야 할까요?

• VSTACK: 여러 시트의 데이터를 수직으로 결합할 때 사용합니다.
• HSTACK: 여러 시트의 데이터를 수평으로 결합할 때 사용합니다.
• CHOOSEROWS: 특정 시트 또는 특정 행만 선택적으로 결합할 때 사용합니다.

UNIQUE 함수: 중복 데이터는 가라!

드디어 중복 제거 시간! VSTACK이나 HSTACK으로 데이터를 합쳤다면, 이제 UNIQUE 함수로 중복 항목을 깔끔하게 제거해 봅시다. =UNIQUE(VSTACK(Sheet1:Sheet3!A1:A10)) 이 수식 하나면 세 개 시트의 A1:A10 범위에 있는 제품명 중 중복된 것들은 싹 사라지고 유일한 제품 목록만 남게 됩니다. 특정 열을 기준으로 중복을 제거하고 싶다면? =UNIQUE(VSTACK(Sheet1:Sheet3!A1:C10),,2)처럼 by_col 인수에 기준 열 번호를 넣어주면 됩니다. 참 쉽죠?

UNIQUE 함수, 더욱 효율적으로 사용하는 팁!

• by_col 인수: 특정 열을 기준으로 중복을 제거할 때 사용합니다.
• exactly_once 인수: 한 번만 나타나는 값만 추출할 때 사용합니다. (TRUE로 설정)

SUMIFS 함수: 조건에 맞는 값만 쏙쏙 더하기

이제 중복 제거된 제품 목록을 기반으로 각 제품의 총 판매량을 계산해 볼까요? SUMIFS 함수가 바로 그 주인공입니다! =SUMIFS(VSTACK(Sheet1:Sheet3!B1:B10), VSTACK(Sheet1:Sheet3!A1:A10),UNIQUE(VSTACK(Sheet1:Sheet3!A1:A10))) 이 수식은 통합된 판매량 데이터에서 각 제품명에 해당하는 판매량만 쏙쏙 골라 더해줍니다. 지역, 기간 등 추가 조건을 넣어 더욱 세분화된 분석도 가능하다는 사실!

SUMIFS 함수, 조건을 추가해서 더욱 강력하게 활용하기!

SUMIFS 함수는 여러 조건을 추가할 수 있어, 복잡한 데이터 분석에도 유용합니다. 예를 들어 특정 지역의 특정 기간 판매량만 계산하고 싶다면, 해당 조건을 추가하여 수식을 작성하면 됩니다.

동적 배열 함수와의 환상적인 콜라보: FILTER, SORT, SORTBY

엑셀의 동적 배열 함수(FILTER, SORT, SORTBY)는 마치 데이터 분석의 어벤져스 같아요! UNIQUE, VSTACK, SUMIFS와 함께 사용하면 그 시너지가 어마어마하죠. 예를 들어 특정 지역의 특정 제품 판매량만 추출해서 정렬하고 싶다면? FILTER 함수로 원하는 데이터만 걸러내고, SORT 함수로 정렬하면 끝! 이처럼 동적 배열 함수를 활용하면 복잡한 데이터 처리도 순식간에 자동화할 수 있습니다. 실시간 데이터 분석? 이제 문제없어요!

동적 배열 함수 활용 예시

=SORT(FILTER(VSTACK(Sheet1:Sheet3!A1:C10),VSTACK(Sheet1:Sheet3!C1:C10)="서울"),2,-1) 이 수식은 서울 지역의 판매 데이터만 추출하여 판매량을 기준으로 내림차순 정렬합니다.

데이터 분석, 이것만 알면 당신도 전문가! 추가 팁 대방출

• 이름 정의: 자주 쓰는 범위나 수식에 이름을 붙여주면 수식이 훨씬 보기 쉬워집니다. "판매량" 대신 "SalesData"처럼 이름을 정의하면 수식이 =SUM(SalesData)처럼 간결해지죠!
• 오류 처리: ISERROR, IFERROR 함수는 예상치 못한 오류를 막아주는 안전장치와 같습니다. #N/A! 같은 오류 메시지 대신 0이나 "데이터 없음" 같은 메시지를 표시하도록 설정할 수 있죠.
• 조건부 서식: 조건부 서식은 데이터를 시각적으로 표현하는 데 유용합니다. 예를 들어 판매량이 100 이상인 셀을 녹색으로 표시하면 데이터의 특징을 한눈에 파악할 수 있죠!
• 피벗 테이블: 대량의 데이터를 요약하고 분석하는 데는 피벗 테이블만 한 게 없죠. 다양한 각도에서 데이터를 분석하고 시각화할 수 있어 데이터 분석의 필수 도구라고 할 수 있습니다.
• Power Query: 엑셀의 강력한 데이터 가져오기 및 변환 도구인 Power Query를 활용하면, 다양한 소스에서 데이터를 가져와 정리하고 변환하여 분석에 필요한 형태로 만들 수 있습니다. 특히 대용량 데이터 처리에 매우 효율적입니다.
• LET 함수: 복잡한 수식을 간소화하고 가독성을 높이는 데 유용한 LET 함수를 사용하면, 중복 계산을 피하고 수식의 성능을 향상시킬 수 있습니다. 수식 내에서 변수를 정의하고 재사용할 수 있으므로, 복잡한 논리를 구현하는 데 매우 효과적입니다.
• LAMBDA 함수: LAMBDA 함수를 사용하면 사용자 정의 함수를 만들어 재사용할 수 있습니다. 특정 계산 로직을 여러 번 사용해야 하는 경우, LAMBDA 함수를 사용하여 사용자 정의 함수를 만들면 수식을 간소화하고 유지 관리를 용이하게 할 수 있습니다.

자, 이제 여러분은 엑셀 데이터 분석 전문가! UNIQUE, VSTACK, SUMIFS, 동적 배열 함수, 그리고 추가 팁까지 모두 활용해서 데이터 분석의 세계를 정복해 보세요! 복잡한 데이터 속에서 숨겨진 인사이트를 발견하는 짜릿한 경험, 지금 바로 시작해 보세요!