スプレッドシートでテストデータ作成マシーンを作る技術

この記事は MicroAd Advent Calendar 2023 の 12日目の記事です。

今回はテストデータ作成の工数を抑えるのに大活躍してくれるスプレッドシートの関数たちの紹介をしていくぜ！

（順位はいつもよく使うなあくらいの感触で適当につけたので当てにしないで下さい）

第1位: ARRAYFORMULA
第2位: IF
第3位: TRANSPOSE
SUBSTITUTE
SPLIT と INDEX
TO_TEXT

第1位: ARRAYFORMULA

そこのあなた、いつまでセルの右下のポチにカーソルを合わせてドラッグし続けているんですか？
テストレコードの数が数十行くらいならまだしも、それが何百行も何千行にもなったらどうするおつもりで？

重くなったスプレッドシートで画面の下までカーソルをドラッグして左クリックしたまま30秒くらいキープしないといけないかもしれませんね。

ダブルクリックすれば下まで適用してくれるやん。
じゃあ、それが何十カラムもあったら？

そんな"カーソルをセンターに入れてクリック"の手間を省いてくれるのがARRAYFORMULA関数。

使い方は、例えば

=ARRAYFORMULA(A1:E12345)

みたいな感じでやるとA1~E12345の範囲の情報をそのまま持ってきてくれたり

=ARRAYFORMULA(A1:C20&"😭")

ってやればA1からC20までの内容に泣きっ面をつけてくれたりします。

もちろん関数内から関数を呼ぶこともできるので、

これがあれば例えレコードが1万行あれど1セルしか変更しないので手間なしですね！

ただし注意点としては元から1引数に複数セルを受け取る関数（例えばCONCATENATEとかJOINとか）については同様の呼び出しができません。

第2位: IF

紹介するまでもないのは承知ですけど、これがないと何もできないレベルで必須関数。
よくある使い方は、例えば

=IF(F18="JAPAN","日本国籍","外国籍")

みたいな感じですが、これをARRAYFORMULAと組み合わせると

=ARRAYFORMULA(
    IF(
        C2:J2="TIMESTAMP",
        "2023-12-12 00:00:"&TEXT(A5:A9,"00"),
        IF(
            C2:J2="BIGINT",
            "1234567890"&TEXT(A5:A9,"00"),
            A5:A9
        )
    )
)

のように多重IFでテストデータ自動作成も可能です。

第3位: TRANSPOSE

新しいパターンの表を作るときにはよく使わせてもらっているTRANSPOSE関数。
ドキュメントとかからコピーしてきたテーブル定義が別シートにあったとして

これをTRANSPOSEすると

いい塩梅でテストレコード記述しやすくなったりします！

SUBSTITUTE

これはもうドキュメントの例がいっちゃんわかりやすいので使い方は下記の通り。

SUBSTITUTE("search for it","search for","Google")

テストデータとして用意した文字列の一部だけを消したい場合とかによくお世話になります。

ちなみに同じような名前でREPLACE関数がありますが、そっちは文字のインデックス番号で入れ替え対象の部分を指定しているのでちょっとやりたいこととは違います。

SPLIT と INDEX

文字列で準備した配列データの３番目だけを取り出したい！！
そんなときにこの２つを使います。

まずは上で紹介したSUBSTITUTEとSPLITを使って配列を分解します。

=SPLIT(
    SUBSTITUTE(
        SUBSTITUTE(B2,"[",""),
        "]",
        ""
    ),
    ","
)

SUBSTITUTEが重なってるのでちょっと見づらくなっちゃってますが、やってるのは
・SUBSTITUTEで括弧を外す
・","で分割
だけです。難しくないでしょ？

これの3番目の要素だけが取りたい！ってなったときに、今度はINDEX関数を使います。

=
INDEX(
    SPLIT(
        SUBSTITUTE(
            SUBSTITUTE(B2,"[",""),
            "]",
            ""
        ),
        ","
    ),
    3
)

これもまた階層深くなってわかりづらいですが、やっていることは

INDEX(SPLITで分割したやつ, 何番目)

の指定だけです。簡単でしょ？

余談ですがこのINDEX関数は値の取得にも使える有能くんで、

=INDEX(A:A,COUNTA(A:A))

みたいな書き方をするとA列の一番下の値を取得、みたいな働きもしてくれます。
スプレッドシートでヒストリカルデータを扱ってる時とかはこれも活躍してくれるかもしれないですね。

TO_TEXT

テスト結果の比較をうまいことスプシでやろうとしたら

いや、勝手に少数部削らんといて…とか、

なんか対数になってる…とか、スプシが数字を"いい感じに"扱ってくれちゃったからこそ不都合が起きちゃうこと、よくありますよね。

そんなお悩みもTO_TEXT関数があればすぐ解決。

=TO_TEXT(12345678901234567890.1)

ってやれば文字列として扱ってくれるのです。これでもうセルごとに表示形式をいじいじしなくていいですね。

おまけ: GPT

弊社は生成AIの利用に規制があり業務には利用していませんが、OpenAIのトークンさえ持っていればSpreadsheetに生成AIの力を組み込むことも可能です。

こちらのアドオンを入れると、例えば

=GPT("Create an example query to insert a test record to the followiing table ...")

みたいにしてChatGPTからの応答をそのままセルに展開したり

=GPT_TABLE("Create 5 sample records", A1:E1)

でA1:E1のカラムを埋める5レコード分のサンプルを作ったりできちゃうんです。いい例は上のアドオンをご覧ください！

まとめ

上で紹介した仲間たちを使えば、テストデータの作成は（初期コストはかかるけど）だいたい自動化することができちゃいます。何度も使うテーブルにデータ入れそうなときには使ってみてはどうでしょう？

ちなみにマイクロアドの一部の開発チームでは

=ARRAYFORMULA(
    IF(
        C2:J2="TIMESTAMP",
        "2023-12-12 00:00:"&TEXT(A5:A9,"00"),
        IF(
            C2:J2="BIGINT",
            "1234567890"&TEXT(A5:A9,"00"),
            A5:A9
        )
    )
)

これを10種類近くの型ぶん作って500レコード以上のテストデータを作っているらしいですよ？（すっとぼけ

何はともあれ有能なスプシ関数さんたち。
皆さんも仲良しになってテスト工数を抑えて素敵なクリスマスを迎えましょう！
メリークリスマス🎄