VBA不要で対話型Excelブックを作成する方法|ステップバイステップ完全ガイド

Excelには、VBAを一切使わずに、レポート・ダッシュボード・フォームといった動的で対話的なファイルを作成できる強力な標準機能が備わっています。選択内容に応じてデータが瞬時に更新される対話型レポートも簡単に実現できます。

このチュートリアルでは、VBAコードを一行も書かずに、こうした対話型ファイルを作成する手順を段階的に解説します。

1. データセットを準備する

対話型Excelファイルを作るには、まずデータセットを整理し、適切な構造に整えることが重要です。書式や構造が整ったデータは、効果的なインタラクティブ動作の土台となります。

• データセットのクリーニングを行う
• 重複データや余分なスペースを削除する
• 書式やデータ型の問題を修正する

データをテーブルに変換する

• データセットを選択します。
• [挿入]タブ >> [テーブル] を選択します。
• [先頭行をテーブルの見出しとして使用する] にチェックを入れます。
• [OK] をクリックします。

• テーブル名を変更します:
  • [テーブルデザイン]タブ >> [テーブル名] で、Sales のように分かりやすい名前を付けます。

2. データの入力規則でドロップダウンリストを作る

ドロップダウンリストは、対話型Excelファイルに不可欠な要素です。ユーザーの入力を素早く、一貫性のある形に制御でき、後述のデータフィルタリングやグラフ・KPIの連動にも活用できます。

手順:

補助リストを作成する: シートの上部や右側に補助用のリストを作成しておきます。後から非表示にできます。

• セルを選択し、以下の数式を入力して地域の一意リストを表示します。

=SORT(UNIQUE(Sales[Region]))

• 別のセルに以下の数式を入力して、製品名の一意リストを表示します。

=SORT(UNIQUE(Sales[Product]))

• (任意) 数式を入力する前に、各リストの上に「All」を追加しておくと便利です。

ドロップダウンリストを作成する:

• ドロップダウンを設定したいセル(例:B2)を選択します。
• [データ]タブ >> [データの入力規則] を選択します。
• [入力値の種類] で [リスト] を選びます。
• [元の値] に、補助列の地域リストを指定します。

• [OK] をクリックします。

• 同じ手順で、製品用のドロップダウンリストも作成します。
• [入力値の種類] で [リスト] を選びます。
• [元の値] に、補助列の製品リストを指定します。

ヒント: リストに名前付き範囲を定義しておくと、データの入力規則での指定がより簡単になります。

3. 動的数式とドロップダウンを組み合わせる

ドロップダウンリストと動的数式を組み合わせると、KPIの自動更新やデータの絞り込みが自動化されます。

KPIを作成する:

• 総売上:

=SUMIFS(
Sales[Revenue],
Sales[Region], IF($B$2="All","*", $B$2),
Sales[Product], IF($B$4="All","*", $B$4))

• 平均割引率:

=AVERAGEIFS(
Sales[Discount],
Sales[Region], IF($B$2="All","*", $B$2),
Sales[Product], IF($B$4="All","*", $B$4))

見やすくするために、パーセンテージ表示形式を適用しましょう。

• 総注文数:

=COUNTIFS(
Sales[Region], IF($B$2="All","*", $B$2),
Sales[Product], IF($B$4="All","*", $B$4))

• 総販売数量:

=SUMIFS(
Sales[Units],
Sales[Region], IF($B$2="All","*", $B$2),
Sales[Product], IF($B$4="All","*", $B$4))

インタラクティブ動作をテストする:

• ドロップダウンリストから地域と製品を選択します。
• KPIが自動的に更新されることを確認します。

4. 名前付き範囲で動的グラフを作成する

ユーザーの選択に応じて自動更新されるグラフを作りましょう。

フィルター済みデータの抽出:

=FILTER(
CHOOSE({1,2}, Sales[Date], Sales[Revenue]),
IF($B$2="All", Sales[Region]<>"", Sales[Region]=$B$2) *
IF($B$4="All", Sales[Product]<>"", Sales[Product]=$B$4),
"No rows")

動的な名前付き範囲を定義する:

• [数式]タブ >> [名前マネージャー] >> [新規] を選択します。
• 名前: FilteredDates
• 参照範囲:

