![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgrGf-NxmtZ28ybzRZd58Ai4HIFvBfU6yoHtdzkvEyL-ajDh0Do9TDAf6tS1OYfW-1GBqfA120nn9M0x83EuYZtZUa6W-3yyTSIPnHEzfJYLEwaivgzn_LVk4GOrR_kM6d7kPRLmCYt-AlgrroYj65awnXrqRuRmlzBJsAL2Z-Qgi1Qs9JgojxtmXpWMQ/w640-h360/GEG%20Changhua%20blogger%20-Google%20sheets%20logo.jpg)
超好用 Google Sheets 試算表函數(持續搜集中...)
歡迎留言分享更多好用的試算表函數!
🎯 欄位資料合併 & | 🎯 "&欄位&" | 🎯 QR Code | 🎯 GOOGLETRANSLATE 翻譯 |
🎯 REPT 班級小組分數登記 | 🎯 RANK 排名 | 🎯 ROUND 無條件進入 |
🎯 ROUNDDOWN 四捨五入 | 🎯 ARRAYFORMULA 最高幾次成績平均 |
🎯 FREQUENCY 成績組距人數統計 | 🎯 VLOOKUP 資料搜尋 | 🎯 YEAR 年齡計算 |
🎯 IMPORTHTML 匯入網頁表格資料 | 🎯 COUNT | 🎯 COUNTA | 🎯 COUNTIF |
🎯 SPARKLINE | 🎯 TRIMMEAN 去頭去尾求平均 | 🎯 MID 民國年月日變為西元年 |
🎯 IMPORTRANG | 🎯 IF | 🎯 IFS
🎯 QR Code
=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150&data="&ENCODEURL(A2))
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhlG1RxH-aG3RZgBR-0_i4mhaDuEbFtOMNdlf2FgxFR9rf-L5XwYLKVPZxidzRkx8xbjJEG9OBPy8bFhieN6-nQOTIloThAkyqDQOxEfandE69lm4BNrhSc9cD17ZUnvM2myhAaHGJ-ldQ1qs0wIYaoCAaPeB6MWfh76MN9V2oq3nvhiX_Nhraz3pTRvul3/w640-h430/%E8%9E%A2%E5%B9%95%E6%93%B7%E5%8F%96%E7%95%AB%E9%9D%A2%202024-12-25%20215441.jpg)
🎯 Ctrl+D 複製第一格欄位
🎯 F4 鎖定儲存格
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg7wnJT3uArSYhDbYikPjd56KKRG3J76OyFl9aQz2yvqXkv6IdTs71clYYjxuFvoI6D_ZTtEt60aDyx14jZYJJ3oy6FgXqxUcYvtrJq3AhS2XWx1AAjeNa-OG2gNVBdugyckD4Jz9zdhB43zVYCBE0v1I0s0J_TS164YF0M09PHUbDfkE1VrJXt1UQ7iw/w640-h442/2022-04-01_22-21-18.gif)
🎯 欄位資料合併 &
=D15&E15
相關使用方法請參考 👉👉「G Suite 管理員」新學年更改學生帳號名稱
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfZ3AUJGcgTb1x531KhS1KdfvAy1aVeNcWZ1SMHnX0VPRTkU3KYkJnsrI3WaooICMZzRiO6nr85oue6FRtWHnRCcBSvNSJ4uNeRmQ4btNNiTtMqdZkmMaZBBRz8lvMyyN2sP3odF6JRj5bLz8b9BtrbRQhLUZvsrActWBb8cowcFcLNwn3qbaplk-esw/w400-h106/2022-03-31_22-53-03.png)
=GOOGLETRANSLATE(A1,"zh","en")
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEip_x7ofvTH5ENGkiDFuF1SCq6WZxhJzi62_IDi_8_b97DMAmWmKrNW5-beyNFCdMr9UNJpd8mQ27PjNd_AkC2qH1PJLFpKEik3axv4YgnBx3Hn4YI7o6K3RR700Xe_r5x8BYY5cPsBZ7xzelB0r62Ux_2CXtEY9i85gyjYA1S7z1QLCO_2PN3OwRwa4w/w640-h178/2022-03-31_21-47-33.png)
中文:zh、英文:en、法文:fr、德文:ge、日文:ja、韓文:ko、西班牙文:es、印度文:hi、阿拉伯文:ar、義大利文:it、越南文:vi、俄文:ru
🎯 REPT 班級小組分數登記,函數說明
=REPT("🏆",$A1)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7ifBWwHR1c4GH-pr4ju--LGNHeFX8Ssks7eRMFDnXZ0c_2gaJYwlyytIoiKZXxBgLRAo6j64AP-T-gLHEAp4ErAWkuZg9mrINpLJFtHdxRDilxeI9ZS9lJ-UA4G81VGMdXXvZJWZFy5xfG39rfwZlbCdBtyIeuYyahGIKRGHKb3jLBb5s3-YNKoVNJA/w640-h236/2022-03-31_21-50-42.png)
🎯 RANK 排名,函數說明
=RANK($I20,$I$2:$I$30,0)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhshDVgHxKYxiwOGMEwphCGzwmpHDLsZuIeRY5tehunzHywf62P1zmf9Uo260v-EVKChR4Cv9RcwVw4rIqKQQ04YK8aDp0Ou2zmlwVGZUuvKrbqL6N6WKuKuvNfBcmq_yq1Io0iMEAUcU8N71yHhazKIIgx4DD0e-tws_nxuUd28TcJ1I8clTemaZXCSA/w640-h210/2022-04-01_08-16-41.png)
🎯 ROUND 無條件進入,函數說明
🎯 ROUNDDOWN 四捨五入,函數說明
🎯 ARRAYFORMULA 陣列公式 運用 arrayformula 在標題中加入函數
=ArrayFormula({"合併資料欄位";B2:B&E2:E})
🎯 ARRAYFORMULA 陣列公式( Ctrl+Shift+Enter)
最高幾次成績平均,函數說明
=arrayformula(average(large(C3:G3,{1,2,3})))
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3iGaClTzQcSZmAUOFNkdzyNR8KAcQ9-AeeWY0GKlnUS1hWWfwPydQb0B9K_i6IPi1y1bYtPknqbA0HHHuLup75-68WCLlQBJy5JeSmfaDIlCw2Uis1nOSeVzQgT3HiXf9W65aEQ4VZt53ccwENlPHmnOAnSwFuiGKCs9xgoabKcVulXnFPkoZus2Kug/w640-h160/2022-04-01_08-52-07.png)
🎯 FREQUENCY 成績組距人數統計,函數說明
=FREQUENCY(I2:I30,L2:L7)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8el9jXVyAbqYCx5BYTcSIJTojdn6whv-kdGolxtuUJ6X2DpXur9gajHKId0BwLt0SIrMokSd2jcEVQhuTswM885Z7VlbCJ-iHQnotq8yKs_p90Bi0OWp-SBYK-PHots_hqeDKJPDRYnXvDlVAsgwASZ5YfdMBoxSicjb8_jYzI1ZgFtiHvz5G2LvIaQ/w640-h456/2022-03-31_21-56-54.png)
=IF(ISBLANK(H2), "", VLOOKUP(H2,'時間'!$A$2:$C$14,3,FALSE))
=vlookup(E6,'班級座位表'!$A$1:$B$43,2,false)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgYbTiEuHV3kfvxIuvS4-zCqG2PfOSj8ogQaTaeDfZ9uuWO_IHxWK0COBE3Qa_sqeQdcvM2dqZrmeGxSFQWjQuI6Gnw5XC5aKygXz4Oh3wwEs4dh23vVUc9IsJgs9ssYAYWN4cOi2egwgCJ1IKp7Mq7pvLGt_CVLQ8mrYKpSmLHzg8vVZukeY-PD_-oLw/w640-h256/2022-03-31_22-04-20.png)
🎯 YEAR 年齡計算,函數說明
=TODAY()
=YEAR(A2)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwZa-vGSKmpGlGTT1fX4zy1qvovIPC25TqsOjiGXRxnteOHh9iEkkHorijXOCPrZEFqOW173RaNQM_b9mN50PWlv-6B9XFwL1l6jvzYQNFhhHQpF-GjeetqytJAH9OQPiQKE0K8pI8jfJxE6enDFW4VFHlcH2G2axSEHAyej44G6DWYaobvh1xxeOwnA/w640-h74/2022-03-31_22-28-41.png)
🎯 IMPORTHTML 匯入網頁表格資料,函數說明
=IMPORTHTML
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjMvNLx0gnh7VttyCBTZKRdNZMlh5hq3dT65v5la_EfmEx7yRNjNRnsRKw3JB8SiELqUQx2A9THPVrv9if1L29gzhfyf4HnQD5knSUzytMHXfEonZB5huDBtgM8H5mmG69aDLOA6ioGLb3bRLOL7OFB79tGK86fbQH6OVS36vcwSH8AI92OB9Fjjo7zEQ/w400-h336/2022-03-31_22-32-07.png)
🎯 COUNTIF,函數說明
=COUNTIF(B11:K11,true)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhiZVl9uHvI6erBMKfZpCQYhrirtX_NLnRsF2XJn9CBRKXy65v0XwIKeKzEWuzXOdjvGoBmu__cbIInrt5EOrNHjeLDLvvMqxOmfS8UH7xoOUn8KwYQGikH80dd5ROJHW641Ts_xhk2ezOLF7JFQQzu_vYqgpSjEP6rk8FWPuduSURXsIAcAUeCVv2iJA/w640-h152/2022-03-31_22-38-28.png)
🎯 SPARKLINE,函數說明
=SPARKLINE(L11,{"charttype","bar";"max",10;"color1","green"})
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgOjnxtWvO-ijSChwJep3U6R6Qes1Ax9ORjBHo81oPCxNr6xC9rJjhmTygrb_wAdLkVF2JFFEwIasqMk9qYS5XFsSDkM7jJzEO-GAXOyPfz-Kz_e8yKjy4G62C8H7Q62sAbagRA4JfRiHxUuTHdQfWqv0RXEUbIL6VfdVmpe-JZJsusy9jOh6JCjtlMQw/w640-h146/2022-03-31_22-41-07.png)
=SPARKLINE(T10,{"charttype","column";"ymin",0;"ymax",max(30);"firstcolor","#8bc34a"})
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-bSJG8Pq9gcJmelaWnTFGU_dv5_zik7CaGlskbV6B6oJpFG5mJmfVD21pUOFegRNPX0I3NMWUSr4rFMzrtgNXAcSE3VjYRZKLDQ9A-dF8kSyh26sAj1iF_3hx_iOJ-X4KJRiOmKfO6yvh6q6pfRZOGESJlw3-I_0wFMXALoEyE7xCvJ-21s33VPkL_Q/s1600/2023-03-01_09-38-51.png)
🎯 TRIMMEAN 去頭去尾求平均,函數說明
=ROUND(TRIMMEAN(A22:E22,2/COUNT(A22:E22)),2)
相關使用方法請參考 👉👉 [筆記] 去頭去尾求平均
🎯 MID 民國年月日變為西元年,函數說明
=MID(A25,1,3)+1911 & "/" & MID(B25,1,2) & "/" & MID(C25,1,2)
相關使用方法請參考 👉👉 [筆記] 把民國年月日變為西元年的方法
語法說明:當 C2欄位以下沒有資料,就顯示 "" ,當 欄位有資料如果符號 其中一個名稱就回傳顯示 數字 資料
=if(A2:A="",,IFS(A2:A="六年一班",601,A2:A="六年二班",602,A2:A="六年三班",603,A2:A="六年四班",604,A2:A="六年五班",605,A2:A="六年六班",606,A2:A="六年七班",607,A2:A="六年八班",608,A2:A="六年九班",609,A2:A="六年十班",610,A2:A="五年一班",501,A2:A="五年二班",502,A2:A="五年三班",503,A2:A="五年四班",504,A2:A="五年五班",505,A2:A="五年六班",506,A2:A="五年七班",507,A2:A="五年八班",508,A2:A="五年九班",509,A2:A="五年十班",510,A2:A="四年一班",401,A2:A="四年二班",402,A2:A="四年三班",403,A2:A="四年四班",404,A2:A="四年五班",405,A2:A="四年六班",406,A2:A="四年七班",407,A2:A="四年八班",408,A2:A="四年九班",409,A2:A="四年十班",410,A2:A="四年十一班",411,A2:A="三年一班",301,A2:A="三年二班",302,A2:A="三年三班",303,A2:A="三年四班",304,A2:A="三年五班",305,A2:A="三年六班",306,A2:A="三年七班",307,A2:A="三年八班",308,A2:A="三年九班",309,A2:A="三年十班",310,A2:A="三年十一班",311,A2:A ="二年一班",201,A2:A ="二年二班",202,A2:A ="二年三班",203,A2:A ="二年四班",204,A2:A ="二年五班",205,A2:A ="二年六班",206,A2:A ="二年七班",207,A2:A ="二年八班",208,A2:A ="二年九班",209,A2:A ="二年十班",210,A2:A ="一年一班",101,A2:A ="一年二班",102,A2:A ="一年三班",103,A2:A ="一年四班",104,A2:A ="一年五班",105,A2:A ="一年六班",106,A2:A ="一年七班",107,A2:A ="一年八班",108,A2:A ="一年九班",109,A2:A ="一年十班",110))
松禧老師好讚!
回覆刪除感恩分享!
感謝老師留言,希望這些函數對老師有幫助~
刪除