Memo/Google/SpreadSheet

https://dexlab.net:443/pukiwiki/index.php?Memo/Google/SpreadSheet
 

スプレッドシート/spread sheet


BigQueryでスプレッドシートのデータを集計する


SUMIFS: 複数条件を指定して合計


IMPORTRANGE: 別スプレッドシートを読み込み


QUERY: SQLでデータを選択

select, where, order by, group by等でデータを選択できる。

  • IMPORTRANGE()と組み合わせる場合、列の指定が 「Col1, Col2」に変わる
  • 「select *」の場合は省略できる。
  • セルや関数を参照する場合: '"&value&"'
  • C列が指定日付未満を表示
    =QUERY('sheet1'!A:H,"where C < date '2020-05-01'",1)
  • C列が今日未満を表示
    =QUERY('sheet1'!A:H,"where C < date '"&text(today(), "YYYY-MM-DD")&"'",1)
  • whereにカレントシートの値で検索
    =QUERY('sheet1'!A:C,"select A,B,C where C='"&C2&"'",1)

SUBTOTAL: 垂直方向の集計/フィルタ適用済みを対象

  • SUBTOTAL
    • 9 - SUM: フィルタ適用された行のみを合計。名前付きフィルタも対象になる。
  • フィルタ適用された行のみ合計
    =SUBTOTAL(9, A2:A101)

カスタムフィルタ

  • 元データに影響を与えずに、複数のフィルタを作成できる
    • 例: 複数人で作業する場合新しくフィルタを作ると他の人に影響が出ないため便利
  • データ > フィルタ表示 > 新しいフィルタ表示を作成

カスタム数式

  • カスタム数式で、計算結果がTrue, Falseになる関数が使える
  • 例:1行目はヘッダ。2行目以降にデータ
  • REGEXMATCH: 正規表現
    • 部分一致検索: A列に"foo"か"bar" 文字列を含む場合のみ表示
      =REGEXMATCH($A2,"foo|bar")
  • OR: 完全一致のみ表示
    =OR($A2="foo", $A2="bar")

組織図をtableから生成


sumif(): 条件付き合計

  • H列に"yes/no/空白", E列に数値があり、"H列 != yes" のE列合計を計算したい場合、「<>」を使う。
    =sumif(sheet1!H2:H,"<>yes",sheet1!E:E)

QRコードを表示

スマホで情報を読み取らせたい時に便利


セルに特定の文字列を入力した場合に行の背景色を変える

  • 範囲に適用: A1:F1000
  • 書式ルール
    • セルの書式設定: カスタム数式
    • =countif($A1, "*文字列*")
  • 書式設定のスタイル: 好みの背景色

iso-8601形式の日付を変換する

「YYYY-MM-DDThh:mi:ssZ」のような形式が文字列として扱われてしまう。

  • 日付として扱われる形式へ変換
    =DATEVALUE(MID($A2,1,10)) + TIMEVALUE(MID($A2,12,8))

VLOOKUPで複数条件を指定

  • 検索キーは、範囲の1列目固定。検索キーは、行を一意に識別できるPrimaryKeyを複数列を結合するなどして作る
    • または、vlookup()の代わりに、query()関数を使う。where句で複数条件を指定できる。
  • itemシートにある情報を、sheet1から参照する場合
  • itemシート
    PKNameCategoryPrice
    =B2&C2Abook350
    =B3&C3Bbook500
    =B4&C4Amusic100
    =B5&C5Bmusic150
  • sheet1
    NameCategoryPrice
    Abook=vlookup(A2&B2,'item'!$A$1:$D$5,4,false)
    Amusic=vlookup(A3&B3,'item'!$A$1:$D$5,4,false)

VLOOKUP: 行方向に検索して、一致した列の値を返す

  • VLOOKUP - ドキュメント エディタ ヘルプ
    VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])
    • 範囲: 検索キーが1列目に含まれるように指定する。全範囲の中からは検索しない。
    • 番号: 出力したい範囲の列番号(1〜N)
    • 並べ替え済み: 完全一致させたいならFALSE

データをCSVでダウンロード

  • ファイル > 形式を指定してダウンロード > カンマ区切りの値(csv)
  • 公開したスプレッドシートが対象
  1. ファイル > ウェブに公開 > 公開開始
  2. URLからkey=の部分をメモ
  3. 以下のリンクのkey部分を置き換えて、ブラウザで開くと直接ダウンロードできる
    http://spreadsheets.google.com/pub?key=[KEY ID]&single=true&gid=0&output=csv

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2020-08-18 (火) 18:30:29 (63d)