【Excel】UNIQUE関数の結果に連番を振る!ExcelのSEQUENCE関数との組み合わせ活用術

Excelで重複しないリストを作成したい場合、UNIQUE関数が便利です。しかし、UNIQUE関数だけではリストに連番を振ることができません。連番を振るには、SEQUENCE関数との組み合わせが有効です。この記事では、UNIQUE関数とSEQUENCE関数を連携させて、重複しないリストに連番を振る方法を解説します。UNIQUE関数の結果を連番付きリストに変換する手順がわかります。

UNIQUE関数は、指定した範囲から重複しない値のリストを返します。SEQUENCE関数は、指定した行数と列数の数値配列を生成します。この2つの関数を組み合わせることで、動的な連番付きリストを簡単に作成できます。これにより、データの管理や分析がより効率的になります。

UNIQUE関数とSEQUENCE関数の基本機能

UNIQUE関数は、配列またはセル範囲から一意の値のリストを返します。例えば、商品リストに同じ商品名が複数ある場合、UNIQUE関数を使えば、重複しない商品名のリストを作成できます。この関数はMicrosoft 365のExcelで利用可能です。Excel 2019以前のバージョンでは利用できません。

SEQUENCE関数もMicrosoft 365で利用できる動的配列関数の一つです。この関数は、指定した行数と列数に基づいて、連番の配列を生成します。例えば、SEQUENCE(5)と入力すると、1から5までの数値が縦に並んだ配列が返されます。SEQUENCE(3,2)と入力すると、3行2列の連番配列が生成されます。

UNIQUE関数とSEQUENCE関数を組み合わせる方法

UNIQUE関数とSEQUENCE関数を組み合わせることで、重複しないリストに連番を振ることができます。基本的な考え方は、UNIQUE関数で抽出される値の個数に合わせてSEQUENCE関数で連番を生成することです。これにより、元データの増減に合わせて連番リストも自動的に更新されます。

この連携を実現するには、LET関数も活用すると、数式がより分かりやすくなります。LET関数を使うと、数式内で名前を定義し、その名前を繰り返し使用できます。これにより、数式の可読性が向上し、メンテナンスも容易になります。

1. 連番を振りたい元データ範囲を確認する
   例えば、A1セルからA10セルに商品名が入力されているとします。この範囲から重複しない商品名のリストを作成し、連番を振ります。
2. UNIQUE関数で重複しないリストを作成する
   B1セルなどに「=UNIQUE(A1:A10)」と入力します。これにより、A1:A10の範囲から重複しない商品名がB1セルから下に展開されます。
