ChatGPT로 엑셀 시트 검색과 거래 내역을 어떻게 자동화할까?

TL;DR

ChatGPT로 작성한 VBA 코드를 엑셀 시트 모듈에 붙여 넣으면 검색 버튼과 셀 선택만으로 거래처 및 구매 내역을 필터링할 수 있습니다. 일반 매크로는 도형 버튼에 연결하고, 거래처 클릭에 반응하는 코드는 Worksheet SelectionChange 이벤트로 구현한 뒤 파일을 XLSM 형식으로 저장합니다.

Transcript

여러분 안녕하세요. 오빠도 엑셀 전진관입니다. 저 오늘은 엑셀에서 자동화 서식을 만들 때 실무에서 바로 적용할 수 있는 유용한 팁을 준비했습니다. 엑셀 자동화를 공부해 보셨다면 아마 VBA에서 유저품을 사용한 여러 자동화 서식을 만드는 방법을 한 번쯤은 보셨을 겁니다.이 유저폼이 자동화에는 굉장히 편리하지만 어 실무에서는 별도의 폼을 실행해서 사용하다 보니 불편할 때도 많이 했는데요. 그래서 오늘은 별도의 요조폼 없이 엑셀 시트 안에서 이렇게 값을 클릭했을 때 실시간으로 필터링 되고요. 또는 버튼을 클릭해서 자동화하는이 방법을 10분 영상으로 알기 쉽게 정리했습니다. 어 코딩을 전혀 몰라도 괜찮습니다. 체 GPT를 사용... Read More

Key Insights

• 일반 매크로는 편집기의 재생 버튼이나 F5 키로 직접 실행하거나, 시트에 배치한 도형 버튼에 연결해 사용자가 클릭할 때 작동시킬 수 있습니다.
• 이벤트 매크로는 셀 선택이나 값 변경 같은 특정 동작을 감지해 실행되며, Worksheet SelectionChange는 선택한 셀이 바뀔 때마다 반응합니다.
• 거래처 DB와 구매 내역 DB를 거래처 아이디로 연결하면, 선택한 거래처의 상세 정보와 구매 기록을 같은 시트의 보고서 영역에 출력할 수 있습니다.
• 검색 매크로는 구분 또는 거래처명과 검색어를 입력받아 마트, 백화점, 온라인, 현대 같은 조건에 맞는 거래처 목록을 필터링하도록 구성됩니다.
• ChatGPT에 작업 순서와 대상 범위, 검색 조건, 출력 방식을 구체적으로 적은 프롬프트를 제공하면 시트에 붙여 넣을 VBA 코드를 작성할 수 있습니다.
• 삽입 탭에서 만든 도형을 검색 버튼처럼 꾸민 뒤 매크로 지정을 선택하면, 생성된 일반 매크로를 버튼 클릭 동작과 연결할 수 있습니다.
• 거래처 아이디가 있는 셀을 클릭했을 때 구매 내역을 갱신하려면, 자동화 대상 시트의 SelectionChange 이벤트 코드에 해당 로직을 넣어야 합니다.
• VBA 코드가 포함된 결과물은 일반 통합문서가 아니라 매크로 사용 통합문서로 저장해야 하며, 저장 후 파일 확장자는 XLSM으로 바뀝니다.
• 🎬 이 채널의 다른 영상:
  • 엑셀 초보자가 VBA로 반복 업무를 자동화하는 방법은 무엇인가?
  • 은행 거래내역으로 자동 확장 엑셀 가계부 대시보드를 어떻게 만드는가?
  • 직장인 PPT를 읽지 않아도 핵심 메시지가 바로 보이게 디자인하는 방법은?
• 📺 이 채널: 오빠두엑셀 l 엑셀 강의 대표채널

Questions & Answers

Q: ChatGPT로 엑셀 거래처 검색을 어떻게 자동화하나요?

검색 조건, 참조할 데이터 범위, 결과를 출력할 위치와 처리 순서를 설명한 프롬프트를 ChatGPT에 입력해 VBA 코드를 생성합니다. 코드를 거래 내역 관리 시트의 코드 영역에 붙여 넣은 뒤, 구분 또는 거래처명과 검색어를 바꾸어 실행하면서 결과가 정확히 필터링되는지 확인합니다.

Q: 엑셀의 일반 매크로와 이벤트 매크로는 무엇이 다른가요?

일반 매크로는 사용자가 재생 버튼이나 F5 키를 누르거나 시트의 버튼을 클릭할 때 실행되고, 이벤트 매크로는 셀 선택이나 값 변경 같은 동작이 발생할 때 자동으로 실행됩니다. 검색 버튼에는 일반 매크로를, 거래처 셀 클릭 감지에는 이벤트 매크로를 사용합니다.

Q: 엑셀 VBA 편집기는 어떻게 실행하나요?

