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

【Excel】これできたの?!アルファベットの連続データ生成★

    こんにちは、HARUです。

    今回は、アルファベットで連続データを作成する方法をご紹介します。
    Excelで管理するデータに何らかの採番をするとき、数字の他に、英字を用いることもありますよね。

    数値の場合はオートフィルを使えば自動で連番にできますが、同じことをアルファベットで行おうとしても、入力された文字列を繰り返すだけとなってしまいます。

    画像

    そこでこの記事では、"ABC"の連続データをサクッと生成するテクニックを解説します。

    アルファベット自体は26個しかありませんが、「A」から始まり、「Z」に到達したら次は「AA」、「AZ」に到達したら次は「BA」、「ZZ」まできたら次は「AAA」と、規則的に増やしていくしくみも後述します。

    画像

    ぜひご一読ください!


    方法①文字コードの活用


    今回は大きく2つの方法をご紹介します。

    1つ目の方法では、CHAR関数とCODE関数を用います。
    実際にこれらの関数を使う前に、Excelにおける文字コードのしくみを解説しておきます。

    文字コードとは?

    ワークシート上に入力できる文字列は、1文字1文字、コードが採番されています。

    たとえば、ひらがなの「あ」は9250、カタカナの「ア」は9506、アルファベットの「A」は65、小文字の「a」は97、春という漢字は「15701」、記号の「#」は35と、いった具合です。
    また、文字コードは数字にも設定されています。「1」は49ですね。

    画像

    このうちアルファベットを並べてみると、大文字の「A」から「Z」が65~90、小文字の「a」から「z」が97~122と、それぞれ通し番号となっています。

    画像

    ちなみに全角文字と半角文字でも文字コードは異なります。半角大文字の「A」は65ですが、全角大文字の「A」は9025です。

    画像

    この記事では一貫して、半角文字をサンプルにしていきます。


    CHAR関数とCODE関数

    この文字列と文字コードと関係を見える化してくれるのが、CHAR(キャラクター)関数とCODE(コード)関数です。

    ※CODEはなんとなく連想しやすいですが、CHAR関数のcharacterは、「性格」や「登場人物」、いわゆるアニメのキャラクターのような使われ方に加えて、ズバリ「文字」といった意味があります。

    文字列を参照して文字コードを取り出すのがCODE関数、
    文字コードを参照して文字列を取り出すのがCHAR関数、
    なんですね。

    画像

    2つの関数を応用すると、以下の手順が成り立ちます。
    ①CODE関数でアルファベット半角大文字”A”の文字コード65を取り出す。
    ②↑①のCODE関数をCHAR関数にネストして”A”と表示させる。

    画像

    ③1つ下のセルにCODE関数を入力し、上のセルの文字コード+1として66を取り出す。
    ④↑③のCODE関数をCHAR関数にネストして”B”を返す。

    画像

    あとは2つ目の数式をオートフィルで拡張すると、アルファベットの連続データが生成されます。

    画像

    この数式を同じ要領で右に入力すれば、横方向にも連続データを並べられます。

    画像

    もし小文字のアルファベットで生成するときは、最初の数式を小文字の”a”に変えてしまう、または入力する文字列は大文字のまま、この数式をLOWER関数でネストします。

    画像
    小文字の"a"を直接入力した場合
    画像
    LOWER関数にネストした場合

    やりやすい方を使ってみてください!


    なお、上のセル+1、または左のセル+1の部分にROW関数やCOLUMN関数を入れ込んで行番号・列番号とリンクさせる方法もありますが、表の加工の仕方によっては、参照がズレてしまう場合もあります。

    画像

    実務で扱うデータや表の更新頻度に応じて使い分けていきましょう。
     


    方法②列番号の取り出し


    さて、方法①の数式を試しに「Z」以降に伸ばしてみると、全然違う文字列が出てきます。

    画像

    ここまでの手順ではアルファベット1文字ごとに採番された文字コードを応用していたので、「Z」に到達したら「AA」、「AZ」に到達したら「BA」といったしくみは実現できないんですね。
    あくまで必要な連続データが少ないケースで使えるテクニックということです。

    そこで2つ目の方法では、Excelのワークシートにすでに存在しているアルファベットの連続データを活用します。

    勘の良い方は気づかれたかもしれません。

    …そう、「列番号」ですね。
    Excelの列番号は、Aからスタートして、XFDまで、最大16,384列あります。

    画像

    この列番号を取り出して上手く流用できれば、規則的にアルファベットが増えていく連続データを構築できます。
     

    ADDRESS関数

    列番号を含むセル番地を取り出すために、ADDRESS関数を使います。
    ADDRESS関数は、指定した行番号と列番号のセル番地を返す関数です。

    構成としては、第1引数に行番号、第2引数に列番号を指定します。
    もし抽出したいセル番地が「A1セル」であれば、1行目なので行番号は「1」、A列は端から1列目なので、列番号も「1」を入力します。

    画像

    いずれも数値で指示するんですね。
    以降の引数は任意なので、一旦これで決定します。
    すると、以下のようにセル番地が表示されます。

    画像

    これがたとえば「C4セル」を返したければ、第1引数の行番号は4行目なので「4」、第2引数の列番号は、C列が端から3列目なので「3」と指示することになります。

    ただ、連続データを作る過程において絶対参照だと都合が悪いので、参照形式を変更します。
    参照形式は第三引数の「参照の種類」で設定します。

    画像

    ここに「1」もしくは何も入力しなければ絶対参照、「2」もしくは「3」であれば行または列だけ固定する複合参照、そして、「4」を指定すれば相対参照になります。
    今回は、相対参照にして決定します。

    画像

    $マークが消えましたね。

    COLUMN関数 

    次に、引数が固定値のままだと、たとえこれをコピーしても延々とA1セルを取り出すことになりますので、自動的にB列、C列と参照を変えていく構造にする必要があります。

    画像

    ここでは、第2引数の列番号にCOLUMN関数を入れ込みましょう。
    COLUMN関数は先ほど少し触れた通り、参照セルの列番号を数値で取り出す関数です。

    特に参照元を指示しなければ、この数式が入力されているセルの列番号を返しますので、以下の内容で決定します。

    画像

    オートフィルで引っぱってみると、列番号が上手くスライドしていきます。

    画像



    SUBSTITUTE関数 

    さて、今回取り出したいのはアルファベットだけなので、行番号の1が邪魔ですよね。これを、削除していきましょう。

    ここで使うのは、SUBSITUTE関数です。
    substituteは「代わりの」や「置き換え」を意味する英語です。
    SUBSTITUTE関数は、参照した文字列から指定の文字を探して、さらに指示した文字列に変換、置き換えてくれる関数です。

    今回は取り出したセル番地から、行番号の1という数値を隠したいので、
    第1引数の文字列は、ADDRESS関数で取り出したセル番地、もともとあった数式そのままです。
    第2引数の検索文字列は行番号の「1」です。
    第3引数の置換文字列は、何も表示したくないのでダブルコーテーションで何もかこまない「""」、要は空白を指示します。

    画像

    これで決定すると、純粋にアルファベットだけが表示されます。

    画像

    小文字にしたいときは、さらにLOWER関数でネストしましょう。
     

    この数式を横にしばらく拡張してみると、求めていた規則性が実現できています。

    画像

    ただ、必要な連続データが大量にあるとき、ぐいーーんと引っ張るのは面倒ですよね。

    こんなときは「A1セル」がアクティブな状態で、名前ボックスに:(コロン)、そしてたとえば「AAA1セル」と入力して[Enter]で決定します。

    画像

    すると、A1セルからAAA1セルが選択できます。

    画像

    ※名前ボックスに「AAA1」とだけ入力し、[Shift]+[Enter]を押すことでも同様に実行できます。また名前ボックスではなく、[Ctrl]+[G]や[F5]で呼び出すジャンプ機能の参照先欄でも同様に行えます。
    やりやすい手順を使ってみてください。

    そして、[Ctrl]+[R]で横にコピーします。
    無事、連続アルファベットデータが生成できました。

    画像

    もしA1セルに書式が設定されていて、すべての要素をコピーしたくない場合は、A1セルがアクティブな状態で[F2]で編集モードにし、[Ctrl]+[Enter]を押します。


    方法②の手順で縦方向に生成する


    「行列の入れ替え」貼り付け

    この連続データを縦に配置するには、[Ctrl]+[C]でコピーして、貼り付け先に移動します。

    Ctrl+Alt+Vで「形式を選択して貼り付け」を開いて、Vで「値」、Eで「行/列の入れ替え」にチェックして実行します。

    画像
    画像

    作業用(横方向)のデータは不要なので削除しておきましょう。

    TRANSPOSE関数

    行列の入れ替えは、参照した配列の縦横方向を変換してくれるTRANSPOSE関数でも行えます。
    transposeは「入れ替える」「転置」を意味する英語です。

    画像
    画像

    元データを削除するなら「形式を選択して貼り付け」と同様に値貼り付けは必要です。
    事後的に行列を入れ替える場合は、やりやすい方を使っていきましょう。


    SEQUENCE関数

    お使いのバージョンにSEQUENCE関数が実装されていれば、ADDRESS関数の列番号の部分をSEQUENCE関数に置き換え、指定の数だけ値を入力することで行列を入れ替える作業も省略できます。

    画像

    上図は、連続データを1,000個生成する場合の構成です。
    この状態で決定すると、最初から縦方向に連続データが生成されます。

    画像

    この数式をカットして適当な場所に貼り付けると、どこからスタートしても連続データが正しく生成されることがわかります。

    画像

    SEQUENCE関数の第1引数の行に値を入力すると行(下)方向に連番が生成されますので、行を「1」にして、第2引数の列を「1000」にすれば、列(横)方向にも取り出せます。

    画像

    もちろんこの場合も、どこを起点に生成をスタートしても連続データが成り立ちます。


    ROW関数

    SEQUENCE関数が使えないバージョンで最初から縦方向に生成したいときは、A1セルに入力したADDRESS関数の列番号にROW関数を入れ込みます。

    画像

    返された結果を下にコピーしましょう。

    画像

    ROW関数を用いることで生成データが行番号と連動しますので、スタート位置を変えると連続データが崩れることがあります。


    ユーザー設定リストへの登録


    最後に今回作った連続データを「ユーザー設定リスト」に登録しておきましょう。

    一度連続データの範囲を選択してコピーし、値貼り付けで値に変換します。

    画像

    そしてこの範囲が選択された状態で、キーボードの[Alt]→[F]→[T]を順に押します。Excelのオプションメニューが開きますので、詳細設定タブに切り替えます。

    画像

    下にスクロールして、「全般」グループにある、「ユーザー設定リストの編集」にアクセスします。

    画像

    ここではスマホやパソコンの単語登録のようなイメージで、指定の連続データを保存しておけます。

    画像

    リストの取り込み元として、今選択している範囲がエントリーされていますので、インポートをクリックします。
    ※先ほどあらかじめ値貼り付けしておいたのは、この取り込み元の範囲が数式になっていると上手くインポートできないためです。

    画像

    作業としてはこれで完了です。OKで閉じて、オプションメニューもOKで閉じます。

    これで今後、文字列として「A」を入力しただけで、アルファベットの連続データが作れます。非常に便利ですよね。

    画像


    ちなみにこの「Z」までいったら次は「AA」、「AZ」までいったら次は「BA」の規則性で連続データを登録すると、リストの項目には「IT」(254個)までしか表示されません。

    画像

    ですが、実際にはそれ以降のデータも記録されています。
    ただしあるときを境に、今回の場合は「YY」まで到達した段階で、連続データがリセットされてしまいます。

    画像

    これは全データの合計文字数によって、ユーザー設定リストに登録できる上限値があるからなんですね。

    ユーザー設定リストに登録された連続データはオートフィルで生成する必要があるため、ここまでぐいーんと引っ張るケースは稀かと思いますが、これ以降の採番が必要なときは、本日解説した一連の関数を組み合わせて生成していきましょう!
     


    いかがでしたか?

    今回のエッセンスをおさえておくと、「ABC」だけの採番のみならず、数字や記号が混ざった管理番号などにも応用できますので、皆さんの日々の業務にあわせて、ぜひ試してみてくださいね!


    ↓↓記事の内容を動画で解説しています↓↓
    ※本記事の委細が動画収録当時のバージョン・解説内容と異なる場合があります。

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

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

    あなたへのおすすめ