AI인사이트 로고AI인사이트
챕터 5

엑셀·데이터 분석 프롬프트 30선

수식 생성, 피벗 테이블 설계, 데이터 정제, 차트 추천, VBA 매크로 작성 프롬프트 30개를 제공합니다.

엑셀은 직장인이 가장 많이 쓰면서도 가장 많이 막히는 도구다. VLOOKUP 하나에 30분을 쓰고, 피벗 테이블 설정에서 헤매고, VBA 매크로는 엄두도 못 내는 게 현실이다. AI에게 정확히 설명하면, 수식·함수·매크로를 즉시 생성할 수 있다.

이 챕터의 프롬프트 30개는 복사 → 대괄호 수정 → AI에 붙여넣기 → 결과를 엑셀에 적용하는 흐름으로 설계했다. 엑셀 버전(2019, 365 등)과 데이터 구조를 정확히 알려줄수록 결과 품질이 올라간다.

A. 엑셀 수식·함수 생성 (8개)

#1. 조건부 합계 수식 (SUMIFS)

엑셀 수식을 만들어줘.

상황: [시트 이름] 시트에 다음 열이 있다.

  • A열: 날짜 (YYYY-MM-DD 형식)
  • B열: 부서명
  • C열: 항목명
  • D열: 금액

필요한 수식: [2026년 1월]에 [마케팅팀]이 사용한 금액 합계를 구하는 SUMIFS 수식. 날짜 조건은 해당 월의 첫째 날과 마지막 날 범위로 설정해줘. 엑셀 버전: [365/2019/2016]

기대 출력: =SUMIFS(D:D, B:B, "마케팅팀", A:A, ">="&DATE(2026,1,1), A:A, "<="&EOMONTH(DATE(2026,1,1),0)) 형태의 수식 + 각 인수 설명.

#2. VLOOKUP/XLOOKUP 자동 생성

두 시트 간 데이터를 매칭하는 수식을 만들어줘.

  • Sheet1 A열: 사원번호, B열: 이름, C열: 부서
  • Sheet2 A열: 사원번호, B열: 급여등급

Sheet1의 D열에 Sheet2의 급여등급을 가져오는 수식을 작성해줘. 매칭 실패 시 'N/A' 대신 '미등록'을 표시. 엑셀 버전이 [365]이면 XLOOKUP, [2019 이하]이면 VLOOKUP+IFERROR로 작성.

기대 출력: 버전별 수식 2종 + XLOOKUP이 없는 환경 대비 VLOOKUP 대안까지 제공.

#3. 중첩 IF 수식 (등급 분류)

점수를 기준으로 등급을 분류하는 엑셀 수식을 만들어줘.

분류 기준:

  • 90점 이상: S등급
  • 80~89점: A등급
  • 70~79점: B등급
  • 60~69점: C등급
  • 60점 미만: D등급

점수는 B2 셀에 있다. 방법 1: 중첩 IF 수식 방법 2: IFS 함수 (엑셀 365/2019) 두 가지 모두 작성해줘. 각 수식에 어떤 상황에서 쓰면 좋은지 한 줄 설명 추가.

#4. 날짜·시간 계산 수식

다음 엑셀 날짜 계산 수식을 만들어줘.

A열: 프로젝트 시작일 (YYYY-MM-DD) B열: 프로젝트 종료일 (YYYY-MM-DD)

C열: 영업일 기준 소요 기간 (주말 제외, 공휴일 목록은 Sheet2의 A열) D열: 종료일까지 남은 일수 (오늘 기준, 음수면 '지연 N일'로 표시) E열: 시작 분기 표시 (예: 2026-Q1)

각 열별로 수식 하나씩 작성해줘.

#5. 텍스트 함수 조합 (데이터 추출)

엑셀에서 텍스트를 가공하는 수식을 만들어줘.

A열 데이터 예시: "서울특별시 강남구 역삼동 123-45 6층"

B열: 시/도만 추출 (예: 서울특별시) C열: 구/군만 추출 (예: 강남구) D열: 전체 주소에서 층수만 추출 (예: 6층)

데이터 형식이 일정하지 않을 수 있으므로, 공백 기준 분리 + 오류 처리 포함. 엑셀 365의 TEXTSPLIT 함수를 쓸 수 있으면 추가로 보여줘.

#6. INDEX-MATCH 다중 조건 검색

두 가지 이상의 조건으로 값을 찾는 INDEX-MATCH 수식을 만들어줘.

데이터 구조:

  • A열: 지역 (서울, 부산, 대전)
  • B열: 제품 (A, B, C)
  • C열: 분기 (Q1, Q2, Q3, Q4)
  • D열: 매출액

