G11 | Google Sheets光標控制面板， 十字光標、一字光標、關閉光標，再也不怕看錯行資料了🎉
#溢位 #SPILL錯誤#excel#跨頁合併#合併工作表#彙整工作表#跨页合并#合并工作表#工作表合并#汇整工作表#excel vba教學視頻#excel vba範例#microsoft excel#Excel行事曆#Excel列表記事#excel 行事历制作#excel 行事历设定#vlookup#Excel月曆#excel 公式及函數運用#excel 公式鎖定#Excel年度行事曆#excel 月曆製作#取出字串內的文字#尋找取代#班表#員工出勤記錄表#自動排班表#補班日#樞紐分析#透視表#取出工作表名稱#串接儲存格#多條件加總#sumif#甘特圖 excel#gantt chart#鎖住圖片#三層下拉清單#Filter#陣列#提取PDF文字#多欄轉單欄#十字光標#光標控制面板#一字光標#AI輔助辦公室應用系列#AI#ChatGPT#Google sheets#Apps Script#google apps Script#AI ChatGPT
266 次觀看｜1 次總結｜2 年前
💫 總結
這部影片介紹如何在Google試算表上設定十字光標的功能，方便查看數據內容。
使用自訂控制功能，可以設定十字光標、一字光標以及關閉光標，避免看錯行資料。
通過與AI的溝通與修改程式碼，完成了動態顯示十字光標的設定。
✦
在Google試算表上設定十字光標的功能，可以幫助使用者更輕鬆地閱讀和定位資料。
00:04在功能區啟動光標面板。
點選十字光標按鈕可以在作用儲存格上標註顏色。
點選水平光標可以只標註橫列的部分。
可以隨時關閉光標功能。
✦
這段影片介紹了Google Sheets程式碼的結構和修改方式
02:43程式碼由Meiko和ChatGPT生成，並且是逐步追加的
程式碼分為主程式和右側面板兩部分
可以修改功能區的名稱和顏色的部分
可以修改右側面板按鈕的名稱
程式碼將放在影片下方的資訊欄供觀眾使用
✦
這段影片示範如何使用自訂控制面板，以及如何使用十字光標查看特定資料。
05:26點擊啟動右側面板可以開啟自訂控制面板。
點擊更新十字光標可以快速查看特定資料。
可以嘗試減少套用的範圍以提升速度。
可以使用格式化的顏色來做光標的標註。
✦
在Google Sheets中，可以使用光標控制面板來設定光標的樣式和顏色。
08:06可以在主程式中更改自訂控制的名稱。
可以啟動光標設定面板並選擇十字光標。
可以調整水平（行）光標的顯示。
可以在色碼表中選擇喜歡的顏色並將其應用到光標。
需要執行一次以應用設定，並可能需要授權設定。
✦
這部分是關於Google Sheets光標控制面板的使用方法。
10:48進階設定未命名的試算表。
與AI溝通，設定十字光標的顯示方式。
提供程式碼並進行溝通，最終只有十字光標的設定。
✦
這一段視頻介紹了在Google Sheets中控制光標的面板，包括更新十字光標、切換到列光標和關閉光標的功能。
13:31可以通過修改程式碼來控制光標的功能。
列的意思在台灣是指橫向的，與美國和中國的欄不同。
在與AI溝通時，要注意將行稱為列，欄稱為行。
00:04Hello everyone, today I want to share with you in this video how to set up the cross cursor function on Google Spreadsheet. We see this information on the screen. In fact, there is quite a lot of it. If you don’t set the freeze window for this information, There is no cross cursor set in the grid. It is actually quite hard to look at the data, so I want to take the students to set up a cursor function. I activate the cursor panel in the ribbon area. After the cursor panel is activated, we can see it on the right. There is a cursor-controlled window. When I click the cross cursor button, an eye-catching color appears in the horizontal column and vertical column of my active cell. Then when I move to another cell, I will go again. Click the cross cursor once and it will re-make a positioning setting. Sometimes when we only need to see the column and not the column, we can click the horizontal cursor. After clicking the horizontal cursor, it will only mark the part of the horizontal column. So when we pull the data far away, we won't see the wrong row. When we don't need this cursor function, we just click this to close the cursor, and then you can perform other editing operations. Today's video is Let me show you how to completely set up this menu and the panel on the right. The steps of our entire operation today are actually very simple, because the difficulty lies in the program code part. Meiko communicates with AI and asks AI to generate it. I think the program code is actually used many times during the communication process. It is not in place all at once. Wait a minute and I can share with you the communication process of Meiko. Let’s quickly start today’s video. We are now at the file of the exercise. We were just in the example. Now we come to the exercise. At the beginning of the exercise, I would like the students to freeze the pane first. Basically, our column name and our name part must be moved first, so I put the cursor first. Then we find the position of B2 in the upper ribbon. Then we find View Freeze and then freeze the first column above, which is the title column. Then when we move the scroll down, you will find that the title will be fixed on the screen. Then we click again to view. Then freeze. At this time, we have to freeze two columns, which is the column at the position of my cursor. After freezing the two columns, it will draw a dividing line to the right of my cursor, so when I move to the right, the name can be fixed on the screen. The above, but when we look at the data like this, it is actually quite hard. Because there is so much data, it is easy to look at the wrong row. So we have to do the cursor settings. Let's first look at some of the program code. The code we see on the screen is generated by the communication between Meiko and ChatGPT. This code is actually quite long and cannot be put in place at once. Meiko just keeps adding and adding, slowly adding the functions I want. It's so long, and then it has two sections. The top section is the main program, and the bottom section is the main program on the right panel. We can modify several places, such as the name of the ribbon above, and this place is now the name in the list. The part below where we can adjust the color is that there is a color code here, and the right panel below is through the HTML code below. Here we can modify the name of the button. So we will paste it first and then modify it. So I click Copy here first. Meiko's part of the code will be placed in the information bar below the video, and students can access it by themselves. After we copy it, let's go back to our example. After returning the example, we first go to the extension function above. Then click on this Apps Script here and click in. We will paste the program code here. A program code should appear on its own first. Then mine was deleted by me, so I will add it and click the plus sign above and then the script code. Then we won't give the name first, and then we will delete the frame it provides here and then Ctrl V to paste it. After pasting it, we can change the name of this panel. Let's keep it as it is for a while, then modify it and save it. Save it. Then we will rename it. Here we take a cross cursor and set it up OK. Then we will paste the second piece of code. The second piece of code we want is the following HTML piece. Copy it and then go back to us. After returning to the example, you still click the plus sign on the left, but this is not a command code. What you need to use here is HTML. Click it, and then we will give it a name after a while. Then we will delete the frame it provides and then Ctrl V After pasting it, a very important point is that we go back to our script code and our HTML naming must follow its rules. So let’s look at the name of the HTML here. What name do you want to choose? It must be correct, otherwise it will It won't be found here. Ctrl C. Then we go to our HTML, click here, and rename it. Ctrl V. Paste it like this. Then we save it and then we go back to our cross cursor setting code. Click Execute and it will be executed quickly. Then let's go back to the example. After you come back, you will find that there is an additional custom control here. However, this custom control (name) is a bit strange. Students can slowly collect some functions and then put it Go to this list. Then let's click this to launch the right panel. After clicking it, the panel on the right will be generated. Then let's change the name of this button. Now let's click on a random position. Let's say I want to know this person. Then I click this to update the cross cursor. I think the speed is about two seconds. In fact, I have been communicating with the AI and hope that its speed can be faster. However, how it communicates with it is that the whole column is the same. Apply this format, so students can try to see if the scope of application can be slightly reduced to see if the speed can be improved. Then it has a very good point. When I am communicating with the AI, please do not replace us. The original color of the bottom of the screen. I hope it can use the formatted color to mark the cursor. So when I click on other cells and then click update, you will find that it will not destroy our original color. Sometimes we You only need to mark the data in horizontal columns, which may not be necessary for vertical columns. After we click on a certain cell, we use the row cursor. If we use the row cursor, we can only mark the color for this column and it will not look more cumbersome. Just this column is fine. When we move to the right when using the scroll, we can also lock this column so that we won't read the wrong row. It's really convenient. When we don't need to use it, just click this. Just close the cursor and turn it off. Then if you don't need it for the time being, turn off the panel. When needed, go to the custom control and turn it on again. Isn't it very convenient? In fact, I think I am following When communicating with AI, I write down my requirements step by step. When I find something inconvenient during execution, I will optimize it again, and then optimize it again if it is inconvenient. In this way, I optimize the final part and it is what I think is more suitable for me. Usage situation, so I think that now that we have the function of ChatGPT, students don’t have to be too afraid to touch the program. Anyway, what he gives is wrong. We just modify and modify (prompt). Just tell him your needs in a very natural language. Okay, then I think AI is also very smart. It can slowly modify its program according to your suggestions to meet your needs. You just need to communicate multiple times. Now let's change these. The name of the button. Let's go back to the program code. Then in the main program, we first change the custom control above. Let's change the function of a Meiko special tool. Then activate the right panel below and we will activate the cursor setting panel. Then we go to HTML, where we just use the cross cursor. Then we do it here. Abroad, it is called row. In mainland China, it is also called row, but in Taiwan, it is column. Then I think we call it horizontal. Close the cursor and then color. For the part, we need to find a color code table, so let's find an OK. Then we'll find a favorite color. Let's pick a classmate and mark it according to the color you like. I'll use this. Ctrl C to copy back. Let's go back to the main program and change the color code here. Ctrl V. I talked a lot, so my throat is a little sore. OK, let's save it and then execute it again. OK. Well, actually during execution, it will An authorized setting. The authorized part of Meiko does not appear now because Meiko has just been tested several times. Then I will reopen a file and operate it once to show you the authorized setting. Let's execute it first. After the execution is completed, let's come back and practice. Then we turn it off first and then reorganize it. After reorganizing it, you will see the above. The name above is the Meiko-specific function. So students don't need to write Meiko. Just write yours. Just name or write your function, and then launch the cursor setting panel. Then after launching it, we click the button cross cursor. If you look at the color, if it is too heavy, you can change the horizontal cursor and then close the cursor. OK, no problem. Then because Meiko's operation this time does not have that authorization, so I will reopen a file and share it with everyone. I will come to a blank spreadsheet, and then we will hand over the Apps Script again, and then re-paste the code. OK, then after I've done everything and posted it, let's click to execute it. When you operate it for the first time, this should appear and you need to authorize. Then let's click on this to review the permissions and then select your own account. Then after you select your account and come in, this security warning will definitely appear. That's okay. Let's just click on Advanced. Then click on Unnamed here. Unnamed Project. This is because I added a new spreadsheet here, so I also You didn't give it a name, that's okay. Just click it and click OK. Then it will do the execution after it's OK. After it's executed, go back to your spreadsheet and then You'll see the panel here and start OK. Then you can click like this and it's done. Meiko will share with you the steps I took to communicate with the AI at the beginning. I was talking to it. I said that I want to make a cross cursor, but this is too simple, so it gives me a fixed one and it will not change. Then I tell it that I want it to be dynamic. Wherever the cursor moves, the cross cursor will be displayed dynamically. Then I still need to make a switch, and then it provides me with the program code. An error occurs. Okay. After communicating a few times, it provides me with a more correct program code. Currently, this program code only has the setting of the cross cursor. And there is this panel on the right side. There are only cross cursors and close on the panel. The execution speed is actually a little slow, so at this time, I opened another new chat room, and then I decided to do a role play because In the previous communication, I asked it to adjust the speed several times. The speed was about three seconds before the cursor could be displayed. But I felt that I was told to wait for three seconds during the operation. I really didn’t have the patience to wait, so I decided Do a role play and see if I ask it to play the role of a Google App Script programming expert and be good at simplifying the code. This is very important. Then it can write professional programs to solve various spreadsheet problems. Then of course it will accept it. Accept it. After that, I posted the correct code that it provided me, then asked it to optimize it, and then told it my needs. I told it that the format part should be clicked with my mouse. To make an immediate change, I kept emphasizing to it whether it can be done horizontally and vertically as long as 20 cells. Don’t fill the whole column because it saves a lot of cells. The whole column is actually very time-consuming, so I want to say that the operation can be faster. Is it possible to just draw a few grids? But after communicating with it several times, it still completes the entire column. Then a very important thing is to ask it not to destroy the color of the bottom of my original user's network. The speed must be fast, and then the next step is to let The user uses a button to make a switch, and then the above action can be performed. Then when it is not needed, the user can press the button to finish. Then it rewrites the code for me, so it is successful at this time. I execute it. The speed was only about two seconds, and I felt that it was very good. Then when I came back, I suddenly thought, well, if I want to make it faster, I would give up the vertical columns and just use horizontal columns. Then I asked it to help again. I modified this code and wanted to have three operations: one is to update the cross cursor, the second is to use the column cursor, and the third is to close the cursor. There are only three functions. Then I emphasized to it again whether it can be 50 squares. Okay, but it may still not understand it, so it helped me correct it. This string of code has these three functions. But because we are talking about the column, the column is Taiwan, but it is the United States and the United States. The Chinese column, so the horizontal column I want, the place where it is applied will be the vertical column, so here I will tell it, the column I am referring to is the Taiwanese column, so it is horizontal, and then ask it to help me modify it. Then it said, oh, it knows that it is the column of Taiwan and then the horizontal part. So actually when we are communicating with the AI, when we want columns, we have to talk about rows, and when we want columns, we have to talk about columns, that is, there is Diandian is not the same as our understanding. Then it updated the code for me. The last code is actually very good. OK, then I told it that you are great. The purpose was achieved. Then, I think this The function code actually automatically activates the panel on the right. When I close the panel, I have to go to the program code and execute it again before it can open the panel again. So I thought, eh It’s not very convenient. Then I wanted to ask if we can open certain functions by clicking on the ribbon above like we can in the window software. This would be great, so I’ll talk to it again. I hope to be able to open certain functions in the ribbon. A function that activates the right panel and then can you help me add this code to it? Then it will help me update this function again. Then it will help me update this function again. So for today's example, it is actually a comparison. It's complicated, but I didn't get it right in one go. I had to communicate with ChatGPT several times before I could achieve my goal. I don't think we have to understand the functions of these programs very well. I think as long as you know how to communicate with AI, then To get the results you want, you can express your needs to it multiple times. Even if the code it gives you is wrong, you can also paste the error message to this AI and let it make modifications. Then I think in the end As long as you are patient, you should still be able to use AI to help you achieve some more automated settings. Well, let’s share today’s video with you here. Then we’ll see you in the next video. Bye bye.