개발 도구 탭에서 Visual Basic 버튼을 클릭하거나 Alt와 F11 키를 동시에 눌러 VBA 편집기를 실행합니다. 개발 도구 탭이 보이지 않으면 리본 메뉴를 우클릭해 리본 메뉴 사용자 지정으로 이동한 뒤 개발 도구를 체크하고 확인 버튼을 누릅니다.

Q: VBA 코드는 엑셀의 어느 위치에 붙여 넣어야 하나요?

프로젝트 창에서 자동화 대상인 거래 내역 관리 시트를 더블 클릭하고, 오른쪽에 열린 해당 시트의 코드 편집 영역에 코드를 붙여 넣습니다. 시트 목록은 통합문서의 시트 추가와 이름 변경을 반영하므로, 대상 시트가 맞는지 확인한 후 코드를 입력해야 합니다.

Q: 검색용 도형 버튼에 매크로를 어떻게 연결하나요?

삽입 탭의 도형에서 모서리가 둥근 사각형을 추가하고 검색이라는 문구와 색상, 윤곽선, 글꼴을 설정합니다. 완성된 도형을 우클릭해 매크로 지정을 선택한 다음 거래처 검색용 매크로를 골라 확인하면, 도형을 클릭할 때 필터링 작업이 실행됩니다.

Q: 거래처 셀을 클릭하면 구매 내역이 나오게 하려면 어떻게 하나요?

거래처 아이디 셀의 선택 변화를 감지하도록 Worksheet SelectionChange 이벤트 매크로를 사용합니다. ChatGPT로 작성한 이벤트 코드를 거래 내역 관리 시트의 코드 영역에 추가하면, 필터링 결과에서 거래처를 클릭할 때 해당 아이디와 연결된 구매 내역이 보고서 영역에 출력됩니다.

Q: 거래처 DB와 구매 내역 DB는 어떻게 연결하나요?

두 데이터 시트가 공통으로 사용하는 거래처 아이디를 기준으로 연결합니다. 구매 내역 DB에 기록된 거래처 아이디가 거래처 DB의 동일한 아이디를 참조하도록 준비하면, 거래처를 검색하거나 선택했을 때 그 거래처의 상세 정보와 구매 내역을 함께 조회할 수 있습니다.

Q: 완성된 엑셀 자동화 파일은 어떤 형식으로 저장해야 하나요?

파일 탭에서 다른 이름으로 저장을 선택하고 파일 형식을 매크로 사용 통합문서로 바꿔 저장해야 합니다. 저장이 완료되면 확장자는 XLSM이 되며, 일반 통합문서 형식이 아니라 VBA 매크로를 포함할 수 있는 형식을 선택하는 것이 핵심입니다.

Q: 공유받은 XLSM 파일을 열 때 오류가 발생하면 어떻게 하나요?

파일의 속성에서 차단 해제 항목을 체크한 뒤 다시 실행합니다. 매크로 파일을 다른 사람과 공유하면 처음 여는 환경에서 화면에 오류가 나타날 수 있으므로, 공유할 때 파일 속성에서 차단을 해제해야 할 수 있다는 안내도 함께 전달하는 것이 좋습니다.

Q: 검색 조건이 제대로 작동하는지는 어떻게 확인하나요?

구분 항목에 마트, 백화점, 온라인 같은 값을 넣어 실행하고, 검색 기준을 거래처명으로 바꾼 뒤 현대나 마트 같은 검색어도 시험합니다. 각 조건에 맞는 거래처만 결과 영역에 표시되는지 확인하고, 이어서 거래처 아이디를 클릭해 구매 내역까지 올바르게 바뀌는지 점검합니다.

Summary & Key Takeaways

• 먼저 거래처 DB와 구매 내역 DB를 거래처 아이디로 연결한 관계형 구조를 준비합니다. 개발 도구의 Visual Basic 버튼이나 Alt와 F11 키로 편집기를 열고, 자동화 대상인 거래 내역 관리 시트의 코드 영역에서 일반 매크로와 이벤트 매크로의 차이를 확인합니다.

• 거래처 검색 조건과 결과 출력 과정을 설명한 프롬프트를 ChatGPT에 입력해 VBA 코드를 생성합니다. 완성된 코드를 대상 시트 모듈에 붙여 넣고, 구분이나 거래처명과 마트, 백화점, 온라인, 현대 같은 검색어를 조합해 결과가 올바르게 필터링되는지 확인합니다.

• 검색용 도형에는 일반 매크로를 지정하고, 거래처 아이디 셀을 클릭할 때 구매 내역을 갱신하는 작업에는 Worksheet SelectionChange 이벤트를 사용합니다. 완성된 파일은 매크로 사용 통합문서인 XLSM으로 저장하며, 공유 파일에서 오류가 나면 파일 속성의 차단 해제를 확인합니다.