Google Spreadsheet – 能人所不能

Google Spreadsheet 已 經 出 來 很 多 年 了 ,但 筆 者 還 是 偏 好 於 使 用 Excel。不 過 ,隨 著 Google Spreadsheet 這 許 多 年 的 發 展 ,它 有 越 來 越 多 的 功 能 是 Excel 所 沒 有 的 ,今 天 筆 者 就 來 介 紹 幾 個 。

Google Finance

=GoogleFinance(symbol, attribute)

Google Finance

Google Finance

Google Finance function 可 以 非 常 簡 易 地 取 得 股 票 方 面 的 即 時 資 訊 。關 於 那 些 attributes 可 以 參 考 以 下 的 文 章 。

http://googledocs.blogspot.hk/2010/08/tips-tricks-googlefinance-in-google.html

ImportXML

=ImportXML(URL, query)

ImportXML

ImportXML

ImportXML 可 以 parse XML 又 或 者 HTML,可 以 簡 易 地 由 XML 或 者 HTML 取 得 所 需 的 資 料 。( 反 而 Import HTML 就 不 是 用 來 parse HTML 的 ,它 是 用 來 取 得 一 整 個 List 或 者 Table 的 資 料 )

而 那 個 query 用 的 就 是 Xpath。上 例 就 是 在 Google Finance 網 頁 找 出 所 需 要 的 股 票 資 料 。例 子 中 的  //meta[@itemprop=’name’]/@content,意 思 就 是 找 所 有 META tag 裡 面 ,attribute 裡 面 有 itemprop=’name’ 的 META tag,再 傳 回 那 一 個 META tag 裡 面 attribute content 的 資 料 。詳 細 的 Xpath Syntax 可 以 參 考 以 下 網 址 。

https://www.w3schools.com/xml/xpath_syntax.asp

QUERY

=QUERY(data, query, headers)

QUERY function

QUERY function

QUERY function 十 分 有 用 ,可 以 在 Google Spreadsheet 的 資 料 裡 面 做 類 似 於 SQL 的 Query。例 如 上 例 中 ,左 邊 是 一 個 簡 單 帳 簿 ,右 面 就 用 了 兩 條 十 分 簡 單 Query function,就 做 到 了 分 類 的 統 計 和 按 月 份 的 統 計 。

QUERY function 裡 面 的 data 就 是 資 料 範 圍 ,就 是 A4:C8 這 樣 一 個 連 續 的 範 圍 的 樣 子 ,當 然 使 用 其 他 sheet 或 者 file 的 資 料 也 一 樣 可 以 。headers 就 是 資 料 範 圍 裡 面 有 沒 有 包 括 headers,如 果 headers 只 有 一 行 就 是 1,有 兩 行 就 是 2,如 此 類 推 ( 是 的 , 別 奇 怪 , 是 可 以 多 於 一 行 的 )。

Remove duplicates from Excel

至 於 query 參 數 ,就 是 整 個 function 的 重 點 ,我 們 的 查 詢 語 句 了 。這 裡 的 query 基 本 上 就 是 一 個 簡 化 了 的 SQL select。它 裡 面 是 沒 有 From 的 (其 實 from 就 是 data 參 數 了 )。至 於 我 們 在 SQL 的 fields 就 會 用 column 來 代 替 ,要 用 大 寫 。其 他 的 where, group by, order by, limit 也 是 一 樣 可 以 用 的 。fields 的 運 算 式 sum, max, min, ave 之 類 也 一 樣 有 提 供 。

比 較 不 同 的 是 它 加 入 了 label 和 format 兩 個 句 法 。

select year(A), month(A), sum(C) group by year(A), month(A) label sum(C) 'Total Cost'

例 如 上 例 就 可 以 把 sum(C) 的 label 設 定 為 Total Cost。

query 的 詳 細 可 以 參 考 下 面 網 址 。

https://developers.google.com/chart/interactive/docs/querylanguage

Google Finance, ImportXML, QUERY 這 幾 個 function 加 起 來 ,就 可 以 寫 出 不 錯 的 portfolio (投 資 組 合 ) 管 理 工 具 了 。當 然 ,也 不 是 人 人 都 有 投 資 股 票 的 ,Google Spreadsheet 當 然 也 有 其 他 好 玩 的 工 能 ,例 如 下 面 兩 個 。