3. SEQUENCE関数で連番を生成する
   UNIQUE関数の結果の個数に合わせて連番を生成するために、COUNTA関数と組み合わせます。例えば、C1セルに「=SEQUENCE(COUNTA(B1#))」と入力します。B1#はスピル配列を参照する記法です。これにより、B1セルから展開されたリストの行数分の連番がC1セルから下に生成されます。
4. HSTACK関数で結合する
   UNIQUE関数の結果とSEQUENCE関数の結果を横に並べて表示するために、HSTACK関数を使用します。例えば、D1セルに「=HSTACK(C1#,B1#)」と入力します。これにより、連番と重複しない商品名のリストがD1セルから横に展開されます。

LET関数を使ったよりスマートな数式

LET関数を使用すると、上記の数式を1つのセルにまとめることができ、さらに可読性が高まります。LET関数は、数式内で一時的な名前と値を定義できる関数です。これにより、同じ計算を何度も記述する必要がなくなり、数式が簡潔になります。

LET関数を使った数式は以下のようになります。

1. LET関数で名前を定義する
   例えば、D1セルに以下の数式を入力します。
   =LET(data,A1:A10,unique_list,UNIQUE(data),serial_numbers,SEQUENCE(COUNTA(unique_list)),HSTACK(serial_numbers,unique_list))
2. 数式の解説
   • `data,A1:A10`:A1:A10の範囲を`data`という名前で定義します。
   • `unique_list,UNIQUE(data)`:`data`の範囲から重複しないリストを`unique_list`という名前で定義します。
   • `serial_numbers,SEQUENCE(COUNTA(unique_list))`:`unique_list`の個数分の連番を`serial_numbers`という名前で定義します。
   • `HSTACK(serial_numbers,unique_list)`:定義した連番(`serial_numbers`)と重複しないリスト(`unique_list`)を横に結合します。

UNIQUE関数とSEQUENCE関数の応用例

UNIQUE関数とSEQUENCE関数を組み合わせることで、様々な応用が可能です。例えば、顧客リストから重複しない顧客名とその顧客に割り振られたIDを自動生成する際に活用できます。また、アンケート結果から回答者の重複しない氏名と回答番号をリストアップする際にも便利です。

さらに、これらの関数は他の動的配列関数と組み合わせて、より高度なデータ処理にも利用できます。例えば、FILTER関数と組み合わせることで、特定の条件を満たす重複しないデータにのみ連番を振るといったことも可能です。

UNIQUE関数とSEQUENCE関数利用時の注意点

UNIQUE関数とSEQUENCE関数は、Microsoft 365のExcelでのみ利用可能です。Excel 2019やそれ以前のバージョンではこれらの関数はサポートされていません。これらのバージョンをご利用の場合は、代替手段としてCOUNTIF関数と手動での連番生成、またはPower Queryなどの機能を利用する必要があります。

また、動的配列関数は、結果が複数のセルに展開される「スピル」という動作をします。スピル範囲に他のデータが存在する場合、#SPILL!エラーが発生します。数式を入力する際は、結果が表示される範囲にデータが重ならないように注意が必要です。必要であれば、結果を表示するセル範囲を事前にクリアしておきましょう。

UNIQUE関数とVLOOKUP関数、INDEX関数との連携

UNIQUE関数で抽出した重複しないリストを基に、VLOOKUP関数やINDEX関数を使って他の情報を参照することも一般的です。例えば、重複しない商品名のリストを作成した後、その商品名に対応する価格や在庫数を参照したい場合に使えます。

UNIQUE関数とSEQUENCE関数で連番を振ったリストを作成した場合、その連番をIDとして利用し、INDEX関数などで特定の行の情報を取得する際に役立ちます。例えば、D1セルに=HSTACK(SEQUENCE(COUNTA(UNIQUE(A1:A10))),UNIQUE(A1:A10))と入力して連番付きリストを作成した場合、連番の「3」に対応する商品名を取得するには、INDEX(D1#,3,2)のように指定します。ここでD1#はスピル配列全体を参照しています。

Power Queryを使った重複除去と連番付与

Excelの動的配列関数が利用できない環境や、より複雑なデータ変換が必要な場合は、Power Queryの利用が強力な代替手段となります。Power Queryは、Excel 2016以降で標準搭載されており、Microsoft 365でも利用可能です。データの取得、変換、整形をGUI操作で行えます。

Power Queryで重複しないリストを作成し、連番を振る手順は以下の通りです。

1. Excelテーブルへの変換
   元データ範囲を選択し、「挿入」タブの「テーブル」をクリックしてExcelテーブルに変換します。
2. Power Queryエディターの起動
   テーブルを選択した状態で、「データ」タブの「テーブルまたは範囲から」をクリックし、Power Queryエディターを起動します。
3. 重複の削除
   重複を除去したい列を選択し、リボンの「ホーム」タブにある「行の削除」から「重複の削除」を選択します。
4. 連番の追加
   「列の追加」タブを選択し、「インデックス列」から「開始位置」を指定して(通常は0または1)連番列を追加します。
5. Excelへの読み込み
   「閉じて読み込む」をクリックし、結果をExcelシートに読み込みます。

よくある質問と回答

Q1: UNIQUE関数とSEQUENCE関数はExcelのどのバージョンで使えますか?

A1: UNIQUE関数とSEQUENCE関数は、Microsoft 365版のExcelでのみ利用可能です。Excel 2019、2021、またはそれ以前のバージョンではサポートされていません。これらのバージョンをご利用の場合は、Power QueryやCOUNTIF関数などを利用して同様の処理を実現する必要があります。

Q2: #SPILL!エラーが表示されるのはなぜですか?

A2: #SPILL!エラーは、動的配列関数(UNIQUE、SEQUENCE、FILTERなど)の結果が表示されるべきセル範囲に、既にデータが存在する場合に発生します。数式を入力する前に、結果が表示される範囲に他のデータがないことを確認してください。不要なデータは削除するか、別の場所に移動させてから数式を入力してください。

Q3: SEQUENCE関数で生成する連番の開始番号を変更できますか?

A3: はい、SEQUENCE関数の第2引数で開始番号を指定できます。例えば、SEQUENCE(5,1,10)と入力すると、10から始まる5つの連番(10,11,12,13,14)が生成されます。元データに連番を振る場合、通常は1から開始させることが多いですが、必要に応じて変更可能です。

Q4: UNIQUE関数で抽出したリストが更新されないのですが?

A4: UNIQUE関数は、数式が入力されているセルが参照している元データが変更された場合に自動的に更新されます。もし更新されない場合は、数式が正しく入力されているか、参照範囲が間違っていないかを確認してください。また、Excelの計算方法が「手動」になっている可能性も考えられます。「数式」タブの「計算オプション」で「自動」になっているか確認してください。

まとめ

この記事では、ExcelのUNIQUE関数とSEQUENCE関数を組み合わせて、重複しないリストに連番を振る方法を解説しました。LET関数を用いることで、さらに簡潔で分かりやすい数式を作成できます。これらの関数を活用することで、データの整理や分析作業を効率化できます。

Microsoft 365をご利用であれば、ぜひこの組み合わせを試してみてください。応用として、FILTER関数など他の動的配列関数と連携させることで、さらに高度なデータ操作も可能になります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】