Windows 11 & Office 2021 Excel 2021

最新データから指定月分をグラフに自動表示する

投稿日:2023年2月5日 更新日:

名前定義にOFFSET関数とCOUNTA関数を使用すると、グラフの範囲を自動的に変更することができて便利です。

ここでは、最新データから指定月分をグラフに表示する方法を解説します。

表示したい範囲の数値は、ドロップダウンリストから選択できるようにしておくと便利です。

最新データから指定月をグラフに表示

参考OFFSET関数を使用して自動拡張できるグラフの作成については、以下の記事で解説しています。

グラフのデータ範囲を可変にして自動でグラフを拡張する2つの方法

Excelでグラフを作成して、グラフの参照元の範囲を拡張すると、グラフのデータ範囲も拡張しなければなりません。 この操作を行わず、グラフの参 ...

続きを見る

ショートカットキーで縦棒グラフを作成

以下のデータをもとに縦棒グラフを作成します。

グラフ元のデータ

標準グラフは、ショートカットキーでも作成できます。

グラフの元データとなる表内のセルを1つ選択します。

ここでは、データのすべてをグラフにするので、グラフ元となる表内のセルを1つ選択するだけでOKす。

表内のセルを1つ選択

キーボードから[Alt]+[F1]を押します。

キーボード[Alt]+[F1]

シート内に縦棒グラフがオブジェクトとして作成されます。このショートカットキーは、標準グラフの作成になります。

縦棒グラフを作成した後、グラフの種類を変更することもできます。

グラフオブジェクトの作成

参考合計値などグラフ作成に不要な範囲がある場合は、範囲選択してください。

また、範囲選択して[F11]キーを押すと、新しいシート(グラフシート)に標準グラフが作成されます。

このショートカットキーについては、以下の記事で解説しています。

キーボードからグラフを作成するには[F11]または[Alt+F1]で

Excelでは、範囲選択して[F11]キーを押すだけで、グラフを作成することができます。 下のような表があります。 グラフにしたい部分を範囲 ...

続きを見る

また、ショートカットキーで作成できる[標準グラフ]は変更することができます。

書式設定済みのグラフを標準グラフにしてショートカットキーでグラフを作成

グラフは、ショートカットキーで作成することができます。 ただし、作成できるグラフは[標準グラフ]です。既定の[標準グラフ]は集合縦棒グラフで ...

続きを見る

データラベルの表示

グラフの右上の[グラフ要素]を展開すると、メニューが表示されます。

ここでは、[データラベル]をクリックして、縦棒グラフにデータラベルを表示します。

グラフのデータラベルを表示

グラフタイトルをセル参照に

グラフタイトルは、直接入力してもかまいませんが、入力済みのセルがあるならば、セル参照で表示すると便利です。

グラフタイトルを選択します。

そして、数式バーに[=]を入力して、参照したいセル(ここではセル[B1])をクリックします。

グラフタイトルをセル参照に変更

[Enter]キーを押すと、以下のようになります。

タイトルが変更された縦棒グラフ

参考ここでは、グラフタイトルは、すでに入力したものがあったのでセルをリンクする方法を紹介していますが、グラフタイトルは直接入力しても構いません。

また、Office2016から追加された新グラフでは、タイトルのセル参照はできません。

最新データから常に3か月分のデータをグラフに表示

作成したグラフをもとに編集していきます。

[月]と[数量]の名前定義を作成

[月]と入力されたセル[B2]を選択して、[数式]タブの[定義された名前]グループにある[名前の定義]をクリックします。

名前の定義

名前を定義して適用します。

[数式]タブの[定義された名前]グループにある[名前の定義]

[新しい名前]ダイアログボックスが表示されます。

[名前]のテキストボックスには、[月]と入力済みになっています。

[範囲]は、[ブック]が選択されていますが、プルダウンから[シート名]を選択します。ここでは[Sheet1]です。

参照範囲には、以下の数式を入力します。[OK]ボタンをクリックして閉じます。

シート名は自動で入力されますので、入力する必要はありません。

