2014年1月19日 星期日

Google spreadsheet(二)

2. QUERY
從網頁導入的資料可能筆數很多,這時如果會使用QUERY來查詢,可以快速擷取你要的資訊,也比較不傷眼睛(XD大誤

語法:QUERY(資料, 查詢, 標題)

資料,指的是儲存格範圍
查詢,姑且稱做為 類SQL語言,不過因為都在 spreadsheet 內操作,所以無須寫 FROM TableName(相關的SQL語法請參照相關資料)
在spreadsheet中,標題是用不上的,都改用欄位名(Column)來查詢--見下面範例

篩選資料,原則上是可以用篩選功能,只不過是我大喜歡這個功能,因為篩選後,列的資料會隱藏,導致右邊的空白欄位無法利用(會隱藏不見)

援用上例,做個實例操作,在K2欄位輸入
=QUERY(A2:H,"select * where H like '%太魯閣%'")

這代表要找到 DATA(A2:H), H欄位中 包含有 太魯閣 字串的記錄
其中 %為萬用字元,為一個以上的字串,也就是  太魯閣前後都可以有字串

這樣就可以找到當日太魯閣號的自強號班次,你也可以改成 普悠瑪 試試...






 

20 則留言:

  1. 如你的圖所示,最後得到7筆結果,有辦法顯示出7這個計算結果在輸入程式碼的儲存格嗎??

    回覆刪除
    回覆
    1. 不是很懂你要表達的意思,是想拿掉公式只貼上值嗎?

      刪除
  2. 在問 如果要同時抓取多個工作表同樣區塊 要怎麼限定多個工作表的範圍

    回覆刪除
    回覆
    1. 假設你想在【查詢結果工作表】QUERY【USER存取介面】的資料
      請在【查詢結果工作表】A1中輸入
      =QUERY('USER存取介面'!A10:H,"select * where H like '%" & "太魯閣" & "%'")

      刪除
  3. 您好:

    想要請教為何QUERY的值會顯示不出來?

    =QUERY('表單回應 3'!$C$2:$D$100, "select C, sum(D) where C like '4717702069605' group by C")
    當將上述程式碼輸入後的下一刻會出現,(如圖一)
    圖一:http://imgur.com/MvLStjb

    但關掉試算表重開後或是從發佈到網路的頁面看就無法顯示了。(如圖二)
    圖二:http://imgur.com/ve2wkxn

    但同樣的程式碼在另一個工作表就沒有上述問題,(如圖三)
    不知道是我少做哪個步驟嗎?
    圖三:http://imgur.com/qjaTGlK

    感謝您的解答!

    回覆刪除
    回覆
    1. 作者已經移除這則留言。

      刪除
    2. 我參照你的例子寫了一小段程式碼,沒有你上述的問題
      https://docs.google.com/spreadsheets/d/1EPIZvO3PQWblJKYtbg0E-wJC4jj1vE2chXgEKLSJ_Ug/edit?usp=sharing

      你的語法基本上沒錯,至於為何會造成這種現象我也無法給你答案

      有個建議,LIKE 通常是用在模糊的比較(搜尋、分類), 例如 王子、王子麵、小王子,可用 LIKE %王子% 語句來下達
      依照你的範例,語法是否改成 C= '4717702069605' 較好?

      刪除
    3. 非常感謝您的回覆,後來我發現貌似是數字的關係。
      只要' '內是中文就沒事。
      雖然不知道為什麼,但總算是解決這個問題了。

      另外,關於您的建議,我一開始就是嘗試使用等號,
      但使用等號完全無法跳出查詢值(圖一),反而一直是(圖二)狀態。
      這可能也與數字有關吧!
      詳細可能也還要在研究....

      非常感謝!

      刪除
    4. 會不會是資料型態的問題,若是被參照的欄位(C欄)是數值的話,你應該要寫成
      =QUERY('表單回應 3'!$C$2:$D$100, "select C, sum(D) where C=4717702069605 group by C")

      刪除
  4. 請問可以做跨檔案的查詢嗎? 資料來源在A檔案,在B檔案查詢A檔案的資料並呈現結果,且A檔案資料更新B檔案會同步更新

    回覆刪除
  5. 你可以使用IMPORTRANGE()+QUERY()二個函數達到你使用的功能
    例如:
    =Query(IMPORTRANGE("URL","客戶資料!A1:h5"),"select * where Col3 like '%貝比%'")

    注意,Col3的C一定要大寫(它對應的是來源試算表的C欄)

    回覆刪除
  6. 另外請教VLOOKUP問題,如果要跨檔案比對,公式要怎麼寫?
    我寫這樣失敗
    =VLOOKUP(B2,("URL","客戶資料!A1:S"),2,FALSE)

    回覆刪除
  7. 我找到方法了
    =IFERROR(vlookup(B2,IMPORTRANGE("URL","1!A2:S"),2,false),)

    回覆刪除
    回覆
    1. 恭喜,不斷的嘗試與討論就可以獲得答案^^

      刪除
  8. 請問:
    關於這一句:

    =QUERY(A2:H,"select * where H like '%太魯閣%'")

    關鍵字"太魯閣"的部份,我想把它指向其它儲存格,讀取該格的內容來篩選需要的資料,
    但始終無法順利成功,最後只能用:

    CONCATENATE("select E where G = '",'F$2,"'")

    迂迴完成,不知道真正的寫法為何?

    回覆刪除
    回覆
    1. 啊!答案就在第二則留言裡。真是踏破鐵鞋無覓處。

      刪除
  9. 大神請教一下~
    要怎樣把checkbox內的多數值顯示在試算表的一個Column內~

    目前post 出去 成功的值是這樣
    "result":"success","data":"{\"step\":[\"加值型方案\"],\"網站開發\":[\"官方網站建置\",\"後台系統開發\"]}"}

    但在試算表內就只會顯示一筆資料

    我的代碼如下

    function record_data(e) {
    Logger.log(JSON.stringify(e)); // log the POST data in case we need to debug it
    try {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = doc.getSheetByName('responses'); // select the responses sheet
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = [ new Date()]; // first element in the row should always be a timestamp
    // loop through the header columns
    for (var i = 1; i < headers.length; i++) { // start at 1 to avoid Timestamp column
    if(headers[i].length > 0) {
    row.push(e.parameter[headers[i]]); // add data to row
    }
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    }
    catch(error) {
    Logger.log(e);
    }
    finally {
    return;
    }

    }

    回覆刪除