インタラクティブな図

説明

大量の情報を高品質に視覚化することは、ほとんどの場合簡単な作業ではありません。そのため、すべてのデータを表示すると、図の過負荷や混乱が生じ、その結果、誤った認識や結論が得られることがよくあります。

たとえば、数か月間の為替レートのデータは次のとおりです。

ご覧のとおり、テーブル全体をプロットするのは得策ではありません。同様の状況における優れた解決策は、ユーザーが自分自身や状況に合わせて調整できる対話型の図を作成することです。つまり:

• 時間軸に沿って未来と過去を行ったり来たり。
• 図の個々の領域を拡大または縮小して、グラフの詳細を詳しく調べます。
• 選択した通貨の表示を有効/無効にします。

次のようになります。

あなたはそれが好きですか？それから私たちは行きました...

ステップ 1. ダイアグラム用に追加のテーブルを作成します。

ほとんどの場合、図の対話性を実装するには、シンプルだが強力な手法が使用されます。図は、元の図に従って構築されるのではなく、必要なデータのみを表示する、数式を含む別の特別に作成されたテーブルに従って構築されます。この場合、出力データは、ユーザーがチェックボックスを使用して選択した通貨についてのみ、この追加テーブルに転送されます。

Excel 2007/2010 では、作成した範囲にコマンドを適用できます。 テーブルとしてフォーマットする (Format as Table) タブから 主要 (Home) :

これにより、次のような利点が得られます。

• このような表内の数式は列全体に自動的に変換されます。表の末尾まで手動で「ドラッグ」する必要はありません。
• 今後テーブルに新しい行 (新しい日付とレート) が追加されると、グラフ内の範囲の調整、他の数式でのこのテーブルへの参照などを含め、テーブルのサイズが自動的に増加します。
• 表はすぐに美しい書式設定 (行間など) になります。
• 各テーブルには独自の名前が付けられ (この例では、Table1 と Table2)、それを数式で使用できます。

ステップ 2. 通貨のチェックボックスを追加する

Excel 2007/2010 では、このタブを表示する必要があります。 開発者 (Developer) および Excel 2003 以前のバージョン - ツールバー フォーム (Forms) 。このために：

• Excel 2003: メニューから選択 ビュー - ツールバー - フォーム (View - Toolbars - Forms)
• Excel 2007 の場合: ボタンをクリックします Office - オプション Excel - リボンに [開発] タブを表示します (Office Button - Excel options - Show Developer Tab in the Ribbon)
• Excel 2010: ファイル - オプション - リボン設定 - 開発者フラグをオンにします (File - Options - Customize Ribbon - Developer)

表示されるツールバーまたはタブ 開発者 (Developer) ドロップダウンリストで 入れる (Insert) ツールを選ぶ フラグ (Checkbox) 2 つのチェックマークを描画して、各通貨を有効または無効にします。

フラグのテキストを変更するには、マウスの右ボタンでフラグをクリックし、コマンドを選択します。 テキストを変更する (Edit text) 。

次に、フラグを任意のセルにバインドして、フラグが有効かどうかを判断しましょう (この例では、追加のテーブルの上部にある 2 つの黄色のセルです)。これを行うには、追加された各フラグを順番に右クリックし、コマンドを選択します オブジェクト形式 (Format Control) 、ウィンドウに設定します 細胞との通信 (Cell link) 。

私たちの目標は、各チェック ボックスを通貨列の上の対応する黄色のセルに結び付けることです。関連するセルでチェックボックスが有効になっている場合、表示されます 真実 (TRUE) 、スイッチをオフにするとき - 嘘 (FALSE) 。これにより、将来的には、数式を使用して接続されたセルをチェックし、追加のテーブルに表示したり、グラフを作成するために元のテーブルの為替レート値を表示したりできるようになります。 #N/A (#N/A) , そのため、グラフは構築されません。

ステップ 3. データを追加のテーブルに変換します

次に、対応する通貨フラグが有効で、関連付けられたセルに単語が含まれている場合に、メイン テーブルからの出力データを変換する数式をセカンダリ テーブルに入力しましょう。 真実 (TRUE) :

コマンドを使用するときの注意点 テーブルとしてフォーマットする (Format as Table) 最初のステップでは、式でテーブルの名前と列の名前を使用する必要があります。通常の範囲の場合、式はより馴染みのあるものになります。

