Excelダッシュボード-インタラクティブコントロール

ダッシュボードに表示するデータが1つの画面に収まらない場合は、Excel VisualBasicの一部として提供されているExcelコントロールを使用することを選択できます。最も一般的に使用されるコントロールは、スクロールバー、ラジオボタン、およびチェックボックスです。これらをダッシュ​​ボードに組み込むことで、インタラクティブにし、ユーザーが可能な選択によってデータのさまざまな側面を表示できるようにすることができます。

ダッシュボードにスクロールバー、チェックボックス、ラジオボタンなどのインタラクティブなコントロールを提供して、受信者が結果として表示されているデータのさまざまな側面を動的に表示できるようにすることができます。受信者と一緒にダッシュボードの特定のレイアウトを決定し、それ以降は同じレイアウトを使用できます。Excelのインタラクティブコントロールは使いやすく、Excelの専門知識は必要ありません。

Excelのインタラクティブコントロールは、リボンの[開発者]タブで使用できます。

リボンに[開発者]タブが見つからない場合は、次の手順を実行します。

  • [Excelのオプション]ボックスで[リボンのカスタマイズ]をクリックします。
  • [リボンのカスタマイズ]ボックスで[メインタブ]を選択します。
  • [メインタブ]リストの[開発者]ボックスをオンにします。
  • [OK]をクリックします。リボンに[開発者]タブがあります。

ダッシュボードのスクロールバー

ダッシュボードの機能の1つは、ダッシュボードの各コンポーネントが可能な限りコンパクトであることです。結果が次のようになっているとします-

以下に示すように、このテーブルにスクロールバーを表示できれば、データを参照しやすくなります。

スクロールバー付きの棒グラフに動的なターゲットラインを含めることもできます。スクロールバーを上下に動かすと、ターゲットラインが上下に移動し、ターゲットラインと交差しているバーが強調表示されます。

次のセクションでは、スクロールバーを作成する方法と、スクロールバーにリンクされた動的なターゲットラインを作成する方法を学習します。また、スクロールバーに動的ラベルを表示する方法についても学習します。

スクロールバーの作成

テーブルのスクロールバーを作成するには、最初に、以下に示すように、列のヘッダーをシートの空の領域にコピーします。

  • スクロールバーを挿入します。

    • リボンの[開発者]タブをクリックします。

    • [コントロール]グループの[挿入]をクリックします。

    • アイコンのドロップダウンリストの[フォームコントロール]の下にあるスクロールバーアイコンをクリックします。

  • カーソルを列Iに移動し、プルダウンして垂直スクロールバーを挿入します。

  • スクロールバーの高さと幅を調整し、テーブルに合わせます。

  • スクロールバーを右クリックします。

  • ドロップダウンリストで[フォーマットコントロール]をクリックします。

[フォーマット制御]ダイアログボックスが表示されます。

  • [制御]タブをクリックします。

  • 表示されるボックスに次のように入力します。

  • [OK]ボタンをクリックします。スクロールバーを使用する準備が整いました。スクロールバーを上下に移動すると、値0〜36をとるスクロールバーのセルリンクとしてセルO2を選択しました。次に、セルO2の値に基づいた参照を使用して、テーブル内のデータのコピーを作成する必要があります。

  • セルK3に、次のように入力します-

    = OFFSET(Summary [@ [S。No。]]、$ O $ 2,0)。

  • Enterボタンを押します。数式をコピーする列のセルに入力します。

  • 数式をコピーして、他の列のセルに入力します。

動的でスクロール可能なテーブルをダッシュ​​ボードにコピーする準備ができました。

  • スクロールバーを下に移動します。

ご覧のとおり、セル内の値(スクロールバーのセルリンク)が変化し、この値に基づいてテーブル内のデータがコピーされます。一度に12行のデータが表示されます。

  • スクロールバーを一番下までドラッグします。

データの最後の12行は、現在の値が36(セルO2に表示)であり、36が[フォームコントロール]ダイアログボックスで設定した最大値であるために表示されます。

要件に応じて、動的テーブルの相対位置の変更、一度に表示される行数の変更、スクロールバーへのセルリンクなどを変更できます。上で見たように、これらは[フォーマット制御]ダイアログボックスで設定する必要があります。

動的でインタラクティブなターゲットラインの作成

過去6か月間の売上を地域ごとに表示するとします。また、毎月の目標を設定しました。

次のことができます-

  • このすべての情報を示す縦棒グラフを作成します。
  • 列全体にターゲットラインを作成します。
  • ターゲットラインをスクロールバーでインタラクティブにします。
  • ターゲットラインを動的にして、データのターゲット値を設定します。
  • 目標を満たしている値を強調表示します。

このすべての情報を示す縦棒グラフを作成します

データを選択します。クラスター化された縦棒グラフを挿入します。

列全体にターゲットラインを作成します

チャートタイプをコンボに変更します。ターゲットシリーズの場合は[線]、残りのシリーズの場合は[クラスター化された列]としてグラフの種類を選択します。

ターゲットラインのベーステーブルを作成します。これは後で動的にします。