찾으려는 값: [서울]의 [제품 B], [Q3] 매출액. 조건이 3개이므로 배열 수식 또는 XLOOKUP 중첩 방식으로 작성. Ctrl+Shift+Enter 필요 여부도 알려줘.

#7. 동적 범위 수식 (OFFSET/INDIRECT)

매달 행이 추가되는 데이터에서 자동으로 범위가 확장되는 수식을 만들어줘.

  • A열: 날짜, B열: 매출
  • 데이터는 A2부터 시작, 매달 30~31행이 추가됨

필요한 수식:

  1. B열의 마지막 값 자동 참조 (빈 셀 무시)
  2. B열 전체 합계 (빈 셀 아래 데이터도 포함)
  3. 최근 12개 행의 평균

OFFSET+COUNTA 방식과 테이블(Ctrl+T) 방식 두 가지 모두 보여줘.

#8. 배열 수식으로 고유값 목록 추출

A열에 중복 데이터가 있다. 중복을 제거한 고유값 목록을 수식으로 만들어줘.

예시 데이터: 서울, 부산, 서울, 대전, 부산, 광주, 서울

방법 1: UNIQUE 함수 (엑셀 365) 방법 2: UNIQUE가 없는 구버전용 대안 방법 3: 고유값 개수를 세는 수식

각 방법마다 수식 + 적용 가능한 엑셀 버전 표시.

B. 피벗 테이블·데이터 구조화 (5개)

#9. 피벗 테이블 설계 가이드

다음 데이터로 피벗 테이블을 만들려고 한다. 최적의 설정을 알려줘.

열 구조: [날짜, 부서, 담당자, 제품, 수량, 단가, 할인율, 매출액] 분석 목표: [부서별·월별 매출 추이를 비교]하고 싶다.

다음을 알려줘:

  1. 행/열/값/필터 영역에 각각 어떤 필드를 배치할지
  2. 값 필드의 집계 함수 (합계/평균/개수 등)
  3. 그룹화 설정 (날짜를 월/분기로 묶는 방법)
  4. 추천 슬라이서 설정 2개
  5. 피벗 테이블로 답할 수 있는 비즈니스 질문 3개

#10. 비정규화 데이터를 피벗 가능 구조로 변환

아래처럼 가로로 펼쳐진 데이터를 피벗 테이블에 적합한 세로(Long) 형태로 바꾸는 방법을 알려줘.

현재 구조 (Wide):

부서 1월 매출 2월 매출 3월 매출
영업1팀 5000 6000 5500

원하는 구조 (Long):

부서 월 매출
영업1팀 1월 5000

파워 쿼리로 변환하는 단계별 절차와, 수식만으로 변환하는 방법 두 가지를 알려줘.

#11. 크로스탭(교차 테이블) 수식 설계

피벗 테이블 없이 수식만으로 크로스탭 요약표를 만들어줘.

원본 데이터:

  • A열: 지역 (서울, 부산, 대전, 광주)
  • B열: 카테고리 (전자, 식품, 의류)
  • C열: 매출액

결과: 행=지역, 열=카테고리, 값=매출 합계인 교차 테이블. SUMIFS 기반 수식으로 작성하되, 새 지역이나 카테고리가 추가되어도 수식 수정 없이 작동하는 방식으로 만들어줘.

#12. 데이터 유효성 검사 설정 가이드

엑셀에서 데이터 입력 오류를 방지하는 유효성 검사를 설정하려고 한다.

요구사항:

  • B열(부서명): [영업, 마케팅, 개발, 인사, 재무] 중에서만 선택 (드롭다운)
  • C열(금액): 양수만 허용, 최대 [1억]
  • D열(이메일): @ 기호 포함 필수
  • E열(날짜): 오늘 이후 날짜만 입력 가능

각 열별로:

  1. 유효성 검사 설정 (메뉴 경로 + 수식)
  2. 오류 메시지 텍스트 추천
  3. 입력 안내 메시지 텍스트 추천

#13. 데이터 모델링 — 여러 시트 관계 설정

엑셀에서 3개 시트를 관계형으로 연결하려고 한다. 설계를 도와줘.

시트1 '주문': 주문번호, 고객코드, 제품코드, 수량, 날짜 시트2 '고객': 고객코드, 고객명, 등급, 지역 시트3 '제품': 제품코드, 제품명, 카테고리, 단가

다음을 알려줘:

  1. 각 시트를 테이블(Ctrl+T)로 변환할 때 이름 규칙
  2. 데이터 모델에서 관계 설정 방법 (1:N 관계)
  3. 이 모델로 만들 수 있는 피벗 테이블 예시 3개
  4. VLOOKUP 대신 데이터 모델을 쓰면 좋은 이유

