2020年10月30日金曜日

Google spreadsheetの関数を使ってみる(QUERY)

 前回の記事では、集計の方法として、SUMIFなどの関数を紹介しました。

今回の集計に使える関数の紹介です。SQLなどになじみのある方は、この関数が便利に感じると思います。

前回の記事と同じように、支払い種別、用途、金額という表があった時に、支払種別と用途ごとに集計したいというような場合に、SQLだと、

select 支払い,用途,sum(金額) from xxx group by 支払い、用途  

というようなSQLで集計するとおもいます。これはSpreadsheetでも同様に描くことができます。

支払い用途金額
現金食費¥1,000
現金交通費¥1,000
現金食費¥600
現金娯楽費¥1,500
現金食費¥500
クレジットカード医療費¥30,000
現金薬代¥1,500
現金医療費¥3,000
クレジットカード医療費¥18,000

セルに下記のように書いてみましょう。

=QUERY(A2:C10,"select A,B,sum(C) group by A,B",-1)

SQLの集計関数と同じ文法が書けます。最初の引数は、範囲。次がQuery文字。

これを書くと下記のように表示することができます。

クレジットカード医療費48000
現金交通費1000
現金医療費3000
現金娯楽費1500
現金薬代1500
現金食費2100

Joinなどはできませんが、それでもSQLに慣れている人にとっては非常に便利な関数です。

SQLに慣れ親しんでいる人は、いろいろ関数を組んで書くよりも楽かもしれませんね。

今回のスプレッドシートの例は、こちら

2020年10月29日木曜日

Google spreadsheetの関数を使ってみる(SUM/SUMIF/SUMIFS)

昨日は、件数を算出する関数について書きました。

昨日の記事。 

本日は、似たような関数の集計系の関数について記載します。

まず、一番有名でシンプルな関数は、単純にすべての数字を足す関数です。

これは、シンプルに下記のように書きます。

=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などの関数を利用します。

いりいろと便利な関数が多いですね。

本日のサンプルスプレッドシートはこちらになります。

2020年10月28日水曜日

Google spreadsheetの関数を使ってみる(COUNT/COUNTIF/COUNTIFS)

Google Spreadsheetには、アンケートフォームを作って、その結果をspreadsheetに転記する機能があります。これは、「挿入」→「フォーム」で作成できます。

例えばこんなフォームができます。

そして、これに回答すると、こんな感じのシートに自動的に転記されます。

さて、このような状況の中、今回は、これらと一緒に集計をしていきたいというようなケースに利用可能なCOUNT系の関数を紹介します。
  • アンケートの合計数
  • 男性数
  • 20代の数
 という3つのパターンを集計する方法を紹介します。

アンケートの合計数は、 =COUNT(FormResponses3!A2:A10001) という感じです。
単純にA2からA10001までの件数を返してくれます。

男性数は、 =COUNTIF(FormResponses3!B2:B10002,"男") という感じです。
引数の最初の部分は、範囲、2番目は条件です。つまりB列が"男"の件数が返ってきます。

20代の数は、COUNTIFSという関数を使います。

=COUNTIFS(FormResponses3!C2:C10002,">=20",FormResponses3!C2:C10002,"<30")

という感じです。範囲と条件を繰り返して書いていきます。上は、C列が20以上で30未満のものの件数が返却されます。

これらを応用すると、例えば下記のようなこともできるでしょう。
  • 男女比率の算出
  • 年代の割合の算出
  • 年代のグラフの追加
アンケートを実施しながら、同時に集計結果が出てしまうのはありがたいですね。
Google spreadsheetの関数を覚えれば覚えるほど、いろいろと便利になると思います。

実際の利用例は上のシートと同じところに書かれています。

2020年10月27日火曜日

Google spreadsheetの関数を使ってみる(CONCATENATE/CELL)

 昨日の記事で紹介した

=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



2020年10月26日月曜日

Google spreadsheetの関数を使ってみる(IMPORTRANGE)

 昨日に続き、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


こちらも、こんな風に使っているなどの情報を下記のコメントかメールで送信していただけるとありがたいです。

2020年10月24日土曜日

Google spreadsheetの関数を使ってみる(IMPORTHTML)

 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)

これだけだと、正直あまりうれしくないと思いますが、これと他の関数を組み合わせてやることによりいろいろ便利にすることができます。

例えば、ほかに用意された関数を使って自分が買おうと思っている株価を別のシートで管理しておいて、配当利回りが4%を超えるようなものを絞り込むとか、そいういう応用をすることができるようになります。こういう応用例もおいおい紹介していければいいかと思ってます。

また、Google App Scriptなどを組み合わせて使うと、一定条件を満たしたときに通知するとかもできるようになると思います。

下記が実際に使ってみたサンプルです。

https://docs.google.com/spreadsheets/d/16a3TRguKFndLx3oGFZPgFml91F8h4xxgMN4le1gtB-4/edit?usp=sharing

こちらも、こういう風に利用したなどの情報をコメントやメールで頂けるとありがたいです。

2020年10月23日金曜日

Google spreadsheetの関数を使ってみる(IMPORTDATA)

 久々の更新になりますが、Google spreadsheetはいろいろと使いこなせば日々の仕事にも役に立ちそうな気がするので、そこら辺を追及していこうかと思ってます。

しばらくは、Google spreadsheetの関数を使っていろいろ遊んだ結果を残していこうということで、暫くいろいろな関数で遊んだ結果を書いていこうかと思っています。

初回は、IMPORTDATAという関数です。こちらは、どういう関数かというと、インターネット上からダウンロードできるcsvファイルをSpreadsheetに取り込むことができるというものです。

例えば、コロナの陽性者数情報を厚生労働省のホームページからcsvファイルとしてダウンロードできます。下記を見ていただくと、いろいろな情報をcsvファイルとして公開しています。

https://www.mhlw.go.jp/stf/covid-19/open-data.html

この中の陽性者情報を取得するcsvファイルのURLを確認して、下記のようにspreadSheetに書いてみましょう。

=IMPORTDATA("https://www.mhlw.go.jp/content/pcr_positive_daily.csv")

すると、データが取得できます。これに、チャートを追加してみるとこんな感じで表示することが可能です。




このサンプルに利用したspreadsheetは下記にリンクをしておきます。

https://docs.google.com/spreadsheets/d/1MSh4XPO8LdBJkNhHbnD9QCW9nV1qKVnjdtvuo9MMpeQ/edit?usp=sharing

なんかいろいろと他にも応用できそうですね。

こんな風に利用したなどの情報をコメント欄でいただけると嬉しいです。