Google

Googleフォームで生年月日を別々に入力させて後で加工したい

Googleスプレッドシート日付
記事内に商品プロモーションを含む場合があります

Googleフォームでイベントの申し込みフォームを作成しました。

生年月日を取得する必要があったのですが、年配の利用者も多いためなるべく入力しやすい方法を採用したいと思い生年月日を年・月・日と分けてみました。

特に年については年配の方は西暦変換できない方もいらっしゃいますので、和暦と一緒に表示するようにしています。

GoogleFormの画面設計

採用したのは上段の指定方法(年月日それぞれ入力)です。

後々の加工を考えれば下段の方法(年月日を一度に入力)が便利ですが、生年月日の場合かなり日付を遡らなければならないので、今回は上段の方法を採用しました。

GoogleForm日付指定

以下は年の選択リストです。必要となるであろう年はすべてリストに入力しました。

西暦指定

入力のユーザーインターフェイス(UI)は良くなったと思うのですが、後々データの加工が面倒です。

GoogleAppScript(GAS)でフォーム入力時に加工して処理する方法もあるのでしょうが、まだGoogleAppScript(GAS)は初心者レベルです。

このため、比較的簡単にできるスプレッドシートに関数を埋め込むことで日付型に変換することにしました。

年・月・日のセルから数字のみを取得してDate関数で日付を作成する

セルに計算式をセットする

スプレッドシートのサンプルです。

以下のように、年・月・日がそれぞれA~Cの列に格納されますので、DとEの列に値を生成したいと思います。

Googleスプレッドシート

 

  • 年は左4文字取得(left関数)
  • 月は”月”の文字を削除(substitute関数)
  • 日は”日”の文字を削除(substitute関数)

Date関数の引数は年、月、日です。

これで、D2セルに【1996/11/30】という日付型の値が取得できました。

Date関数の注意点

ところが、このDate関数に大問題が・・・。

なんと存在しない日付(2月30日、11月30日など)を指定すると勝手に自動計算して存在する翌月にまるめてしまいます。

例えば、2018年11月31日(存在しない)なら、2018年12月1日に・・・。

大迷惑な話です。

勝手に別の日を生成するのですから・・・。エラーになってほしいです

というわけで、更に別のセルに計算式を入れました。

もう一列追加

E2セルに、もし生成された日付が指定された月と違っていたら日付が自動的に変更されたとみなし「☓」を表記、正しければ日付を表記するようにします。

※ちなみにあらかじめ月の選択肢は1月~12月、日の選択肢は1日~31日としていますので、13月や0日は入力されません。

これで、正しい日付なら【YYYY/MM/DD】、そうでなければ【☓】を表記できるようになりました。

とっても便利なArrayFormula関数

さて、上記の計算式をセットすることで目的は達しましたが、最終的にはこの計算式を全行に適用したい訳です。

EXCELと同様、セルの右下をつまんで必要な行までドラッグすることで計算式はコピーできます。

ですが、行数が定かでない場合や行の追加時も勝手に計算式がセットされるようにしたい場合に、このやり方では不便です。

そこで、ArrayFormula関数の出番です。

EXCELにはない関数ですが、とても便利なので覚えましょう。

ArrayFormula関数の使い方

なんかよく分からなかったのは私だけでしょうか?

やってみるほうがよく分かります。

まずは簡単な例から・・・

上記のA列の左4文字を切り取る処理をArrayForumula関数を使って設定します。

=ArrayFormula(left(A2:A,4))

上記の計算式をB2セルに入力します。

Enterと同時に全行に値がセットされました。

便利ですね!

【left(A2:A)】はA2セルからAに値が入っている最後の行までという指定になります。

新たな行のA列に値を入れると自動的にB列にも値がセットされるようになります。

最初にご紹介した計算式をArrayFormula関数で実装する

この計算式をArrayFormula関数を使うと以下のようになります。

=ArrayFormula(date(left(A2:A,4),substitute(B2:B, “月”, “”),substitute(C2:C, “日”, “”)))

ちょっと長ったらしい計算式になりましたが実装できたのでOKです!

試してみてくださいね!

ABOUT ME
アズビーパートナーズ
プログラマー→社内SE→SIerのお仕事をしています。 メーカー勤務を経て、中小企業の社内SE、フリーランスなど様々な形態で働いてきました。 業務上生まれた困った…を解決してきたTipsを備忘録も兼ねて公開しています。 困っている誰かのお役に立てれば幸いです。