2016年2月6日土曜日

Googleスプレッドシート関数:FAX送付状の自動化

H280206記載
主にGoogle Apps Scriptを用いて、証拠説明書を自動化したので、今度は、主に、関数を用いてFAX送付状を(ほぼ)自動化します。試行錯誤しながら綺麗な書面を作っていき、後は、IDの数字を入れれば、ほぼできる。そんなのを目指します。







   

1  Googleスプレッドシート関数:FAX送付状の自動化


証拠説明書が、どちらかというと、Google Apps Scriptを用いたものですが、このFAX送付状は、Googleスプレッドシート関数を用いて作ったものです。


2  コンセプト


FAX送付状は、意外と作るのが面倒です。事務に指示するにも、その指示がややこしかったりします。事務所効率のために、「ID」を入れるだけで、一気にFAX送付状を作成する。

・ 住所、FAX番号、宛名等は、住所録を利用する。

ことにして、必要な分はFAXシートのフォーム(以下「FAXフォーム」という)で作ります。

3  使用するGoogleスプレッドシート関数

3.1  使用する主な関数


使用する関数は、実はかなり限られています。

sum,todayなどの極めて基本的なものは別として、


でまとめています。


3.2  Importrange関数


まいどおなじみImportrange関数です。これだけで、Googleスプレッドシートを利用する価値がある、利用価値が極めて高い関数です。Excelにはないようです(<2016-02-05 Fri> 現在)

住所録から、主として、郵便番号、住所、FAX番号、宛名などを引っ張る、さらにFAXフォーム回答シートからデータを引っ張るのに使います。

=importrange("YourGooglesheetID","'シートの名前'!H9")

意外と迷うのは、シートの括り方です。デフォルトフォーム回答シートの名前(「フォームの回答 1」)(これやめてほしいです)のようにスペースが入るものなどは、「'フォームの回答 1'」と書きます。 (メモとして残しました)

3.3 vlookup関数


これが本件の肝となる関数です。

vlookup関数を利用して、ID番号の数字を入力するだけで、全てを一気に作成できるようにします。

=if(vlookup($A$3,'フォームの回答 1'!$a:$o,7)="","",vlookup($A$3,'フォームの回答 1'!$a:$o,7))

ifの構文に入れ子をしているだけです。

「$A$3」(絶対参照を使い貼付けしても数字が変わらないようにしている)

この数字に対応したものを探して7番目の値が何もなければ、空白を、
そうでなければ(値があれば)、その対応した数値
を入れます。これにより、数値がない場合にエラー値が表示されなくなります。



4  事前準備

4.1  住所録統合シートのimportrange


まずは、住所録統合シートのimportrageをします。

具体的にいえば、FAXフォーム回答シートと同じスプレッドシートにもう一枚シートを追加して(ここでは「list1」)、ここに、住所録統合シートをimportrangeします。

直接できないこともないですが、IDを確認したりするのに二つのスプレッドシートを行き来するのも大変です。


4.2 フォームの作成とFAXフォーム回答シート


必要な項目は、住所録に載っていない情報で、フォームで書いたほうがいい項目です。

 今、私のフォーム項目では、
 
・「ID(住所録と同じもの)」

・「送付書類1」

・「送付書類1」の「枚数」

・同2,その枚数

・同3、その枚数

・「詳細」(説明する部分です)

ぐらいが入っています。

 なるだけシートを直接さわらず、動くものはなるだけフォームでする
というコンセプトでもあります。

 これを、テンプレ送付状シートに関連付ける(テンプレ送付状シートでフォームを作成すれば自動的)。

5  具体的な流れ



・住所録に住所等が入っている場合には、FAXフォームだけに入力すればよいということになります。

FAXフォームの項目に、住所録と一致したIDを入れます(ここが今回のキモ)。


・住所録に住所等が入っていない場合には、先に住所録フォームを用いて、必要な項目を入れます。


そこで付与されたIDを用いて、FAXフォームに入力をします。

6  テンプレ送付状シート

6.1  めげそうですが、もう少しです。


実は、これがかなり面倒です。Excelで書面を作る感覚です。少しづつ不具合(印刷の調整等)を直しながら試行錯誤します。

スクリーンショットで1つずつ説明を加えます。

6.2  1つずつの説明

6.2.1 3A: 


ID入力するところ。

ここに数字を入れると全てが変わるようにします。

住所録と同じIDを用いることで、「3A」の数字が、住所録からのvlookupとフォーム回答からのvlookupに同時に作用するということになります。

6.2.2 2D: 

送付枚数の合計数が入っています。ここは、特に関数を入れていません、

6.2.3 2A: 

ここは、「=today()」関数で本日の日付が入ります。
Googleスプレッドシートは、元号が入らないのが、少し不満な点ではあります。

6.2.4 6D: 

ここは関数(if,vlookup関数)が入っています。if関数で空白の場合には空白、FAX番号が入っていればFAX番号を入れるもので、住所録から引っ張ってきたデータです。

6.2.5 8B:

郵便番号のマーク「〒」は固定です。住所がない場合でも、それほど違和感はないので「郵便番号」が空白でも残っています。

この送付先項目は、すべて、住所録からvlookup関数を用いて引っ張っています。
(12Gも)

6.2.6 19D、20D,21D,22D:

これは、フォーム回答シートから引っ張っています。送付書類です。
大体は、4つぐらいあれば足ります。もっと増やす場合には、枠を増やします。

6.2.7 19E,20E,21E,22E:

これもフォーム回答シートから引っ張っています。枚数です。

6.2.8 17L:

これは、sum関数で合計をしています。合計枚数はあったほうがいいですね。FAX送付状は、単に礼儀の問題だけではなく、何を何枚送ったかを記録しておくことにもあります。
ちなみに、15行にある項目は、固定です、印刷してから、チェックするというアナログの方式が一番はやいです。なんでもデジタル化すればよいというものでもないとおもいます。

6.2.9 28,29,30以下:

この28のところだけが、フォーム回答シートからの取り込みです。
後のは、固定です。
テンプレには、下の方に受領書も入っています。
受領書が要らないときは、印刷するときに範囲指定外におけば出力されません。

7  最終調整、書式の整え

見栄えからすれば、ここが一番むずかしい(私も試行錯誤中)

とりあえず、できました!

これで、先ほどの3AにIDの数字を入れるだけで、入力したもの、住所録から引っ張ってきたデータが反映されます。


後は、印刷して職印を押して完成です。


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