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

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

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

https://docs.google.com/forms/d/e/1FAIpQLSdSfizwWS_FP94glDc91hyoLsj6rwcKNmcwM0UWjyTKj1w6GA/viewform?usp=sf_link

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

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

さて、このような状況の中、今回は、これらと一緒に集計をしていきたいというようなケースに利用可能なCOUNT系の関数を紹介します。

• アンケートの合計数
• 男性数
• 20代の数

 という３つのパターンを集計する方法を紹介します。

アンケートの合計数は、 =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の関数を覚えれば覚えるほど、いろいろと便利になると思います。

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

https://docs.google.com/spreadsheets/d/1_YZq5FrqiZTS1Fn5k6Dc7LHmrepLAq6wexM8BrwKuT4/edit#gid=0

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

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

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

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)

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

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

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

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

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

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

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

 久々の更新になりますが、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")

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

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

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

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

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

Google ドキュメントで章番号・節番号を付与

Google ドキュメントで見出しに章番号を付与しようといろいろ調べてみたけど標準ではそういう機能がなさそうな感じ。

ということで、スクリプト使ってできそうだったのでちょっと作成してみました。

基本的に、必要な概念は、

var pars = DocumentApp.getActiveDocument().getBody().getParagraphs();

でパラグラフを取得してループを回していきます。

  var hdg = par.getHeading();
    if (hdg == DocumentApp.ParagraphHeading.HEADING1

ここでヘッダ１なら章番号を加算して出力するという感じでやってます。

par.setText(counterh1+'.\t'+chunks[0]);

初めて書いてみましたが、なるほどなかなか便利ですね。

コード例：

findbugsプラグインを作ってみる

findbugsプラグインを作ってみました。
とりあえず、簡単なチェックなら簡単にできそうだということがわかっりました。

作り方は、下記のページを参考にしました。
・FingBugsのプラグインのTutorial http://code.google.com/p/findbugs/wiki/DetectorPluginTutorial

・DM_DEFAULT_ENCODINGを検出するfindbugsのソースコード http://code.google.com/p/findbugs/source/browse/trunk/findbugs/src/java/edu/umd/cs/findbugs/detect/DefaultEncodingDetector.java?r=14166

とりあえず、ある関数を呼び出しているというのを検出するものを書いてみました。

そして、BigDecimal.divide(java.math.BigDecimal)を呼んでいる箇所を 検出する場合には、下記のように書けばいい感じになりました。

あとは、Tutorialに書いているようにmessages.xmlとfindbugs.xmlと必要であればmessages_ja.xmlを 書いてあげてjarファイルにでもしてあげて、Findbugsのpluginフォルダに入れて 実行してあげれば、チェックを追加してあげることができます。

import edu.umd.cs.findbugs.BugInstance;
import edu.umd.cs.findbugs.BugReporter;
import edu.umd.cs.findbugs.MethodAnnotation;
import edu.umd.cs.findbugs.ba.XFactory;
import edu.umd.cs.findbugs.ba.XMethod;
import edu.umd.cs.findbugs.bcel.OpcodeStackDetector;

public class BigDecimalDevide9Detector extends OpcodeStackDetector{
 private final BugReporter bugReporter;
 public BigDecimalDevide9Detector(BugReporter bugReporter) {
        this.bugReporter = bugReporter;
    } 
 @Override
 public void sawOpcode(int seen) {
        switch (seen) {
        case INVOKEVIRTUAL:
        case INVOKESPECIAL:
        case INVOKESTATIC:
            XMethod callSeen = XFactory.createXMethod(MethodAnnotation.fromCalledMethod(this));
            XMethod badMethod = XFactory.createXMethod("java.math.BigDecimal", "divide","(Ljava/math/BigDecimal;)Ljava/math/BigDecimal;", false);
            if (badMethod.equals(callSeen)) {
                bugReporter.reportBug(new BugInstance(this, "TUTORIAL_BUG", HIGH_PRIORITY).addClassAndMethod(this)
                  .addClassAndMethod(this).addSourceLine(this));
            }
        }
 }
}

本当は、もっと細かいロジックも書いてみたいのだけど、今後、いろいろとFindBugsのソースを 見ながらちょっとずつ見ていこうかなと思っています。