見出し画像

【Excel】連続データの最速生成★SEQUENCE関数

こんにちは、HARUです!

実務では、名簿やスケジュール表などの様々な用途で「連続データ」を並べたいことがよくあります。

サンプルのように対象のデータが10件や20件程度でしたら、順に手入力していってもさほど手間ではありませんが、1000件、10000件、100000件と増えていくと、気が遠くなるほど相当な時間がかかってしまいます。

このようなシーンでの実践術として、「連番」を素早く生成する方法を別の記事でご紹介しています。
↓解説記事はこちら↓

前回テーマとした「数値」に加えて、今回は日付・時刻表記、アルファベットや記号との組み合わせなど、連続データのバリエーションを増やしていきましょう。

ぜひ最後までご一読ください!


連番の生成方法(おさらい)


まずは数値で連番を作成する方法をおさらいします。
やり方は大きく5つで、いずれも連番を30000件生成するケースを見ていきます。


オートフィル

一つ目の方法は、隣接する列に途切れることなくデータが入力されているケースで使えるやり方です。

①「1」「2」と入力する。
②この2つの値(セル)を選択する。

③右下のフィルハンドルをダブルクリックする。

これによりオートフィルが発動して、隣のデータが続く限り一気に連番が生成できます。

ただし、実務ではまっさらワークシートに一から採番しなくてはならないシーンも往々にしてあります。

そんなとき、ここからご紹介していくテクニックが効力を発揮します。 



連続データの作成

二つ目の方法は、連続データの作成です。

①A1セルに「1」と入力する。
②[Alt]→[H]→[F]→[I]→[S]と順に押す。
③「連続データ」のダイアログボックスで必要な設定を行う。

増分値:連番の規則性として、いくつずつ増やすかを指示する。
今回は1、2、3と1つずつ増やすためデフォルトの「1」のままとする。

停止値:連番の最終値を指示する。
今回は最大値の「30000」を入力する。

範囲:連番を生成する方向を指示する。
今回は縦方向に記述するため「列」を選択する。

種類:連続データの種類を指示する。
今回は数値を1つずつ足しこむため「加算」を選択する。

これにより、1~30000の連続データが生成できます。



数式をコピーする

三つ目の方法は、簡易的な数式をコピーするパターンです。

①先頭のセルに「1」と入力する。
②1つ下のセルに「上のセル+1」という数式を入力する。

この数式を30000件コピーする場合、対象となるセル範囲をあらかじめ選択する必要があります。
ここで、マウスでぐいーーんと対象範囲を広げていくのは大変ですよね。

こんなときは、
③数式が入力されたセルをアクティブにして[Alt]+[F3]を押す。
④「名前ボックス」に、"A30000"と入力する。
⑤[Shift]+[Enter]を押す。

すると、A2セルから、A30000セルの範囲が選択されます。

最後に、
⑥[Ctrl]+[D]で下方向にコピーする。

これにより「上のセル+1」という数式が対象範囲に一括反映されます。
必要に応じて、数式の部分を値貼り付けで数値に変換しておきましょう。

※[Alt]+[F3]による名前ボックスの選択は、Excel2016以降のバージョンが対象です。もし[Alt]+[F3]が機能しないときは、[Ctrl]+[G]もしくは[F5]で「ジャンプ」を呼び出しましょう。参照先欄で、同じ操作が可能です。



ROW関数

四つ目の方法ではROW関数を使います。

このROW関数は、指定したセルの行番号を返してくれる関数です。
特に参照セルを指示しなければ、このROW関数が入力されているセルの行番号が返ります。

あとの手順は前述の「名前ボックス」や「ジャンプ」による一括範囲選択と同じです。

対象範囲を指定したら、[Ctrl]+[D]でコピーします。
※もし書式を引き継ぎたくない場合は、[F2]で編集モードにしてから、値や数式を一括入力する[Ctrl]+[Enter]で実行しましょう。


▶ROW関数のメリット
ROW関数で採番した場合、たとえ行を削除したり入れ替えたりしても、連番が崩れないというメリットがあります。

この特長を活かして表を管理したいときは、値貼り付けはせず数式を残しておきましょう。

↓行を移動・削除・非表示にしても連番を成立させるには↓



SEQUENCE関数

最後に使うのは、SEQUENCE(シーケンス)関数です。
sequence:連続、一続きの、順序

※Microsoft365、またはExcel2021で追加された関数です。
使える環境が限られるアイテムではありますが、作業としては最もシンプルです。

①"=SEQ"まで入力する。
※ここでSEQUENCE関数が出てこなければ、残念ながらこの関数は使えません。

②[Tab]で関数を挿入し、第1引数「行」に"30000"と入力する。

これで決定すると「スピル」が発動し、30000件の連番が自動生成されます。

↓「スピル」の解説記事はこちら↓


前置きが長くなりましたが、今回はこのSEQUENCE関数を深堀りして連続データのレパートリーを増やしましょう。

まずはSEQUENCE関数の基本構成を見ていきます。


SEQUENCE関数の基本構成


行・列

前述の通り、SEQUENCE関数の必須引数は第1引数のみです。

第1引数「行」に"10"と入力すると、行単位で10件の連番がスピルで生成されます。

第1引数「行」は必須引数ですが、"0"と入力したり、カンマで区切ることで省略可能です。
この状態で第2引数「列」に"3"と入力すると、列単位で3件の連番が生成されます。