=INDEX('Interactive Sheet'!$B$7#, ,1)

• 名前: FilteredRevenue
• 参照範囲:

=INDEX('Interactive Sheet'!$B$7#, ,2)

グラフを作成する:

• [挿入]タブ >> [グラフ] から [折れ線グラフ] を選択します。
• グラフを右クリック >> [データの選択] を選びます。

• 系列の値には以下を入力します。

='Interactive Sheet'!FilteredRevenue

• 横軸ラベルには以下を入力します。

='Interactive Sheet'!FilteredDates

インタラクティブ動作をテストする:

• 地域「East」+ 製品「すべて」を選択すると、該当する全データがグラフに表示されます。

• 特定の地域と特定の製品を選ぶと、グラフが即座に絞り込まれます。
• VBAなしでも、あらゆる組み合わせでこの動作が可能です。

5. 条件付き書式で視覚的なフィードバックを加える

条件付き書式を使えば、重要な値やユーザーの選択内容を瞬時に強調表示できます。

手順:

• データ範囲を選択します。
• [ホーム]タブ >> [条件付き書式] >> [新しいルール] を選択します。
• [数式を使用して、書式設定するセルを決定] を選びます。
• 以下の数式を入力します。

=AND(OR('Interactive Sheet'!$B$2="All", $C2='Interactive Sheet'!$B$2), OR('Interactive Sheet'!$B$4="All", $F2='Interactive Sheet'!$B$4))

• 強調色を選択します。
• [OK] をクリックします。

これで、条件に一致する行だけがハイライトされ、フィルター選択が視覚的に分かりやすくなります。

6. ピボットテーブル・スライサー・対話型グラフの活用

ピボットテーブル、ピボットグラフ、スライサーは、Excelの対話機能の中核であり、データの絞り込みを非常に簡単に行えます。

ピボットテーブルを作成する:

• データ範囲を選択します。
• [挿入]タブ >> [ピボットテーブル] を選択します。
• 配置先として [新しいワークシート] または [既存のワークシート] を選びます。
• [OK] をクリックします。

• [ピボットテーブルのフィールド] から:
  • [行]エリアに Product をドラッグします。
  • [値]エリアに Revenue をドラッグします。

ピボットグラフを作成する:

• ピボットテーブルを選択します。
• [ピボットテーブル分析]タブ >> [ピボットグラフ] を選択します。
• [集合縦棒] グラフを選びます。
• [OK] をクリックします。

対話型スライサーを追加する:

• [ピボットテーブル分析]タブ >> [スライサーの挿入] を選択します。
• フィルター対象のフィールドを選びます。例:
  • 地域、製品、営業担当者など。
• スライサーのボタンをクリックするだけで、グラフやテーブルを瞬時に絞り込めます。

• スライサーとグラフをまとめて移動したい場合は、グループ化しましょう。
• スライサーをグラフの近くまたは上に配置し、すべてのオブジェクト(グラフとスライサー)を選択して右クリック >> [グループ化] を選びます。

共有スライサーで複数のピボットテーブルを連携させる

• 同じデータソースから複数のピボットテーブルを作成します。
• [スライサー] を挿入します。
• スライサーを右クリック >> [レポートの接続] を選択します。
• スライサーを複数のピボットテーブルに接続します。

• その後、スライサーとグラフをダッシュボード用シートにコピーします。
• ピボットテーブルのシートは非表示にしても問題ありません。

7. ハイパーリンクでナビゲーションを実現する

ハイパーリンクを活用すると、Excelファイルをクリック操作だけで動くアプリのような使い勝手にできます。ユーザーはさまざまなシート、グラフ、テーブル間を自由に移動できます。

手順:

• [挿入]タブ >> [図形] >> [四角形] を選択します。
• 図形に「売上データ」という名前を付けます。
• 図形を右クリック >> [リンク] を選びます。

• [ハイパーリンクの挿入] ダイアログで:
• [このドキュメント内] を選択 >> セル参照に A1 を入力 >> Sales シートを選択します。
• [OK] をクリックします。

• [このドキュメント内] を選択 >> セル参照に A3 を入力 >> PivotTable シートを選択します。
• [OK] をクリックします。

• 見た目を整えるために、ボタン風に書式設定しましょう。
• このようなナビゲーション用ハイパーリンクを使うと、「ダッシュボード」と各「詳細」シートの間を簡単に移動できます。

完成した対話型Excelファイル:

• 下の例では、全地域を選択した状態で「アクセサリー」製品が選ばれています。
• スライサーからも選択可能です。
• すべてのデータが自動的に更新されます。

応用のコツ

• 今回は、FILTER関数を使った動的グラフとピボットグラフという、異なる2つの対話手法を紹介しました。用途に合った方を選んでください。
• さらに、フォームコントロールを活用すれば、VBA不要でより多くの対話オプションを追加できます。
• 検索ボックスを作りたい場合は、動的なXLOOKUP関数が便利です。

練習用ブックのダウンロード

まとめ

これらの機能とテクニックを組み合わせれば、VBAコードを一切書かずに、高度で対話的なExcelファイルを作成できます。ポイントは、複数のテクニックを掛け合わせてインタラクティブ性を最大化することです。対話要素は必ず入念にテストし、ユーザー向けに分かりやすい操作説明を添えることを忘れないでください。練習を重ねれば、VBAを一行も書かずに、データ分析を強力かつ使いやすいものへと変える印象的なダッシュボードやツールを作れるようになるでしょう。