C. 데이터 정제·전처리 (5개)

#14. 지저분한 데이터 정리 수식

다음과 같이 지저분한 데이터를 정제하는 엑셀 수식을 만들어줘.

문제 유형:

  1. 이름에 불필요한 공백 포함: " 홍 길 동 " → "홍길동"
  2. 전화번호 형식 불일치: "010.1234.5678", "01012345678", "010 1234 5678" → "010-1234-5678"
  3. 날짜 형식 불일치: "2026.03.15", "26/03/15", "20260315" → 엑셀 날짜 값
  4. 금액에 원(₩) 기호·쉼표 포함: "₩1,500,000" → 숫자 1500000

각 문제마다 수식 1개씩, 총 4개 수식.

#15. 중복 데이터 탐지·처리

엑셀에서 중복 데이터를 찾고 처리하는 방법을 수식으로 알려줘.

데이터: A열(이름), B열(이메일), C열(전화번호)

필요한 수식:

  1. A열에서 같은 이름이 2번 이상 나오면 '중복'이라고 표시하는 D열 수식
  2. A열+B열 조합이 중복인 경우만 표시하는 E열 수식
  3. 중복 건수를 세는 수식
  4. 첫 번째 등장만 남기고 나머지를 '삭제 대상'으로 표시하는 수식

조건부 서식으로 중복 행을 하이라이트하는 방법도 알려줘.

#16. 대량 데이터 분할·병합 수식

하나의 셀에 여러 값이 합쳐진 데이터를 분리해야 한다.

예시:

  • A2: "김철수/과장/마케팅팀" → B열: 김철수, C열: 과장, D열: 마케팅팀
  • A3: "서울, 강남, 역삼" → B열: 서울, C열: 강남, D열: 역삼

구분자(/ 또는 ,)를 기준으로 분리하는 수식을 만들어줘. 방법 1: MID+FIND 조합 (모든 버전) 방법 2: TEXTSPLIT (엑셀 365) 방법 3: 여러 셀의 값을 다시 하나로 합치는 TEXTJOIN 수식

#17. 이상값(Outlier) 탐지 수식

매출 데이터에서 이상값을 탐지하는 엑셀 수식을 만들어줘.

B열에 월별 매출 데이터가 B2:B25에 있다.

다음 3가지 방법으로 이상값 여부를 판단하는 수식을 각각 만들어줘:

  1. Z-score 방식: 평균에서 표준편차 2배 이상 벗어나면 '이상값'
  2. IQR 방식: Q1-1.5IQR 미만 또는 Q3+1.5IQR 초과이면 '이상값'
  3. 전월 대비 변동률이 [50]% 이상이면 '급변' 표시

결과를 C열에 '정상' 또는 '이상값'으로 표시하는 수식. QUARTILE, STDEV, AVERAGE 함수를 활용해줘.

#18. 텍스트를 AI에 전달하기 위한 데이터 포맷팅

엑셀 데이터를 AI(ChatGPT, Claude 등)에 효과적으로 전달하기 위한 포맷으로 변환하는 방법을 알려줘.

데이터: A열(이름), B열(부서), C열(직급), D열(매출), E열(평가등급) 행 수: 약 [50]행

다음 포맷별 변환 수식 또는 방법을 알려줘:

  1. 마크다운 테이블 형식으로 변환하는 수식
  2. CSV 한 줄 형식 (쉼표 구분)
  3. 번호 붙인 텍스트 목록 ("1. 홍길동, 마케팅팀, 과장, 5000만원")

팁: AI에 엑셀 데이터를 줄 때 가장 효과적인 형식은 무엇인지, 행 수가 많을 때 어떻게 나눠서 전달하면 좋은지도 알려줘.

D. 차트·시각화 추천 (4개)

#19. 데이터에 맞는 차트 유형 추천

아래 데이터를 시각화하려고 한다. 가장 적합한 차트 유형과 설정을 추천해줘.

데이터 설명: [12개월간 5개 부서의 월별 매출 추이]

다음을 알려줘:

  1. 추천 차트 유형 3가지 (우선순위 + 이유)
  2. 각 차트의 X축, Y축, 범례 설정
  3. 색상 팔레트 추천 (비즈니스 보고서용, 5색)
  4. 차트 제목과 부제목 예시
  5. 피해야 할 차트 유형과 그 이유

#20. 대시보드 레이아웃 설계

엑셀로 경영 대시보드를 만들려고 한다. 레이아웃을 설계해줘.

