方格精選

IMPORTRANGE 的進階應用(二):結合 QUERY 篩選資料

更新於 發佈於 閱讀時間約 10 分鐘
什麼,IMPORTRANGE 的進階應用還有第二招!
上次我們學到了怎麼用下拉式選單,讓 IMPORTRANGE 切換到不同的目標來匯入資料(還沒看過的話,快來這邊);這次呢,我想把強大的 QUERY 跟 IMPORTRANGE 結合在一起,讓你可以即時處理匯入進來試算表的最新資料,包含選取特定欄位、依特定條件篩選資料、對資料進行加總彙整等等功能!
雖然我不是數學家,但這樣處理資料聽起來很不錯,對吧 (⁰▿⁰)

先來回顧一下

IMPORTRANGE 可以幫你即時更新試算表的資訊,語法也很單純:
=IMPORTRANGE(試算表連結, 要匯入的範圍)
比如說:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1MzLjKDb0XVjk_R6Ij84jFFl7z8KNJjJckUGi42Mwr6U", "貓!A:N")
想要完整複習一下 IMPORTRANGE 的用法,歡迎到我之前的教學:IMPORTRANGE,即時同步 Google 試算表的資料! 來看看唷。
不過 IMPORTRANGE 只會把資料匯進來,沒辦法讓我們操作像是加總、調換欄位位置、排序、篩選等處理,這時候該怎麼辦呢?
放心,我們就可以用 QUERY 來為我們代勞,直接利用 QUERY 達成剛剛提到的操作!把 QUERY 跟 IMPORTRANGE 結合在一起,就可以讓 QUERY 讀取 IMPORTRANGE 的資料,再去做之後的處理。
當然,如果你覺得這樣寫太複雜,你也可以先 IMPORTRANGE 到一個工作表,之後再開另外一個工作表使用 QUERY 再處理剛匯入進來的資料,這樣分開處理也沒問題。
QUERY 是什麼?QUERY 是一個可以對大量資料查詢特定資訊,依據特定篩選條件快速回傳儲存格、欄、範圍的函式。歡迎你到過去的文章複習一下唷:
注意事項:
  1. 如果試算表有太多 IMPORTRANGE、或是 IMPORTRANGE 匯入範圍太大,也可能會拖累到 Google 試算表的效能,所以可以斟酌一下 IMPORTRANGE 的使用時機。
  2. 如果你有把大型的資料庫放到試算表上運算或整理的需求,可以考慮用資料連結器連到 BigQuery、再連結到試算表。

怎麼做呢?

把 QUERY 跟 IMPORTRANGE 合體就行了!像這樣:
=QUERY(IMPORTRANGE(試算表連結, 要匯入的範圍), "QUERY 指令")
這麼一來 QUERY 就會讀取 IMPORTRANGE 匯入進來的資料,再透過 QUERY 指令來處理處理囉。
使用方式上只有一件事情要注意,那就是欄位的寫法
在寫 QUERY 的時候,我們會用試算表的欄位字母(A、B、C⋯⋯)、像是:
=QUERY('資料'!A:E, "SELECT A, B, C ...")
但是如果要用到 IMPORTRANGE,就得換個寫法:
=QUERY(IMPORTRANGE(網址, 範圍), "SELECT Col1, Col2, Col3 ...")
你會看到這邊有個新東西叫「Col1」,這個代表 IMPORTRANGE 範圍的第一欄。假如你的 IMPORTRANGE 範圍是 A:F,那麼:
  • Col1 就是 A 欄
  • Col2 就是 B 欄
  • Col3 就是 C 欄
  • ...
又或是如果你的 IMPORTRANGE 範圍是 B:G,那麼
  • Col1 就是 B 欄
  • Col2 就是 C 欄
  • Col3 就是 D 欄
  • ...
另外,Col 的寫法有大小寫之分,C 一定要大寫、ol 也一定要小寫唷!
不過要用數字來表示欄位有點違反人性,所以我這邊附上了字母跟數字的對應表給大家參考(假設 A 欄是第一欄):
那我們來一起動手做吧!(*´ω`)人(´ω`*)

練習時間

請先在你自己的雲端硬碟那邊新增一個試算表,用 IMPORTRANGE,在 A1 輸入:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1MzLjKDb0XVjk_R6Ij84jFFl7z8KNJjJckUGi42Mwr6U/edit#gid=978955641", "貓!A:N")
如果出現錯誤,需要允許存取的話,按下藍色按鈕就可以囉。執行後應該會看到這樣的畫面:

SELECT 特定欄位

