Hello everyone, This is oppadu Excel. Now, today is a really hot topic these days. I'm going to look into ChatGPT right away. We will find out how to fully embed this ChatGPT into Excel and use it. Usually, if you search on YouTube or Google, there are many things that are used by asking ChatGPT for functions or VBA codes like this. What we are going to learn today is completely new. So, if you ask Excel to extract only keywords from your resume in this way, you can extract only important keywords from your resume like this. And if you summarize and enter about 5 topics that will be included in the report, you can automatically complete the report. Likewise, we are going to find out how to automatically complete blog posts like this. All the features you use today Anyone with Excel 2013 or later can use it However, it is only available on Windows, and on Mac, you can use ChatGPT in another way. This function is only available on M365, so we plan to prepare a separate lecture later. And today we use VBA, but even beginners in Excel can follow it If you come to the homepage, you have organized all the example files and master codes. I hope that today's video and the materials I have prepared will be a little helpful for you to see your work. So, let's start today's lecture First of all, an API key must be issued to integrate ChatGPT, which we use today, with Excel. So, let's first look at how to access the ChatGPT site and get an API key issued. If you go to the video comment or the link written on the screen, you will see the ChatGPT introduction page like this If you look at the top right here, there is an API. Click this button. Then, if you have an existing ID, click the LOG IN button here. And if you do not have an ID, click the SIGN UP button. You can register as a member here. If you click the Continue with Google button below, you can conveniently log in with your Gmail account. I'll try logging in quickly too If you log in like this, if you look closely at the top right, you will see this icon Click this icon. I'll click the button called View API keys in the middle. Then, go to the page where you can get an API key like this If you click this plus button in the middle here, your API key will be issued. Copy the key by clicking the green button on the right side of the Copy button here. After running Notepad in Windows, paste it here to save this key. This key can only be checked once, so if you forget this key, click the button to delete the key you used previously and get a new key issued again. Now everything is ready Now, let's use it right away by linking Excel and ChatGPT. If you download the sample file from the homepage, we have prepared all the completed forms. So, you can use it right away when you need it in practice, or you can apply it right away by referring to this form. And there are a few things you should know before using ChatGPT. Today, let's take a quick look at the five examples below and quickly check the important things you need to know. First of all, I will add the VBA code to link Excel and ChatGPT. If you look at the top of the Excel screen, there is a tab called [Development Tools]. And if you look in the middle, there is a button called Development Tools like this. Check this button. Click the OK button to add [Development Tools] like this Click the Developer tab here, then click the [Visual Basic] button on the left, or press Alt + F11 as a shortcut key to launch the macro editor, and the most difficult content in today's lecture is over. Now, you can directly integrate ChatGPT by simply copying and pasting. Click the [Insert] button at the top of the editor. I'll add a module here. Then, when you come to the homepage, I wrote down the master code like this. Copy this code all the way, then drag it all the way to the end and paste it into this module. And then if you look at the starting point of this code, it's at the top You can put your API key here. If you copy and paste the API key we created earlier into this, you are all ready to integrate ChatGPT. Now, close the macro editor. If you are ready like this, stretch it out. Now, I'm going to use ChatGPT in earnest. First of all, this is a simple question example. Next to 'write a question' like this, the first 'Hello! How is your day?' And select the GPT result cell below. If you enter x after entering an equal sign, there is an xGPT function like this Enter this function with the Tab key. The first argument selects the cell in which the question is entered. The second argument is randomness. I'll select the input cell and enter the function with the Enter key. And if you wait a little bit, you can see the ChatGPT response like this Was it very simple to connect? Now, we're going to change the randomness a bit. This randomness outputs a fixed answer as it approaches 0. Choose a word that is close to the correct answer as determined by ChatGPT And the closer it is to 1, the more diverse the answer. Therefore, when you need to get an accurate answer, use it close to 0. If you need more diverse answers, you can use it closer to 1. So, let's change this randomness to around 0.7 this time. And if you wait a little bit, you can get a slightly different answer like this Or it can be done like this. Like the example on the right, type 'write a poem related to kimchi stew to send to your lover' I'll wait a little bit, then you can see that I'm building a poem like this And this is also possible. Copy all the function questions written as examples, including periods. Paste it here and type your question with the Enter key to get to the function simply like this However, what are the limitations on retrieving functions? Usually, in practice, we want to ask how to find the amount for a product in the entire range, but the GPT-3 model is still not far enough to understand the situation. So, when you ask for a function, you have to enter all of these ranges one by one. So, I still think it's a bit inconvenient to use right away in practice. I think this part will gradually improve over time as the GPT-4 model comes out later. And similarly, if you copy and paste these gym-related questions, you can get legal advice like this If you look at it now, this answer will be a bit long. In this case, you will have to wait a little longer I'll wait a little longer Then you can also seek legal advice in this way. After checking all of this, I will delete the function entered in this. The reason for deleting the function like this Later, when the value of the sheet is changed or the file is re-executed, this function continues to operate, so there are unnecessary costs. I will delete the function because there is also a problem with slowing down the file. As we proceed through the examples one by one, we will eventually find out how to integrate GPT more conveniently in other ways instead of this function. Then the second example might give you an idea In fact, this is a feature I use very often, and I write the topics you want to get ideas for by dividing them with commas. Then, select the number and type of ideas to envision. If you select the separator in this way, I wrote a function so that the question to be used for GPT below is completed in advance in this way. Please check this function in more detail after finishing the lecture. Since the question is pre-completed like this, we can print the result using the xGPT function we just used. So, after selecting the result cell below, enter an equal sign and enter the xGPT function. And, as before, select cell C10, which is the cell where the question is entered. Second, I'm going to type random, then I'll type in the function with the Enter key. And I'll wait for a while. After about 10 seconds, the results came out well like this. However, looking at the results, there is one problem When outputting multiple values like this, each of these values can come out in one sentence, but there are times when each of these data is divided into several ranges and output? Of course, after selecting the cell here, click the Home tab If you check word wrap, you can also see the value divided by line breaks like this However, I have created the xGPT_List function in advance so that you can use it when you need to divide this data into several ranges and output them. However, since this function is an array function, the usage is slightly different depending on the version. For Excel 2019 and earlier users, enter an equal sign while selecting this range widely. If you enter x, you will see the xGPT_List function. After entering this function, select the question and randomness as before. Please note that you must enter with Ctrl + Shift + Enter at the end. And in Excel 2021 and M365 versions, it is the same as the regular function select only one cell After entering the equal sign, enter the xGPT_List function. You can select a question and randomness and enter the function. Today's video will be based on the 365 version. So, after entering the function like this, if you write it with the Enter key, I will wait for a while. Then, the YouTube title related to the Sinchon date we just entered is automatically completed. Shall we change the output type? I'll change the YouTube title to a topic or hashtag. And if you wait for a while, you can see that the GPT result is output as a range like this If you have checked everything like this, this function can continue to be executed in the same way as before, so please delete this function and move to the next sheet. The third example will look at keyword extraction. Let's assume a situation The company needs to review your resume, but usually this isn't one or two, right? You have to review dozens to hundreds of resumes, but it takes a lot of time to see them one by one. Therefore, at that time, if you can extract frequently used keywords from your resume and quickly review whether the direction our company pursues and this person's values match or whether the contents are well written, your work will be more convenient. So today I'm going to extract keywords from my resume. I'll select one from the resume on the right, copy it, and paste it in the form of a value. And if you look at the bottom, I wrote the function in advance so that the question is completed when the value is entered like this. So, if you use the same function as before, you will be able to extract keywords. But when we've been using functions so far I found out that it is inconvenient because the function continues to run whenever the value of the sheet changes. Of course, if this is a one-off task, a function might be more convenient. Otherwise, if you need to use this function repeatedly, add a button like this It is more convenient if you make ChatGPT work when you click this button. So I'm going to look into this method. This method is also really simple. It can be made with one line of VBA code. Let's look at it together. Run the macro editor again in the same way as before. So, click on the Developer tab. Click on the Visual Basic button on the left to launch the macro editor. Then, if you double-click the module on the left, here is the macro code we copied and pasted earlier. If you look closely at the bottom, there are statements called MyGPT_Test1 and Test2. If you need more commands later. It's okay to copy this and paste it below, or you can rename it. This time, we will analyze the keywords So, I will change the name from MyGPT_Test1 to Keyword this time. Now, you only need to write one line of command inside. The command is, xGPT_Run. After entering it like this, if you put a space, the arguments come out like this. It's really simple to use. I'll take a good look at the sheet. If you look closely at the sheet like this, the cell address where the statement we will use for ChatGPT this time is written. It's currently cell C6, right? And this is the cell to output the result. Select the starting cell of the cell to output Please remember only this cell address. So, in the xGPT_Run function, enter the cell address one by one. It was cell C6 earlier, right? So put C6 in double quotation marks. The second cell to output is cell B8, so I'll put it in double quotation marks as B8. And the last third argument is whether or not to output as a list. This time, we will output as a list from cell B8. So, whether to output as a list or not, I will enter True. And type it with enter key and you are all done Now go back to the sheet and have it. You have this button? I'll right-click this button. Then in the middle, there's an item called Assign Macro. Selecting this item will have the MyGPT_Keyword function we just created After selecting this function, if you put it in the OK button, you can now run the macro when you click this button in the future. Shall we click this button once? After clicking the button, I'll wait a bit. Then, an information window will appear like this. If you click the OK button and check the contents, you can see that the keywords in your resume have been analyzed at once like this Shall we change our resume? Copy and paste the second resume this time. I'll click the button again. And if you wait a bit, an information window will appear like this. If you click the OK button, you can see that the keywords in your resume are quickly analyzed. How is it using ChatGPT? Do you get the feeling that 'this is a really powerful feature'? But what we know so far is just the beginning Now, we're going to look at practical examples of how to apply this ChatGPT in practice. Stretch out because it's important from now on. Let's focus a little more and look at practical examples. Practical example First, we're going to analyze the financial statements. You may have felt while using GPT so far, but there is a lot of current usage, so there is such a thing. In fact, when we use Hangul, there is a problem that the response is much slower than when we use English. So, while using ChatGPT, if you need to analyze some mathematical data unrelated to language or create a really long sentence, you can get results faster if you use English rather than Korean. I'll analyze the data. I wrote down the link on the right in advance. If you click Samsung Electronics among the links, you can view Samsung Electronics' financial statements on Yahoo Securities. I'll drag this financial statement all the way and copy it with Ctrl + C. Then paste the copied financial statement into this cell as a value. Double click on this cell After making the cursor blink, paste it as a value inside. Enter it with the Enter key, and everything is ready. If you look closely at the screen now, we will look at the sheet when we analyzed keywords earlier. The input cell address was cell C6, the output cell address was cell B8, right? And if you look at the financial statement sheet, you can see the same as input cell C6 and output cell B8. So, we can use the macro we created earlier, right? So, right-click on this button on the screen. After coming to macro designation in the same way as before, select the keyword macro you used earlier. Click the OK button to register the macro. And then click the Execute button. Click this button and wait for a while If you wait for about 10 seconds, you can see the result of the analysis like this But after analyzing This result was printed in English So, I'm going to translate the results printed in English back into Korean. I've prepared a function for this translation to be used right away in Excel. It's the GoogleTranslate function. Select the first cell in the Google translation result below. Enter an equal sign and then type google. The GoogleTranslate function is like this. Enter this function with the Tab key If you select the cell to be translated, the first cell, and then press the Enter key, you can see that it is translated directly into Korean like this. And then I'll autofill this function all the way down Then, in this way, financial statement analysis in Korean has been completed simply. Now, shall we change this financial statement to another company? This time, the Hynix financial statement. Copy and paste it inside. Click the GPT execution button again and wait a little bit. If you wait a little bit like this, you can see the simple analysis of the financial statements for Hynix like this. this is the end It is the long-awaited function of the end of the practical use of ChatGPT. If you briefly write about 5 topics to enter into a report or blog post, you will find out the function of automatically creating a report. So, if you enter only the topic you want in this form, we have prepared it so that you can use it right away. First of all, I will choose a business report as the document type. And then on to the subject. This time I'll do it like this. In order to analyze the YouTube channel, I will put it like this to analyze Then, if you look closely at the bottom like this, you can see that the questions to be used for ChatGPT are automatically translated and completed in the same way as before. And if you click the plus button on the left, you can use the GoogleTranslate function in this way to translate the topics you just wrote into English. I wrote a function in advance so that these translated topics could be put back together to complete the question. Please check this function in detail after today's lecture. Since the question is completed like this, you can click this button just like before and apply a macro to run GPT, right? So let me add a new statement Run the macro editor again. This time, I will put Report after MyGPT. And enter xGPT_Run in the same way as before. The cell to input is the current cell C16. Enter C16 in double quotation marks. The cell to output is the current cell B18. Put B18 in double quotation marks. Currently, it will be output as a single statement in a cell, rather than as a list. So, I'll put it as false for whether it's a list or not, and if you press the Enter key, everything is ready. Go back to the sheet and right-click the button. Select the Report function you just created in Specify Macro, and click the OK button to apply the macro. then click the button And I'll wait a minute After that, if you wait for a while, the report is completed like this In my case, it took about 20 seconds because the sentence was long. This time may vary depending on server usage or internet speed. When you click the OK button, the report is completed neatly like this, and the results of GPT are printed in English. The result printed in English will be translated back into Korean with the GoogleTranslate function. So, if you look closely at this content, the naturalness of Hangeul in the current sentence may be a little awkward, so you can use it by correcting this awkward part appropriately. Isn't that great? Just put in a topic like this and the report will be completed automatically. Shall we make a blog post instead of a business report this time? Please turn it into a blog post. This time, I will make a post about the cause and solution of the low birth rate problem in Korea. And in the same way, click the Run GPT button and wait for a while. And if you wait a little bit, an information window like this will appear. If you click the OK button, it could be a blog post report on the low birth rate issue in Korea. You can see that the report is automatically completed like this Now that we've created all the files I will save this file Since the file we just used contains macros, we need to save it as a macro-enabled workbook. Press the shortcut key F12 to launch the Save As window. This file is saved with macros included. Next time you need this function, you can use it in conjunction with Excel. And most importantly, the ChatGPT API features we've been using so far aren't free. Of course, the ChatGPT test function provided by default on the homepage can be used for free. The API we just used is paid according to usage. However, when you sign up for ChatGPT for the first time, you get an 18 dollar credit that can be used for 3 months. So, after the free credits are over, you need to register the payment method on the page where the API key was issued earlier. If you go to the page where the API key was issued earlier, there is a Billing button on the left like this Click on this billing Click the Setup Paid Account button on the right to register and use the card. Then click Usage on the left to check how much you've used so far And the cost of ChatGPT is settled in units of tokens. For a more detailed explanation of fees or tokens, please refer to the first sheet in the example file as we have briefly summarized the key contents. For a more detailed explanation, please refer to the link to ChatGPT's official website below. If you have additional questions about the content covered in today's lecture, please leave a comment below. Thank you very much for watching the long video today. See you in the next lecture
