Memorizing the formula is too weak! Use ChatGPT to deal with Excel problems, and the efficiency increases dramatically

ChatGPT Since the launch of OpenAI on November 30 last year, this AI chatbot has quickly become a "popular hot chicken" in the AI ​​world. Once it was released, many netizens were so obsessed with it that they stayed up all night talking to it, just to explore the application ceiling of ChatGPT. After testing, many people found that ChatGPT seemed to be an all-round warrior who could chat, write code, modify bugs, Making smart speakers, writing neural networks...

But! As a senior migrant worker, Word, PPT, Excel, etc. are indispensable in daily work. I would not be happy if ChatGPT could be integrated into these applications. Microsoft is already working intensively on this.

We won’t be able to guess how quickly Microsoft moves, but some people are already unable to sit still. This AI named PyCoach Enthusiasts started using ChatGPT to write Excel formulas, and their work efficiency increased by 10 times.

PyCoach says what we need to do is create valid prompts so that ChatGPT can generate Excel formulas and macros.

Anyone who has used ChatGPT knows that prompts occupy a very important position. Among the three major office tools of Word, Excel, and PPT, Excel is undoubtedly the most difficult to master. If you want to be proficient in it, you need to remember many formulas. But it’s much easier with tips, chatting with ChatGPT can solve the problem.

Let’s take a look at how PyCoach implements it.

Use ChatGPT to complete the Excel formula

First you need to create an account. After successful registration, you will get the following interface:

Create account address: https://chat.openai.com/auth/login

Connect The next step is to use ChatGPT to complete the Excel formula. When using Excel, we often use its built-in calculation functions, including database functions, date and time functions, statistical functions, etc. These functions each have their own names and formats. When calling, they need to accurately enter parameters according to the prescribed format, which brings some burden to Excel users.

But now, we just use natural language to "tell" ChatGPT what to calculate. Let's take the following full-year income and expense data table as an example. Let's assume that we are new to Excel and don't know how to add the values ​​of the Expenses column.

SUM

In this case (when we want to sum some data), we Just tell ChatGPT which data to sum, and it will output a formula with the actual parameters plugged in. For example:

ChatGPT is like a little assistant who is proficient in Excel. We put the formulas written by it into B14 The sum of the data in cells B2 to B13 can be obtained in the cell.

Sometimes, we have multiple questions about an Excel table. At this time, we can also ask continuous questions about ChatGPT. For example, for the income and expense data table above, if we want to know 1. the number of times monthly expenses exceed $100,000; 2. the total number of unpaid expenses, we can ask ChatGPT to get the calculation formula:

COUNTIF

This step is to count the number of times monthly expenses exceed $100,000:

SUMIF

This step is to request the fees marked "No" in the "Paid" status column. and:

Especially for some complex functions, if we can’t remember the parameter format, we can ask ChatGPT to help write the correct format, such as VLOOKUP:

Extract data

Next, challenge the task to upgrade. Suppose we have the following list of phone numbers, and we want the additional area code, which is what is in the preceding brackets.

##Describe this task to ChatGPT:

The following is the formula generated by ChatGPT:

=MID (A1,FIND ("(",A1)+1,FIND (")",A1)-FIND ("(",A1)-1)

The only modification we have to make is to replace A1 with A2, and then we can get the result!

Calculate unique values

Next we count how many unique area codes there are in column B . As you can see in the image below, ChatGPT generates very complex formulas that don't work. The reason may be that ChatGPT remembers every detail of the conversation. We can try to solve it by asking a general question:

##The formula generated by ChatGPT is as follows

=SUMPRODUCT (1/COUNTIF (range, range))

If you add the range of the area code, the formula becomes as follows:

=SUMPRODUCT (1/COUNTIF (B2:B9, B2:B9))

Use ChatGPT to create a macro

Next let’s Try using VBA to create a simple macro to sort the sheet by tab name.

As can be seen from the above figure, ChatGPT has an error. At this time, we need to describe the error to ChatGPT and debug it. .

After some debugging, ChatGPT got the job done, but not as expected. Additionally, ChatGPT sorts sheets by tab name, which changes one of the tab names to temp.

The above is PyCoach’s exploration of ChatGPT. It can be seen that ChatGPT is still very helpful. Friends who are still having trouble writing Excel formulas can give it a try.

The above is the detailed content of Memorizing the formula is too weak! Use ChatGPT to deal with Excel problems, and the efficiency increases dramatically. For more information, please follow other related articles on the PHP Chinese website!