2016年2月9日火曜日

法律家に役に立つ Googleスプレッドシート関数




H271216記載
フォーム、スクリプト、Googleスプレッドシート、更にDBにつなげるととても便利ですが、Excelと少し違うところもあります。ほそぼそと追加していきます。
H280209追記
「concatenate」関数を追記。スプレッドシートで綺麗な文章を作るには必要です。テンプレを作るのが一番むずかしいですね。



   

1 法律家に役に立つ Googleスプレッドシート関数


Excelとは少し異なるところもあるので、Googleスプレッドシートの関数集を随時追加していきたいとおもいます。仕様変更がある場合があるので、現在日付もつけて記載していきます。

2 IMPORTRANGE (<2015-12-16 Wed>現在)

2.1  更新を別のシートに更新反映


いきなり変な関数ですが、Excelのリンク貼り付けに相当するものといえましょうか。元データが更新されたら、変更先のデータも変更されていきます。

=IMPORTRANGE("スプレッドシートのID", "範囲指定")

でします。

スプレッドシートのIDは、URLに入っている文字列です。/d/の後のところにあるものです。

範囲指定は、名前付け範囲をすれば、そのままその名前を入れることができます。たとえば、フォームから送信され反映されたデータは、後で追加されていくことになります。例えばA列全部で指定しておくと後で追加されても直す必要がありません。

2.2  使いみち


フォームで反映された「回答」データは、あまり触らないほうがいいみたいです。元データはフォームで反映させたまま置いておきながら、フォーム前に作ったデータと統合する、たとえば、フォーム前のデータの最後の行からフォーム作成データを追加していきたいなどの用途があります。

データの使い回し(filter,vlookupと組み合わせる)が可能となります。


……………………………………………………

3 if (<2015-12-23 Wed>現在)


条件分岐のためにする関数。特にGoogleスプレッドシートで特有なものではありませんが、IMPORTRANGE関数と共に使うと、差し込み的な使い方ができます。

3.1  基本


if関数はExcelでもあり、同じ動きをします。

=if(セル等の範囲,真ならの値,そうでないならの値)

if関数は、他の関数と併用が可能です。

3.2  応用、importrange関数と併用


例えば、別シート(ここでは「シートの名前」)H9のセルについて


=if( 'シートの名前'!H9="","",IMPORTRANGE("GoogleスプレッドシートID", "'シートの名前'!H9"))


とすると、H9が空白なら空白を入れ、そうでないなら、H9の値を入れる。という関数になります。

H9の値は、文字列でもいいので、フォームからFAX番号、宛先住所、宛先氏名などを入れ、それぞれを指定しておけば、フォームを入れ回答シートに反映された瞬間に、送付状などが完成します。


……………………………………………………

4 vlookup (<2015-12-25 Fri> 記載)


 import(range。html など)関数と並び、神関数といわれてるとかいないとか。Excelに実装されたものと同じです。

4.1 基本


vlookup関数とフォームリストから、差し込み印刷的なものができます。


=vlookup(検索値、範囲、列番号)

間違ってはいないのですが、イメージがつかみにくい記載です。簡単にいえは、「検索値」は、範囲にあるIDや製品番号などの数字などを入れる場所です。ここにその数字を入れるだけで、そのID行にある各種のデータを引っ張ってくるイメージです。


「範囲」は、引っ張ってくるデータシートです。


「列番号」は、範囲の最も左上のところを1として、何番目にあたるかの数字を入れるところです。


4.2  応用 if関数との併用


vlookup関数は、if関数との併用もできます。

=if(VLOOKUP($A$3,list!$A:$Q,7)="","",VLOOKUP($A$3,list!$A:$Q,7))

現在あるシートのA3のセルに、ID等を入れたら、「list」シートのAからQの範囲を検索して、7番目の値等を抽出する。という意味です。

if関数で、仮にこの「7」のところに、何もなかったら空白を、そうでなければ「7」のところにある値をという形で入れています。

4.3  具体的な使い方:送付状テンプレから送付状作成


やり方としては、

フォームでフォームの回答シート(これはあまりいじらないほうがいいみたいです)

→新しいシートに、「list」(なんでもいいです)名前のシートを作り、

→このlistに、importrange関数で、データを引っ張ってきます。listシートは、フォームが更新されれば自動的に、更新されます。

→スクリプトを利用して、IDをつけます(追記予定)。