포함할 KPI: [매출액, 영업이익률, 고객 수, 이탈률, NPS, 재구매율] 기간: 최근 [12]개월 + 전년 동기 비교

다음을 알려줘:

  1. 대시보드 시트 레이아웃 (어떤 셀 범위에 어떤 요소 배치)
  2. 각 KPI별 추천 시각화 방식 (숫자 카드, 스파크라인, 게이지 등)
  3. 슬라이서/드롭다운으로 필터링할 항목
  4. 조건부 서식 설정 (목표 미달 시 빨간색 등)
  5. 데이터 연결 시트 구조

#21. 차트 해석 문구 자동 생성

엑셀 차트에 첨부할 분석 코멘트를 작성해줘.

차트 데이터:

  • X축: 2025년 1월~12월
  • Y축: 월별 매출 (단위: 억원)
  • 값: [1.2, 1.5, 1.3, 1.8, 2.1, 1.9, 2.5, 2.3, 2.8, 3.1, 2.9, 3.5]

다음 형태로 코멘트 3개를 작성해줘:

  1. 전체 트렌드 요약 (1문장)
  2. 주요 변곡점 분석 (상승/하락 전환 시점)
  3. 향후 전망 또는 주의 포인트 (1문장)

보고서에 바로 붙일 수 있는 격식체로 작성.

#22. 비교 분석 차트 구성 가이드

두 가지 이상의 항목을 비교하는 차트를 설계해줘.

비교 대상: [자사 vs 경쟁사 A vs 경쟁사 B] 비교 항목: [가격, 품질, 배송, A/S, 브랜드 인지도] (각 항목 0~100점)

다음을 알려줘:

  1. 방사형(레이더) 차트 설정 방법 (축, 계열, 레이블)
  2. 묶은 세로 막대형 차트 설정 방법
  3. 어떤 차트가 이 비교에 더 효과적인지와 이유
  4. 강점/약점을 시각적으로 강조하는 조건부 서식 팁

E. VBA 매크로 작성 (4개)

#23. 반복 작업 자동화 매크로

매주 수행하는 엑셀 반복 작업을 자동화하는 VBA 매크로를 작성해줘.

작업 내용:

  1. '원본데이터' 시트의 A1부터 데이터 범위를 자동 감지
  2. B열(부서) 기준으로 부서별 시트를 자동 생성 (이미 있으면 기존 데이터 삭제)
  3. 각 부서 시트에 해당 부서 데이터만 복사
  4. 각 시트에 AutoFit 적용
  5. 완료 시 처리 건수를 MsgBox로 표시

에러 처리 포함해줘. 코드에 한글 주석 필수.

#24. 보고서 자동 포맷팅 매크로

엑셀 데이터에 보고서 서식을 자동 적용하는 VBA 매크로를 작성해줘.

