Excelで複数階層の階層構造を作成する方法｜初心者向け2つの簡単な手順を解説

Excelで作業をしていると、複数階層の階層構造（マルチレベル・ヒエラルキー）を作成したい場面がよくあります。階層構造を活用すれば、データをシンプルかつ簡潔な形式で表現でき、情報の把握や分析が格段にしやすくなります。Microsoft Excelは強力で多機能なソフトウェアであり、いくつかの簡単なステップを踏むだけで誰でも複数階層の階層構造を作成できます。

この記事では、Excelで階層構造を作成するための2つの簡単な方法を、実際の画面操作の手順とともに詳しく解説します。

Excelで複数階層の階層構造を作成する2つの方法

今回紹介する2つの方法は以下の通りです。

• 方法1： Excelの「データの入力規則」機能を使う方法
• 方法2： Excelの「Power Pivot」アドインを使う方法

なお、この記事では Microsoft Excel 365 を使用していますが、お使いのバージョンに応じて同様の手順で実行できます。

方法1：データの入力規則を使って複数階層の階層構造を作成する

データの入力規則機能の活用は、Excelで複数階層の階層構造を作成する最も手軽な方法のひとつです。次のデータセットには、シネコンで上映中の作品について「映画名」「上映スケジュール」「座席タイプ」の情報が含まれています。このデータをもとに、連動するドロップダウンリストによる階層構造を作成していきましょう。

ステップ01： 「映画名」列に入力規則を設定する

まず、「映画名」列にドロップダウンリストを作成し、好きな映画を選択できるようにします。手順は非常にシンプルです。

• まず、「映画名」列のセル B5 を選択します。
• 次に、リボンの「データ」タブを開きます。
• 「データツール」グループにある「データの入力規則」をクリックします。

すると、下図のような「データの入力規則」ダイアログボックスが表示されます。

• 下図の矢印部分のドロップダウンアイコンをクリックします。
• 一覧から「リスト」を選択します。

• 続いて、「元の値」ボックスをクリックし、以下の数式を入力します。

='Dataset 1 '!$B$5:$B$10

ここで $B$5:$B$10 は、データセット内の「映画名」列のセル範囲です。

ポイント： ここで絶対参照を使用するのは、どのセルでもすべての映画名を選択肢として表示させる必要があるためです。

• 設定が完了したら「OK」をクリックします。

• すると、下図のようにセル B5 の横にドロップダウンアイコンが表示されます。これをクリックしましょう。

ドロップダウンリストから、好きな映画を選択できるようになります。

ステップ02： 入力規則を「映画名」列の残りのセルにコピーする

次に、Excelの「形式を選択して貼り付け」機能を使って、セル B5 の入力規則を「映画名」列の他のセルにも適用します。

• まず、セル B5 を選択し、Ctrl + C でコピーします。

• 次に、「映画名」列の残りのセルを選択します。
• リボンの「ホーム」タブを開きます。
• 「貼り付け」の下矢印をクリックします。
• ドロップダウンから「形式を選択して貼り付け」を選びます。

• 「形式を選択して貼り付け」ダイアログボックスが表示されたら、下図のように「入力規則」を選択します。
• その後「OK」をクリックします。

これで、選択したセルに入力規則がコピーされました。各セルの横にあるドロップダウンリストから好きな映画名を選べるようになり、下図のような結果が得られます。

ステップ03： 「上映スケジュール」列に連動する入力規則を設定する

次に、「映画名」列での選択内容に応じて変化するドロップダウンリストを「上映スケジュール」列に作成します。たとえば、映画「Into the Deep」は1日に5つの上映時間がありますが、映画「Bullet Train」は3つしかありません。つまり、「上映スケジュール」列に表示される選択肢は、「映画名」列で選んだ映画によって変わる仕組みが必要です。ここでは、OFFSET関数とMATCH関数を組み合わせて実現します。

• まず、セル C5 を選択し、ステップ01と同じ手順でデータの入力規則ダイアログボックスを開きます。

• 次に、「元の値」ボックスに以下の数式を入力します。

=OFFSET('Dataset 1 '!$B$14,1,MATCH($B5,'Dataset 1 '!$B$14:$G$14,0)-1,5,1)

