close
프로필 배경
프로필 로고

마늘빵 베이커리

엑셀 배우기 · 2025. 4. 30. fullscreen 넓게보기

🚀 엑셀+ChatGPT = 초고수 되기! 2025년 업무 효율 200% 높이는 7가지 필수 프롬프트 1.수식

엑셀을 사용하다 보면 복잡한 함수나 데이터 분석에서 막히는 순간이 많으시죠? 이제 ChatGPT의 도움을 받아 엑셀 작업 시간을 획기적으로 줄이고 전문가처럼 작업할 수 있습니다. 해외에서 큰 인기를 얻고 있는 엑셀 프롬프트 7가지를  천천히 차례로 소개해 드릴게요!

 

엑셀 함수는 업무 자동화와 데이터 분석의 핵심이지만, 복잡한 수식을 만들려면 전문지식이 필요하죠. ChatGPT를 활용하면 원하는 기능의 수식을 쉽게 만들 수 있어요. 함수의 구조를 이해하고 응용할 수 있도록 상세한 설명까지 받을 수 있답니다!

여기서 ChatGPT 는 요즘 인공지능(AI)들을 대변하는 이름이에요. 

 

🚀 1. 수식 프롬프트 - 복잡한 엑셀 함수도 쉽게 만들기 😎

프롬프트 템플릿 : "[특정 작업]을 위한 엑셀 수식 만들어줘. 로직이랑 각 구성요소 쉽게 설명해줘서 나중에 응용할 수 있게 템플릿화 해줘."

 

사용 예시 1: 여러 시트 데이터 통합

프롬프트:

"여러 시트에 있는 특정 이름의 데이터를 자동으로 찾아서 합계를 내는 엑셀 수식 만들어줘. 로직이랑 각 구성요소 쉽게 설명해줘서 나중에 응용할 수 있게 템플릿화 해줘."

예제1: 엑셀에 필요한 수식을 ChatGPT 가 만들다

함수 결과:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!A:A"),"홍길동",INDIRECT("'"&Sheets&"'!B:B")))

함수 구문 설명:

예제1: 챗gpt가 생성한 수식에 필요한 시트2
예제1: 챗gpt가 생성한 수식에 필요한 시트3

시트이름을 이름정의(Sheets)하고 텍스트로 간접참조(INDIRECT)하여 조건부 합계(SUMIF+SUMPRODUCT)를 계산해요.

인수 설명:

  • Sheets: 시트 이름이 들어있는 범위를 이름 정의(예: D1:D3)
  • INDIRECT("'"&Sheets&"'!A:A"): 각 시트의 A열 전체를 동적으로 참조
  • "홍길동": 찾고자 하는 이름 (조건)
  • INDIRECT("'"&Sheets&"'!B:B"): 합계를 내고자 하는 각 시트의 B열 전체

주의사항: 시트 이름에 특수문자가 있거나 시트 구조가 일관되지 않으면 오류가 발생할 수 있어요. 시트 이름은 단순하게 유지하고, 모든 시트에서 동일한 구조를 유지하세요!

 

사용 예시 2: 조건부 데이터 추출

프롬프트:

"특정 부서(영업부)의 매출(실제)이 목표치(목표)를 초과한 직원들의 이름을 자동으로 콤마(, )로 구분해서 나열하는 엑셀 수식을 만들어줘. 로직이랑 각 구성요소 쉽게 설명해줘서 나중에 응용할 수 있게 템플릿화 해줘."

예제2: AI 가 만든 조건부 데이터 추출 엑셀 Textjoin 함수식

함수 결과:

=TEXTJOIN(", ", TRUE, IF((B2:B20="영업부")*(C2:C20>D2:D20), A2:A20, ""))

함수 구문 설명:

이름들은 TEXTJOIN 함수로 연결하고, 결합연산자로 콤마(, )를 이용해요. 조건1과 조건2는 * 연산자로 연결하여 AND 처리해요.

  • TEXTJOIN 함수: 지정된 구분 기호로 텍스트를 결합합니다.
  • IF 함수: 조건에 따라 다른 값을 반환합니다.

