Shulou(Shulou.com)11/24 Report--

The original title: "what level can be achieved by using ChatGPT to do Excel? here comes the answer!" "

As an ordinary professional who is not very proficient in Excel applications, when dealing with data in forms, he often can't think of any formula to solve the current problems, or can't remember the specific usage of a formula, so he has to check it temporarily, which is troublesome.

But with ChatGPT, we only need to clearly describe the problems we encounter, and it can automatically give us a probable solution, and even help us write VBA macros to solve complex table processing problems.

Let's test it with several common functions to see if it can really give the correct function formula.

Summation of continuous regions

Cases A2 to A9 cells are the data to be summed. If we want to get their summation results in A10 cells, we tell ChatGPT the requirements and get the following reply, which is indeed the correct result.

Cumulative summation

Next, the difficulty of peace escalates.

For ordinary workers, it is difficult to write the cumulative sum if they do not know the relative and absolute references, so we try to ask ChatGPT to write it for us. The cumulative summation in the corresponding cells in column B is given as follows:

The results given here do meet the requirements, but each cell is a new formula, so after trying to communicate, it gives me a function result that can meet the demand at once, which is quite intelligent.

Conditional summation

Now let's count the total amount of orders for pendants sold in store 3. For the first time, it gives the SUMIF function, which can only satisfy the order synthesis of all the goods sold by Store 3.

After telling it the calculation result, it modifies the function to SUMIFS function in time, and gives the correct function formula. It seems that it is not difficult to sum it with multiple conditions.

Logical function

Suppose the mainland of Tivat holds a Qiu language test, and only those who score more than 80 in both written and oral tests can get a certificate. Let's see if ChatGPT can judge it correctly.

Obviously, it's easy for it to handle.

Query reference function

Vlookup function is a very useful query function, you can use a keyword to find other related data, but for this function with more parameters, many people will not use it correctly. After I submitted the question to ChatGPT, it gave me the correct example.

You only need to replace the parameters with the information in the table to get the correct results:

Race

= VLOOKUP engraved A1VOKUP C9pd2 false attribute

= VLOOKUP engraved A1VOKUP A1VOKUP C9Mague 3meme false

Data with equal length and regularity of text functions can be split quickly using fragmentation, which is difficult to split when text and numbers differ in length as follows.

But ChatGPT still offers the right solution:

If you don't understand this function, you can ask it to help you explain how this function is implemented.

Date function if the work needs to follow up the progress of many projects, know the start date of the project, and the latest completion date.

If you simply subtract the date, you can only get the number of days between, but not the exact number of working days. At this point, you can throw the problem to ChatGPT and let it help you with the calculation.

VBA programming finally let's try to ask AI to write a VBA macro for us to output the names of all worksheets in the workbook to the current cell.

After I used the code to create the macro, I got the right result, which can really help us make VBA free!

Written in the end, from the above tests, using the AI tool of the ChatGPT class can indeed help us solve some common problems in function calculation and VBA programming, but it is important to note:

❶ We have to clearly state our table structure and needs, which is a very high threshold for most people who do not understand the form. Learning to ask questions correctly is the first step to successfully solve the problem.

The solution generated by ❷ AI is not necessarily the right result, you have to have certain knowledge accumulation and methods to judge and verify.

❸ is just like the sentence at the bottom of today's tool window-it's not AI who takes your job, it's the one who mastered AI first.

Therefore, if you want ChatGPT to help you solve Excel problems, you must at least have a certain Excel foundation and have an overall and systematic understanding of Excel.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: teacher Xiaoyang

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.