ここで、セル $B$14 は「Dataset 1」シートの「上映スケジュール」の見出しセル、セル $B5 は「映画名」列で選択された映画、範囲 $B$14:$G$14 は「Dataset 1」シート上の映画名の配列を指しています。

数式の解説

• MATCH($B5,'Dataset 1 '!$B$14:$G$14,0) → 指定した検索値が行・列・表の中で何番目にあるかを返します。
  • $B5 → 引数 lookup_value（検索値）
  • 'Dataset 1 '!$B$14:$G$14 → 引数 lookup_array（検索範囲）
  • 0 → 引数 match_type（照合の型：完全一致）
  • 結果 → 1
• OFFSET('Dataset 1 '!$B$14,1,MATCH($B5,'Dataset 1 '!$B$14:$G$14,0)-1,5,1) → OFFSET('Dataset 1 '!$B$14,1,1-1,5,1) となります。
  • 'Dataset 1 '!$B$14 → 引数 reference（基準となるセル）
  • 1 → 引数 rows（上下方向の移動量）
  • 1-1 → 引数 cols（左右方向の移動量）
  • 5 → 引数 height（範囲の高さ）
  • 1 → 引数 width（範囲の幅）
  • 結果 → {11:00 AM, 3:30 PM, 6:00 PM, 9:30 PM, 11:00 PM}

• 設定後、「OK」をクリックします。

• すると、下図のようにセル C5 の横にドロップダウンアイコンが表示されます。クリックしてみましょう。

映画「Into the Deep」の利用可能な上映スケジュールが表示されます。

ステップ04： 入力規則を「上映スケジュール」列の残りのセルにコピーする

続いて、セル C5 の入力規則の設定を、残りのセルにも「形式を選択して貼り付け」機能でコピーします。

• セル C5 を選択し、ステップ02と同じ手順を実行します。
• その後、映画「Bullet Train」を選択してみると、データセットどおり上映スケジュールの選択肢が3つだけ表示されることが確認できます。

• 同様に、他の映画についても上映スケジュールを選択すると、下図のような結果になります。

ステップ05： 「座席タイプ」列に連動する入力規則を設定する

さらに、「上映スケジュール」での選択内容に応じて変化するドロップダウンリストを「座席タイプ」列に作成します。データセットを見ると、すべての上映スケジュールで同じ座席タイプが用意されているわけではありません。たとえば11:00 AMの回では3種類すべての座席タイプが選べますが、6:00 PMの回では「レギュラー」と「VIP」のみとなっています。この連動型ドロップダウンリストも、OFFSET関数とMATCH関数を使って作成します。

• まず、セル D5 を選択し、ステップ01と同じ手順でデータの入力規則ダイアログボックスを開きます。

• 次に、ダイアログボックスの「元の値」ボックスに以下の数式を入力します。

=OFFSET('Dataset 1 '!$B$23,1,MATCH($C5,'Dataset 1 '!$B$23:$F$23,0)-1,3,1)

ここで、セル $B$23 は「Dataset 1」シートの「座席タイプ」の見出しセル、セル $C5 は選択された上映スケジュール、範囲 $B$23:$F$23 は「Dataset 1」シート上の上映スケジュールの配列を指しています。

数式の解説

• MATCH($C5,'Dataset 1 '!$B$23:$F$23,0) → 検索値の位置を返します。
  • $C5 → 引数 lookup_value（検索値）
  • 'Dataset 1 '!$B$23:$F$23 → 引数 lookup_array（検索範囲）
  • 0 → 引数 match_type（照合の型：完全一致）
  • 結果 → 3
• OFFSET('Dataset 1 '!$B$23,1,MATCH($C5,'Dataset 1 '!$B$23:$F$23,0)-1,3,1) → OFFSET('Dataset 1 '!$B$23,1,3-1,3,1) となります。
  • 'Dataset 1 '!$B$23 → 引数 reference（基準となるセル）
  • 1 → 引数 rows（上下方向の移動量）
  • 3-1 → 引数 cols（左右方向の移動量）
  • 3 → 引数 height（範囲の高さ）
  • 1 → 引数 width（範囲の幅）
  • 結果 → {Regular, VIP}

• 設定後、「OK」をクリックします。

すると、セル D5 の横にドロップダウンアイコンが表示されます。選択されている上映スケジュールが6:00 PMのため、「Regular」と「VIP」の2つの選択肢だけが表示されます。

