[數據分析#37] Google Sheet 試算表20 個必學功能(公式教學 ...

而Google Sheet 比起Excel，除了免費之外，還有太多只有他才有的功能 ... [數據分析#37] Google Sheet 試算表20 個必學功能(公式教學/外掛及工具分享). Skiptocontent 【早鳥學員募集中】數據分析師轉職培訓班一對一諮詢預約：https://calendly.com/couplehonest/step Postauthor:LisaWu Postpublished:86月,2022 Postcategory:數據分析/GoogleSheet 近期，很多企業(尤其是新創或是跨國企業)都開始購買GoogleCloud的服務，讓員工可以在Google空間裡協作：傳送Email、共同編輯文檔或是資料等等，一起來學習Google工具的實用功能吧！ 而GoogleSheet比起Excel，除了免費之外，還有太多只有他才有的功能，接下來會分享20個對於上班族、企業、或是個人來說最實用的必學功能！ 內容目錄 隱藏 1 為什麼要用GoogleSheet？ 2 GoogleSheet公式教學 2.1 1.GoogleSheet工作表之間互相引用資料：IMPORTRANGE()、ARRAYFORMULA() 2.2 2.除了內建篩選功能，還有篩選公式：FILTER() 2.3 3.財經公式，用來自動更新股票報價：GOOGLEFINANCE() 2.4 4.除了內建排序功能，還有排序公式：QUERY() 2.5 5.自動抓圖功能：用Image()抓網路上的菜單 2.6 6.自動翻譯多國語言：GOOGLETRANSLATE() 3 GoogleSheet技巧 3.1 1.資料驗證：製作下拉式選單、輸入錯誤時提醒 3.2 2.版本回復、編輯記錄查看：是誰把我的資料刪掉了？ 3.3 3.GoogleSheet自動作圖 3.4 4.文字分隔：將文字分隔成不同欄 3.5 5.讓其他人直接建立副本 3.6 6.保護工作表：部分不給其他人編輯 4 不用GoogleSheetAPI就能用的外掛 4.1 1.自動生成短連結：PICSEE_UTM() 4.2 2.虛擬貨幣追蹤：CRYPTOFINANCE() 4.3 3.自動寄送Email：YAMM、Mergommailmerge 4.4 4.甘特圖範例 4.5 5.串接GA(GoogleAnalytics)或是GoogleSearchConsole 4.6 6.GoogleSheet爬蟲工具：Instagram數據分析工具 4.7 7.方程式求最佳解：NLSOLVE() 5 總結 5.1 如何練習GoogleSheet實用公式、技巧、外掛？ 5.2 更多學習資源推薦 6 【1對1免費諮詢會議】數據分析師轉職培訓班 為什麼要用GoogleSheet？ GoogleSheet免費：比起Excel的價格，GoogleSheet在幾乎跟Excel永遠相同功能的前提下，他是免費的連網公式跟外掛都超好用：GoogleSheet上有非常多開發者，在開發免費或是付費工具，像是連網公式、爬蟲、其他實用的外掛工具等等共同編輯：不像Excel需要互傳檔案，GoogleSheet只要傳網誌、開啟權限給對方，就可以開始共同編輯工作表之間可以互相引用：如果有兩份GoogleSheet工作表，可以用公式連接兩者之間的資料，不用再「複製貼上」了 GoogleSheet公式教學 1.GoogleSheet工作表之間互相引用資料：IMPORTRANGE()、ARRAYFORMULA() 在日常工作應該常常會碰到，A工作表想要跟B工作表互相比對，最簡單的方式當然是把B的資料複製，然後貼上到A，但GoogleSheet中有公式可以支援，不用再「複製貼上」了！ 方法一：IMPORTRANGE()把B的工作表自動連動到A裡面，這樣即使是B移動了，A也會跟著連動推薦方法二：如果想要在自己的試算表裡面，在不同分頁裡面互相複製也可以用ARRAYFORMULA()陣列公式連動，ARRAYFORMULA()還有很多其他進階的實用使用情境，詳細教學可以看：GoogleSheetArrayformula5大超實用使用情境教學 2.除了內建篩選功能，還有篩選公式：FILTER() 如果你不想看到所有的資料，可以使用篩選功能來設定你要的條件 方法一：內建篩選功能，直接篩選原始資料，限制是每個欄位一次只能篩選一組條件(推薦)方法二：使用篩選公式FILTER()：如果用篩選公式，可以彈性的變換條件，也不會動到原始的數據，詳細教學可以看：GoogleSheetFILTER()使用方法FILTER()還能跟其他函數，像是查找函數：VLOOKUP()、統計函數：PERCENTILE()、MEDIAN()等一起組合，讓數據分析變得更多元且方便，詳細教學可以看：GoogleSheetFILTER()使用方法 3.財經公式，用來自動更新股票報價：GOOGLEFINANCE() 如果你有投資台股或是美股，想要自動獲得台股或是美股報價，可以用GOOGLEFINANCE()取得每一天的報價外，除此之外，還可以查到每天的價格變化：收盤價、最高價、最低價52週期間的最高價、52週期間的最低價交易量、在外流通的股數如果你查的是共同基金，還有很多財務相關資訊，例如：最近的現金配息金額、最近的現金配息日期等等GOOGLEFINANCE()也可以用來追蹤匯率GOOGLEFINANCE()可以用來製作個人投資報酬率計算表，詳細可以參考我製作的範例：股票投報率計算機免費下載(再教你算投資組合有沒有贏過被動投資) 4.除了內建排序功能，還有排序公式：QUERY() 如果你想對資料做排序，例如說由大到小 方法一：內建排序功能：直接排序原始資料，會「直接」改變原始資料(推薦)方法二：使用QUERY()公式：可以用GoogleSheet才有的QUERY()公式排序資料，不會改變原始資料的格式QUERY()公式以ORDERBY排序資料：ASC就是從小到大，DESC就是從大到小，詳細公式教學可以參考：GoogleSheetQuery9語法大全及練習檔案 QUERY()除了排序以外，還能以SQL語言的形式處理GoogleSheet的資料，詳細教學我會在：GoogleSheetQuery9語法大全及練習檔案這篇文章中教大家如何使用 5.自動抓圖功能：用Image()抓網路上的菜單 用Image()公式，只要將圖片網址跟想要的大小設定好，就可以直接在GoogleSheet中出現，詳細教學：Image()–抓取網路上的圖片菜單 6.自動翻譯多國語言：GOOGLETRANSLATE() 不管是單字或是句子都可以用GOOGLETRANSLATE()翻譯句子翻譯範例：「googlesheet超好用」翻成英文「GoogleSheetissupereasytouse」 GoogleSheet技巧 1.資料驗證：製作下拉式選單、輸入錯誤時提醒 資料驗證的好處是，節省大家打字時間，直接讓大家用選的，即使大家打錯，也可以跳出錯誤訊息，讓輸入者可以更正如何做資料驗證？可以在：資料→資料驗證這裡完成下拉式表單、資料驗證下拉式選單範例：辦公室飲料表單，對於飲料來說：大小/冰/糖，是最不會變的，所以我們可以把這三欄做成下拉式表單讓大家選取，詳細可以參考：辦公室飲料表單教學跟範本 2.版本回復、編輯記錄查看：是誰把我的資料刪掉了？ 在共同編輯的環境下，我們很怕有人不小心刪掉重要的資訊，導致工作表異常GoogleSheet提供版本回復功能：直接回復到某個時間點的檔案如何做版本回復？點選編輯最後時間，進行版本回復GoogleSheet也提供編輯紀錄查看：每一格的編輯紀錄都可以查看，包括誰進行改動？跟改動的內容是什麼？如何查看編輯紀錄？點按右鍵→編輯紀錄 3.GoogleSheet自動作圖 方法一：除了可以將GoogleSheet的資料，自己以插入→圖表做圖方法二：GoogleSheet右下角的探索功能：GoogleSheet會建議你最適合的圖，點選之後，就自動作好圖了！ 4.文字分隔：將文字分隔成不同欄 方法一：如果有一長串字由文字、空格、或是符號組成，可以在：資料→將文字分隔成不同欄，做資料分割方法二：如果想要用公式完成，可以用SPLIT()、RIGHT()、LEFT()這類的分割函數 5.讓其他人直接建立副本 只要將GoogleSheet中後面edit以後的刪掉，改成copy，就可以變成直接建立副本的分享模式 6.保護工作表：部分不給其他人編輯 如何保護工作表使用？資料→保護工作表和範圍可以保護到什麼程度？可以設定一個範圍(一個工作表、或是工作表裡的某個範圍)有哪些人可以擁有權限編輯也可以設定所有人都沒有權限編輯我通常會在，這個工作表需要大家一起共同編輯，但是規則、範例不希望其他人動到的時候，會去使用「保護工作表」的功能 不用GoogleSheetAPI就能用的外掛 點擴充功能中的外掛程式，裡面有非常多第三方製作好的公式、工具可以用！以下會介紹幾個很實用、而且完全免費的隱藏外掛跟公式！ 1.自動生成短連結：PICSEE_UTM() 如何取得？擴充功能→外掛程式→取得外掛程式→PICSEE如何使用？PICSEE_UTM()：可以自行輸入utm後產生短連結PICSEE_CLICK()：可以即時的查詢各個短網址所帶來的點擊成效如何除了產生短連結，你還需要「批次」產生短連結或是還原短連結，可以參考：GoogleSheet批次產短網址、產生與還原超連結 2.虛擬貨幣追蹤：CRYPTOFINANCE() 除了用GOOGLEFINANCE()查詢台股、美股報價外，可以用CRYPTOFINANCE()追蹤虛擬貨幣的報價 3.自動寄送Email：YAMM、Mergommailmerge 最新的個人Gmail一天可以發500封個人化設定的Email：外掛程式->取得外掛程式->Mergommailmerge，可以客製化訊息、可以設計電子報(GoogleDoc設計)、可以觀察開啟率一天可以發送100封，可以使用CreateamailmergeusingGmailandGoogleSheets，或是你也可以點此複製一份模板來使用YAMM：YAMM外掛免費版一天只能發50封，但是也是滿容易使用的，還可以知道對方有沒有打開信，確認開啟率 4.甘特圖範例 如何取得？擴充功能→外掛程式→取得外掛程式→GanttChart擴充功能→外掛程式→取得外掛程式→ProjectSheetPlanning這裡有GanttChart：教學影片 5.串接GA(GoogleAnalytics)或是GoogleSearchConsole 外掛如何取得？擴充功能→外掛程式→取得外掛程式→GoogleAnalytics為什麼要用這個外掛？GA不就可以分析數據了嗎？如果需要跟公司同事一起分享GA成效，不一定要開啟GA權限給他們，可以直接串接外掛程式後，用Google試算表製作簡單報表，分享給其他員工或是同事 6.GoogleSheet爬蟲工具：Instagram數據分析工具 工具說明：他可以幫我搜索instagram上的搜索結果，例如說我想知道Top100「美食」的搜索結果，他會幫我把帳號、追蹤人數等等都放到GoogleSheet上，讓我方便的去查看搜索結果 7.方程式求最佳解：NLSOLVE() 算出售價以及折扣之間的最佳解：之前有寫過一篇，行銷折扣分析的文章，用GoogleSheetNLSOLVE計算機我可以算出不同折扣門檻下，我的折扣率要設在多少以下才可以確保我賺的利潤是一樣的 總結 以上分享的公式、技巧跟外掛都是不需要特別學會程式，一般人就能使用的實用功能這些功能，基本上在Excel中不太好找，或是找不到，因此GoogleSheet真的非常適合一般人或是上班族們學習 如何練習GoogleSheet實用公式、技巧、外掛？ 複製一份我的GoogleSheet教學文件在我整理好的工作表中，試用看看這些功能 更多學習資源推薦 Google官方教學：GettingStartedwithGoogleSheetsLisa的超白話實用教學：Lisa的GoogleSheet公式教學GoogleDataAnalyticsProfessionalCertificate：Google認證的資料分析師證照課程有幫助嗎？誰最適合上？可以參考我的學習心得，證照班適合想要全方位學習數據分析工具跟技巧的人！ 如何使用這個網站？我的數據分析文章導覽！裡面也會分享更多實用GoogleSheet教學：本站數據分析文章導覽及更多GoogleSheet實用文章教學 本站數據分析文章導覽 【1對1免費諮詢會議】數據分析師轉職培訓班 hi我是Lisa 你也是0經驗、沒有相關背景，但也想要轉職數據分析師，或運用數據分析，提升在領域的專業度嗎？ - 你是否也覺得，數據分析的網路學習資源很多，但是多到不知道從哪裡開始？ 學了很多工具，但學完了，問題才真正開始，不知道業界怎麼使用？又該如何面臨工作跟面試？ - 我在2019年從營運專員的職位轉職到數據分析師 在這之後，因為數據分析，也讓我快速建構不同領域的知識：行銷、營運、商業策略、產品、網頁 轉職過程中，花了很多時間成本摸索 我發現，學數據分析最快的方式是： 確認自己跟市場需求之間的差距：訂定轉職策略及了解數據分析師的工作方式以業界應用的角度學習工具：包括數據工具及分析用思維架構，並且如何高效運用建立一個商業上的真實專案，並且得到回饋：打造屬於你及貼合職缺需求的作品集和專案打造一個高效履歷跟面談：透過高價值包裝術拿下面試、成功獲得數據行業的門票 當初如果我有這套系統思維，應該可以在短時間內轉職，馬上拿到進入業界的門票 - 現在，我想把這套完整課程分享給你 這是專門為了0經驗、沒有相關背景的轉職者所設計 不需要去考研究所、不需要花大錢準備出國讀書 可以用最快的時間準備轉職、拿到門票！ - 目前早鳥封測方案已經結束銷售啦！ 如果你正在準備轉職數據分析師，且試圖找過解決辦法卻始終治標不治本！那歡迎填底下的這個表單，在開始免費1對1諮詢時、第一批正式招生時，馬上通知你！ PleaseShareThis Sharethiscontent Opensinanewwindow Opensinanewwindow Opensinanewwindow Opensinanewwindow Opensinanewwindow Opensinanewwindow Opensinanewwindow Opensinanewwindow Opensinanewwindow Opensinanewwindow Opensinanewwindow LisaWu Hi大家好！我是喜歡探索身心靈健康及平衡的數據分析師LisaWu！這個網站會幫助你：利用數據分析(理性)及身心靈平衡(感性)的系統，去達成人生目標！如果你對我的人生系統工具有興趣，歡迎聯繫我：[email protected] YouMightAlsoLike [數據分析#17]數據分析職缺整理：營運分析、商業分析、專案分析、行銷分析在做什麼？ 206月,2021 [數據分析#7]股票投報率計算機免費下載(再教你算投資組合有沒有贏過被動投資) 1612月,2020 ThisPostHas3Comments Pingback:[數據分析#0]數據分析文章導覽-LisaWu財富自由之路 Pingback:[數據分析#38]Google試算表：一次學完12個基礎功能，一起早點下班！-LisaWu財富自由之路 Pingback:[數據分析#39]Google試算表：圖表怎麼做？5大常用圖手把手教學-LisaWu財富自由之路 Commentsareclosed. AboutMe Lisa LisaWu，熱愛效率學習、追求靈魂、時間跟熱量自由。

喜歡簡單的靈性生活、講究工作效率的數據分析，還有越來越多讓我感到滿足跟快樂的興趣，如果有合作需求，歡迎聯繫我:) ⚠️免責聲明如果你決定透過我的聯盟連結去購買，我將獲得少量佣金資助，讓我可以持續創作、持續營運網站的同時，也並不影響你購買的價格，甚至是更便宜。

這裡不會有任何炒作或是虛假的產品評價，好用我就會說好用，不好用我也會說明我的立場跟原因，我推薦的所有工具與資源都是我本身正在使用或是試用過的，且確實提供我幫助的！   插入/編輯連結 關閉 請輸入目標網址 網址 連結文字 在新分頁中開啟連結 或連結到現有的內容 搜尋 尚未指定搜尋詞彙。

以下顯示最近發佈的項目。

搜尋或使用向上/向下鍵以選取項目。

取消