엑셀은 활용도가 매우 높아, 수많은 사람들이 일상 업무와 데이터 분석에 활용하고 있습니다.
하지만 엑셀의 다양한 기능을 최대한 활용하기 위해서는 종종 VBA(Visual Basic for Applications) 코드를 작성해야 하는데, 이는 처음 접하는 분들에게는 어려운 작업일 수 있습니다.
왜냐하면, 코드 언어 자체를 공부해야 하거든요 ㅠㅠ
VBA 코드란?
VBA 코드는 엑셀에서 반복 작업을 자동화하고, 사용자 지정 기능을 추가할 수 있게 해주는 일종의 스크립트입니다. 마치 엑셀에 명령을 내리는 일종의 "지침서"라고 생각하시면 됩니다.
예를 들어, 엑셀에서 매일 같은 데이터를 정리하고 보고서를 만드는 일이 있다면, VBA 코드를 사용해서 그 과정을 자동으로 처리할 수 있습니다. 이렇게 하면 사용자가 일일이 수작업으로 할 필요 없이, 단 한 번의 클릭으로 반복 작업을 끝낼 수 있습니다.
VBA 코드를 배우려면 상당한 시간을 들여서 공부를 해야 하지만,
다행히 GPT로 이러한 문제를 손쉽게 해결할 수 있는 방법이 생겨났습니다.
이제는 내 엑셀 안에 있는 내용과 내가 원하는 결과물을 GPT에게 설명만 잘해준다면
GPT가 알아서 VBA 코드를 만들어주는데요.
이번 블로그에서는 GPT와 엑셀을 연동하여 사용하는 방법에 대해 자세히 알아보겠습니다.
실제 활용 예제 3가지를 소개하겠습니다.
아! 미리 말씀드리면 GPT는 유료버전을 사용하셔야 더 정확한 코드를 얻을 수 있습니다.
GPT-3.5를 쓰시는 분들도 가능은 하지만, 오류가 발생할 확률이 높기 때문에 이점 참고해 주시기 바랍니다!
활용 예제 1번_셀 통합하기
가장 간단한 예제부터 알아보겠습니다.
위 표를 보시면 개발팀, 경리팀, 교육팀, 컨텐츠팀 이렇게 4팀이 있는데요.
오른쪽 엑셀 표처럼 A열에 있는 중복된 칸을 통합해보려고 합니다.
물론 셀 통합하기 기능을 사용하면 아주 간단하게 진행할 수 있지만,
활용하는 방법이라고 생각하시고 가볍게 봐주시면 좋을 거 같습니다.
우선 GPT에게 다음과 같이 질문해봅니다.
이때 "칼럼"은 A열에 있는 내용이라고 생각하시면 됩니다.
A열에 "부서" 칼럼이 있는데,
같은 부서의 칸을 통합하는 VBA 코드를 작성해 달라고 GPT에게 요청을 합니다.
그러면 아래와 같이 GPT가 VBA코드를 작성해 줍니다.
이 코드를 그대로 엑셀 문서에 적용해 주면 되는데요.
다음과 같은 순서대로 진행하시면 됩니다.
VBA 코드 적용 방법
우선, 기본 엑셀 파일을 매크로 통합 문서로 변형을 해주어야 합니다.
VBA 코드를 적용할 엑셀 파일 왼쪽 상단에 "파일"을 눌러주세요.
그리고 순서대로
1. 다른 이름으로 저장
2. 찾아보기
3. 아래 파일 형식에서 "Excel 매크로 사용 통합 문서"를 클릭하시면 됩니다.
4. VBA 코드를 적용하실 때마다, 파일 형식을 변형해주셔야 합니다.
그리고 다음은 아래 화면처럼 개발 도구라는 탭을 활성화시켜주셔야 합니다.
활성화하는 방법은
1. 엑셀 왼쪽 상단에 "파일"을 눌러주시고,
2. 왼쪽 하단에 있는 "옵션"을 눌러주십니다.
3. 엑셀 옵션에서 "리본 사용자 지정" 메뉴를 눌러주시고,
4. 오른쪽에 "개발 도구"를 체크해 주십니다.
5. 마지막으로 확인을 눌러주시면 개발 도구 메뉴가 활성화되어 있을 거예요.
그러면 준비 단계는 끝났으니, 이제 본격적으로 VBA 코드를 적용해 보겠습니다
개발 도구 메뉴에 들어간 다음에 Visual Basic을 클릭해 주세요.
그러면 아래와 같은 화면이 나오는데요.
상단에 있는 "삽입"을 누르고 그리고 "모듈"을 눌러주세요
나타나는 창에 GPT가 만들어준 VBA코드를 그대로 복사해서 붙여 넣기 해주세요
그리고 상단에 있는 "재생"버튼을 클릭해 주시면, VBA 코드가 적용된 걸 확인하실 수 있습니다.
활용 예제 2번_데이터 일괄 처리하기
다음은 본격적으로 실무에 적용할 수 있는 내용을 알아보겠습니다.
아래 표에 있는 이름과 이메일 그리고 전화번호를 전부 암호화시켜보도록 하겠습니다.
우선 GPT에게 VBA코드를 적용해 달라고 요청해 보겠습니다.
프롬프트
엑셀 안에 있는 내용을 암호화하는 VBA 코드를 작성해 주세요.
1. C열에는 "이름" 칼럼이 있습니다. 2행부터 실제 데이터가 있습니다. 2행부터 끝행까지 이름을 전부 암호화해주세요. 암호화는 이름의 첫 글자만 남기고, 나머지 텍스트는 *로 표기해주세요.
Ex) 한서준 -> 한**
2. E열에는 "이메일" 칼럼이 있습니다. 2행부터 실제 데이터가 있습니다. 2행부터 끝행까지 이메일을 전부 암호화해주세요. 암호화 할 때, @이하의 텍스트는 그대로 유지하고 @ 앞 텍스트는 첫 글자만 남기세요. 그리고 나머지 텍스트는 *처리합니다.
Ex) 4rnzu2wo@hanmail.com -> 4******@hanmail.com
3. F열에는 "전화번호" 칼럼이 있습니다. 2행부터 실제 데이터가 있습니다. 2행부터 끝행까지 전화번호를 전부 암호화 해주세요. 암호화할 때, 전화번호 가장 맨 앞 숫자 3글자와 가장 마지막 숫자 4글자만 남기고, 나머지 번호는 암호화해줘.
Ex) 010-7834-2426 -> 010-****-2426
프롬프트를 위와 같이 적어줍니다.
이때 중요한 점은 총 3가지입니다.
1. 어떤 열에 어떤 내용이 있는지 명확하게 설명합니다. 위 예시에 쓰여 있는 것처럼 C열에 어떤 칼럼이 있는지 그리고 어디부터 데이터가 실행이 되는지 정확하게 써주면 GPT가 더 잘 알아듣습니다. 만약에 이런 설명 없이 무작정 VBA 코드를 만들어 달라고 하면, 오류가 날 확률이 높습니다.
2. 무엇을 만들어야 하는지 GPT에게 잘 설명해 주세요. 여기서는 암호화를 원한다고 썼는데요. 명확한 결과물을 위해 내가 원하는 데이터 처리 방식에 대해서 말해주어야 합니다.
3. 예시를 써주세요. GPT에게 질문하고 답변을 받을 때, 예시를 주면, 정확도가 어마어마하게 높아집니다. 예시를 주느냐 안 주느냐에 따라서, 다른 답변을 얻을 수 있습니다.
프롬프트를 입력하시면, 답변을 얻으실 수 있습니다. 해당 VBA 코드를 예시 1번처럼 적용하시면 원하는 결과물을 얻을 수 있을 거예요.
활용 예제 3번_연말 보너스 계산하기
이번 VBA 코드는 연말 보너스를 자동으로 추가하는 방법에 대해서 알아보겠습니다.
저는 이 예시에서 고용형태가 정규직이고, 고용기간이 20년이 넘는 직원에게 연말 보너스를 10% 가산한 VBA코드를 작성해 달라고 요청해 보겠습니다.
그리고 연말 보너스가 추가되면 노란색으로 칸을 표시해 달라고 요청해 볼게요.
D열에는 "고용 형태" 칼럼이 있습니다.
F열에는 "고용 기간(년)" 칼럼이 있습니다.
H열에는 "연말 보너스" 칼럼이 있습니다.
고용 형태가 정규직이고, 고용기간(년)이 20년이 넘은 직원에게 연말 보너스를 10% 가산하는 VBA코드를 작성해 주세요. 그리고, 연말 보너스가 가산된 칸은 노란색 칸으로 표시를 해주세요
이렇게 요청하면, GPT가 VBA 코드를 작성해 주는 것을 확인하실 수 있으실 거예요.
적용하는 것은 1번 예시처럼 매크로 통합문서로 파일 형식 변경 후에 진행하시면 됩니다.