=OFFSET($B$4,COUNTA($B:$B)-2-3,0,3)

[新しい名前]ダイアログボックス

再度、[名前の定義]をクリックして、[新しい名前]ダイアログボックスを表示します。

[名前]に分かりやすい名前を付けます。ここでは[数量]と付けます。

[範囲]は、シート名を選択します。

[参照範囲]には、以下のように入力して、[OK]ボタンをクリックして閉じます。

=OFFSET($C$4,COUNTA($B:$B)-2-3,0,3)

[新しい名前]ダイアログボックス

[数式]タブの[定義された名前]グループにある[名前の管理]をクリックして確認してみます。

[月]と[数量]という名前があり、異なるのは基準となるセル値のみとなっています。

[名前の管理]ダイアログボックス

OFFSET関数とCOUNTA関数

OFFSET関数は、[参照]のセルから[行数]と[列数]分移動したセルを始点にして、[高さ](行)と[幅](列)のセル参照を返す関数です。

OFFSET(参照, 行数, 列数, [高さ], [幅]) [高さ]と[幅]は省略可能です。

OFFSET 関数(Microsoft)

OFFSET関数を分解すると、以下の内容になります。

  • 基準:セル[B4]
    基準となるセル(セル範囲)を指定します。
  • 行数:COUNTA($B:$B)-2-3
    基準のセルから下方向へ移動する数を指定します。
    B列の空白でないセルの数をCOUNTA関数で求めます。[-2]は、B列のセル[B1](月別売上数)とセル[B3](月)を省くためです。

参照範囲の数式の解説

表の作りにより、行数の数値は変わりますので気を付けてください。

例えば、表の開始位置がセル[B1]であれば、[-1]となります。

表にタイトルがない場合の[名前の管理]ダイアログボックス

COUNTA関数は、範囲内の空白でないセルの個数を返します。空白スペースは文字としてカウントされます。

COUNTA 関数(Microsoft)

参考COUNT関数は、範囲内の数値が含まれるセルの個数を返します。

COUNT 関数(Microsoft)

グラフ系列の数式に定義済みの名前を組み込む

縦棒グラフの系列をクリックします。

数式バーに以下のような数式が表示されます。この数式を編集します。

グラフ系列の数式

数式の[$B$4:$B$9]を[月]、[$C$4:$C$9]を[数量]に変更します。

=SERIES(Sheet1!$C$3,Sheet1!月,Sheet1!数量,1)

最新のデータから3か月分(7月、8月、9月)のグラフが表示されるようになります。

グラフ系列の数式を編集

確認のために10月のデータを表に追加してみます。最新から3か月分(8月、9月、10月)のグラフに変わります。

データを追加した場合のグラフ

表示する月をセル参照に変更して自動表示させるには

最新の3か月分のデータをグラフに自動表示させるために、名前定義でOFFSET関数とCOUNTA関数を使用して設定しています。

この名前の定義で入力した数式を編集します。

[数式]タブの[定義された名前]グループの[名前の管理]をクリックして、[名前の管理]ダイアログボックスを表示して、[月]と[数量]の参照範囲を確認します。

3か月分のデータ表示なので、参照範囲に[3]が入力されています。

[名前の管理]ダイアログボックスで2個の名前を確認

セル参照のセルを決めて、数値を入力します。

ここでは、セル[H2]を参照セルとします。仮に[6]と入力しておきます。

参照のセルに数値を入力

名前定義の参照範囲を修正

名前定義の参照範囲を修正します。

[名前の管理]ダイアログボックスの[月]の名前を選択して、[編集]ボタンをクリックします。

[名前の管理]ダイアログボックスの[編集]ボタン

[名前の編集]ダイアログボックスが表示されます。

[参照範囲]の[ダイアログボックス拡大/縮小]ボタンをクリックします。

[名前の編集]ダイアログボックス

以下のような[名前の編集-参照範囲]ダイアログボックスとなります。

このウィンドウは、大きさを変更できますので、見やすい大きさに拡大しておくといいと思います。

[3]の箇所が2か所ありますので、ここへセル参照を入力します。ここでは[H2]ですね。

