前回の記事では、集計の方法として、SUMIFなどの関数を紹介しました。
今回の集計に使える関数の紹介です。SQLなどになじみのある方は、この関数が便利に感じると思います。
前回の記事と同じように、支払い種別、用途、金額という表があった時に、支払種別と用途ごとに集計したいというような場合に、SQLだと、
select 支払い,用途,sum(金額) from xxx group by 支払い、用途
というようなSQLで集計するとおもいます。これはSpreadsheetでも同様に描くことができます。
セルに下記のように書いてみましょう。
=QUERY(A2:C10,"select A,B,sum(C) group by A,B",-1)
SQLの集計関数と同じ文法が書けます。最初の引数は、範囲。次がQuery文字。
これを書くと下記のように表示することができます。
Joinなどはできませんが、それでもSQLに慣れている人にとっては非常に便利な関数です。
SQLに慣れ親しんでいる人は、いろいろ関数を組んで書くよりも楽かもしれませんね。
今回のスプレッドシートの例は、こちら。
昨日は、件数を算出する関数について書きました。
本日は、似たような関数の集計系の関数について記載します。
まず、一番有名でシンプルな関数は、単純にすべての数字を足す関数です。
これは、シンプルに下記のように書きます。
=SUM(C2:C100)
C列の2行目から100行目を足すときにつかします。
次に条件に一致したときに足すという関数SUMIFです。
=SUMIF(B2:B100,"食費",C2:C100)
B列に用途、C列に金額というような表があった時には、上のように書くと、食費の合計値を算出することができます。
さらに、複合条件にしたい場合には、SUMIFSという関数を利用します。
=SUMIFS(C2:C100,B2:B100,"医療費",A2:A100,"クレジットカード")
A列に支払い種別、B列に用途、C列に金額というような表があった時にクレジットカード払いの医療費というのを集計したい場合には、SUMIFなどの関数を利用します。
いりいろと便利な関数が多いですね。
本日のサンプルスプレッドシートはこちらになります。
昨日の記事で紹介した
=IMPORTRANGE("1CoJnqVABFhSx0CNgl7hSgGn-_w6hqtCq3PhBVUJ3VCM","Sheet1!B3")
という記述ですが、2番目の引数が文字型になってしまっていることにより、実はちょっと不便な感じになってます。行を追加してコピーすると、通常の方式であれば参照部分が行に対応して、
B4,B5
と増えていってくれる部分が、文字列のままなのでB3のままになってしまう問題があります。
それを解消するために、文字列結合関数のCONCATENATEとCELL情報を取得するCELLという関数を使って下記のように書き換えられます。
=IMPORTRANGE("1CoJnqVABFhSx0CNgl7hSgGn-_w6hqtCq3PhBVUJ3VCM",CONCATENATE("Sheet1!",Cell("address",B3)))
"Sheet1!B3" の部分が CONCATENATE("Sheet1!",Cell("address",B3)) にリファクタリングされています。
CONCATENATEは、複数の文字列を結合する関数です。
CELL関数は、Cell("address",B3)のアドレス情報を文字列化してくれます。実際には、$B$3が返却されます。
文字列の結合とかってあんまり利用しないと思われがちですが、こういう関数を使いこなそうと思った時には、あると便利に使えたりしますね。
本日の関数を使ったサンプルのスプレッドシートはこちらになります。
https://docs.google.com/spreadsheets/d/1wgeeUHpZfemXc0cg0QhEwKHbHw3QxpiNxs0Gi9d9zVA
昨日に続き、Import系の関数サンプルです。
本日は、IMPORTRANGEという、他のSpreadSheetを取り込むための関数です。
個人的には、こちらの関数が一番実用的かなと思っています。
SpreadSheetだと、毎月、同じフォーマットで値を出すことも多いかと思います。
例えば、簡単な例として、9月分の家計簿と10月分の家計簿を作ってみました。もちろん、こちらは架空の家計簿です。
ここでシートが分かれていると、困るのが、前月との差額を計算したいときに困ります。
その問題を解決してくれる関数がIMPORTRANGEという関数です。別ファイルに作成された票をあたかも自分の表にあるかのように扱えるのが素晴らしい点です。
使い方は簡単です。
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1d2EW-83Jd68q8oeuzww_Mrnz23pVnedQdFSgXyeIn7w","Sheet1!B3")
のように書くと、リンク先のSheet1のB3列目を取得することができます。
=IMPORTRANGE("1CoJnqVABFhSx0CNgl7hSgGn-_w6hqtCq3PhBVUJ3VCM","Sheet1!B3")
というように省略して書くことも可能です。
また、範囲を指定すると、複数列・複数行を取り込むことも可能です。
=IMPORTRANGE("1CoJnqVABFhSx0CNgl7hSgGn-_w6hqtCq3PhBVUJ3VCM","Sheet1!A1:B3")
これも他の関数と組み合わせることにより、可能性は無限に広がっていきます。
サンプルのSpreadsheetはこちらです。
https://docs.google.com/spreadsheets/d/1CoJnqVABFhSx0CNgl7hSgGn-_w6hqtCq3PhBVUJ3VCM/edit#gid=0
こちらも、こんな風に使っているなどの情報を下記のコメントかメールで送信していただけるとありがたいです。
2日連続で書いてみます。とりあえず、なるべく毎日書くように頑張ってみようと現段階では考えてます。
昨日紹介した(IMPORTDATA)関数とやりたいことは似ているんですが、本日は、IMPORTHTML関数を使ってみます。
昨日はcsvファイルなどを取り込むときに使う関数でしたが、この関数は、HTML上のテーブルやリスト値を取得することができます。
csv形式での後悔はしてないけど、ブラウザ上に表示されているような場合には便利です。
例えば、Yahooの株情報を表示するページを例にとってみます。
https://info.finance.yahoo.co.jp/ranking/?kd=8&mk=1&tm=d&vl=a
このページでは、配当利回りが高い順に表示されるページですが、これをGoogleSpread Sheetに下記のように記述することにより取り込むことが可能になります。
=IMPORTHTML("https://info.finance.yahoo.co.jp/ranking/?kd=8&mk=1&tm=d&vl=a","table",1)
これだけだと、正直あまりうれしくないと思いますが、これと他の関数を組み合わせてやることによりいろいろ便利にすることができます。
例えば、ほかに用意された関数を使って自分が買おうと思っている株価を別のシートで管理しておいて、配当利回りが４％を超えるようなものを絞り込むとか、そいういう応用をすることができるようになります。こういう応用例もおいおい紹介していければいいかと思ってます。
また、Google App Scriptなどを組み合わせて使うと、一定条件を満たしたときに通知するとかもできるようになると思います。
下記が実際に使ってみたサンプルです。
https://docs.google.com/spreadsheets/d/16a3TRguKFndLx3oGFZPgFml91F8h4xxgMN4le1gtB-4/edit?usp=sharing
こちらも、こういう風に利用したなどの情報をコメントやメールで頂けるとありがたいです。
久々の更新になりますが、Google spreadsheetはいろいろと使いこなせば日々の仕事にも役に立ちそうな気がするので、そこら辺を追及していこうかと思ってます。
しばらくは、Google spreadsheetの関数を使っていろいろ遊んだ結果を残していこうということで、暫くいろいろな関数で遊んだ結果を書いていこうかと思っています。
初回は、IMPORTDATAという関数です。こちらは、どういう関数かというと、インターネット上からダウンロードできるcsvファイルをSpreadsheetに取り込むことができるというものです。
例えば、コロナの陽性者数情報を厚生労働省のホームページからｃｓｖファイルとしてダウンロードできます。下記を見ていただくと、いろいろな情報をｃｓｖファイルとして公開しています。
https://www.mhlw.go.jp/stf/covid-19/open-data.html
この中の陽性者情報を取得するｃｓｖファイルのURLを確認して、下記のようにspreadSheetに書いてみましょう。
=IMPORTDATA("https://www.mhlw.go.jp/content/pcr_positive_daily.csv")
すると、データが取得できます。これに、チャートを追加してみるとこんな感じで表示することが可能です。
https://docs.google.com/spreadsheets/d/1MSh4XPO8LdBJkNhHbnD9QCW9nV1qKVnjdtvuo9MMpeQ/edit?usp=sharing
なんかいろいろと他にも応用できそうですね。
こんな風に利用したなどの情報をコメント欄でいただけると嬉しいです。