ステップ06： 入力規則を「座席タイプ」列の残りのセルにコピーする

最後に、セル D5 の入力規則の設定を「座席タイプ」列の他のセルにもコピーします。

• まず、セル D5 を選択し、ステップ02と同じ手順を実行します。
• 次に、セル C6 の横のドロップダウンアイコンをクリックします。選択されている上映スケジュールが11:00 AMなので、3種類すべての座席タイプが選択可能になっています。

• 同様に、他のセルでも座席タイプを選択すると、下図のような結果が得られます。

あわせて読みたい： Excelで階層構造を作成する3つの簡単な方法

方法2：Power Pivotを使って複数階層の階層構造を作成する

Power Pivot機能の活用も、Excelで複数階層の階層構造を作成するのに非常に効率的な方法です。次のデータセットには、アメリカ合衆国の各州のさまざまな都市における売上データのテーブルが含まれています。このテーブルから「国 → 州 → 都市」という階層構造を作成していきましょう。

ステップ01： テーブルをPower Pivotウィンドウに追加する

まだPower Pivotアドインを有効化していない場合は、先に有効化しておきましょう。準備ができたら、以下の手順に進みます。

• まず、テーブル全体を選択し、Ctrl + C でコピーします。

• 次に、リボンの「Power Pivot」タブを開きます。
• 下図のように「管理」をクリックします。

すると、下図のような空のPower Pivotウィンドウが開きます。

• Power Pivotウィンドウで「クリップボード」をクリックします。
• ドロップダウンから「貼り付け」を選択します。

すると、「貼り付けプレビュー」ダイアログボックスが表示されます。

• 「貼り付けプレビュー」ダイアログボックスで「OK」をクリックします。

これで、テーブルが下図のようにPower Pivotウィンドウに追加されます。

ステップ02： ダイアグラムビューで階層を作成する

このステップでは、「都市は州の下に属し、州は国の下に属する」という階層を作成します。

• まず、Power Pivotウィンドウの「ホーム」タブにある「ダイアグラム ビュー」をクリックします。

すると、下図のようにダイアグラムビューが画面に表示されます。

• 次に、下図の矢印で示した部分を右クリックし、「階層の作成」を選択します。

これで階層が作成されます。

• 次に、「Country」列をドラッグして、新しく作成した階層にドロップします。

• 同様に、残りの列も順番にドラッグ＆ドロップすると、下図のような結果になります。

ステップ03： ピボットテーブルを作成する

• 続いて、「ピボットテーブル」オプションをクリックします。

• 「ピボットテーブルの作成」ダイアログボックスが表示されたら、「OK」をクリックします。

すると、下図のように新しいワークシートが作成されます。

• 次に、「ピボットテーブルのフィールド」ペインで、Hierarchy1を「行」エリアへ、Salesを「値」エリアへそれぞれドラッグ＆ドロップします。

これで、ワークシートに下図のようなピボットテーブルが作成されます。

• 次に、下図の矢印で示した「+ 記号」をクリックします。

すると、各州の名前と、それぞれの売上合計が下図のように展開表示されます。

• さらに、州「Arizona」の横にある「+ 記号」をクリックしてみましょう。

これにより、Arizona州に属する都市と、それぞれの売上が表示されます。

同様に、他の州の横にある「+ アイコン」をクリックしていけば、下図のような結果が得られます。

あわせて読みたい： Excelのピボットテーブルで階層を作成する簡単な手順

練習セクション

配布しているExcelワークブックでは、各ワークシートの右側に練習セクションを用意しています。ぜひご自身で操作を試してみてください。

まとめ

今回は、Excelで複数階層の階層構造を作成する2つの方法として、「データの入力規則」を使った連動ドロップダウンリストの作成方法と、「Power Pivot」を使った階層の作成方法を解説しました。どちらの方法も実務で役立つテクニックなので、ぜひマスターしてください。記事の内容についてご質問や改善のご提案があれば、お気軽にコメントをお寄せください。Excelのさらなる学習には、当サイトExcelDemyもぜひご活用ください。Happy Learning!

関連記事

• Excelで階層チャートを作成する3つの簡単な方法
• Excelのピボットテーブルで日付階層を作成する簡単な手順
• ExcelでSmartArtの階層図を使う方法（簡単な手順付き）
• Excelで行の階層を追加する2つの簡単な方法