적용할 서식:

  1. 1행(헤더): 배경색 [남색], 글자색 흰색, 굵게, 가운데 정렬
  2. 데이터 영역: 테두리(얇은 실선), 행 높이 25
  3. 금액 열([D열]): 통화 형식(₩#,##0), 우측 정렬
  4. 합계 행(마지막 행 아래): SUM 수식 자동 삽입, 배경색 연회색
  5. 열 너비 자동 조절
  6. 시트 이름을 '[보고서_YYYYMMDD]' 형식으로 변경

한글 주석 포함, Sub 이름은 FormatReport로.

#25. 여러 파일 데이터 통합 매크로

특정 폴더의 엑셀 파일들을 하나로 합치는 VBA 매크로를 작성해줘.

조건:

  1. 사용자가 폴더를 선택하면 해당 폴더의 모든 .xlsx 파일을 처리
  2. 각 파일의 첫 번째 시트에서 데이터 복사
  3. 현재 통합 파일의 '통합' 시트에 세로로 이어 붙이기
  4. 헤더는 첫 번째 파일에서만 가져오고 나머지는 데이터만 복사
  5. 원본 파일명을 마지막 열에 기록 (어떤 파일에서 왔는지 추적용)
  6. 진행률 표시 (상태 표시줄에 '3/10 파일 처리 중...')

FileDialog와 Dir 함수 사용. 에러 처리 필수.

#26. 자동 이메일 발송 매크로

엑셀 데이터를 기반으로 Outlook 이메일을 자동 발송하는 VBA 매크로를 작성해줘.

데이터 구조:

  • A열: 수신자 이름
  • B열: 이메일 주소
  • C열: 제목에 들어갈 [프로젝트명]
  • D열: 마감일
  • E열: 발송 상태 (발송 후 '완료'로 업데이트)

이메일 본문 템플릿: "[수신자명]님 안녕하세요. [프로젝트명] 관련 마감일([마감일])이 다가오고 있어 안내드립니다. 확인 부탁드립니다."

Outlook Application 객체 사용. 발송 전 미리보기(Display) 옵션도 포함.

F. 구글 시트 함수 (4개)

#27. 구글 시트 전용 함수 활용

엑셀에는 없고 구글 시트에만 있는 함수를 활용하는 수식을 만들어줘.

  1. IMPORTRANGE: 다른 스프레드시트에서 데이터 가져오기
    • URL: [스프레드시트 URL], 범위: Sheet1!A1:D100
  2. QUERY: SQL 스타일로 데이터 필터링
    • "부서가 '마케팅'이고 매출이 1000 이상인 행만 추출, 매출 내림차순 정렬"
  3. ARRAYFORMULA: 수식 하나로 전체 열에 적용
    • B열의 값에 1.1을 곱한 결과를 C열 전체에 적용

각 함수의 정확한 문법, 실행 예시, 흔한 에러와 해결법을 알려줘.

#28. 구글 시트 QUERY 함수 마스터

구글 시트의 QUERY 함수로 다음 분석을 수행하는 수식을 만들어줘.

데이터 범위: A1:F500 열 구조: A(날짜), B(부서), C(담당자), D(제품), E(수량), F(매출액)

QUERY 수식 5개:

  1. 부서별 매출 합계 (GROUP BY)
  2. 월별·부서별 매출 합계 (PIVOT)
  3. 매출 상위 10건만 추출 (ORDER BY + LIMIT)
  4. 특정 기간(2026-01-01 ~ 2026-03-31) 데이터만 필터링
  5. 부서별 매출 평균이 500만원 이상인 부서만 표시

각 수식에 SQL 문법 설명을 한 줄씩 추가해줘.

#29. 구글 시트 자동화 (Apps Script 기초)

구글 시트에서 Apps Script로 자동화하는 코드를 작성해줘.

시나리오: 매주 월요일 아침, 현재 시트의 데이터를 요약해서 이메일로 발송

코드 요구사항:

  1. '매출데이터' 시트에서 이번 주 데이터 필터링
  2. 부서별 합계 계산
  3. HTML 테이블 형태로 이메일 본문 생성
  4. [수신자 이메일 주소]로 발송
  5. 시간 기반 트리거 설정 방법

전체 코드 + 트리거 설정 절차를 알려줘.

#30. 엑셀 vs 구글 시트 함수 대조표

자주 쓰는 엑셀 함수의 구글 시트 대응 함수를 정리해줘.

다음 20개 함수를 대조표로 만들어줘: VLOOKUP, XLOOKUP, SUMIFS, COUNTIFS, INDEX, MATCH, IF, IFS, IFERROR, TEXT, LEFT, RIGHT, MID, FIND, SUBSTITUTE, UNIQUE, FILTER, SORT, INDIRECT, OFFSET

표 형식: | 엑셀 함수 | 구글 시트 대응 | 차이점 | 주의사항 |

구글 시트에서만 되는 기능(QUERY, IMPORTRANGE, GOOGLEFINANCE 등)도 별도 섹션으로 추가.

실전 팁: AI에 엑셀 질문을 잘하는 법

엑셀 관련 프롬프트에서 결과 품질을 높이려면 다음 정보를 반드시 포함하자.

필수 정보 예시 빠뜨리면 생기는 문제
엑셀 버전 365, 2019, 2016 XLOOKUP 등 신규 함수 사용 여부 판단 불가
열 구조 A열: 이름, B열: 부서 셀 참조가 엉뚱한 열을 가리킴
데이터 시작 행 2행부터 (1행 헤더) 수식 범위 오류
예시 데이터 2~3행 "홍길동, 마케팅, 5000" AI가 데이터 형식을 추측해야 함
원하는 결과 형태 "합계", "목록", "TRUE/FALSE" 출력 형식이 기대와 다름

수식을 받은 후 체크리스트

  1. 셀 참조 확인: AI가 A2라고 했는데 실제 데이터가 A3부터면 수정
  2. 절대/상대 참조: 수식을 아래로 복사할 때 $A$1(절대) vs A1(상대)이 맞는지 확인
  3. 테스트: 결과를 알고 있는 행에서 먼저 수식 확인
  4. 에러 처리: #N/A, #VALUE!, #REF! 등이 나오면 IFERROR로 감싸기

이 30개 프롬프트를 업무 상황에 맞게 활용하면, 엑셀 수식 하나에 30분씩 쓰는 시간을 1분으로 줄일 수 있다. 다음 챕터에서는 마케팅·광고 업무에 최적화된 프롬프트를 다룬다.