챗GPT로 엑셀 수식과 VBA 반복 업무를 어떻게 자동화할까?

TL;DR

엑셀의 핵심 기능인 VLOOKUP, COUNTIF·SUMIF, 피벗 테이블을 익힌 뒤, 모르는 수식이나 반복 작업은 데이터 예시와 원하는 결과를 챗GPT에 구체적으로 전달하면 된다. 챗GPT가 만든 수식과 VBA 코드는 엑셀에서 실행하고 결과를 원본 데이터와 대조해야 하며, 회사 보안 정보는 입력하지 않는 것이 중요하다.

Transcript

컨트롤 C 그다음 GPT한테 가서요. 컨트롤 V로 붙여 넣을게요. 발전자분해서 발전할 도는 수식을 따서 얘를 그대로 복사해서 엔터를 쳐 볼게요. 그러면 202401부터 맨 끝으로 이동을 해 보면요. 완벽하게 구해줬네요. [음악] 안녕하세요. 공대나온 여자 사람들. 동료사들의 긴가장입니다. 오늘 영상은 아주 중요한 영상입니다. 제가 대기업 엘사를 퇴사하고 1인 기업을 시작으로 벌써 직원 네명을 둔 5인 기업 사장님이 됐다고 했는데요. 지난주에 아주 재밌는 일이 하나 있었습니다. 저희 마케터한테 데이터를 주면서 이것 좀 분석해 달라고 했어요. 근데 마케터가 데이터를 한참을 뜯어 보더니 이런 보고 결과를 갖고 왔어요. 평균이... Read More

Key Insights

• 엑셀 기본 기능을 먼저 익혀야 자주 발생하는 문제를 매번 챗GPT에 질문하지 않고 직접 처리할 수 있다.
• VLOOKUP은 프로젝트 ID 같은 공통 키를 기준으로 서로 다른 표에서 담당 부서 등의 값을 찾아 연결한다.
• COUNTIF와 SUMIF는 조건에 맞는 데이터의 개수나 합계를 계산해 필요한 값만 골라 집계하는 데 쓰인다.
• 피벗 테이블은 많은 원본 데이터를 부서별 예산, 집행액, 잔여액처럼 몇 번의 필드 배치로 요약한다.
• 챗GPT에는 셀 위치, 참조할 열, 원하는 출력 형식을 함께 알려줘야 바로 적용할 수 있는 수식을 받기 쉽다.
• 발주일자를 연월 형식으로 바꾸는 예시에서는 생성된 수식을 첫 셀에 붙이고 아래 행까지 채워 결과를 완성했다.
• 색칠, 셀 병합 해제, 여러 시트 통합처럼 수식으로 하기 어려운 작업은 VBA 매크로로 자동화할 수 있다.
• AI가 만든 계산과 코드는 그대로 신뢰하지 말고 원본 값과 비교해 오류, 머리글 누락, 범위 문제를 확인해야 한다.
• 🎬 이 채널의 다른 영상:
  • 20인 미만 팀의 업무를 사람 대신 시스템이 기억하게 하려면?
  • 신입사원이 예산과 참석자 성향에 맞춰 회식 장소를 정하는 방법은?
  • 작은 회사는 어떻게 노션으로 표준 업무 시스템을 구축할까?
• 📺 이 채널: 공여사들

Questions & Answers

Q: AI 시대에도 엑셀 기본 기능을 배워야 하는 이유는 무엇인가?

자주 쓰는 엑셀 기능은 직접 처리할 수 있어야 매번 챗GPT에 질문하는 낭비를 줄일 수 있다. 영상은 서로 다른 표를 연결하는 VLOOKUP, 조건별 개수와 합계를 구하는 COUNTIF·SUMIF, 많은 데이터를 요약하는 피벗 테이블을 직장인의 핵심 기본기로 제시하며, 모르는 문제에만 챗GPT를 활용하라고 설명한다.

Q: 서로 다른 엑셀 표에서 담당 부서를 어떻게 가져오는가?

두 표에 공통으로 존재하는 프로젝트 ID를 기준으로 VLOOKUP을 사용하면 담당 부서를 가져올 수 있다. 조회할 프로젝트 ID, 담당 부서 표의 범위, 오른쪽에서 반환할 열 번호를 지정하고 정확히 일치하는 값을 찾도록 FALSE 또는 0을 입력한 뒤, 수식을 나머지 행까지 채우면 된다.

Q: 부서별 예산과 집행 금액을 빠르게 집계하려면 어떻게 해야 하는가?

원본 데이터 전체를 선택해 피벗 테이블을 만든 뒤 담당 부서를 행 영역에, 예산 총금액과 집행 금액 및 예산 잔여 금액을 값 영역에 배치하면 된다. 그러면 각 부서의 고유값을 기준으로 금액 합계가 나뉘어 표시되므로, SUMIF 수식을 항목마다 작성하는 작업을 줄일 수 있다.

Q: 닫힌 피벗 테이블 필드 목록은 어떻게 다시 여는가?