=ЕСЛИ(F$1; B4; #Н/Д)

黄色のセル (F$1) への参照が部分的に固定されていることに注意してください。そのため、数式を範囲全体にコピーするときに、下ではなく右に移動する必要があります。

ここで、フラグをチェックすると、追加のテーブルには元のテーブルのデータ、またはグラフ上に線が表示されない人工的に作成されたエラー # N/A が埋め込まれます。

ステップ 4. 時間軸とスケーリング用のスクロール バーを作成します。

次に、Excel シートにスクロール バーを追加しましょう。これを使用すると、ユーザーは時間軸に沿ってグラフを簡単に移動し、グラフの増加スケールを変更できます。

スクロール・バー (Scroll bar) ツールバー上のフラグと同じ場所に配置します。 フォーム (Forms) またはタブ上で 開発者 (Developer) :

時間とスケールを変更するために、シート上の任意の場所に 2 つのストリップを 1 つずつ描画します。

各スクロール バーは、スライダーの位置の数値が表示される独自のセル (図の青と緑のセル) に関連付ける必要があります。次に、それを使用してスケールとオフセットを決定します。これを行うには、描画されたストリップを右クリックし、コンテキスト メニューからコマンドを選択します。 オブジェクト形式 (Format control) 。ダイアログ ボックスでは、スライダーが移動する範囲内で、関連するセルと最小値と最大値を設定できます。

したがって、上記のすべてを行った後、スライダーを移動すると 2 つのスクロール バーが表示され、それに沿って関連するセルの値が 1 ～ 307 の範囲で変化するはずです。

ステップ 5. 動的な名前付き範囲を作成する

特定の期間のデータのみをグラフに表示するには、追加のテーブル内の必要なセルのみを参照する名前付き範囲を作成します。この範囲は 2 つのパラメータによって特徴付けられます。

• 表の先頭から指定された行数だけインデントします。つまり、過去から未来のタイムライン (緑色のセル) でインデントします。
• 高さのセルの数、つまりスケール (青いセル)

後で、この名前付き範囲を生データとして使用してグラフを作成します。

このような範囲を作成するには、次の関数を使用します。 СМЕЩ (OFFSET) カテゴリーから 参照と配列 (Lookup and Reference) - この関数は、シート内の指定された場所に指定されたサイズの範囲への参照を作成でき、次の引数を持ちます。

開始セルが開始点として取得され、それに対して指定された数の行だけ下に、右に列だけシフトが設定されます。この関数の最後の 2 つの引数は、必要な範囲の高さと幅です。したがって、たとえば、1 月 4 日から始まる 5 日間のレートを含むデータ範囲を参照したい場合は、関数を使用できます。 СМЕЩ (OFFSET) 次の引数を使用します。

=СМЕЩ(A3;4;1;5;2)

重要なのは、この数式の定数を、可変内容のセル (この場合は青と緑のセル) への参照に置き換えることができることです。これを行うには、関数を使用して動的な名前付き範囲を作成します。 СМЕЩ (OFFSET) 。このために：

• Excel 2007/2010 では、ボタンをクリックします。 ネームマネージャー (Name Manager) タブ上で 数式 (Formulas)
• Excel 2003 以前 - メニューから選択 挿入 - 名前 - 割り当て (Insert – Name – Define)

ボタンをクリックして新しい名前付き範囲を作成します 作成する (Create) をクリックし、ウィンドウに範囲名とセル参照を入力します。

まず、名前を付けた 2 つの単純な静的名前付き範囲を作成しましょう。 Shift そして Zoom 、それぞれ青と緑のセルを参照します。

ここで、少し複雑になります。名前を付けて範囲を作成しましょう。 Euros 、関数によって参照されます СМЕЩ (OFFSET) 以前に作成した範囲を使用して、選択した期間のユーロ為替レートのデータに基づいて シフト そして ズーム そして細胞 E3 参照点として:

現在のシートの名前が範囲の名前の前に使用されることに注意してください。これにより、名前付き範囲の範囲が狭まります。つまり、ワークブック全体ではなく、現在のシートの制限内で範囲が使用可能になります。これは、将来図を作成するために必要です。 Excel の新しいバージョンでは、ドロップダウン リストを使用してローカル シート名を作成できます。 地域 。

同様に、名前付き範囲が作成されます Dollars ドル為替レートに関するデータ:

そしてその範囲が写真を完成させます Labels これは、X 軸の署名、つまり選択したセグメントの日付を示します。

全体像は次のようになります。

ステップ 6. ダイアグラムを作成します

補助テーブルの上部にある複数の行 (たとえば、E3:G10 の範囲) を選択し、それに基づいてタイプ図を作成してみましょう。 スケジュール (Line) 。これを行うには、Excel 2007/2010 で、タブに移動する必要があります。 入れる (Insert) そしてグループの中で チャート (Chart) タイプを選択してください スケジュール (Line) 、古いバージョンではメニューから選択します 挿入図 - 図 (Insert - Chart) 。作成した図上の線の 1 つを強調表示すると、関数が数式バーに表示されます。 РЯД (SERIES) 、選択された一連のデータを提供します。

この関数は、選択したグラフ シリーズのデータ範囲とラベルを設定します。私たちのタスクは、引数内の静的な範囲を、以前に作成した動的な範囲に置き換えることです。これは、次のように変更することで数式バーで直接実行できます。

=РЯД(Лист1!$F$3; Лист1!$E$4:$E$10 ; Лист1!$F$4:$F$10 ; 1)

の上：

=РЯД(Лист1!$F$3; Лист1! Labels ; Лист1! Euros ; 1)

ドルとユーロのデータ系列に対してこの手順を順番に実行すると、必要なものが得られます。チャートはダイナミック レンジに従って構築されます。 Dollars そして Euros 、X 軸への署名は同じダイナミック レンジから取得されます。 Labels 。スライダーの位置を変更すると範囲が変更され、その結果、チャートが変更されます。フラグをオンまたはオフにすると、必要な通貨のみが表示されます。

したがって、分析に必要なデータを正確に表示できる完全にインタラクティブなチャートが完成しました。

トピックに関する記事:

• Excel でグラフ (図) を作成する
• グラフに 2 番目の軸を追加する方法