인수 설명:

  • ", ": 구분 기호 (쉼표와 공백)
  • TRUE: 빈 문자열 무시 옵션
  • (B2:B20="영업부"): 부서가 "영업부"인지 확인 (TRUE/FALSE 배열 반환)
  • (C2:C20>D2:D20): 실제 매출이 목표 매출보다 큰지 확인 (TRUE/FALSE 배열 반환)
  • A2:A20: 이름이 있는 범위
  • "": 조건이 만족되지 않을 때 반환할 빈 문자열

예제 데이터:

이름,부서,실제매출,목표매출
김영희,영업부,3500000,3000000
이철수,마케팅부,2800000,2500000
박지영,영업부,2900000,3000000
최동훈,영업부,3200000,3000000

주의사항: Excel 2016 이상 버전에서만 TEXTJOIN 함수를 사용할 수 있어요. 이전 버전에서는 VBA나 다른 함수 조합으로 대체해야 합니다.

 

사용 예시 3: 동적 날짜 기반 데이터 분석

프롬프트:

"오늘 날짜 기준으로 최근 30일 내 발생한 거래 금액의 평균을 구하는 엑셀 수식을 만들어줘. 로직이랑 각 구성요소 쉽게 설명해줘서 나중에 응용할 수 있게 템플릿화 해줘."

예제3: CHATGPT 로 만든 기간 평균 엑셀 수식

함수 결과:

=AVERAGEIFS(C2:C100, B2:B100, ">="&TODAY()-30, B2:B100, "<="&TODAY())

함수 구문 설명:

C열 평균을 구해요. 단, 거래일자가 오늘 이전 30일후 인 거래금액 평균을 구하는 것입니다.

인수 설명:

  • C2:C100: 거래 금액이 있는 범위
  • B2:B100: 거래 날짜가 있는 범위
  • ">="&TODAY()-30: 오늘로부터 30일 전 이후(포함)라는 조건
  • "<="&TODAY(): 오늘 이전(포함)이라는 조건

예제 데이터:

거래ID,거래일자,거래금액
T001,2025-04-01,120000
T002,2025-03-25,85000
T003,2025-03-10,230000
T004,2025-02-28,150000

주의사항: 날짜 형식이 제대로 지정되어 있어야 합니다. 텍스트로 인식된 날짜는 비교가 정확하게 이루어지지 않을 수 있어요.

 

사용 예시 4: 중첩 조건 검색

프롬프트:

"여러 조건(지역이 '서울'이고 제품 카테고리가 '전자제품'이며 판매량이 평균 이상인 경우)을 모두 만족하는 데이터 행의 개수를 세는 엑셀 수식을 만들어줘. 로직이랑 각 구성요소 쉽게 설명해줘서 나중에 응용할 수 있게 템플릿화 해줘."

예제4: 중첩조건비교 엑셀 수식을 AI 가 만들다

함수 결과:

=COUNTIFS(A2:A100, "서울", B2:B100, "전자제품", C2:C100, ">="&AVERAGE(C2:C100))

함수 구문 설명:

  • COUNTIFS 함수: 여러 조건을 만족하는 셀의 개수를 세는 함수입니다.
  • AVERAGE 함수: 지정된 범위의 평균값을 계산합니다.

인수 설명:

  • A2:A100: 지역 정보가 있는 범위
  • "서울": 지역 조건
  • B2:B100: 제품 카테고리 정보가 있는 범위
  • "전자제품": 제품 카테고리 조건
  • C2:C100: 판매량 정보가 있는 범위
  • ">="&AVERAGE(C2:C100): 판매량이 전체 평균 이상이라는 조건

예제 데이터:

지역,제품카테고리,판매량
서울,전자제품,120
부산,가구,85
서울,가구,95
서울,전자제품,150
인천,전자제품,110

주의사항: 데이터에 빈 셀이나 오류 값이 있으면 AVERAGE 함수 결과가 정확하지 않을 수 있어요. AVERAGEIF를 사용하여 유효한 값만 평균 계산에 포함시키는 것이 좋습니다.

 