なお「行」「列」は値を返すセル数を指示するため、1未満の小数値やマイナス値を入力するとエラーが返ります。
必ず1以上の値を設定しましょう。


行・列の複合設定

第1引数「行」に"10"、第2引数「列」に"3"と入力すると、行単位で10件、列単位で3件の連番が生成されます。

この場合、連番が横→縦のZ型に並びます。

SEQUENCE関数単体では生成方向を変えることはできないため、参照範囲の行列を入れ替えるTRANSPOSE関数にネストし、SEQUENCE関数の第1引数「行」と第2引数「列」を入れ替えます。
(今回の場合、「行」を"3"、「列」を"10"とする)

これにより、連番が縦→横の方向に並びます。


開始位置

第3引数「開始」で連続データの開始位置を指示できます。
(下図は開始位置を"6"とした状態)

開始位置には、"0"や負の値、小数値なども設定できます。


間隔

第4引数「目盛り」で連続データの間隔を指示できます。
(下図は間隔を"2"とした状態)

間隔にも、"0"や負の値、小数値を設定できます。



それぞれの引数をお好みで設定することで、データ生成の幅が広がります。
(下図は行10、列2、開始5、目盛り5とした状態)



一連の基本動作により、SEQUENCE関数では規則的な数値データを任意の数量だけ生成できることがわかりました。
デフォルトでは1ずつ加算されていき、第4引数「目盛り」に負の値を設定することで減算させることも可能です。

ここで、指定の数だけ規則的な連番を返すSEQUENCE関数に加え、指定の数だけ不規則な乱数を返す関数もご紹介しておきます。



RANDARRAY関数


RANDARRAYは「rand:乱数、ランダム」「array:配列」を組み合わせた造語です。

RANDARRAY関数はSEQUENCE関数と同じく、Microsoft365またはExcel2021以降で使える関数です。


基本構成

RANDARRAY関数には必須の引数がなく、何も入力しなければランダムで1未満の小数値が1つ表示されます。

[F9]を押すと再計算が実行され、返される値が更新されます。


第1引数「行」、第2引数「列」はSEQUENCE関数と同じで、それぞれに指示した数だけ乱数が生成されます。


第3引数「最小」、第4引数「最大」には、乱数をどの範囲の値で発生させるかを指示します。

最小を"1"、最大を"100"とした場合、その範囲の小数値がランダムで表示されます。


デフォルトで小数値が返されるRANDARRAY関数も、第5引数「整数」で整数値を設定できます。

整数値を選択した場合、返される値が整数になります。




連続データの生成例


SEQUENCE関数とRANDARRAY関数の仕様を活かし、いくつかの連続データを生成してみましょう。


管理コード

特定の文字列と連番を組み合わせることで、管理コードや文書登録番号などの規則的な連続データを作成できます。

①SEQUENCE関数で100件の連番を生成する。
②1桁や2桁の数値でも"001"の3桁表示となるよう、TEXT関数でネストし、TEXT関数の第2引数「表示形式」を"000"とする。
③上記①②の先頭に"&"(アンパサンド)でアルファベットを連結する。


パスコード

桁数のみが指定されている認証番号やパスワードなど、値をランダムで採番したいケースで活用できます。

①RANDARRAY関数で1~9999の乱数を100件生成する。
②どの桁数の値が返っても4桁表示となるよう、TEXT関数でネストし、TEXT関数の第2引数「表示形式」を"0000"とする。


料金体系

レンタルスペースやアクティビティ施設の利用料金表など、最低使用時間(開始)と料金の変動単位(目盛り)を設定することで、一定間隔の料金体系を作成できます。

①SEQUENCE関数で、"3"時間を最低使用時間とし、料金を"3"時間ずつ増加させる連番を"16"件(48時間分)生成する。
②&で"時間"と連結する。※表示形式で"時間"を表記してもOK。

①SEQUENCE関数で通常価格を1,550円から350円ずつ加算し、会員価格は1,500円から300円ずつ加算する。
②表示形式を通貨(¥記号)に変更する。
※通貨(¥記号)の表示形式は[Ctrl]+[Shift]+[4]で設定する。


時間帯ごとの来店人数

規則的に値が増減するSEQUENCE関数は日付や時刻との相性がよく、指定の日数や時間間隔でデータ分析やスケジュール管理を行いたいときに重宝します。

①DATE関数で2023年7月1日のシリアル値を取得する。
②上記①のDATE関数をSEQUENCE関数の第3引数「開始」に入れ込み、これを開始位置とする連番を31件生成する。
②表示形式を"●月●日"に設定する。

①SEQUENCE関数で、"10"時から"2"時間おきの値を"5"件返す。
②上記①をTIME関数の第1引数「hour」にネストし、時刻として認識させる。
③表示形式を"●●:●●"に設定する。




いかがでしたか?

RANDARRAY関数の活用シーンは限定的かもしれませんが、ぜひSEQUENCE関数とセットでおさえておきましょう。

ちなみに我々スキル配信者は講座収録用のダミーデータ作成でRANDARRAY関数をフル活用しています。
今回の記事でサンプルとした製品の価格や仕様書No.、時間帯別の来店人数などもすべてRANDARRAY関数で生成した値を使いました!

Excel操作の練習や関数の動作確認用に、ぜひ使ってみてくださいね!



↓様々な関数を組み合わせてアルファベットの連続データを作成する方法はこちら↓



↓↓Excel操作をとにかく高速化したい方へ↓↓


この記事が気に入ったらサポートをしてみませんか?