フォーム コントロールを使用してインタラクティブな Excel ダッシュボードを構築する (VBA は必要ありません)

インタラクティブ ダッシュボードは、Excel で大規模なデータを動的に視覚化および分析するための強力なツールです。データを動的に分析して迅速な洞察を得る方法を提供します。 Excel のフォーム コントロールは、VBA を使用せずにこのようなダッシュボードを作成するための優れた機能です。これにより、ユーザーはボタン、スライダー、コンボ ボックス、チェックボックスを通じてデータを操作できるようになります。この記事では、Excel のフォーム コントロールを使用してインタラクティブなダッシュボードを作成する方法を説明します。

インタラクティブなダッシュボードを作成するために必要なフォーム コントロールを表示する販売データセットを検討してください。

ステップ 1:データセットを準備する

• 売上データを Excel テーブルに変換します。データ範囲を選択します。
• 挿入に移動します タブ>> テーブルを選択します .
  • [テーブルにヘッダーがある] を選択します。>> [OK] をクリックします。 .

• 読みやすくするために、テーブルに SalesData という名前を付けます。

Excel Table は、数式とグラフのダイナミック レンジを提供します。

ステップ 2:フォーム コントロールを挿入し、セルにリンクする

データセットのタイプに基づいて、関連するフォーム コントロールを選択できます。

• オプション ボタン (地域でフィルタリングするため):
  • 開発者 に移動します。 挿入からタブ>>>> オプション ボタンを選択します .
  • プラス アイコン (+) を配置します。 ダッシュボード領域。
  • オプション ボタンを右クリックします。>> フォーマット コントロールを選択します。 .

• • フォーマット オブジェクト 内 ダイアログボックス:
    • セルリンク :セル I1 を選択して、選択した領域を保存します。
    • 3-D シェーディングを選択します。>> [OK] をクリックします。 .
  • オプション ボタンを右クリックします。>> [テキストの編集] を選択します。>> 地域 を挿入 (例:北、南、東、西) 1 つずつ

• コンボボックス (製品でフィルタリングするため):
  • 「開発者」タブ → 挿入 → コンボボックス .
  • ダッシュボード領域に置きます。
  • 開発者 に移動します。 挿入からタブ>>>> コンボボックスを選択します .
  • プラス アイコン (+) を配置します。 ダッシュボード領域。
  • コンボ ボックスを右クリックします。>> フォーマット コントロールを選択します。 .
  • フォーマット オブジェクト 内 ダイアログボックス:
    • 入力範囲 :製品のリストを選択します (例:製品 A、製品 B、製品 C、製品 D)。
    • セルリンク :セル J1 を選択して、選択した製品を保存します。
    • 3-D シェーディングを選択します。>> [OK] をクリックします。 .

• チェックボックス (収益、販売数、利益などの指標を切り替える):
  • 開発者 に移動します。 挿入からタブ>>>> チェックボックスを選択します .
  • プラス アイコン (+) を配置します。 ダッシュボード領域。
  • チェックボックスを右クリックします。>> フォーマット コントロールを選択します。 .
  • フォーマット オブジェクト 内 ダイアログボックス:
    • セルリンク :セル K1 を選択して、選択したメトリクスを保存します。
    • 3-D シェーディングを選択します。>> [OK] をクリックします。 .
  • オプション ボタンを右クリックします。>> [テキストの編集] を選択します。>> 指標名 (収益、販売数、利益など) を 1 つずつ挿入します。
  • セルにリンクされている場合、各チェックボックスは TRUE または FALSE を返します (例:それぞれ、収益、販売数、利益の K1、K2、K3)。

ステップ 3:動的数式を作成する

動的関数を使用して販売データを形式コントロールにリンクし、ダッシュボードをインタラクティブにすることができます。 FILTER、SWITCH、IF 関数を使用して、フォームの選択に基づいてデータを動的に抽出します。

動的 FILTER 関数の使用

地域ごとにデータをフィルタリングする :

選択したセルに次の数式を挿入して、選択した地域に基づいて売上データをフィルタリングします。