사용 예시 5: 동적 검색 및 값 추출

프롬프트:

"사원 이름을 입력하면 해당 사원의 최근 성과평가 점수를 자동으로 찾아주는 엑셀 수식을 만들어줘. 같은 이름의 사원이 여러 명 있을 경우 사원번호로 구분할 수 있게 해줘. 로직이랑 각 구성요소 쉽게 설명해줘서 나중에 응용할 수 있게 템플릿화 해줘."

예제5: 복잡한 엑셀 수식도 CHATGPT 작성 가능

함수 결과:

=IFERROR(INDEX(D2:D100, MATCH(1, (A2:A100=G1)*(B2:B100=G2), 0)), "해당 사원 정보 없음")

함수 구문 설명:

MATCH 함수로 두조건(이름과 사원번호)을 만족하는 값이 있는 행을 얻고 INDEX 함수로 반환해요.

  • INDEX 함수: 지정된 위치(행)의 값을 반환합니다.
  • MATCH 함수: 배열에서 특정 값의 위치를 찾습니다.
  • IFERROR 함수: 오류가 발생하면 지정된 값을 반환합니다.

인수 설명:

  • D2:D100: 성과평가 점수가 있는 범위
  • A2:A100=G1: 이름이 G1 셀의 값과 일치하는지 비교 (TRUE/FALSE 배열 반환)
  • B2:B100=G2: 사원번호가 G2 셀의 값과 일치하는지 비교 (TRUE/FALSE 배열 반환)
  • MATCH(1, (A2:A100=G1)*(B2:B100=G2), 0): 두 조건이 모두( * 연산자가 AND의 기능 ) TRUE인 행의 위치를 찾음
  • "해당 사원 정보 없음": 일치하는 정보가 없을 때 표시할 메시지

예제 데이터:

이름,사원번호,평가일자,성과점수
김영희,E001,2025-01-15,85
이철수,E002,2025-01-15,92
김영희,E003,2025-01-15,78
박지영,E004,2025-01-20,88

주의사항: 이 함수는 배열 수식이므로, Excel 365나 최신 버전에서는 그냥 입력해도 되지만, 이전 버전에서는 Ctrl+Shift+Enter로 입력해야 합니다.

 

수식 사용 시 발생하는 주요 오류 정리

  1. #NAME? - 함수 이름이 잘못되었거나 Excel이 인식하지 못하는 이름을 사용했을 때
    • 해결책: 함수 이름의 철자를 확인하거나 해당 함수가 사용 중인 Excel 버전에서 지원되는지 확인하세요.
  2. #VALUE! - 잘못된 데이터 유형을 사용했을 때
    • 해결책: 함수에 맞는 데이터 유형(숫자, 텍스트, 날짜 등)을 사용하고 있는지 확인하세요.
  3. #REF! - 유효하지 않은 셀 참조를 사용했을 때
    • 해결책: 참조하는 셀이나 범위가 삭제되지 않았는지, 올바른 위치인지 확인하세요.
  4. #DIV/0! - 0으로 나누려고 할 때
    • 해결책: IFERROR 함수나 IF 함수를 사용하여 0으로 나누는 상황을 처리하세요.
  5. #N/A - 검색 함수에서 값을 찾을 수 없을 때
    • 해결책: IFERROR 또는 IFNA 함수로 오류를 처리하거나, 검색 조건을 재확인하세요.

엑셀 수식은 처음에는 복잡해 보일 수 있지만, ChatGPT의 도움으로 원하는 기능을 구현하는 수식을 쉽게 만들 수 있어요! 위 예제들을 참고해서 여러분의 업무에 필요한 수식을 만들어보세요. 그리고 함수의 각 구성 요소를 이해하면 나중에 비슷한 상황에서 쉽게 응용할 수 있답니다. 😊

 

다음 게시물에서는 ChatGPT(AI) 를 데이터 전처리에 활용하는 프롬프트를 살펴볼께요.

 

 

작성자: 마늘빵

엑셀 배우기 관련 글
더 보기

티스토리툴바