VBA を使用せずに対話型 Excel ワークブックを作成する:ステップバイステップ ガイド

Excel には、VBA を使用せずにレポート、ダッシュボード、フォームなどの動的で対話型のファイルを作成できる強力な組み込み機能が備わっています。データを即座に更新できる対話型レポートを作成できます。

このチュートリアルでは、VBA を使用せずにこれらの対話型ファイルを構築する方法を説明します。

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

インタラクティブな Excel ファイルを作成するには、データセットをクリーンアップして構造化する必要があります。効果的なインタラクティブな動作を作成するには、適切にフォーマットされ構造化されたデータセットが不可欠です。

• データセットをクリーンアップします。
• 重複とスペースを削除します。
• 形式とデータ型の問題を修正する

データをテーブルに変換

• データセットを選択します。
• 挿入 に移動します。 タブ>> テーブルを選択します .
• テーブルにヘッダーがあるをチェックします。 .
• [OK] をクリックします。 .

• テーブルの名前を変更します:
  • テーブル デザインに移動します。 タブ>> テーブル名を選択します そして、Sales などの適切な名前を付けます。 .

2.データ検証ドロップダウン リストを使用する

ドロップダウン リストは、対話型の Excel ファイルを作成するために不可欠な要素です。ドロップダウン リストを使用すると、迅速かつ一貫性のある制御されたユーザー入力が可能になります。後で、これを使用してデータ、グラフ、KPI を対話的にフィルタリングできます。

手順:

ヘルパー リストの作成: シートの上部または右側にヘルパー リストを作成します。これらは後で非表示にすることができます。

• セルを選択し、次の数式を入力して一意の領域を一覧表示します。

=SORT(UNIQUE(Sales[Region]))

• セルを選択し、次の数式を入力して製品名をリストします。

=SORT(UNIQUE(Sales[Product]))

• オプション: 数式を挿入して一意のリストを作成する前に、各リストの上に「すべて」を追加します。

ドロップダウン リストの作成:

• ドロップダウンを表示するセルを選択します (例:B2)。
• データに移動します。 タブ>> データ検証を選択します .
• [許可] の下で 、リストを選択します。 .
• ソース内 、ヘルパー列から地域リストを選択します。

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

• 同様の手順に従って、Product のドロップダウン リストを作成します。
• [許可] の下で 、リストを選択します。 .
• ソース内 、ヘルパー列から製品リストを選択します。

ヒント: リストの名前付き範囲を作成し、それをデータ検証に使用できます。

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")

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

• 数式 に移動します。 タブ>> 名前マネージャー を選択します>> 新規を選択します .
• 名前: フィルタされた日付
• 参照先:

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

• 名前: フィルターされた収益
• 参照先:

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

グラフを作成します:

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

• 系列値には次のように入力します。

='Interactive Sheet'!FilteredRevenue

• 横軸ラベルの場合:

='Interactive Sheet'!FilteredDates

対話性のテスト:

• 東部地域とすべての商品を選択します。
• グラフには、関連するすべてのデータが表示されます。

• 特定の地域と特定の製品を選択します。
• グラフは即座にフィルタリングされます。
• これは、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] をクリックします。 .

• ピボットテーブル フィールドから :
  • 製品 をドラッグします 行 へ
  • 収益をドラッグします 価値観 に合わせて

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

• ピボットテーブルを選択します。
• ピボットテーブル分析に移動します。 タブ>> ピボットグラフを選択します .
• クラスタ化された列 を選択します。 チャート。
• [OK] をクリックします。 .

インタラクティブ スライサーを追加:

• ピボットテーブル分析に移動します。 タブ>> スライサーの挿入を選択します。 .
• 次のようなフィルタリングの基準となるフィールドを選択します。
  • 地域、製品、販売員など
• スライサーのボタンをクリックすると、グラフや表を即座にフィルタリングできます。

• スライサーとグラフを一緒に保つために、それらをグループ化できます。
• スライサーをチャートの近くまたはチャート上に配置し、すべてのオブジェクト (チャートとスライサー) を選択し、右クリックして [グループ] を選択します。 .

共有スライサーを使用した複数のピボットテーブルの接続

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

• 次に、スライサーとグラフをダッシュボード シートにコピーします。
• ピボットテーブル シートは非表示にすることができます。

7.ナビゲーションにハイパーリンクを使用する

Excel ファイルをクリック可能なアプリのようにするには、ナビゲーションにハイパーリンクを使用します。これにより、ユーザーは別のシート、グラフ、または表にジャンプできるようになります。

手順:

• 挿入に移動します タブ>> イラストから>> 形状を選択します>> 長方形を選択します .
• 図形に「Sales Data」という名前を付けます。
• 図形を右クリック>> リンク を選択します。 .

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

• [このドキュメントに配置] を選択します。>> セル参照 A3 を入力します。>> ピボットテーブル を選択します シート。
• [OK] をクリックします。 .

• 見た目をすっきりさせるために、ボタンのような形式にします。
• このようなナビゲーション ハイパーリンクを使用すると、ユーザーは「ダッシュボード」シートとさまざまな「詳細」シート間を簡単に移動できます。

最終的なインタラクティブ Excel ファイル:

• 以下の例は、すべての地域と選択された「アクセサリ」製品を示しています。
• スライサーから選択を行うこともできます。
• すべてのデータは自動的に更新されます。

ヒント

• さまざまなインタラクティブな方法を示すために、ダイナミック チャート (FILTER 関数を使用) とピボットグラフの両方をデモンストレーションしました。ニーズに最適なタイプを選択できます。
• VBA を必要とせずに、よりインタラクティブなオプションを提供するフォーム コントロールを探索することもできます。
• 検索ボックスを作成するには、動的 XLOOKUP 関数を使用できます。

練習ワークブックをダウンロード

結論

これらの機能とテクニックを使用すると、VBA を使用しなくても、洗練された対話型の Excel ファイルを作成できます。重要なのは、対話性を最大限に高めるために複数のテクニックを組み合わせることです。インタラクティブ要素を常に徹底的にテストし、ユーザーに明確な指示を提供することを忘れないでください。練習を重ねると、VBA コードを 1 行も記述することなく、データ分析を強力かつユーザーフレンドリーにする優れたダッシュボードやツールを作成できるようになります。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!