=FILTER(SalesData, SalesData[Region] = SWITCH(J1, 1, "North", 2, "South", 3, "East", 4, "West"))

• フィルタ 関数は SalesData テーブルを抽出し、地域がセル J1 で選択された値と一致する行を表示します。
• スイッチ 関数は、J1 の数値 (例:1、2、3、または 4) を対応する地域名 (「North」、「South」、「East」、「West」) に変換します。一致する行のみが結果に含まれます。

商品ごとにデータをフィルタリングする :

選択した製品に基づいて売上データをフィルタリングするには、選択したセルに次の数式を挿入します。

=FILTER(SalesData, SalesData[Product] = SWITCH(K1, 1, "Product A", 2, "Product B", 3, "Product C", 4, "Product D"))

この数式は、コンボ ボックスの選択に基づいて販売データをフィルタリングします。コンボ ボックスから製品 A を選択したので、製品 A の販売データが返されます。

地域と製品ごとにデータをフィルタリングする :

選択したセルに次の数式を挿入し、選択した地域と製品に基づいて売上データをフィルタリングします。

=FILTER(SalesData, (SalesData[Region] = SWITCH(J1, 1, "North", 2, "South", 3, "East", 4, "West")) * (SalesData[Product] = SWITCH(K1, 1, "Product A", 2, "Product B", 3, "Product C", 4, "Product D")))

この数式は、コンボ ボックスとオプション ボタンの選択に基づいて販売データをフィルター処理します。南部地域と製品 B を選択しました。フィルター関数は、南部地域からの製品 B の販売情報のみを返します。

動的メトリック表示

IF 関数を使用すると、チェック ボックスの選択に基づいて販売指標を表示できます。

チェックボックスの値に基づいて、メトリクスに次の条件付きロジックを挿入します。いずれかのチェックボックスを選択すると、リンクされたセルで TRUE が返され、それ以外の場合は FALSE が返されます。リンクされたセルの値に基づいて、IF 関数は結果を返します。

収益 :

次の式を挿入して総収益を取得します。

=IF(L1, SUM(SalesData4[Revenue ($)]), "")

フィルタリングされた販売データから合計を取得するには、次の式を挿入します。

[収益] のチェックボックスをオンにすると、これらの数式で収益が合計されます。

利益 :

次の式を挿入して、合計利益を取得します。

=IF(M1, SUM(SalesData4[Profit ($)]), "")

フィルタリングされた販売データから合計を取得するには、次の式を挿入します。

「利益」のチェックボックスを選択すると、これらの数式で利益が合計されます。

販売数 :

次の式を挿入して、合計販売個数を取得します。

=IF(N1, SUM(SalesData4[Units Sold]), "")

フィルタリングされた販売データから合計を取得するには、次の式を挿入します。

販売単位のチェックボックスをオンにすると、これらの数式で合計単位が合計されます。

出力:

ステップ 4:動的グラフを挿入する

• フィルタリングされたデータを選択します。
• 挿入 に移動します。 タブ>> [すべてのチャート] をクリックします。>> 列 を選択します チャート。

• グラフはコンボ ボックスに基づいて自動的に更新されます。 とオプション ボタン 。このグラフは、北部地域における製品 A の売上を示しています。

• フィルター オプションを変更して、グラフの動的更新を確認します。東部地域と製品 C を選択しました。

ステップ 5:ダッシュボードをカスタマイズする

• 図形の挿入:
  • ダッシュボードの要件に応じて、フィルタ、グラフ、指標などのセクションを個別に追加します。
• 書式設定:
  • 一貫した色、フォント、スタイルを使用できます。
  • 条件付き書式を使用して強調表示できます。

結論

上記の手順により、Excel のフォーム コントロールを使用した対話型ダッシュボードを作成できます。大規模なデータセットを使用してインタラクティブで視覚的に魅力的なダッシュボードを作成することで、重要な手順をすべて示しました。データを動的にフィルタリングして分析できるため、意思決定のための強力なツールになります。フォーム コントロールの追加機能を忘れずに試してください。

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