QUERY 函式大解析(十三):MATCHES「|」用 TEXTJOIN 與範圍連動

更新於 發佈於 閱讀時間約 9 分鐘

這邊有個台灣的旅宿清單,有各個旅宿的資訊:

我想用 QUERY 找位於「高雄市、臺南市、屏東縣、臺東縣」的旅宿資訊。如果用 OR 來寫:

=QUERY(..., "SELECT ... WHERE A = '高雄市'
OR A = '臺南市'
OR A = '屏東縣'
OR A = '臺東縣'")

用上次介紹的 MATCHES|」來寫:

=QUERY(..., "SELECT ... WHERE A MATCHES '高雄市|臺南市|屏東縣|臺東縣'")


那麽如果我們希望 QUERY 能跟範圍內的資料連動,又要怎麼做呢?

像是下面這邊,我們想用 QUERY 找位於 G 欄的「高雄市、臺南市、屏東縣、臺東縣」旅宿資訊:

raw-image

要怎麼寫呢?如果要按照之前介紹過的 QUERY 函式大解析(九):與儲存格連動,用「&」串連的話:

=QUERY(..., "SELECT ... WHERE A = '" & G2 & "'
OR A = '" & G3 & "'
OR A = '" & G4 & "'
OR A = '" & G5 & "'")

但是這方法得一個個指定儲存格,且如果日後清單再繼續增加或減少的話,就得再編輯 OR,這方法有點不太理想。

那怎麼寫更好呢?可以用 WHEREMATCHES 搭配 TEXTJOIN 函式來寫。

今天來分享 MATCHES|」跟 TEXTJOIN 的另一個應用,讓你的 QUERY 可以更彈性,條件是一個範圍的資料也能迎刃而解。

歡迎來這邊複製一份練習用的試算表,一起來做做看!先打開「練習」工作表,你會看到 A 到 E 欄是等等要 QUERY 的地方, G 欄有縣市清單:

raw-image

這次會提到三個之前介紹過的概念,如果想要複習的話,也歡迎來這看看:




思路

我們的目標是做出這樣的 QUERY

=QUERY('台灣旅宿網旅宿清單'!A:L,
"SELECT C, A, B, K, L
WHERE A MATCHES '高雄市|臺南市|屏東縣|臺東縣'")

來看看那個 WHERE我們要試著把 MATCHES 後面的那串跟縣市清單連在一起,讓它成為「高雄市|臺南市|屏東縣|臺東縣」這樣的正規表達式咒語

怎麼做呢?先說結論,有兩個步驟:

  1. TEXTJOIN 函式,把縣市清單內的儲存格以「|」為連結字元串在一起,形成一個像是「a|b|c|d|...」的文字,即是正規表達式的「咒語」。
  2. & 字元與第一個步驟的結果連接,把它跟 MATCHES 接上。

這樣就可以讓 QUERY 跟範圍連動在一起了。


第一步:TEXTJOIN 函式

我們先在 H2 寫 TEXTJOIN 函式,把縣市清單串在一起看看吧。輸入:

=TEXTJOIN("|", TRUE, G2:G)
  • 串連字元:|
  • 忽略空白:是
  • 範圍:G2 到 G(整個 G2 以下的範圍)
raw-image

結果就順利顯示了我們要的正規表達式「咒語」:

raw-image

你可以在 G 欄的清單這邊加入新的縣市,看看 H2 的結果會不會跟著改變。我這邊加入了「花蓮縣」,H2 ​成功跟著連動了:

raw-image



第二步:用 & 字元跟 TEXTJOIN 結果連接,把它跟 MATCHES 接上

好,既然要讓 QUERY 可以跟儲存格互動,要出動 & 啦!我們在 A2,按照題目需求先完成前半段的 QUERY

=QUERY('台灣旅宿網旅宿清單'!A:L,
"SELECT C, A, B, K, L
WHERE A MATCHES
raw-image


因為等等要和 H2 的文字連結,我們要先起一個單引號「'」:

=QUERY('台灣旅宿網旅宿清單'!A:L,
"SELECT C, A, B, K, L
WHERE A MATCHES '

再來用雙引號「"」暫時先把目前的 QUERY 指令先關起來:

=QUERY('台灣旅宿網旅宿清單'!A:L,
"SELECT C, A, B, K, L
WHERE A MATCHES '"

然後我們用 & 字元,串連 H2:

=QUERY('台灣旅宿網旅宿清單'!A:L,
"SELECT C, A, B, K, L
WHERE A MATCHES '" & H2

再來寫第二個 &、準備繼續連接 QUERY 指令:

=QUERY('台灣旅宿網旅宿清單'!A:L,
"SELECT C, A, B, K, L
WHERE A MATCHES '" & H2 &

接著是雙引號「"」,準備把剩下的 QUERY 寫完:

=QUERY('台灣旅宿網旅宿清單'!A:L,
"SELECT C, A, B, K, L
WHERE A MATCHES '" & H2 & "

寫單引號「'」,讓 MATCHES 結束。

=QUERY('台灣旅宿網旅宿清單'!A:L,
"SELECT C, A, B, K, L
WHERE A MATCHES '" & H2 & "'

接下來我們還可以繼續寫其他的 QUERY 語句(ORDER BYLIMIT 等),舉例來說,按照 A 欄位做排序後再按 B 欄位排序:

=QUERY('台灣旅宿網旅宿清單'!A:L,
"SELECT C, A, B, K, L
WHERE A MATCHES '" & H2 & "'
ORDER BY A, B")

如果沒打算繼續寫的話,你可以寫上雙引號「"」讓 QUERY 語句結束:

=QUERY('台灣旅宿網旅宿清單'!A:L,
"SELECT C, A, B, K, L
WHERE A MATCHES '" & H2 & "'")

颯爽按下 Enter 鍵,看看結果:

raw-image

成功啦!你可以自由測試,把清單改成不同縣市,看看執行結果怎麼樣:

raw-image

當然你也可以一次把第一步跟第二步寫好,像這樣:

=QUERY('台灣旅宿網旅宿清單'!A:L,
"SELECT C, A, B, K, L
WHERE A MATCHES '" & TEXTJOIN("|", TRUE, G2:G) & "'")

結果也完全一樣的唷!


你可以考慮用這招,讓 TEXTJOIN 函式去取動態更新的範圍(像是 IMPORTRANGEQUERYFILTER 等),可以讓你的 QUERY 更有彈性喔!希望這招對你有幫助。



如果你喜歡這次的文章,歡迎你透過這些方法支持我:

  • 按下愛心、按下儲存
  • 留言告訴我你的想法
  • 加入喜特先生的官方沙龍,即時看到我發布的教學
  • 付費訂閱喜特先生的官方沙龍,加入每月小額訂閱方案
  • 追蹤喜特先生的 Facebook
  • 這邊小額贊助我的創作!

想要看更多文章的話,歡迎來到我的 Notion 頁面找找有沒有你需要的資源喔!

我是喜特先生,Mr. Sheet,我們下個教學見!


avatar-img
14.7K會員
148內容數
簡潔,快速,有效, 讓你的日常生活、工作生產力大提升! ___ 快按「加入」,馬上追蹤所有喜特先生的更新,有 Google 試算表教學、Google Apps Script 的研究、數據分析課程的開箱,還有 Google 試算表疑難雜症的解題分享唷!💪
留言0
查看全部
avatar-img
發表第一個留言支持創作者!
喜特先生官方沙龍 的其他內容
在用 QUERY 查詢資料時,你曾遇過在 WHERE 寫很多個 OR 的狀況嗎?有個更簡單好用的寫法推薦給你,來瞧瞧!
這是「按條件算OO」系列文的第二篇教學!今天會來聊聊 AVERAGEIF 和 AVERAGEIFS。
這邊統整了所有過去發表過關於 QUERY 函式的教學分享,希望可以方便你按照順序閱讀和練習。 QUERY 可以用來查詢、篩選、聚集、排序資料,還可以做張簡易的資料透視表,是我在 Google 試算表上做數據分析、製作報告、製作儀表板時最常用的函式之一,既方便又好用,誠心推薦!
一起來看看怎麼做 MIN 吧!這篇教學是喜特先生為 Google 試算表新手而寫的「聚集函式御五家」系列文之一,會分享在 Google 試算表上簡單又實用的五種函式 SUM、AVERAGE、COUNT 與 COUNTA、MAX、 MIN ,每篇都會解釋語法、並且附上一個簡單的例子讓你可以一起練習。
來看看怎麼用 MAX 吧!這篇教學是喜特先生為 Google 試算表新手而寫的「聚集函式御五家」系列文之一,會分享在 Google 試算表上簡單又實用的五種函式 SUM、AVERAGE、COUNT 與 COUNTA、MAX、 MIN ,每篇都會解釋語法、並且附上一個簡單的例子讓你可以一起練習。
一起看看 COUNT 跟 COUNTA 如何操作吧!這篇教學是喜特先生為 Google 試算表新手而寫的「聚集函式御五家」系列文之一,每篇都會解釋語法、並且附上一個簡單的例子讓你可以一起練習。
在用 QUERY 查詢資料時,你曾遇過在 WHERE 寫很多個 OR 的狀況嗎?有個更簡單好用的寫法推薦給你,來瞧瞧!
這是「按條件算OO」系列文的第二篇教學!今天會來聊聊 AVERAGEIF 和 AVERAGEIFS。
這邊統整了所有過去發表過關於 QUERY 函式的教學分享,希望可以方便你按照順序閱讀和練習。 QUERY 可以用來查詢、篩選、聚集、排序資料,還可以做張簡易的資料透視表,是我在 Google 試算表上做數據分析、製作報告、製作儀表板時最常用的函式之一,既方便又好用,誠心推薦!
一起來看看怎麼做 MIN 吧!這篇教學是喜特先生為 Google 試算表新手而寫的「聚集函式御五家」系列文之一,會分享在 Google 試算表上簡單又實用的五種函式 SUM、AVERAGE、COUNT 與 COUNTA、MAX、 MIN ,每篇都會解釋語法、並且附上一個簡單的例子讓你可以一起練習。
來看看怎麼用 MAX 吧!這篇教學是喜特先生為 Google 試算表新手而寫的「聚集函式御五家」系列文之一,會分享在 Google 試算表上簡單又實用的五種函式 SUM、AVERAGE、COUNT 與 COUNTA、MAX、 MIN ,每篇都會解釋語法、並且附上一個簡單的例子讓你可以一起練習。
一起看看 COUNT 跟 COUNTA 如何操作吧!這篇教學是喜特先生為 Google 試算表新手而寫的「聚集函式御五家」系列文之一,每篇都會解釋語法、並且附上一個簡單的例子讓你可以一起練習。
你可能也想看
Google News 追蹤
Thumbnail
大家好,我是woody,是一名料理創作者,非常努力地在嘗試將複雜的料理簡單化,讓大家也可以體驗到料理的樂趣而我也非常享受料理的過程,今天想跟大家聊聊,除了料理本身,料理創作背後的成本。
Thumbnail
哈囉~很久沒跟各位自我介紹一下了~ 大家好~我是爺恩 我是一名圖文插畫家,有追蹤我一段時間的應該有發現爺恩這個品牌經營了好像.....快五年了(汗)時間過得真快!隨著時間過去,創作這件事好像變得更忙碌了,也很開心跟很多厲害的創作者以及廠商互相合作幫忙,還有最重要的是大家的支持與陪伴🥹。  
Thumbnail
嘿,大家新年快樂~ 新年大家都在做什麼呢? 跨年夜的我趕工製作某個外包設計案,在工作告一段落時趕上倒數。 然後和兩個小孩過了一個忙亂的元旦。在深夜時刻,看到朋友傳來的解籤網站,興致勃勃熬夜體驗了一下,覺得非常好玩,或許有人玩過了,但還是想寫上來分享紀錄一下~
Thumbnail
※ 何時該使用 JOIN? JOIN 使用的時機是:當你需要同時查詢一張以上的資料表的時候。 ※ SQL有哪些TABLE JOIN的方式? INNER JOIN LEFT JOIN RIGHT JOIN SELF JOIN ※ 使用 JOIN 的時候,我們需要考慮到: 我要使用哪一種
Thumbnail
※ 什麼是WHERE? 使用 WHERE來設定條件,可以幫助我們縮小查詢結果的範圍,取得想要的結果。 ※ 語法: ※ 解析順序: From:先看是哪一張table→table裡面符合Where指定條件的record→再看Select指定的是那些欄位→再根據那個欄位進行排序。 ※ 使⽤⽅
Thumbnail
利用文字紀錄,明確寫下自己的採購項目......
Thumbnail
跟團 / D2 / 瑞穗牧場 - 富里花海景觀區 - 北迴歸線標誌公園 - 鐵花村
Thumbnail
跟團 / D0~D1 / 見晴懷古步道 - 太平山莊 - 多望吊橋 - 湯圍溝
Thumbnail
去日本玩該怎麼找住宿,在這邊跟大家推薦一下我的作法!
Thumbnail
在Dcard有人求救一個問題:想要將layer與panel的資料提出出來,如下圖。 這個題目是很經典的需求,就是多條件查找,多條件查找有蠻多種不同的解決方法,甚至版本不同解法也是天壤之別哦。 準備動作 在寫函數之前,記得要先觀察一下我們想要提取的資料有什麼樣的規則,可以發現A欄中只
Thumbnail
大家好,我是woody,是一名料理創作者,非常努力地在嘗試將複雜的料理簡單化,讓大家也可以體驗到料理的樂趣而我也非常享受料理的過程,今天想跟大家聊聊,除了料理本身,料理創作背後的成本。
Thumbnail
哈囉~很久沒跟各位自我介紹一下了~ 大家好~我是爺恩 我是一名圖文插畫家,有追蹤我一段時間的應該有發現爺恩這個品牌經營了好像.....快五年了(汗)時間過得真快!隨著時間過去,創作這件事好像變得更忙碌了,也很開心跟很多厲害的創作者以及廠商互相合作幫忙,還有最重要的是大家的支持與陪伴🥹。  
Thumbnail
嘿,大家新年快樂~ 新年大家都在做什麼呢? 跨年夜的我趕工製作某個外包設計案,在工作告一段落時趕上倒數。 然後和兩個小孩過了一個忙亂的元旦。在深夜時刻,看到朋友傳來的解籤網站,興致勃勃熬夜體驗了一下,覺得非常好玩,或許有人玩過了,但還是想寫上來分享紀錄一下~
Thumbnail
※ 何時該使用 JOIN? JOIN 使用的時機是:當你需要同時查詢一張以上的資料表的時候。 ※ SQL有哪些TABLE JOIN的方式? INNER JOIN LEFT JOIN RIGHT JOIN SELF JOIN ※ 使用 JOIN 的時候,我們需要考慮到: 我要使用哪一種
Thumbnail
※ 什麼是WHERE? 使用 WHERE來設定條件,可以幫助我們縮小查詢結果的範圍,取得想要的結果。 ※ 語法: ※ 解析順序: From:先看是哪一張table→table裡面符合Where指定條件的record→再看Select指定的是那些欄位→再根據那個欄位進行排序。 ※ 使⽤⽅
Thumbnail
利用文字紀錄,明確寫下自己的採購項目......
Thumbnail
跟團 / D2 / 瑞穗牧場 - 富里花海景觀區 - 北迴歸線標誌公園 - 鐵花村
Thumbnail
跟團 / D0~D1 / 見晴懷古步道 - 太平山莊 - 多望吊橋 - 湯圍溝
Thumbnail
去日本玩該怎麼找住宿,在這邊跟大家推薦一下我的作法!
Thumbnail
在Dcard有人求救一個問題:想要將layer與panel的資料提出出來,如下圖。 這個題目是很經典的需求,就是多條件查找,多條件查找有蠻多種不同的解決方法,甚至版本不同解法也是天壤之別哦。 準備動作 在寫函數之前,記得要先觀察一下我們想要提取的資料有什麼樣的規則,可以發現A欄中只