피벗 테이블 안의 아무 셀이나 클릭한 다음 상단에 나타나는 피벗 테이블 분석 탭에서 필드 목록 버튼을 누르면 된다. 영상에서는 사용자가 필드 창을 닫은 상황을 챗GPT에 질문했고, 안내받은 순서대로 분석 탭 오른쪽의 필드 목록을 선택해 창이 다시 표시되는 것을 확인했다.

Q: 챗GPT에 엑셀 수식을 요청할 때 어떤 정보를 제공해야 하는가?

데이터 예시와 셀 위치, 참조할 열, 만들고 싶은 결과를 한 문장에 구체적으로 포함해야 한다. 영상에서는 선택한 데이터를 붙여 넣고 F열에 발주 연월을 구하려 하며 발주일자 열을 참고한다는 조건을 전달했다. 받은 수식은 대상 셀에 붙인 뒤 아래 행까지 채우고 마지막 값까지 검증했다.

Q: 발주일자에서 발주 연월을 추출하는 수식은 어떻게 만들 수 있는가?

발주일자가 들어 있는 열과 결과를 표시할 셀을 챗GPT에 알려주고, 발주 연월 형식으로 변환하는 엑셀 수식을 요청하면 된다. 영상의 예시에서는 챗GPT가 TEXT 함수를 활용한 수식을 제시했고, 이를 첫 결과 셀에 붙여 넣은 다음 끝까지 채워 2024년 1월부터 12월까지의 값을 확인했다.

Q: 여러 부서 시트를 하나의 시트로 통합하는 VBA는 어떻게 요청하는가?

각 시트가 부서별로 나뉘어 있고 A열부터 H열까지 동일한 양식이며 행 수는 서로 다르다는 조건을 챗GPT에 전달한 뒤, 하나의 시트로 합치는 VBA 코드를 요청하면 된다. 생성된 코드는 Alt+F11로 VBA 편집기를 열고 모듈에 붙여 넣은 후 F5 또는 Alt+F8로 실행한다.

Q: 챗GPT가 만든 시트 통합 매크로에서 무엇을 확인해야 하는가?

통합된 각 부서의 데이터가 원본과 일치하는지, 열 순서가 유지됐는지, 머리글이 포함됐는지를 확인해야 한다. 영상의 실행 결과에서는 여러 부서 데이터가 통합됐지만 첫 행의 열 이름이 빠졌다. 이 경우 첫 번째 시트의 머리글도 포함하도록 조건을 추가해 코드를 다시 요청할 수 있다.

Q: 어떤 엑셀 작업을 VBA 매크로로 자동화할 수 있는가?

수식만으로 처리하기 어려운 색칠, 셀 병합 해제, 여러 시트 결합, 기준값에 따른 데이터 분리 같은 반복 작업을 VBA 매크로로 자동화할 수 있다. 영상은 동일한 양식의 부서별 시트를 통합하는 사례와 함께, KPI 실적에 따른 신호등 색칠이나 특정 인사 기록 양식으로 데이터를 옮기는 작업도 예로 든다.

Q: 챗GPT로 엑셀 작업을 할 때 결과 검증이 필요한 이유는 무엇인가?

생성형 AI가 제시한 계산이나 코드는 잘못될 수 있으므로 원본 데이터와 직접 대조해야 한다. 영상 초반에는 AI로 분석한 평균과 적정 기간의 숫자가 이상했던 사례가 언급되며, 매크로 예시에서도 통합 결과의 머리글이 누락됐다. 따라서 일부 키값과 마지막 행, 합계, 열 구조를 확인하는 절차가 필요하다.

Summary & Key Takeaways

• 먼저 직장인이 반복해서 쓰는 기본 기능을 익힌다. 서로 다른 표는 프로젝트 ID 같은 키값과 VLOOKUP으로 연결하고, 조건별 개수와 합계는 COUNTIF·SUMIF로 계산한다. 데이터가 많다면 피벗 테이블의 행과 값 영역에 필드를 배치해 부서별 예산, 집행액, 잔여액을 빠르게 집계할 수 있다.

• 기본 기능으로 해결하기 어렵거나 사용법이 기억나지 않을 때는 챗GPT에 상황을 그대로 설명한다. 질문 문장을 복사해 엑셀 작업임을 밝히면 닫힌 피벗 테이블 필드 목록을 다시 여는 절차를 받을 수 있다. 셀 범위와 목표 열을 함께 제시하면 발주일자에서 발주 연월을 추출하는 수식도 작성받을 수 있다.

• 수식으로 처리하기 어려운 반복 작업은 챗GPT에 VBA 코드를 요청한다. 여러 부서 시트의 열 구조가 같고 행 수만 다르다는 조건을 알려주면 하나의 통합 시트로 모으는 매크로를 만들 수 있다. 생성된 코드는 VBA 편집기의 모듈에 붙여 실행하되, 누락된 머리글이나 잘못된 결과가 없는지 반드시 검증해야 한다.