メインコンテンツへスキップ
見出し画像

【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操作をとにかく高速化したい方へ↓↓


     
     
    元東証上場企業の事業企画職|サラリーマン稼業の傍ら、20万人以上登録のITスキルメディアを個人運営|書籍「Excel超時短メソッド」発売即重版→ http://amzn.to/38Nq5Qx|可愛い2児のパパ🍼|Noteでは有益スキル情報を文字と図解で徹底解説📝

    あなたへのおすすめ