엑셀에서 여러 시트에 나누어 저장된 데이터를 찾으려면 시트를 하나씩 열어 확인해야 하는 경우가 많습니다. 특히 거래처 목록, 상품 재고, 월별 매출, 고객 정보처럼 자료가 많아질수록 원하는 데이터를 찾는 데 시간이 오래 걸립니다.
이럴 때 챗GPT를 활용하면 여러 시트에서 원하는 데이터를 검색하는 방법을 쉽게 익히고, 필요한 엑셀 수식까지 만들 수 있습니다.
이번 글에서는 엑셀의 기본 검색 기능부터 여러 시트의 데이터를 한 번에 검색하는 방법, 챗GPT로 검색 수식을 만드는 방법까지 실제 예제로 알아보겠습니다.
1. 엑셀 여러 시트에서 데이터를 찾는 방법
엑셀 파일에는 여러 개의 시트를 만들 수 있습니다. 예를 들어 상품 판매 내역을 월별로 관리한다면 다음과 같이 구성할 수 있습니다.
| 시트 이름 | 저장된 데이터 |
|---|---|
| 1월 | 1월 상품 판매 내역 |
| 2월 | 2월 상품 판매 내역 |
| 3월 | 3월 상품 판매 내역 |
| 4월 | 4월 상품 판매 내역 |
이 상태에서 특정 상품의 판매 내역을 찾으려면 각 시트에서 상품명을 검색해야 합니다.
하지만 엑셀의 통합 문서 검색 기능을 사용하면 여러 시트에 저장된 데이터를 한 번에 찾을 수 있습니다.
또한 FILTER, VSTACK 등의 함수를 활용하면 여러 시트의 데이터를 하나로 모아 원하는 조건에 맞는 결과만 표시할 수도 있습니다.
2. Ctrl+F로 여러 시트에서 한 번에 검색하기
가장 간단한 방법은 엑셀의 찾기 기능을 사용하는 것입니다.
- 검색할 엑셀 파일을 엽니다.
- 키보드에서 Ctrl+F를 누릅니다.
- 검색할 상품명이나 고객명을 입력합니다.
- 옵션을 클릭합니다.
- 검색 범위를 ‘시트’에서 ‘통합 문서’로 변경합니다.
- ‘모두 찾기’를 클릭합니다.
예를 들어 ‘무선마우스’를 검색하면 해당 단어가 포함된 셀을 통합 문서 전체에서 찾아 목록으로 표시합니다.
검색 결과에는 시트 이름과 셀 위치 등이 나타나므로 원하는 항목을 선택하면 해당 위치로 이동할 수 있습니다.
이 방법은 별도의 수식을 만들지 않아도 사용할 수 있어 엑셀 초보자에게 편리합니다.
다만 검색된 데이터를 별도의 표로 자동 정리하려면 함수나 파워 쿼리를 활용하는 것이 좋습니다.
3. 챗GPT로 여러 시트 검색 수식 만들기
챗GPT를 사용하면 복잡한 수식을 직접 작성하지 않아도 됩니다.
먼저 엑셀 파일의 시트 구성과 데이터 위치를 확인합니다.
예를 들어 다음과 같은 판매 자료가 있다고 가정하겠습니다.
| 시트 | 상품명 | 판매수량 | 판매금액 |
|---|---|---|---|
| 1월 | 무선마우스 | 15 | 300,000원 |
| 1월 | 키보드 | 10 | 250,000원 |
| 2월 | 무선마우스 | 20 | 400,000원 |
| 2월 | 모니터 | 5 | 750,000원 |
| 3월 | 무선마우스 | 12 | 240,000원 |
각 시트에는 A열에 상품명, B열에 판매수량, C열에 판매금액이 저장되어 있다고 가정합니다.
챗GPT에 다음과 같이 질문합니다.
“엑셀에 1월, 2월, 3월 시트가 있습니다. 각 시트의 A열에는 상품명, B열에는 판매수량, C열에는 판매금액이 있습니다. 모든 시트에서 무선마우스의 판매수량을 합산하는 수식을 만들어 주세요.”
챗GPT는 다음과 같은 SUMIF 수식을 제안할 수 있습니다.
=SUMIF('1월'!A:A,"무선마우스",'1월'!B:B)+SUMIF('2월'!A:A,"무선마우스",'2월'!B:B)+SUMIF('3월'!A:A,"무선마우스",'3월'!B:B)이 수식은 각 시트에서 무선마우스를 찾아 판매수량을 합산합니다.
계산 결과는 다음과 같습니다.
15 + 20 + 12 = 47개
따라서 무선마우스의 전체 판매수량은 47개입니다.
4. 검색어를 입력하면 자동으로 결과가 바뀌게 만들기
매번 수식에 상품명을 직접 입력하면 불편합니다.
검색할 상품명을 별도의 셀에 입력하도록 만들면 훨씬 편리하게 사용할 수 있습니다.
새로운 시트를 만들고 이름을 ‘검색’으로 지정합니다.
| 셀 위치 | 입력 내용 |
|---|---|
| A2 | 검색할 상품명 |
| B2 | 무선마우스 |
| A4 | 전체 판매수량 |
| B4 | 계산 수식 |
B4 셀에 다음 수식을 입력합니다.
=SUMIF('1월'!A:A,B2,'1월'!B:B)+SUMIF('2월'!A:A,B2,'2월'!B:B)+SUMIF('3월'!A:A,B2,'3월'!B:B)이제 B2 셀의 상품명을 변경하면 해당 상품의 전체 판매수량이 자동으로 계산됩니다.
예를 들어 B2에 ‘키보드’를 입력하면 키보드 판매수량이 계산됩니다.
상품이 많아도 수식을 매번 수정할 필요가 없습니다.
5. VSTACK과 FILTER 함수로 여러 시트의 검색 결과 모으기
Microsoft 365 또는 해당 함수를 지원하는 엑셀 버전에서는 VSTACK과 FILTER를 함께 사용할 수 있습니다.
VSTACK은 여러 범위의 데이터를 세로로 연결하는 함수입니다.
FILTER는 조건에 맞는 데이터만 추출하는 함수입니다.
예를 들어 1월, 2월, 3월 시트의 A2 범위에 상품 판매 내역이 있다고 가정합니다.
검색 시트의 B2 셀에 상품명을 입력하고, A6 셀에 다음 수식을 작성합니다.
=LET(data,VSTACK('1월'!A2:C100,'2월'!A2:C100,'3월'!A2:C100),FILTER(data,(INDEX(data,,1)=B2)*(INDEX(data,,1)<>""),"검색 결과 없음"))이 수식은 다음 순서로 작동합니다.
- VSTACK으로 3개 시트의 데이터를 하나로 연결합니다.
- INDEX로 연결된 데이터의 첫 번째 열인 상품명을 확인합니다.
- FILTER로 B2 셀의 검색어와 일치하는 행만 추출합니다.
- 검색 결과가 없으면 ‘검색 결과 없음’을 표시합니다.
B2에 ‘무선마우스’를 입력하면 다음과 같은 결과가 나타납니다.
| 상품명 | 판매수량 | 판매금액 |
|---|---|---|
| 무선마우스 | 15 | 300,000원 |
| 무선마우스 | 20 | 400,000원 |
| 무선마우스 | 12 | 240,000원 |
이 방법을 사용하면 여러 시트를 이동하지 않아도 원하는 상품의 판매 내역을 한곳에서 확인할 수 있습니다.
단, 위 수식은 검색 범위를 100행까지 지정한 예제입니다. 데이터가 100행을 넘으면 범위를 늘려야 하며, 결과가 표시될 셀 아래에는 충분한 빈 공간이 필요합니다.
6. 여러 시트에서 특정 고객의 거래 내역 찾기
이번에는 고객 관리 업무에 적용해 보겠습니다.
거래처별 매출 자료가 여러 시트에 저장되어 있다고 가정합니다.
| 시트 | 고객명 | 거래금액 |
|---|---|---|
| 서울 | 김민수 | 150,000원 |
| 서울 | 이영희 | 220,000원 |
| 부산 | 김민수 | 180,000원 |
| 대구 | 김민수 | 120,000원 |
모든 시트에서 김민수 고객의 거래금액을 합산하려면 다음 수식을 사용할 수 있습니다.
=SUMIF(서울!A:A,"김민수",서울!B:B)+SUMIF(부산!A:A,"김민수",부산!B:B)+SUMIF(대구!A:A,"김민수",대구!B:B)계산 결과는 다음과 같습니다.
150,000 + 180,000 + 120,000 = 450,000원
김민수 고객의 전체 거래금액은 450,000원입니다.
이처럼 여러 시트에 나누어진 거래 내역도 하나의 수식으로 계산할 수 있습니다.
7. 시트가 많을 때는 파워 쿼리 활용하기
시트가 10개, 20개 이상으로 늘어나면 수식을 계속 추가하는 방식은 관리하기 어렵습니다.
이럴 때는 엑셀의 파워 쿼리(Power Query)를 활용하는 것이 좋습니다.
파워 쿼리는 여러 표를 하나로 결합하고 필요한 데이터를 정리하는 기능입니다.
기본 작업 순서는 다음과 같습니다.
- 각 시트의 데이터 범위를 선택하고 Ctrl+T를 눌러 표로 만듭니다.
- 각 표에 서로 다른 이름을 지정합니다.
- 데이터 메뉴에서 ‘테이블/범위에서’를 선택해 각 표를 파워 쿼리로 불러옵니다.
- 파워 쿼리 편집기에서 ‘쿼리 추가’ 기능으로 여러 표를 결합합니다.
- 필요한 열과 검색 조건을 설정합니다.
- ‘닫기 및 로드’를 선택해 결과를 엑셀 시트에 표시합니다.
이후 원본 데이터가 변경되면 새로 고침을 통해 결합된 결과를 업데이트할 수 있습니다.
단, 새로운 시트나 표가 추가되면 기존 쿼리에 자동으로 포함되지 않을 수 있으므로 쿼리 설정을 확인해야 합니다.
8. 챗GPT에 엑셀 파일을 업로드해서 데이터 찾기
엑셀 수식이 어렵다면 챗GPT에 직접 파일을 업로드해 분석을 요청할 수도 있습니다.
챗GPT에서 엑셀 파일을 첨부한 다음 다음과 같이 입력합니다.
“첨부한 엑셀 파일의 모든 시트를 확인하고 무선마우스가 포함된 행을 찾아 주세요. 시트 이름, 상품명, 판매수량, 판매금액을 표로 정리하고 전체 판매수량과 판매금액도 합산해 주세요.”
챗GPT가 파일을 정상적으로 읽을 수 있다면 각 시트의 데이터를 확인하고 요청한 조건에 맞는 결과를 정리할 수 있습니다.
또한 다음과 같은 작업도 요청할 수 있습니다.
- 여러 시트에 중복된 고객명 찾기
- 특정 상품의 월별 판매 내역 비교하기
- 거래처별 미수금 합산하기
- 여러 시트에서 누락된 데이터 찾기
- 검색 결과를 새로운 엑셀 파일로 정리하기
단, 개인정보나 회사의 기밀 자료가 포함된 파일은 업로드하기 전에 민감한 정보를 제거하거나 조직의 데이터 처리 규정을 확인하는 것이 좋습니다.
9. 검색이 제대로 되지 않을 때 확인할 사항
여러 시트에서 데이터를 검색할 때 결과가 나오지 않거나 계산이 잘못되는 경우가 있습니다.
대표적인 원인과 해결 방법은 다음과 같습니다.
| 문제 | 원인 및 해결 방법 |
|---|---|
| 검색 결과가 없음 | 검색어의 띄어쓰기와 오탈자 확인 |
| 일부 데이터가 누락됨 | 검색 범위가 전체 데이터를 포함하는지 확인 |
| 수식에 #REF! 표시 | 시트 이름 변경 또는 삭제 여부 확인 |
| #NAME? 오류 | 엑셀 버전의 함수 지원 여부 확인 |
| #SPILL! 오류 | 검색 결과가 표시될 셀 범위 비우기 |
| 합계가 실제와 다름 | 숫자가 문자로 저장되었는지 확인 |
특히 상품명 앞뒤에 불필요한 공백이 있으면 같은 상품이라도 서로 다른 데이터로 인식될 수 있습니다.
이럴 때는 TRIM 함수를 활용해 불필요한 공백을 정리할 수 있습니다.
=TRIM(A2)이 수식은 A2 셀에 있는 텍스트의 앞뒤 공백을 제거하고 단어 사이의 연속된 일반 공백을 하나로 정리합니다.
다만 줄바꿈이나 특수 공백은 별도로 처리해야 할 수 있습니다.
10. 여러 시트 검색에 적합한 방법 비교
| 검색 방법 | 주요 기능 | 추천 상황 |
|---|---|---|
| Ctrl+F | 전체 시트에서 특정 값 찾기 | 간단한 검색 |
| SUMIF | 조건에 맞는 숫자 합산 | 판매량·매출 합계 |
| FILTER + VSTACK | 여러 시트에서 조건에 맞는 행 추출 | 검색 결과 목록 만들기 |
| 파워 쿼리 | 여러 표 결합 및 정리 | 대량 데이터 관리 |
| 챗GPT 파일 분석 | 자연어로 검색·분석 요청 | 수식 작성이 어려운 경우 |
검색할 데이터가 적다면 Ctrl+F만으로도 충분합니다.
여러 시트의 판매수량이나 거래금액을 계산하려면 SUMIF가 편리합니다.
검색된 행을 별도의 목록으로 표시하려면 FILTER와 VSTACK을 활용할 수 있습니다.
반면 데이터가 많고 정기적으로 업데이트해야 한다면 파워 쿼리를 활용하는 것이 효율적입니다.
엑셀 함수의 사용 방법과 지원 버전은 마이크로소프트 공식 자료에서 확인할 수 있습니다.
11. 실제 업무에 활용하는 방법
여러 시트 검색 기능은 다양한 업무에 활용할 수 있습니다.
쇼핑몰 운영자는 월별 판매 시트에서 특정 상품의 판매수량과 매출을 확인할 수 있습니다.
거래처 관리 담당자는 지역별 시트에서 동일 고객의 거래 내역을 찾을 수 있습니다.
재고 담당자는 여러 창고의 재고 시트를 비교해 특정 상품의 전체 재고수량을 계산할 수 있습니다.
또한 회계 담당자는 월별 비용 자료에서 특정 거래처의 지급 내역을 검색할 수 있습니다.
이러한 작업을 챗GPT와 엑셀 함수로 자동화하면 반복적인 검색 시간을 줄이고 자료를 보다 체계적으로 관리할 수 있습니다.
12. 마무리
엑셀에서 여러 시트에 나누어진 데이터를 찾는 작업은 생각보다 간단하게 해결할 수 있습니다.
단순히 특정 단어를 찾는 경우에는 Ctrl+F를 사용하고, 여러 시트의 숫자를 합산하려면 SUMIF를 활용하면 됩니다.
검색 조건에 맞는 데이터를 표로 추출하려면 FILTER와 VSTACK을 사용할 수 있으며, 대량의 데이터를 관리한다면 파워 쿼리가 적합합니다.
특히 챗GPT에 시트 이름, 데이터 위치, 검색 조건을 구체적으로 알려주면 실제 업무에 사용할 수 있는 수식을 만드는 데 도움을 받을 수 있습니다.
처음에는 3개 정도의 시트로 검색 수식을 테스트한 뒤 실제 업무 파일에 적용하는 것이 좋습니다. 검색 결과와 원본 데이터를 한 번 비교해 정확성을 확인하면 더욱 안정적으로 활용할 수 있습니다.
답글 남기기