新しいExcelでテストデータを作ろう

新しいExcelに搭載される関数を使うと、いろいろなテスト自動化に使えそうなテストデータを量産できそうなので方法を紹介します。
OfficeInsider環境で調べております。

SEQUENCE関数

SEQUENCE関数は一覧表を作る関数です。
「一覧表を作る？Excel関数で？何言ってるかよくわからないんですけど」
みたいに思うと思いますが、実際に一覧表が出来上がります。
書式は

=SEQUENCE(一覧表の縦サイズ,一覧表の横サイズ,開始値,増減値)

です。
セルA1に次の計算式を入れてみます。

=SEQUENCE(5,5,101,1)

これ、セルA1に入れただけです。
新しいExcelはこんな感じで、範囲に出力できる計算式を作ることができるようになりました。
この機能を「スピル」と呼びます。
スピルに関してはこちら↓
新しいExcelの一番の変更点は「スピル」？

RANDARRAY関数

同じようにスピルする関数で、乱数の一覧表を作成するRANDARRAY関数があります。
書式は

=RANDARRAY(一覧表の縦サイズ,一覧表の横サイズ)

です。
出力値は0以上1未満（検証して以内ではなさそう）です。
セルA1に次の計算式を入れてみます。

=RANDARRAY(3,5)

という乱数一覧が表示されます。
「整数を入れたい」と？
関数を整数になる関数でくくればいいのです。

=INT(RANDARRAY(3,5))

「それじゃ0にしかならない？100から200の値を入れたい」と？
乱数に100かけて100を足せばいいです。

=INT(RANDARRAY(3,5)*100)+100

「16進数でほしい」と？
16進数に変換する関数を使えばよいです。

=DEC2HEX(INT(RANDARRAY(3,5)*100)+100)

といったように、このようなスピルする関数も他の関数と組み合わせができます。

関数の組み合わせでテストデータを作る

さて、ここから、他の関数と徹底的に組み合わせて一覧表を作成したいと思います。
まず、１行目はフィールド名にしましょう。
今回は、学校のテストの成績表を作ってみます。
フィールドは、生徒名、国語、英語、数学、理科、社会の６つとします。
生徒名は、生徒1から始め、9人分のデータを作成します。

項目名

1行目は数値の順に文字列を出していくCHOOSE関数を使います。

=SEQUENCE(1,6,1,1)

で1行に1～6の数値が求まります。
それにCHOOSE関数を組み合わせます。

=CHOOSE(SEQUENCE(1,6,1,1),"生徒","国語","英語","数学","理科","社会")

これで1行目を項目名とすることができます。

生徒名

次に2行目以降の1列目に生徒名を「生徒」と行番号を組み合わせていきます。
SEQUENCE関数の値を6で割って整数にすればこの行番号になります。なんか行番号を求めるROW関数ではうまく動かないので使いません。

="生徒"&INT(SEQUENCE(9,6,1,1)/6)

点数

最後に2行目以降のランダムな点数を求めます。0から99点でよければ、次の計算式です。

=INT(RANDARRAY(10,6)*100)

9人分なのですが、表の範囲全ての分を入れないと、データがないよと言われたので、10行分作ります。
これで求めるものすべてが出来ました。

1行目の検出

今度はそれを条件で分けていかなければなりません。
1行目を検出するには、次の数式で検出できます。

=SEQUENCE(10,6,1,1)<=6

やはり10行分用意しています。安全を見ています。
で、6以内であれば1行目と判断しています。

1列目の検出

次は生徒名を入れる1列目の検出ですが次の計算式です。

=MOD(SEQUENCE(10,6,1,1),6)=1

MOD関数で6で割った余りを求め、その値が1ならば1列目です。

IF関数でジョイントする

以上をIF関数で分岐させていきます。
1列目ならば項目名、1列目なら生徒名、それ以外は点数となります。

=IF(1行目なら,項目名,IF(1列目なら,生徒名,点数))

という式に上記のものを組み合わせると次のようになります。

=IF(SEQUENCE(10,6,1,1)<=6,CHOOSE(SEQUENCE(1,6,1,1),"生徒","国語","英語","数学","理科","社会"),IF(MOD(SEQUENCE(10,6,1,1),6)=1,"生徒"&INT(SEQUENCE(10,6,1,1)/6),INT(RANDARRAY(10,6)*100)))

ということで出来上がりです。

まとめ

なんと一覧表が1つの計算式で出来てしまったのです。
これが新しいExcelの目玉中の大目玉かもしれません。
組み合わせることができる関数とそうではない関数がありますので、作成し試しながら作っていくことになるでしょう。
ランダムなテストデータを作る時は、n進数を求める関数や、値を様々なフォーマットに変換できるTEXT関数などと相性がいいかと。