I. ChatGPT在理解VBA代码中的利用

II. 使用ChatGPT编写Excel宏的步骤

III. 使用ChatGPT编写Excel宏的技能

IV. ChatGPT在Excel宏编写中的利用案例

Introduction

Spreadsheets are commonly used in finance and accounting departments to perform critical tasks. Traditionally, only highly skilled spreadsheet users were capable of writing macros to automate repetitive tasks. However, with the advancement of natural language AI technology, specifically the introduction of ChatGPT⑷ (GPT⑷), even individuals with limited programming experience can now create intricate scripts to streamline their work. This development not only enhances productivity but also allows professionals to dedicate more time to value-added projects.

Streamlining Excel Macros with GPT⑷

In a recent article published in the Journal of Accountancy in January 2023, Kelly Williams, a Certified Public Accountant (CPA), shared her experience of using ChatGPT⑶.5 (GPT⑶.5) for automating Excel tasks. However, GPT⑷, introduced in March 2023, offers significant improvements over its predecessor. Trained on more than 100 trillion data points, compared to the 175 billion data points used to train GPT⑶.5, GPT⑷ exhibits better comprehension and software writing skills. Furthermore, GPT⑷ has the capability to process longer and more detailed prompts, enabling it to tackle more complex macro writing challenges.

The Power of GPT⑷ in Creating Excel Macros

Many finance and accounting professionals engage in repetitive tasks, such as downloading data into spreadsheets, cleaning and editing the data, and then uploading it into another system. Similarly, data cleansing, appending data from different systems, and summarizing it using tools like PivotTables are often time-consuming and prone to errors. These repetitive steps are typically performed in a fixed sequence month after month for several years.

Thanks to natural language AI, complex macros can now be created to automate processes like data cleansing, data summarization, intricate computations, PivotTable creation, and generating unique reports. While various generative AI tools can potentially create macros, this article focuses on writing prompts specifically for OpenAI’s GPT⑷ to generate complex Excel macros.

Step 1: Account Creation

The first step is to create an account on openai.com. It is recommended to sign up for a premium account, which costs $20 per month and provides access to the advanced AI system, GPT⑷. Free accounts only offer access to GPT⑶.5.

Step 2: Developing the Prompt

To instruct GPT⑷, it is necessary to create a detailed prompt. Start by outlining the Excel actions in a text editor, numbering each stage carefully. It is crucial to ensure that the numbering accurately copies and pastes into GPT⑷. The prompt should be thorough, specific, and as comprehensive as possible. Keep the prompt document saved, as you may need to add or modify steps while creating the macro. Instructions may not be interpreted by GPT⑷ as intended.

Step 3: Entering the Prompt

Once the prompt is created with a precise and accurate description of the desired actions, open a chat window on openai.com and select the GPT⑷ model. Copy and paste the prompt from Step 2 into the prompt box. The system will generate a code along with instructions on how to enter it in Excel or Google Sheets.

If the code is particularly long, the system may pause. In such cases, simply type “continue” and the system will generate the remaining code.

Step 4: Testing and Adjustment

After copying the code into the spreadsheet and saving it, run the macro. GPT⑷ can assist in troubleshooting any issues encountered and provide comprehensive instructions on incorporating the macro into the spreadsheet.

It is essential to have a backup of the files before executing a macro, as there is no way to undo the macro once it is initiated. Additionally, the initial version of the macro may not work precisely as intended. In such cases, modify the prompt in Step 2, re-enter it in GPT⑷, and adjust the resulting code in the spreadsheet accordingly. Alternatively, describe the problem in the GPT⑷ prompt box, and the system will refine the code. Repeat the testing and adjustment steps as necessary until the macro produces the desired results.

Enhancing Accessibility with Personal.xlsb

All macros saved in the Personal.xlsb worksheet in Excel are accessible from any open workbook. This allows easier access compared to macros created in other workbooks, which are only accessible when the relevant workbook is open.

Ensuring Privacy and Confidentiality

It is important to note that no confidential information should be entered into an AI prompt. Any prompts entered may be reviewed by individuals for evaluation and improvement purposes. Therefore, prompts should always remain as generic as possible to protect sensitive information.

Example: Data Cleansing, Complex Computation, and Summarization

To illustrate the capabilities of GPT⑷ in creating Excel macros, let’s consider an example of calculating commissions for a global sales force based on product type, location, and units sold. The process includes data parsing, computation of commissions based on predefined rules, and summarization of results. The provided raw data serves as the foundation for these calculations.

问题1：关键词1是甚么？

答案：关于关键词1，它是Excel中的一个功能/概念/工具，用于……

• 更详细的解释和示例。
• 其他相关信息。

问题2：关键词2怎样使用ChatGPT创建Excel宏？

答案：使用ChatGPT创建Excel宏的步骤以下：

1. 打开Excel并按下Alt + F11键，打开Visual Basic编辑器。
2. 在编辑器中，您可以输入自定义的ChatGPT代码来创建宏。
3. 编写代码时，请确保使用正确的语法和函数，以实现所需的任务。
4. 保存并运行宏，以利用代码并履行相应的操作。

请注意，使用ChatGPT创建Excel宏需要一定的编程知识和经验。

问题3：怎样使用ChatGPT解释VBA宏代码？

答案：使用ChatGPT解释VBA宏代码的步骤以下：

1. 将要解释的VBA宏代码复制到剪贴板中。
2. 打开ChatGPT，并进入Excel宏解释模式。
3. 将代码粘贴到ChatGPT中，然后等待生成对代码的解释。
4. ChatGPT将提供逐行解释和理解代码的详细说明。

使用ChatGPT解释VBA宏代码可以帮助您更好地理解代码逻辑和功能，特别是在处理复杂的宏代码时非常有用。