我們假如只想要抽取登錄 ID(A)、動物種類(B)、動物品種(C)、動物性別(D)、動物相片網址(I)、收容所名稱(K)、收容所地址(L)、收容所電話(M)的資料,那就可以用 QUERY 的 SELECT 選取上面指定的欄位。像是這樣(記得要換成正確的數字喔!):
=QUERY(
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1MzLjKDb0XVjk_R6Ij84jFFl7z8KNJjJckUGi42Mwr6U/edit#gid=978955641", "貓!A:N"),
"SELECT Col1, Col2, Col3, Col4, Col9, Col11, Col12, Col13")
結果如下:
如果 IMPORTRANGE 的資料欄位順序讓我們不方便分析的話,我們也會用 QUERY 來調換順序。例如,我就可以把原本在 I 欄的「動物相片網址」,利用 QUERY 移到最前面:
=QUERY(
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1MzLjKDb0XVjk_R6Ij84jFFl7z8KNJjJckUGi42Mwr6U/edit#gid=978955641", "貓!A:N"),
"SELECT Col9, Col1, Col2, Col3, Col4, Col11, Col12, Col13")

SELECT + WHERE

我們如果想找收容所地址位於「桃園市」的資訊,就可以用 QUERY 的 WHERE 來篩選,像是「starts with '桃園市'」就會是個好方法。
假設我們要搜尋的欄位不動,那就在 SELECT 後面加上 WHERE 字句就可以了,像是這樣:
=QUERY(
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1MzLjKDb0XVjk_R6Ij84jFFl7z8KNJjJckUGi42Mwr6U/edit#gid=978955641", "貓!A:N"),
"SELECT Col1, Col2, Col3, Col4, Col9, Col11, Col12, Col13
 WHERE Col11 starts with '桃園市'")
這邊當然也要記得換上 Col11,代表原本資料的第 11 欄的位置。執行結果就像這樣:

SELECT + GROUP BY

再來最後一個題目!假如我們想得到資料中所有貓咪的數量、並且用動物性別來分的話,該怎麼辦呢?我們可以用 GROUP BY 來操作:
=QUERY(
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1MzLjKDb0XVjk_R6Ij84jFFl7z8KNJjJckUGi42Mwr6U/edit#gid=978955641", "貓!A:N"),
"SELECT Col4, Count(Col1)
 GROUP BY Col4")
當然,如果你的資料有數字匯總的需求,之前提過的 AVG、SUM、MAX、MIN 也可以搭配 GROUP BY 用上,把欄位字母替代好就好囉!
你如果已經看過我其他介紹過 QUERY 的教學,應該也知道 QUERY 其實有很多可能性。今後你也可以再用 ORDER BY 排序、LIMIT 限制資料筆數、LABEL 更換標題或是其他我們之前學過的 QUERY 指令做出更細緻的資料呈現。

結論

所以你會發現其實操作跟一般的 QUERY 差不多,唯一要注意的就是欄位字母要換成 Col 的形式而已!只要簡單地把 IMPORTRANGE 跟 QUERY 結合,分析的可能性就會多很多,歡迎試試看 ᕕ ( ᐛ ) ᕗ

如果你喜歡這次的文章,歡迎你透過這些方法支持我:
・按下愛心、按下儲存
・留言告訴我你的想法
・加入喜特先生的官方沙龍,即時看到我發布的教學
・付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
・追蹤喜特先生的 Facebook
・按這邊小額贊助我的創作!
想要看更多文章,歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔!
我是喜特先生,Mr. Sheet,我們下個教學見!
avatar-img
14.9K會員
149內容數
簡潔,快速,有效, 讓你的日常生活、工作生產力大提升! ___ 快按「加入」,馬上追蹤所有喜特先生的更新,有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱,還有 Google 試算表疑難雜症的解題分享唷!💪
留言0
查看全部
avatar-img
發表第一個留言支持創作者!
喜特先生官方沙龍 的其他內容
在今天的這篇教學,我想列舉五種試算表變慢的可能原因、還有相對應的解決方案!如果你遇到這樣的問題,或許這篇教學可以幫你從龜速深淵中拉出來,邁向健康順暢的喜特人生!
有個可以在 Google 試算表上做單位換算的函式,叫做「CONVERT」,來看看到底怎麼快速轉換單位、免手算吧!
如果你 WHERE 的條件需要常常更動,但每次都要進去 QUERY 所在的儲存格編輯語法,實在是有點太麻煩了?但其實有個小技巧,可以讓 QUERY 更新得更輕鬆。來看看吧!
連 Google QUERY 官方文件都沒寫的秘密,在這邊公開啦!如果你想提升處理大型資料庫的清理或分析效率,歡迎來參考「SKIPPING」!
日幣大貶,在想著是不是該換一下手上的新台幣了 ( ´・◡・`) ? GOOGLEFINANCE 帶你找到當日的匯率,還有更多功能等你發現唷!
不知不覺寫到第七篇了!QUERY 真的有好多好多東西可以說 (ノ>ω<)ノ QUERY 其實還能處理有日期、時間的資料,而且語法也相當容易,和我們之前就看過的聚集函式很像。你如果會了之前的聚集函式,相信這次處理日期和時間也會對你來說很簡單!
在今天的這篇教學,我想列舉五種試算表變慢的可能原因、還有相對應的解決方案!如果你遇到這樣的問題,或許這篇教學可以幫你從龜速深淵中拉出來,邁向健康順暢的喜特人生!
有個可以在 Google 試算表上做單位換算的函式,叫做「CONVERT」,來看看到底怎麼快速轉換單位、免手算吧!
如果你 WHERE 的條件需要常常更動,但每次都要進去 QUERY 所在的儲存格編輯語法,實在是有點太麻煩了?但其實有個小技巧,可以讓 QUERY 更新得更輕鬆。來看看吧!
連 Google QUERY 官方文件都沒寫的秘密,在這邊公開啦!如果你想提升處理大型資料庫的清理或分析效率,歡迎來參考「SKIPPING」!
日幣大貶,在想著是不是該換一下手上的新台幣了 ( ´・◡・`) ? GOOGLEFINANCE 帶你找到當日的匯率,還有更多功能等你發現唷!
不知不覺寫到第七篇了!QUERY 真的有好多好多東西可以說 (ノ>ω<)ノ QUERY 其實還能處理有日期、時間的資料,而且語法也相當容易,和我們之前就看過的聚集函式很像。你如果會了之前的聚集函式,相信這次處理日期和時間也會對你來說很簡單!
你可能也想看
Google News 追蹤
Thumbnail
/ 大家現在出門買東西還會帶錢包嗎 鴨鴨發現自己好像快一個禮拜沒帶錢包出門 還是可以天天買滿買好回家(? 因此為了記錄手機消費跟各種紅利優惠 鴨鴨都會特別注意銀行的App好不好用! 像是介面設計就是會很在意的地方 很多銀行通常會為了要滿足不同客群 會推出很多App讓使用者下載 每次
Thumbnail
商業簡報不僅僅是呈現數據,更需要深入瞭解數據分析及有效的工具運用。本文探討於Excel中使用不同函數來改善數據處理效率,包括IF、IFS、VLOOKUP、XLOOKUP及INDEX與MATCH的結合,幫助商業人士更好地從數據中提取洞見,助力業務增值,學習優化數據分析過程,讓您的商業簡報更具影響力。
Thumbnail
※ 把record加到table有兩種方式: VALUES • SELECT ※ 語法 INSERT INTO VALUES 語法: Record 代表一組值的集合,每個值對應到表格中的一個欄位(column)。 INSERT INTO 語法用來指定要插入資料的表格。 需要提供一個
Thumbnail
條件資料行是POWER QUERY裡面一個可以設定指定條件,進而判斷符合條件的資料產生指定的內容,就跟EXCEL中的IF函數很像。 但是POWER QUERY的條件資料行,他是利用對話式的視窗,所以不需要自己動手寫函數,就可以順利達到相同的功能。 今天就用1個資料3個題目來學習這個功能
Thumbnail
網友提出的一個問題,如影片。 當輸入關鍵字+數量,例:起司+10 下拉式選單自動產生有關起司的產品的清單供選擇並且帶出規格、數量、金額與小計 《為什麼要做這個功能呢?》 當資料很多的時候,如果每筆資料都是用篩選的方式來找出想要的產品,可能會耗掉非常多的時間。 所以如果可以藉由關鍵字,
如何在SQL實踐中EXCEL 常用功能 篩選 和 擷取文字串?需要熟練地使用分組(GROUP BY) 與 排序 (ORDER BY) 以及SUBSTRING_INDEX函數!
Thumbnail
本文介紹如何使用 萬金油 做出輔助列,以達成下拉選單的要求。將詳細討論如何處理資料範圍變動、萬金油公式和快速新增名稱的技巧。此外,也分享了三個參考影片以供學習。
Thumbnail
在POWER QUERY從0到1 #6,就有介紹過資料合併這個功能。 #6 從0到1的POWER QUERY 資料合併 神似VLOOKUP但比他好用100倍 資料合併很神似函數的VLOOKUP,但除了單純以VLOOKUP方式查找合併資料之外,總共有6種不同的合併方式。 用一個簡單的範例來做
Thumbnail
在POWER QUERY從0到1 #9 樞紐資料行的功能是將長資料轉換成寬資料,使數據可以快速分析。 而所謂的取消資料行樞紐,就是把寬資料轉換成長資料的一個過程,也就是資料的正規化。 如下圖所示,左邊的圖為二維結構,屬於寬資料,每列可能包含多筆數據(1.2.3月),右邊的圖屬於長資料,每列都
Thumbnail
POWER QUERY樞紐資料行這個功能其實跟EXCEL的樞紐相似度大約90%,但是使用方式有點不太相同。 那樞紐到底是什麼東東呢? 其實樞紐就是將一維表轉成二維表,或者有人說將長資料轉成寬資料,那什麼是長資料什麼是寬資料呢? 長資料 資料中不論有多少欄,每一列只有一筆數據,長資
Thumbnail
※ 基本操作:SQL 語法,SELECT, WHERE, CREATE, UPDATE, DELETE。 SELECT:從資料庫中或資料表中指定要選擇的欄位中取得資料,稱之為查詢 (query)。 ※ 語法:要由兩部分構成,第一部分是要 "拿什麼" 資料 (若有多項用逗號隔開);第二部分則為
Thumbnail
/ 大家現在出門買東西還會帶錢包嗎 鴨鴨發現自己好像快一個禮拜沒帶錢包出門 還是可以天天買滿買好回家(? 因此為了記錄手機消費跟各種紅利優惠 鴨鴨都會特別注意銀行的App好不好用! 像是介面設計就是會很在意的地方 很多銀行通常會為了要滿足不同客群 會推出很多App讓使用者下載 每次
Thumbnail
商業簡報不僅僅是呈現數據,更需要深入瞭解數據分析及有效的工具運用。本文探討於Excel中使用不同函數來改善數據處理效率,包括IF、IFS、VLOOKUP、XLOOKUP及INDEX與MATCH的結合,幫助商業人士更好地從數據中提取洞見,助力業務增值,學習優化數據分析過程,讓您的商業簡報更具影響力。
Thumbnail
※ 把record加到table有兩種方式: VALUES • SELECT ※ 語法 INSERT INTO VALUES 語法: Record 代表一組值的集合,每個值對應到表格中的一個欄位(column)。 INSERT INTO 語法用來指定要插入資料的表格。 需要提供一個
Thumbnail
條件資料行是POWER QUERY裡面一個可以設定指定條件,進而判斷符合條件的資料產生指定的內容,就跟EXCEL中的IF函數很像。 但是POWER QUERY的條件資料行,他是利用對話式的視窗,所以不需要自己動手寫函數,就可以順利達到相同的功能。 今天就用1個資料3個題目來學習這個功能
Thumbnail
網友提出的一個問題,如影片。 當輸入關鍵字+數量,例:起司+10 下拉式選單自動產生有關起司的產品的清單供選擇並且帶出規格、數量、金額與小計 《為什麼要做這個功能呢?》 當資料很多的時候,如果每筆資料都是用篩選的方式來找出想要的產品,可能會耗掉非常多的時間。 所以如果可以藉由關鍵字,
如何在SQL實踐中EXCEL 常用功能 篩選 和 擷取文字串?需要熟練地使用分組(GROUP BY) 與 排序 (ORDER BY) 以及SUBSTRING_INDEX函數!
Thumbnail
本文介紹如何使用 萬金油 做出輔助列,以達成下拉選單的要求。將詳細討論如何處理資料範圍變動、萬金油公式和快速新增名稱的技巧。此外,也分享了三個參考影片以供學習。
Thumbnail
在POWER QUERY從0到1 #6,就有介紹過資料合併這個功能。 #6 從0到1的POWER QUERY 資料合併 神似VLOOKUP但比他好用100倍 資料合併很神似函數的VLOOKUP,但除了單純以VLOOKUP方式查找合併資料之外,總共有6種不同的合併方式。 用一個簡單的範例來做
Thumbnail
在POWER QUERY從0到1 #9 樞紐資料行的功能是將長資料轉換成寬資料,使數據可以快速分析。 而所謂的取消資料行樞紐,就是把寬資料轉換成長資料的一個過程,也就是資料的正規化。 如下圖所示,左邊的圖為二維結構,屬於寬資料,每列可能包含多筆數據(1.2.3月),右邊的圖屬於長資料,每列都
Thumbnail
POWER QUERY樞紐資料行這個功能其實跟EXCEL的樞紐相似度大約90%,但是使用方式有點不太相同。 那樞紐到底是什麼東東呢? 其實樞紐就是將一維表轉成二維表,或者有人說將長資料轉成寬資料,那什麼是長資料什麼是寬資料呢? 長資料 資料中不論有多少欄,每一列只有一筆數據,長資
Thumbnail
※ 基本操作:SQL 語法,SELECT, WHERE, CREATE, UPDATE, DELETE。 SELECT:從資料庫中或資料表中指定要選擇的欄位中取得資料,稱之為查詢 (query)。 ※ 語法:要由兩部分構成,第一部分是要 "拿什麼" 資料 (若有多項用逗號隔開);第二部分則為