【続】Googleシート年号自動変換

こんにちは、ヒロです。

数あるブログの中から、僕のブログにお越しいただきありがとうございます。簡単に読める記事を目指していますので、ぜひ最後までお読みください。

このブログでは、デジタルトランスフォーメーション（DX）を促進するための自動化ツールの情報をお届けし、その結果生まれた時間を大切な人との時間や趣味、スキルアップに使っていただければと考えています。

昨日の記事の続編になります。小さな改良です。

まずは試してみた

昨日の記事で紹介したGoogleシートの便利な関数ARRAYFORMULAについて説明しました。ずっと前から気になっていたことがあります。

気になったこと

セルが空白かどうかを判断するISBLANK()の範囲がB列全体になっているのですが、どこまで空白かを確認しているのか？パフォーマンスに影響を与えていないか？（計算時間）

ググってみたもののそれらしい回答が見つからなかったのでChatGPTに聞いてみました。やはり、パフォーマンスに影響を与えるようです。

どんな改良？

ARRAYFORMULAが計算する範囲を絞りたいのですが、単純に1000行などにしたくないのです。いろいろテストした結果（これも一例です）以下の方法に変更しています。

最大行数をCOUNTA(B)で取得します。この関数のみ最大行数ChatGPTによると10,000行。最大行数が分かったので="B2"&M1で指定する範囲を作っておきます。その範囲の入っているセルを参照する関数がINDIRECTです。

コードをコピペして試したい方はどうぞ！

=ARRAYFORMULA(IF(ISBLANK(INDIRECT(O1)),"","令和"&TEXT(VALUE(YEAR(INDIRECT(O1)))-2018,"##")&"年 "&TEXT(INDIRECT(O1),"MM月DD日")))

なお、O1はARRAYFORMULAの範囲を作っています。="B2"&M1、M1は最大行数に5を加えた数字、=COUNTA(B)+5です。5を加える必要はないと思うのですが、最大行数を少し超える数字にしてみました。（おまじないですｗ）

ついでに年号の表現も整えてみました。

変数と固定値

プログラムの世界では、変数と固定値は毎回使いますね。ARRAYFORMULAで関数をコピペせずに計算させる際にも、変数と固定値をしっかり区別することにしました。年号自動変換では、変数として最大行数を取得することでARRAYFORMULAの計算範囲を変化させるようにしています。

セルが空白であることを確認する別の方法

今回、空白行を検出したら計算をしないようにしています。この範囲を制限する設定をしないとエラーメッセージ#N/Aなどが表示されます。

YouTubeでARRAYFORMULAを検索していたら、海外の方ですが、文字数が0であったら計算をしないようにしていました。使用関数はLEN()です。

ストレートに空白かどうかをチェックすることも、文字数が0かどうかを確認することも、どちらの方法も動作しています。

どちらが適切なのかは分からないので、もし情報を見つけたら次回シェアします。

まとめ

ARRAYFORMULAを使って、「おーーーできた！」と喜ぶだけではなく、改良を続けることが重要です。以下のポイントに注意して、さらなる効率化を図りましょう。

1. 範囲の最適化：計算範囲を絞ることで、パフォーマンスの向上を目指します。不要な計算を減らすことで、シート全体の動作がスムーズになります。

2. 関数の工夫：ISBLANK()やLEN()を使ってセルが空白かどうかを判断する際、どちらの方法がより適しているか検討し、自分のニーズに合った方法を選びましょう。

3. 変数と固定値の区別：プログラム内で変数と固定値を明確に区別することで、可読性とメンテナンス性が向上します。特に、最大行数を動的に取得するようにすると、シートの構造変更にも柔軟に対応できます。

自動化によって生まれた時間を有効に活用し、趣味やスキルアップ、家族との時間を大切にしましょう。時間管理の効率化は、ワークライフバランスの向上にもつながります。

最後に、今回の改良が皆さんの作業効率向上に役立てば嬉しいです。これからも引き続き、便利な自動化ツールや技術の紹介をしていきますので、お楽しみに。今後もブログをご愛読いただければ幸いです。