ターゲットラインのデータ系列値を上の表のターゲット列に変更します。

[OK]ボタンをクリックします。

クラスター化された列の配色を変更します。ターゲットラインを緑色の点線に変更します。

スクロールバーを使用してターゲットラインをインタラクティブにします

  • スクロールバーを挿入してグラフの下に配置し、1月から6月までのサイズにします。

  • [フォーマット制御]ダイアログボックスにスクロールバーのパラメータを入力します。

  • 月とターゲットの2つの列を持つテーブルを作成します。

  • データテーブルとスクロールバーのセルリンクに基づいて値を入力します。

このテーブルには、スクロールバーの位置に基づいて月と対応するターゲットが表示されます。

ターゲットラインを動的にして、データのターゲット値を設定します

これで、ターゲットラインを動的にする準備が整いました。

  • すべての行に= $ G $ 12と入力して、ターゲット行用に作成したベーステーブルのターゲット列の値を変更します。

ご存知のように、セルG12はターゲット値を動的に表示します。

ご覧のとおり、ターゲットラインはスクロールバーに基づいて移動します。

目標を達成している値を強調表示する

これが最後のステップです。任意の時点で目標を満たす値を強調表示する必要があります。

  • データテーブルの右側に列を追加します-East-Results、North-Results、SouthResults、West-Results。

  • セルH3に、次の数式を入力します-

    = IF(D3> = $ G $ 12、D3、NA())

  • 数式をテーブル内の他のセルにコピーします。テーブルのサイズを変更します。

ご覧のとおり、East-Results、North-Results、SouthResults、West-Resultsの列の値は、スクロールバー(つまり、ターゲット値)に基づいて動的に変化します。ターゲット以上の値が表示され、その他の値は#N / Aです。

  • グラフのデータ範囲を変更して、新しく追加された列をデータテーブルに含めます。

  • [チャートタイプの変更]をクリックします。

  • ターゲットシリーズをラインにし、残りをクラスター化された列にします。

  • 新しく追加されたデータ系列については、[2次軸]を選択します。

  • 系列East、North、South、Westの塗りつぶし色がオレンジ色になり、系列East-Results、North-Results、South-Results、WestResultsの塗りつぶし色が緑色になるようにデータ系列をフォーマットします。

  • ターゲットラインのデータラベルを入力し、動的データテーブルの月の値へのセル参照を使用して動的にします。

動的なターゲットラインを含むチャートは、ダッシュボードに含める準備ができています。

二次軸は不要なのでクリアできます。スクロールバーを移動すると、ターゲットラインが移動し、それに応じてバーが強調表示されます。ターゲットラインには、月を示すラベルもあります。

Excelオプション(ラジオ)ボタン

ラジオボタンは通常、特定のオプションセットからオプションを選択するために使用されます。常に小さな円で描かれ、選択するとドットが表示されます。ラジオボタンのセットがある場合、選択できるのはそのうちの1つだけです。

Excelでは、ラジオボタンはオプションボタンと呼ばれます。

グラフのExcelオプションボタンを使用して、読者が確認したいデータの詳細を選択できます。たとえば、前のセクションの例では、月に基づくターゲット値を持つ動的なターゲットラインを取得するためのスクロールバーを作成しました。オプションボタンを使用して、月、つまりターゲット値を選択し、ターゲットラインをターゲット値に基づいて選択できます。以下は手順です-

  • このすべての情報を示す縦棒グラフを作成します。
  • 列全体にターゲットラインを作成します。
  • オプションボタンを使用してターゲットラインをインタラクティブにします。
  • ターゲットラインを動的にして、データのターゲット値を設定します。
  • 目標を満たしている値を強調表示します。

手順1と2は、前の場合と同じです。2番目のステップが終了すると、次のグラフが作成されます。

オプションボタンを使用してターゲットラインをインタラクティブにします

  • オプションボタンを挿入します。

    • リボンの[開発者]タブをクリックします。

    • [コントロール]グループの[挿入]をクリックします。

    • アイコンのドロップダウンリストの[フォームコントロール]の下にある[オプションボタン]アイコンをクリックします。

チャートの右上隅に配置します。

オプションボタンを右クリックします。ドロップダウンリストの[フォーマット制御]オプションをクリックします。

[コントロール]タブの[オブジェクトのフォーマット]ダイアログボックスにオプションボタンのパラメーターを入力します。

セルF10はオプションボタンにリンクされています。オプションボタンのコピーを垂直に5つ作成します。

ご覧のとおり、すべてのオプションボタンには、キャプション名と呼ばれる同じ名前が付いています。ただし、内部的には、Excelのこれらのオプションボタンには異なる名前が付けられており、[名前]ボックスで確認できます。さらに、オプションボタン1がセルF10にリンクするように設定されているため、すべてのコピーも同じセルを参照します。

オプションボタンのいずれかをクリックします。

ご覧のとおり、リンクされたセルの番号がオプションボタンのシリアル番号に変わります。オプションボタンの名前を1月、2月、3月、4月、5月、6月に変更します。