ブログ化しました(<2016-01-20 Wed>




→この数式を任意のところにコピペしていきます。コピペに便利なように(コピペしても数式が変わらないように)、「$」をつけて絶対参照にすると便利です。あとは、何番目(「7」)のところを変えていくだけです。


→IDのところに、IDの数字を入れるだけで、住所、送付先氏名等が一気に変わります。








5 filter関数(<2016-01-20 Wed>記載)


依頼者から受け取った証拠、弁護士側で入手した証拠は、とにかく、リスト化しておくと便利です。検番をつけて、一括管理するという方式で検察官が採用する方式です。

そんなときに使えるfilter関数です。



5.1  関数


filter関数

=filter(適用されるシート,条件)

です。

5.2 具体的な応用編、importrange関数と共に


具体的には、たとえば、

=filter(IMPORTRANGE("スプレッドシートID", "'list1'!A:J"),list1!J:J = "提出")

などと使います。

この例では、スプレッドシートIDの「list1」シートのA〜Jの範囲を取り込んで、Jにある「提出」というものだけをシートにfilter関数で取り出しています。


5.3  使いみち


検番のリストから、証拠化するもののみをfilter関数で取り出し、これを証拠説明書のシートに反映させます。

フォームから、証拠に必要な入力をするだけで、自動的に証拠説明書が作成されます。

最初、証拠提出「未定」にしておけば、証拠説明書のシートに反映されず、

再編集URLを利用して、もう一度、「提出」扱いにしておけば、証拠説明書シートに反映される

ような使いかたができます。


フォームで反映されたシートから直接、importrangeしてもいいですが、

IDを後からつけて整理するなら、

フォーム反映シート

→IDをつけた(たとえば)「list1」シート

フォームで「提出」「予定」「未提出」を書いてもいいし、list1で反映されない部分のところに直接書いてもいい。

→list1から、もう一度importrage、かつ、filterで「提出」のみのシート(ここでは「list2」)

→証拠説明書テンプレに、再びlist2をimportrange

のような形で、一気に証拠説明書が作成可能です。

6 concatenate関数(<2016-02-09 Tue> 記載)

6.1 関数


今回記載している、「concatenate」関数は、簡単にいえば、文字列をつなげる関数です。Excelにも、Googleスプレッドシート関数にもあります。

実際に表示すると、saimusyalistにある債務者(依頼者)のフリガナ、生年月日のデータを引っ張りだし、自動入力します。

表示的には、

( フリガナ ) ( S62.11.11生 )

などと表示されます。

この関数を使わないと、カッコを別のセルに入れる必要があり、位置が気に入らない、無駄なセルを挿入する必要があるなどとなります.

=concatenate(" ( ", if(VLOOKUP($A$30,saimusyalist!$B:$BL,50)="","",VLOOKUP($A$30,saimusyalist!$B:$BL,50))," ) ( ",if(VLOOKUP($A$30,saimusyalist!$B:$BL,52)="","",VLOOKUP($A$30,saimusyalist!$B:$BL,52)),"生 )")

関数は、1つずつで覚えても効率的ではありません。基本的なものは他に譲って、実際例を書いています。意外と、基本はわかるんやが、組み合わせたらどうするんじゃろ?というのが分からないので、自分のメモとしても書いています。

6.2 具体的なデータ処理の流れ


この例は、受任通知、特に、債務整理や破産などの超定型的な文章に有用です。

上記の例では、「50」のところには、依頼者の「フリガナ」が、「52」のところには、「生年月日」のデータが入っています。債権者に特定情報を与えるものですが、依頼者にとって(破産等で問題となる嘘をついていなければ)、ここが空白となることはないとはいえますので、if関数は必要ではないですが、一応入れています。


結果は、文字列となるので、再度計算はできません(さらに関数等をつかわなければ)。たとえば、引っ張ってくるデータが、数字の場合には、元のシートに、たとえば「40,000」となっていても、「40000」と表示されます。

具体的には、

統合住所録データから、filter関数とimportrange関数を用いて、依頼者のデータを取り出して「saimusyalist」に引っ張る、

vlookup関数を用いて、(上記の例では)「A30」のセルに、その依頼者のIDを入れる

ということで処理すると自動化が可能です。

6.3 使いみち


数字を純粋に扱うExcelやスプレッドシートが得意な場面ではあまり役に立つものではありませんが、別のセルにある文字列を結合等したり、セルが空きすぎてて1つずつに入れると綺麗ではない、そんなときに使えます。


7 int関数、rounddown関数(roundup、round)


以前書いたものもまとめておきます。




 裁判所の扱う金額、たとえば、利息計算などは、円未満は、切り捨てで処理されることが多いです。

おそらく、強制力を伴う国家権力というところが理由になってるんですね。切り捨て・切り上げとかは、結構おもしろく労働法上では、労働者に有利になるように切り上げとなることもあります。
ともかく、

=int(数値)

で、円未満切り捨てになります。

rounddown関数(切り上げ:roundup,四捨五入:round)でもできますが、桁数を入れないといけないので、普通は、int関数で。

10円以下切り捨てとか細かいときには、使えます。

=rounddown(数値等,桁数)

でします。

一番難しいのは、桁数ですが、

1の位が、「0」

小数点以下1位が「1」、小数点以下2位の位「0.1」の位が「2」

逆に、10の位が「-1」、100の位が「-2」、

となります。結果としての桁数を指定しますので、小数点以下2位を切り下げ等するときは、桁数としては、「1」を入れることになります。

忘れやすいので詳しくメモしました。

8 countif関数


countif関数は、個数を数える関数です。

文字列でも扱えるので、たとえば、表の「◯」がついた個数を数値で表すことができます。

=countif(範囲,"文字列等")

でします。

具体的には、たとえば、

=countif(C1:C30, "◯")

などと書きます。


…………………………………………………… 追記予定