Google Translate

=GoogleTranslate(text, source language,target language)

Google Translate

Google Translate

Google Translate 顧 名 思 義 ,就 是 可 以 翻 譯 字 串 。做 做 繁 簡 轉 換 也 可 以 。但 別 問 我 為 什 麼 bus 會 譯 做 總 線 。XDDDDDD

Image

=IMAGE( URL, selection (optional) )

Image function

Image function

Image function 可 以 在 儲 存 格 內 插 入 圖 片 。至 於 selection 參 數 ,1 是 auto fit; 2 是 stretch to fit; 3 是 original size; 4 就 是 自 訂 大 小 。

ctleung張 先 生 ,男 性 ,肖 龍 。
職 業 :I.T. Consultant
簡 介 :不 好 好 讀 書 ;七 尺 差 五 寸 ,手 長 過 膝 ,雙 耳 垂 肩 ;性 寬 和 ,寡 言 語 ,喜 怒 不 形 於 色 。據 說 少 時 曾 斬 白 蛇 於 鳳 凰 山 下 ……
This entry was posted in Computer & Network and tagged , , , , , , , , , , . Bookmark the permalink.

4 Responses to Google Spreadsheet – 能人所不能

  1. jolin says:

    Hi

    不好意思,因為正在苦惱一個google spreadsheet的query功能,意外看到您的文章,想要請教您~

    就是我目前建立了兩個分頁,希望第二個分頁的A欄,能把第一個分頁的A欄裡,關於「人才」的資料篩選出來,不過怎麼寫不對。不曉得方不方便請教您。https://docs.google.com/spreadsheets/d/1NBI67w60qU0fr4qm0-txtLj7LsiP1ByP78yPy2SzKTM/edit?usp=sharing

    先跟您說謝謝!

  2. jacky says:

    版主好:
    請教這個問題
    假設我指定抓取在B1儲存格的資料來套取相同模式的內部資料,但是該網頁的網址資訊是在網址中間,該怎麼解決呢?
    說明如下:
    假設網址是https://www.mar.com/ppp/abc
    異動點是abc的情況,語法如下,是OK可行的,
    =importxml(“https://www.mar.com/ppp/”&B1,”//*@id=’Rf’]/tbody/tr[7]/td[23]/div”)
    那如果網址(https://www.mar.com/ppp/abc)異動的是”ppp”而不是”abc”該怎麼修改呢?

    • C.T. Leung says:

      哎呀,你自己都識得「& B1」,還竟然問得出這個問題,真的十分奇怪。你欠缺的是一些很基本的基本知識。回顧一下我們的 function。
      =IMPORTXML(url, xpath_query)
      裡面有兩個參數,url 和 xpath_query,這兩個參數,說穿了其實都是字串(string)。所以,在 google spreadsheet 裡面是如何處理字串的呢?十分簡單。
      1. 字串要用 double quote 括起來:
      2. 要 join 兩個字串就用「&」operator;
      這兩個你都知道,而且正在用了。「&」operator 其實和其他 operator 一樣,例如「+」號,你總可以連續用好多好多次吧?為何要止於一次呢?
      例子:
      1. = A1 & A2 & A3
      2. = A1 & “,” & A2
      3. = “I AM ” & “A” & ” BOY.”
      4. = “TEXT” & A1 & “STRING”
      所以,你要的答案也就呼之欲出了。
      “https://www.mar.com/” & B1 & “/abc”
      哈哈哈,夠簡單吧。
      特別值得一提,除了用「&」operator,其實 google spreadsheet 還有一條轉門用在連接字串的 function,CONCATENATE,用回上面的例子。
      1. = CONCATENATE(A1, A2, A3)
      2. = CONCATENATE(A1, “,”, A2)
      3. = CONCATENATE(“I AM “, “A”, ” BOY”)
      …….
      CONCATENATE 和 「&」operator 得出得結果是沒有分別的。所以,你用 CONCATENATE 也一樣可以。

Leave a Reply

Your email address will not be published. Required fields are marked *