Today we will talk about how to use chat gpt Improve your excel ability by at least 10 times chat gpt has been applied to more and more fields Excel is a tool that we often use in our daily life and work. Today this video will change the way you use excel forever Let's not talk nonsense, let's directly demonstrate the operation First we need to log in to chatgpt Here we can use the free 3.5 version Because we don't need gpt4 networking and plug-in functions In addition, the current response speed of 3.5 is higher than that of gpt4 to a certain extent. What we are going to demonstrate is to use chat gpt to help you Use and understand the use of excel functions First we show a very simple example and gradually increase the difficulty This is the operating data of a wine sales company We can see that it has many different products This is how much each product sold last month I want to calculate the sum of all product sales amount in the last month Suppose we don't know how to evaluate the function You can see that there is a button to insert a function at this position We can click on it to go to the options page of the function Entering text in this position can tell excel what kind of work we need this function to do For example, we enter You can see that excel itself cannot provide us with any feedback Then we bring the same problem to char gpt see how it performs Input: I need to calculate b2 to b12 in excel What function should be used to sum all numbers everyone can see chat gpt gives us the result of a function Then we need copy code now then put this function Paste into the cell we specified The result is generated Of course this is the simplest function Now let's increase the difficulty to see the performance of chat gpt This is the company's wine list price list Our task is to find this small Nolan dry red Commodity prices corresponding to green forest dry white This we just used to show what we actually encountered Maybe there will be more types of products for this sale ten times twenty times this So how do we find our target product in this and automatically fill in its unit price In the traditional method, we may need to select the column and use the search and selection function to find the corresponding Find out the corresponding small Nolan dry red and fill in the unit price in the specified cell In fact, we can also use the combination of vlookup function or index match function to quickly extract this value If you don't know how to use these functions We can directly throw the question to chat gpt to see its performance Tell chat gpt directly: I want to be in this excel form Find Petit Nolan Dry Red and extract its sales amount we click send You can see that chat gpt provides us with two methods of functions One is the vlookup function The other is the function combination of index and match Then we first look at the first vlookup It not only tells us the result of the function, but also tells us that the function The logic behind it We can see its first hint It requires us to name Petit Nolan Instead of this option for our D2 So how to do it We first copy Then we paste this function in the specified cell At the same time, if we want to find Xiaonuo blue dry red, we need to use D2 Replace with Xiaonuo blue dry red then it directly generates the result We can compare it to see its answer the answer is absolutely correct Then let's take a look at its index and match function combination It also tells us the logic behind the operation of this function Then we paste and copy Because this goal is green forest dry white Then we need to replace the position of D2 with green forest dry white Then compare the results again the answer is absolutely correct The power of chect gpt is that it can not only provide us with output results It also gives us an idea of how the function works In this way we can draw inferences We don't need to ask chatgpt the same question next time we have the same question It's like learning a trick Okay, let's increase the difficulty further The task of this job is to extract the name of each mail For example, the first lucy@gmail.com We need to extract the name of his email address then the name is lucy So we can address him as Dear Lucy when we email him in the future The first method we can use the quick fill function of excel such as the first we know his name should be lucy then other we can click data quick fill button In this way, the contents of other similar cells can be quickly generated In addition to this method, we can also use functions to fill in We will report the problem to chatgpt and see how he will deal with it I need to extract all the text information before @ in column A in the excel text You can see that it also provides us with two methods of functions First of all, the first one is the function combination of left and find used You can see this formula What it says is that the formula will extract the text before @ in cell a1 But in fact our a1 cell is a header Then we need to change A1 to A2 Then first copy the function We select the cells that need to generate values Then paste this function Change a1 to a2 Then we need to click ctrl and enter You can see that we also generated the result At the same time, this result is completely correct Next, let's try the second function method provided by chatgpt Similarly we also need to change a1 to a2 everyone can see It achieves the desired result as well At the same time, if we want to seriously understand the operation logic behind this function we can ask chat gpt Please help me explain how this function works In this way chatgpt will tell us how this function works behind the scenes At the same time, it includes the cells corresponding to the meaning represented by each part This is convenient for us to use later If something goes wrong we also know what to do where to make some changes ok, we still need to further increase the difficulty We see this is everything this wine company sells for a while We want to know the main types of SKUs that are sold today among so many products it involves a lot of repetition Our job is to de-duplicate it Finally determine how many types of products he sells today We also give the question to chat gpt Then we can ask chat gpt like this In excel, I want to know that there are several different contents in total from a2 to a17 I need you to provide me with a calculation function Then a2 and a17 refer to us A range that requires function calculation in excel You can see that chatgpt provides us with two function values You can see that the first function value calculates the number of non-blank cells So it's of little use to us We can directly paste his second function value We paste the function in the specified cell You can see that the result it generates is six So let's do a quick check to see if its result is correct You can see that the result is completely correct Then we need to further increase the difficulty This job requires us to publish corresponding emails for each specified mailbox And inform us in the mail that the goods have been sent to you you need to pay for us the specified amount You can see that the amount corresponding to different mailboxes is also different. If we need to complete this work, we need to use the macro function of excel First we need the data column Turn on developer features Right click on this part Then select custom region You can see that we need to check the development tools in this position After confirmation, a development tool option will pop up in this position in our excel sheet Then in the development tool, we have to click on this visual basic and then simultaneously click Insert module So here we need to enter the code of a module We inform chatgpt of our needs See how chatgpt performs We can inform chatgpt in this excel like this edit a macro to send mail using the following content hello Your purchase has been dispatched for you Please send the product after receiving the product and confirming that it is correct payment x then (extract x value from column a remitted to the company's designated account) (The email link sent is in column b) Then click send to see how chatgpt responds to us You can see that chatgpt has written a vba code for us Then we just need to copy this code Then paste it into this inserted module Then we click close In this case, we can click the macro button again to see it has formed a directive We only need to click execute next. What needs to be emphasized here is because our excel performs the function of sending emails through its associated outlook account Achieved But actually excel this outlook Its connection has been unstable After many operations, we finally sent the email Then we have to go to our mailbox to see if he has received the specified email You can see that we have received the mail Hello, the product you purchased has been sent out for you Please pay 3212 after receiving the product and confirming the product Let's compare this result 3212 is no problem at all The program is here, I want to say that with the chat gpt, we can Never again because of the complicated and unknown functions of excel feel helpless We just need to know what functions excel can achieve Specifically how to achieve it, we can hand over this work to chat gpt While completing the work efficiently, we can also use chatgpt to Understand the function and usage of these excel functions Continuously widen the gap with others through such accumulation If you like my channel, please like, share and subscribe This will let the algorithm know that you like content like Push more relevant channels for you Looking forward to meeting you next time I am Joy bye bye
