酩酊記

0.1%は音楽の話、残り99.9%はaiko大先生への愛

新人AdOpsが命を救われたspreadsheet関数

iPhone発売まであと30分弱なので、眠気覚ましに書く。

背景
  • 社会人スタートからwindowsひとすじ4年+前職のwifiがバカ遅すぎ=必然的にExcelと恋に落ちる
    • 業務は主に広告の運用とサービスKPI分析とか監視とか 偏差値3のガチ文系につき全部Excel手動でがんばる
  • そんななか転職して憧れのmacをゲット
    • Excel for Macがシングルコアしか使ってくれなくてあまりの遅さに咽び泣く
  • 業務上止むをえず、苦手だったGoogle Spreadsheetに手を出す
  • Excelで使えない関数使えすぎ大問題が発生し、Excel時代より作業が圧倒的に早く終わる
    • 特に使うのは3つ、多分これがなかったら職を失って死んでいたはず
    • 感謝の意を込めてそれぞれ「いmp」「あっr」「qry」で呼び出せるようにしてある
importrange
  • 別シートのデータをURL+範囲指定で呼び出せる魔法
  • 使用例
importrange(”[他シートURLとかkeyとか]”,”[シート(タブ)名]!A:H”)
  • [URL or Key]はセル内文字列も当然参照可能
  • google系主要広告サービスのレポートデータはcsvで落とさずgoogle driveに即UPできる
  • そのシートURLを適当なセルにペッと貼り付けるだけでrawdataが更新されるようにできる
    • これによりローカルに落としたデータをシートにコピペ、という原始的手法が消滅する
  • 後述のqueryと組み合わせると神が爆誕
arrayformula
  • 同じ関数を同列に一斉適用してくれる魔法
  • 使用例
arrayformula(vlookup(A:A,”nayose!A:H”,2,0))
  • 行数が都度異なるrawdata(例えば日ごとに行が増えるdaily数値)を参照して都度関数をひっぱるのが面倒なときに使える
  • vlookupの例をみるとわかるとおり、array~を使う場合もターゲットの参照セルを単一セル(A1)から配列(A:A)に変えるだけ
  • よくあるユースケースとしてはvlookupと組み合わせて広告系のID名寄せとか
  • 同じクリエイティブを複数DSPで回す場合、DSP-AではID:1234に対しDSP-BではID:9876、これらを横串集計したい的な
  • 名寄せシートにID対照表を控えておいて一律でユニークな値を返してあげる的な
query
  • これこそが神 自シート/他シート/単一シート/複数シート問わずあらゆるデータを条件付きで必要な分だけ取ってきてくれる魔法
  • 実際に見てもらったほうがはやい
使用例
query("sheetA!A:H”,”Select A Where C=‘ok')
query({sheetA!H:X;sheetB!H:X,sheetC!H:X},”SELECT Col3,Col5 WHERE Col5=‘ok')
query(importrange(“URL”,”raw!A:H”),”Select Col3,Col5 Where Col1=‘ok’ Order by Col3 asc)
query({sheetA!H:X;sheetB!H:X,sheetC!H:X},”SELECT Col3,Col5 WHERE Col5=‘ok')
  • WHERE句の条件をstringで指定する場合はシングルクォーテーションをつかおう
  • 大文字小文字をミスるとエラーを吐くので注意 
  • 4つめの例で上げた対象範囲シートのところをimportrangeにすれば異なるシートから1つのタブにデータをがっちゃんこできる(はず)
後記