[H2]と入力して、キーボードから[F4]キーを押すと、絶対参照になります。

[名前の編集-参照範囲]ダイアログボックス

以下のようになったら、再度、[ダイアログボックス拡大/縮小]ボタンをクリックします。

参照範囲を修正した[[名前の編集-参照範囲]ダイアログボックス

[名前の編集]ダイアログボックスで[参照範囲]が修正されたことを確認して、[OK]ボタンをクリックします。

=OFFSET(Sheet1!$B$4,COUNTA(Sheet1!$B:$B)-2-$H$2,0,$H$2)

[名前の編集]ダイアログボックス

同じように[数量]も[3]の箇所をセル参照に修正します。

[名前の管理]ダイアログボックスには、シート名が表示されていますが、シート名は自動で入力されたものです。

[月]と[数量]の参照範囲を修正した後の[名前の管理]ダイアログボックス

グラフは、以下のように最新データから6(セル[H2]の数値)か月分の表示になります。

6か月分のデータが表示されたグラフ

試しに、セル[H2]の数値を[4]に変更してみます。すぐに最新から4か月分のグラフになります。

4か月分のデータが表示されたグラフ

ドロップダウンリストから選択できるように設定

数値を入力するセルは、入力規則でドロップダウンから選択できるようにしておくと便利です。

[データ]タブの[データツール]グループにある[データの入力規則]をクリックします。

データの入力規則

セルに入力できるデータの種類を制限するルールを一覧の中から選びます。

たとえば、1、2、3などの値リストを表示したり、1000より大きい数値のみを有効値として許可したりできます。

[データ]タブの[データツール]グループにある[データの入力規則]

[データの入力規則]ダイアログボックスが表示されます。

[入力値の種類]をドロップダウンリストの選択肢から[リスト]を選択します。

そして、元の値に、セルに表示したい数値を入力します。ここでは、以下のように入力しています。

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

2,3,4,5,6,7,8,9,10,11,12

[データの入力規則]ダイアログボックス

セル[H2]のドロップダウンリストから数値を選択すると、その数値分の最新データが表示されるようになります。

ただし、グラフ元のデータより大きい数値を入力するとエラーになりますので、気をつけてください。

ドロップダウンリストから数値を選択したグラフ

グラフの種類の変更

折れ線グラフなどに変更しても面白いと思います。

グラフを選択すると、[グラフのデザイン]タブが表示されますので、[種類]グループの[グラフの種類の変更]をクリックします。

[グラフの種類の変更]ダイアログボックスが表示されますので、[すべてのグラフ]タブから[折れ線]を選択します。

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

[グラフの種類の変更]ダイアログボックスから[折れ線]を選択

以下のようになります。

同じように最新データの数値を変更すると、折れ線グラフのデータ範囲を変更できます。

縦棒グラフを折れ線グラフに変更

名前定義をせずにグラフでデータを絞り込む方法

名前定義を設定しなくても、グラフデータを絞り込むこともできます。

グラフフィルターで表示するデータを指定

グラフフィルターを利用して、グラフに表示する内容を簡単に変更できます。

グラフフィルターは、Excel2013以降の機能です。

標準グラフを作成します。

グラフフィルターをクリックします。表示したい月のチェックボックスをオンにします。連続しない月でもOKです。

グラフフィルターでグラフデータの絞り込み

テーブルのスライサーで絞り込み

Excel2013以降は、テーブルでもスライサーを使用できるようになっていますので、スライサーを使用してデータを絞り込むこともできます。

テーブルでもスライサーの挿入ができる(フィルター処理を視覚的に)

Excel2013では、テーブルでも[スライサー]が使用できるようになりました。 以下のようなスライサーを使った絞り込みを行うことができます ...

続きを見る

フィルターボタンで絞り込むこともできますが、スライサーを使用すると視覚的に分かりやすくなります。

ただし、この場合は、グラフ元のデータも同時に絞り込まれます。

テーブルに変換

まず、表をテーブルに変換します。

表内のセルを選択して、[挿入]タブの[テーブル]グループにある[テーブル]をクリックします。

ショートカットキーは、[Ctrl]+[T]です。

テーブル

関連データの整理と分析を行うテーブルを作成します。

テーブルに作成しておくと、シート内のデータの並べ替え、フィルター、書式設定を簡単に行えるようになります。

[挿入]タブの[テーブル]グループにある[テーブル]

[テーブルの作成]ダイアログボックスが表示されますので、範囲を確認して問題がなければ[OK]ボタンをクリックします。

[テーブルの作成]ダイアログボックス

[テーブルツール]タブが現れ、自動的にスタイルが設定されて、フィルターボタンも表示されます。

テーブルに変換

自動的に設定されたスタイルを戻すには、[テーブルデザイン]タブの[テーブルスタイル]の[その他]ボタンをクリックして、

[テーブルデザイン]タブの[テーブルスタイル]の[その他]ボタン

スタイルの一覧から[なし]を選択します。

スタイルの一覧の[なし]

スライサーの挿入

[テーブルデザイン]タブの[ツール]グループにある[スライサーの挿入]をクリックします。

スライサーの挿入

スライサーを使って、データのフィルター処理を視覚的に実行します。

スライサーを使うと、テーブルやピボットテーブル、ピボットグラフ、キューブ関数のフィルター処理が速くなり、簡単になります。

[スライサーの挿入]ダイアログボックスが表示されます。

[月]のチェックボックスをオンして[OK]ボタンをクリックします。

[テーブルデザイン]タブの[ツール]グループにある[スライサーの挿入]

[月]のスライサーが表示されます。[複数選択]ボタンをクリックします。

[月]のスライサーの[複数選択]ボタン

以下のようにデータを絞り込んで表示できるようになります。

テーブルのスライサーでデータを絞り込み

スライサーの設定は、[月]のフィルターボタンでの選択内容と連動します。

[月]のフィルターボタン

参考範囲に名前を付けてOFFSET関数を使用して、グラフの範囲を自動的に移動することもできます。

水平スルロールバーを使ってグラフの表示期間をずらして表示する方法を解説しています。

水平スクロールバーを配置してグラフ系列の期間をずらして表示する

Excelのフォームコントロール内のスクロールバーを利用して、グラフの表示期間をずらして表示することができます。 水平スクロールバーの両端に ...

続きを見る

スクロール操作の画像

以下の記事でも名前定義にOFFSET関数を使用しています。

ドロップダウンリストから項目を選択してグラフ元の範囲を切り替える

入力規則で作成したドロップダウンリストから項目を選択して、グラフ範囲を変更する方法です。 表の項目と数値の参照範囲に名前を定義して、グラフ系 ...

続きを見る

Excel2021とMicrosoft 365のExcelでは、UNIQUE(ユニーク)関数を使用することができます。

UNIQUE関数を使うと、指定したデータ範囲から重複を削除したデータを作成できます。

以下は、UNIQUE関数とOFFSET関数、COUNTA関数を使用した内容の記事です。

UNIQUE関数で重複しない値をリスト化して表内の該当セルを塗りつぶす

Excel2021とMicrosoft 365のExcelでは、UNIQUE(ユニーク)関数を使用することができます。 UNIQUE関数を使 ...

続きを見る

ピボットテーブルとピボットグラフであれば、[タイムライン]を使用して、期間にフィルターをかけることができます。

Excel2013以降の機能です。

ピボットテーブルの[タイムライン]で日付のフィルター処理を行う

Excel2013のピボットテーブルの新機能です。 ピボットテーブルに日付フィールドがある場合、タイムラインで期間を指定してデータを動的に表 ...

続きを見る

ピボットテーブルのタイムライン

検索

ほかの記事も検索してみませんか?

Access / Excel / PowerPoint / Word

created by Rinker
★★★★★ 分かりやすい!Excelの基本はこれでOK♪
Amazonのレビューを見る

Profile

執筆者/はま

-Windows 11 & Office 2021 Excel 2021

Copyright © 2001-2024 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2024 AllRights Reserved Powered by AFFINGER4.