年度毎月別の表から指定年度のデータを引用しグラフを描く

 左のような年度毎月別に作成した表があります。この表はある物を観察した値です。 年度を指定すると該当する年度の値からグラフを描く関数式を説明します。

[ 表の説明 ]

 行(4行目から15行目)は月(1月から12月)です。列(CからF)は年(H1からH4)です。 セルB3(黄色)は、年度を入力する場所です。B列の4行目から15行目は、グラフ描画のデータ参照範囲です。セルB3に年度(H1〜H4の値)を入力するとB列の4行目から15行目(月)に該当する年度のデータが代入され、その値でグラフ描画を実行します。

[ セルB4の関数式 ]


関数:OFFSET  書式:OFFSET(基準,行数,列数,高さ,幅)

 基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセルまたはセル範囲の参照 (オフセット参照) を返します。返されるセル参照は、セル、セル範囲のいずれかの参照です。また、返されるセル参照の行数と列数を指定することもできます。


組合関数:ROW  書式:ROW(範囲)
引数として指定された範囲の行番号を返します。ROW()=セルB4の行数となり-3(見出しの行数を減算)することで「基準:セルB3」からの行数となります。4-3=1 基準B3からセルB4は1行目の位置といったことになります。


組合関数:MATCH  書式:MATCH(検査値,検査範囲,照合の型)
指定された照合の型に従って検査範囲内を検索し、検査値と一致する要素の、範囲内での相対的な位置を表す数値を返します。要するに、検査値(入力年)がセル範囲(セルC3からF3=年度)の何番目に位置するかを返します。セルB3に「H3」を入力した場合の値は、3となります。

この関数式の意味は、セルB3を基準として1行移動(ROW()-3)し、3列移動したセルE8の値を代入するといった意味になります。





エラー処理

 存在しない年度を入力すると
   
#N/A →「対象データがない」といったエラーが発生します。存在しない時などエラーの時は、空白で体裁を整えたいものです。そこで、IF構文を組み合わせることによってエラー表示を回避することができます。この関数式はいろいろなエラーに対応することが可能であり、覚えることに意義あり と思います。

=IF(ISERROR(OFFSET($B$3,ROW()-3,MATCH($B$3,$C$3:$F$3,0))),"",OFFSET($B$3,ROW()-3,MATCH($B$3,$C$3:$F$3,0)))

ISERROR関数でOFFSETのエラー値を判断しエラーの時に「空白」、エラーでない時にOFFSETを実行するといった構文です。

最後に

以前に、セルの参照を可変にできないかとの問い合わせがあり、その回答としてOFFSET関数を説明したことがあります。その方は、「普段、見慣れない関数...」と話していました。その後、丁寧なメールを頂きました。「この関数を使ったことでその方の社内の評価が上がった」と示されていました。Mambowも関数で知らないことはたくさんあります。その得た知識を活用することで自分に何か良い結果をもたされたことは嬉しいことで、なおさら第3者の方からのお褒めの言葉ですから嬉しさ倍増ってところですね。小生も、精進、精進.......