例えば、Google spread sheetで数字の部分だけを取り出したいとかいうニーズがあったりします。
そんな時に利用できるのが、REGEXTRACT関数です。
正規表現を使って文字列を抽出することができます。
例えば、「10円」というセルから10を取り出そうと思うと、
=REGEXEXTRACT(A2,"[0-9]+")
と記述することにより、10の部分を取り出すことができます。
他の例だと、例えば、メールのアドレス部分が、
「サンプル太郎 <spreadsheet@example.com>」のような形式で入っていた場合。
下記のように記述すると、名前の部分が取り出せます。
=REGEXEXTRACT(E2,"(.+)<")
また、下のように書くとメールアドレスの部分を取り出せますね。
=REGEXEXTRACT(E2,"<(.+)>")
このように、文字列を自由自在に分解できると、また、spreadsheetの活用範囲が広がるのではないでしょうか。
本日の関数の実サンプルはこちらのリンクを参照ください。
ある列に入っている値の一覧を取得したいと思うことがあると思います。
そんな時に使う関数が、UNIQUE関数です。
利用方法は簡単です。A列に入っている値の一覧が取得したいときには、下記のように記述します。
=UNIQUE(A2:A10)
このように書くと、ここから下に、一意な値が並びます。
それをさらに並べ替えたい場合には、SORT関数を使います。
=SORT(UNIQUE(C2:C10))
これで、一覧を作成したうえで並び替えることができます。
実際のSpreadsheetのサンプルのURLはこちら。
Google Apps scriptを使ってSpreadSheetを開いたらメールを送る例は、前回の記事でも紹介しました。
似たようなユースケースですが、今回は、フォームを使って自動送信メールを実現する方法を紹介します。メール送信プログラムは前回の記事で紹介したので本日は割愛します。
また、フォームの作成は、以前の記事で紹介しているのでこれも割愛します。
メールのプログラムを書いた後に、スクリプトエディタ上の「編集」→「現在のプロジェクトのトリガー」より、トリガを作成のボタンを押していただき、イベントの種類を選択から「フォーム送信時」を選ぶとフォームを提出したときに自動的にメールを送信することができます。
var toAdr = Session.getActiveUser().getEmail() ;
これを使うと例えば下記のようなことが実現できます。
例えば、下記のようなことをしたくないでしょうか。
例えば、鍵が開錠されたら通知をしたいけど、鍵は、単なる物理鍵なので、通知は難しい。
そんな時に、下記のような構成にすることによって、疑似的に実現可能かなと思っています。
・暗証番号などのカギをGoogle spreadsheetに書いておく。
・Google spreadsheetが開かれたら、誰が開いたかを管理者にメールする
このようなことが、Google spreadsheetとGoogle apps scriptを使うと簡単にできます。
暗証番号型のカギで鍵をかけてその暗証番号などの秘密のファイルをどこかにおいておき、
そのファイルが開封されたら、通知してほしい。
まず、Google spreadsheetから「ツール」→「スクリプトエディタ」を開きます。
その後下記のようなコードを書きます。
<pre>
function send_mail(){
var toAdr = "xxxxxxx9999@gmail.com";
var ccAdr = "";
var bccAdr = "";
var subject = "";
var name = "";
var files = new Array();
var body = Session.getActiveUser().getEmail() +
"さんから訪問がありました。\n";
MailApp.sendEmail({to:toAdr, cc:ccAdr, bcc:bccAdr, subject:subject, name:name, body:body, attachments:files});
}
</pre>
Session.getActiveUser().getEmail() は接続者のメールアドレス情報を取得します。
MailApp.sendEmailはメールの送信をする関数です。
toAdrに送信したいあｔメールアドレスをいれてください。
その後、スクリプトエディタ上で、実行→関数を実行→send_mailを選択すると許可を求める画面が出てきます。今ログインしているユーザの代わりにメールを送信する許可を求められるので、問題がなければ許可をします。
すると、メールが送信されると思います。
次に、スクリプトエディタ上の「編集」→「現在のプロジェクトのトリガー」を選択すると、この関数をいつ発火するかを選択できます。「トリガーを追加」を押して起動時に発火するように下記の通り、選択します。
イベントの種類を「起動時」とすることでファイルを開いたときにメール送信関数を呼び出すようにすることができます。
とても簡単ですね。。。
前回の記事では、集計の方法として、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
なんかいろいろと他にも応用できそうですね。
こんな風に利用したなどの情報をコメント欄でいただけると嬉しいです。