月とターゲットの2つの列を持つテーブルを作成します。データテーブルとスクロールバーのセルリンクに基づいて値を入力します。

このテーブルには、選択したオプションボタンに基づいて月と対応するターゲットが表示されます。

ターゲットラインを動的にして、データのターゲット値を設定します

これで、ターゲットラインを動的にする準備が整いました。

  • すべての行に= $ G $ 12と入力して、ターゲット行用に作成したベーステーブルのターゲット列の値を変更します。

ご存知のように、セルG12はターゲット値を動的に表示します。

ご覧のとおり、選択したオプションボタンに基づいてターゲットラインが表示されます。

目標を達成している値を強調表示する

これが最後のステップです。任意の時点で目標を満たす値を強調表示する必要があります。

  • データテーブルの右側に列を追加します-East-Results、North-Results、SouthResults、West-Results。

  • セルH3に、次の数式を入力します-

    = IF(D3> = $ G $ 12、D3、NA())

  • 数式をテーブル内の他のセルにコピーします。テーブルのサイズを変更します。

ご覧のとおり、East-Results、North-Results、SouthResults、West-Resultsの列の値は、スクロールバー(つまり、ターゲット値)に基づいて動的に変化します。ターゲット以上の値が表示され、その他の値は#N / Aです。

  • グラフのデータ範囲を変更して、新しく追加された列をデータテーブルに含めます。

  • [チャートタイプの変更]をクリックします。

  • ターゲットシリーズをラインにし、残りをクラスター化された列にします。

  • 新しく追加されたデータ系列については、[2次軸]を選択します。

  • 系列East、North、South、Westの塗りつぶし色がオレンジ色になり、系列East-Results、North-Results、South-Results、WestResultsの塗りつぶし色が緑色になるようにデータ系列をフォーマットします。

  • セル$ G $ 12の値を使用して動的データラベルをターゲット行に追加します。

  • 二次軸は不要なのでクリアしてください。

  • リボンの[表示]タブで、[グリッド線]チェックボックスをオフにします。

  • FormatAxisオプションのLabelオプションをHighに変更します。これにより、垂直軸ラベルが右にシフトし、ターゲットラインデータラベルが目立つようになります。

動的なターゲットラインとオプションボタンを備えたチャートは、ダッシュボードに含める準備ができています。

オプションボタンを選択すると、選択した月の目標値に従って目標線が表示され、それに応じてバーが強調表示されます。ターゲットラインには、ターゲット値を示すデータラベルもあります。

Excelのチェックボックス

チェックボックスは通常、特定のオプションセットから1つ以上のオプションを選択するために使用されます。チェックボックスは常に小さな四角で表され、選択するとチェックマークが付きます。チェックボックスのセットがある場合、それらをいくつでも選択できます。例えば、

グラフのExcelチェックボックスを使用して、読者が確認したいデータの詳細を選択できます。たとえば、前のセクションの例では、東、北、南、西の4つの地域のデータを表示する縦棒グラフを作成しました。チェックボックスを使用して、データを表示する地域を選択できます。一度に任意の数のリージョンを選択できます。

前のセクションの最後のステップから始めることができます-

  • チェックボックスを挿入します。

    • リボンの[開発者]タブをクリックします。

    • [コントロール]グループの[挿入]をクリックします。

    • アイコンのドロップダウンリストの[フォームコントロール]の下にある[チェックボックス]アイコンをクリックします。

  • チャートの左上隅に配置します。

  • チェックボックスの名前をEastに変更します。

  • チェックボックスを右クリックします。ドロップダウンリストで[フォーマットコントロール]をクリックします。

  • [コントロール]タブの[フォーマットコントロール]ダイアログボックスにチェックボックスパラメータを入力します。

  • [OK]ボタンをクリックします。リンクされたセルC19で、チェックボックスを選択するとTRUEが表示され、チェックボックスの選択を解除するとFALSEが表示されることがわかります。

  • チェックボックスをコピーして、水平方向に3回貼り付けます。

  • 名前をNorth、South、Westに変更します。

ご覧のとおり、チェックボックスをコピーすると、リンクされたセルはコピーされたチェックボックスでも同じままです。ただし、チェックボックスには複数の選択肢がある可能性があるため、リンクされたセルを異なるものにする必要があります。

  • 北、南、西のリンクされたセルをそれぞれ$ C $ 20、$ C $ 21、$ C $ 22に変更します。

次のステップは、選択したリージョンのデータのみをチャートに含めることです。

  • 次のようにテーブル構造を作成します-

  • セルC21にタイプ= IF($ C $ 19、H3、NA())。
  • セルD21にタイプ= IF($ D $ 19、I3、NA())。
  • セルE21にタイプ= IF($ E $ 19、J3、NA())。
  • セルF21にタイプ= IF($ F $ 19、K3、NA())。
  • 表の他の行に入力します。
  • ターゲット列を追加します。

  • チャートデータをこのテーブルに変更します。

グラフには、選択した月に設定された目標値を超える、選択した地域のデータが表示されます。